윈도우 함수

윈도우 함수 (Window functions)

윈도우 함수는 쿼리 결과의 여러 행에 걸쳐 계산을 수행해요. HAVING 절 다음, ORDER BY 절 이전에 실행되며, 윈도우를 지정하는 OVER 절이 필요한 특별한 문법으로 호출해요.

출처: 문서

본문

윈도우 함수는 쿼리 결과의 여러 행에 걸쳐 계산을 수행해요. HAVING 절 다음, ORDER BY 절 이전에 실행돼요. 윈도우 함수를 호출하려면 윈도우를 지정하는 OVER 절을 사용하는 특별한 문법이 필요해요.

예를 들어, 다음 쿼리는 각 clerk에 대해 가격 기준 주문 순위를 매겨요.

SELECT orderkey, clerk, totalprice,
       rank() OVER (PARTITION BY clerk
                    ORDER BY totalprice DESC) AS rnk
FROM orders
ORDER BY clerk, rnk

윈도우는 두 가지 방식으로 지정할 수 있어요(WINDOW 절 참고):

  • WINDOW 절에 정의된 명명된 윈도우 사양을 참조하는 방식
  • 윈도우 구성 요소를 정의하면서 WINDOW 절에 미리 정의된 윈도우 구성 요소도 참조할 수 있는 인라인 윈도우 사양 방식

집계 함수 (Aggregate functions)

모든 집계 함수는 OVER 절을 붙이면 윈도우 함수로 사용할 수 있어요. 집계 함수는 현재 행의 윈도우 프레임 안에 있는 행들에 대해 각 행마다 계산돼요. 참고로 집계 중 정렬은 지원되지 않아요.

예를 들어, 다음 쿼리는 clerk별로 주문 가격의 일별 누적합(rolling sum)을 만들어요.

SELECT clerk, orderdate, orderkey, totalprice,
       sum(totalprice) OVER (PARTITION BY clerk
                             ORDER BY orderdate) AS rolling_sum
FROM orders
ORDER BY clerk, orderdate, orderkey

순위 함수 (Ranking functions)

  • cume_dist() → bigint

    값 그룹에서 한 값의 누적 분포(cumulative distribution)를 돌려줘요. 결과는 윈도우 파티션의 행 수로 나눈, 윈도우 정렬에서 해당 행보다 앞서거나 같은(peer) 행 수예요. 따라서 정렬에서 동률(tie) 값들은 같은 분포 값을 갖게 돼요. 윈도우 프레임은 지정하면 안 돼요.

  • dense_rank() → bigint

    값 그룹에서 한 값의 순위를 돌려줘요. rank()와 비슷하지만, 동률 값이 수열에 공백(gap)을 만들지 않아요. 윈도우 프레임은 지정하면 안 돼요.

  • ntile(n) → bigint

    각 윈도우 파티션의 행을 n개 버킷으로 나눠요. 버킷 값은 1부터 최대 n까지이며, 버킷 값끼리의 차이는 최대 1이에요. 파티션의 행 수가 버킷 수로 균등하게 나누어떨어지지 않으면 나머지 값들이 첫 번째 버킷부터 시작해 버킷당 하나씩 분배돼요.

    예를 들어 6개 행과 4개 버킷이면 버킷 값은 다음과 같아요: 1 1 2 2 3 4

    ntile() 함수에는 윈도우 프레임을 지정하면 안 돼요.

  • percent_rank() → double

    값 그룹에서 한 값의 백분율 순위를 돌려줘요. 결과는 (r - 1) / (n - 1)이에요. 여기서 r은 행의 rank()이고 n은 윈도우 파티션의 총 행 수예요. 윈도우 프레임은 지정하면 안 돼요.

  • rank() → bigint

    값 그룹에서 한 값의 순위를 돌려줘요. 순위는 해당 행보다 앞서면서 peer가 아닌 행의 수에 1을 더한 값이에요. 따라서 정렬에서의 동률 값은 수열에 공백을 만들 수 있어요. 순위는 각 윈도우 파티션마다 수행돼요. 윈도우 프레임은 지정하면 안 돼요.

  • row_number() → bigint

    윈도우 파티션 안의 행 정렬 순서에 따라, 1부터 시작하는 각 행의 고유·순차 번호를 돌려줘요. 윈도우 프레임은 지정하면 안 돼요.

값 함수 (Value functions)

기본적으로 null 값은 존중돼요. IGNORE NULLS가 지정되면 x가 null인 모든 행이 계산에서 제외돼요. IGNORE NULLS가 지정되었는데 모든 행에서 x가 null이라면 default_value가 반환되고, 그것도 지정되지 않았으면 null이 반환돼요.

  • first_value(x) → [입력과 동일]

    윈도우의 첫 번째 값을 돌려줘요.

  • last_value(x) → [입력과 동일]

    윈도우의 마지막 값을 돌려줘요.

  • nth_value(x, offset) → [입력과 동일]

    윈도우 시작에서 지정된 offset에 있는 값을 돌려줘요. offset은 1부터 시작해요. offset은 모든 스칼라 표현식이 될 수 있어요. offset이 null이거나 윈도우의 값 수보다 크면 null이 반환돼요. offset이 0이거나 음수면 오류예요.

  • lead(x[, offset[, default_value]]) → [입력과 동일]

    윈도우 파티션에서 현재 행보다 offset개 뒤에 있는 행의 값을 돌려줘요. offset은 0(현재 행)부터 시작해요. offset은 모든 스칼라 표현식이 될 수 있고, 기본 offset은 1이에요. offset이 null이면 오류가 발생해요. offset이 파티션 안에 없는 행을 가리키면 default_value가 반환되고, 지정되지 않았으면 null이 반환돼요. lead() 함수는 윈도우 정렬을 지정해야 해요. 윈도우 프레임은 지정하면 안 돼요.

  • lag(x[, offset[, default_value]]) → [입력과 동일]

    윈도우 파티션에서 현재 행보다 offset개 앞에 있는 행의 값을 돌려줘요. offset은 0(현재 행)부터 시작해요. offset은 모든 스칼라 표현식이 될 수 있고, 기본 offset은 1이에요. offset이 null이면 오류가 발생해요. offset이 파티션 안에 없는 행을 가리키면 default_value가 반환되고, 지정되지 않았으면 null이 반환돼요. lag() 함수는 윈도우 정렬을 지정해야 해요. 윈도우 프레임은 지정하면 안 돼요.

더 알아보기 (Learn more)

윈도우 함수로 행 간 계산과 순위·누적값을 다룰 수 있게 됐어요. 이어서 사용자 정의 함수(user-defined functions)를 살펴보면 좋아요.