SQL查询问题：筛选累计占80%使用量的高频库存零件

问题分析 #

原WHERE子句报错的核心原因：

• 窗口函数（SUM() OVER()）无法直接在WHERE子句中使用
• 未先计算出所有零件的总使用量，无法准确判断累计占比

修正后的查询语句 #

WITH PartUsage AS (
    -- 计算单个零件使用量 + 全类别总使用量
    SELECT 
        im.StockCode,
        im.Description,
        SUM(imv.TrnQty) AS Qty,
        SUM(SUM(imv.TrnQty)) OVER () AS TotalQty
    FROM InvMaster im
    RIGHT JOIN InvMovements imv ON im.StockCode = imv.StockCode
    WHERE im.Description LIKE '%Bag/%'
    GROUP BY im.StockCode, im.Description
),
CumulativeUsage AS (
    -- 按使用量降序计算累计值
    SELECT 
        StockCode,
        Description,
        Qty,
        TotalQty,
        SUM(Qty) OVER (ORDER BY Qty DESC) AS CumulativeQty
    FROM PartUsage
)
-- 筛选累计覆盖80%总使用量的零件
SELECT 
    StockCode,
    Description,
    Qty
FROM CumulativeUsage
WHERE CumulativeQty <= TotalQty * 0.8
ORDER BY Qty DESC;

关键修改说明 #

1. 使用CTE分步计算：将逻辑拆分为三个部分，避免在WHERE子句中直接使用窗口函数，同时让查询逻辑更易读
2. 计算总使用量：通过SUM(SUM(imv.TrnQty)) OVER ()一次性算出当前类别下所有零件的总使用量
3. 累计用量计算：在第二个CTE中用窗口函数按使用量降序累加，得到每个零件对应的累计使用量
4. 筛选条件生效：在最终查询中直接用累计用量与总用量的80%做比较，实现需求

额外提示 #

原查询中的top 1000可以移除，因为我们需要的是自然累计到80%的零件集合，不需要提前限制条数；如果数据量极大，可保留该限制，但需确保不会截断累计到80%所需的零件。

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