侧边栏壁纸
博主头像
王一川博主等级

努力成为一个不会前端的全栈工程师

  • 累计撰写 70 篇文章
  • 累计创建 20 个标签
  • 累计收到 39 条评论

目 录CONTENT

文章目录

SQL:间隔连续问题

王一川
2021-11-19 / 2 评论 / 0 点赞 / 1,569 阅读 / 3,403 字
温馨提示:
本文最后更新于 2022-06-02,若内容或图片失效,请留言反馈。部分素材来自网络,若不小心影响到您的利益,请联系我们删除。

上一篇 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

0

评论区