[패스트캠퍼스] 데이터 분석 부트캠프 13기 SQL(3) 윈도우 함수(Window function)

이번에는 수업시간에 배운 윈도우 함수에 대해 정리해 보았다. 

 

윈도우 함수란?

• 윈도우 함수는 SQL 쿼리 내에서 데이터를 행 별로 처리할 수 있는 기능을 제공하는 함수입니다.
• 해당 행과 관련있는 행들을 함께 윈도우에 포함시켜 계산을 수행할 수 있습니다. 
• 원본 데이터를 유지한 채 각 행에 대한 계산을 함께 볼 수 있어 데이터의 맥락을 유지한 채 분석할 수 있습니다. 
• PARTITION BY로 윈도우 내에서 GROUP BY 와 유사하게 데이터를 나눌 수 있습니다. 

 

윈도우 함수 기본 구조

SELECT WINDOW_FUNCTION()
	OVER (window_spec) | OVER window_name

 

윈도우 함수는 크게 함수를 불러오는 window_function 파트와 

행을 처리하는 over_clause 파트로 나눌 수 있다. 

 

윈도우 함수별로 필요로 하는 인수가 다르기 때문에 괄호 안에는 함수에 맞게 인수를 넣어주면 된다. 

 

OVER() 안에는 크게 4가지 구문을 넣을 수 있는데 이는 모두 선택 사항이다. 

만약 모든 구문을 생략할 경우 OVER()는 전체 행에 대해서 윈도우 함수를 실행하여 출력한다. 

window_spec:
    [window_name] [partition_clause] [order_clause] [frame_clause]

 

window_name

윈도우에도 이름을 붙여 원할 때마다 불러올 수 있다. 이때 WINDOW라는 구문을 사용해 윈도우에 이름을 붙이고 어떤 내용인지 AS로 작성해준다. 

WINDOW window_name AS (window_spec)
    [, window_name AS (window_spec)] ...

 

각 윈도우 함수가 같은 윈도우에서 실행된다면 다음과 같이 마지막에 윈도우를 정의하여 쿼리를 훨씬 간단하게 작성할 수 있다.  

SELECT
  val,
  ROW_NUMBER() OVER w AS 'row_number',
  RANK()       OVER w AS 'rank',
  DENSE_RANK() OVER w AS 'dense_rank'
FROM numbers
WINDOW w AS (ORDER BY val);

 

그리고 OVER 구문 내에서 첫번째 파트로 window_name을 불러온 뒤에 다른 조건을 추가해 줄 수도 있다. 

SELECT
  DISTINCT year, country,
  FIRST_VALUE(year) OVER (w ORDER BY year ASC) AS first,
  FIRST_VALUE(year) OVER (w ORDER BY year DESC) AS last
FROM sales
WINDOW w AS (PARTITION BY country);

 

위와 같이 실행하면 중복되는 PARTITION BY country는 w라는 윈도우 이름으로 처리하고 각 행에 맞게 추가 조건을 달아 함수를 실행할 수 있다. 이 때 같은 조건을 뒤에 추가하는 것은 오류가 발생하므로 주의해야 한다. 

 

PARTITON BY

PARTITION BY는 윈도우 내에서 행들을 그룹지어 볼 수 있는 기능이다.

각 행들의 결과는 그 행이 포함되어 있는 파티션 내로 한정지어 처리된다. 

만약 PARTITION BY를 생략한다면 전체 행을 하나의 파티션으로 보고 처리한다. 

partition_clause:
    PARTITION BY expr [, expr] ...

 

PARTITION BY 뒤에 오는 인수는 표준 SQL에서는 칼럼 이름만 허용하지만 언어에 따라 표현식을 허용하는 경우도 있다. 

예를 들어 테이블에 TS라는 이름의 TIMESTAMP 칼럼이 있다면

표준 SQL에서는 PARTITION BY TS 만 허용하지만, 언어에 따라 PARTITION BY HOUR(TS) 도 허용할 수 있다. 

이렇게 하면 칼럼 내용이 같은 행끼리 파티션이 만들어지고 윈도우 함수는 그 파티션 내에서 처리되어 출력된다. 

 

ORDER BY

ORDER BY 는 잘 알 듯이 윈도우 내에서 정렬을 할 수 있는 구문이다. 순위를 매기거나 파티션을 나눌 때는 정렬이 필수이므로 원하는 칼럼으로 정렬해 함수를 적용하는 것이 중요하다. 

 

Frame_clause

frame은 파티션 내에서 만드는 더 작은 파티션이라 생각할 수 있다. 프레임을 어떻게 정의하느냐에 따라서 현재 행을 기준으로 앞뒤로 포함되는 행들이 달라지게 된다. 행의 수를 기준으로 삼을 수도 있고 행의 값을 기준으로 삼을 수도 있다. 

 

행이 한 칸씩 내려가면서 프레임도 함께 움직이며 현재 행을 포함하는 범위의 행들을 한 프레임에 놓고 윈도우 함수를 처리할 수 있는 기능이다. 

 

프레임 구문은 다음의 nonaggregation 함수들과 집계 함수에서만 작동한다. 

FIRST_VALUE()
LAST_VALUE()
NTH_VALUE()

 

밑의 함수들은 frame_clause를 사용하지 않아야 하며, 만약 넣더라도 무시하고 윈도우 함수를 실행하게 된다. 

CUME_DIST()
DENSE_RANK()
LAG()
LEAD()
NTILE()
PERCENT_RANK()
RANK()
ROW_NUMBER()

 

프레임 구문은 크게 두 파트로 나눌 수 있다. 

 

프레임을 ROWS로 나눌 것인지, 값의 범위인 RANGE로 나눌 것인지 정해주고

 

얼마나 포함할 것인지 범위를 정해주면 된다. 

frame_clause:
    frame_units frame_extent

frame_units:
    {ROWS | RANGE}
    
frame_extent:
    {frame_start | frame_between}

frame_between:
    BETWEEN frame_start AND frame_end

frame_start, frame_end: {
    CURRENT ROW
  | UNBOUNDED PRECEDING      -- 현재 행 이전의 모든 행
  | UNBOUNDED FOLLOWING      -- 현재 행 이후의 모든 행
  | expr PRECEDING
  | expr FOLLOWING
}

 

종합 예시) 

SELECT
         time, subject, val,
         FIRST_VALUE(val)  OVER w AS 'first',
         LAST_VALUE(val)   OVER w AS 'last',
         NTH_VALUE(val, 2) OVER w AS 'second',
         NTH_VALUE(val, 4) OVER w AS 'fourth'
       FROM observations
       WINDOW w AS (PARTITION BY subject ORDER BY time
                    ROWS UNBOUNDED PRECEDING);

 

 

비집계 함수

집계함수는 GROUP BY 에도 쓰이기 때문에 생략하고 비집계함수만 살펴보면, 다음과 같다. 

CUME_DIST()	Cumulative distribution value
DENSE_RANK()	Rank of current row within its partition, without gaps
FIRST_VALUE()	Value of argument from first row of window frame
LAG()	Value of argument from row lagging current row within partition
LAST_VALUE()	Value of argument from last row of window frame
LEAD()	Value of argument from row leading current row within partition
NTH_VALUE()	Value of argument from N-th row of window frame
NTILE()	Bucket number of current row within its partition.
PERCENT_RANK()	Percentage rank value
RANK()	Rank of current row within its partition, with gaps
ROW_NUMBER()	Number of current row within its partition