如何统计均值上方3倍标准差的行数？SQL报错求助

你的问题出在普通聚合函数（AVG/STDEV）和非聚合列（ID/Dept等）不能同时出现在SELECT中而不加GROUP BY，而你需要的是全表级别的统计值，不是分组统计，所以有两种可行的解决方式：

方法1：单独计算全局统计值后关联 #

先单独计算全表的均值、标准差和3倍标准差阈值，再通过交叉关联把阈值应用到每一行数据上：

;WITH GlobalStats AS
(
    SELECT
        AVG([Frequency]) AS freq_avg,
        STDEV([Frequency]) AS freq_stdev,
        AVG([Amount]) AS amt_avg,
        STDEV([Amount]) AS amt_stdev,
        AVG([Frequency]) + 3*STDEV([Frequency]) AS freq_3sd,
        AVG([Amount]) + 3*STDEV([Amount]) AS amt_3sd
    FROM MyTable
)
SELECT COUNT(*)
FROM MyTable t
CROSS JOIN GlobalStats gs
WHERE t.[Frequency] >= gs.freq_3sd
  AND t.[Amount] >= gs.amt_3sd

GlobalStats只会生成一行包含全表统计值的数据，CROSS JOIN会把原表的每一行都和这行统计值配对，这样就能直接用阈值过滤符合条件的行。

方法2：使用窗口函数计算全局统计值 #

用OVER ()窗口子句让聚合函数计算全表范围的统计值，这样不需要GROUP BY就能在每行数据上带上全局阈值：

;WITH CTE AS
(
    SELECT
        t.*,
        AVG([Frequency]) OVER () AS freq_avg,
        STDEV([Frequency]) OVER () AS freq_stdev,
        AVG([Amount]) OVER () AS amt_avg,
        STDEV([Amount]) OVER () AS amt_stdev,
        AVG([Frequency]) OVER () + 3*STDEV([Frequency]) OVER () AS freq_3sd,
        AVG([Amount]) OVER () + 3*STDEV([Amount]) OVER () AS amt_3sd
    FROM MyTable t
)
SELECT COUNT(*)
FROM CTE
WHERE [Frequency] >= freq_3sd
  AND [Amount] >= amt_3sd

OVER ()指定窗口为整个数据集，所以每个行都会得到相同的全表统计值，完美避开GROUP BY的要求，写法更简洁。

两种方法都能实现你的需求，窗口函数写法更直观，而交叉关联的方式在大数据集上性能差异不大，可根据习惯选择。

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