|
http://www.taoyouhuipin.top/
查询7天连续登陆用户这个问题很经典，解决方法也有很多，这里我讲一下我的处理方法，希望对大家有帮助。
具体思路1: 因为是要求解连续登陆的，那么如果对日期（需要先对日期distinct 去重）进行排序的话，可以知道 日期是连续递增，序号也是，例如：
| date | 序号 | | 2020-11-15 | N | | 2020-11-16 /2020-11-15 +1 day | N+1 | | 2020-11-17 /2020-11-15 +1 day | N+2 | | 2020-11-18 /2020-11-15 +1 day | N+3 | | ...... | ...... | | | | | | |
当我们用date - 序号天数的时候，如果存在连续登陆的日期，那么得出来的日期，应该是相同的
new_date = 2020-11-15 减去 N = 2020-11-16 减去 （N+1）= 2020-11-17 减去 （N+2）= 2020-11-18 减去 （N+3）......
我们再对new_date 分组 ，则可知道连续登陆的有多少次
select
new_date ,count(1)
from (
2020-11-15 减去 N = 2020-11-16 减去 （N+1）= 2020-11-17 减去 （N+2）= 2020-11-18 减去 （N+3）......
)
;
综上思路，我们再来理解解答题目：
with orde as (
select '2019-12-28' as date, 1 id
union all
select '2019-12-29' as date, 1 id
union all
select '2019-12-30' as date, 1 id
union all
select '2019-12-31' as date, 1 id
union all
select '2020-01-01' as date, 1 id
union all
select '2020-01-02' as date, 1 id
union all
select '2020-01-03' as date, 1 id
union all
select '2020-01-05' as date, 1 id
union all
select '2020-01-06' as date, 1 id
union all
select '2020-01-07' as date, 1 id
union all
select '2020-01-08' as date, 1 id
union all
select '2020-01-01' as date, 2 id
union all
select '2020-01-01' as date, 2 id
union all
select '2020-01-11' as date, 2 id
union all
select '2020-01-12' as date, 2 id
union all
select '2020-01-13' as date, 2 id
union all
select '2020-01-14' as date, 2 id
union all
select '2020-01-15' as date, 2 id
union all
select '2020-01-16' as date, 2 id
union all
select '2020-01-17' as date, 2 id
union all
select '2020-01-18' as date, 2 id
)
select id,count(1)
from
(
select *,date_sub(date(日期) ,interval cum day) 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(1)>7;
ps: 题目只是要求我们再1~30日之间计算，而上述脚本亦可计算 跨月、跨年，如果跨月跨年不算符合要求，可以根据下列逻辑计算（直接用日期 减去 数值，会有隐式数据类型转换）：
with orde as (
select '2019-12-28' as date, 1 id
union all
select '2019-12-29' as date, 1 id
union all
select '2019-12-30' as date, 1 id
union all
select '2019-12-31' as date, 1 id
union all
select '2020-01-01' as date, 1 id
union all
select '2020-01-02' as date, 1 id
union all
select '2020-01-03' as date, 1 id
union all
select '2020-01-05' as date, 1 id
union all
select '2020-01-06' as date, 1 id
union all
select '2020-01-07' as date, 1 id
union all
select '2020-01-08' as date, 1 id
union all
select '2020-01-01' as date, 2 id
union all
select '2020-01-01' as date, 2 id
union all
select '2020-01-11' as date, 2 id
union all
select '2020-01-12' as date, 2 id
union all
select '2020-01-13' as date, 2 id
union all
select '2020-01-14' as date, 2 id
union all
select '2020-01-15' as date, 2 id
union all
select '2020-01-16' as date, 2 id
union all
select '2020-01-17' as date, 2 id
union all
select '2020-01-18' as date, 2 id
)
select id,count(1)
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(1)>=7;
 | 
转播
