如何在CASE语句中使用聚合函数？实战场景求助

问题分析 #

你的需求核心是：

• 当column2符合column1 + "core"的匹配规则时，保留原始行数据；
• 当column1对应的column2不符合上述规则时（如示例中的ww），将该分组下所有column2值用分号;连接后输出单行。

现有查询的问题 #

1. Query1：使用split(column2,'xyz')[1]完全偏离匹配逻辑，xyz并非实际分隔符，无法正确判断column1与column2的匹配关系。
2. Query2：like条件写反（应为column2包含column1而非反过来），且按column2, column1分组会导致ww的每个不同column2单独成组，无法实现聚合效果。

正确查询语句 #

使用CTE先标记每个column1分组是否需要聚合，再根据标记决定输出原始值或聚合值：

WITH group_agg_flag AS (
    SELECT 
        column1,
        -- 判断该分组是否存在不符合匹配规则的行
        BOOL_OR(LOWER(column2) != LOWER(column1) || 'core') AS need_aggregate
    FROM my_table
    GROUP BY column1
)
SELECT 
    t.column1,
    CASE 
        WHEN gaf.need_aggregate THEN ARRAY_JOIN(ARRAY_AGG(t.column2), ';')
        ELSE t.column2
    END AS column2
FROM my_table t
JOIN group_agg_flag gaf ON t.column1 = gaf.column1
-- 需聚合的组只输出一行，无需聚合的组保留所有原始行
GROUP BY 
    t.column1,
    CASE WHEN gaf.need_aggregate THEN 1 ELSE t.column2 END,
    gaf.need_aggregate
ORDER BY t.column1;

逻辑说明 #

1. group_agg_flag CTE：对每个column1分组，标记该组是否存在不符合column1+"core"规则的行（need_aggregate为true表示需要聚合）。
2. 主查询：
   • 对于无需聚合的分组（如de、uk等），按原始column2分组，保留所有行；
   • 对于需要聚合的分组（如ww），仅按column1和标记分组，将所有column2值聚合为分号连接的字符串。

输出结果 #

执行上述查询后，将得到你期望的输出：

column1 column2
    de  decore
    uk  ukcore
    es  escore
    ca  cacore
    es  escore
    uk  ukcore
    ww  a1sz;a1qu;a1pp;a1kv;a1ba;a15j
    pl  plcore

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