计算各配送分支订单行平均数量时遇SQL聚合函数错误

我需要计算各配送分支（DC）的已售商品订单行平均数量，最初使用如下SQL查询：

SELECT 
    dc.dcname, AVG(si.RowOrder) AS Average
FROM
    gnr.tblDC dc
INNER JOIN 
    sle.tblSaleHdr sh ON sh.DCRef = dc.id
INNER JOIN 
    sle.tblSaleItm si ON si.HdrRef = sh.id
WHERE 
    sh.Year = 2023
    AND sh.exitref IS NOT NULL
    AND sh.SaleDate BETWEEN '2023/01/01' AND '2023/10/30'
GROUP BY 
    dc.DCName

但该查询结果不准确，手动计算正确值为3.82，而查询返回4.42。

我尝试改用包含COUNT的AVG写法：

SELECT 
    dc.dcname, AVG(count(si.RowOrder)) AS Average
FROM 
    gnr.tblDC dc
INNER JOIN 
    sle.tblSaleHdr sh ON sh.DCRef = dc.id
INNER JOIN 
    sle.tblSaleItm si ON si.HdrRef = sh.id
WHERE 
    sh.Year = 2023
    AND sh.exitref IS NOT NULL
    AND sh.SaleDate BETWEEN '2023/01/01' AND '2023/10/30'
GROUP BY 
    dc.DCName

此时出现错误：

Cannot perform an aggregate function on an expression containing an aggregate or a subquery

补充表结构数据 #

gnr.tbldc: #

ID  DCCode  DCName
------------------------
0   0        HQ
1   14       Guilan
9   31       Esfahan

sle.tblSaleHdr #

ID    SaleNo  OrderRef    ExitRef SaleDate    TotalAmount UserRef ChangeDate  Year    DCRef   
-----------------------------------------------------------------------------------------------------
650135799   15727   650127693   1008    2023/10/10  48093773.00 1334    2023/10/18  2023    9   
650135800   15728   650127694   1009    2023/10/10  22845240.00 1334    2023/10/18  2023    9

sle.tblSaleItm #

ID      HdrRef    RowOrder    GoodsRef    UnitRef UnitCapasity    UnitQty TotalQty    AmountNut   Discount    Amount
--------------------------------------------------------------------------------------------------------------------------------    
650430979   650135799   1   328 0   50.000  1.000   50.000  10169500.00 1655500.00  11825000.00 
650430980   650135799   2   329 0   50.000  1.000   50.000  10169500.00 1655500.00  11825000.00 
650430981   650135799   3   769 0   20.000  1.000   20.000  1651200.00  268800.00   1920000.00  
650430982   650135799   4   480 0   20.000  1.000   20.000  1651200.00  268800.00   1920000.00  
650430983   650135799   5   481 0   20.000  1.000   20.000  1651200.00  268800.00   1920000.00  
650430984   650135799   6   504 0   96.000  1.000   96.000  4499973.00  499995.00   4999968.00  
650430985   650135799   7   832 0   24.000  1.000   24.000  1925280.00  366720.00   2292000.00  
650430986   650135799   8   191 0   40.000  1.000   40.000  2413920.00  154080.00   2568000.00  
650430987   650135799   9   785 0   36.000  1.000   36.000  1099800.00  70200.00    1170000.00  
650430988   650135799   10  765 0   36.000  1.000   36.000  3246948.00  207252.00   3454200.00  
650430989   650135799   11  1072    0   48.000  1.000   48.000  1987632.00  323568.00   2311200.00  
650430990   650135799   12  722 0   36.000  1.000   36.000  1529910.00  189090.00   1719000.00  
650430991   650135799   13  409 0   24.000  1.000   24.000  1971120.00  320880.00   2292000.00  
650430992   650135799   14  145 0   36.000  1.000   36.000  1553580.00  172620.00   1726200.00  
650430993   650135799   15  1081    0   36.000  1.000   36.000  1553580.00  172620.00   1726200.00  
650430994   650135799   16  503 0   90.000  1.000   90.000  1019430.00  65070.00    1084500.00  
650430995   650135800   1   1168    0   48.000  5.000   240.000 14120160.00 1227840.00  15348000.00 
650430996   650135800   2   1152    0   48.000  5.000   240.000 8725080.00  862920.00   9588000.00  

问题分析与解决方案 #

第一个查询结果错误原因 #

AVG(si.RowOrder)计算的是所有订单行的RowOrder字段值的平均值，而RowOrder是每个订单内部的行号（比如示例中第一个订单行号从1到16，第二个从1到2），这和我们需要的「每个订单的平均行数」完全不是同一个统计维度。

第二个查询报错原因 #

SQL不允许在同一层GROUP BY中嵌套聚合函数（如AVG(COUNT(...))），必须先在子查询中计算出每个订单的行数，再在外层对这些数值求平均。

正确SQL写法 #

写法一：子查询先统计每个订单的行数

SELECT 
    dc.dcname, 
    AVG(order_item_count) AS Average
FROM
    gnr.tblDC dc
INNER JOIN (
    -- 子查询：计算每个符合条件的订单的行数
    SELECT 
        sh.DCRef, 
        COUNT(*) AS order_item_count
    FROM 
        sle.tblSaleHdr sh
    INNER JOIN 
        sle.tblSaleItm si ON si.HdrRef = sh.id
    WHERE 
        sh.Year = 2023
        AND sh.exitref IS NOT NULL
        AND sh.SaleDate BETWEEN '2023/01/01' AND '2023/10/30'
    GROUP BY 
        sh.id, sh.DCRef
) AS order_item_counts ON order_item_counts.DCRef = dc.id
GROUP BY 
    dc.DCName

写法二：总订单行数除以总订单数

直接计算每个DC下的总订单行数，除以该DC下的订单总数（用COUNT(DISTINCT sh.id)去重统计订单数），得到平均每行订单的数量：

SELECT 
    dc.dcname, 
    -- 乘以1.0确保结果为小数，避免整数除法丢失精度
    COUNT(si.id) * 1.0 / COUNT(DISTINCT sh.id) AS Average
FROM
    gnr.tblDC dc
INNER JOIN 
    sle.tblSaleHdr sh ON sh.DCRef = dc.id
INNER JOIN 
    sle.tblSaleItm si ON si.HdrRef = sh.id
WHERE 
    sh.Year = 2023
    AND sh.exitref IS NOT NULL
    AND sh.SaleDate BETWEEN '2023/01/01' AND '2023/10/30'
GROUP BY 
    dc.DCName

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