如何用MySQL查询每位Netflix用户的最长连续订阅月数？

我有一个名为Netflix的表，包含Name和Date两列，记录了按月购买Netflix订阅服务的客户信息，具体数据如下：

需要获取每位客户的最长连续订阅月数，期望输出格式如下：

建表及插入数据的SQL代码 #

Create table If Not Exists Netflix (Name varchar(10), Date Date);

insert into Netflix (Name, Date) values ('Ram', '2021-01-01');
insert into Netflix (Name, Date) values ('Ram', '2021-02-01');
insert into Netflix (Name, Date) values ('Sam', '2021-01-01');
insert into Netflix (Name, Date) values ('Sam', '2021-02-01');
insert into Netflix (Name, Date) values ('Sam', '2021-03-01');
insert into Netflix (Name, Date) values ('Ram', '2021-06-01');
insert into Netflix (Name, Date) values ('Ram', '2021-07-01');
insert into Netflix (Name, Date) values ('Ram', '2021-08-01');
insert into Netflix (Name, Date) values ('Sam', '2021-08-01');
insert into Netflix (Name, Date) values ('Sam', '2021-09-01');
insert into Netflix (Name, Date) values ('Sam', '2021-10-01');
insert into Netflix (Name, Date) values ('Sam', '2021-11-01');

实现需求的MySQL查询语句 #

WITH subscription_groups AS (
    SELECT 
        Name,
        Date,
        -- 通过日期减去排序后的月份序号，为连续订阅记录生成相同的分组ID
        DATE_SUB(Date, INTERVAL ROW_NUMBER() OVER (PARTITION BY Name ORDER BY Date) MONTH) AS group_id
    FROM Netflix
),
continuous_subscriptions AS (
    SELECT 
        Name,
        MIN(Date) AS start_month,
        MAX(Date) AS end_month,
        COUNT(*) AS max_count
    FROM subscription_groups
    GROUP BY Name, group_id
)
SELECT 
    Name,
    start_month,
    end_month,
    max_count
FROM continuous_subscriptions
WHERE (Name, max_count) IN (
    SELECT Name, MAX(max_count)
    FROM continuous_subscriptions
    GROUP BY Name
)
ORDER BY Name;

逻辑说明 #

1. 分组连续记录：使用ROW_NUMBER()对每个用户的订阅日期排序，再用DATE_SUB计算分组标识——连续的月份会得到相同的group_id，因为相邻月份的日期减去递增的序号后结果一致。
2. 聚合连续组信息：按用户和分组ID聚合，得到每组的开始月份、结束月份和连续月数。
3. 筛选最长连续组：通过子查询获取每个用户的最大连续月数，再筛选出对应的数据行，确保每个用户只返回最长的连续订阅记录。

内容的提问来源于stack exchange，提问作者Sai Shodhan Rao