如何查询同一年度内连续登录≥3周的用户？SQL求助

问题回顾 #

现有用户登录表记录如下：

name      week_no   year_no
fb        5         2021
twitter   1         2022
twitter   2         2022
twitter   3         2022
twitter   7         2022
youtube  21         2022

需要找出同一年度内连续登录≥3周的用户，预期输出：

name        year_no
twitter     2022

测试表创建语句：

CREATE TABLE test (
    name varchar(20),
    week_no int,
    year_no int
);
INSERT INTO test (name, week_no, year_no)
VALUES ('fb', 5, 2021), 
       ('twitter', 1, 2022),
       ('twitter', 2, 2022),
       ('twitter', 3, 2022), 
       ('twitter', 7, 2022),
       ('youtube', 21, 2022);

你的尝试问题说明 #

你写的select * from test group by year_no, name;仅能按年度和用户分组，但无法判断周数是否连续，也无法统计连续的周数，因此达不到需求。

解决方案SQL语句 #

WITH user_week_ranked AS (
    SELECT 
        name,
        year_no,
        week_no,
        -- 按用户和年度分组，对周数排序得到序号
        ROW_NUMBER() OVER (PARTITION BY name, year_no ORDER BY week_no) AS rn
    FROM test
),
continuous_groups AS (
    SELECT 
        name,
        year_no,
        -- 连续周数的特征：week_no - rn 的值相同
        week_no - rn AS group_key,
        COUNT(*) AS consecutive_weeks
    FROM user_week_ranked
    GROUP BY name, year_no, group_key
    -- 筛选连续周数≥3的分组
    HAVING COUNT(*) >= 3
)
-- 去重得到符合条件的用户和年度
SELECT DISTINCT name, year_no
FROM continuous_groups;

语句解释 #

1. CTE user_week_ranked：

   • 使用ROW_NUMBER()窗口函数，按name和year_no分组，对每组内的week_no升序排序，得到每个周对应的序号rn。比如twitter2022年的周1、2、3、7对应的rn是1、2、3、4。

2. CTE continuous_groups：

   • 计算week_no - rn，连续的周数会得到相同的group_key（比如1-1=0，2-2=0，3-3=0；7-4=3）。
   • 按name、year_no、group_key分组，统计每组的记录数consecutive_weeks，筛选出数量≥3的分组。

3. 最终查询：

   • 对符合条件的分组去重，得到唯一的用户和年度组合。

执行结果 #

运行上述语句后，会得到预期输出：

name        year_no
twitter     2022

内容的提问来源于stack exchange，提问作者user_12