『SQL实战』高频考题之连续问题，2种解法全解析！

数据攻略 2024-08-21

327

点击上方蓝色『数据攻略』关注+星标~

数据分析求职干货不错过

哈喽大家好，我是数据攻略的六哥~
数据分析笔面试关于sql的考核
经常会遇到这类 「连续问题」，比如：

连续登录/活跃/连续复购N天用户
最长连续签到用户
求出特定条件下的连续用户数
...

这类问题属于是日常工作场景中
常见又重要的问题，且解题构思较巧妙
所以也是面试官喜欢出题的类型~

本篇就来以实际面试sql题为例
唠一唠常见的2种解法思路
争取让大家吃透此类「连续问题」
文末有考点总结和自测题，可举一反三

------正文手动分割线------

本文结构速览：

一、问题背景（真题）

二、解题思路（2种）

三、参考代码（2种）

四、考点总结+自测

一、问题背景

假设你现在负责支付宝会员业务，为提高用户粘性，产品上线了新一版签到领积分兑换的功能，现希望了解新功能的用户使用情况，希望得知连续签到3天及以上的用户ID和对应符合条件的连续签到起始日期？

表描述如下：
用户签到日志表：user_sign_log 第一列是用户id，第二列是用户签到日期

user_id	sign_in_time
1001	2024-01-01 05:19:20
1002	2024-01-01 13:49:40
1003	2024-01-01 15:19:10
1002	2024-01-02 14:29:50
1001	2024-01-03 13:29:30
1002	2024-01-03 17:19:20

说明：签到功能一天仅需要签到一次

输出表结构如下：

user_id
start_sign_dt
continous_days

二、解题思路

说到底，这类问题难倒一票人的本质：是因为数据库非常不擅长做行之间的操作，更多是在列上做“连接”操作，所以就需要大家用巧思来用sql解这类连续问题

思路一

这类解法是最常规、用的最多的一种方式：

首先，明确什么是连续？映射到数据上的表现形式是什么？比如：

如果用户1.1号→1.2号→1.3号都签到，本质就是等差数列，间隔数是一定的，都是1
如果用户不连续签到，1.1号→1.3号→1.10号，日期之间的间隔数则不一定，这个例子中就是2、7
🚩有了这个例子，我们就明确了如何区分连续，就是 等差数列递增，有固定间隔

其次，思考如何构造等差的公差，也就是固定的间隔？——此时，就需要有一个同样等差递增的列b，自然联想到了排序函数：利用row_number()函数来构造b，会有两种情况：（可以联合看下面的图理解↓）

如果签到日期sign_in_time连续，新增的b列值也连续的，大家一起递增，则自然作差相减的值△delta_group即为一个固定的值！，正好是想要的结果✅
如果签到日期sign_in_time不连续，但新增的b列值是连续的，则作差相减的值△delta_group不是一个固定的值，就不是我们想要统计的结果了❌

然后，连续的记录已经标记出来了，那我们要如何区分不同的连续签到区间？本质就是分组标志是什么？答案就是公差△delta_group是一致的，这个是共同点，可以利用此做计数分组！（可以联合看下面的图理解↓）
最后，做筛选：对应分组中筛选出计数≥3即可，即为连续3天及以上的用户即可

思路二

这类思路，其实是理解透彻题意目的情况下，结合数据库记录本质来巧妙解决问题，前面说过数据库做行上操作不擅长，如果需要跨行筛选数据，则需要用偏移函数lead/lag()来实现，用法详见👉讲懂高频Hive：窗口函数（二）

首先，理解题目本意，并结合偏移函数来思考符合条件的记录特征：

如果用户1.1号→1.2号→1.3号都签到，符合连续签到3天及以上，则从用户当前签到日期dt1，如1.1号，往后lead(sign_in_time,2)寻找后2条的实际签到记录dt2，如果连续，理论上的日期应该为1.3号，因为1.3和1.1作差=2天，从这个case发现可以定位到1.3，所以符合连续条件✅
如果用户签到记录为1.1号→1.3号→1.10号，不连续签到，则往后lead(sign_in_time,2)寻找后2条的实际签到记录dt2，理论日期值应该是1.3，但实际偏移2条记录的日期dt2是1.10，1.10和1.1作差=9天，＞2天，实际差值天数与理论差值天数不符，则明显不符合连续条件❌
🚩有了这个例子，我们就明确了如何识别符号条件的记录特征，就是实际差值天数=理论差值天数

然后，我们知道了符合条件的数据记录特征，即对签到时间做升序排序后，利用lead()函数获取往后的第2条签到时间dt2，用当前签到时间dt1与其作差，得到相差天数△days_diff
最后，做筛选就好了，根据题意，筛选实际差值天数△days_diff为2天的用户记录，即为连续签到3天及以上的用户

三、参考代码

思路一

步骤0：以防万一先对日志GROUPBY去重，数据粒度为签到日期sign_in_dt+用户id
步骤1：利用ROW_NUMBER() 构造新等差递增列b，做排序：按照用户id作为分组，以签到日期升序排序
步骤2：利用DATE_SUB原签到日期与新递增列b作差，得到差值日期delta_group（注意，这里如果连续，得到的就是连续签到的最早日期前一日）
步骤3：利用delta_group为分组标志，做COUNT计数分组，筛选出连续签到3天及以上的用户和对应连续签到起始日期

--步骤3：计数分组，筛选出连续签到3天及以上的用户和对应连续签到起始日期
SELECT  user_id
       ,DATE_ADD(delta_group,1) AS start_sign_dt
       ,COUNT(1)                AS continous_days
FROM
( --步骤2：原签到日期与新递增列b作差，得到差值日期（注意，这里如果连续，得到的就是连续签到的最早日期前一日） 
 SELECT  user_id
        ,sign_in_dt
        ,b
        ,DATE_SUB(sign_in_dt,binint(b)) AS delta_group
 FROM
 ( --步骤1：构造新等差递增列b，做排序：按照用户id作为分组，以签到日期升序排序 
  SELECT  *
         ,ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY  sign_in_dt) AS b
  FROM
  ( --步骤0：以防万一先对日志去重，数据粒度为签到日期sign_in_dt+用户id 
   SELECT  user_id
          ,TO_DATE(sign_in_time) AS sign_in_dt
   FROM user_sign_log
   GROUP BY  user_id
            ,TO_DATE(sign_in_time)
  )t1
 )t2
)t3
GROUP BY  continous_days
         ,DATE_ADD(delta_group,1)
HAVING COUNT(1) >= 3

思路二

步骤0：以防万一先对日志GROUPBY去重，数据粒度为签到日期sign_in_dt+用户id
步骤1：利用LEAD() 每个用户签到日期往后偏移2条找实际签到日期记录dt2：按照用户id作为分组，以签到日期升序排序
步骤2：利用DATEDIFF() 原签到日期dt1与偏移后2条的实际签到日期dt2作差，得到差值间隔天数days_diff
步骤3：利用WHERE筛选符合条件，即理论差值=2天的用户，即为连续签到3天及以上的用户


--步骤3：筛选差值符合理论差值=2天的用户，即为连续签到3天及以上的用户
SELECT  user_id
FROM
( --步骤2：原签到日期dt1与偏移后2条的实际签到日期dt2作差，得到差值间隔天数days_diff 
 SELECT  user_id
        ,sign_in_dt
        ,after2_sign_dt
        ,DATEDIFF(after2_sign_dt,sign_in_dt) AS days_diff
 FROM
 ( --步骤1：每个用户签到日期往后偏移2条找实际签到日期记录dt2：按照用户id作为分组，以签到日期升序排序 
  SELECT  *
         ,LEAD(sign_in_dt,2) OVER (PARTITION BY user_id ORDER BY  sign_in_dt) AS after2_sign_dt
  FROM
  ( --步骤0：以防万一先对日志去重，数据粒度为签到日期sign_in_dt+用户id 
   SELECT  user_id
          ,TO_DATE(sign_in_time) AS sign_in_dt
   FROM user_sign_log
   GROUP BY  user_id
            ,TO_DATE(sign_in_time)
  )t1
 )t2
)t3
WHERE days_diff = 2
GROUP BY user_id 
;