如何按ID分组并筛选连续销量为0的日期区间

原始数据表包含Product IDs（产品ID）、date_time（日期，按日统计）、Quantity Sold（销量）三列，现有数据如下：

ID - Date - Quantity Sold
1  - 1/1 - 7
1  - 1/2 - 1
1  - 1/3 - 0
1  - 1/4 - 0
1  - 1/5 - 0
1  - 1/6 - 2
1  - 1/7 - 8
2  - 1/1 - 0
2  - 1/2 - 0
2  - 1/3 - 1
2  - 1/4 - 10
2  - 1/5 - 2
2  - 1/6 - 0
2  - 1/7 - 0

需要统计每个产品连续销量为0的日期区间，期望结果如下：

ID - StartDate - EndDate - Quan
1 - 1/3 - 1/5 - 0
2 - 1/1 - 1/2 - 0
2 - 1/6 - 1/7 - 0

解决方案：用「间隙与岛屿」思路处理连续分组 #

这类连续区间分组问题属于SQL经典的「间隙与岛屿」场景，核心是给连续的日期分配相同的分组标识，具体实现代码如下（以MySQL为例，其他数据库可调整日期函数）：

-- 第一步：筛选出所有销量为0的记录
WITH zero_sales AS (
    SELECT ID, Date
    FROM your_table
    WHERE `Quantity Sold` = 0
),
-- 第二步：生成分组标识，连续日期会得到相同的group_key
grouped_data AS (
    SELECT 
        ID,
        Date,
        -- 日期减去按ID排序后的行号天数，连续日期的group_key一致
        DATE_SUB(Date, INTERVAL ROW_NUMBER() OVER (PARTITION BY ID ORDER BY Date) DAY) AS group_key
    FROM zero_sales
)
-- 第三步：按ID和分组标识聚合，得到连续0销量的起止日期
SELECT 
    ID,
    MIN(Date) AS StartDate,
    MAX(Date) AS EndDate,
    0 AS Quan
FROM grouped_data
GROUP BY ID, group_key
ORDER BY ID, StartDate;

逻辑说明 #

1. 筛选目标记录：先把销量为0的行单独提取出来，减少后续计算量；
2. 生成分组标识：通过ROW_NUMBER()给每个ID下的日期按顺序编号，用当前日期减去编号对应的天数，连续的日期因为编号递增和日期递增同步，得到的group_key会完全相同，非连续的日期则会生成不同的group_key；
3. 聚合统计：按ID和group_key分组，取每组的最小、最大日期，就是该段连续0销量的起止时间。

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