MySQL累计库存查询补全无变动日期数据及性能优化求助

问题背景 #

我们有一张超过1200万行的movimientos_stock表，记录WMS系统所有产品自启用以来的每一笔出入库数据。当前可通过以下查询获取实时库存：

SELECT
    ms.codigo_art,
    SUM(ms.cantidad) AS stock
FROM movimientos_stock AS ms
GROUP BY ms.codigo_art;

其中codigo_art为产品ID，fecha为日期时间字段。

现有查询能计算每日累计库存，但缺失产品无库存变动日期的记录（比如示例中缺少2020-10-06的库存行）：

SELECT
    ms.fecha,
    ms.codigo,
    SUM(ms.cantidad) OVER(PARTITION BY ms.codigo ORDER BY ms.fecha) as Stock
FROM 
    (SELECT DATE(ms1.fecha) AS fecha, ms1.codigo_art as codigo, sum(ms1.cantidad) as cantidad
    FROM movimientos_stock AS ms1
    GROUP BY 1,2) AS ms
ORDER BY 1,2;

尝试用DISTINCT fecha与DISTINCT codigo_art交叉连接再左联补全数据时，Google Cloud数据库CPU直接飙升至100%，现有资源无法支撑，需高效解决方案。

解决方案 #

1. 生成轻量级日期序列与产品列表 #

避免直接对全量DISTINCT结果做交叉连接，先锁定需要覆盖的日期范围（从最早库存变动日到当前日）生成连续日期序列，同时提取所有有库存记录的产品ID，减少无效组合：

WITH date_series AS (
    SELECT date_range AS fecha
    FROM UNNEST(GENERATE_DATE_ARRAY(
        (SELECT MIN(DATE(fecha)) FROM movimientos_stock),
        CURRENT_DATE(),
        INTERVAL 1 DAY
    )) AS date_range
),
product_list AS (
    SELECT DISTINCT codigo_art AS codigo
    FROM movimientos_stock
)

2. 关联数据并填充缺失库存 #

将日期序列、产品列表与每日库存变动汇总左联，用窗口函数向前填充缺失日期的库存值，避免重复计算累计和：

WITH date_series AS (
    SELECT date_range AS fecha
    FROM UNNEST(GENERATE_DATE_ARRAY(
        (SELECT MIN(DATE(fecha)) FROM movimientos_stock),
        CURRENT_DATE(),
        INTERVAL 1 DAY
    )) AS date_range
),
product_list AS (
    SELECT DISTINCT codigo_art AS codigo
    FROM movimientos_stock
),
daily_movements AS (
    SELECT 
        DATE(fecha) AS fecha,
        codigo_art AS codigo,
        SUM(cantidad) AS daily_change
    FROM movimientos_stock
    GROUP BY DATE(fecha), codigo_art
),
combined_data AS (
    SELECT 
        ds.fecha,
        pl.codigo,
        COALESCE(dm.daily_change, 0) AS daily_change
    FROM date_series ds
    CROSS JOIN product_list pl
    LEFT JOIN daily_movements dm 
        ON ds.fecha = dm.fecha AND pl.codigo = dm.codigo
)
SELECT 
    fecha,
    codigo,
    LAST_VALUE(SUM(daily_change) OVER (PARTITION BY codigo ORDER BY fecha) IGNORE NULLS) 
        OVER (PARTITION BY codigo ORDER BY fecha ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS stock
FROM combined_data
ORDER BY fecha, codigo;

3. 性能优化关键点 #

• 给movimientos_stock表创建(codigo_art, fecha)复合索引，加速分组和关联操作。
• 按需限制日期范围：若无需全量历史数据，在GENERATE_DATE_ARRAY中指定起始/结束日期，缩小笛卡尔积规模。
• 预存产品列表：将product_list的结果持久化到小表，避免每次查询重复计算DISTINCT codigo_art。

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