3月29日刷题笔记--SQL连续登录天数

本篇题目#

题目一：2023年1月用户最长连续登录天数（登陆表 tb_dau）
题目二：找出存在连续登录 ≥ 3 天的用户（登录表 login_tb）

核心原理：日期 - 行号 = 常数#

连续序列中，日期减去其行号的结果是一个固定不变的常数。

一旦序列中断，差值就会改变，从而把不同的连续段自然地区分开。

举例说明#

用户 10000 的登录数据：

fdate	rk	DATE_SUB(fdate, rk)
2023-01-01	1	2022-12-31
2023-01-02	2	2022-12-31
2023-01-04	3	2023-01-01

01-01 和 01-02 连续 → 差值相同，都是 2022-12-31，属于同一段
01-04 中断了 → 差值变为 2023-01-01，另起一段

解题五步模板#

1
去重 → 编号 → 差值分组 → 聚合得到段长度 → 取结果

1
-- Step 1：去重 + 编排行号
2
WITH t1 AS (
3
    SELECT DISTINCT
4
        user_id,
5
        fdate,
6
        ROW_NUMBER() OVER(PARTITION BY user_id ORDER BY fdate) AS rk
7
    FROM tb_dau
8
    WHERE fdate BETWEEN '开始日期' AND '结束日期'
9
),
10

11
-- Step 2：用"日期 - 行号"标记连续分组，统计每段长度
12
t2 AS (
13
    SELECT
14
        user_id,
15
        DATE_SUB(fdate, INTERVAL rk DAY) AS diff,  -- 差值相同 = 同一连续段
16
        COUNT(1) AS consec_days
17
    FROM t1
18
    GROUP BY user_id, DATE_SUB(fdate, INTERVAL rk DAY)
19
)
20

21
-- Step 3：按需求取结果
22
SELECT
23
    user_id,
24
    MAX(consec_days) AS max_consec_days
25
FROM t2
26
GROUP BY user_id;

题目一：最长连续登录天数#

题意：统计 2023 年 1 月每个用户最长的连续登录天数。

输入（tb_dau）：

fdate	user_id
2023-01-01	10000
2023-01-02	10000
2023-01-04	10000

输出：

user_id	max_consec_days
10000	2

完整 SQL#

1
WITH t1 AS (
2
    SELECT DISTINCT user_id, fdate,
3
           ROW_NUMBER() OVER(PARTITION BY user_id ORDER BY fdate) AS rk
4
    FROM tb_dau
5
    WHERE fdate BETWEEN '2023-01-01' AND '2023-01-31'
6
),
7
t2 AS (
8
    SELECT user_id, COUNT(1) AS consec_days
9
    FROM t1
10
    GROUP BY user_id, DATE_SUB(fdate, INTERVAL rk DAY)
11
)
12
SELECT user_id, MAX(consec_days) AS max_consec_days
13
FROM t2
14
GROUP BY user_id;

执行过程拆解#

t1 的结果（去重 + 编号）：

user_id	fdate	rk
10000	2023-01-01	1
10000	2023-01-02	2
10000	2023-01-04	3

t2 的结果（差值分组 + 计数）：

user_id	diff	consec_days
10000	2022-12-31	2
10000	2023-01-01	1

最终：MAX(consec_days) = 2 ✓

题目二：筛选连续登录 ≥ 3 天的用户#

题意：从登录记录中，找出在任意时间段内存在连续登录 ≥ 3 天的用户，只保留已注册用户。

完整 SQL#

1
WITH T1 AS (
2
    SELECT DISTINCT user_id, DATE(log_time) AS login_time
3
    FROM login_tb
4
    WHERE user_id IN (SELECT user_id FROM register_tb)  -- 只看已注册用户
5
),
6
T2 AS (
7
    SELECT user_id, login_time,
8
           ROW_NUMBER() OVER(PARTITION BY user_id ORDER BY login_time) AS rn
9
    FROM T1
10
),
11
T3 AS (
12
    SELECT user_id, COUNT(1) AS consec_days
13
    FROM T2
14
    GROUP BY user_id, DATE_SUB(login_time, INTERVAL rn DAY)
15
)
16
SELECT DISTINCT user_id
17
FROM T3
18
WHERE consec_days >= 3
19
ORDER BY user_id;

与题目一的区别#

	题目一	题目二
目标	最长连续天数是多少	是否存在连续 ≥ N 天
最终取法	`MAX(consec_days)`	`WHERE consec_days >= 3`
额外处理	日期范围过滤	关联注册表过滤无效用户

常见变种汇总#

题目问法	Step 3 写法
最长连续天数	`MAX(consec_days)`
是否存在连续 N 天	`WHERE consec_days >= N`
连续 N 天的用户名单	`WHERE consec_days >= N` + `SELECT DISTINCT user_id`

容易踩的坑#

① 忘记 DISTINCT

同一天登录多次会产生重复行，行号就乱了，差值不再稳定，必须先去重。

② 用 RANK 代替 ROW_NUMBER

RANK 遇到并列会跳号（1, 1, 3），差值就不准了，必须用 ROW_NUMBER。

③ 日期序列 vs 数字序列

1
-- 日期序列用 DATE_SUB
2
DATE_SUB(fdate, INTERVAL rk DAY)
3

4
-- 数字序列直接相减
5
val - rk

总结#

核心公式：连续序列中，日期 - 行号 = 常数，差值相同即同一连续段
五步模板：去重 → 编号 → 差值分组 → COUNT 段长 → 取结果
Step 3 灵活变：根据题目要求换用 MAX / WHERE / MIN+MAX 等