用户连续操作(登录)数量(次数)最大的记录(用户)
DataStep SQL数据分析 2018-09-10
用户连续操作(登录)数量(次数)最大的记录(用户)背景
根据用户连续记录天数来计算的,求出用户在一段时间内最大的连续记录时间,例如在 2016-01-01 和 2016-01-28 之间,如果用户在3号和4号都记录了,那么连续记录天数为2,如果用户在6号-10号每日都记录了,那么最大连续记录天数为5,简而言之:求连续日期登录次数最大的用户;突破口
- 借助rownumber即可求解;
- 如果是连续的记录,那么 diffDate- rn 肯定是相同的!
- 核心的代码:logindate - row_number() over (partition by userid order by logindate) as groupday
- 实际上,上面这个查询,遇到一天登录多次的情况下,统计是不准确的,所以这时应该先去除某天的重复数据,才是正确的
衍生
不仅可以解决一段时间内的最大连续登陆天数,还能计算连续登陆3天,7天的用户
For example:
userID | logindate
1000
2014-01-10
1000
2014-01-11
1000
2014-02-01
1000
2014-02-02
1001
2014-02-01
1001
2014-02-02
1001
2014-02-03
1001
2014-02-04
1001
2014-02-05
1002
2014-02-01
1002
2014-02-03
1002
2014-02-05
.....
借助窗口函数row_number;
select userid, max(days)
from
(
select userid, groupday, count(*)
as days
from
(
select
userid, logindate - row_number() over (partition by userid order by logindate)
as groupday
from mytable
)
group
by userid, groupday
)
group
by userid
--having max(days)
>=
2
mysql 的查询query
select
userid, max(days)
from
(
select
userid, date_add(logindate, interval -row_number day)
as groupday, count(*)
as days
from
(
select
userid, logindate,
@row_num
:=
@row_num
+
1
as row_number
from mytable
cross join (select
@row_num
:=
0) r
order by userid, logindate
)
group
by userid, groupday
)
group
by userid
-- having max(days)
>=
2
结果
结果符合我们的预期,算是完整的答案了。参考
求连续操作(登录)数量(次数)最大的记录(用户) - 云+社区 - 腾讯云
mysql - How to wirte an extensible SQL to find the users who continuously login for n days - Stack Overflow