컴퓨터/데이터베이스

[데이터 베이스] WINDOW 함수

sidedoor 2024. 2. 11. 21:32

window function

행과 행 간의 관계를 정의하기 위해 사용되는 함수로 테이블의 행들에 대해 계산을 수행하면서 행들을 개별적으로 그룹화하지 않아 출력을 할 때 각 행에 대한 값들을 유지하는 함수이다.

 

SELECT column, AGG_FUNC(column) OVER (
    PARTITION BY column
    ORDER BY column
) FROM table;

 

이는 위와 같은 구문의 형태로 사용되는데 AGG_FUNC에 사용할 수 있는 함수는 아래와 같다.

 

1. 기본 집계 함수

  • COUNT(): 지정된 column에 NULL 값을 제외한 값의 수를 계산한다.
  • SUM(): 숫자 column의 총합을 계산한다.
  • AVG(): 숫자 column의 평균 값을 계산한다.
  • MIN(): column의 최소값을 찾는다.
  • MAX(): column의 최대값을 찾는다.

2. 확장된 집계 함수

  • STDDEV() / STDDEV_POP(): 표준 편차를 계산한다.
  • VAR_POP(): 분산을 계산한다.
  • CORR(column1, column2): 두 column 간의 상관계수를 계산한다.

3. 순위 관련 함수

  • ROW_NUMBER(): partition의 각 행에 대해 고유한 순서 번호를 할당한다.
  • RANK(): 동일한 값을 가진 행에 동일 순위를 부여하고, 다음 순위는 동일 순위 다음의 순서를 건너뛴 번호를 부여한다.
  • DENSE_RANK(): RANK()와 유사하지만 순위 사이에 건너뛰기 없이 연속적인 순위를 부여한다.
  • NTILE(n): 결과 세트를 n 개의 동일한 부분으로 나누고, 각 행이 속한 부분의 번호를 반환한다.

PARTITION의 경우 데이터를 특정 기준열에 따라 파티션으로 나누고, 각 파티션 내에서 독립적으로 함수를 적용한다.

 

실제 데이터가 아래와 같이 있다고 가정했을 때 window function예시를 살펴보면 아래와 같다.

regiom sale_date sales
East 2021-01-01 100
East 2021-01-02 150
West 2021-01-01 200
West 2021-01-02 100

 

SELECT *, SUM(sales) OVER (
	PARTITION BY region 
   	ORDER BY sale_date
) AS cumulative_sales
FROM sales;

 

PARTITION BY region을 통해 데이터를 region별로 구분하여 각 rigion에 대한 계산을 독립적으로 수행한다.

 

region sale_date sales cumulative_sales
East 2021-01-01 100 100
East 2021-01-02 150 250
West 2021-01-01 200 200
West 2021-01-02 100 300

 

출력 결과는 sales 테이블의 모든 행을 포함하고, 각 행에는 해당 지역의 sale_date까지의 누적 판매량이 추가로 표시된다.

이때 총 판매량의 합이 표시되는 것이 아니라 누적 판매량이 표시되는 이유는 ORDER BY를 사용하여 순서대로 표기하였기 때문에 필터의 영향을 받아 해당하는 부분까지의 합을 출력했기 때문이다.

FRAMING

데이터의 특정 범위에 대해 집계나 계산을 수행할 수 있도록 정의한다.

이 프레임은 현재 행을 기준으로 전후의 행들을 포함할 수 있고, 이를 통해 누적 합계, 이동 평균, 이동 합계와 같은 계산을 할 수 있다.

 
SUM(sales) OVER (
    PARTITION BY region
    ORDER BY sale_date
    ROWS BETWEEN 1 PRECEDING AND CURRENT ROW
)

 

기본적인 FRAMING구문은 위처럼 ORDER BY의 뒤에 오는데 일반적으로 프레임의 범위를 지정하는 ROWS 또는 RANGE 키워드와 함께 사용된다.

ROWS: 물리적 행의 수를 기반으로 프레임을 정의한다.

RANGE: 현재 행의 값과 동일한 값을 가진 행을 기반으로 프레임을 정의한다.

 

 - 프레임 지정 방법

n PRECEDING: 현재 행 바로 n번째 앞의 행부터 시작하는 프레임을 의미한다.

CURRENT ROW: 현재 행을 의미한다.

n FOLLOWING: 현재 행 바로 n번째 다음의 행까지 포함하는 프레임을 의미한.

UNBOUNDED PRECEDING: 파티션의 시작부터 현재 행까지를 포함하는 프레임을 의미한다.

UNBOUNDED FOLLOWING: 현재 행부터 파티션의 끝까지를 포함하는 프레임을 의미한다.

 

따라서 위의 코드에서는 각 region내에서 sale_date 순으로 정렬된 데이터에 대해 현재 행과 바로 이전 행을 포함하는 프레임에 대해 sales의 합계를 계산한다.