윈도우 함수

윈도우 함수 (Window Functions)

윈도우 함수를 사용하면 윈도우에 걸쳐 평균 계산, 정렬, 순위, 항목 개수, 합계 계산, 최소·최대 값 찾기를 할 수 있어요.

중요: 윈도우 함수로 쿼리하려면 Pinot의 다중 스테이지 엔진(MSE)을 활성화해야 해요. 다중 스테이지 엔진(MSE) 활성화 및 사용을 참고하세요.

출처: 문서

본문

윈도우 함수 개요

윈도우 함수 기능의 개요예요.

윈도우 함수 문법

Pinot의 윈도우 함수(windowedCall)는 다음 문법 정의를 가집니다:

windowedCall:
      windowFunction
      OVER 
      window

windowFunction:
      function_name '(' value [, value ]* ')'
   |
      function_name '(' '*' ')'

window:
      '('
      [ PARTITION BY expression [, expression ]* ]
      [ ORDER BY orderItem [, orderItem ]* ]
      [
          frame_clause
          [ exclude_clause ]
      ]
      ')'

frame_clause:
      RANGE BETWEEN frame_start AND frame_end
    |
      ROWS BETWEEN frame_start AND frame_end
    |
      RANGE frame_start
    |
      ROWS frame_start

exclude_clause:
      EXCLUDE NO OTHERS
    |
      EXCLUDE CURRENT ROW
    |
      EXCLUDE GROUP
    |
      EXCLUDE TIES
      
frame_start:
      UNBOUNDED PRECEDING
    |
      offset PRECEDING
    |
      CURRENT ROW

frame_end:
      offset PRECEDING
    |
      CURRENT ROW
    |  
      offset FOLLOWING
     
frame_end:
      offset PRECEDING
    |
      CURRENT ROW
    |
      offset FOLLOWING
    |
      UNBOUNDED FOLLOWING

windowedCall은 실제 윈도우 연산을 가리킵니다.
windowFunction은 사용된 윈도우 함수를 가리키며, 지원 윈도우 함수를 참고하세요.
window는 윈도우 정의/윈도잉 메커니즘이며, 지원 윈도우 메커니즘을 참고하세요.

더 구체적인 윈도우 함수 사용 예시는 예시 섹션으로 건너뛸 수 있어요.

정밀도 처리

다중 스테이지 쿼리 엔진의 윈도우 집계 함수(SUM, MIN, MAX)는 컬럼의 데이터 타입을 존중하므로 결과가 기본 컬럼의 정밀도를 보존합니다:

컬럼 타입	동작
`INT` / `LONG`	`SUM`은 `LONG`을 반환해 큰 값(> 2^53)이 `DOUBLE`로 cast될 때 발생하는 정밀도 손실을 피합니다. `MIN` / `MAX`는 원시 비교를 사용합니다.
`BIG_DECIMAL`	`SUM`, `MIN`, `MAX`는 `BIG_DECIMAL` 값에서 직접 동작해 전체 십진수 정밀도를 보존합니다.
`FLOAT` / `DOUBLE`	`SUM`은 `DOUBLE`을 반환합니다(이전 동작과 동일).

윈도우 함수 쿼리 레이아웃 예시

다음 쿼리는 윈도우 함수의 완전한 구성 요소를 보여줘요. PARTITION BY, ORDER BY, FRAME 절은 모두 선택 사항입니다.

SELECT FUNC(column1) OVER (PARTITION BY column2 ORDER BY column3 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
    FROM tableName
    WHERE filter_clause

윈도우 메커니즘 (OVER 절)

Partition by 절

PARTITION BY 절이 지정되면 PARTITION BY 절에 나타나는 컬럼의 값에 따라 중간 결과가 서로 다른 파티션으로 그룹화됩니다.
PARTITION BY 절이 지정되지 않으면 전체 결과가 하나의 큰 파티션으로 간주됩니다(즉 결과 집합에 파티션이 하나만 있습니다).

Order by 절

ORDER BY 절이 지정되면 같은 파티션 안의 모든 행이 윈도우 ORDER BY 절에 나타나는 컬럼의 값에 따라 정렬됩니다. ORDER BY 절은 파티션 안의 행이 처리되는 순서를 결정해요.
PARTITION BY 절은 지정했지만 ORDER BY 절을 지정하지 않으면 행의 순서는 정의되지 않습니다. 출력을 정렬하려면 쿼리에서 전역 ORDER BY 절을 사용하세요.

Frame 절

경고: RANGE 타입 윈도우 프레임은 현재 offset PRECEDING / offset FOLLOWING과 함께 사용할 수 없습니다.

현재 지원되는 윈도우 프레임 절:

RANGE frame_start — frame_start는 UNBOUNDED PRECEDING 또는 CURRENT ROW(frame_end는 기본적으로 CURRENT ROW)
ROWS frame_start — frame_start는 UNBOUNDED PRECEDING, offset PRECEDING 또는 CURRENT ROW(frame_end는 기본적으로 CURRENT ROW)
RANGE BETWEEN frame_start AND frame_end — frame_start는 UNBOUNDED PRECEDING 또는 CURRENT ROW, frame_end는 CURRENT ROW 또는 UNBOUNDED FOLLOWING
ROWS BETWEEN frame_start AND frame_end — frame_start / frame_end는 다음 중 하나:
- UNBOUNDED PRECEDING (frame_start만)
- offset PRECEDING (offset은 정수 리터럴)
- CURRENT ROW
- offset FOLLOWING (offset은 정수 리터럴)
- UNBOUNDED FOLLOWING (frame_end만)

Exclude 절

Pinot이 윈도우 함수를 평가하기 전에 현재 윈도우 프레임에서 행을 제거하려면 선택적 EXCLUDE 절을 사용하세요:

EXCLUDE NO OTHERS가 기본 동작입니다.
EXCLUDE CURRENT ROW는 현재 행만 제거합니다.
EXCLUDE GROUP은 현재 행과 그 모든 피어를 제거합니다.
EXCLUDE TIES는 현재 행의 피어를 제거하지만 현재 행은 유지합니다.

Pinot은 위에 나열된 지원 ROWS 프레임과 지원 RANGE 프레임에서 EXCLUDE를 지원해요. GROUP과 TIES는 윈도우의 ORDER BY 키를 사용해 피어를 정의합니다. 윈도우가 ORDER BY를 지정하지 않으면 전체 파티션이 하나의 피어 그룹으로 처리됩니다.

Pinot은 SUM, COUNT, AVG, MIN, MAX, BOOL_AND, BOOL_OR, FIRST_VALUE, LAST_VALUE에 대해 EXCLUDE를 지원합니다.

경고: 쿼리가 EXCLUDE CURRENT ROW, EXCLUDE GROUP 또는 EXCLUDE TIES에 의존한다면 롤링 업그레이드 중 브로커보다 서버를 먼저 업그레이드하세요. 구형 서버는 비기본 exclude를 기본 EXCLUDE NO OTHERS로 처리합니다.

RANGE 모드에서 frame_start의 CURRENT ROW는 프레임이 현재 행의 첫 번째 피어 행에서 시작함을 의미하고(윈도우 ORDER BY 절이 현재 행과 동등하게 정렬하는 행), frame_end의 CURRENT ROW는 프레임이 현재 행의 마지막 피어 행에서 끝남을 의미합니다. ROWS 모드에서 CURRENT ROW는 단순히 현재 행을 뜻합니다.

ORDER BY 절이 지정되지 않으면 윈도우 프레임은 항상 RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING이며 수정할 수 없습니다. ORDER BY 절이 있으면 쿼리에 명시적 윈도우 프레임이 없을 때 기본 프레임은 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW입니다.

OVER 절에 FRAME, PARTITION BY, ORDER BY가 모두 없으면(빈 OVER) 전체 결과 집합이 하나의 파티션으로 간주되고 윈도우에 프레임이 하나 있습니다.

OVER 절은 지정한 지원 윈도우 함수를 행 그룹에 적용해 각 행에 대해 단일 결과를 반환합니다. OVER 절은 행이 어떻게 배열되고 그 행에 대해 집계가 어떻게 수행되는지 지정해요.

OVER 절 안에는 PARTITION BY 절, ORDER BY 절, FRAME 절의 세 가지 선택 구성 요소가 있습니다.

윈도우 함수

윈도우 함수는 일반적으로 다음을 수행하는 데 사용됩니다:

평균 계산
항목 순위
항목 개수
합계 계산
최소·최대 값 찾기

지원 윈도우 함수는 다음 표에 나열되어 있습니다.

함수	설명	예시	레코드 미선택 시 기본값
AVG	정의된 윈도우에서 숫자 컬럼 값의 평균 반환.	`AVG(playerScore)`	`Double.NEGATIVE_INFINITY`
BOOL_AND	윈도우의 값 하나라도 `false`면 `false`, 값 하나라도 `null`이면 `null`, 모든 값이 `true`이면 `true` 반환.		`null`
BOOL_OR	윈도우의 값 하나라도 `true`면 `true`, 값 하나라도 `null`이면 `null`, 모든 값이 `false`이면 `false` 반환.		`null`
COUNT	윈도우의 값 개수 반환	`COUNT(*)`	`0`
MIN	숫자 컬럼의 최소 값을 `Double`로 반환	`MIN(playerScore)`	`null`
MAX	숫자 컬럼의 최대 값을 `Double`로 반환	`MAX(playerScore)`	`null`
SUM	숫자 컬럼 값의 합을 `Double`로 반환	`SUM(playerScore)`	`null`
LEAD	`LEAD` 함수는 self-join 없이 같은 결과 집합 안의 후속 행에 접근을 제공.	`LEAD(column_name [, offset [, default_value]]) [IGNORE NULLS \| RESPECT NULLS]`
LAG	`LAG` 함수는 self-join 없이 같은 결과 집합 안의 이전 행에 접근을 제공.	`LAG(column_name [, offset [, default_value]]) [IGNORE NULLS \| RESPECT NULLS]`
FIRST_VALUE	`FIRST_VALUE` 함수는 윈도우의 첫 번째 행의 값을 반환.	`FIRST_VALUE(salary)`
LAST_VALUE	`LAST_VALUE` 함수는 윈도우의 마지막 행의 값을 반환	`LAST_VALUE(salary)`
ROW_NUMBER	파티션 안의 현재 행 번호를 1부터 시작해 반환.	`ROW_NUMBER()`
RANK	간격 포함 현재 행의 순위 반환 — 즉 피어 그룹의 첫 번째 행의 `row_number`.	`RANK()`
DENSE_RANK	간격 없이 현재 행의 순위 반환.	`DENSE_RANK()`

ROW_NUMBER, RANK, DENSE_RANK 윈도우 함수는 정의상 전체 파티션에 적용되므로 윈도우 프레임 절을 지정할 수 없어요. 마찬가지로 LAG와 LEAD는 행 offset이 함수 자체의 입력이므로 윈도우 프레임 절을 지정할 수 없습니다. EXCLUDE는 윈도우 프레임을 수정하므로 이 함수들과 함께 사용할 수 없습니다.

고객 ID별 거래 합계

각 고객 ID에 대해 결제 날짜 순으로 정렬된 누적 거래액 합계를 계산해요(기본 프레임은 UNBOUNDED PRECEDING과 CURRENT ROW입니다).

SELECT customer_id, payment_date, amount, SUM(amount) OVER(PARTITION BY customer_id ORDER BY payment_date) from payment;

customer_id	payment_date	amount	sum
1	2023-02-14 23:22:38.996577	5.99	5.99
1	2023-02-15 16:31:19.996577	0.99	6.98
1	2023-02-15 19:37:12.996577	9.99	16.97
1	2023-02-16 13:47:23.996577	4.99	21.96
2	2023-02-17 19:23:24.996577	2.99	2.99
2	2023-02-17 19:23:24.996577	0.99	3.98
3	2023-02-16 00:02:31.996577	8.99	8.99
3	2023-02-16 13:47:36.996577	6.99	15.98
3	2023-02-17 03:43:41.996577	6.99	22.97
4	2023-02-15 07:59:54.996577	4.99	4.99
4	2023-02-16 06:37:06.996577	0.99	5.98

고객 ID별 최소·최대 거래 찾기

각 고객이 만든 모든 거래를 비교해 최저 금액(MIN() 사용) 또는 최고 금액(MAX() 사용) 거래를 계산해요(기본 프레임은 UNBOUNDED PRECEDING과 UNBOUNDED FOLLOWING입니다). 다음 쿼리는 최저 금액 거래를 찾는 방법을 보여줍니다.

SELECT customer_id, payment_date, amount, MIN(amount) OVER(PARTITION BY customer_id) from payment;

customer_id	payment_date	amount	min
1	2023-02-14 23:22:38.996577	5.99	0.99
1	2023-02-15 16:31:19.996577	0.99	0.99
1	2023-02-15 19:37:12.996577	9.99	0.99
2	2023-04-30 04:34:36.996577	4.99	4.99
2	2023-04-30 12:16:09.996577	10.99	4.99
3	2023-03-23 05:38:40.996577	2.99	2.99
3	2023-04-07 08:51:51.996577	3.99	2.99
3	2023-04-08 11:15:37.996577	4.99	2.99

고객 ID별 평균 거래액

고객이 만든 모든 거래에 대한 평균 거래액을 계산해요(기본 프레임은 UNBOUNDED PRECEDING과 UNBOUNDED FOLLOWING입니다).

SELECT customer_id, payment_date, amount, AVG(amount) OVER(PARTITION BY customer_id) from payment;

customer_id	payment_date	amount	avg
1	2023-02-14 23:22:38.996577	5.99	5.66
1	2023-02-15 16:31:19.996577	0.99	5.66
1	2023-02-15 19:37:12.996577	9.99	5.66
2	2023-04-30 04:34:36.996577	4.99	7.99
2	2023-04-30 12:16:09.996577	10.99	7.99
3	2023-03-23 05:38:40.996577	2.99	3.99
3	2023-04-07 08:51:51.996577	3.99	3.99
3	2023-04-08 11:15:37.996577	4.99	3.99

영업팀 연간 누계 매출 순위

ROW_NUMBER()를 사용해 연간 누계 매출로 팀원 순위를 매깁니다(기본 프레임은 UNBOUNDED PRECEDING과 UNBOUNDED FOLLOWING입니다).

SELECT ROW_NUMBER() OVER(ORDER BY SalesYTD DESC) AS Row,   
    FirstName, LastName AS "Total sales YTD"   
FROM Sales.vSalesPerson;

Row	FirstName	LastName	Total sales YTD
1	Joe	Smith	2251368.34
2	Alice	Davis	2151341.64
3	James	Jones	1551363.54
4	Dane	Scott	1251358.72

고객 ID별 거래 수

각 고객이 만든 거래 수를 셉니다(기본 프레임은 UNBOUNDED PRECEDING과 UNBOUNDED FOLLOWING입니다).

SELECT customer_id, payment_date, amount, count(amount) OVER(PARTITION BY customer_id) from payment;

customer_id	payment_date	amount	count
1	2023-02-14 23:22:38.99657	10.99	2
1	2023-02-15 16:31:19.996577	8.99	2
2	2023-04-30 04:34:36.996577	23.50	3
2	2023-04-07 08:51:51.996577	12.35	3
2	2023-04-08 11:15:37.996577	8.29	3

윈도우 함수

윈도우 함수 (Window Functions)

본문

윈도우 함수 개요

윈도우 함수 문법

정밀도 처리

윈도우 함수 쿼리 레이아웃 예시

윈도우 메커니즘 (OVER 절)

Partition by 절

Order by 절

Frame 절

Exclude 절

윈도우 함수

윈도우 집계 쿼리 예시

고객 ID별 거래 합계

고객 ID별 최소·최대 거래 찾기

고객 ID별 평균 거래액

영업팀 연간 누계 매출 순위

고객 ID별 거래 수

더 알아보기 (Learn more)