WITH raw_data AS (
    SELECT DISTINCT user_id, fdate
    FROM tb_dau
    WHERE fdate BETWEEN '2023-01-01' AND '2023-01-31'
),
grouped AS (
SELECT 
    user_id,
    DATE_SUB(fdate, INTERVAL 
        ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY fdate) DAY
    ) AS grp
FROM raw_data
)
SELECT 
    user_id,
    MAX(cnt) AS max_consec_days
FROM (
    SELECT user_id, COUNT(*) AS cnt
    FROM grouped
    GROUP BY user_id, grp
) t2
GROUP BY user_id
ORDER BY user_id

🧠 SQL 的完整思路解析（最长连续登录天数）

📌 题目目标从 tb_dau 表中，找出 2023年1月 每个用户的 最长连续登录天数（同一天多次登录只算1次）。

✅ SQL 结构总览

WITHraw_data AS ( ... ), -- 第1步：清洗数据grouped AS ( ... ) -- 第2步：打连续段标签SELECT ... -- 第3-4步：统计段长 + 取最大值

整体采用 分阶段 CTE 设计，层次清晰，符合“先准备 → 再分组 → 最后聚合”的分析范式。

🔍 分步详解

Step 1️⃣：raw_data —— 数据清洗与范围限定

WITH raw_data AS (SELECT DISTINCT user_id, fdate FROM tb_dauWHERE fdate BETWEEN '2023-01-01' AND '2023-01-31')

✅ 目的：

  • 去重：DISTINCT 确保同一用户同一天只计 1 次（避免重复登录干扰连续性判断）；
  • 时间裁剪：只保留 2023 年 1 月的数据，减少后续计算量；
  • 为分区裁剪做准备：如果 tb_dau 按 fdate 分区，此条件可跳过无关分区，极大提升性能。

💡 这是所有连续性分析的前提：干净、无重复、范围明确的时间序列。

Step 2️⃣：grouped —— 核心：生成“连续段标识”（grp）

grouped AS (SELECTuser_id,DATE_SUB(fdate, INTERVALROW_NUMBER() OVER (PARTITION BY user_id ORDER BY fdate) DAY) AS grpFROM raw_data)

🔑 核心思想（Gap-and-Island 模型）：对于任意一个连续递增的日期序列，日期 - 行号 的结果恒定。

📊 举例说明：user_id fdate ROW_NUMBER() DATE_SUB(fdate, rn) → grpA 2023-01-01 1 2022-12-31

A 2023-01-02 2 2022-12-31

A 2023-01-03 3 2022-12-31

A 2023-01-05 4 2023-01-01

A 2023-01-06 5 2023-01-01

→ 相同的 grp 值代表一个连续登录段。

⚙️ 关键细节：

  • PARTITION BY user_id：确保每个用户独立计算行号；
  • ORDER BY fdate：保证日期顺序正确；
  • DATE_SUB(..., INTERVAL rn DAY)：MySQL/StarRocks 安全的日期减法（避免 fdate - rn 语法错误）。

✅ 这一步是整个解法的灵魂，将“连续性识别”转化为“等值分组”问题。

Step 3️⃣ & 4️⃣：外层查询 —— 统计段长 + 取最大值

SELECTuser_id,MAX(cnt) AS max_consec_daysFROM (SELECT user_id, COUNT(*) AS cntFROM groupedGROUP BY user_id, grp -- 按用户 + 连续段分组) t2GROUP BY user_id -- 每个用户取最长段ORDER BY user_id;

📈 执行流程：

  1. 内层子查询： GROUP BY user_id, grp → 每个连续段一行；COUNT(*) → 该段的连续天数（即段长度）；
  2. 外层聚合： GROUP BY user_id + MAX(cnt) → 每个用户的最长连续天数；
  3. 最终排序：按 user_id 输出，便于阅读或下游处理。

💡 虽然多了一层子查询，但这是必须的——因为 grp 是中间计算字段，无法在单层 GROUP BY 中同时完成“分段计数”和“取最大值”。

✅ 优点 说明逻辑清晰 每个 CTE 职责单一，易于理解和维护

正确性高 处理了去重、排序、分组、聚合全流程

兼容性强 使用 DATE_SUB + INTERVAL，适配 MySQL / StarRocks

性能良好 提前过滤时间范围，减少数据量；窗口函数按用户分区，可并行

📝 总结：你的解题思维链

✅ 这就是标准的“连续区间识别”解法模板，可直接迁移到：

  • 最长连续签到
  • 最长连续活跃
  • 最长连续订单等场景

🎯 记忆口诀（基于你的代码）

一去重，二打标， 三分组，四取大。