윈도우 함수란?

SQL 결과에 partition을 설정하거나, 결과 전체에 별도의 조건을 부여하여 그 조건에 따른 새로운 컬럼을 생성하는 함수를 의미한다.

왜 윈도우 함수라 칭할까

이전에 회원의 구매일로부터 최근 30일 전에 발생한 총 매출금액을 구하는 목적으로 쿼리를 짜던 중, OVER절에, OVER(PARTITION BY user_id ORDER BY UNIX_TIMESTAMP(date) RANGE BETWEEN 30*24*60*60 PRECEDING AND CURRENT ROW) 라는 쿼리를 짰을 때였다.

그때 쿼리 리뷰를 하면서 팀 동료분께서 윈도우가 이렇게 움직일 수 있구나 라고 말씀 해 주셔서 왜 윈도우 함수라고 표현하는지 비유적으로 이해할 수 있었다.

커다란 창 틀이 있고, 그 창틀 안에서 창문이 움직이는 과정이 떠올랐던 순간이었다.

• 커다란 창 틀 == 회원의 전체 구매 내역
• 좌우로 움직이는 창문이 멈춰선 위치 == 회원이 구매한 날짜
• 창문의 길이 == 30일

---

종류

생각 외로, 굉장히 다양하다.

• 기본적으로 순서를 부여하는 ROW_NUMBER()
• 순위를 부여하는 RANK()
• 동일 순위 번호를 뛰어넘지않는 DENSE_RANK()

---

row 기준으로 이전의 값 혹은 이후의 값을 가져오는

• 다음 행
  LEAD(column_name, N번째 행, 없을 경우 대체값( default Null ))
• 이전 행
  LAG(column_name, N번째 행, 없을 경우 대체값( default Null ))

---

• percentile 값을 가져올 때
  APPROX_PERCENTILE(column_name, percentile값(소수점)) OVER(PARTITION BY product_id ORDER BY id ASC)

---

• 상품마다 daily 누적 매출 합을 구하는 컬럼 쿼리 예시
  SUM(revenue) OVER(PARTITION BY product_id ORDER BY date ASC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)

• 회원마다 구매가 발생할 때, 그 구매를 포함한 모든 누적 구매 건수를 구하는 쿼리 예시
  COUNT(sales_id) OVER(PARTITION BY user_id ORDER BY date ASC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)

• ↑ 그 구매를 제외한 직전까지의 모든 누적 구매 건수를 구하는 쿼리 예시
  COUNT(sales_id) OVER(PARTITION BY user_id ORDER BY date ASC ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING ROW)

• 회원마다 구매가 발생할 때, 구매가 발생한 날짜로부터 과거 90일간의 매출 총 금액
  SUM(revenue) OVER(PARTITION BY user_id ORDER BY UNIX_TIMESTAMP(date) RANGE BETWEEN 90*24*60*60 PRECEDING AND CURRENT ROW)

  [ 참고 ]
  ↑ 위 쿼리는 Bigquery에서는 가능하지만, AWS Athena에서는 아직 지원이 되지 않는다.
  `RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING` 만 지원됨 확인
  SQL 환경에 따라서 window 함수 지원 여부가 달라질 수 있음

---

특이점

• group by의 기준이 될 수 없다.
• 누적 합, 누적 개수 등은 SUM, COUNT의 집계함수처럼 보이지만 group by 없이 사용하는 윈도우 함수이다.