MySQL 8+含缺失数据的分区累计库存求和方案咨询

我有一张记录不同国家、不同产品库存变动（Variation）的表，表结构及数据如下：

需要在MySQL 8+中编写查询语句，按月份、产品（Product）、国家（Country）汇总仓库库存（Stock），期望结果如下：

但部分国家或产品存在数据缺失的情况，简单的分区累计求和无法得到正确结果：

SELECT
Date,
Product,
Country,
SUM(SUM(variation)) OVER (PARTITION BY Product, Country ORDER BY Date)
FROM mytable
GROUP BY Date,Country,Product

目前想到的方案是用存储过程循环日期计算总计，想咨询是否有更高效的替代方案，比如递归CTE？

可以用递归CTE生成所有需要统计的月份，结合所有国家和产品的笛卡尔积确保每个月的每个国家-产品组合都存在，再左连接原始数据的月度变动汇总，最后计算累计库存。完整SQL语句如下：

WITH RECURSIVE date_range AS (
    -- 取表中最早和最晚的月份
    SELECT 
        DATE_FORMAT(MIN(Date), '%Y-%m-01') AS month_start,
        DATE_FORMAT(MAX(Date), '%Y-%m-01') AS max_month
    FROM mytable
    UNION ALL
    SELECT 
        DATE_ADD(month_start, INTERVAL 1 MONTH),
        max_month
    FROM date_range
    WHERE month_start < max_month
),
-- 获取所有唯一的国家和产品组合
dimensions AS (
    SELECT DISTINCT Country, Product FROM mytable
),
-- 生成完整的月份-国家-产品组合
full_combinations AS (
    SELECT 
        dr.month_start,
        d.Country,
        d.Product
    FROM date_range dr
    CROSS JOIN dimensions d
),
-- 计算每个维度的月度变动总和
monthly_variations AS (
    SELECT 
        DATE_FORMAT(Date, '%Y-%m-01') AS month_start,
        Country,
        Product,
        SUM(Variation) AS total_variation
    FROM mytable
    GROUP BY month_start, Country, Product
)
-- 计算累计库存并格式化日期
SELECT 
    DATE_FORMAT(fc.month_start, '%Y年%m月') AS Date,
    fc.Country,
    fc.Product,
    -- 累计求和，空值用0填充
    SUM(COALESCE(mv.total_variation, 0)) OVER (
        PARTITION BY fc.Country, fc.Product 
        ORDER BY fc.month_start
    ) AS Stock
FROM full_combinations fc
LEFT JOIN monthly_variations mv 
    ON fc.month_start = mv.month_start
    AND fc.Country = mv.Country
    AND fc.Product = mv.Product
-- 按月份倒序、国家、产品排序，匹配期望结果的顺序
ORDER BY fc.month_start DESC, fc.Country, fc.Product;

关键说明 #

• 递归CTE date_range 生成连续的月份序列，避免遗漏无库存变动的月份
• full_combinations 交叉连接保证每个月的所有国家-产品组合都被统计，解决数据缺失问题
• COALESCE 将缺失的变动值替换为0，确保累计求和逻辑正确
• 纯SQL方案无需循环，利用MySQL窗口函数和CTE特性高效完成计算，性能优于存储过程

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