Как исключить повторяющиеся значения из результата агрегированного запроса Oracle LISTAGG

Я пытаюсь исключить повторяющиеся значения из агрегированных результатов в соответствии с этой рекомендацией Oracle :

with depts as (
select 'ACCOUNTING' as department , 'CLERC' as job from dual union all
select 'ACCOUNTING' as department , 'MANAGER' as job from dual union all
select 'ACCOUNTING' as department , 'PRESIDENT' as job from dual union all

select 'RESEARCH' as department , 'ANALYST' as job from dual union all
select 'RESEARCH' as department , 'ANALYST' as job from dual union all
select 'RESEARCH' as department , 'CLERK' as job from dual union all
select 'RESEARCH' as department , 'CLERK' as job from dual union all
select 'RESEARCH' as department , 'MANAGER' as job from dual union all

select 'SALES' as department , 'MANAGER' as job from dual union all
select 'SALES' as department , 'CLERK' as job from dual union all
select 'SALES' as department , 'SALESMAN' as job from dual union all
select 'SALES' as department , 'SALESMAN' as job from dual union all
select 'SALES' as department , 'SALESMAN' as job from dual union all
select 'SALES' as department , 'SALESMAN' as job from dual
)

select d.department,
       listagg (d.job,', ' ) within group (order by d.job) jobs
  from depts d
 group by d.department

как вы видите, полевые задания содержат повторяющиеся значения.

и Oracle рекомендуют устранить это таким образом:

with depts as (
select 'ACCOUNTING' as department , 'CLERC' as job from dual union all
select 'ACCOUNTING' as department , 'MANAGER' as job from dual union all
select 'ACCOUNTING' as department , 'PRESIDENT' as job from dual union all

select 'RESEARCH' as department , 'ANALYST' as job from dual union all
select 'RESEARCH' as department , 'ANALYST' as job from dual union all
select 'RESEARCH' as department , 'CLERK' as job from dual union all
select 'RESEARCH' as department , 'CLERK' as job from dual union all
select 'RESEARCH' as department , 'MANAGER' as job from dual union all

select 'SALES' as department , 'MANAGER' as job from dual union all
select 'SALES' as department , 'CLERK' as job from dual union all
select 'SALES' as department , 'SALESMAN' as job from dual union all
select 'SALES' as department , 'SALESMAN' as job from dual union all
select 'SALES' as department , 'SALESMAN' as job from dual union all
select 'SALES' as department , 'SALESMAN' as job from dual
)

SELECT d.department,
       (select LISTAGG(job,', ')
               WITHIN GROUP (ORDER BY job)
          from (select unique job job
                  from depts t
                 where t.department = d.department)) jobs
  FROM depts d

но это не работает с вер. Oracle Database 11g Enterprise Edition, выпуск 11.2.0.4.0 — 64-разрядная версия.

Верна ли эта рекомендация? Насколько я знаю, мы не могли отправить данные во вложенный запрос на второй уровень вложенности.


sql string oracle group-by listagg
person Andrey Zinovich    schedule 30.12.2019    source источник

Ответы (2)


arrow_upward
3
arrow_downward

Начиная с Oracle 19c, LISTAGG() поддерживает DISTINCT:

select department, listagg(distinct job, ',') within group(order by job) jobs
from depts
group by department

В более ранних версиях типичным обходным решением является SELECT DISTINCT в подзапросе, а затем агрегирование:

select department, listagg(job, ',') within group(order by job) jobs
from (select distinct department, job from depts) t
group by department

Демонстрация DB Fiddle:

DEPARTMENT | JOBS                   
:--------- | :----------------------
ACCOUNTING | CLERC,MANAGER,PRESIDENT
RESEARCH   | ANALYST,CLERK,MANAGER  
SALES      | CLERK,MANAGER,SALESMAN
person GMB    schedule 30.12.2019
comment
Итак, верна ли рекомендация на сайте Oracle? - person Andrey Zinovich; 31.12.2019

arrow_upward
0
arrow_downward

Другая возможность — развернуть функцию, которая LISTAGG игнорирует значения NULL.

Простой row_number the duplicates andlistaggonly the values withrn = 1`

with dep as (
select 
  DEPARTMENT, JOB,
  row_number() over (partition by  DEPARTMENT, JOB order by JOB) as rn
from depts)
select 
 department, listagg(case when rn = 1 then job end, ',') within group(order by job) jobs
from dep
group by department
person Marmite Bomber    schedule 30.12.2019