如何在含UNION的SQL查询中加入仅ID1、2的列平均填充率？

示例数据 #

CREATE TABLE #Temp1 (
    ID varchar(10)
    ,ColumnOne varchar(55)
    ,ColumnTwo varchar(55)
    ,ColumnThree varchar(55)
);

INSERT INTO #Temp1 (ID, ColumnOne, ColumnTwo, ColumnThree)
VALUES('1', 'John', '12345', NULL), ('2', NULL, NULL, NULL), ('3', 'Jerry', '67890', 'abcde')

对应数据表格：

初始查询结果 #

通过以下含UNION的SQL查询，可得到各ID与列的填充率（注：修正了原查询中字段名的笔误，原查询直接用A/B/C会报错，实际对应表中ColumnOne/ColumnTwo/ColumnThree字段）：

SELECT 
    ID
    ,'A' AS ColumnNM
    ,CAST(100 * AVG(CASE WHEN ColumnOne IS NOT NULL THEN 1.0 ELSE 0.0 END) AS numeric(10,2)) AS Column_fR
FROM #Temp1
GROUP BY ID

UNION

SELECT 
    ID
    ,'B' AS ColumnNM
    ,CAST(100 * AVG(CASE WHEN ColumnTwo IS NOT NULL THEN 1.0 ELSE 0.0 END) AS numeric(10,2)) AS Column_fR
FROM #Temp1
GROUP BY ID

UNION

SELECT 
    ID
    ,'C' AS ColumnNM
    ,CAST(100 * AVG(CASE WHEN ColumnThree IS NOT NULL THEN 1.0 ELSE 0.0 END) AS numeric(10,2)) AS Column_fR
FROM #Temp1
GROUP BY ID

查询结果：

需求说明 #

希望在上述查询结果的每行中，新增IDOneTwoAverage字段，该字段为仅计算ID1和ID2的对应列平均填充率，预期结果如下：

请问如何在初始的含UNION的SQL查询中实现该需求？

方法1：在每个UNION分支中直接计算目标平均值 #

写法直接，适合小数据集，每个分支独立计算ID1和ID2的对应列平均填充率：

SELECT 
    ID
    ,'A' AS ColumnNM
    ,CAST(100 * AVG(CASE WHEN ColumnOne IS NOT NULL THEN 1.0 ELSE 0.0 END) AS numeric(10,2)) AS Column_fR
    ,CAST(100 * AVG(CASE WHEN ID IN ('1','2') AND ColumnOne IS NOT NULL THEN 1.0 ELSE 0.0 END) OVER (PARTITION BY 'A') AS numeric(10,2)) AS IDOneTwoAverage
FROM #Temp1
GROUP BY ID

UNION

SELECT 
    ID
    ,'B' AS ColumnNM
    ,CAST(100 * AVG(CASE WHEN ColumnTwo IS NOT NULL THEN 1.0 ELSE 0.0 END) AS numeric(10,2)) AS Column_fR
    ,CAST(100 * AVG(CASE WHEN ID IN ('1','2') AND ColumnTwo IS NOT NULL THEN 1.0 ELSE 0.0 END) OVER (PARTITION BY 'B') AS numeric(10,2)) AS IDOneTwoAverage
FROM #Temp1
GROUP BY ID

UNION

SELECT 
    ID
    ,'C' AS ColumnNM
    ,CAST(100 * AVG(CASE WHEN ColumnThree IS NOT NULL THEN 1.0 ELSE 0.0 END) AS numeric(10,2)) AS Column_fR
    ,CAST(100 * AVG(CASE WHEN ID IN ('1','2') AND ColumnThree IS NOT NULL THEN 1.0 ELSE 0.0 END) OVER (PARTITION BY 'C') AS numeric(10,2)) AS IDOneTwoAverage
FROM #Temp1
GROUP BY ID

方法2：预先计算目标平均值再关联主查询 #

通过CTE预先计算一次ID1和ID2的各列平均值，再与主查询结果关联，减少重复计算，性能更优：

WITH ID12_Averages AS (
    SELECT
        'A' AS ColumnNM,
        CAST(100 * AVG(CASE WHEN ColumnOne IS NOT NULL THEN 1.0 ELSE 0.0 END) AS numeric(10,2)) AS AvgRate
    FROM #Temp1
    WHERE ID IN ('1','2')
    UNION ALL
    SELECT
        'B' AS ColumnNM,
        CAST(100 * AVG(CASE WHEN ColumnTwo IS NOT NULL THEN 1.0 ELSE 0.0 END) AS numeric(10,2)) AS AvgRate
    FROM #Temp1
    WHERE ID IN ('1','2')
    UNION ALL
    SELECT
        'C' AS ColumnNM,
        CAST(100 * AVG(CASE WHEN ColumnThree IS NOT NULL THEN 1.0 ELSE 0.0 END) AS numeric(10,2)) AS AvgRate
    FROM #Temp1
    WHERE ID IN ('1','2')
),
MainQuery AS (
    SELECT 
        ID
        ,'A' AS ColumnNM
        ,CAST(100 * AVG(CASE WHEN ColumnOne IS NOT NULL THEN 1.0 ELSE 0.0 END) AS numeric(10,2)) AS Column_fR
    FROM #Temp1
    GROUP BY ID
    UNION
    SELECT 
        ID
        ,'B' AS ColumnNM
        ,CAST(100 * AVG(CASE WHEN ColumnTwo IS NOT NULL THEN 1.0 ELSE 0.0 END) AS numeric(10,2)) AS Column_fR
    FROM #Temp1
    GROUP BY ID
    UNION
    SELECT 
        ID
        ,'C' AS ColumnNM
        ,CAST(100 * AVG(CASE WHEN ColumnThree IS NOT NULL THEN 1.0 ELSE 0.0 END) AS numeric(10,2)) AS Column_fR
    FROM #Temp1
    GROUP BY ID
)
SELECT 
    m.ID,
    m.ColumnNM,
    m.Column_fR,
    a.AvgRate AS IDOneTwoAverage
FROM MainQuery m
JOIN ID12_Averages a ON m.ColumnNM = a.ColumnNM
ORDER BY m.ID, m.ColumnNM;

说明 #

• 方法1写法直观，但数据量大时会重复计算ID1和ID2的平均值；
• 方法2通过CTE将目标平均值计算逻辑抽离，仅执行一次，再关联主查询，性能更高效；
• 两种方法均修正了原查询中字段名的笔误，确保SQL可正常执行。

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