求含collect_set的Hive查询对应的Oracle等价实现及确认

首先得明确：Hive的collect_set()函数核心是对目标字段去重后，将多个值拼接成无重复的集合字符串，而Oracle的listagg()默认不会自动去重，直接替换会和原Hive逻辑不一致。

你给出的Oracle写法存在的问题 #

你提供的这段代码：

select appn_id,listagg(upper(trim(dcsn_type_nm)) || ':' || upper(trim(dcsn_outcm_nm))) within group (order by null) set_type_outcm_nm FROM <left join between few tables> group by appn_id

缺少去重逻辑，会把所有符合条件的拼接值（包括重复项）都聚合起来，这和Hivecollect_set()的去重特性不符，所以是不正确的。

正确的Oracle等价写法 #

要实现和Hivecollect_set()完全一致的逻辑，需要先通过子查询去重，再用listagg()聚合：

select 
    appn_id,
    listagg(combined_val, ',') within group (order by null) as set_type_outcm_nm
from (
    -- 先对拼接后的字段去重，匹配collect_set的去重特性
    select distinct 
        appn_id,
        upper(trim(dcsn_type_nm)) || ':' || upper(trim(dcsn_outcm_nm)) as combined_val
    FROM <left join between few tables>
) t
group by appn_id

关键说明 #

• distinct子句：先过滤掉同一个appn_id下重复的拼接值，对应Hivecollect_set()的去重功能。
• order by null：保持和Hivecollect_set()一样的无序输出（因为collect_set的结果顺序是不确定的），如果需要固定排序，可以替换为具体字段（比如order by combined_val）。
• 分隔符：Hivecollect_set()默认用逗号分隔，Oraclelistagg()也默认用逗号，这里显式写出来更清晰，如果你需要其他分隔符可以修改第一个参数后的逗号。

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