[오라클 레퍼런스 함수] LISTAGG - 그룹 내 정렬 데이터의 컬럼 값 결합(연결)

★

 

 

LISTAGG

 

구문

LISTAGG( [ ALL | DISTINCT ] measure_expr 
           [, 'delimiter'] [listagg_overflow_clause] )
           [ WITHIN GROUP  order_by_clause ]
           [OVER query_partition_clause]

 

listagg_overflow_clause::=

{ ON OVERFLOW ERROR }
|
{ ON OVERFLOW TRUNCATE [ 'truncation-indicator' ] [ { WITH | WITHOUT } COUNT ] }

 

order_by_clause::=

ORDER [ SIBLINGS ] BY
{ expr | position | c_alias }
[ ASC | DESC ]
[ NULLS FIRST | NULLS LAST ]
  [, { expr | position | c_alias }
     [ ASC | DESC ]
     [ NULLS FIRST | NULLS LAST ]
  ]...

 

query_partition_clause::=

PARTITION BY
  { expr[, expr ]...
  | ( expr[, expr ]... )
  }

 

참고:

ORDER BY 절 및 OVER 절의 구문, 의미, 그리고 제한 사항에 대한 정보는 "Analytic Functions"에 대한 내용을 참조하세요.

 

목적

지정된 속성(measure)에 대해, LISTAGG 함수는 ORDER BY 절에서 지정된 각 그룹 내에서 데이터를 정렬한 다음 속성 컬럼의 값을 결합(concatenate)합니다.

• 단일 집합(single-set) 집계 함수로서, LISTAGG는 모든 행에 대해 동작하고 단일 출력 행을 반환합니다.
• 그룹 집합(group-set) 집계로서, 이 함수는 GROUP BY 절에 의해 정의된 각 그룹에 대해 동작하며 출력 행을 반환합니다.
• 분석 함수로서, LISTAGG는 query_partition_clause의 하나 이상의 표현식을 기준으로 쿼리 결과 집합을 그룹으로 분할합니다.

 

이 함수에 대한 인수는 다음 규칙을 따릅니다:

• ALL 키워드는 선택적이며 의미를 분명하게 해주는 역할을 합니다.
• measure_expr는 속성 컬럼으로서 어떤 표현식이든 사용할 수 있습니다. 속성 컬럼의 null 값은 무시됩니다.
• delimiter는 속성 컬럼 값들을 구분하는 데 사용되는 문자열을 지정합니다. 이 절은 선택적이며 기본값은 NULL입니다.
  만약 measure_expr이 RAW 타입이라면 delimiter도 RAW 타입이어야 합니다. 이를 위해 delimiter를 RAW로 암시적으로 변환할 수 있는 문자열로 지정하거나, UTL_RAW.CAST_TO_RAW 함수를 사용하여 명시적으로 delimiter를 RAW로 변환할 수 있습니다.
• order_by_clause는 연결된 값을 반환하는 순서를 결정합니다. 이 함수는 ORDER BY 컬럼 목록이 고유한 순서를 지정하는 경우에만 결정론적(deterministic)입니다.
• 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바이트입니다. 반환 값이 반환 데이터 타입에 맞는지 확인할 때 최종 구분자는 포함되지 않습니다.

 

참고:

• MAX_STRING_SIZE 초기화 매개변수에 대한 정보는 Extended Data Types를 참조하십시오.
• Oracle Database Globalization Support Guide의 부록 C는 LISTAGG의 문자 반환 값에 할당되는 정렬 파생 규칙(collation derivation rules)에 대한 정보를 제공합니다.
• 자세한 내용은 Database Data Warehousing Guide를 참조하십시오.

 

listagg_overflow_clause

이 절은 반환 값이 반환 데이터 타입의 최대 길이를 초과할 때 함수의 동작 방식을 제어합니다.

ON OVERFLOW ERROR 이 절을 지정하면 함수는 ORA-01489 오류를 반환합니다. 이것이 기본 설정입니다.

ON OVERFLOW TRUNCATE 이 절을 지정하면 함수는 자른 속성 값 목록을 반환합니다.

 

• truncation_indicator는 자른 속성 값 목록 뒤에 추가될 문자열을 지정합니다. 이 절을 생략하면 자르기 표시는 줄임표 (...)입니다.
  
  만약 measure_expr이 RAW 유형인 경우, truncation indicator도 RAW 유형이어야 합니다. truncation indicator를 RAW로 암시적으로 변환할 수 있는 문자열로 지정하거나, truncation indicator를 명시적으로 RAW로 변환할 수 있습니다. 예를 들어 UTL_RAW.CAST_TO_RAW 함수를 사용할 수 있습니다.
• WITH COUNT를 지정한 경우, 자르기 표시 뒤에 데이터베이스는 괄호로 묶인 잘린 값의 수를 추가합니다. 이 경우 데이터베이스는 최종 구분 기호, 자르기 표시 및 괄호로 묶인 24자의 숫자 값에 대한 반환 값에 충분한 공간을 제공하기 위해 충분한 속성 값을 자릅니다.
• WITHOUT COUNT를 지정한 경우, 데이터베이스는 반환 값에서 잘린 값의 수를 제외합니다. 이 경우 데이터베이스는 최종 구분 기호와 자르기 표시에 공간을 제공하기 위해 충분한 속성 값을 자릅니다.

WITH COUNT 또는 WITHOUT COUNT를 지정하지 않은 경우 기본값은 WITH COUNT입니다.

 

집계 예제

다음의 단일 집합 집계 예제는 hr.employees 테이블에서 부서 30에 속하는 모든 직원을 고용 날짜와 성(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

 

다음의 그룹 집합 집계 예제는 hr.employees 테이블의 각 부서 ID에 대해 그 부서의 직원을 고용 날짜순으로 나열합니다:

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

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)'가 추가됩니다.

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;

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일 이전에 고용된 모든 다른 직원을 보여줍니다.

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

 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

 

---

출처: 오라클 레퍼런스

원문 링크: Oracle LISTAGG 함수 문서