一文搞懂连续问题

数据仓库_晨曦

于 2024-07-09 22:16:19 发布

阅读量683

点赞数 22

文章标签：大数据 sql 面试 hive spark

本文链接：https://blog.csdn.net/thenowaiting/article/details/140308171

版权

一、连续问题简述

我们写过各式各样的连续，今天我们做一个总结。

连续问题考察范围可能涉及到：开窗函数，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 字段。