如何在SQL中计算逐行累积乘积（生产场景需求）

核心思路 #

要计算逐行的累积报废率，本质是按机器分组，对每道工序的产出/投入值做累积乘积。利用对数的数学性质：乘积的对数等于对数的和，再通过指数函数还原乘积，结合窗口函数即可实现逐行计算。

具体SQL实现 #

假设你的表结构为：production_data(MachineID VARCHAR, ProcessSeq INT, InputQty NUMERIC, OutputQty NUMERIC)

SELECT
    MachineID,
    ProcessSeq,
    InputQty,
    OutputQty,
    -- 计算单工序损耗系数，用NULLIF避免除以0报错
    OutputQty / NULLIF(InputQty, 0) AS OutFactor,
    -- 计算累积损耗系数：按机器分组，按工序顺序累加对数后取指数
    EXP(
        SUM(
            LOG(OutputQty / NULLIF(InputQty, 0))
        ) OVER (
            PARTITION BY MachineID 
            ORDER BY ProcessSeq 
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
        )
    ) AS OutFactorAccumulated
FROM production_data;

关键部分解释 #

• PARTITION BY MachineID：限定累积计算仅在同一机器的工序组内进行，不同机器的数据完全隔离。
• ORDER BY ProcessSeq：保证工序按生产先后顺序排序，确保累积逻辑从第一道工序开始到当前工序。
• ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW：明确窗口范围为当前分组的第一行到当前行，部分数据库默认行为一致，但显式声明更清晰可靠。
• NULLIF(InputQty, 0)：避免投入数量为0时触发除以0的错误，此时OutFactor返回NULL，对应的累积值也会是NULL，可根据业务需求用CASE调整（比如设为0）。

特殊情况处理 #

如果存在工序产出为0的情况，LOG(0)会报错，可通过CASE语句提前处理：

SELECT
    MachineID,
    ProcessSeq,
    InputQty,
    OutputQty,
    CASE 
        WHEN InputQty = 0 OR OutputQty = 0 THEN 0
        ELSE OutputQty / InputQty 
    END AS OutFactor,
    EXP(
        SUM(
            LOG(
                CASE 
                    WHEN InputQty = 0 OR OutputQty = 0 THEN 1  -- 用1替代0避免LOG报错，后续通过外层CASE修正累积值
                    ELSE OutputQty / InputQty 
                END
            )
        ) OVER (
            PARTITION BY MachineID 
            ORDER BY ProcessSeq 
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
        )
    ) * CASE WHEN EXISTS (
        SELECT 1 FROM production_data pd 
        WHERE pd.MachineID = production_data.MachineID 
          AND pd.ProcessSeq <= production_data.ProcessSeq 
          AND (pd.InputQty = 0 OR pd.OutputQty = 0)
    ) THEN 0 ELSE 1 END AS OutFactorAccumulated
FROM production_data;

这段代码会在某道工序出现无效值（投入/产出为0）时，将该工序及之后的累积报废率直接设为0，符合业务逻辑。

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