MySQL8.0无存储过程实现补全缺失日期计算商品日均售价

问题说明 #

现有商品售价表存储各卖家不同日期的商品售价，表约束为：同一卖家、同一商品、同一销售日期仅存在1条记录。表中可能存在全量商品缺失部分日期记录的情况，例如样例数据就缺失2022-07-09（周六）、2022-07-10（周日）两个日期的全部数据。
查询要求：以指定日期为基准向前回溯连续7天，按销售日期、商品ID维度，统计每个商品当日所有卖家的平均售价，缺失销售日期对应的平均售价字段返回NULL。要求仅用MySQL 8.0普通SQL实现，不得使用存储过程。

已知信息 #

• 原表字段：
  • id_seller：卖家ID
  • id_item：商品ID
  • price：售价
  • sale_date：销售日期
• 样例数据：仅包含2022-07-08、2022-07-11两个日期的各卖家各商品售价记录
• 期望输出字段：
  • id_item：商品ID
  • avg_price：平均售价，无数据日期取值为NULL
  • sale_date：销售日期

实现思路 #

缺失日期补全的核心是先构造完整的维度基准组合，再关联聚合结果，无需额外建辅助表：

• 用MySQL 8.0原生支持的递归CTE生成基准日期向前连续7天的完整日期序列
• 提取统计时间范围内所有出现过的商品ID全集，避免漏商品
• 将日期序列与商品全集做笛卡尔积，生成所有「日期+商品」的组合，再左关联原表预先聚合好的日度商品平均售价，匹配不到数据的记录avg_price自然返回NULL

实现SQL #

-- 替换为实际需要的基准日期
SET @base_date = '2022-07-11';

WITH RECURSIVE date_series AS (
    -- 生成连续7天日期序列的起点：基准日期往前推6天
    SELECT DATE_SUB(@base_date, INTERVAL 6 DAY) AS sale_date
    UNION ALL
    SELECT DATE_ADD(sale_date, INTERVAL 1 DAY)
    FROM date_series
    WHERE sale_date < @base_date
),
-- 取统计周期内全量商品ID
item_all AS (
    SELECT DISTINCT id_item
    FROM sale_price -- 此处替换为你的实际表名
    WHERE sale_date BETWEEN DATE_SUB(@base_date, INTERVAL 6 DAY) AND @base_date
),
-- 预聚合原表有数据的日期、商品维度平均售价
daily_avg AS (
    SELECT
        sale_date,
        id_item,
        AVG(price) AS avg_price
    FROM sale_price -- 此处替换为你的实际表名
    WHERE sale_date BETWEEN DATE_SUB(@base_date, INTERVAL 6 DAY) AND @base_date
    GROUP BY sale_date, id_item
)
-- 关联生成最终结果
SELECT
    ia.id_item,
    da.avg_price,
    ds.sale_date
FROM date_series ds
CROSS JOIN item_all ia
LEFT JOIN daily_avg da
    ON ds.sale_date = da.sale_date
    AND ia.id_item = da.id_item
ORDER BY ds.sale_date, ia.id_item;

效果说明 #

以样例数据、基准日期设为2022-07-11为例：

• 会自动生成2022-07-05至2022-07-11共7天的完整日期
• 所有周期内出现过的商品都会匹配到全部7个日期
• 2022-07-08、2022-07-11有原始数据的日期，会返回对应商品计算后的平均售价
• 2022-07-05、2022-07-06、2022-07-07、2022-07-09、2022-07-10无原始数据的日期，对应商品的avg_price自动返回NULL，完全符合需求

注意：使用时需要把代码里的sale_price替换为你实际业务中的售价表名，修改@base_date的取值即可切换统计基准日，无需调整其他逻辑。

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