Oracle LISTAGG 설명 : 오라클 함수

1. 함수의 목적 
   
   Oracle LISTAGG는 ORDER BY 절에 지정된 각 그룹 내의 데이터를 열의 값으로 병합한다.

   ■ 단일 세트 집계 함수의 LISTAGG는 모든 행에 대해 작업하고 단일 출력 행을 리턴한다.

   ■ 그룹 집합 집계로서의  LISTAGG는 GROUP BY 절에 정의된 각 그룹에 대해 작업하고 결과 행을 반환한다.

   ■ 분석 함수로서의 LISTAGG는 query_partition_clause를 기반으로 쿼리 결과 세트를 그룹으로 분할한다.

2. 샘플을 통한 개념 퀵뷰

hr.employees 테이블의 Department_id=30에 있는 모든 직원을 hire_date, last_name으로 정렬하여 나열한다.

SELECT LISTAGG(last_name, '; ')
WITHIN GROUP (ORDER BY hire_date, last_name) "Emp_list",
MIN(hire_date) "Earliest"
FROM employees
WHERE department_id = 30;

Emp_list                                                                            Earliest
------------------------------------------------------------            ---------
Raphaely; Khoo; Tobias; Baida; Himuro; Colmenares    07-DEC-02

3. 사용방법

4. 함수 PARAMETER 설명

반환값의 데이터 타입은  VARCHAR2이며, measure_expr이 RAW일 경우에만 리턴 데이터타입도 RAW가 된다.

[measure_expr]
모든 표현식이 입력 파라미터로 가능하다. null값은 무시된다.

[delimiter]
값을 병합시 값과 값사이에 들어갈 자열을 정의한다. 정의하지 않으면  null이 디폴트가 된다.

[order_by_clause]
order_by_clause는 연결될 값의 순서를 결정한다.


5. 다양한 샘플표현

example1) 그룹 세트 집계 예로, employees 테이블의 각 부서 ID에 대해 해당 부서의 직원을 hire_date 순으로 나열한다.

SELECT department_id "Dept.",
LISTAGG(last_name, '; ') WITHIN GROUP (ORDER BY hire_date) "Employees"
FROM employees
GROUP BY department_id
ORDER BY department_id;

example2) employees테이블에서 2003 년 9 월 1 일 이전에 고용된 각 직원에 대해 직원의 부서, 고용 날짜 및 해당 부서의 다른 모든 직원도 2003 년 9 월 1 일 이전에 고용됬다면 Emp_list에 같이 조회한다.

SELECT department_id "Dept", hire_date "Date", last_name "Name",
LISTAGG(last_name, '; ') WITHIN GROUP (ORDER BY hire_date, last_name)
OVER (PARTITION BY department_id) as "Emp_list"
FROM employees
WHERE hire_date < '01-SEP-2003'
ORDER BY "Dept", "Date", "Name";