如何在SQL中通过总采购额与采购次数计算平均采购金额

原SQL存在的核心问题 #

1. 别名引用限制：SQL执行顺序是FROM/JOIN → WHERE → GROUP BY → SELECT，SELECT里定义的别名（如"Total Purchases"）无法在同一层的聚合函数中直接调用，数据库计算AVG("Total Purchases")时还未生成这个别名。
2. 逻辑错误：平均采购金额的正确计算方式是总采购额 ÷ 采购次数，而非对总采购额用AVG()——单个客户的总采购额是单值，AVG()计算无意义，再加上错误的分组逻辑导致结果为0。
3. GROUP BY错误："Number of Purchases"是聚合后的别名，不能直接用于分组；按SQL标准，GROUP BY应包含所有非聚合的原始列（这里是CUS_CODE和CUS_BALANCE）。
4. 多余WHERE条件：L.INV_NUMBER = I.INV_NUMBER已经在RIGHT JOIN的ON条件中生效，加在WHERE里会将RIGHT JOIN转为INNER JOIN，过滤掉无采购记录的客户。

修正后的SQL（直接计算版） #

SELECT C.CUS_CODE,
       C.CUS_BALANCE,
       ROUND(SUM(L.LINE_UNITS * L.LINE_PRICE), 2) AS "Total Purchases",
       COUNT(L.LINE_NUMBER) AS "Number of Purchases",
       -- 处理采购次数为0的情况，避免除以0报错
       ROUND(CASE WHEN COUNT(L.LINE_NUMBER) > 0 THEN SUM(L.LINE_UNITS * L.LINE_PRICE)/COUNT(L.LINE_NUMBER) ELSE 0 END, 2) AS "Average Purchase Amount"
FROM CUSTOMER AS C
RIGHT JOIN INVOICE AS I
    ON C.CUS_CODE = I.CUS_CODE
RIGHT JOIN LINE AS L
    ON I.INV_NUMBER = L.INV_NUMBER
GROUP BY C.CUS_CODE, C.CUS_BALANCE

修正后的SQL（CTE版，可读性更高） #

如果需要更清晰的逻辑拆分，可以用CTE先计算总采购额和采购次数，再推导平均值：

WITH CustomerPurchases AS (
    SELECT C.CUS_CODE,
           C.CUS_BALANCE,
           ROUND(SUM(L.LINE_UNITS * L.LINE_PRICE), 2) AS "Total Purchases",
           COUNT(L.LINE_NUMBER) AS "Number of Purchases"
    FROM CUSTOMER AS C
    RIGHT JOIN INVOICE AS I
        ON C.CUS_CODE = I.CUS_CODE
    RIGHT JOIN LINE AS L
        ON I.INV_NUMBER = L.INV_NUMBER
    GROUP BY C.CUS_CODE, C.CUS_BALANCE
)
SELECT CUS_CODE,
       CUS_BALANCE,
       "Total Purchases",
       "Number of Purchases",
       ROUND(CASE WHEN "Number of Purchases" > 0 THEN "Total Purchases"/"Number of Purchases" ELSE 0 END, 2) AS "Average Purchase Amount"
FROM CustomerPurchases

关键修正点说明 #

• 保留RIGHT JOIN语义：若不需要包含无采购记录的客户，可改为LEFT JOIN或INNER JOIN。
• 分组逻辑合规：严格按非聚合原始列分组，避免数据库兼容性问题。
• 除以0防护：用CASE WHEN处理采购次数为0的场景，避免运行时错误。
• 直接计算平均值：通过总采购额与采购次数的商得到正确结果，而非错误使用AVG()。

内容的提问来源于stack exchange，提问作者Devin Culp Corasair