Teradata SQL中合并CASE WHEN与SUM OVER实现分类汇总优化

核心思路 #

将分类逻辑集中定义一次，既用于生成Category列，也作为聚合或窗口函数的分组依据，彻底避免重复编写分类条件，简化代码维护。

实现方式（Teradata兼容） #

方式1：CTE复用分类逻辑（可读性最优） #

通过公共表表达式（CTE）先统一处理分类，后续查询直接复用结果，逻辑清晰且易于维护：

WITH categorized_data AS (
    SELECT
        -- 仅在此处编写所有分类逻辑
        CASE
            WHEN col1 = 'A' AND col2 > 10 THEN 'Category 1'
            WHEN col1 IN ('B', 'C') AND col3 = 'X' THEN 'Category 2'
            WHEN col4 LIKE '%Y%' THEN 'Category 3'
            WHEN col2 BETWEEN 5 AND 9 AND col3 = 'Z' THEN 'Category 4'
            WHEN col1 = 'D' AND amount_col < 100 THEN 'Category 5'
            ELSE 'Other'
        END AS Category,
        amount_col -- 需要求和的目标列
    FROM your_source_table
)
SELECT
    Category,
    SUM(amount_col) AS total_amount,
    -- 全局汇总（所有分类的总和）
    SUM(amount_col) OVER () AS total_all_categories,
    -- 计算分类占比（保留两位小数）
    ROUND(SUM(amount_col) / SUM(amount_col) OVER () * 100, 2) AS percentage
FROM categorized_data
GROUP BY Category
ORDER BY total_amount DESC;

方式2：主查询内直接复用（无需CTE） #

Teradata支持通过列位置引用GROUP BY中的别名，避免重复编写CASE表达式：

SELECT
    CASE
        WHEN col1 = 'A' AND col2 > 10 THEN 'Category 1'
        WHEN col1 IN ('B', 'C') AND col3 = 'X' THEN 'Category 2'
        WHEN col4 LIKE '%Y%' THEN 'Category 3'
        WHEN col2 BETWEEN 5 AND 9 AND col3 = 'Z' THEN 'Category 4'
        WHEN col1 = 'D' AND amount_col < 100 THEN 'Category 5'
        ELSE 'Other'
    END AS Category,
    SUM(amount_col) AS total_amount,
    SUM(amount_col) OVER () AS total_all_categories
FROM your_source_table
GROUP BY 1 -- 引用SELECT列表第1列的Category，避免重复CASE
ORDER BY total_amount DESC;

示例验证 #

示例数据集 #

col1 | col2 | col3 | col4 | amount_col
-----|------|------|------|------------
A    | 15   | Z    | ABC  | 100
B    | 5    | X    | DEF  | 200
C    | 8    | X    | GHI  | 150
D    | 3    | Y    | JKY  | 50
A    | 8    | Z    | KLM  | 75
D    | 2    | W    | NOP  | 40

期望输出 #

Category   | total_amount | total_all_categories | percentage
-----------|--------------|----------------------|------------
Category 2 | 350          | 615                  | 56.91
Category 1 | 100          | 615                  | 16.26
Category 5 | 40           | 615                  | 6.50
Category 3 | 50           | 615                  | 8.13
Other      | 75           | 615                  | 12.20

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