Oracle LISTAGG（）是否具有多个属性？ [英] Oracle LISTAGG() for multiple attributes?

问题描述

我想知道是否有更好的解决方案（例如更快的执行力）来解决以下问题。

I wonder if there is a better (i.e. faster execution) solution to the problem described below.

步骤1）

create table t (k number, v1 number, v2 number);

insert into t values (1,1,1);
insert into t values (1,2,2);
insert into t values (1,2,3);
insert into t values (1,3,3);
insert into t values (1,4,3);  
insert into t values (2,7,8);
insert into t values (2,7,9);

步骤2）

我想返回以下数据集

(k, v1_list, v2_list)
1, (1,2,3,4), (1,2,3)
2, (7), (8,9)

步骤3）

我能够通过运行listagg（）的多个组合来解决该问题，我想知道是否可以通过其他方式实现。
在此示例中，我处理2个属性，但实际上我必须对数百个列表运行类似的语句。如下所示的查询可能会变得很麻烦。我想知道是否可以在一个查询中完成，可能多次使用listagg（）？

I am able to solve it by running the multiple combination of listagg() and I wonder if it can be achieved in another way. In this example I deal with 2 attributes but in reality I have to run the similar statements for hundreds of lists. Querying as shown below may become combersome. I wonder if it can be done in one query, possibly using listagg() several times?

with q1 as (
 select distinct k, listagg (v1, ', ') within group (order by k) over (partition by k) v1_list
 from   t
 group  by k, v1),
q2 as (
 select distinct k, listagg (v2, ', ') within group (order by k) over (partition by k) v2_list
 from   t
 group  by k, v2)
--
select distinct t.k, v1_list, v2_list from t       
--
join   q1 on q1.k = t.k
join   q2 on q2.k = t.k

感谢您的建议，

-卢卡斯

推荐答案

您可以尝试未记录的函数 wm_concat

You can try undocumented function wm_concat

select k, wm_concat(distinct(v1)), wm_concat(distinct(v2))
from t
group by k

或使用 listagg

select k, listagg(v1, ', ') within group (order by k), listagg(v2, ', ') within group (order by k)
from (
    select distinct k, v1, null v2 from t  
    union all
    select distinct k, null v1, v2 from t
)
group by k

这篇关于Oracle LISTAGG（）是否具有多个属性？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！