[TIL] SQL - 윈도우 함수

1. Core Concepts

윈도우 함수의 기본 개념

• 윈도우 함수란 무엇인가?

  • 현재 행을 기준으로, 같은 윈도우에 속한 관련 행들과의 연산을 가능하게 하는 함수이다.

• 윈도우 함수는 왜 등장했는가?

  • 그룹의 집계값(평균, 개수, 순위 등)을 구하기 위해 GROUP BY를 사용하면 집계 결과만 남고 개별 행 정보가 사라진다는 한계가 있다.
  • 개별 행을 유지한 채로, 해당 행이 속한 그룹의 집계값을 함께 계산하기 위해 윈도우 함수가 등장했다.

• 윈도우 함수를 썼을 때 유용한 경우

  • 특정 시점의 데이터 값을 알고 싶을 때
  • 랭킹, 누적합을 구하고 싶을 때
  • 특정 시점의 이전 값을 알고 싶을 때

• Syntax

    SELECT col1, 
       window_function(col2) OVER (PARTITION BY col3 ORDER BY col4) AS new_column
	FROM table_name;

• window_function

  • 탐색 함수: LEAD, LAG, FIRST_VALUE, LAST_VALUE
  • 번호 지정 함수: ROW_NUMBER, RANK, DENSE_RANK
  • 집계 분석 함수: AVG, COUNT

• PARTITION BY

  • 윈도우 함수가 “어디까지를 하나의 계산 단위로 볼 것인지”를 정하는 기준

• ORDER BY

  • PARTITION BY로 나뉜 윈도우 내부에서 행의 순서를 결정하는 기준
  • 번호 지정 함수(ROW_NUMBER, RANK 등)에서는 필수적으로 사용된다
  • 탐색 함수(LAG, LEAD)에서는 논리적으로 필수지만, 문법적으로는 선택이다.

2. What I Learned

ROW_NUMBER

• ORDER BY에 유일하지 않은 값만 있으면, 같은 값들 사이의 순서는 보장되지 않는다.

ORDER BY

• SELECT 절의 ORDER BY:

  • 최종 출력 결과의 순서를 보장한다.

• PARTITION절의 ORDER BY :

  • 각 파티션 내부의 계산 순서를 보장한다.
  • PARTITION절에 ORDER BY가 있다고 해서 전체 데이터가 정렬된다고 보장할 수 없다.

3. Confusing Points

LAST_VALUE의 프레임 범위

오늘 수업 도중 각 user의 마지막 방문 월을 구하기 위해 다음과 같이 쿼리를 짰다.

SELECT
  *,
  LAST_VALUE(visit_month) OVER (PARTITION BY user_id ORDER BY visit_month) AS last_month
FROM
  advanced.analytics_function_01

하지만 결과 테이블을 보니 의도한 대로 작동하지 않았다.

의문이 들었던 점은 위 쿼리에서 윈도우 함수 부분만 FIRST_VALUE로 바꾼 쿼리는 의도대로 동작을 했다는 점이었다.

SELECT
  *,
  FIRST_VALUE(visit_month) OVER (PARTITION BY user_id ORDER BY visit_month) AS first_month
FROM
  advanced.analytics_function_01

공식 문서에서 그 답을 찾을 수 있었다.

BigQuery 공식 문서

FIRST_VALUE returns the value of the value_expression for the first row in the current window frame.

LAST_VALUE returns the value of the value_expression for the last row in the current window frame.

두 함수 모두 현재 윈도우 프레임에 따라 반환되는 값이 다르다는 것을 알 수 있었다.
윈도우 함수의 기본 프레임은 다음과 같다.

RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

• FIRST_VALUE

  • 현재 윈도우 프레임에서 첫 번째 행을 가져온다.
  • 기본 프레임의 시작이 UNBOUNDED PRECEDING이므로 항상 파티션의 첫 번째 행을 가져온다.
  • 그래서 모든 행에서 첫 번째 값을 반환한다.

• LAST_VALUE

  • 현재 윈도우 프레임에서 마지막 행을 가져온다.
  • 기본 프레임이 CURRENT ROW까지이므로 프레임의 마지막 행은 항상 현재 행이다.
  • 따라서 현재 행을 반환한다.

NULL 값을 바로 이전 값으로 처리하기

수업 시간에 연습 문제를 풀었는데, 문제의 조건이 주문 데이터에서 NULL 값이라고 되어있는 부분을 바로 이전 날짜의 값으로 채우는 것이었다.

WITH raw_data AS (
  SELECT DATE '2024-05-01' AS date, 15 AS number_of_orders UNION ALL
  SELECT DATE '2024-05-02', 13 UNION ALL
  SELECT DATE '2024-05-03', NULL UNION ALL
  SELECT DATE '2024-05-04', 16 UNION ALL
  SELECT DATE '2024-05-05', NULL UNION ALL
  SELECT DATE '2024-05-06', 18 UNION ALL
  SELECT DATE '2024-05-07', 20 UNION ALL
  SELECT DATE '2024-05-08', NULL UNION ALL
  SELECT DATE '2024-05-09', 13 UNION ALL
  SELECT DATE '2024-05-10', 14 UNION ALL
  SELECT DATE '2024-05-11', NULL UNION ALL
  SELECT DATE '2024-05-12', NULL
)

SELECT
  *,
  IFNULL(
    number_of_orders, 
    LAG(number_of_orders) OVER (ORDER BY date)
  ) AS filled_number_of_orders
FROM 
  raw_data

위 쿼리는 처음에 작성한 쿼리로, LAG 함수로 바로 이전 값으로 NULL값을 채우려 했다. 하지만 만약 테이블에 NULL값이 2번 이상 연속적으로 등장한다면 NULL이 아닌 마지막 값을 기억할 수 없다.

이런 상황에서는 LAST_VALUE를 사용하면 된다!

SELECT
  *,
  LAST_VALUE(number_of_orders IGNORE NULLS) OVER (ORDER BY date) AS filled_number_of_orders
FROM 
  raw_data

LAST_VALUE 프레임의 범위는 첫 번째 행부터 현재 행이므로 누적 상태를 표현할 수 있다.
여기서 IGNORE NULLS 옵션을 주면 NULL을 제외한 값들 중 가장 마지막 값(가장 최근 값)을 반환한다.

4. 느낀 점

코딩 테스트 문제를 풀면서 가장 어려웠던 부분이 윈도우 함수였다. 수업을 들으며 왜 이런 기능이 필요하게 되었는지 이해가 되었고, 실제 분석에서 자주 쓰일 수밖에 없겠구나 느꼈다.
같은 결과를 서브쿼리로도 만들 수 있지만, 윈도우 함수를 쓰면 가독성이 더 좋아진다. 이제는 윈도우 함수를 어려운 문법이 아닌, 문제를 더 명확히 풀기 위한 선택지 중 하나로 받아들이게 되었다.