SQL中使用LISTAGG时替换地点为大洲并实现去重的方法

要实现将国家替换为对应大洲且去重的需求，核心是先把location字段的国家转换为大洲，再对大洲去重后聚合。以下分两种场景给出修改后的SQL：

场景1：已有国家-大洲映射表 #

如果数据库中存在一张存储国家与对应大洲的映射表（例如表名为country_continent，包含country（国家名称）和continent（大洲名称）两个字段），可以通过表关联实现转换：

select
    a.product_desc as "Product",
    a.PRODUCT_ID as "Product ID",
    a.START_DATE::timestamp as "Start Date",
    LISTAGG(DISTINCT cc.continent, ', ') WITHIN GROUP (ORDER BY cc.continent DESC) AS "Location" 
from "table" as a
join country_continent cc on a.location = cc.country
group by a.product_desc, a.PRODUCT_ID, a.START_DATE

场景2：无映射表，手动配置映射 #

如果没有现成的映射表，可以用CASE语句硬编码国家与大洲的对应关系：

select
    a.product_desc as "Product",
    a.PRODUCT_ID as "Product ID",
    a.START_DATE::timestamp as "Start Date",
    LISTAGG(DISTINCT 
        CASE a.location
            WHEN '美国' THEN '北美洲'
            WHEN '加拿大' THEN '北美洲'
            WHEN '中国' THEN '亚洲'
            WHEN '印度' THEN '亚洲'
            -- 根据实际需求添加更多国家-大洲映射
            ELSE a.location -- 未匹配的国家保留原名称
        END, ', ') WITHIN GROUP (ORDER BY 
        CASE a.location
            WHEN '美国' THEN '北美洲'
            WHEN '加拿大' THEN '北美洲'
            WHEN '中国' THEN '亚洲'
            WHEN '印度' THEN '亚洲'
            ELSE a.location
        END DESC) AS "Location" 
from "table" as a
group by a.product_desc, a.PRODUCT_ID, a.START_DATE

关键说明 #

• 用DISTINCT修饰LISTAGG的参数，确保同一大洲仅出现一次
• 必须添加GROUP BY子句，按产品名称、ID、起始日期分组，否则聚合函数无法正常工作
• 原查询中的select distinct可以去掉，因为GROUP BY已经会自动分组去重

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