SQL技术问询：分组计算N件最便宜商品的平均单价

现有电商订单表orders，字段包括：

• Item_Id（int类型）：商品ID
• Qty（int类型）：订单中该商品的购买数量
• Price（int类型）：该商品的单价
• Date（timestamp类型）：订单日期

需要实现：按Item_Id和日周期分组，计算每组中N件最便宜商品的平均单价（优先选取单价最低的库存，凑够N件后计算总价除以N）。

已实现普通均价的分组SQL，但替换AVG()函数时遇到问题：

1. 自定义标量函数用游标实现逻辑，但无法在GROUP BY中调用，且游标性能差；
2. CLR聚合函数因无法支持并行合并，无法适用。

可以通过窗口函数计算累计数量，结合分组筛选实现需求，无需游标或CLR。以下是具体实现：

完整SQL代码 #

DECLARE @N INT = 10; -- 替换为你需要的目标数量

WITH grouped_orders AS (
    -- 按商品ID和日周期分组，按单价升序排序并计算累计购买量
    SELECT 
        Item_Id,
        Qty,
        Price,
        DATEADD(DAY, DATEDIFF(DAY, '2020', [Date]) / 1 * 1, '2020') AS GroupDate,
        -- 分组内按单价升序的累计数量
        SUM(Qty) OVER (PARTITION BY Item_Id, DATEDIFF(DAY, '2020', [Date]) ORDER BY Price ASC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS CumulativeQty,
        -- 分组内上一条记录的累计数量，用于判断是否需要截取部分数量
        SUM(Qty) OVER (PARTITION BY Item_Id, DATEDIFF(DAY, '2020', [Date]) ORDER BY Price ASC ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) AS PrevCumulativeQty
    FROM orders
),
valid_items AS (
    -- 筛选属于前N件的商品记录，计算有效数量和对应总价
    SELECT 
        Item_Id,
        GroupDate,
        CASE
            WHEN CumulativeQty <= @N THEN Qty
            ELSE @N - ISNULL(PrevCumulativeQty, 0)
        END AS ValidQty,
        CASE
            WHEN CumulativeQty <= @N THEN Qty * Price
            ELSE (@N - ISNULL(PrevCumulativeQty, 0)) * Price
        END AS ValidTotal
    FROM grouped_orders
    -- 只保留覆盖到N件范围的记录
    WHERE CumulativeQty - Qty < @N
)
-- 最终分组计算平均单价
SELECT 
    Item_Id,
    GroupDate AS [Date],
    -- 用CAST避免整数除法精度丢失
    CAST(SUM(ValidTotal) AS DECIMAL(18,2)) / @N AS AvgPriceOfNCheapest
FROM valid_items
GROUP BY Item_Id, GroupDate
ORDER BY Item_Id ASC, GroupDate ASC;

方案说明 #

1. 性能优化：全程使用集合运算与窗口函数，避免游标带来的性能损耗，支持SQL Server的并行执行优化；
2. 逻辑准确性：严格按单价升序优先选取商品，确保计算的是最低可能的均价；
3. 灵活性：仅需修改@N参数即可适配不同的选取数量需求，核心逻辑无需调整。

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