MySQL：如何为日期范围内所有日期生成各推荐类型累计销售数？

要实现日期范围内每一天、每个推荐类型都有对应记录，且累计销售数能延续到无销售的日期，需要先构造完整的日期-推荐类型组合，再关联销售数据计算累计值，具体步骤如下：

1. 生成指定日期范围内的所有日期 #

首先需要生成包含目标日期区间内每一天的临时数据集，不同数据库的实现方式略有差异：

• PostgreSQL 用 generate_series 直接生成日期序列
• MySQL 用递归CTE生成日期序列

2. 获取所有唯一的推荐类型 #

从 Service 表中提取所有不重复的 sr_id，确保每个推荐类型都被覆盖。

3. 构造日期-推荐类型的完整组合 #

将日期序列和唯一推荐类型做交叉连接，得到每个推荐类型对应每一天的记录，这一步是补全缺失行的核心。

4. 计算累计销售数 #

先按推荐类型和日期聚合每日销售数量，再通过窗口函数对完整组合做累计求和，用 COALESCE 将无销售日期的累计值转为0或继承之前的累计数。

PostgreSQL 示例SQL #

WITH date_range AS (
    -- 生成2020-01-01到2021-01-01的所有日期
    SELECT generate_series(
        '2020-01-01'::date,
        '2021-01-01'::date,
        '1 day'::interval
    ) AS date
),
unique_sr_ids AS (
    -- 获取所有唯一的推荐类型
    SELECT DISTINCT sr_id FROM Service
),
all_combinations AS (
    -- 交叉连接得到每个推荐类型+每一天的完整组合
    SELECT us.sr_id, dr.date::date AS date
    FROM unique_sr_ids us
    CROSS JOIN date_range dr
),
daily_sales AS (
    -- 按推荐类型和日期聚合每日销售数量
    SELECT 
        sr_id,
        s_saledate,
        COUNT(uuid) AS daily_count
    FROM Service
    GROUP BY sr_id, s_saledate
)
SELECT 
    ac.sr_id,
    ac.date,
    -- 累计求和，无销售日期用COALESCE补0，自动延续之前的累计值
    COALESCE(SUM(ds.daily_count) OVER (
        PARTITION BY ac.sr_id 
        ORDER BY ac.date
    ), 0) AS num_sales_as_of_date
FROM all_combinations ac
LEFT JOIN daily_sales ds 
    ON ac.sr_id = ds.sr_id 
    AND ac.date = ds.s_saledate
ORDER BY ac.sr_id, ac.date;

MySQL 示例SQL #

WITH RECURSIVE date_range AS (
    -- 递归生成2020-01-01到2021-01-01的所有日期
    SELECT '2020-01-01' AS date
    UNION ALL
    SELECT DATE_ADD(date, INTERVAL 1 DAY)
    FROM date_range
    WHERE date < '2021-01-01'
),
unique_sr_ids AS (
    -- 获取所有唯一的推荐类型
    SELECT DISTINCT sr_id FROM Service
),
all_combinations AS (
    -- 交叉连接得到每个推荐类型+每一天的完整组合
    SELECT us.sr_id, dr.date
    FROM unique_sr_ids us
    CROSS JOIN date_range dr
),
daily_sales AS (
    -- 按推荐类型和日期聚合每日销售数量
    SELECT 
        sr_id,
        s_saledate,
        COUNT(uuid) AS daily_count
    FROM Service
    GROUP BY sr_id, s_saledate
)
SELECT 
    ac.sr_id,
    ac.date,
    -- 累计求和，无销售日期用COALESCE补0，自动延续之前的累计值
    COALESCE(SUM(ds.daily_count) OVER (
        PARTITION BY ac.sr_id 
        ORDER BY ac.date
    ), 0) AS num_sales_as_of_date
FROM all_combinations ac
LEFT JOIN daily_sales ds 
    ON ac.sr_id = ds.sr_id 
    AND ac.date = ds.s_saledate
ORDER BY ac.sr_id, ac.date;

关键说明 #

• 交叉连接确保了日期范围内的每一天和每个推荐类型都有对应行，解决了原查询只返回存在销售记录行的问题
• 窗口函数 SUM(...) OVER (PARTITION BY sr_id ORDER BY date) 会自动按日期顺序累计求和，即使某一天没有销售数据，也会继承前一天的累计值
• COALESCE 用于处理初始无销售的日期（比如sr_id=2在2020-01-01之前没有销售），将累计值的NULL转为0

内容的提问来源于stack exchange，提问作者J.spenc