如何基于GroupCD条件扩展SQL分组查询，生成多列SurrogateKEY填充率？

临时表定义及示例数据 #

CREATE TABLE #TempTable (
ID varchar(10),
GroupCD varchar(10),
SurrogateKEY1 varchar(10),
SurrogateKEY2 varchar(10)
)
INSERT INTO #TempTable (ID, GroupCD, SurrogateKEY1, SurrogateKEY2)
VALUES 
    ('1', 'UNK', '12345', '89225'),
    ('3', 'ABC', NULL, '44658'),
    ('3', 'DEF', NULL, '99658'),
    ('5', 'ABC', '09184', NULL),
    ('4', 'DEF', NULL, '85598'),
    ('1', 'GHI', '80642', '77890')

对应数据表格：

现有实现的SQL及结果 #

已实现按ID分组计算SurrogateKEY列填充率的SQL：

SELECT
    ID,
    CAST(SUM(CASE WHEN SurrogateKEY1 IS NULL THEN 0 ELSE 1 END) / CAST(COUNT(1) AS FLOAT) * 100 AS Decimal(8,3)) AS SurrogateKEY1_fR,
    CAST(SUM(CASE WHEN SurrogateKEY2 IS NULL THEN 0 ELSE 1 END) / CAST(COUNT(1) AS FLOAT) * 100 AS Decimal(8,3)) AS SurrogateKEY2_fR
FROM #TempTable
GROUP BY ID

输出结果：

需求扩展 #

需要扩展查询，基于GroupCD='ABC'和非ABC的条件，分别计算每个ID对应的SurrogateKEY1和SurrogateKEY2的填充率，期望输出如下：

解决方案 #

可以通过在统计逻辑中嵌套CASE语句过滤GroupCD的范围，同时处理分母为0的情况，最终得到符合要求的结果：

SELECT
    ID,
    -- 计算非ABC分组的SurrogateKEY1填充率
    CAST(COALESCE(
        SUM(CASE WHEN GroupCD != 'ABC' AND SurrogateKEY1 IS NOT NULL THEN 1 ELSE 0 END) 
        / NULLIF(CAST(COUNT(CASE WHEN GroupCD != 'ABC' THEN 1 END) AS FLOAT), 0) 
        * 100, 
        0
    ) AS Decimal(8,3)) AS SurrogateKEY1_fR_NonABC,
    
    -- 计算ABC分组的SurrogateKEY1填充率
    CAST(COALESCE(
        SUM(CASE WHEN GroupCD = 'ABC' AND SurrogateKEY1 IS NOT NULL THEN 1 ELSE 0 END) 
        / NULLIF(CAST(COUNT(CASE WHEN GroupCD = 'ABC' THEN 1 END) AS FLOAT), 0) 
        * 100, 
        0
    ) AS Decimal(8,3)) AS SurrogateKEY1_fR_ClassFilterABC,
    
    -- 计算非ABC分组的SurrogateKEY2填充率
    CAST(COALESCE(
        SUM(CASE WHEN GroupCD != 'ABC' AND SurrogateKEY2 IS NOT NULL THEN 1 ELSE 0 END) 
        / NULLIF(CAST(COUNT(CASE WHEN GroupCD != 'ABC' THEN 1 END) AS FLOAT), 0) 
        * 100, 
        0
    ) AS Decimal(8,3)) AS SurrogateKEY2_fR_NonABC,
    
    -- 计算ABC分组的SurrogateKEY2填充率
    CAST(COALESCE(
        SUM(CASE WHEN GroupCD = 'ABC' AND SurrogateKEY2 IS NOT NULL THEN 1 ELSE 0 END) 
        / NULLIF(CAST(COUNT(CASE WHEN GroupCD = 'ABC' THEN 1 END) AS FLOAT), 0) 
        * 100, 
        0
    ) AS Decimal(8,3)) AS SurrogateKEY2_fR_ClassFilterABC
FROM #TempTable
GROUP BY ID

逻辑说明 #

1. 分组过滤：通过CASE WHEN GroupCD = 'ABC'或GroupCD != 'ABC'，分别统计对应分组内的有效数据条数和总条数
2. 避免除以0：用NULLIF将分母为0的情况转为NULL，再通过COALESCE将NULL转为0.000，匹配期望输出中无对应分组时的填充率
3. 格式统一：最后用CAST将结果转为Decimal(8,3)，保证输出格式一致

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