源数据
dates:登录时间
author_id:登录的作者id
/*
最大持续更新天数
1.按作者分组,日期排序后,给行编号
*/
SELECT
author_id,
dates,
ROW_NUMBER() over (PARTITION BY author_id ORDER BY dates) rn
执行结果:
如果是连续登录,减去连续的行号,应该得到的是同一个日期
SELECT
author_id,
dates,
rn,
DATE_SUB(dates,INTERVAL rn DAY) sub_dates #减去指定间隔的时间
FROM
(SELECT
author_id,
dates,
ROW_NUMBER() over (PARTITION BY author_id ORDER BY dates) rn
FROM
temp_author_act)a
执行结果:
/*
最大持续更新天数
按作者和辅助日期列分组,一样的辅助日期,就是连续登录的几天
*/
SELECT
distinct author_id,
count(sub_dates) login_days
FROM
(SELECT
author_id,
dates,
rn,
DATE_SUB(dates,INTERVAL rn DAY) sub_dates
FROM
(SELECT
author_id,
dates,
ROW_NUMBER() over (PARTITION BY author_id ORDER BY dates) rn
FROM
temp_author_act)a)b
GROUP BY
author_id,
sub_dates;
执行结果:
所有的连续数据,都可以用row_number()来处理~~~