查询 7 天连续登陆用户这个问题很经典, 解决方法也有很多, 这里我讲一下笔者的方法, 希望对大家有帮助.
具体思路:
1, 因为每天用户登录次数可能不止一次, 所以需要先将用户每天的登录日期去重.
2, 再用 row_number() over(partition by _ order by _) 函数将用户 id 分组, 按照登陆时间进行排序.
3, 计算登录日期减去第二步骤得到的结果值, 用户连续登陆情况下, 每次相减的结果都相同.
4, 按照 id 和日期分组并求和, 筛选大于等于 7 的即为连续 7 天登陆的用户.
表信息如下图
第一步: 用户登录日期去重
select DISTINCT date(date) as 日期, id from orde;
结果为:
第二步: 用 row_number() over() 函数计数
select *,row_number() over(PARTITION by id order by 日期) as cum
from (select DISTINCT date(date) as 日期, id from orde)a;
结果为:
第三步: 日期减去计数值得到结果
select *,date(日期)-cum as 结果 from
(select *,row_number() over(PARTITION by id order by 日期) as cum from
(select DISTINCT date(date) as 日期, id from orde)a)b;
结果:
第四步: 根据 id 和结果分组并计算总和, 大于等于 7 的即为连续登陆 7 天的用户
select id,count(*) from
(select *,date(日期)-cum as 结果 from
(select *,row_number() over(PARTITION by id order by 日期) as cum from
(select DISTINCT date(date) as 日期, id from orde)a)b)c GROUP BY id, 结果 having count(*)>=7;
结果为:
用了多次嵌套查询, 最终得到我们需要的结果.
来源: http://www.bubuko.com/infodetail-3324195.html