当前位置: 动力学知识库 > 问答 > 编程问答 >

sql - 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;

NOTE: tran_party.team_id_redirect is a foreign key that references team.team_id.

Current output:

ACCOUNT_ID TRANSACTION_ID TEAMS_REDIRECTED

---------- -------------- ----------------

1001 11 WWW, WWW, XXXXX

1001 12 UUU, UUU, VV

1002 13

Expected output:

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

What I tried:

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;

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.

Any help will be appreciated.

网友答案:

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'),
                     ',')

Modified query-

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>
分享给朋友:
您可能感兴趣的文章:
随机阅读: