WINDOW 함수

BigQuery 입문

Matthew Forrest

Field CTO

WINDOW 함수란

ROLLING SUM이 있는 WINDOW를 보여주는 일러스트

WINDOW 함수 사용 시점

유형별 WINDOW 함수

¹ https://towardsdatascience.com/a-guide-to-advanced-sql-window-functions-f63f2642cbf9

WINDOW 구조, PARTITION, ORDER BY

SELECT
  customer_id,
  order_date,
  order_total,
  ROW_NUMBER() OVER(

    PARTITION BY customer_id

    ORDER BY order_date

  ) AS order_sequence
FROM orders;

ROW_NUMBER(): 행 번호를 반환하는 윈도 함수
OVER(): 윈도 프레임 정의
PARTITION BY customer_id: 고객 기준으로 그룹화
ORDER BY order_date: 각 파티션 내에서 정렬
order_sequence: 윈도 함수 결과

RANK와 PERCENT_RANK

SELECT
  product_id,
  product_photos_qty,
  -- 각 행의 서수 순위
  RANK() OVER(
    ORDER BY product_photos_qty DESC
  ) as rank,
  -- 각 행의 백분위 순위
  PERCENT_RANK() OVER(
    ORDER BY product_photos_qty
  ) as percent
FROM ecommerce.ecomm_products 
ORDER BY product_photos_qty DESC;

RANK와 PERCENT_RANK 쿼리 결과

LAG와 LEAD

SELECT
  product_id,
  -- 이전 행의 값을 반환
  LAG(product_photos_qty) OVER(
    ORDER BY product_photos_qty
  ) as lag,
  product_photos_qty,
  -- 다음 행의 값을 반환
  LEAD(product_photos_qty) OVER(
    ORDER BY product_photos_qty
  ) as lead
FROM ecommerce.ecomm_products 
ORDER BY product_photos_qty DESC;

쿼리 결과에서 LAG와 LEAD를 보여주는 이미지

RANGE BETWEEN과 CURRENT ROW

SELECT
  order_id,
  order_timestamp,
  SUM(cost) OVER(
    ORDER BY order_timestamp 
    ROWS BETWEEN 2 PRECEDING 
    AND CURRENT ROW) as rolling_avg
FROM sales_data
ORDER BY order_timestamp

행 기준 경계 옵션:

UNBOUNDED PRECEDING: 이전의 모든 행
UNBOUNDED FOLLOWING: 이후의 모든 행
[INT] ROWS PRECEDING: 지정한 개수만큼 이전 행
[INT] ROWS FOLLOWING: 지정한 개수만큼 이후 행

QUALIFY

SELECT
  product_id,
  product_photos_qty,
  RANK() OVER(
    ORDER BY product_photos_qty DESC
  ) as rank
FROM ecommerce.ecomm_products 
-- QUALIFY로 필터링
QUALIFY rank < 4
ORDER BY product_photos_qty DESC;

QUALIFY로 순위가 3 이하인 값을 찾는 쿼리 결과

집계가 아니므로 HAVING은 사용할 수 없음

Ayo berlatih!

BigQuery 입문