Contour 윈도 함수

윈도 함수는 현재 행과 관련된 여러 행을 함께 계산하면서 원래 행을 유지합니다. 일반 그룹 집계처럼 여러 입력 행을 한 출력 행으로 합치지 않습니다. 각 행에 전체 최댓값, 그룹별 합계나 누적값을 붙일 때 사용합니다.

Filter 모드에는 OVER 식을 직접 넣을 수 없으므로 Add new column에서 값을 만든 뒤 필요한 행을 필터링합니다.

전체 행을 계산하는 OVER

OVER 뒤의 괄호가 비어 있으면 전체 행에 함수를 적용합니다. 다음 식은 date 열의 최댓값을 각 행에 붙입니다.

MAX("date") OVER ()

사용할 수 있는 함수와 모드별 제한은 함수 참조에 나와 있습니다.

PARTITION BY

PARTITION BY는 같은 값을 가진 행을 파티션으로 나눕니다. 함수는 각 파티션에서 별도로 계산합니다.

COUNT("person_id") OVER (PARTITION BY "gender")

이 식은 같은 gender 값을 가진 그룹의 person_id 개수를 각 행에 붙입니다.

ORDER BY와 프레임

ORDER BY로 파티션 안의 정렬 열과 ASC 또는 DESC 방향을 지정합니다. ROWS BETWEEN은 현재 행을 기준으로 계산할 행 위치의 범위를 정합니다.

예를 들어 이름순 행에서 현재 행이 Leonard이고 바로 앞이 Jonathan, 바로 뒤가 Mary이면 1 PRECEDING은 Jonathan, 1 FOLLOWING은 Mary입니다. Adam이 첫 행, Zoe가 마지막 행이면 각각 UNBOUNDED PRECEDING과 UNBOUNDED FOLLOWING 위치입니다.

다음 식은 날짜순 현재 매출 행과 그 앞의 최대 네 행을 합친 범위에서 평균을 계산합니다. 다섯 일의 기간이 아니라 최대 다섯 매출 행의 범위입니다.

AVG("sale_value") OVER (
    ORDER BY "date_of_sale" ASC
    ROWS BETWEEN 4 PRECEDING AND CURRENT ROW
)

파티션별 누적 매출 건수는 다음과 같이 계산합니다.

COUNT("sale_id") OVER (
    PARTITION BY "product_category"
    ORDER BY "date_of_sale" ASC
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
)

먼저 product_category로 파티션을 나누고 그 안에서 date_of_sale로 정렬합니다.

RANGE를 사용한 누적 합계

구매 비용의 누적 합계는 다음과 같이 계산할 수 있습니다.

SUM("item_cost") OVER (
    ORDER BY "purchase_date" ASC
    RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
)

종류별로 나누려면 PARTITION BY를 추가합니다.

SUM("item_cost") OVER (
    PARTITION BY "category"
    ORDER BY "purchase_date" ASC
    RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
)

ROWS는 정렬된 행의 위치를 기준으로 하고 RANGE는 정렬값을 기준으로 합니다. 위 RANGE의 CURRENT ROW 경계에는 현재 행과 같은 정렬값을 가진 행들도 포함됩니다. 정렬값이 같은 구매가 여러 개 있을 때 행마다 하나씩 더하는 ROWS 누적값과 구분합니다.

운석 데이터 예제

운석 낙하 CSV는 Meteoritical Society의 데이터를 NASA Data Portal을 통해 제공한 자료입니다. name, id, class, mass, year 등의 열을 사용합니다.

이 CSV의 일부 운석 이름에는 UTF-8로 해석할 수 없는 바이트가 있습니다. 가져올 때 인코딩 오류나 이름의 깨진 문자가 없는지 확인합니다.

분류별 최대 질량

다음 식으로 max_size_by_class 열을 만듭니다.

MAX("mass") OVER (PARTITION BY "class")

다음은 결과의 일부입니다.

분류별 누적 질량

SUM("mass") OVER (
    PARTITION BY "class"
    ORDER BY "year" ASC
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
)

분류 안에서 연도로 정렬한 뒤 현재 행과 이전 행의 질량을 더합니다. 같은 연도의 행 사이 순서가 필요하면 고유한 보조 키를 ORDER BY에 추가합니다. 예를 들어 id의 고유성을 확인한 경우에는 year 뒤에 id를 정렬 키로 사용할 수 있습니다.

총질량, 기여율과 개수

분류별 전체 합계는 정렬과 누적 프레임 없이 계산합니다.

SUM("mass") OVER (PARTITION BY "class")

분류의 총질량이 0이 아닐 때 개별 운석의 기여율은 다음과 같습니다.

"mass" / (SUM("mass") OVER (PARTITION BY "class")) * 100

분류별 운석 수는 id를 셉니다.

COUNT("id") OVER (PARTITION BY "class")

동률과 비결정성

ROW_NUMBER, FIRST, LAST, LEAD, LAG, NTILE, ARRAY_AGG와 ARRAY_AGG_DISTINCT 등 순서에 영향을 받는 계산은 같은 파티션 안에 정렬값이 같은 행이 있으면 실행마다 결과가 달라질 수 있습니다. ROWS를 사용하는 누적 계산도 동률 행에 어떤 순서가 부여되는지에 영향을 받습니다.

재현 가능한 결과가 필요하면 정렬 키 조합이 행을 구분하도록 고유한 보조 키를 추가합니다. 파티션만 지정한 배열 집계도 반환 원소의 순서를 고정하지 않습니다.