需求: 查询每日近7日的用户数
说明: 每日近7日的用户数 意思是" 每天的日期往前推7日(如: 时间区间为 create_date between date_sub('2020-12-15', 7) and '2020-12-15' ) "
相关参考信息:
参考1: count(distinct ) over(partition by order by)替换成size(collect_set() over(partition by order by))
链接1: https://blog.csdn.net/kaaosidao/article/details/82908506
那位网友有好的方法, 求教?
搜索到的信息: 思路如下
1. 借助 size(ccollect_set() over(partition by xxx))
-- 获取每日近7日的用户数
select regdate
,size(collect_set(case when regdate between date_sub(regdate,7) and regdate then device_id end)
over(partition by regdate, date_sub(regdate,7)) ) as cnt
from (
select substr(regdate,1,10) as regdate
,device_id
from ce_user
) t1