【SQL】Hive/Spark SQL笔记之时间函数、环比/同比/时间比较计算

获取当天：

'${zdt.format("yyyy-MM-dd")}'

//获取上月月末

select '${zdt.lastMonth().format("yyyy-MM-dd")}'

T-1上月末

select '${zdt.addDay(-1).lastMonth().format("yyyyMMdd")}'

1个小时前

select '${zdt.addHour(-1).format("yyyy-MM-dd")}'

昨天

select '${zdt.addDay(-1).format("yyyy-MM-dd")}'

前天

select '${zdt.addDay(-2).format("yyyy-MM-dd")}' 

add(3,-1) 表示上一周 参数3 为周(7天，非自然周)

select '${zdt.add(3,-1).format("yyyy-MM-dd")}'

//下一周

select '${zdt.add(3,1).format("yyyy-MM-dd")}'

add(2,-1) 表示上个月 参数2 为月

select '${zdt.add(2,-1).format("yyyy-MM-dd")}'

下个月：

select '${zdt.add(2,1).format("yyyy-MM-dd")}'

add(1,-1) 表示去年 参数1 为年

select '${zdt.add(1,-1).format("yyyy-MM-dd")}'去年的今天

//明年

select '${zdt.add(1,1).format("yyyy-MM-dd")}'

经常用到的时间维度对比：

---常用日期对比维度
where (ds = '${zdt.addDay(-1).format("yyyyMMdd")}' --mtd日期T-1日期
or ds='${zdt.addDay(-1).lastMonth().format("yyyyMMdd")}'--T-1上月末
or ds= '${zdt.addDay(-1).add(2,-1).format("yyyyMMdd")}' -- 上月T-1同期
or ds='${zdt.addDay(-1).add(1,-1).format("yyyyMMdd")}'  -- 上年T-1同期
or ds='${zdt.addDay(-1).add(1,-1).lastMonth().format("yyyyMMdd")}')-- 去年上月月末
or ds='${zdt.addDay(-1).add(1,-1).format("yyyy")}1231' 上年年末

---希望自己想要的时间分区为T-1日期的上月末
where  cast(format_datetime(date_parse(ds,'%Y%m%d'),'yyyy-MM-dd') as date) = date_add('day', -1, date_add('month', 1, date_trunc('month', cast('${zdt.addDay(-1).format("yyyy-MM-dd")}' as date))))
----希望自己想要的时间分区为T-1日期的那个月的月末
where  cast(format_datetime(date_parse(ds,'%Y%m%d'),'yyyy-MM-dd') as date) = date_add('day', -1, date_add('month', 1, date_trunc('month', cast('${zdt.addDay(-1).format("yyyy-MM-dd")}' as date))))

将两个不同的日期按自己的想法对齐

在lef join 两个不同的表格中，需要时间维度按自己的想法对齐

如：希望一个表的T-1日期维度下的数据=另外一个表的上月末日期（用户ID相等）

先将另外一个表只取每月月末数据，然后用下面的代码

on cast (a.cm_id as varchar)=cast (c.cm_id as varchar) and substr(cast(a.date as varchar),1,7)= substr(cast(date_add('month', 1 , cast(format_datetime(date_parse(c.dt,'%Y%m%d'),'yyyy-MM-dd') as date)) as varchar),1,7)

只取过去每年每月月末和T-1日期数据

where (dt>='20230101'
  and (substr(dt,5,4) in ('0131','0229','0331','0430','0531','0630','0731','0831','0930','1031','1130','1231')      
  or (dt like '%0228' and mod(cast(substr(dt,1,4) as bigint),4) in (1,2,3)))
   or dt='${zdt.addDay(-1).format("yyyyMMdd")}')

将日期减一天,加一天则是date_add('day',1,xxx)

SELECT date_add('day',-1, cast(format_datetime(date_parse(ds,'%Y%m%d'),'yyyy-MM-dd') as date))

月份进行相减，计算想要的日期相差多少个月

--prestro语法里：是将日期所在的月份进行相减：
date_diff('month',cast(concat(substr(t1.first_confirm_date,1,7),'-01') as date),cast(concat(substr(t3.stat_dt,1,7),'-01') as date)) as mob
---KYUUBI（SPARK）语法里,将日期所在的月份进行相减：
months_between(cast(concat(substr(t1.first_confirm_date,1,7),'-01') as date),cast(concat(substr(t3.stat_dt,1,7),'-01') as date)) as mob
---KYUUBI（SPARK）语法里,简单将日期直接进行相减获取相差多少天
 datediff(cast(date AS date), cast(stat_dt AS date)) as days