基于产品促销状态表获取连续促销日期区间

针对你提供的表结构和需求，可以通过窗口函数+分组聚合的方式实现连续时间段的统计，核心思路是给连续的日期分配同一个分组标识，再对分组聚合取起止日期。

实现步骤与SQL代码 #

WITH promotion_days AS (
    -- 筛选出处于促销状态的记录（排除promotion_product_id为null的日期）
    SELECT 
        day,
        promotion_product_id
    FROM your_table_name
    WHERE promotion_product_id IS NOT NULL
),
grouped_days AS (
    -- 给每条记录分配行号，计算分组标识
    SELECT 
        promotion_product_id,
        day,
        -- 连续日期减去行号偏移量后会得到相同的分组值
        DATE_SUB(day, INTERVAL (ROW_NUMBER() OVER (PARTITION BY promotion_product_id ORDER BY day) - 1) DAY) AS group_id
    FROM promotion_days
)
-- 按产品和分组标识聚合，得到起止时间
SELECT 
    promotion_product_id,
    MIN(day) AS start_promotion_at,
    MAX(day) AS end_promotion_at
FROM grouped_days
GROUP BY promotion_product_id, group_id
ORDER BY promotion_product_id, start_promotion_at;

代码说明 #

1. promotion_days CTE：先过滤掉非促销日期（promotion_product_id为null的行），只保留产品处于促销状态的日期记录。
2. grouped_days CTE：通过ROW_NUMBER()按产品分组、日期排序生成行号，再用DATE_SUB计算分组标识——连续的日期减去行号偏移后会得到同一个日期值，以此作为连续区间的分组依据。
3. 最终聚合：按产品和分组标识分组，取每组的最小日期作为开始时间，最大日期作为结束时间，就是需要的连续促销时间段。

测试结果 #

代入你提供的测试数据，执行后会得到期望的输出：

promotion_product_id  start_promotion_at  end_promotion_at
1251589               2023-02-22          2023-03-07
1251589               2023-03-09          2023-03-10

内容的提问来源于stack exchange，提问作者Javier Lopez Tomas