一文搞懂连续问题
原创
一、连续问题简述
我们写过各式各样的连续，今天我们做一个总结。
连续问题考察范围可能涉及到：开窗函数，lag函数，row_number()，sum()over(order by) 等各种函数，以及相关数据处理技巧等，无论选取那种方法，连续问题都是相对较为复杂，考察综合能力的一类问题。
我们先思考一下什么是连续，如果给出一份数据，我们怎么才能"直接"查询出连续的内容呢？
- 是给出上一数据的日期？
- 还是给出与上一数据的差值？
- 还是给出每个是否与上一数据是否连续的标志字段？
都不是，而是特定分组下，将连续内容赋值相同的分组ID；
再次强调，是在特定分组下，将连续的内容赋值相同的分组ID；
解释：
特定分组：指的是连续的主体，例如判断用户是否连续登录，则这个特定分组是每个用户；
连续分组赋值相同的分组ID，是指添加一列为group_id, 连续的行分配相同的的ID值，该ID在不同的连续组之间不同。
面试题目是不能这样出的，因为这样描述起来太过复杂，很难描述清楚，并且描述完成之后基本就给出了答案，所以题目往往是要求求取连续之后的聚合信息，例如：查询最大连续天数、合并连续的数据、查询连续超过N的用户等等。
二、解题思路
我们以得到分组ID为界，将整个求解过程分为两部分，第一部分为判断连续条件，第二部分为连续之后的处理逻辑。基本上所有的题目都是在这两部分上增加逻辑来提升面试题目的复杂度。
计算连续分组赋值相同的分组ID
判断连续条件，有两种处理思路：1.双排序差值法；2.累积求和法；
1.双排序差值法
双排序差值法是指对一列连续数据得到排序1，对符合条件数据进行排序的到排序2，两列排序差值作为分组ID的方法.如果实际数据连续，则差值不变，如果间断则差值变化，从而保证每个连续段有唯一组id.
我们以腾讯大数据面试SQL-连续登陆超过N天的用户 为例，查询分组ID的逻辑SQL如下
select user_id,
login_date,
datediff(from_unixtime(unix_timestamp(login_date, 'yyyyMMdd'), 'yyyy-MM-dd'), '2022-01-01') as date_diff,
row_number() over (partition by user_id order by login_date asc) as row_num,
row_number() over (partition by user_id order by login_date asc) -
datediff(from_unixtime(unix_timestamp(login_date, 'yyyyMMdd'), 'yyyy-MM-dd'), '2022-01-01') as diff
from t_login_log;执行结果
+----------+-------------+------------+----------+-------+
| user_id | login_date | date_diff | row_num | diff |
+----------+-------------+------------+----------+-------+
| 0001 | 20220101 | 0 | 1 | 1 |
| 0001 | 20220102 | 1 | 2 | 1 |
| 0001 | 20220103 | 2 | 3 | 1 |
| 0001 | 20220104 | 3 | 4 | 1 |
| 0001 | 20220105 | 4 | 5 | 1 |
| 0001 | 20220107 | 6 | 6 | 0 |
| 0001 | 20220108 | 7 | 7 | 0 |
| 0001 | 20220109 | 8 | 8 | 0 |
| 0002 | 20220101 | 0 | 1 | 1 |
| 0002 | 20220102 | 1 | 2 | 1 |
| 0002 | 20220103 | 2 | 3 | 1 |
| 0002 | 20220107 | 6 | 4 | -2 |
| 0002 | 20220108 | 7 | 5 | -2 |
| 0003 | 20220107 | 6 | 1 | -5 |
| 0003 | 20220108 | 7 | 2 | -5 |
| 0003 | 20220109 | 8 | 3 | -5 |
+----------+-------------+------------+----------+-------+先计算出当前日期与一个固定日期（具体是什么日期无所谓，只是以一个固定日期为锚点）的差得到排序值1（date_diff），然后使用row_number()函数根据用户分组，按照登陆日期进行排序得到排序值2（row_num）,然后用两个排序值做差，谁减去谁都无所谓，差值正负无关。可以看出只要连续登陆的数据，diff值就是相同的。如果出现间断，则差值变化。diff即我们要的连续分组赋值相同的分组ID。
2.累积求和法
累积求和法，利用sum()over(order by) 函数特性，累加求和到当前行，如果值为0则累加和不变的特性。巧妙的将连续记录标记为0，不连续的数据标记为1，从而得到连续分组赋值相同的分组ID的目的。
以常见大数据面试SQL-连续点击三次用户为例，求取连续分组赋值相同的分组ID过程为：
select user_id,
click_time,
is_same_user,
sum(is_same_user) over (order by click_time asc) as sum_order
from (select user_id,
click_time,
case
when lag(user_id) over (order by click_time asc) is null then 0
when user_id = lag(user_id) over (order by click_time asc) then 0
else 1 end as is_same_user
from t_click_log) t执行结果
+----------+-------------+---------------+------------+
| user_id | click_time | is_same_user | sum_order |
+----------+-------------+---------------+------------+
| 1 | 1736337600 | 0 | 0 |
| 2 | 1736337670 | 1 | 1 |
| 1 | 1736337710 | 1 | 2 |
| 1 | 1736337715 | 0 | 2 |
| 1 | 1736337750 | 0 | 2 |
| 2 | 1736337760 | 1 | 3 |
| 3 | 1736337820 | 1 | 4 |
| 3 | 1736337840 | 0 | 4 |
| 3 | 1736337850 | 0 | 4 |
| 3 | 1736337910 | 0 | 4 |
| 4 | 1736337915 | 1 | 5 |
+----------+-------------+---------------+------------+题目是分析用户是否连续点击，使用lag函数得到上一行用户id与当前行用户id进行比较，如果相同则赋值为0，如果不相同则赋值为1，得到is_same_user列，然后对其使用sum(is_same_user)over(order by click_time),得到累积求和的结果。因为是同一个用户，则累积和不变，如果不同累积和+1,于是得到了连续分组赋值相同的分组ID sum_order 字段。
统计分析得到最终结果
在得到分组ID之后，根据分组ID与特定分组的列，进行分组，即可得到每个连续的段。然后使用聚合函数统计连续行数（连续天数）或者max或者min得到最大最小值等。或者对结果再次筛选得到对应的用户等。或者对数据进行拼接聚合等，总之后续添加逻辑即可。
三、详细题目拆解
普通连续问题
1. 拼多多大数据面试SQL-求连续段的最后一个数及每个连续段的个数
该题目是在得到连续分组ID 之后，增加了聚合逻辑的考察，考察max（）,count（）函数；
2. 腾讯大数据面试SQL-连续登陆超过N天的用户
该题目是在得到连续分组ID 之后，增加了聚合逻辑的考察，count()统计连续登陆天数，然后对统计结果进行筛选；
3. 常见大数据面试SQL-连续点击三次用户
该题目是在得到连续分组ID 之后，增加了聚合逻辑的考察，count()统计连续登陆天数，然后对统计结果进行筛选；
中等难度连续问题
1.常见大数据面试SQL-销售额连续3天增长的商户
该题目先是对连续条件增加要求，要求销售额增长。然后是在得到连续分组ID 之后，count()统计连续天数，并对统计结果进行筛选。
2. 百度大数据面试SQL-合并用户浏览行为
该题目先是对连续条件增加要求，要求与上一行数据时间差小于60S，得到连续分组ID 之后将数据进行合并处理。
3.腾讯大数据面试SQL-连续5天涨幅超过5%的股票
该题目是在限制要求每天涨幅的大于5%之后，得到连续分组ID，然后要求连续天数大于5天。
4. 京东大数据面试SQL-合并数据
该题目属于在得到分组ID之后，增加了数据进行拼接聚合的的要求。
高难度连续问题
1. 百度大数据面试SQL-连续签到领金币
该题目对连续条件判断上增加了难度，按月分组，在得到连续分组ID之后，计算出连续天数，还需要对天数进行重置，之后又对不同天数得到金币数量进行计算。 整体上十分的繁琐复杂。但是依旧是在得到连续分组ID前后增加一些处理逻辑。
2. 常见大数据面试SQL-各用户最长的连续登录天数-可间断
该题目在连续问题上增加难度，先要求去除重复数据，然后需要判断连续，间隔一天也属于连续。在得到连续分组ID之后 需要计算出连续登陆的最早和最晚日期，然后差值计算，还需要考虑到差值与登陆天数差天的细节。
总结
通过以上面试题目可以看出，只要找到连续分组ID，所以的题目都可以迎刃而解。重要的是判断每个条件属于判断连续的逻辑还是连续之后的处理逻辑就好。
原创声明：本文系作者授权腾讯云开发者社区发表，未经许可，不得转载。
如有侵权，请联系 cloudcommunity@tencent.com 删除。
原创声明：本文系作者授权腾讯云开发者社区发表，未经许可，不得转载。
如有侵权，请联系 cloudcommunity@tencent.com 删除。
腾讯云开发者
