基本介绍
在 Apache Hive 中,datediff 函数用于计算两个日期之间的天数差异。它接受两个日期作为参数,并返回这两个日期之间的天数差。
以下是 datediff 函数的一般语法:
DATEDIFF(enddate, startdate)
其中 enddate 是结束日期,startdate 是起始日期。函数将返回 enddate 减去 startdate 后的天数差。
例如,如果我们想要计算 2022 年 1 月 1 日和 2022 年 1 月 10 日之间的天数差,我们可以使用以下 Hive 查询:
SELECT DATEDIFF('2022-01-10', '2022-01-01');
这将返回 9,表示这两个日期之间相隔 9 天。
总之,datediff 函数在 Hive 中是用于计算日期之间天数差异的非常有用的函数。
实战
求用户的日活,2日留存,3日留存,7日留存指标
具体实现:
insert overwrite table imei_retain_table_test pattition (day)
select
imei
,max(is_valid_act) as is_valid_act
,max(retain_day_2_act) as retain_day_2_act
,max(retain_day_3_act) as retain_day_3_act
,max(retain_day_7_act) as retain_day_7_act
,day
from
(
selet
imei
,max(is_valid_act) as is_valid_act
,max(if(datediff(t2.day - t1.day)=1 and t1.is_valid_act=1 and t2.is_valid_act =1,1,0) as retain_day_2_act
,max(if(datediff(t2.day - t1.day)=2 and t1.is_valid_act=1 and t2.is_valid_act =1,1,0) as retain_day_3_act
,max(if(datediff(t2.day - t1.day)=6 and t1.is_valid_act=1 and t2.is_valid_act =1,1,0) as retain_day_7_act
,day
from
(select
day
,imei
,max(is_valid_act) as is_valid_act
from imei_vaild_table_test
where day in ('${etl_date}','${etl_date_2}','${etl_date_3}','${etl_date_7}')
group by
day ,imei
) t1
left join
(select
day
,imei
,max(is_valid_act) as is_valid_act
from imei_vaild_table_test
where day in ('${etl_date}')
group by day,imei
) t2
group by day,imei
union all
select
imei
,is_valid_act
,retain_day_2_act
,retain_day_3_act
,retain_day_7_act
,day
from imei_retain_table_test
where day in ('${etl_date}','${etl_date_2}','${etl_date_3}','${etl_date_7}')
) tt
group by
imei
,day