SQL Listagg как дополнительный столбец
Я ищу запрос Oracle SQL, который агрегирует столбец, но добавляет агрегацию в качестве дополнительного столбца вместо того, чтобы «действительно» агрегировать строки. Поэтому я хочу получить точно такое же количество строк с дополнительным столбцом с агрегированными значениями. Пример ниже должен помочь понять, что я имею в виду.
Стартовая таблица:
ID GIVEN_NAME LAST_NAME
11 CARL CLARKE
22 CARL CLARKE
33 CARL CLARKE
44 ANDREW JOHNSON
55 ANDREW JOHNSON
Обычная группа listagg (ID) от GIVEN_NAME и LAST_NAME даст мне:
LISTAGG(ID) GIVEN_NAME LAST_NAME
112233 CARL CLARKE
4455 ANDREW JOHNSON
Я хочу просто добавить агрегированный столбец:
AGGREGATE ID GIVEN_NAME LAST_NAME
112233 11 CARL CLARKE
112233 22 CARL CLARKE
112233 33 CARL CLARKE
4455 44 ANDREW JOHNSON
4455 55 ANDREW JOHNSON
Это вообще возможно? Спасибо!
Ответы 4
вот один вариант для вас, используя listagg:
with sample_data as (
select level id,level given_name,level last_name from dual connect by level <= 10
union all
select level+30 id,level given_name,level last_name from dual connect by level <= 5
union all
select level+500 id,level given_name,level last_name from dual connect by level <= 3
order by id
)
select
(select listagg(id) within group (order by id)
from sample_data di
where di.given_name=d.given_name and di.last_name=d.last_name) aggregate
,d.*
from sample_data d
;
Есть два варианта: агрегированный и аналитический LISTAGG.
Вы ищете аналитический вариант:
Агрегатная функция
select name, listagg(id) within group (order by id) as lst
from tab
group by name;
NAME LST
---- ------
X 1112
Y 1314
Аналитическая функция
Обратите внимание, что предложение OVER, в котором используется PARTITION BY, представляет собой группирующий ключ.
select name, id, listagg(id) within group (order by id) over( partition by name) as lst
from tab;
NAME ID LST
---- ---------- -------
X 11 1112
X 12 1112
Y 13 1314
Y 14 1314
Используйте функцию LISTAGG в аналитическая форма (OVER (аналитическое предложение)):
select GIVEN_NAME, LAST_NAME,
LISTAGG(ID) WITHIN GROUP (ORDER BY ID) OVER (PARTITION BY department_id) as "AGGREGATE"
from SOMETABLE
это будет работать:
create table table123(col1 varchar(20),col2 varchar(20),col3 varchar(20))
insert into table123 values('11' , 'CARL' , 'CLARKE');
insert into table123 values('22' , 'CARL' , 'CLARKE');
insert into table123 values('33' , 'CARL' , 'CLARKE');
insert into table123 values('44' , 'ANDREW' , 'JOHNSON');
insert into table123 values('55' , 'ANDREW' , 'JOHNSON');
SELECT * FROM table123;
SELECT a.col1,a.col2,a.col3,(select LISTAGG(col1, '') WITHIN GROUP (ORDER BY
col2) "aggreaget" from table123 where col2=a.col2 group by col2 )
FROM table123 a group by a.col2,a.col1,a.col3 order by a.col1
образец вывода:
11 CARL CLARKE 112233
22 CARL CLARKE 112233
33 CARL CLARKE 112233
44 ANDREW JOHNSON 4455
55 ANDREW JOHNSON 4455