简析 SQL 分析题目——计算连续签到天数

SQL干货分享 2021-04-19

3076

（CSDN博主：写代码也要符合基本法）

今天小刘看到一道有趣的 SQL 数据分析题目，准备和大家分享一下

不能只让我一个人秃头！！！

一软件中有用户每日签到功能，现在要用 SQL 计算出上月中各用户的最后一次连续签到的天数（只有一天的，算作连续一天）

数据准备

首先我们准备一下五个用户在七月份的随机签到数据

CREATE TABLE demo_checkin_history AS
SELECT user_id, MAX(checkin_date) checkin_date
  FROM (SELECT ceil(LEVEL  31) user_id
              ,to_date('20200701', 'yyyymmdd') + 31 * dbms_random.value checkin_date
          FROM dual
        CONNECT BY LEVEL <= 31 * 5)
 GROUP BY user_id, trunc(checkin_date);

题目分析

用户每次签到的记录在表中是行行分开的，想要检查日期是否连续，让人首先想到的是跨行引用，将一行前前后后的数据都引用到一行上，以便比较计算

这正是这道题迷惑人的地方，因为用户实际连续签到的天数是未知的，从而我们不能确定要跨行引用多少次才够用，相反如果是想要找到某月连续签到天数不小于 N 天的用户，这个思路倒也是可行的

既然实际连续签到了几天是未知数，那么小刘想到的是利用递归查询，从用户最后一次签到日期开始，每向下一层去寻找前一天的记录，直到断签，此时 LEVEL 还正好就是这组连续签到的天数

递归查询的根结点，选取每个用户的签到日期最大的那些行

递归的规则，即为同一用户的签到日期相差一天

另外我们只需得到连续签到的天数，即每个树最大的 LEVEL 值，从而我们将只筛选叶子结点的数据即可

SELECT t.user_id
      ,t.checkin_date  checkin_date_from
      ,connect_by_root t.checkin_date checkin_date_to
      ,LEVEL           days
  FROM demo_checkin_history t
 WHERE connect_by_isleaf = 1
 START WITH t.checkin_date =
            (SELECT MAX(u.checkin_date)
               FROM demo_checkin_history u
              WHERE u.user_id = t.user_id)
CONNECT BY PRIOR trunc(t.checkin_date) = trunc(t.checkin_date) + 1
       AND PRIOR t.user_id = t.user_id
 ORDER BY days DESC, checkin_date_from DESC;

对于递归查询不太熟悉的小伙伴，可以点击这里回顾

加大难度

前面是找出每个用户上月最后一次连续签到的数据，现在比方说老（diao）板（mao）改主意了，他想看看每个用户上月的最大连续签到天数对比

新需求对于我们上例程序的最大挑战在于，根结点定位规则变化了，而且是由相对固定变成了相对不定

当然，简单粗暴的办法是去掉 START WITH 子句，任由数据库从每一行分别去递归，最后找到每个客户的最大的 LEVEL 值就完事儿

但考虑到实际业务场景中，如果只有五个用户，那公司也就倒闭了，需要这么大费周章去计算报表的，用户量没准儿是上万的，百万行量级的表去放飞自我的反复递归，带来的必定是老板的怒火性能的浪费

所以，延续上例的思路，我们这里应当调整根结点定位规则，想办法去找到每一个断签点，从这些点开始往前捯

此时，跨行引用有了用武之地

在每一个用户的分区内，按日期排序后，排在某行前一位的日期不与本行日期连续，则为断签点

例如我们观察一下用户 2 的签到日期，断签点已被圈出

观察数据我们发现，该用户不仅常常断签，而且坚持签到总是不超三日

这里的关键在于同样天数的连续签到，一个用户可能会有多组，那么我们就要在查询时考虑只取其中某一组，本例我们取最后一组

定位断签点为根结点的递归查询，我们可以这样来写

WITH checkin_his AS
 (SELECT t.user_id
        ,t.checkin_date
        ,lag(t.checkin_date) over(PARTITION BY t.user_id ORDER BY t.checkin_date DESC) next_checkin_date
    FROM demo_checkin_history t),
conn_rst AS
 (SELECT h.user_id
        ,h.checkin_date checkin_date_from
        ,connect_by_root h.checkin_date checkin_date_to
        ,LEVEL days
        ,MAX(LEVEL) over(PARTITION BY h.user_id) max_days
        ,MAX(h.checkin_date) keep(dense_rank FIRST ORDER BY LEVEL DESC) over(PARTITION BY h.user_id) last_checkin_date_from
    FROM checkin_his h
   WHERE connect_by_isleaf = 1
   START WITH h.next_checkin_date IS NULL
           OR trunc(h.next_checkin_date) - trunc(h.checkin_date) > 1
  CONNECT BY PRIOR h.user_id = h.user_id
         AND PRIOR trunc(h.checkin_date) = trunc(h.checkin_date) + 1)
SELECT c.user_id, c.checkin_date_from, c.checkin_date_to, c.days
  FROM conn_rst c
 WHERE c.days = c.max_days
   AND c.checkin_date_from = c.last_checkin_date_from
 ORDER BY days DESC, checkin_date_from DESC;

这里对于分析函数 lag 和 dense_rank 用法不太熟悉的小伙伴，请点击这里和这里回顾

转换思路

这个题目，无论是原题还是增加难度后的情况，事实上题干都可以回归到最基本的一个点——数（shǔ）数（shù）

SQL 中有一个很朴素的实现计数的功能 COUNT，但是我们看到题目后基本上第一个排除的就是用使用 COUNT 函数，因为使用 GROUP BY 分组的话，一定是要一致的值才能分到一组，而在本题中，连续签到对应的日期是变化的，所以不能分到一组，进而无法简单的计数

这就跟做选择题一样，往往我们首先排除的选项恰恰就是正确答案（猛男落泪）

此时此刻此情此景，我们需要运用一些数学课上学来的知识，数学中常用的一种思想就是

--= 化变元为常量 =--

这里我们仍以用户 2 的数据来举例

以日期为横轴，累积签到天数作为纵轴，可以得到下图折线

图中线形水平的地方，是用户没有签到的日子，它们在数据表中是不存在的，这里只在图中补充出来

而线形上升的地方，正是用户连续签到的阶段，由于每天最多签到一次，所以这些上升的地方斜率都是 1

这时我们再虚构一条折线，这条折线代表着某个勤奋的用户，坚持每天签到

不积跬步无以至千里，虚构用户其线形的斜率无非也是 1，但坚持的力量让用户 2 不能望其项背

喝完鸡汤，我们用纯粹的数学的眼光观察这两条折线，如果将紫线设为 f1，红线设为 f2，那么 f1-f2，就能得到黑线，设为 f3

这个黑线妙就妙在，红线水平的地方，它是上升的，而红线上升的地方，它却是水平的

（朋友们，我们休息的时候，正是别人和我们拉开差距的时候啊！）

至此，我们巧妙地构造了一个变元，与原来的变元相减后，得到了常量

换句话说，我们得以把连续的日期分到一组当中了！

第一步，我们需要用表中的数据，计算出每一行上的累积天数，可以使用

COUNT(1) OVER(PARTITION BY user_id ORDER BY checkin_date)

因为聚合函数在分析模式下默认的窗口正好是第一行到当前行，所以无需考虑窗口问题

不过，计算第一行到当前行有多少行，不正是计算排名吗？所以可以用排名函数来取代计数，尽管没有资料支持，但小刘觉得取名次比计数运算能让数据库少做些事情🤔

本题中同一用户的签到日期不会出现排名并列的情况，故选择 ROW_NUMBER函数即可

ROW_NUMBER() OVER(PARTITION BY user_id ORDER BY checkin_date)

第二步，实现构造函数 f1，由于其表达式为 f1(x)=x，换成人话就是日期

EXTRACT(DAY FROM checkin_date)

第三步，得到初代版本

WITH history AS
 (SELECT h.user_id
        ,h.checkin_date
        ,extract(DAY FROM h.checkin_date) f1
        ,row_number() over(PARTITION BY h.user_id ORDER BY h.checkin_date) f2
    FROM demo_checkin_history h),
group_rst AS
 (SELECT h.user_id
        ,MIN(h.checkin_date) checkin_date_from
        ,MAX(h.checkin_date) checkin_date_to
        ,COUNT(1) days
    FROM history h
   GROUP BY h.user_id, h.f1 - h.f2)
SELECT g.user_id
      ,MAX(g.checkin_date_from) keep(dense_rank FIRST ORDER BY days DESC) checkin_date_from
      ,MAX(g.checkin_date_to) keep(dense_rank FIRST ORDER BY days DESC) checkin_date_to
      ,MAX(g.days) max_days
  FROM group_rst g
 GROUP BY g.user_id
 ORDER BY max_days DESC, checkin_date_from DESC;

第四步，上例是基于仅统计一个月份内的前提，如果时间范围放大就会出问题，所以需要把构造函数 f1 再还原到签到日期本身

WITH history AS
 (SELECT h.user_id
        ,h.checkin_date
        ,row_number() over(PARTITION BY h.user_id ORDER BY h.checkin_date) delta
    FROM demo_checkin_history h),
group_rst AS
 (SELECT h.user_id
        ,MIN(h.checkin_date) checkin_date_from
        ,MAX(h.checkin_date) checkin_date_to
        ,COUNT(1) days
    FROM history h
   GROUP BY h.user_id, trunc(h.checkin_date) - h.delta)
SELECT g.user_id
      ,MAX(g.checkin_date_from) keep(dense_rank FIRST ORDER BY days DESC) checkin_date_from
      ,MAX(g.checkin_date_to) keep(dense_rank FIRST ORDER BY days DESC) checkin_date_to
      ,MAX(g.days) max_days
  FROM group_rst g
 GROUP BY g.user_id
 ORDER BY max_days DESC, checkin_date_from DESC;

对比总结

递归查询法思路相对更简洁清晰，构造变元法则略微烧脑了一丢丢

但是从性能角度出发，后者更优，尤其是在数据量较大的情形下，避免递归是很必要的

今天的脱发就到这里吧，相信各位看官各位大佬还有更好的方法解决这个问题，敬请私信赐教！

数据库

文章转载自SQL干货分享，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

简析 SQL 分析题目——计算连续签到天数

评论