01 | SQL统计连续问题

原创已于 2025-06-24 14:39:18 修改 · 373 阅读

1 ·

CC 4.0 BY-SA版权

文章标签：

#大数据 #数据仓库 #数据分析 #sql #面试

于 2025-06-24 14:12:33 首次发布

SQL面试真题详解专栏收录该内容

8 篇文章 ¥9.90 ¥99.00

订阅专栏

超级会员免费看

在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连续性问题，核心在于利用行号与目标字段的差值构造分组标识。