SELECT列表中子查询内的不同LISTAGG [英] Distinct LISTAGG that is inside a subquery in the SELECT list
问题描述
这是我正在尝试做的事情和获得的收获的最小工作示例:
Here is a minimal working example of what I'm trying to do and what I'm getting:
我有一个查询，如下所示:
I have a query as follows:
/*
with tran_party as -- ALL DUMMY DATA ARE IN THESE CTE FOR YOUR REFERENCE
(select 1 tran_party_id, 11 transaction_id, 101 team_id_redirect
from dual
union all
select 2, 11, 101 from dual
union all
select 3, 11, 102 from dual
union all
select 4, 12, 103 from dual
union all
select 5, 12, 103 from dual
union all
select 6, 12, 104 from dual
union all
select 7, 13, 104 from dual
union all
select 8, 13, 105 from dual),
tran as
(select 11 transaction_id, 1001 account_id, 1034.93 amount from dual
union all
select 12, 1001, 2321.89 from dual
union all
select 13, 1002, 3201.47 from dual),
account as
(select 1001 account_id, 111 team_id from dual
union all
select 1002, 112 from dual),
team as
(select 101 team_id, 'UUU' as team_code from dual
union all
select 102, 'VV' from dual
union all
select 103, 'WWW' from dual
union all
select 104, 'XXXXX' from dual
union all
select 105, 'Z' from dual)
-- */
-- The Actual Query
select a.account_id,
t.transaction_id,
(select listagg (tm_redir.team_code, ', ')
within group (order by tm_redir.team_code)
from tran_party tp_redir
inner join team tm_redir
on tp_redir.team_id_redirect = tm_redir.team_id
inner join tran t_redir
on tp_redir.transaction_id = t_redir.transaction_id
where t_redir.account_id = a.account_id
and t_redir.transaction_id != t.transaction_id)
as teams_redirected
from tran t inner join account a on t.account_id = a.account_id;
注意:: tran_party.team_id_redirect 是引用 team.team_id 的外键.
NOTE: tran_party.team_id_redirect is a foreign key that references team.team_id.
当前输出:
ACCOUNT_ID TRANSACTION_ID TEAMS_REDIRECTED
---------- -------------- ----------------
1001 11 WWW, WWW, XXXXX
1001 12 UUU, UUU, VV
1002 13
预期输出:
我希望只选择一次TEAMS_REDIRECTED列中的重复项，如下所示:
I want the repeated items in TEAMS_REDIRECTED column to be selected only once, like this:
ACCOUNT_ID TRANSACTION_ID TEAMS_REDIRECTED
---------- -------------- ----------------
1001 11 WWW, XXXXX
1001 12 UUU, VV
1002 13
我尝试过的事情:
我写了一个内联视图，而不是直接从tran_party中进行选择，它像这样从tran_party中选择不同的值:
Instead of selecting from tran_party directly, I wrote an inline view that selects distinct values from tran_party like this:
select a.account_id,
t.transaction_id,
(select listagg (tm_redir.team_code, ', ')
within group (order by tm_redir.team_code)
from (select distinct transaction_id, team_id_redirect -- Note this inline view
from tran_party) tp_redir
inner join team tm_redir
on tp_redir.team_id_redirect = tm_redir.team_id
inner join tran t_redir
on tp_redir.transaction_id = t_redir.transaction_id
where t_redir.account_id = a.account_id
and t_redir.transaction_id != t.transaction_id)
as teams_redirected
from tran t inner join account a on t.account_id = a.account_id;
虽然这确实给了我预期的输出，但是当我在实际代码中使用此解决方案时，只需要一行约13秒钟即可检索到一行.因此，我无法使用已经尝试过的东西.
While this does give me the expected output, when I use this solution in my actual code, it takes about 13 seconds to retrieve just one row. Thus I cannot use what I already tried.
任何帮助将不胜感激.
推荐答案
以下方法摆脱了嵌入式视图以获取重复项，它在LISTAGG函数上使用REGEXP_REPLACE和RTRIM来获取aggregated list中的不同结果集.因此，它最多只能进行一次扫描.
The following method gets rid of the in-line view to fetch duplicates, it uses REGEXP_REPLACE and RTRIM on the LISTAGG function to get the distinct result set in the aggregated list. Thus, it won't do more than one scan.
将此段添加到您的代码中，
Adding this piece to your code,
RTRIM(REGEXP_REPLACE(listagg (tm_redir.team_code, ',')
WITHIN GROUP (ORDER BY tm_redir.team_code),
'([^,]+)(,\1)+', '\1'),
',')
修改后的查询-
SQL> with tran_party as -- ALL DUMMY DATA ARE IN THESE CTE FOR YOUR REFERENCE
2 (select 1 tran_party_id, 11 transaction_id, 101 team_id_redirect
3 from dual
4 union all
5 select 2, 11, 101 from dual
6 union all
7 select 3, 11, 102 from dual
8 union all
9 select 4, 12, 103 from dual
10 union all
11 select 5, 12, 103 from dual
12 union all
13 select 6, 12, 104 from dual
14 union all
15 select 7, 13, 104 from dual
16 union all
17 select 8, 13, 105 from dual),
18 tran as
19 (select 11 transaction_id, 1001 account_id, 1034.93 amount from dual
20 union all
21 select 12, 1001, 2321.89 from dual
22 union all
23 select 13, 1002, 3201.47 from dual),
24 account as
25 (select 1001 account_id, 111 team_id from dual
26 union all
27 select 1002, 112 from dual),
28 team as
29 (select 101 team_id, 'UUU' as team_code from dual
30 union all
31 select 102, 'VV' from dual
32 union all
33 select 103, 'WWW' from dual
34 union all
35 select 104, 'XXXXX' from dual
36 union all
37 select 105, 'Z' from dual)
38 -- The Actual Query
39 select a.account_id,
40 t.transaction_id,
41 (SELECT RTRIM(
42 REGEXP_REPLACE(listagg (tm_redir.team_code, ',')
43 WITHIN GROUP (ORDER BY tm_redir.team_code),
44 '([^,]+)(,\1)+', '\1'),
45 ',')
46 from tran_party tp_redir
47 inner join team tm_redir
48 on tp_redir.team_id_redirect = tm_redir.team_id
49 inner join tran t_redir
50 on tp_redir.transaction_id = t_redir.transaction_id
51 where t_redir.account_id = a.account_id
52 and t_redir.transaction_id != t.transaction_id)
53 AS teams_redirected
54 from tran t inner join account a on t.account_id = a.account_id
55 /
ACCOUNT_ID TRANSACTION_ID TEAMS_REDIRECTED
---------- -------------- --------------------
1001 11 WWW,XXXXX
1001 12 UUU,VV
1002 13
SQL>
这篇关于SELECT列表中子查询内的不同LISTAGG的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
