怎么查连续登录N天的用户？

SQL手册第 2 题约 6 分钟读完

本文目录

总结
面试原题
核心思路：日期 - 序号 = 常数
完整SQL（三步走）
面试加分：为什么这个方法有效？
实战踩坑：我在工作中怎么用的
变体题：连续N天增长的股票

总结

关键点	内容
--------	------
核心公式	日期 - ROW_NUMBER = 分组标识
原理	等差数列差值为常数
必须先去重	否则序号会被撑大
注意引擎差异	DATE_SUB在不同引擎中行为不同
常见变体	连续增长、连续下降、连续达标

这道题的思路掌握了，"连续"类问题就不怕了。

下一篇03聊行转列和列转行，这道题在实际工作中用得非常多，但面试的时候很多人写不利索。

你是用什么方法解连续登录问题的？有没有更优雅的写法？评论区聊聊～

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

先把这段讲顺；面试官追问时，再展开下面的原理和细节。

详细讲解

这道题是SQL面试的"分水岭"——能写出来的人，说明对窗口函数不只是会用，而是真正理解了。今天我把这道题的思路掰开了讲，保证你看完就能举一反三。

面试原题

给定用户登录日志表 user_login(user_id, login_date)，找出连续登录3天及以上的用户及其最长连续登录天数。

核心思路：日期 - 序号 = 常数

这个解法的精髓就一句话：如果一个用户连续登录，那么登录日期减去行号得到的值是固定的。

举个例子，用户A的登录记录：

login_date  | row_number | login_date - row_number
2025-01-01  | 1          | 2024-12-31
2025-01-02  | 2          | 2024-12-31
2025-01-03  | 3          | 2024-12-31
2025-01-05  | 4          | 2025-01-01  ← 断了，差值变了
2025-01-06  | 5          | 2025-01-01

看到了吗？1号到3号是连续的，减去序号后都是12月31号。5号和6号是另一段连续，减去序号都是1月1号。差值相同的记录，就属于同一段连续登录。

完整SQL（三步走）

第一步：去重 + 编号

同一天可能登录多次，先去重，再按日期排序编号。

WITH login_dedup AS (
  SELECT DISTINCT user_id, login_date
  FROM user_login
),
login_with_rn AS (
  SELECT
    user_id,
    login_date,
    ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date) AS rn
  FROM login_dedup
)

第二步：算差值，分组

用登录日期减去序号，得到分组标识。

, login_grouped AS (
  SELECT
    user_id,
    login_date,
    DATE_SUB(login_date, rn) AS grp  -- 日期 - 序号 = 分组标识
  FROM login_with_rn
)

第三步：按分组统计连续天数，筛选 >= 3

SELECT
  user_id,
  grp,
  MIN(login_date) AS start_date,
  MAX(login_date) AS end_date,
  COUNT(1)        AS consecutive_days
FROM login_grouped
GROUP BY user_id, grp
HAVING COUNT(1) >= 3
ORDER BY user_id, start_date

如果只要每个用户的最长连续天数：

SELECT
  user_id,
  MAX(consecutive_days) AS max_consecutive_days
FROM (
  SELECT
    user_id,
    grp,
    COUNT(1) AS consecutive_days
  FROM login_grouped
  GROUP BY user_id, grp
) t
GROUP BY user_id
HAVING MAX(consecutive_days) >= 3

面试加分：为什么这个方法有效？

面试官可能会追问原理。你可以这样解释：

连续日期的本质是一个等差数列，公差为1。行号也是公差为1的等差数列。两个等差数列相减，结果是常数。一旦日期不连续了，差值就会发生跳变，自然形成了新的分组。

这个方法的时间复杂度是O(N log N)（排序的开销），空间复杂度O(N)，在大数据场景下性能很好。

实战踩坑：我在工作中怎么用的

我之前做元宇宙社交App的时候，运营提了个需求：找出连续7天活跃的用户，给他们发一个"忠实用户"徽章。

需求听着简单，但有两个坑：

坑1：一天多次登录

用户一天可能打开App十几次，如果不先DISTINCT去重，ROW_NUMBER的序号会被撑大，差值计算就全乱了。这就是为什么第一步一定要去重。

坑2：DATE_SUB的引擎兼容性

在Hive里，DATE_SUB(login_date, rn) 没问题。但在某些引擎里（比如早期的Doris），DATE_SUB不支持直接减整数，要用 DATE_ADD(login_date, INTERVAL -rn DAY)。我当时在Doris上跑这个SQL直接报错，排查了半小时才发现是函数兼容问题。

所以面试的时候如果你能提到这些实际踩坑经验，面试官会觉得你是真写过，不是背的。

变体题：连续N天增长的股票

同样的思路可以解决"连续N天上涨的股票"问题，只需要把条件从"日期连续"变成"价格递增"：

-- 先标记每天是否比前一天涨了
WITH stock_flag AS (
  SELECT *,
    CASE WHEN price > LAG(price) OVER (PARTITION BY stock_id ORDER BY trade_date)
         THEN 1 ELSE 0 END AS is_up
  FROM stock_price
),
-- 再用前缀和分组（遇到0就断开）
stock_grouped AS (
  SELECT *,
    SUM(CASE WHEN is_up = 0 THEN 1 ELSE 0 END) 
      OVER (PARTITION BY stock_id ORDER BY trade_date) AS grp
  FROM stock_flag
)
SELECT stock_id, grp, COUNT(1) AS up_days
FROM stock_grouped
WHERE is_up = 1
GROUP BY stock_id, grp
HAVING COUNT(1) >= 3

这个变体用到了前缀和分组的思路，和"日期减序号"是同一类方法。面试官经常会在连续登录之后追问这个变体，提前准备好就不慌。

刷题进度保存在当前浏览器