Redshift : listagg (여러 행 데이터 합치기)

 

 

 

LISTAGG()

LISTAGG() 함수는 동일한 Column에 있는 데이터들 중 여러 Row의 데이터들을 하나의 행으로 합쳐주는 기능을 가집니다.
자세한건 실제 예시를 보시죠.

Table name = employees

department_no	join_dt	name
10	2020-02-01	Bero
10	2020-02-02	Alice
10	2020-02-03	Chris
20	2020-02-03	Dave
20	2020-02-02	Filia
20	2020-02-04	Elin
30	2020-02-06	Grace
30	2020-02-04	Irene
30	2020-02-05	Hella

 

LISTAGG(column_name, 'seperator') WITHIN GROUP (ORDER BY column_name) OVER(PARTITION BY column_name)

LISTAGG 함수의 기본적인 형식은 위와 같습니다.
Query의 SELECT 부분에서 LISTAGG 함수를 사용합니다.
또한 ORDER BY 부분은 필수입니다.
반면에 OVER PARTITION BY 부분은 없어도 됩니다.

 

 

 

 

 

 

 

SELECT department_no,
       LISTAGG(name, ', ') WITHIN GROUP (ORDER BY name) AS employees
FROM employees
GROUP BY department_no;

 

Result Set

 

department_no	employees
10	Alice, Bero, Chirs
20	Dave, Elin, Filia
30	Grace, Hella, Irene

 

1. LISTAGG(name, ', ')
먼저 이 부분을 보시면 name이라는 컬럼에 있는 여러 행의 데이터를 하나의 행에 합칠거라는 뜻이며 이때 사용할 구분자는 콤마와 스페이스(, )임을 알 수 있습니다.

2. WITHIN GROUP (ORDER BY name)
1번에서의 내용처럼 name 컬럼에 있는 여러 Rows의 데이터를 하나의 Raw에 합일 것인데 합칠 값들간의 순서는 어떻게 할 것인지를 정해야 합니다.
WITHIN GROUP 부분은 이러한 의미를 가지고 있습니다.
위 내용에선 ORDER BY name이라고 적혀있으므로 LISTAGG 함수를 진행하면서 여러 Rows의 데이터를 합칠 때 name 컬럼의 데이터를 오름차순으로 합친다는 것입니다.

 

 

 

 

 

 

 

SELECT department_no,
       LISTAGG(name, ', ') WITHIN GROUP (ORDER BY NULL) AS employees
FROM employees
GROUP BY department_no;

 

Result Set

 

department_no	employees
10	Bero, Alice, Chirs
20	Dave, Filia, Elin
30	Grace, Irene, Hella

 

1. LISTAGG(name, ', ')
먼저 이 부분을 보시면 name이라는 컬럼에 있는 여러 행의 데이터를 하나의 행에 합칠거라는 뜻이며 이때 사용할 구분자는 콤마와 스페이스(, )임을 알 수 있습니다.

2. WITHIN GROUP (ORDER BY NULL)
1번에서의 내용처럼 name 컬럼에 있는 여러 Rows의 데이터를 하나의 Raw에 합일 것인데 합칠 값들간의 순서는 어떻게 할 것인지를 정해야 합니다.
WITHIN GROUP 부분은 이러한 의미를 가지고 있습니다.
위 내용에선 ORDER BY NULL이라고 적혀있으므로 LISTAGG 함수를 진행하면서 여러 Rows의 데이터를 합칠 때 name 컬럼의 데이터를 별도의 정렬 없이 테이블에 적재된 그대로 합친다는 뜻입니다.

 

 

 

 

 

 

 

 

 

 

 

SELECT department_no,
       LISTAGG(name) WITHIN GROUP (ORDER BY NULL) AS employees
FROM employees
GROUP BY department_no;

 

Result Wet

department_no	employees
10	BeroAliceChirs
20	DaveFiliaElin
30	GraceIreneHella

 

1. LISTAGG(name)
이 예시는 바로 예전에 봤던 예시와 거의 동일하지만 LISTAGG 부분에 구분자가 생략되어 있습니다.
이런 경우 위에 나온 표처럼 구분자가 없이 Text가 바로 합쳐져서 나오게됩니다.

 

 

 

 

 

 

 

 

 

 

 

SELECT  department_no,
        join_dt,
        name,
        LISTAGG(name, ',') WITHIN GROUP (ORDER BY join_dt) OVER(PARTITION BY department_no) AS employees
FROM employees;

 

Result Set

department_no	join_dt	name	employees
10	2020-02-01	Bero	Bero, Alice, Chris
10	2020-02-02	Alice	Bero, Alice, Chris
10	2020-02-03	Chris	Bero, Alice, Chris
20	2020-02-02	Filia	Filia, Dave, Elin
20	2020-02-03	Dave	Filia, Dave, Elin
20	2020-02-04	Elin	Filia, Dave, Elin
30	2020-02-04	Irene	Irene, Hella, Grace
30	2020-02-05	Hella	Irene, Hella, Grace
30	2020-02-06	Grace	Irene, Hella, Grace

 

1. LISTAGG(name, ',')
LISTAGG 함수로 합칠 데이터가 있는 Column은 name column이며 값들의 구분은 콤마와 공백(, )으로 합니다.

2. WITHIN GROUP (ORDER BY join_dt)
name column의 데이터를 합칠 때 join_dt 기준 오름차순으로 name column의 데이터를 합칩니다.

3. OVER(PARTITION BY department_no)
그리고 이러한 LISTAGG 함수의 적용은 동일한 department_no를 가진 name column의 데이터끼리 LISTAGG 함수가 적용됩니다.

(이렇게 over partitoin by 부분을 넣으면 listagg함수를 적용한 새로운 column을 만들게 되므로 group by 를 query의 가장 마지막에 명시할 필요가 없습니다.)


위 결과를 보면 결국 name column이 합쳐졌으며 name의 순서는 join_dt 기준 오름차순이라는 것을 알 수 있습니다.
이와 더불어 동일한 department_no를 가진 name끼리 합쳐졌다는 것도 알 수 있습니다.
또한 초반에 봤던 예시와는 달리 쿼리 결과 출력 시 department_no, join_dt, name을 모두 추출하므로 그룹화할 Row가 없어 모든 Row가 추출되었습니다.

 

 

 

 

 

 

 

 

 

다음 예시를 봅시다.

Table name = employees

department_no	join_dt	name
10	2020-02-01	Chris
10	2020-02-02	Alice
10	2020-02-03	Chris
20	2020-02-03	Elin
20	2020-02-02	Elin
20	2020-02-04	Elin
30	2020-02-06	Grace
30	2020-02-04	Irene
30	2020-02-05	Hella

 

select department_no,
       listagg(distinct name, ', ') as employees
from employees
group by department_no

만약 위 예시처럼 listagg 속에 distinct를 써주면 중복된 값이 모두 제거되고 unique한 값들만 aggregate됩니다.

 

Result Set

department_no	employees
10	Chris, Alice
20	Elin
30	Grace, Irene, Hella

 

 

 

select department_no,
       listaggdistinct(name, ', ') as employees
from employees
group by department_no

위처럼 listaggdistinct로 사용해도 됩니다.