如何在Microsoft SQL Server 2019中计算多列平均累积值？

场景说明 #

使用Microsoft SQL Server 2019，现有如下测试表：

需要生成包含三个统计值的结果表：

计算逻辑 #

• mean12：先分别计算Q1、Q2列的ROUND(AVG(列), 0)，再对这两个值求ROUND(平均值, 0)
• mean123：对Q1、Q2、Q3列执行相同逻辑
• mean1234：对Q1、Q2、Q3、Q4列执行相同逻辑，若某列全为NULL则忽略该列，仅计算有有效数据列的平均值

示例mean12计算过程：

ROUND((ROUND((43+35+32+32+18+57)/6) + ROUND((-3+33+35+39-20+14+60)/7))/2) = 
ROUND((ROUND(36.16) + ROUND(22.57))/2) =
ROUND((36+23)/2) = ROUND(29.5) = 30

原代码问题 #

尝试使用以下SQL时，因Q4列全为NULL，ROUND(AVG(Q4),0)返回NULL，导致mean1234结果为NULL，无法得到预期的29：

SELECT 
    ROUND(((ROUND(AVG(Q1), 0) + ROUND(AVG(Q2), 0)) / 2), 0) AS Mean12,
    ROUND(((ROUND(AVG(Q1), 0) + ROUND(AVG(Q2), 0) + ROUND(AVG(Q3), 0)) / 3), 0) AS Mean123,
    ROUND(((ROUND(AVG(Q1), 0) + ROUND(AVG(Q2), 0) + ROUND(AVG(Q3), 0) + ROUND(AVG(Q4), 0)) / 4), 0) AS Mean1234

解决方案 #

方法1：使用UNPIVOT转换列到行（推荐） #

通过UNPIVOT将列转换为行，自动过滤全NULL的列，再分别计算不同列组合的平均值：

WITH ColumnAvgs AS (
    SELECT
        col,
        ROUND(AVG(val), 0) AS avg_val
    FROM test
    UNPIVOT (
        val FOR col IN (Q1, Q2, Q3, Q4)
    ) AS up
    GROUP BY col
)
SELECT
    ROUND(AVG(CASE WHEN col IN ('Q1','Q2') THEN avg_val END), 0) AS mean12,
    ROUND(AVG(CASE WHEN col IN ('Q1','Q2','Q3') THEN avg_val END), 0) AS mean123,
    ROUND(AVG(avg_val), 0) AS mean1234
FROM ColumnAvgs;

方法2：手动判断非NULL列数量 #

先计算各列的平均取值，再通过条件判断统计有效列的数量，避免NULL值影响计算：

WITH ColumnAvgs AS (
    SELECT
        ROUND(AVG(Q1), 0) AS q1_avg,
        ROUND(AVG(Q2), 0) AS q2_avg,
        ROUND(AVG(Q3), 0) AS q3_avg,
        ROUND(AVG(Q4), 0) AS q4_avg
    FROM test
)
SELECT
    ROUND((q1_avg + q2_avg) / 2.0, 0) AS mean12,
    ROUND((q1_avg + q2_avg + q3_avg) / 3.0, 0) AS mean123,
    ROUND(
        (ISNULL(q1_avg, 0) + ISNULL(q2_avg, 0) + ISNULL(q3_avg, 0) + ISNULL(q4_avg, 0))
        / NULLIF(
            CASE WHEN q1_avg IS NOT NULL THEN 1 ELSE 0 END +
            CASE WHEN q2_avg IS NOT NULL THEN 1 ELSE 0 END +
            CASE WHEN q3_avg IS NOT NULL THEN 1 ELSE 0 END +
            CASE WHEN q4_avg IS NOT NULL THEN 1 ELSE 0 END,
            0
        ),
        0
    ) AS mean1234
FROM ColumnAvgs;

附建表及插入数据代码 #

CREATE TABLE test(
   name VARCHAR(5) PRIMARY KEY
  ,Q1   INTEGER 
  ,Q2   INTEGER 
  ,Q3   INTEGER 
  ,Q4   INT 
);
INSERT INTO test(name,Q1,Q2,Q3,Q4) VALUES
 ('test1',43,-3,NULL,NULL)
,('test2',35,33,NULL,NULL)
,('test3',32,35,21,NULL)
,('test4',32,39,NULL,NULL)
,('test5',NULL,NULL,NULL,NULL)
,('test6',18,-20,37,NULL)
,('test7',NULL,14,NULL,NULL)
,('test8',57,60,NULL,NULL);

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