PostgreSQL中CASE语句优化咨询及新增条件测试

哇，1500行重复的CASE语句？那维护和性能都得打折扣！我来帮你重构这个查询，不仅能大幅减少代码冗余，还能提升查询效率，同时我还新增了两组CASE条件来测试优化后的扩展性。

核心思路：提取重复逻辑，预计算复用 #

你的原始查询里有大量重复的逻辑：比如每个CASE都要检查多个字段是否包含关键词，还要重复验证VSOM产品匹配，甚至重复调用UPPER()/LOWER()函数。我们可以用CTE（公共表表达式）把这些重复计算提前做一次，后续所有CASE都直接复用结果。

优化后的完整代码（含新增测试条件） #

WITH campaign_preprocessed AS (
    SELECT
        *,
        -- 预计算：是否属于VSOM相关产品（所有CASE都要用到的逻辑）
        (position('VSOM' IN UPPER(campaign_name)) > 0 
         OR position('VSPHERE WITH OPERATIONS MANAGEMENT' IN UPPER(campaign_name)) > 0 
         OR position('VSOM' IN UPPER(products)) > 0 
         OR position('VSPHERE WITH OPERATIONS MANAGEMENT' IN UPPER(products)) > 0) AS is_vsom,
        -- 把需要检查的文本字段拼接成统一的小写字符串，一次匹配覆盖所有字段
        LOWER(CONCAT_WS('|', id, campaign_name, marketing_source_code, call_to_action, program_type, 
                       campaign_lvl1_name, campaign_lvl2_name, campaign_lvl3_name, campaign_lvl4_name)) AS all_text_fields,
        -- 预计算大写的campaign_name，方便后续排除项检查
        UPPER(campaign_name) AS upper_campaign_name
    FROM base.vdim_campaign
)
SELECT
    -- 原有的：VSOM+hol关键词（排除特定误匹配项）
    CASE 
        WHEN all_text_fields LIKE '%hol%' 
             AND NOT (upper_campaign_name LIKE '%HOLLYWOOD%' 
                      OR upper_campaign_name LIKE '%HOLE%' 
                      OR upper_campaign_name LIKE '%HOLDER%' 
                      OR upper_campaign_name LIKE '%STOCKHOLM%' 
                      OR upper_campaign_name LIKE '%HOLIDAY%' 
                      OR upper_campaign_name LIKE '%CATHOLIC%' 
                      OR upper_campaign_name LIKE '%HOLISTIC%' 
                      OR upper_campaign_name LIKE '%HOLY%' 
                      OR upper_campaign_name LIKE '%HOLTKAMP%' 
                      OR upper_campaign_name LIKE '%HOLDING%' 
                      OR upper_campaign_name LIKE '%CUSTHOLRECP%')
             AND is_vsom
        THEN 1 ELSE 0 
    END AS vsom_hol_response,
    -- 原有的：VSOM+seminar关键词
    CASE 
        WHEN all_text_fields LIKE '%seminar%' AND is_vsom
        THEN 1 ELSE 0 
    END AS vsom_seminar_response,
    -- 原有的：VSOM+webinar关键词
    CASE 
        WHEN all_text_fields LIKE '%webinar%' AND is_vsom
        THEN 1 ELSE 0 
    END AS vsom_webinar_response,
    -- 新增测试1：VSOM+workshop关键词
    CASE 
        WHEN all_text_fields LIKE '%workshop%' AND is_vsom
        THEN 1 ELSE 0 
    END AS vsom_workshop_response,
    -- 新增测试2：VSOM+on-demand/ondemand关键词（排除单纯的DEMAND匹配）
    CASE 
        WHEN (all_text_fields LIKE '%ondemand%' OR all_text_fields LIKE '%on-demand%')
             AND NOT (upper_campaign_name LIKE '%DEMAND%' AND upper_campaign_name NOT LIKE '%ON-DEMAND%')
             AND is_vsom
        THEN 1 ELSE 0 
    END AS vsom_on_demand_response
FROM campaign_preprocessed;

优化点详解 #

• 减少重复计算：CTE里的is_vsom、all_text_fields、upper_campaign_name只计算一次，所有CASE直接复用，避免了1500次重复的字符串转换和匹配操作，性能提升明显。
• 简化多字段匹配：原来每个CASE要写8-9个LIKE条件，现在只要对拼接后的all_text_fields做一次LIKE，代码量直接砍半。
• 扩展性拉满：新增CASE条件时，只需要关注关键词和特殊排除逻辑（比如新增的on-demand案例），再也不用复制粘贴一大串字段匹配和VSOM检查的代码。
• 可读性提升：每个CASE的逻辑变得清晰，一眼就能看出是“关键词匹配+排除项+VSOM验证”的组合，维护起来轻松很多。

额外优化建议（可选） #

如果你的关键词匹配需要更灵活的规则（比如匹配整个单词），可以用PostgreSQL的正则表达式操作符~*（不区分大小写），比如：

-- 匹配整个hol单词（避免匹配hollywood里的hol）
all_text_fields ~* '\mhol\M'

不过这个要根据你的业务需求调整，原来的%hol%是模糊匹配子串，正则版本是匹配完整单词。

内容的提问来源于stack exchange，提问作者Dheeraj Jain D