SQL计算售出商品出库量移动平均结果与预期不符求助

核心错误原因 #

• 窗口函数PARTITION BY错误包含inqty字段：出库行的inqty=0，和入库行的非0inqty被划分到不同分区，计算移动平均时无法关联到之前的入库数据
• 直接对包含0的原始ItemValue计算均值：出库行本身ItemValue=0，如果参与均值计算会导致结果不符合出库成本取值逻辑

修正后的SQL脚本 #

SELECT * FROM (
    SELECT 
        transactdatee,
        itcode,
        gname,
        inqty,
        ItemValue,
        OutQty,
        -- 移动平均仅针对入库值计算，忽略出库的0值
        AVG(CASE WHEN inqty > 0 THEN ItemValue ELSE NULL END) OVER (
            -- 按商品唯一维度分区，移除多余的inqty字段
            PARTITION BY itcode, gname 
            ORDER BY TransactDatee, inqty ASC 
            ROWS BETWEEN 2 PRECEDING AND CURRENT ROW 
        ) AS MovingAvg,
        ISNULL(
            CASE WHEN OutQty <> 0 THEN 
                AVG(CASE WHEN inqty > 0 THEN ItemValue ELSE NULL END) OVER (
                    PARTITION BY itcode, gname 
                    ORDER BY TransactDatee, inqty ASC 
                    ROWS BETWEEN 2 PRECEDING AND CURRENT ROW 
                ) 
            END,
        0) AS Movingaverageforout 
    FROM mak_stockInHandValue
    WHERE gname LIKE '%bahria town%' AND itcode = 2983
) a
ORDER BY a.transactdatee ASC

逻辑说明 #

修正后的逻辑先通过CASE WHEN过滤掉出库行的0值ItemValue，AVG函数计算时会自动忽略NULL值，同时移除了partition by中的inqty字段，保证同一个商品的出入库记录在同一个分区内计算，出库时就能取到之前最近3次入库的移动平均成本，和你期望的输出结果一致。

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