某APP活跃用户,字段uid和dt两个字段,求连续出勤天数大于等于三天的用户和用户最大登录天数
求连续出勤天数,使用row_number()方法:
SELECT
a.uid,
DATE_SUB(a.dt,INTERVAL a.rn DAY),COUNT(1)
FROM
(SELECT
uid
,dt
,row_number() over(PARTITION BY uid ORDER BY dt) AS rn
FROM active
) a
GROUP BY a.uid,DATE_SUB(a.dt,INTERVAL a.rn DAY)
HAVING COUNT(1)>=3
思路:求连续登录数
求连续出勤的时候,我们可以发现每个日期是相差一天的,当我们使用
row_number() over(PARTITION BY uid ORDER BY dt) AS rn 的时候,使用DATE_SUB(a.dt,INTERVAL a.rn DAY),即连续出勤减去递增的数字得到的结果是相等的,如果非连续的状态得到的结果是不等的
这时候再使用合并的累加的方法就可以算出最终结果
GROUP BY a.uid,DATE_SUB(a.dt,INTERVAL a.rn DAY)
HAVING COUNT(1)>=3
思路:求最大活跃数
当我求出连续活跃天数的方法后,用户的最大活跃天数就相对轻松了,只要在活跃天数的累加后,追加max函数:
SELECT
b.uid
,MAX(b.ct) FROM
(
SELECT
a.uid
,DATE_SUB(a.dt,INTERVAL a.rn DAY),COUNT(1) AS ct
FROM
(SELECT uid
,dt
,row_number() over(PARTITION BY uid ORDER BY dt) AS rn
FROM active
) a
GROUP BY a.uid,DATE_SUB(a.dt,INTERVAL a.rn DAY)
)b
GROUP BY uid
结果: