一文解决SQL连续问题！

一文解决SQL连续问题！

计算连续活跃

连续活跃或者登录的计算是数据分析业务场景中非常经典和常见的问题，而且涉及到的解题思路比较巧妙，所以也是面试笔试中非常喜欢考查的知识点~

记录用户活跃时间信息的表logins如下：

+--------+---------------+
| userid | activity_date |
+--------+---------------+
|  10001 | 2021-01-01    |
|  10001 | 2021-01-01    |
|  10001 | 2021-01-02    |
|  10001 | 2021-01-02    |
|  10001 | 2021-01-03    |
|  10002 | 2021-01-02    |
|  10002 | 2021-01-02    |
|  10002 | 2021-01-03    |
|  10002 | 2021-01-04    |
|  10002 | 2021-01-04    |
+--------+---------------+

需求：计算连续活跃2天及以上的用户。

步骤1：用户活跃日期去重

因为一个用户同一天可能活跃多次，我们只需要知道用户在某天是否活跃即可，所以需要对「用户id+活跃日期去重」。

select 
  DISTINCT activity_date active_day,
  userid
from logins;
-- 或者
select 
  userid,activity_date active_day
from logins
group by 1,2;

+--------+---------------+
| userid | activity_date |
+--------+---------------+
|  10001 | 2021-01-01    |
|  10001 | 2021-01-02    |
|  10001 | 2021-01-03    |
|  10002 | 2021-01-02    |
|  10002 | 2021-01-03    |
|  10002 | 2021-01-04    |
+--------+---------------+

步骤2：窗口函数按日期排序

有了第1步「去重后」的结果，我们可以用row_number() 函数对其进行开窗（其他的也行），以userid分组（partition by userid），日期升序排序（order by active_day），得到每个用户按照日期升序的排名。

select *,
row_number() over(PARTITION by userid
order by active_day) as rn
from
(select userid,activity_date as active_day
from logins
group by 1,2) tmp;

+--------+------------+----+
| userid | active_day | rn |
+--------+------------+----+
|  10001 | 2021-01-01 |  1 |
|  10001 | 2021-01-02 |  2 |
|  10001 | 2021-01-03 |  3 |
|  10002 | 2021-01-02 |  1 |
|  10002 | 2021-01-03 |  2 |
|  10002 | 2021-01-04 |  3 |
+--------+------------+----+

步骤3：序减定组

【序减定组】是啥意思？因为我们用row_number排序之后的名次是连续的，那么如果恰巧日期也是连续的，日期和排序「差值」不就是一个固定常数了吗？这样产生的每个不同的常数就代表不同的连续日期的组合！

我们使用date_sub函数将当前日期active_day和当前的排序值rn相减，得到一个新的列，命名为day_group

with t as
(
select *,
row_number() over(PARTITION by userid
order by active_day) as rn
from
(select 
  userid,activity_date as active_day
from logins
group by 1,2) tmp1)



select 
 t.*, date_sub(active_day, interval rn DAY) as day_group
from t

+--------+------------+----+------------+
| userid | active_day | rn | day_group  |
+--------+------------+----+------------+
|  10001 | 2021-01-01 |  1 | 2020-12-31 |
|  10001 | 2021-01-02 |  2 | 2020-12-31 |
|  10001 | 2021-01-03 |  3 | 2020-12-31 |
|  10002 | 2021-01-02 |  1 | 2021-01-01 |
|  10002 | 2021-01-03 |  2 | 2021-01-01 |
|  10002 | 2021-01-04 |  3 | 2021-01-01 |
+--------+------------+----+------------+

步骤4：根据分组列计算连续天数

select 
userid, day_group, 
count(*) as continous_days
from
(select 
 t.*, 
  date_sub(active_day, interval rn DAY) 
  as day_group
from t) tmp
group by day_group, userid
having count(*) >= 2

+--------+------------+----------------+
| userid | day_group  | continous_days |
+--------+------------+----------------+
|  10001 | 2020-12-31 |              3 |
|  10002 | 2021-01-01 |              3 |
+--------+------------+----------------+

到这里结果就出来了，userid为10001和10002的用户都连续活跃了3天，也就是符合我们条件的用户。

本文由 mdnice 多平台发布