SQL重复查询优化方案：存储重复片段替代重复编写

Got it, let's fix that repetitive mess in your query—writing 20 nearly identical CASE statements is not only tedious but also a nightmare to maintain. The good news is we can reuse those repeated logic fragments without relying on stored functions. Here are two clean, maintainable approaches:

方法1：用CTE提取公共逻辑 + 条件聚合 #

First, we'll pull out all the repeated parts (like date calculations and static filters) into Common Table Expressions (CTEs)—these act as temporary, reusable datasets within your query. Then we'll use conditional logic to generate all your required columns without duplicating code.

示例代码 #

WITH date_params AS (
    -- 一次性计算所有需要的日期参数，避免重复子查询
    SELECT 
        EXTRACT(MONTH FROM MAX(SHOWN_DATE)) AS current_month,
        EXTRACT(YEAR FROM MAX(SHOWN_DATE)) AS current_year,
        TRUNC(MAX(Date_Month), 'month') AS max_month_trunc
    FROM TABLE_A
), filtered_data AS (
    -- 提前应用静态过滤条件，后续查询不用重复写
    SELECT 
        A1.*,
        EXTRACT(MONTH FROM A1.DATE_MONTH) AS sale_month,
        EXTRACT(YEAR FROM A1.DATE_MONTH) AS sale_year
    FROM TABLE_A A1
    WHERE 
        A1.ACCOUNT <> 'Not Provided' 
        AND A1.TYPE <> 'DIRECT'
)
SELECT 
    fd.*,
    -- MTD 销售额
    CASE WHEN fd.sale_month = dp.current_month AND fd.sale_year = dp.current_year AND fd.PRODUCT = 'A' THEN fd.SALES ELSE 0 END AS MTD_PRODUCT_A,
    CASE WHEN fd.sale_month = dp.current_month AND fd.sale_year = dp.current_year AND fd.PRODUCT = 'B' THEN fd.SALES ELSE 0 END AS MTD_PRODUCT_B,
    CASE WHEN fd.sale_month = dp.current_month AND fd.sale_year = dp.current_year AND fd.PRODUCT = 'C' THEN fd.SALES ELSE 0 END AS MTD_PRODUCT_C,
    CASE WHEN fd.sale_month = dp.current_month AND fd.sale_year = dp.current_year AND fd.PRODUCT = 'D' THEN fd.SALES ELSE 0 END AS MTD_PRODUCT_D,
    CASE WHEN fd.sale_month = dp.current_month AND fd.sale_year = dp.current_year AND fd.PRODUCT = 'E' THEN fd.SALES ELSE 0 END AS MTD_PRODUCT_E,
    
    -- 去年同期MTD
    CASE WHEN fd.sale_month = dp.current_month AND fd.sale_year = dp.current_year - 1 AND fd.PRODUCT = 'A' THEN fd.SALES ELSE 0 END AS MTD_PY_PRODUCT_A,
    CASE WHEN fd.sale_month = dp.current_month AND fd.sale_year = dp.current_year - 1 AND fd.PRODUCT = 'B' THEN fd.SALES ELSE 0 END AS MTD_PY_PRODUCT_B,
    CASE WHEN fd.sale_month = dp.current_month AND fd.sale_year = dp.current_year - 1 AND fd.PRODUCT = 'C' THEN fd.SALES ELSE 0 END AS MTD_PY_PRODUCT_C,
    CASE WHEN fd.sale_month = dp.current_month AND fd.sale_year = dp.current_year - 1 AND fd.PRODUCT = 'D' THEN fd.SALES ELSE 0 END AS MTD_PY_PRODUCT_D,
    CASE WHEN fd.sale_month = dp.current_month AND fd.sale_year = dp.current_year - 1 AND fd.PRODUCT = 'E' THEN fd.SALES ELSE 0 END AS MTD_PY_PRODUCT_E,
    
    -- MAT 销售额
    CASE WHEN fd.DATE_MONTH BETWEEN ADD_MONTHS(dp.max_month_trunc, -11) AND dp.max_month_trunc AND fd.PRODUCT = 'A' THEN fd.SALES ELSE 0 END AS MAT_PRODUCT_A,
    CASE WHEN fd.DATE_MONTH BETWEEN ADD_MONTHS(dp.max_month_trunc, -11) AND dp.max_month_trunc AND fd.PRODUCT = 'B' THEN fd.SALES ELSE 0 END AS MAT_PRODUCT_B,
    CASE WHEN fd.DATE_MONTH BETWEEN ADD_MONTHS(dp.max_month_trunc, -11) AND dp.max_month_trunc AND fd.PRODUCT = 'C' THEN fd.SALES ELSE 0 END AS MAT_PRODUCT_C,
    CASE WHEN fd.DATE_MONTH BETWEEN ADD_MONTHS(dp.max_month_trunc, -11) AND dp.max_month_trunc AND fd.PRODUCT = 'D' THEN fd.SALES ELSE 0 END AS MAT_PRODUCT_D,
    CASE WHEN fd.DATE_MONTH BETWEEN ADD_MONTHS(dp.max_month_trunc, -11) AND dp.max_month_trunc AND fd.PRODUCT = 'E' THEN fd.SALES ELSE 0 END AS MAT_PRODUCT_E,
    
    -- 去年同期MAT
    CASE WHEN fd.DATE_MONTH BETWEEN ADD_MONTHS(dp.max_month_trunc, -23) AND ADD_MONTHS(dp.max_month_trunc, -12) AND fd.PRODUCT = 'A' THEN fd.SALES ELSE 0 END AS MAT_PY_PRODUCT_A,
    CASE WHEN fd.DATE_MONTH BETWEEN ADD_MONTHS(dp.max_month_trunc, -23) AND ADD_MONTHS(dp.max_month_trunc, -12) AND fd.PRODUCT = 'B' THEN fd.SALES ELSE 0 END AS MAT_PY_PRODUCT_B,
    CASE WHEN fd.DATE_MONTH BETWEEN ADD_MONTHS(dp.max_month_trunc, -23) AND ADD_MONTHS(dp.max_month_trunc, -12) AND fd.PRODUCT = 'C' THEN fd.SALES ELSE 0 END AS MAT_PY_PRODUCT_C,
    CASE WHEN fd.DATE_MONTH BETWEEN ADD_MONTHS(dp.max_month_trunc, -23) AND ADD_MONTHS(dp.max_month_trunc, -12) AND fd.PRODUCT = 'D' THEN fd.SALES ELSE 0 END AS MAT_PY_PRODUCT_D,
    CASE WHEN fd.DATE_MONTH BETWEEN ADD_MONTHS(dp.max_month_trunc, -23) AND ADD_MONTHS(dp.max_month_trunc, -12) AND fd.PRODUCT = 'E' THEN fd.SALES ELSE 0 END AS MAT_PY_PRODUCT_E
FROM filtered_data fd
CROSS JOIN date_params dp;

为什么这更好？ #

• 重复逻辑只写一次: 日期参数和静态过滤条件在CTE里定义一次，不用在每个CASE里重复写。
• 更易维护: 如果需要调整过滤条件或日期逻辑，只改CTE部分就行，不用修改20个CASE语句。
• 保留原表所有列: filtered_data包含A1.*，所以你依然能拿到原查询的所有输出列。

方法2：用PIVOT进一步简化（数据库支持的话） #

If your database supports PIVOT (like Oracle, SQL Server, or PostgreSQL with the crosstab extension), you can eliminate even more repetition by rotating rows into columns directly. This is especially great if you might add more products later.

示例代码（Oracle风格） #

WITH date_params AS (
    SELECT 
        EXTRACT(MONTH FROM MAX(SHOWN_DATE)) AS current_month,
        EXTRACT(YEAR FROM MAX(SHOWN_DATE)) AS current_year,
        TRUNC(MAX(Date_Month), 'month') AS max_month_trunc
    FROM TABLE_A
), tagged_data AS (
    SELECT 
        A1.*,
        -- 给每条数据标记所属的时间维度
        CASE 
            WHEN EXTRACT(MONTH FROM A1.DATE_MONTH) = dp.current_month 
                 AND EXTRACT(YEAR FROM A1.DATE_MONTH) = dp.current_year THEN 'MTD'
            WHEN EXTRACT(MONTH FROM A1.DATE_MONTH) = dp.current_month 
                 AND EXTRACT(YEAR FROM A1.DATE_MONTH) = dp.current_year - 1 THEN 'MTD_PY'
            WHEN A1.DATE_MONTH BETWEEN ADD_MONTHS(dp.max_month_trunc, -11) AND dp.max_month_trunc THEN 'MAT'
            WHEN A1.DATE_MONTH BETWEEN ADD_MONTHS(dp.max_month_trunc, -23) AND ADD_MONTHS(dp.max_month_trunc, -12) THEN 'MAT_PY'
            ELSE NULL
        END AS time_period
    FROM TABLE_A A1
    CROSS JOIN date_params dp
    WHERE 
        A1.ACCOUNT <> 'Not Provided' 
        AND A1.TYPE <> 'DIRECT'
)
SELECT *
FROM tagged_data
PIVOT (
    -- 聚合销售额，按产品和时间维度转成列
    SUM(SALES) AS sales
    FOR (PRODUCT, time_period) IN (
        ('A', 'MTD') AS MTD_PRODUCT_A,
        ('B', 'MTD') AS MTD_PRODUCT_B,
        ('C', 'MTD') AS MTD_PRODUCT_C,
        ('D', 'MTD') AS MTD_PRODUCT_D,
        ('E', 'MTD') AS MTD_PRODUCT_E,
        
        ('A', 'MTD_PY') AS MTD_PY_PRODUCT_A,
        ('B', 'MTD_PY') AS MTD_PY_PRODUCT_B,
        ('C', 'MTD_PY') AS MTD_PY_PRODUCT_C,
        ('D', 'MTD_PY') AS MTD_PY_PRODUCT_D,
        ('E', 'MTD_PY') AS MTD_PY_PRODUCT_E,
        
        ('A', 'MAT') AS MAT_PRODUCT_A,
        ('B', 'MAT') AS MAT_PRODUCT_B,
        ('C', 'MAT') AS MAT_PRODUCT_C,
        ('D', 'MAT') AS MAT_PRODUCT_D,
        ('E', 'MAT') AS MAT_PRODUCT_E,
        
        ('A', 'MAT_PY') AS MAT_PY_PRODUCT_A,
        ('B', 'MAT_PY') AS MAT_PY_PRODUCT_B,
        ('C', 'MAT_PY') AS MAT_PY_PRODUCT_C,
        ('D', 'MAT_PY') AS MAT_PY_PRODUCT_D,
        ('E', 'MAT_PY') AS MAT_PY_PRODUCT_E
    )
);

为什么这更好？ #

• 极致简洁: 不用写一堆CASE语句，只需要定义一次时间维度的逻辑，然后在PIVOT里列出产品和时间的组合。
• 扩展性强: 要加产品F？只需要在PIVOT的IN子句里加一行('F', 'MTD') AS MTD_PRODUCT_F（以及对应的其他时间维度）就行。
• 逻辑集中: 所有时间维度的判断都在tagged_data CTE里，修改起来非常方便。

关键注意事项 #

• 数据库兼容性: PIVOT语法 varies by database—PostgreSQL uses crosstab from the tablefunc extension, SQL Server uses PIVOT with similar syntax, and Oracle's implementation matches the example above. Adjust as needed for your DB.
• 性能: Both approaches should perform as well (if not better) than your original query, since we're reducing redundant subqueries and filtering early in the CTEs.

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