SQL按客户计算订单平均值的实现问题

看起来你遇到的核心问题是把订单行级别的平均当成了订单级别的平均——你现在的查询直接对所有OrderLine的Qty*Price求平均，但实际上需要先算出每个订单的总金额，再对这些订单总额求平均值，这才是你想要的客户所有订单的平均金额。

先明确你的需求和数据背景：

• 你需要按「客户+单个订单」的维度展示数据，包含：单个订单的总金额、该客户的订单总数、该客户所有订单的总金额、该客户所有订单的平均金额（总金额÷订单数）
• 从你给出的示例数据看，客户10073有3个订单，总金额1243.27，所以平均金额是1243.27÷3≈414.42333，这正是你期望的结果。

为什么原来的查询得不到正确的平均值？ #

你原来的AVG子查询是直接对该客户所有OrderLine的Qty*Price做平均，相当于把所有订单行的金额拉平求平均，而不是先计算每个订单的总额，再对订单的总额求平均，所以结果会远低于预期。

解决方案：先聚合订单，再计算客户级平均 #

我们可以用CTE（公共表表达式）先算出每个订单的总金额，再基于订单级数据计算客户的聚合指标，最后关联得到结果：

WITH OrderTotals AS (
    -- 第一步：计算每个订单的总金额（订单级聚合）
    SELECT 
        O.CustId,
        O.OrderId,
        SUM(OL.Qty * OL.Price) AS SUMOrder
    FROM [Order] O
    JOIN OrderLine OL ON O.OrderId = OL.orderid
    GROUP BY O.CustId, O.OrderId
),
CustomerSummary AS (
    -- 第二步：计算每个客户的订单总数、总金额、平均订单金额（客户级聚合）
    SELECT 
        CustId,
        COUNT(OrderId) AS Countorders,
        SUM(SUMOrder) AS SumAllOrders,
        AVG(SUMOrder) AS AVGAllOrders
    FROM OrderTotals
    GROUP BY CustId
)
-- 第三步：关联两个CTE，得到最终的「客户+订单」维度数据
SELECT 
    OT.CustId,
    OT.OrderId,
    OT.SUMOrder,
    COS.AVGAllOrders,
    COS.Countorders,
    COS.SumAllOrders
FROM OrderTotals OT
JOIN CustomerSummary COS ON OT.CustId = COS.CustId
ORDER BY OT.CustId, OT.OrderId;

更简洁的窗口函数写法 #

如果你想用更简洁的方式，可以直接用窗口函数在订单级数据上计算客户级的聚合值，不需要额外的客户级CTE：

WITH OrderTotals AS (
    SELECT 
        O.CustId,
        O.OrderId,
        SUM(OL.Qty * OL.Price) AS SUMOrder
    FROM [Order] O
    JOIN OrderLine OL ON O.OrderId = OL.orderid
    GROUP BY O.CustId, O.OrderId
)
SELECT 
    CustId,
    OrderId,
    SUMOrder,
    -- 按客户分组，计算所有订单的平均金额
    AVG(SUMOrder) OVER (PARTITION BY CustId) AS AVGAllOrders,
    -- 按客户分组，计算订单总数
    COUNT(OrderId) OVER (PARTITION BY CustId) AS Countorders,
    -- 按客户分组，计算所有订单的总金额
    SUM(SUMOrder) OVER (PARTITION BY CustId) AS SumAllOrders
FROM OrderTotals
ORDER BY CustId, OrderId;

结果验证 #

用你的示例数据测试的话：

• 订单VOR0060751的总金额是161.29，和你预期的一致
• 客户10073的三个订单总额为161.29+702.04+318.19=1243.27，平均金额为1243.27÷3≈414.42333，完全匹配你想要的结果。

内容来源于stack exchange