上一篇 SQL 问题是连续登录问题，比如连续签到七天送 VIP，一刀999，但是部分玩家因为各种各样的原因导致漏签了一天，因此良心策划说话了：连续登录的天数可以间断一天，也就是说玩家在1，3，5，6天登录也算连续登录6天。求每个用户最大的连续登录天数
玩家登录数据如下：
id dt
1001 2021-12-12
1002 2021-12-12
1001 2021-12-13
1001 2021-12-14
1001 2021-12-16
1002 2021-12-16
1001 2021-12-19
1002 2021-12-17
1001 2021-12-20
结果展示
首先我们手动的计算一下，把用户分一下组并按照时间排序
id dt
1001 2021-12-12
1001 2021-12-13
1001 2021-12-14
1001 2021-12-16
1001 2021-12-19
1001 2021-12-20
1002 2021-12-12
1002 2021-12-16
1002 2021-12-17
发现 1001 最大连续登录 5 天，1002 最大连续登录 2 天
思路分析
间断连续应该是连续问题的变种，那么尝试用连续问题的思路看能不能解决，首先生成一个连续的序列(这里只看1001)，然后两列相减
id dt num sub
1001,2021-12-12,1,2021-12-11
1001,2021-12-13,2,2021-12-11
1001,2021-12-14,3,2021-12-11
1001,2021-12-16,4,2021-12-12
1001,2021-12-19,5,2021-12-14
1001,2021-12-20,6,2021-12-14
12 - 14 连续登录肯定没有问题，16 其实也需要放在 12 -14 这个组里面，然后组内的最大值-最小值 + 1 就是最大连续登录问题了！现在的问题是如何把 16 也放在一起？sub 字段间隔一天的值在 sub 字段里是连续的。这种思路貌似是可行的但是如果间断两天也算连续呢，那是不是还要再生成一个连续序列？是时候换一个思路了(废话到此结束)
上面的分析思路是可取的，就是如何把12，13，14，16分到一个组，19，20分到一个组，这类问题就是一个分组问题，但不是简单的 group，规则是只要与上一行数据的差值在 2 以内就是一个组，也就是说我们需要首先取到上一行数据
id dt lagdt sub
1001,2021-12-12,1970-01-01,xxx(很大的数)
1001,2021-12-13,2021-12-12,1
1001,2021-12-14,2021-12-13,1
1001,2021-12-16,2021-12-14,2
1001,2021-12-19,2021-12-16,3
1001,2021-12-20,2021-12-19,1
然后我们根据 sub 字段的值给对应的分组编号如下：
id dt lagdt sub groupid
1001,2021-12-12,1970-01-01,xxx,1
1001,2021-12-13,2021-12-12,1 ,1
1001,2021-12-14,2021-12-13,1 ,1
1001,2021-12-16,2021-12-14,2 ,1
1001,2021-12-19,2021-12-16,3 ,2
1001,2021-12-20,2021-12-19,1 ,2
现在的问题是我们如何把 sub 列转换成 groupid 列之后按照 groupid、id 进行分组即可，我这里的思路是如果 sub > 2 就把 sub 变成 0 否则就是 1，那最后的结果如下
id dt lagdt sub fsub
1001,2021-12-12,1970-01-01,xxx,1
1001,2021-12-13,2021-12-12,1 ,0
1001,2021-12-14,2021-12-13,1 ,0
1001,2021-12-16,2021-12-14,2 ,0
1001,2021-12-19,2021-12-16,3 ,1
1001,2021-12-20,2021-12-19,1 ,0
最后按照 fsub 按列进行开窗累加，其实就是开窗累加到当前行即可，下面是每一步的sql实现
SQL 实现
获取上一行数据，关于上一行下一行数据用函数 lag 和 lead
select id, ts, lag(ts, 1, '1970-01-01') over (partition by id order by ts) lagts
from group_problem t;
-- 结果如下
1001,2021-12-12,1970-01-01
1001,2021-12-13,2021-12-12
1001,2021-12-14,2021-12-13
1001,2021-12-16,2021-12-14
1001,2021-12-19,2021-12-16
1001,2021-12-20,2021-12-19
1002,2021-12-12,1970-01-01
1002,2021-12-16,2021-12-12
1002,2021-12-17,2021-12-16
获取 dt 和 lagts 的差值
select id, ts, lagts, datediff(ts, lagts) diffts
from (
select id, ts, lag(ts, 1, '1970-01-01') over (partition by id order by ts) lagts
from group_problem t
) t1;
-- 结果如下
1001,2021-12-12,1970-01-01,18973
1001,2021-12-13,2021-12-12,1
1001,2021-12-14,2021-12-13,1
1001,2021-12-16,2021-12-14,2
1001,2021-12-19,2021-12-16,3
1001,2021-12-20,2021-12-19,1
1002,2021-12-12,1970-01-01,18973
1002,2021-12-16,2021-12-12,4
1002,2021-12-17,2021-12-16,1
如果 diffts > 2 转换成 1 否则是 0，然后开窗累加到当前行即可
select id, ts, lagts, diffts, sum(if(diffts > 2, 1, 0)) over (partition by id order by ts) sumts
from (
select id, ts, lagts, datediff(ts, lagts) diffts
from (
select id, ts, lag(ts, 1, '1970-01-01') over (partition by id order by ts) lagts
from group_problem t
) t1
) t2;
-- 结果如下
1001,2021-12-12,1970-01-01,18973,1
1001,2021-12-13,2021-12-12,1,1
1001,2021-12-14,2021-12-13,1,1
1001,2021-12-16,2021-12-14,2,1
1001,2021-12-19,2021-12-16,3,2
1001,2021-12-20,2021-12-19,1,2
1002,2021-12-12,1970-01-01,18973,1
1002,2021-12-16,2021-12-12,4,2
1002,2021-12-17,2021-12-16,1,2
最后统计即可
select id, max(maxlogin) as maxlogin
from (
select id, sumdiff, datediff(max(ts), min(ts)) + 1 maxlogin
from (
select id,
ts,
lagts,
diffts,
sum(if(diffts > 2, 1, 0)) over (partition by id order by ts) sumdiff
from (
select id, ts, lagts, datediff(ts, lagts) diffts
from (
select id,
ts,
lag(ts, 1, '1970-01-01') over (partition by id order by ts) lagts
from group_problem t
) t1
) t2
) t3
group by id, sumdiff
) t4
group by id;
-- 结果如下
1001,5
1002,2
end
评论区