빅쿼리 윈도우 함수

윈도우 함수 활용

BigQuery에서 윈도우 함수를 활용한 고급 데이터 분석 방법을 다루는 종합 가이드입니다.

1. 윈도우 함수 개념과 정의

1.1 윈도우 함수란?

윈도우 함수(Window Function)는 행의 집합에 대해 계산을 수행하되, 그룹화 없이 각 행의 결과를 반환하는 함수입니다.

-- 기본 집계 함수 (그룹화로 인해 행 수 감소)
SELECT 
  department,
  COUNT(*) as total_employees
FROM employees
GROUP BY department;

-- 윈도우 함수 (각 행이 유지됨)
SELECT 
  employee_name,
  department,
  salary,
  COUNT(*) OVER (PARTITION BY department) as dept_total_employees
FROM employees;

1.2 윈도우 함수의 장점

행 보존: 원본 행이 모두 유지됨
복잡한 계산: 순위, 누적합, 이동평균 등 고급 분석 가능
성능 효율성: 서브쿼리 없이 복잡한 분석 가능

2. 기본 문법과 구조

2.1 기본 구문

SELECT 
  column1,
  column2,
  WINDOW_FUNCTION() OVER (
    [PARTITION BY partition_column]
    [ORDER BY order_column]
    [ROWS/RANGE frame_specification]
  ) as window_result
FROM table_name;

2.2 OVER 절 구성 요소

PARTITION BY: 윈도우 분할

-- 부서별로 분할하여 계산
SELECT 
  employee_name,
  department,
  salary,
  AVG(salary) OVER (PARTITION BY department) as dept_avg_salary
FROM employees;

ORDER BY: 정렬 기준

-- 급여 순으로 정렬하여 순위 계산
SELECT 
  employee_name,
  salary,
  ROW_NUMBER() OVER (ORDER BY salary DESC) as salary_rank
FROM employees;

프레임 절: 계산 범위 지정

-- 현재 행을 포함한 이전 2행까지의 평균
SELECT 
  order_date,
  daily_sales,
  AVG(daily_sales) OVER (
    ORDER BY order_date 
    ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
  ) as moving_avg_3days
FROM daily_sales;

3. 순위 함수

3.1 ROW_NUMBER()

각 행에 고유한 순번을 할당합니다.

-- 부서별 급여 순위 (중복값도 서로 다른 순위)
SELECT 
  employee_name,
  department,
  salary,
  ROW_NUMBER() OVER (
    PARTITION BY department 
    ORDER BY salary DESC
  ) as row_num
FROM employees;

-- 결과 예시:
-- John    | Sales | 5000 | 1
-- Mary    | Sales | 4500 | 2
-- Tom     | Sales | 4500 | 3  (같은 급여지만 다른 순위)

3.2 RANK()

같은 값에 대해 동일한 순위를 부여하고, 다음 순위는 건너뜁니다.

SELECT 
  employee_name,
  department,
  salary,
  RANK() OVER (
    PARTITION BY department 
    ORDER BY salary DESC
  ) as rank_num
FROM employees;

-- 결과 예시:
-- John    | Sales | 5000 | 1
-- Mary    | Sales | 4500 | 2
-- Tom     | Sales | 4500 | 2  (동일 순위)
-- Alice   | Sales | 4000 | 4  (순위 3은 건너뜀)

3.3 DENSE_RANK()

같은 값에 대해 동일한 순위를 부여하되, 다음 순위를 건너뛰지 않습니다.

SELECT 
  employee_name,
  department,
  salary,
  DENSE_RANK() OVER (
    PARTITION BY department 
    ORDER BY salary DESC
  ) as dense_rank_num
FROM employees;

-- 결과 예시:
-- John    | Sales | 5000 | 1
-- Mary    | Sales | 4500 | 2
-- Tom     | Sales | 4500 | 2  (동일 순위)
-- Alice   | Sales | 4000 | 3  (순위 연속)

3.4 NTILE()

전체를 N개 그룹으로 나누어 그룹 번호를 할당합니다.

-- 급여를 기준으로 4분위 그룹 생성
SELECT 
  employee_name,
  salary,
  NTILE(4) OVER (ORDER BY salary) as salary_quartile
FROM employees;

-- 실제 활용: 고객 세그멘테이션
SELECT 
  customer_id,
  total_purchase_amount,
  NTILE(3) OVER (ORDER BY total_purchase_amount DESC) as customer_tier,
  CASE NTILE(3) OVER (ORDER BY total_purchase_amount DESC)
    WHEN 1 THEN 'VIP'
    WHEN 2 THEN 'Gold'
    WHEN 3 THEN 'Silver'
  END as tier_name
FROM customer_summary;

4. 집계 윈도우 함수

4.1 누적합 (Cumulative Sum)

-- 일별 매출의 누적합
SELECT 
  order_date,
  daily_sales,
  SUM(daily_sales) OVER (
    ORDER BY order_date 
    ROWS UNBOUNDED PRECEDING
  ) as cumulative_sales
FROM daily_sales
ORDER BY order_date;

-- 부서별 누적 급여
SELECT 
  employee_name,
  department,
  salary,
  SUM(salary) OVER (
    PARTITION BY department 
    ORDER BY hire_date 
    ROWS UNBOUNDED PRECEDING
  ) as cumulative_dept_salary
FROM employees;

4.2 이동평균 (Moving Average)

-- 3일 이동평균
SELECT 
  order_date,
  daily_sales,
  AVG(daily_sales) OVER (
    ORDER BY order_date 
    ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
  ) as moving_avg_3days,
  
-- 7일 이동평균
  AVG(daily_sales) OVER (
    ORDER BY order_date 
    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
  ) as moving_avg_7days
FROM daily_sales
ORDER BY order_date;

4.3 백분율 계산

-- 전체 대비 비율
SELECT 
  department,
  employee_count,
  employee_count / SUM(employee_count) OVER () * 100 as pct_of_total,
  
-- 누적 백분율
  SUM(employee_count) OVER (
    ORDER BY employee_count DESC 
    ROWS UNBOUNDED PRECEDING
  ) / SUM(employee_count) OVER () * 100 as cumulative_pct
FROM department_summary
ORDER BY employee_count DESC;

5. 값 접근 함수

5.1 LAG()와 LEAD()

이전/다음 행의 값에 접근합니다.

-- 전월 대비 증감률 계산
SELECT 
  year_month,
  monthly_sales,
  LAG(monthly_sales) OVER (ORDER BY year_month) as prev_month_sales,
  
-- 증감률 계산
  ROUND(
    (monthly_sales - LAG(monthly_sales) OVER (ORDER BY year_month)) /
    LAG(monthly_sales) OVER (ORDER BY year_month) * 100, 2
  ) as growth_rate_pct,
  
-- 다음 달 매출 (예측값과 비교용)
  LEAD(monthly_sales) OVER (ORDER BY year_month) as next_month_sales
FROM monthly_sales_summary
ORDER BY year_month;

-- 기본값 지정 (첫 번째/마지막 행 처리)
SELECT 
  product_id,
  order_date,
  quantity,
  LAG(quantity, 1, 0) OVER (
    PARTITION BY product_id 
    ORDER BY order_date
  ) as prev_quantity
FROM orders;

5.2 FIRST_VALUE()와 LAST_VALUE()

윈도우 내 첫 번째/마지막 값에 접근합니다.

-- 부서별 최고/최저 급여와 비교
SELECT 
  employee_name,
  department,
  salary,
  
-- 부서 내 최고 급여
  FIRST_VALUE(salary) OVER (
    PARTITION BY department 
    ORDER BY salary DESC
    ROWS UNBOUNDED PRECEDING
  ) as dept_max_salary,
  
-- 부서 내 최저 급여  
  LAST_VALUE(salary) OVER (
    PARTITION BY department 
    ORDER BY salary DESC
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
  ) as dept_min_salary,

-- 최고 급여 대비 비율
  ROUND(salary / FIRST_VALUE(salary) OVER (
    PARTITION BY department 
    ORDER BY salary DESC
    ROWS UNBOUNDED PRECEDING
  ) * 100, 1) as pct_of_max_salary
FROM employees;

5.3 NTH_VALUE()

N번째 값에 접근합니다.

-- 부서별 2번째, 3번째 높은 급여
SELECT 
  employee_name,
  department,
  salary,
  NTH_VALUE(salary, 2) OVER (
    PARTITION BY department 
    ORDER BY salary DESC
    ROWS UNBOUNDED PRECEDING
  ) as second_highest_salary,
  
  NTH_VALUE(salary, 3) OVER (
    PARTITION BY department 
    ORDER BY salary DESC
    ROWS UNBOUNDED PRECEDING
  ) as third_highest_salary
FROM employees;

6. 프레임 절 활용

6.1 ROWS vs RANGE

-- ROWS: 물리적 행 기준
SELECT 
  order_date,
  sales_amount,
  SUM(sales_amount) OVER (
    ORDER BY order_date 
    ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
  ) as sum_last_3_rows
FROM daily_sales;

-- RANGE: 값 기준 (동일 날짜는 함께 포함)
SELECT 
  order_date,
  sales_amount,
  SUM(sales_amount) OVER (
    ORDER BY order_date 
    RANGE BETWEEN INTERVAL 2 DAY PRECEDING AND CURRENT ROW
  ) as sum_last_3_days
FROM daily_sales;

6.2 프레임 경계 지정

-- 다양한 프레임 경계 예시
SELECT 
  order_date,
  daily_sales,
  
-- 처음부터 현재까지
  SUM(daily_sales) OVER (
    ORDER BY order_date 
    ROWS UNBOUNDED PRECEDING
  ) as cumulative_sum,
  
-- 이전 3행부터 다음 1행까지
  AVG(daily_sales) OVER (
    ORDER BY order_date 
    ROWS BETWEEN 3 PRECEDING AND 1 FOLLOWING
  ) as avg_5_rows,
  
-- 현재부터 끝까지
  COUNT(*) OVER (
    ORDER BY order_date 
    ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
  ) as remaining_days
FROM daily_sales;

7. 실제 활용 사례

7.1 매출 분석

-- 종합 매출 분석 대시보드 쿼리
WITH daily_sales_analysis AS (
  SELECT 
    order_date,
    SUM(amount) as daily_sales,
    COUNT(DISTINCT order_id) as daily_orders,
    COUNT(DISTINCT customer_id) as daily_customers
  FROM orders 
  WHERE order_date >= DATE_SUB(CURRENT_DATE(), INTERVAL 90 DAY)
  GROUP BY order_date
)
SELECT 
  order_date,
  daily_sales,
  daily_orders,
  daily_customers,
  
-- 이동평균 (7일, 30일)
  ROUND(AVG(daily_sales) OVER (
    ORDER BY order_date 
    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
  ), 2) as sales_7day_avg,
  
  ROUND(AVG(daily_sales) OVER (
    ORDER BY order_date 
    ROWS BETWEEN 29 PRECEDING AND CURRENT ROW
  ), 2) as sales_30day_avg,
  
-- 전일/전주 대비 증감률
  ROUND((daily_sales - LAG(daily_sales, 1) OVER (ORDER BY order_date)) /
        LAG(daily_sales, 1) OVER (ORDER BY order_date) * 100, 2) as daily_growth_pct,
  
  ROUND((daily_sales - LAG(daily_sales, 7) OVER (ORDER BY order_date)) /
        LAG(daily_sales, 7) OVER (ORDER BY order_date) * 100, 2) as weekly_growth_pct,
  
-- 누적 매출
  SUM(daily_sales) OVER (
    ORDER BY order_date 
    ROWS UNBOUNDED PRECEDING
  ) as cumulative_sales,
  
-- 월별 순위
  RANK() OVER (
    PARTITION BY EXTRACT(YEAR FROM order_date), EXTRACT(MONTH FROM order_date)
    ORDER BY daily_sales DESC
  ) as monthly_sales_rank
FROM daily_sales_analysis
ORDER BY order_date;

7.2 고객 세그멘테이션

-- RFM 분석을 위한 고객 세그멘테이션
WITH customer_rfm AS (
  SELECT 
    customer_id,
    DATE_DIFF(CURRENT_DATE(), MAX(order_date), DAY) as recency,
    COUNT(DISTINCT order_id) as frequency,
    ROUND(AVG(order_amount), 2) as monetary_avg,
    SUM(order_amount) as monetary_total
  FROM orders 
  WHERE order_date >= DATE_SUB(CURRENT_DATE(), INTERVAL 365 DAY)
  GROUP BY customer_id
),
customer_scores AS (
  SELECT 
    customer_id,
    recency,
    frequency, 
    monetary_total,
    
-- RFM 점수 (5분위 기준)
    6 - NTILE(5) OVER (ORDER BY recency) as recency_score,
    NTILE(5) OVER (ORDER BY frequency) as frequency_score,
    NTILE(5) OVER (ORDER BY monetary_total) as monetary_score
  FROM customer_rfm
)
SELECT 
  customer_id,
  recency,
  frequency,
  monetary_total,
  recency_score,
  frequency_score,
  monetary_score,
  
-- 종합 점수
  (recency_score + frequency_score + monetary_score) as total_score,
  
-- 고객 등급
  CASE 
    WHEN (recency_score + frequency_score + monetary_score) >= 12 THEN 'Champion'
    WHEN (recency_score + frequency_score + monetary_score) >= 9 THEN 'Loyal'
    WHEN (recency_score + frequency_score + monetary_score) >= 6 THEN 'Potential'
    ELSE 'At Risk'
  END as customer_segment,
  
-- 각 세그먼트 내 순위
  RANK() OVER (
    PARTITION BY CASE 
      WHEN (recency_score + frequency_score + monetary_score) >= 12 THEN 'Champion'
      WHEN (recency_score + frequency_score + monetary_score) >= 9 THEN 'Loyal'
      WHEN (recency_score + frequency_score + monetary_score) >= 6 THEN 'Potential'
      ELSE 'At Risk'
    END
    ORDER BY monetary_total DESC
  ) as segment_rank
FROM customer_scores
ORDER BY total_score DESC, monetary_total DESC;

7.3 재고 분석

-- 재고 회전율 및 안전재고 분석
WITH inventory_analysis AS (
  SELECT 
    product_id,
    product_name,
    category,
    warehouse_date,
    stock_quantity,
    daily_sales_qty,
    restock_qty
  FROM inventory_daily
  WHERE warehouse_date >= DATE_SUB(CURRENT_DATE(), INTERVAL 180 DAY)
)
SELECT 
  product_id,
  product_name,
  category,
  warehouse_date,
  stock_quantity,
  daily_sales_qty,
  
-- 7일 이동평균 판매량
  ROUND(AVG(daily_sales_qty) OVER (
    PARTITION BY product_id
    ORDER BY warehouse_date 
    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
  ), 2) as avg_daily_sales_7d,
  
-- 재고 소진 예상일 (현재 재고 / 평균 판매량)
  ROUND(stock_quantity / NULLIF(AVG(daily_sales_qty) OVER (
    PARTITION BY product_id
    ORDER BY warehouse_date 
    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
  ), 0), 1) as days_until_stockout,
  
-- 최대/최소 재고량 (30일 기준)
  MAX(stock_quantity) OVER (
    PARTITION BY product_id
    ORDER BY warehouse_date 
    ROWS BETWEEN 29 PRECEDING AND CURRENT ROW
  ) as max_stock_30d,
  
  MIN(stock_quantity) OVER (
    PARTITION BY product_id
    ORDER BY warehouse_date 
    ROWS BETWEEN 29 PRECEDING AND CURRENT ROW
  ) as min_stock_30d,
  
-- 재고 회전율 (월별)
  ROUND(SUM(daily_sales_qty) OVER (
    PARTITION BY product_id
    ORDER BY warehouse_date 
    ROWS BETWEEN 29 PRECEDING AND CURRENT ROW
  ) / NULLIF(AVG(stock_quantity) OVER (
    PARTITION BY product_id
    ORDER BY warehouse_date 
    ROWS BETWEEN 29 PRECEDING AND CURRENT ROW
  ), 0), 2) as inventory_turnover_30d
FROM inventory_analysis
ORDER BY product_id, warehouse_date;

8. 성능 최적화

8.1 파티션 키 활용

-- 파티션 테이블에서 윈도우 함수 최적화
SELECT 
  order_date,
  customer_id,
  order_amount,
  ROW_NUMBER() OVER (
    PARTITION BY DATE_TRUNC(order_date, MONTH), customer_id
    ORDER BY order_amount DESC
  ) as monthly_customer_rank
FROM orders 
WHERE order_date >= DATE_SUB(CURRENT_DATE(), INTERVAL 12 MONTH)
  AND DATE_TRUNC(order_date, MONTH) = '2024-01-01'; -- 파티션 프루닝

8.2 윈도우 함수 재사용

-- 동일한 OVER 절을 여러 번 사용할 때
SELECT 
  employee_name,
  department,
  salary,
  ROW_NUMBER() OVER dept_salary_desc as rank_num,
  RANK() OVER dept_salary_desc as rank_with_ties,
  PERCENT_RANK() OVER dept_salary_desc as percentile_rank
FROM employees
WINDOW dept_salary_desc AS (
  PARTITION BY department 
  ORDER BY salary DESC
);

8.3 적절한 프레임 절 사용

-- 불필요하게 큰 프레임 피하기
-- ❌ 비효율적
SELECT 
  order_date,
  sales,
  AVG(sales) OVER (
    ORDER BY order_date 
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
  ) as overall_avg  -- 전체 평균은 단순 AVG() 사용
FROM daily_sales;

-- ✅ 효율적
SELECT 
  order_date,
  sales,
  AVG(sales) OVER () as overall_avg,  -- 전체 평균
  AVG(sales) OVER (
    ORDER BY order_date 
    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW  -- 필요한 범위만
  ) as week_avg
FROM daily_sales;

9. 모범 사례와 주의점

9.1 모범 사례

1. 명확한 윈도우 정의

-- ✅ 좋은 예: 윈도우를 명확히 정의
SELECT 
  customer_id,
  order_date,
  order_amount,
  SUM(order_amount) OVER (
    PARTITION BY customer_id 
    ORDER BY order_date 
    ROWS UNBOUNDED PRECEDING
  ) as cumulative_spent
FROM orders;

2. WINDOW 절 활용

-- ✅ 좋은 예: 윈도우 재사용
SELECT 
  product_id,
  sales_date,
  daily_sales,
  AVG(daily_sales) OVER w as moving_avg,
  MIN(daily_sales) OVER w as moving_min,
  MAX(daily_sales) OVER w as moving_max
FROM product_sales
WINDOW w AS (
  PARTITION BY product_id 
  ORDER BY sales_date 
  ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
);

9.2 주의점

1. LAST_VALUE() 함수 사용 시 프레임 주의

-- ❌ 잘못된 예: 기본 프레임으로 인해 예상과 다른 결과
SELECT 
  order_date,
  sales,
  LAST_VALUE(sales) OVER (ORDER BY order_date) as last_sales
FROM daily_sales;

-- ✅ 올바른 예: 전체 프레임 명시
SELECT 
  order_date,
  sales,
  LAST_VALUE(sales) OVER (
    ORDER BY order_date 
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
  ) as last_sales
FROM daily_sales;

2. NULL 값 처리

-- NULL 값이 있는 경우 적절한 처리
SELECT 
  order_date,
  sales_amount,
  LAG(sales_amount, 1) OVER (ORDER BY order_date) as prev_sales,
  
-- NULL 안전 계산
  ROUND(
    SAFE_DIVIDE(
      sales_amount - LAG(sales_amount, 1) OVER (ORDER BY order_date),
      NULLIF(LAG(sales_amount, 1) OVER (ORDER BY order_date), 0)
    ) * 100, 2
  ) as growth_rate_pct
FROM daily_sales;

3. 대용량 데이터 처리 시 고려사항

-- 파티션 필터링으로 처리량 제한
SELECT 
  order_id,
  customer_id,
  order_date,
  order_amount,
  ROW_NUMBER() OVER (
    PARTITION BY customer_id 
    ORDER BY order_date DESC
  ) as recent_order_rank
FROM orders 
WHERE order_date >= DATE_SUB(CURRENT_DATE(), INTERVAL 90 DAY)  -- 처리량 제한
  AND customer_id IN (SELECT customer_id FROM target_customers); -- 대상 제한

9.3 디버깅 팁

-- 윈도우 함수 결과 확인을 위한 단계별 접근
WITH base_data AS (
  SELECT 
    order_date,
    customer_id,
    order_amount
  FROM orders 
  WHERE order_date >= '2024-01-01'
),
with_row_numbers AS (
  SELECT 
    *,
    ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date) as rn
  FROM base_data
),
with_calculations AS (
  SELECT 
    *,
    LAG(order_amount) OVER (PARTITION BY customer_id ORDER BY order_date) as prev_amount,
    SUM(order_amount) OVER (
      PARTITION BY customer_id 
      ORDER BY order_date 
      ROWS UNBOUNDED PRECEDING
    ) as cumulative_amount
  FROM with_row_numbers
)
SELECT * FROM with_calculations
ORDER BY customer_id, order_date;

BigQuery의 윈도우 함수는 복잡한 분석을 간단하고 효율적으로 수행할 수 있는 강력한 도구입니다. 적절한 파티셔닝과 프레임 설정을 통해 성능을 최적화하고, 비즈니스 요구사항에 맞는 인사이트를 도출할 수 있습니다.