题解 | 最长连续登录天数【“日期差与行号差”方法】

select 
    user_id,
    max(连续登录天数) as max_consec_days
from
(select
    user_id,初始日期,max(日期排序)-min(日期排序)+1 as 连续登录天数
from
(select 
    user_id,
    fdate,
    row_number() over(partition by user_id order by fdate) as 日期排序,
    date_sub(fdate, interval row_number() over(partition by user_id order by fdate) day) as 初始日期
from tb_dau
where fdate between '2023-01-01' and '2023-01-31') as t1
group by user_id,初始日期 ) as t2
group by user_id

我们来逐层解析这段 SQL 代码。这段 SQL 的核心目标是：计算每个用户在 2023-01-01 至 2023-01-31 期间的最大连续登录天数。

它通过三层嵌套查询，采用了一种在 SQL 中非常经典和巧妙的“日期差与行号差”的方法来识别连续序列。

我们从最内层的查询开始，一步步向外剖析。

核心思路预览

假设我们有一个用户的登录日期序列。如果我们能为每一个连续的登录日期分配一个唯一的“组ID”，那么问题就简化为：按用户和“组ID”分组，计算每个组的天数，然后取最大值。

例如：

这个 SQL 的精髓就在于如何用纯 SQL 逻辑生成这个“连续登录组”的标识。

第一层查询 (最内层，别名为 t1)

select 
    user_id,
    fdate,
    row_number() over(partition by user_id order by fdate) as 日期排序,
    date_sub(fdate, interval row_number() over(partition by user_id order by fdate) day) as 初始日期
from tb_dau
where fdate between '2023-01-01' and '2023-01-31'

这是整个逻辑的基石，也是最巧妙的一步。我们来分析它的每一列：

1. from tb_dau where fdate between '2023-01-01' and '2023-01-31':从 tb_dau (Daily Active User, 日活用户表) 中筛选出 2023年1月份的数据。
2. row_number() over(partition by user_id order by fdate) as 日期排序:row_number() 是一个窗口函数，它会为查询结果集中的每一行分配一个从1开始的唯一连续整数。over(...) 定义了窗口的范围和排序方式。partition by user_id: 将数据按 user_id 进行分组。窗口函数将在每个分组内独立计算。order by fdate: 在每个用户分组内，按照 fdate (登录日期) 从小到大进行排序。效果：对于每个用户，他的每一条登录记录都会按照日期顺序被打上一个序号。
3. date_sub(fdate, interval row_number(...) day) as 初始日期:date_sub(date, interval N day) 是一个日期函数，意为从 date 日期减去 N 天。这里的 N 就是我们上一步计算出的 日期排序 的序号。核心魔法：我们用实际登录日期减去它在用户登录序列中的序号。 如果登录是连续的，这个差值会是一个固定不变的日期。如果登录中断了，这个差值就会发生变化。

举个例子来理解：

假设用户 101 在2023年1月的登录情况如下：

观察结果：

• 1月1日、2日、3日这三天，初始日期 都是 2022-12-31。这三天被成功地识别为一个连续登录组。
• 1月5日、6日这两天，初始日期 都是 2023-01-01。它们被识别为另一个连续登录组。
• 初始日期 这个字段，完美地扮演了我们之前设想的“连续登录组ID”的角色！

第二层查询 (中间层，别名为 t2)

select
    user_id,
    max(日期排序)-min(日期排序)+1 as 连续登录天数
from
    (/* 第一层查询 t1 */) as t1
group by user_id, 初始日期

在得到了 t1 的结果后，我们已经有了每个用户的“连续登录组ID”（即 初始日期）。现在，我们要计算每个组的长度。

1. group by user_id, 初始日期:这是关键的分组步骤。我们告诉 SQL：请将 t1 的结果，按照 user_id 和 初始日期 的组合进行分组。根据我们上一步的例子，这会形成如下几个分组： (user_id=101, 初始日期=2022-12-31)(user_id=101, 初始日期=2023-01-01)
2. max(日期排序)-min(日期排序)+1 as 连续登录天数:在每个分组内，日期排序 是一个连续的整数序列（例如，第一组是1, 2, 3；第二组是4, 5）。max(日期排序) 得到该组最大的序号，min(日期排序) 得到最小的序号。它们的差值加1，就是这个连续序列的长度，也就是连续登录的天数。 第一组: 3 - 1 + 1 = 3 天第二组: 5 - 4 + 1 = 2 天

t2 查询的输出结果示例：

这个结果告诉我们，用户 101 在1月份有两段连续登录记录，一段3天，一段2天。

第三层查询 (最外层)

select
    user_id,
    max(连续登录天数) as max_consec_days
from
    (/* 第二层查询 t2 */) as t2
group by user_id

这是最后一步，目标是从每个用户的所有连续登录段中，找出最长的那一段。

1. group by user_id:按用户进行分组。
2. max(连续登录天数) as max_consec_days:在每个用户的分组内，我们使用 max() 聚合函数找出 连续登录天数 这一列的最大值。对于我们的例子，用户 101 的 max(3, 2) 就是 3。

最终结果示例：

这个结果就是我们想要的：每个用户在指定月份内的最大连续登录天数。

总结

这段 SQL 代码是一个非常经典且高效的实现。它的逻辑链条清晰：

1. 识别连续组：通过 row_number() 生成序号，再用 date_sub 将序号从日期中减去。如果日期是连续的，结果会是一个固定值，这个固定值就成了“连续登录组”的唯一标识。
2. 计算每组长度：对“用户+连续登录组”进行分组，利用组内序号的 max 和 min 计算出每个连续登录段的天数。
3. 找出最大值：在每个用户的所有连续登录段中，取天数最多的那个值，即为最终结果。

希望这个逐层解析能帮助你完全理解这段代码的精妙之处！