
目录
在SQL中,连续问题是一类比较常见的数据处理需求,核心是识别序列中连续出现的记录。如连续登录、连续充值、连续购买等场景都属于同类问题。
一、核心解决思路
通过日期/数值与行号的差值创建分组标识,连续记录因差值相同会被归入同一组,再用分组聚合计算连续次数。
二、通用解决步骤
例:SQL统计连续登录3天及以上的用户
2.1 数据准备
登录记录表 user_login:
CREATE TABLE user_login (
user_id INT NOT NULL,
login_date DATE NOT NULL
);
INSERT INTO user_login (user_id, login_date) VALUES
(1, '2025-06-01'),
(1, '2025-06-02'),
(1, '2025-06-03'), -- 用户1连续3天登录
(1, '2025-06-04'),
(2, '2025-06-01'),
(2, '2025-06-03'), -- 用户2有间隔(缺少1月2日)
(2, '2025-06-04');
步骤1:去重并生成行号
WITH ranked_logins AS (
SELECT
user_id,
login_date,
ROW_NUMBER() OVER (
PARTITION BY user_id
ORDER BY login_date ASC
) AS rn
FROM user_login
GROUP BY user_id, login_date -- 去重同用户同天多次登录
)
select * from ranked_logins
结果:

步骤2:计算分组标识
原理:连续日期的 login_date - rn 值相同(因日期与行号同步增长)。
WITH ranked_logins AS (
SELECT
user_id,
login_date,
ROW_NUMBER() OVER (
PARTITION BY user_id
ORDER BY login_date ASC
) AS rn
FROM user_login
GROUP BY user_id, login_date -- 去重同用户同天多次登录
),
grouped_logins AS (
SELECT
user_id,
login_date,
DATE_SUB(login_date, INTERVAL rn DAY) AS group_flag -- MySQL写法
FROM
ranked_logins
)
select * from grouped_logins
结果:

步骤3:分组统计连续天数
WITH ranked_logins AS (
SELECT
user_id,
login_date,
ROW_NUMBER() OVER (
PARTITION BY user_id
ORDER BY login_date ASC
) AS rn
FROM user_login
GROUP BY user_id, login_date -- 去重同用户同天多次登录
),
grouped_logins AS (
SELECT
user_id,
login_date,
DATE_SUB(login_date, INTERVAL rn DAY) AS group_flag -- MySQL写法
FROM
ranked_logins
)
SELECT
user_id,
MIN(login_date) AS start_date,
MAX(login_date) AS end_date,
COUNT(*) AS consecutive_days
FROM grouped_logins
GROUP BY user_id, group_flag
HAVING COUNT(*) >= 3; -- 筛选连续3天+
结果:

2.2 关键点解析
- 行号生成
ROW_NUMBER()按用户分区、日期排序,为连续序列奠定基础。 - 分组标识原理
- 连续序列:
日期 - 行号 = 固定值(如2025-06-01 - 1 = 2025-06-02 - 2)。 - 中断序列:跳过的日期会导致行号增长快于日期,差值变化。
- 去重必要性
生产环境中,用户可能一天登录多次,注意去重,避免同一用户同天多次登录干扰行号连续性。
三、其他连续问题变体
3.1 最长连续序列
-- 在分组统计后按连续天数降序取第一条
SELECT user_id, consecutive_days
FROM (
-- 前述分组统计逻辑
)
ORDER BY consecutive_days DESC
LIMIT 1;
四、优化与注意事项
- 性能:窗口函数效率较高,但大数据量时需对
user_id分区字段建索引。 - 跨年处理:使用
DATE_SUB/DATEADD可自动处理跨年日期。 - 中断定义:根据需求调整连续性判断(如允许间隔1天视为连续,需修改分组逻辑)。
通过此方法可高效解决各类SQL连续性问题,核心在于利用行号与目标字段的差值构造分组标识。
2万+

被折叠的 条评论
为什么被折叠?



