Oracle 相当于 Hive collect_set 函数

Question

下面是 hive 查询的预言机等价物是什么？

select appn_id,collect_set(CONCAT(upper(TRIM(dcsn_type_nm)),':',upper(TRIM(dcsn_outcm_nm))))
FROM  <left join between few tables>
group by appn_id  

编辑：

根据戈登的回答更新 -

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  

Answer 1

我认为这是使用的方式，等效的listagg()：

select listagg(upper(trim(dcsn_type_nm)) || ':' || upper(trim(dcsn_outcm_nm))) with group (order by null)

listagg()是一个聚合函数，因此它合并了来自多行的数据。