按商品及业务周计算期末库存累计值的技术实现问询

嘿，我明白你现在的困扰——要按每个商品、每个业务周末来计算库存，试过WITH ROLLUP和@runtot:=变量累计但没成功对吧？其实WITH ROLLUP更适合分组后的汇总统计，而你需要的是按商品维度、时间序列的累计库存，用窗口函数（MySQL 8.0+）或者变量（MySQL 5.x）会更靠谱。下面给你详细拆解可行方案：

先明确前提假设 #

假设你的交易表名为transactions，结构大概是这样的：

• product_id：商品唯一ID
• transaction_date：交易日期（DATE类型）
• quantity：交易数量（入库为正整数，出库为负整数）
• 业务周末定义为每周六（如果是周五/周日，调整DAYOFWEEK的参数即可）

方案1：MySQL 8.0+（推荐，用窗口函数） #

窗口函数是MySQL 8.0以后新增的特性，比变量写法更清晰、不易出错。步骤如下：

1. 生成所有需要统计的业务周末日期 #

先从交易表中提取所有周六的日期，如果需要包含没有交易的周末，可以用递归CTE生成完整日期序列再筛选周六：

WITH weekends AS (
    -- 方式1：从交易表中提取已有交易的周六
    SELECT DISTINCT transaction_date AS weekend_date
    FROM transactions
    WHERE DAYOFWEEK(transaction_date) = 7 -- DAYOFWEEK中周日=1，周六=7；周五=6，周日=1
    
    -- 方式2：生成指定时间范围内的所有周六（适合需要补全空周末的场景）
    -- WITH RECURSIVE date_range AS (
    --     SELECT MIN(transaction_date) AS dt FROM transactions
    --     UNION ALL
    --     SELECT DATE_ADD(dt, INTERVAL 1 DAY) FROM date_range WHERE dt < (SELECT MAX(transaction_date) FROM transactions)
    -- )
    -- SELECT dt AS weekend_date FROM date_range WHERE DAYOFWEEK(dt) = 7
),

2. 计算每个商品的累计库存 #

用SUM() OVER()窗口函数按商品分组、按日期排序，计算截至每个日期的累计库存：

product_running_inventory AS (
    SELECT
        product_id,
        transaction_date,
        SUM(quantity) OVER (PARTITION BY product_id ORDER BY transaction_date) AS running_inventory
    FROM transactions
)

3. 关联周末日期，提取每个周末的库存 #

把商品和所有周末做交叉连接，左连接累计库存数据，用LAST_VALUE()取截至该周末的最新库存值（即使周末当天没有交易，也能拿到最近的库存）：

SELECT
    p.product_id,
    w.weekend_date,
    LAST_VALUE(COALESCE(pr.running_inventory, 0)) OVER (
        PARTITION BY p.product_id 
        ORDER BY w.weekend_date 
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS weekend_inventory
FROM weekends w
CROSS JOIN (SELECT DISTINCT product_id FROM transactions) p
LEFT JOIN product_running_inventory pr 
    ON pr.product_id = p.product_id 
    AND pr.transaction_date <= w.weekend_date
ORDER BY p.product_id, w.weekend_date;

方案2：MySQL 5.x（用变量实现累计） #

如果你的MySQL版本低于8.0，只能用用户变量来模拟累计逻辑，注意要严格按商品和日期排序：

SELECT
    product_id,
    weekend_date,
    @running_inv := CASE
        WHEN @current_product = product_id THEN @running_inv + daily_total
        ELSE daily_total
    END AS weekend_inventory,
    @current_product := product_id
FROM (
    -- 先按商品、日期汇总每日交易，再关联所有周末日期
    SELECT
        p.product_id,
        w.weekend_date,
        COALESCE(SUM(t.quantity), 0) AS daily_total
    FROM (SELECT DISTINCT product_id FROM transactions) p
    CROSS JOIN (
        SELECT DISTINCT transaction_date AS weekend_date
        FROM transactions
        WHERE DAYOFWEEK(transaction_date) =7
    ) w
    LEFT JOIN transactions t 
        ON t.product_id = p.product_id 
        AND t.transaction_date <= w.weekend_date
    GROUP BY p.product_id, w.weekend_date
    ORDER BY p.product_id, w.weekend_date
) AS sorted_data
CROSS JOIN (SELECT @current_product := NULL, @running_inv := 0) AS init_vars;

关键注意事项 #

1. 业务周末定义：调整DAYOFWEEK的参数，比如周五是6，周日是1；如果是自定义的“业务周末”（比如每月最后一个周五），需要修改周末日期的生成逻辑。
2. 初始库存：如果交易表之前有初始库存，需要加一个初始库存表（比如initial_inventory），在累计时加上初始值（比如running_inventory + initial_qty）。
3. 空周末处理：如果需要显示没有交易的周末的库存，用递归CTE生成完整的周末日期序列，避免遗漏。

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