现场写一个『连续登录 3 天的用户』SQL——窗口函数面试的头牌题

-- 原始表：user_login_log
-- 字段：uid BIGINT, login_time TIMESTAMP
 
-- Step 1: 按天去重（同一天多次登录算一次）
WITH daily_login AS (
  SELECT uid, DATE(login_time) AS dt
  FROM user_login_log
  GROUP BY uid, DATE(login_time)
),
-- Step 2: 算 ROW_NUMBER 和日期差
ranked AS (
  SELECT uid, dt,
    ROW_NUMBER() OVER (PARTITION BY uid ORDER BY dt) AS rn,
    -- 关键一步：日期减 rn，同一段连续的 grp 相同
    DATE_SUB(dt, INTERVAL rn DAY) AS grp
  FROM daily_login
),
-- Step 3: 按 grp 分组，数连续天数
grouped AS (
  SELECT uid, grp, COUNT(*) AS consecutive_days
  FROM ranked
  GROUP BY uid, grp
)
-- Step 4: 连续 >= 3 天的用户（去重后只返回 uid）
SELECT DISTINCT uid
FROM grouped
WHERE consecutive_days >= 3;

dt       rn   dt - rn（grp）
3/1      1    3/1 - 1 = 2/28
3/2      2    3/2 - 2 = 2/28  ← 和上面相同
3/3      3    3/3 - 3 = 2/28  ← 和上面相同
3/5      4    3/5 - 4 = 3/1   ← 不同了，说明断了
 
连续的 3 天 grp 都是 2/28，断开后 grp 变了。
按 grp 分组，COUNT(*) = 连续天数。

-- 在上面 grouped 的基础上，每个用户取最大值
SELECT uid, MAX(consecutive_days) AS max_streak
FROM (
  SELECT uid, grp, COUNT(*) AS consecutive_days
  FROM (
    SELECT uid, dt,
      DATE_SUB(dt, INTERVAL ROW_NUMBER() OVER (PARTITION BY uid ORDER BY dt) DAY) AS grp
    FROM (SELECT DISTINCT uid, DATE(login_time) AS dt FROM user_login_log) x
  ) y
  GROUP BY uid, grp
) z
GROUP BY uid;

段落三：变体二——连续 N 天允许断 1 天

这是第二个常见变体，业务侧经常这么要求：『连续 7 天只缺 1 天也算活跃』。思路是：把『允许断 1 天』转换成『日期差 <= 2 天』。用 LAG 看前一条记录，差 <= 2 就归为同一段。

WITH daily AS (
  SELECT DISTINCT uid, DATE(login_time) AS dt FROM user_login_log
),
with_gap AS (
  SELECT uid, dt,
    LAG(dt) OVER (PARTITION BY uid ORDER BY dt) AS prev_dt,
    -- 如果和前一天间隔 > 2，算一个新段的开始
    CASE WHEN DATEDIFF(dt, LAG(dt) OVER (PARTITION BY uid ORDER BY dt)) > 2
         THEN 1 ELSE 0 END AS is_new_seg
  FROM daily
),
segmented AS (
  -- 累加 is_new_seg 作为段编号
  SELECT uid, dt,
    SUM(is_new_seg) OVER (PARTITION BY uid ORDER BY dt) AS seg_id
  FROM with_gap
)
SELECT uid, seg_id, COUNT(*) AS days_in_seg
FROM segmented GROUP BY uid, seg_id
HAVING COUNT(*) >= 7;

段落四：变体三——计算连续登录的起止日期

不止要天数，还要起止日期。在 grouped 基础上加 MIN/MAX。

SELECT uid,
  MIN(dt) AS streak_start,
  MAX(dt) AS streak_end,
  COUNT(*) AS streak_days
FROM (
  SELECT uid, dt,
    DATE_SUB(dt, INTERVAL ROW_NUMBER() OVER (PARTITION BY uid ORDER BY dt) DAY) AS grp
  FROM (SELECT DISTINCT uid, DATE(login_time) AS dt FROM user_login_log) x
) y
GROUP BY uid, grp
HAVING COUNT(*) >= 3;

段落五：方案对比——日期差法 vs LAG 差值法

方案	思路	优点	缺点
日期差法（dt - rn）	排名减日期生成 grp	最经典、写起来最短	需要 DATE_SUB 支持
LAG 差值法	LAG 看前一行，断 > 1 就开新段	可扩展允许断 N 天	SQL 更长
SUM OVER 累加法	差 > 1 时打 1，SUM 累加作为 seg_id	最灵活	最难理解
自 join 法	自己 join 自己 + 3 次	不会窗口也能写	性能最差，不推荐

面试官最认可的是日期差法，因为它最简洁、体现了对窗口函数的理解。但如果是允许断 N 天的变体，要马上切到 LAG 或 SUM OVER 累加法——这种『能切换方案』的能力才是考点。

面试高频追问

追问一：ROW_NUMBER / RANK / DENSE_RANK 的区别？

这是紧接着必问的。ROW_NUMBER 无论值是否相同都给连续编号（1、2、3、4）；RANK 相同值给相同排名且跳号（1、2、2、4）；DENSE_RANK 相同值给相同排名不跳号（1、2、2、3）。连续登录题用 ROW_NUMBER，因为我们按天去重了，值必然唯一；但如果没去重，用 ROW_NUMBER 会算错。

追问二：这个 SQL 的数据量如果是 10 亿行怎么优化？

首先一定加分区过滤（比如只看最近 30 天）；其次去重那一步可以提到 DWS 层预计算（dws_user_login_daily），ADS 层直接用；最后是窗口函数会触发 shuffle，PARTITION BY uid 如果 uid 分布不均会倾斜，可以加盐（uid + random 前缀），或者用 AQE 的 skew join 自动处理。我在某出海硬件品牌处理过类似的 20 亿行用户行为表，dws 预聚合 + 分区 + AQE，执行时间从 40 分钟降到 8 分钟。

追问三：如果登录日志是实时流，怎么算实时的连续登录？

离线思路不能直接搬过来。实时场景通常在 Flink 里用 Keyed State 维护每个用户的『上次登录日期』和『当前连续天数』，新一条登录进来时比较：如果日期连续就天数 +1，断开就重置为 1。Keyed State 要加 TTL（比如 60 天过期），否则状态会无限膨胀。这也是第 18 篇实时数仓要细讲的点。

实战踩坑：一个时区坑让连续登录算错了 30%

我在某短视频平台最早写连续登录 SQL 时有个坑：login_time 是 UTC 时间，但我们业务方要按北京时间算『自然日』。我直接用 DATE(login_time) 切，结果北京时间凌晨 0-8 点登录的行为都被算到前一天去了，连续登录漏算 30%。

解决办法是 DATE(CONVERT_TZ(login_time, 'UTC', '+08:00'))。后来做出海业务时这个坑又放大了——8 个时区，不能统一用北京时间，要按用户自己的时区切。这就是第 12 篇要讲的跨时区问题。

-- ❌ 错误：直接 DATE()
SELECT uid, DATE(login_time) AS dt FROM log;
 
-- ✅ 正确：先转时区再取日期
SELECT uid, DATE(CONVERT_TZ(login_time, 'UTC', '+08:00')) AS dt FROM log;
 
-- ✅ 出海业务：按用户自己时区切
SELECT l.uid, DATE(CONVERT_TZ(l.login_time, 'UTC', u.tz)) AS dt
FROM log l JOIN dim_user u USING (uid);

总结

连续登录题本质是考『日期连续性判断』的 SQL 能力，核心套路就是『日期 - 排名 = 同一段』。能写出最基础的版本是 6 分，能写出最长连续 / 允许断 1 天 / 起止日期三个变体是 8 分，能补上优化思路和实时方案的追问是 9 分。再加上一个踩坑故事，这道题满分。

下一篇：『一个 SQL 跑了 3 小时，怎么优化？』——SQL 实操第二道，不是考语法，考排障思路。

你最近一次被问『连续 N 天』是在面试还是工作？碰到过哪些奇葩的变体（比如『连续 N 天累计消费 > X』）？

关注「Wood聊大数据」| 9年大数据老兵，聊技术、聊职场、聊成长