【오라클(Oracle) SQL 함수】 LISTAGG 함수

포스팅 목차

o LISTAGG

문 법

listagg_overflow_clause::=

목 적

지정된 측정값에 대해, LISTAGG 함수는 ORDER BY 절에 지정된 각 그룹 내의 데이터를 순서대로 정렬한 다음 측정값 열의 값을 연결한다.

단일 집합 집계 함수로 사용하는 경우 LISTAGG 함수는 모든 행에 대해 작업을 수행하여 단일 출력 행을 반환한다.
그룹 집합 집계 함수로 사용하는 경우 이 함수는 GROUP BY 절에서 정의한 각 그룹에 대해 작업을 수행하여 출력 행을 반환한다.
분석 함수로서 사용하는 경우, LISTAGG 함수는 query_partition_clause 절에서 지정한 하나 이상의 표현식을 기준으로 쿼리 결과 집합을 그룹으로 분할한다.

함수의 인수에는 다음과 같은 규칙이 적용된다.

ALL 키워드는 선택 사항이며, 작업 내용을 명확하게 지정하기 위하여 사용된다.
measure_expr 인수는 측정값 열이며, 모든 표현식을 지정 할 수 있다. 측정값 열의 NULL 값은 무시된다.
delimiter 인수는 측정값 열의 값을 구분하기 위하여 사용하는 문자열을 지정한다. 이 절은 선택적으로 지정하는 옵션이며 기본값은 NULL이다. measure_expr 인수가 RAW 타입인 경우 구분 기호(delimiter)는 RAW 타입이어야 한다. 이를 위해 구분 기호(delimiter)를 암묵적으로 RAW 타입으로 변환할 수 있는 문자열로 지정하거나, UTL_RAW.CAST_TO_RAW 함수와 같은 방법으로 명시적으로 구분 기호를 RAW 타입으로 변환할 수 있다.
order_by_clause 절에는 연결되는 값의 순서를 지정한다. ORDER BY 절에 지정한 열 목록이 고유한 순서로 지정된 경우에만, LISTAGG 함수는 결정론적(deterministic)으로 작업을 수행한다. 결정론적이라는 것은, 함수의 입력 값이 동일하다면, 함수의 출력 값도 항상 동일하다는 것을 의미한다. ORDER BY 절에 지정된 컬럼 목록이 고유한 순서로 지정되지 않는 경우에는 함수의 작업 결과에 대한 출력 값이 작업 시점마다 달라질 수 있다.
order_by_clause 절을 지정하는 경우에는 WITHIN GROUP도 함께 지정해야 하며, 그 반대의 경우도 마찬가지이다. 이 두 절은 함께 지정하거나 함께 지정하지 않아야 한다.

DISTINCT 키워드를 사용하면 목록에서 중복된 값을 제거한다.

측정값 열의 타입이 RAW 타입인 경우 반환되는 데이터 타입은 RAW 타입이다. 그렇지 않은 경우 반환되는 데이터 타입은 VARCHAR2이다.

반환 데이터 타입의 최대 길이는 MAX_STRING_SIZE 초기화 매개변수의 값에 따라 달라진다. MAX_STRING_SIZE = EXTENDED인 경우 VARCHAR2 및 RAW 데이터 타입의 최대 길이는 32767바이트이다. MAX_STRING_SIZE = STANDARD인 경우 최대 길이는 VARCHAR2 데이터 타입의 최대 길이는 4000바이트 이고, RAW 데이터 타입의 경우 2000바이트이다. 반환되는 값이 반환 데이터 타입에 맞는지 확인하는 경우 최종 구분자는 포함되지 않는다.

listagg_overflow_clause

이 절은 반환되는 값이 반환 데이터 타입의 최대 길이를 초과하는 경우 함수가 작동하는 방식을 제어한다.

ON OVERFLOW ERROR : 이 절을 지정하면 함수에서 ORA-01489 오류를 반환한다. 이것이 기본값이다.

ON OVERFLOW TRUNCATE : 이 절을 지정하면, 반환되는 문자열의 길이가 4000Byte 를 초과하는 경우 함수는 길이를 절사(절단)한 측정값 목록을 반환한다.

truncation_indicator 에는 측정값을 절사한 측정값 목록 뒤에 추가할 문자열을 지정한다. 이 절을 생략하는 경우 문자열을 절사(절단, 자른)한 것을 표시하기 위한 절단 표시기(잘림 표시기, truncation indicator)로 줄임표(...)를 사용한다. measure_expr 인수가 RAW 타입인 경우 절단 표시기(truncation indicator)는 RAW 타입이어야 한다. 절단 표시기(truncation indicator)를 암묵적으로 RAW 타입으로 변환할 수 있는 문자열로 지정하거나, UL_RAW.CAST_TO_RAW 함수 등과 같이 절단 표시기(truncation indicator)를 명시적으로 RAW 타입으로 변환하여 이 작업을 수행할 수 있다.
WITH COUNT 인수를 지정하는 경우 절단 표시기(truncation indicator) 뒤에 데이터베이스가 절사된 값의 개수를 괄호로 묶어서 추가한다. 이 경우, 데이터베이스는 반환 값에 최종 구분 기호, 절단 표시기, 그리고 괄호 안에 24글자의 숫자 값을 배치하기 위한 공간을 확보하기 위해 충분한 측정값을 잘라낸다.
WITHOUT COUNT를 지정하면 데이터베이스는 반환 값에서 잘린 값의 수를 생략한다. 이 경우, 데이터베이스는 반환 값에 최종 구분 기호와 절단 표시기를 배치하기 위한 공간을 확보하기 위해 충분한 측정값을 잘라낸다.

WITH COUNT 또는 WITHOUT COUNT 인수를 지정하지 않으면 기본값은 WITH COUNT 가 설정된다.

패키지 함수 비교 (Oracle, Pandas, R Prog, Dplyr, Sqldf, Pandasql, Data.Table, DuckDB)

LISTAGG Oracle Function 프로그래밍 비교

예 제

집계 함수

다음 단일 집합의 집계 예제는 hr.employees 테이블에 포함된 부서 30에 근무하는 모든 직원을 채용 날짜와 성을 기준으로 정렬하여 나열합니다.

Oracle Program

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

Results

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

다음 그룹 집합의 집계 예제에서는 hr.employees 테이블의 각 부서 ID별로 해당 부서의 직원들을 고용 일자를 기준으로 정렬하여 나열한다.

Oracle Program

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

Results

Dept. Employees
------ ------------------------------------------------------------
    10 Whalen
    20 Hartstein; Fay
    30 Raphaely; Khoo; Tobias; Baida; Himuro; Colmenares
    40 Mavris
    50 Kaufling; Ladwig; Rajs; Sarchand; Bell; Mallin; Weiss; Davie
       s; Marlow; Bull; Everett; Fripp; Chung; Nayer; Dilly; Bissot
       ; Vollman; Stiles; Atkinson; Taylor; Seo; Fleaur; Matos; Pat
       el; Walsh; Feeney; Dellinger; McCain; Vargas; Gates; Rogers;
        Mikkilineni; Landry; Cabrio; Jones; Olson; OConnell; Sulliv
       an; Mourgos; Gee; Perkins; Grant; Geoni; Philtanker; Markle
    60 Austin; Hunold; Pataballa; Lorentz; Ernst
    70 Baer
. . .

다음 예제는 이전 예제와 동일하지만 ON OVERFLOW TRUNCATE 절이 추가로 포함되어 있다. 이 예제에서는 반환 값의 최대 길이가 200바이트라는 인위적으로 작은 수라고 가정하고 있다. 50번 부서의 직원 목록이 200바이트를 초과하기 때문에 출력 결과 목록의 뒷 부분이 절단되고 최종 구분 기호 ';', 지정된 잘림 표시기 '...', 잘린 값의 수 '(23)' 가 추가되었다.

Oracle Program

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

Results

Dept. Employees
------ ------------------------------------------------------------
    10 Whalen
    20 Hartstein; Fay
    30 Raphaely; Khoo; Tobias; Baida; Himuro; Colmenares
    40 Mavris
    50 Kaufling; Ladwig; Rajs; Sarchand; Bell; Mallin; Weiss; Davie
       s; Marlow; Bull; Everett; Fripp; Chung; Nayer; Dilly; Bissot
       ; Vollman; Stiles; Atkinson; Taylor; Seo; Fleaur; ... (23)
    70 Baer
. . .

분석 함수

다음 분석 예제에서는 2003년 9월 1일 이전에 고용된 모든 직원에 대해 각 직원의 소속 부서, 채용 일자 및 해당 부서에서 2003년 9월 1일 이전에 채용된 다른 모든 직원들의 이름을 함께 보여준다.

Oracle Program

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

Results

 Dept Date      Name            Emp_list
----- --------- --------------- ---------------------------------------------
   30 07-DEC-02 Raphaely        Raphaely; Khoo
   30 18-MAY-03 Khoo            Raphaely; Khoo
   40 07-JUN-02 Mavris          Mavris
   50 01-MAY-03 Kaufling        Kaufling; Ladwig
   50 14-JUL-03 Ladwig          Kaufling; Ladwig
   70 07-JUN-02 Baer            Baer
   90 13-JAN-01 De Haan         De Haan; King
   90 17-JUN-03 King            De Haan; King
  100 16-AUG-02 Faviet          Faviet; Greenberg
  100 17-AUG-02 Greenberg       Faviet; Greenberg
  110 07-JUN-02 Gietz           Gietz; Higgins
  110 07-JUN-02 Higgins         Gietz; Higgins

오라클 SQL 함수(Oracle SQL Function) 목록 리스트 링크

[SQL, Pandas, R Prog, Dplyr, SQLDF, PANDASQL, DATA.TABLE] SQL EMP 예제로 만나는 테이블 데이터 처리 방법 리스트 링크

2024.01 생성

저작자표시 비영리 변경금지

'오라클 게시판 > 오라클 함수' 카테고리의 다른 글

【오라클(Oracle) SQL 함수】 LEAD 함수 (1)	2024.01.08
【오라클(Oracle) SQL 함수】 NTH_VALUE 함수 (1)	2024.01.07
오라클(Oracle) SQL 함수 리스트 (0)	2023.09.30
【오라클(Oracle) SQL 함수】 VALIDATE_CONVERSION 함수 (0)	2022.05.26
【오라클(Oracle) SQL 함수】 XMLTRANSFORM 함수 (0)	2021.10.25

【오라클(Oracle) SQL 함수】 LISTAGG 함수

o LISTAGG

문 법

목 적

listagg_overflow_clause

패키지 함수 비교 (Oracle, Pandas, R Prog, Dplyr, Sqldf, Pandasql, Data.Table, DuckDB)

예 제

집계 함수

분석 함수

'오라클 게시판 > 오라클 함수' 카테고리의 다른 글

댓글

티스토리툴바

【오라클(Oracle) SQL 함수】 LISTAGG 함수

o LISTAGG

문 법

목 적

listagg_overflow_clause

패키지 함수 비교 (Oracle, Pandas, R Prog, Dplyr, Sqldf, Pandasql, Data.Table, DuckDB)

예 제

집계 함수

분석 함수

'오라클 게시판 > 오라클 함수' 카테고리의 다른 글

관련글

댓글

티스토리툴바