如何将按类别分组的指标值展平为单行宽表并避免数据集膨胀？同时如何减少SQL查询中的代码重复？

看起来你遇到了两个典型的SQL宽表构建和代码冗余问题，我来帮你逐个解决：

我需要创建一张宽平表展示不同类别对应的各类指标值，但当前方法导致数据集膨胀，想知道怎么构建每个指标对应单一值的宽表（比如期望得到TOTAL_CAT_VALUE_SAMPLE_1 = 372.8, CAT_1_VALUE_SAMPLE_1 = 155.0, CAT_2_VALUE_SAMPLE_1=NULL, CAT_3_VALUE_SAMPLE_1=217.8这样的单行结果）。另外，对于仅WHERE子句日期范围不同的重复SQL查询，有没有办法减少代码重复？

当前SQL查询 #

WITH metrics_sample_1 AS (
    SELECT 
        CATEGORY,
        SUM(METRIC_VALUE * COEFF) AS TOTAL_CAT_VALUE_SAMPLE_1,
        SUM(CASE WHEN CATEGORY = 'CAT_1' THEN METRIC_VALUE * COEFF ELSE 0 END) AS CAT_1_VALUE_SAMPLE_1,
        SUM(CASE WHEN CATEGORY = 'CAT_2' THEN METRIC_VALUE * COEFF ELSE 0 END) AS CAT_2_VALUE_SAMPLE_1,
        SUM(CASE WHEN CATEGORY = 'CAT_3' THEN METRIC_VALUE * COEFF ELSE 0 END) AS CAT_3_VALUE_SAMPLE_1,
        COUNT(DISTINCT CAT_ID) AS CAT_ID_COUNT_SAMPLE_1
    FROM METRICS_DATA
    WHERE ACTION_DATE > (DATEADD(DAY, -10, GETDATE()))
    GROUP BY CATEGORY
), metrics_sample_2 AS (
    SELECT 
        CATEGORY,
        SUM(METRIC_VALUE * COEFF) AS TOTAL_CAT_VALUE_SAMPLE_2,
        SUM(CASE WHEN CATEGORY = 'CAT_1' THEN METRIC_VALUE * COEFF ELSE 0 END) AS CAT_1_VALUE_SAMPLE_2,
        SUM(CASE WHEN CATEGORY = 'CAT_2' THEN METRIC_VALUE * COEFF ELSE 0 END) AS CAT_2_VALUE_SAMPLE_2,
        SUM(CASE WHEN CATEGORY = 'CAT_3' THEN METRIC_VALUE * COEFF ELSE 0 END) AS CAT_3_VALUE_SAMPLE_2
    FROM METRICS_DATA
    WHERE ACTION_DATE BETWEEN DATEADD(DAY, -20, GETDATE()) and DATEADD(DAY, -10, GETDATE())
    GROUP BY CATEGORY
)
SELECT * FROM metrics_sample_1

（同时查询metrics_sample_1和metrics_sample_2时数据集会进一步膨胀）

当前结果 #

+------------+---------------------------+----------------------+----------------------+----------------------+
| CATEGORY   | TOTAL_CAT_VALUE_SAMPLE_1  | CAT_1_VALUE_SAMPLE_1 | CAT_2_VALUE_SAMPLE_1 | CAT_3_VALUE_SAMPLE_1 |
+------------+---------------------------+----------------------+----------------------+----------------------+
| CAT_1      | 155.0                     | 155.0                | 0.0                  | 0.0                  |
| CAT_2      | NULL                      | 0.0                  | NULL                 | 0.0                  |
| CAT_3      | 217.8                     | 0.0                  | 0.0                  | 217.8                |
+------------+---------------------------+----------------------+----------------------+----------------------+

期望结果 #

+---------------------------+----------------------+----------------------+----------------------+
| TOTAL_CAT_VALUE_SAMPLE_1  | CAT_1_VALUE_SAMPLE_1 | CAT_2_VALUE_SAMPLE_1 | CAT_3_VALUE_SAMPLE_1 |
+---------------------------+----------------------+----------------------+----------------------+
| 372.8                     | 155.0                | NULL                 | 217.8                |
+---------------------------+----------------------+----------------------+----------------------+

解决方案 #

1. 构建无膨胀的单行宽表 #

你的问题核心是不必要的GROUP BY CATEGORY——你想要的是全类别的汇总值，而不是按每个类别分组的明细结果。去掉GROUP BY，再用NULLIF把无数据的0转换成NULL，就能得到你要的单行宽表：

WITH metrics_sample_1 AS (
    SELECT 
        SUM(METRIC_VALUE * COEFF) AS TOTAL_CAT_VALUE_SAMPLE_1,
        -- 无数据时返回NULL而非0
        NULLIF(SUM(CASE WHEN CATEGORY = 'CAT_1' THEN METRIC_VALUE * COEFF ELSE 0 END), 0) AS CAT_1_VALUE_SAMPLE_1,
        NULLIF(SUM(CASE WHEN CATEGORY = 'CAT_2' THEN METRIC_VALUE * COEFF ELSE 0 END), 0) AS CAT_2_VALUE_SAMPLE_1,
        NULLIF(SUM(CASE WHEN CATEGORY = 'CAT_3' THEN METRIC_VALUE * COEFF ELSE 0 END), 0) AS CAT_3_VALUE_SAMPLE_1,
        COUNT(DISTINCT CAT_ID) AS CAT_ID_COUNT_SAMPLE_1
    FROM METRICS_DATA
    WHERE ACTION_DATE > DATEADD(DAY, -10, GETDATE())
)
SELECT * FROM metrics_sample_1;

这个查询会直接返回一行汇总结果：

• TOTAL_CAT_VALUE_SAMPLE_1自动计算所有类别的总和（155.0+217.8=372.8）
• 没有数据的类别（比如CAT_2）会显示NULL，完全匹配你的期望格式

2. 减少日期范围不同的重复查询 #

对于仅时间范围不同的重复聚合逻辑，我们可以把时间条件嵌入聚合函数，用一个CTE完成所有时间窗口的计算，避免重复写相同的SUM/CASE代码：

WITH all_metrics AS (
    SELECT 
        -- 样本1：最近10天的指标
        SUM(CASE WHEN ACTION_DATE > DATEADD(DAY, -10, GETDATE()) THEN METRIC_VALUE * COEFF END) AS TOTAL_CAT_VALUE_SAMPLE_1,
        NULLIF(SUM(CASE WHEN ACTION_DATE > DATEADD(DAY, -10, GETDATE()) AND CATEGORY = 'CAT_1' THEN METRIC_VALUE * COEFF END), 0) AS CAT_1_VALUE_SAMPLE_1,
        NULLIF(SUM(CASE WHEN ACTION_DATE > DATEADD(DAY, -10, GETDATE()) AND CATEGORY = 'CAT_2' THEN METRIC_VALUE * COEFF END), 0) AS CAT_2_VALUE_SAMPLE_1,
        NULLIF(SUM(CASE WHEN ACTION_DATE > DATEADD(DAY, -10, GETDATE()) AND CATEGORY = 'CAT_3' THEN METRIC_VALUE * COEFF END), 0) AS CAT_3_VALUE_SAMPLE_1,
        COUNT(DISTINCT CASE WHEN ACTION_DATE > DATEADD(DAY, -10, GETDATE()) THEN CAT_ID END) AS CAT_ID_COUNT_SAMPLE_1,
        
        -- 样本2：10-20天前的指标
        SUM(CASE WHEN ACTION_DATE BETWEEN DATEADD(DAY, -20, GETDATE()) AND DATEADD(DAY, -10, GETDATE()) THEN METRIC_VALUE * COEFF END) AS TOTAL_CAT_VALUE_SAMPLE_2,
        NULLIF(SUM(CASE WHEN ACTION_DATE BETWEEN DATEADD(DAY, -20, GETDATE()) AND DATEADD(DAY, -10, GETDATE()) AND CATEGORY = 'CAT_1' THEN METRIC_VALUE * COEFF END), 0) AS CAT_1_VALUE_SAMPLE_2,
        NULLIF(SUM(CASE WHEN ACTION_DATE BETWEEN DATEADD(DAY, -20, GETDATE()) AND DATEADD(DAY, -10, GETDATE()) AND CATEGORY = 'CAT_2' THEN METRIC_VALUE * COEFF END), 0) AS CAT_2_VALUE_SAMPLE_2,
        NULLIF(SUM(CASE WHEN ACTION_DATE BETWEEN DATEADD(DAY, -20, GETDATE()) AND DATEADD(DAY, -10, GETDATE()) AND CATEGORY = 'CAT_3' THEN METRIC_VALUE * COEFF END), 0) AS CAT_3_VALUE_SAMPLE_2
    FROM METRICS_DATA
    -- 提前过滤时间范围，减少扫描数据量
    WHERE ACTION_DATE > DATEADD(DAY, -20, GETDATE())
)
SELECT * FROM all_metrics;

这种写法的好处：

• 只扫描一次METRICS_DATA表，性能比两次独立查询好很多
• 聚合逻辑只维护一处，后续修改指标或类别时不用重复改多段代码
• 结果仍是单行宽表，不会出现数据集膨胀

如果你的时间范围更多（比如周、月、季度），还可以用生成时间范围临时表+交叉应用的方式进一步简化，但上面的写法已经能解决你当前的重复代码问题。

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