概要描述

quark中不支持listagg函数，可以使用其他函数进行改写，本文给出解释说明。

详细介绍

oracle语法

with tb as
(select 1 as deptno,'zhangsan' as name from dual union all
select 1 as deptno,'lisi' as name from dual union all
select 1 as deptno,'lisi' as name from dual union all
select 2 as deptno,'wangwu' as name from dual  union all
select 2 as deptno,'wangwu' as name from dual)
select
       distinct    --使用distinct的话会去重，方法1和方法2支持；如果不需要去重，考虑方法3
       deptno,
       listagg(name,'/') within group (order by name desc) over (partition by deptno ) as total_ename
from tb;

quark改写

方法一：concat_ws结合collect_list

升序：

set character.literal.as.string=TRUE;

with tb as
(select 1 as deptno,'zhangsan' as name from system.dual union all
select 1 as deptno,'lisi' as name from system.dual union all
select 1 as deptno,'lisi' as name from system.dual union all
select 2 as deptno,'wangwu' as name from system.dual  union all
select 2 as deptno,'wangwu' as name from system.dual)
select
       deptno,
       CONCAT_WS(',',sort_array(COLLECT_LIST(name)))
from tb
GROUP BY deptno;

降序：

WARP-136509 sort_array函数支持降序， 验证方式： SELECT sort_array(array(3,1,4,5,2),false) AS result FROM system.dual;

set character.literal.as.string=TRUE;

with tb as
(select 1 as deptno,'zhangsan' as name from system.dual union all
select 1 as deptno,'lisi' as name from system.dual union all
select 1 as deptno,'lisi' as name from system.dual union all
select 2 as deptno,'wangwu' as name from system.dual union all
select 2 as deptno,'wangwu' as name from system.dual)
select
deptno,
CONCAT_WS(',',sort_array(COLLECT_LIST(name),false))
from tb
GROUP BY deptno;

方法二：group_concat结合group by

去重；无法排序，且不像开源hive支持group_concat(distinct name ORDER BY name DESC SEPARATOR ‘,’)

set character.literal.as.string=TRUE;

with tb as
(select 1 as deptno,'zhangsan' as name from system.dual union all
select 1 as deptno,'lisi' as name from system.dual union all
select 1 as deptno,'lisi' as name from system.dual union all
select 2 as deptno,'wangwu' as name from system.dual  union all
select 2 as deptno,'wangwu' as name from system.dual)
select
       deptno,
       group_concat(name, ',')
from tb
group by deptno;

方法三：group_concat结合partition by的方法

不去重；无法排序，且不像开源hive支持group_concat(distinct name ORDER BY name DESC SEPARATOR ‘,’)

set character.literal.as.string=TRUE;

with tb as
(select 1 as deptno,'zhangsan' as name from system.dual union all
select 1 as deptno,'lisi' as name from system.dual union all
select 1 as deptno,'lisi' as name from system.dual union all
select 2 as deptno,'wangwu' as name from system.dual  union all
select 2 as deptno,'wangwu' as name from system.dual)
select
       deptno,
       group_concat(name, ',') over (partition by deptno )
from tb;