如何在SQL中按月计算子查询列的整体平均值以对比业绩？

问题说明 #

子查询已统计出每个销售代表（repid）每月的Total Prepaid ACTs、Total Postpaid ACTs等业绩数据，需要在子查询外按月末日期（EOM）计算当月所有rep的Total Prepaid ACTs平均值，用于对比单个rep业绩与当月整体水平。但当前代码的AVG函数因外层GROUP BY包含了每个rep的所有字段，仅计算单行值，导致Average Prepaid等于对应行的Total Prepaid ACTs，无法得到预期的月度整体均值。

原SQL代码 #

SELECT
    *
    , AVG(PM.[Total Prepaid ACTs]) AS 'Average Prepaid'
FROM
    (
    SELECT
        WT.repid
        , ISNULL(SUM(NetPrePaidActivations), 0) AS 'Total Prepaid ACTs'
        , ISNULL(SUM(NetPostpaidIncBYOD), 0) AS 'Total Postpaid ACTs'
        , CAST(ROUND(ISNULL(SUM(AccessorySales), 0), 2) AS FLOAT) AS 'Total Acc Sales'
        , ISNULL(SUM(AccessoryUnits), 0) AS 'Total Acc Units'
        , EOMONTH(WT.ActivityDate, 0) AS 'EOM'
    FROM
        WalmartWSP.dbo.WalmartTransactions WT
    LEFT JOIN
        WalmartWSP.dbo.RepRoster RR
    ON
        WT.repid = RR.RepID
    WHERE
        WT.repid <> 0
        AND
        RR.RepHireDate BETWEEN '06-01-2022' AND '12-31-2022'
        AND
        WT.ActivityDate BETWEEN '06-01-2022' AND '12-31-2022'
    GROUP BY
        WT.repid
        , EOMONTH(WT.ActivityDate, 0)
    ) PM --ProductivityMetrics
GROUP BY
    PM.EOM
    , PM.repid
    , PM.[Total Acc Sales]
    , PM.[Total Acc Units]
    , PM.[Total Postpaid ACTs]
    , PM.[Total Prepaid ACTs]

当前结果 #

repid   Total Prepaid ACTs    EOM           Average Prepaid
369928  5                     2022-10-31    5
373049  17                    2022-11-30    17
369579  0                     2022-10-31    0
361235  22                    2022-11-30    22
370359  6                     2022-11-30    6

期望结果 #

repid   Total Prepaid ACTs    EOM           Average Prepaid
369928  5                     2022-10-31    2.5
373049  17                    2022-11-30    15.0
369579  0                     2022-10-31    2.5
361235  22                    2022-11-30    15.0
370359  6                     2022-11-30    15.0

解决方案 #

使用窗口函数替代原有AVG+GROUP BY的方式，通过PARTITION BY EOM按月末日期分组计算整体均值，无需外层GROUP BY（子查询已完成rep+月度的分组）。

修正后的SQL代码 #

SELECT
    PM.*
    , AVG(PM.[Total Prepaid ACTs]) OVER (PARTITION BY PM.EOM) AS 'Average Prepaid'
FROM
    (
    SELECT
        WT.repid
        , ISNULL(SUM(NetPrePaidActivations), 0) AS 'Total Prepaid ACTs'
        , ISNULL(SUM(NetPostpaidIncBYOD), 0) AS 'Total Postpaid ACTs'
        , CAST(ROUND(ISNULL(SUM(AccessorySales), 0), 2) AS FLOAT) AS 'Total Acc Sales'
        , ISNULL(SUM(AccessoryUnits), 0) AS 'Total Acc Units'
        , EOMONTH(WT.ActivityDate, 0) AS 'EOM'
    FROM
        WalmartWSP.dbo.WalmartTransactions WT
    LEFT JOIN
        WalmartWSP.dbo.RepRoster RR
    ON
        WT.repid = RR.RepID
    WHERE
        WT.repid <> 0
        AND
        RR.RepHireDate BETWEEN '06-01-2022' AND '12-31-2022'
        AND
        WT.ActivityDate BETWEEN '06-01-2022' AND '12-31-2022'
    GROUP BY
        WT.repid
        , EOMONTH(WT.ActivityDate, 0)
    ) PM --ProductivityMetrics

代码解释 #

• AVG(...) OVER (PARTITION BY PM.EOM)：窗口函数会将数据按EOM（月末日期）分组，对每个组内所有rep的Total Prepaid ACTs计算平均值，然后将该平均值附加到组内的每一行上，实现每个rep的行都能显示当月整体均值的需求。
• 去掉了外层的GROUP BY：子查询已经按repid和EOM完成分组，外层只需直接查询子查询结果并添加窗口函数计算的均值即可，避免了原有GROUP BY导致的单行聚合问题。

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