윈도우 함수

PostgreSQL 문서에서는 윈도우 함수를 다음과 같이 정의합니다.

윈도우 함수는 현재 행과 관련된 테이블 행 집합에 대해 계산을 수행합니다. 이는 집계 함수로 수행할 수 있는 계산 유형과 비교할 수 있습니다. 그러나 일반적인 집계 함수와 달리, 윈도우 함수의 사용은 행이 단일 결과물 행으로 그룹화되지 않도록 합니다 — 행은 각각의 신원을 유지합니다. 윈도우 함수는 내부적으로 쿼리 결과의 현재 행 이상에 액세스할 수 있습니다.

이 문서에서는 Contour 표현식에서 사용하고자 하는 일부 윈도우 함수의 구문을 설명합니다. 윈도우 함수에 대한 자세한 배경 정보는 다음 추가 자료를 참조하십시오:

• 윈도우 함수 (PostgreSQL 문서)
• SQL 윈도우 함수 (Mode Analytics 튜토리얼)
• SQL 윈도우 함수 소개 (Apache Drill)
• 분석 함수 (Oracle 문서)

기본 구문

가장 기본적인 형태로, 윈도우 함수는 다음과 같이 나눌 수 있습니다:

<함수> OVER <어떤 윈도우>

여기서 함수는 지원되는 집계 함수 중 하나이고, 윈도우는 테이블의 행의 부분 집합입니다.

()를 사용하여 윈도우를 생략할 수 있습니다 - 이렇게 하면 함수가 테이블의 모든 행에 적용됩니다.

다음 예제는 날짜 열에서 최대 값을 가진 항목을 모든 행에 추가합니다.

MAX("date") OVER ()
-- 최대("날짜") 오버 ()

PARTITION BY

또한 창 정의 전에 선택적으로 PARTITION BY 절을 추가할 수 있습니다. PARTITION BY는 주어진 열의 값에 따라 창 내의 행을 그룹화합니다. 그런 다음 집계 함수가 각 파티션에 따로 적용됩니다.

예를 들어, 사람 기록이 있는 표에서 다음 표현식은 남성과 여성의 총 수를 계산하고 해당 행의 성별 값에 대한 각 행에 카운트를 추가합니다:

COUNT("person_id") OVER (PARTITION BY "gender") -- "gender"별로 파티션을 나눈 다음 "person_id" 개수를 계산

다음 기준으로 정렬

윈도우를 정의하는 표현식에서는 윈도우의 범위와 테이블의 행을 어떻게 정렬할지 지정해야 합니다. 이 하위 표현식은 다음과 같이 간략화할 수 있습니다.

<테이블 정렬 방식> 행 사이 <시작 위치>와 <끝 위치>

여기서 "테이블 정렬 방식"은 (1) 어떤 열로 정렬할지와 (2) 오름차순 또는 내림차순으로 정렬할지를 나타냅니다.

윈도우의 범위("시작 위치"와 "끝 위치")를 지정하는 다음과 같은 가능성이 있습니다.

• UNBOUNDED PRECEDING: 테이블 시작부터 현재 행까지.
• n PRECEDING (예: 2 PRECEDING): 현재 행 전 n 행부터 현재 행까지.
• CURRENT ROW
• n FOLLOWING (예: 5 FOLLOWING): 현재 행에서 현재 행 다음 n 행까지.
• UNBOUNDED FOLLOWING: 현재 행에서 테이블 끝까지.

다음은 위의 가능성을 레이블로 표시한 예시 테이블입니다:

FIRST_NAME |
------------
Adam       |<-- UNBOUNDED PRECEDING (무한한 앞)
...        |
Alison     |
Amanda     |
Jack       |
Jasmine    |
Jonathan   | <-- 1 PRECEDING (1명 앞)
Leonard    | <-- CURRENT ROW (현재 행)
Mary       | <-- 1 FOLLOWING (1명 뒤)
Tracey     |
...        |
Zoe        | <-- UNBOUNDED FOLLOWING (무한한 뒤)

(출처: blog.jooq.org)

따라서 매출 기록이 있는 표를 사용하여 다음을 사용하여 최근 5건의 매출에서 평균 매출 값을 찾을 수 있습니다:

-- 평균 판매값 계산 (판매 날짜 기준 오름차순 정렬)
-- 4개 이전 행부터 현재 행까지 범위 설정
AVG("sale_value") OVER (ORDER BY "date_of_sale" ASC 
    ROWS BETWEEN 4 PRECEDING AND CURRENT ROW)

"마지막 5 건의 판매"는 창입니다. OVER 뒤의 하위 표현식은 날짜별로 표를 정렬한 다음 각 행에 대해 이전 4개 행과 현재 행을 포함하여 평균을 계산합니다.

모든 것을 함께 묶기

다음 복잡한 예제는 위에서 언급한 모든 구문을 함께 사용합니다. 이 표현식은 현재 판매까지의 제품 카테고리별 누적 판매 건수를 보여줍니다.

-- 제품 카테고리별로 분할하고 판매 날짜를 오름차순으로 정렬합니다.
-- 현재 행 이전의 모든 행들을 포함한 구간에서 sale_id의 개수를 세어줍니다.
COUNT("sale_id") OVER (PARTITION BY "product_category" 
    ORDER BY "date_of_sale" ASC 
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)

참고로 항목들은 이제 파티션별로 정렬됩니다 – 다시 말해, 테이블이 먼저 파티션으로 나누어지고, 각 파티션 내에서 행이 정렬됩니다.

추가 예시

구매한 항목을 기록하는 테이블이 있다고 가정합니다. 다음 윈도우 함수를 사용하여 구매 누적 합계를 나타내는 새 열을 생성할 수 있습니다:

SUM("item_cost") OVER (ORDER BY "purchase_date" ASC 
    RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW )
-- "purchase_date"를 기준으로 오름차순 정렬하여 누적 합계를 계산
-- UNBOUNDED PRECEDING은 시작지점부터 현재 행까지의 범위를 의미

카테고리별로 그룹화된 누적 합계를 계산하려면 파티션을 추가할 수 있습니다:

-- 각 "category" 별로 누적 합계를 구합니다.
SUM("item_cost") OVER (PARTITION BY "category"
    -- "purchase_date"를 기준으로 오름차순 정렬합니다.
    ORDER BY "purchase_date" ASC
    -- 시작 범위는 제한 없이 이전 행부터 현재 행까지의 누적 합계를 구합니다.
    RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)

이것은 행을 "category" 열로 분할하고, 각 범주 내에서 구매 날짜별로 행을 정렬하며, 해당 범주의 모든 품목 비용의 누적 합계를 계산합니다.

운석 데이터를 사용한 예제

다음 예제는 운석 착지 데이터셋을 사용하여 직접 시도해 볼 수 있습니다. 이 데이터셋은 NASA Data Portal을 통해 Meteoritical Society에서 제공됩니다.

이 표현식은 각 클래스에서 가장 큰 운석을 계산합니다:

MAX("mass") OVER (PARTITION BY "class" ) -- "class"별로 파티션을 나눈 후 "mass"의 최대값을 계산합니다.

위의 윈도우 함수로 새 열 max_size_by_class를 도출하면:

… 결과 표는 다음과 같이 보입니다:

시간에 따른 운석 분류별 질량의 누적 합(진행 중인 총계)을 계산하려면:

Copied!
1
2
3
4
5
-- 각 클래스별로 나누고(year를 기준으로 오름차순 정렬한 후), 처음부터 현재 행까지의 누적된 질량 합계를 구합니다.
SUM("mass") OVER (PARTITION BY "class"
    ORDER BY "year" ASC
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
)

이것은 표를 운석 종류별로 분할하고, 각 분할을 날짜별로 정렬한 다음, 각 행에서 mass 열의 현재 행과 이전의 모든 행의 합을 계산하고, 그 합계를 현재 행의 새로운 열로 추가합니다.

운석 종류별 질량의 총계 (누적이 아닌)를 계산하려면:

Copied!
1
2
-- 각 "class"별로 그룹화하여 "mass"의 합계를 구합니다.
SUM("mass") OVER (PARTITION BY "class")

이 집계는 그 자체로 유용하지 않을 수 있지만, 확장하면 클래스의 총체적인 질량에 대한 이 운석의 기여도를 계산하는 더 흥미로운 통계를 구할 수 있습니다.

Copied!
1
"mass" / (SUM("mass") OVER (PARTITION BY "class")) * 100  -- 질량을 같은 클래스 내 질량 합계로 나눈 후 백분율로 표시

각 클래스별로 발견된 운석의 총 개수 (개수)를 계산하려면:

Copied!
1
COUNT("id") OVER (PARTITION BY "class") -- "class"에 따라 파티션된 id의 개수를 계산

비결정성