SQL - Window Function(RANK, SUM OVER)

 

Window Function;

데이터베이스 내에서 특정 행(row)의 값이 전체 데이터셋이나 특정 부분집합(partition)의 다른 행과 비교되거나 연관 지어질 때 유용하게 사용되는 함수

 

여러 행을 참조하면서도, 각 행의 결과를 반환하기 때문에 일반적인 집계 함수(Aggregate Function)와는 다름

 

 

 

 

특징

행별 결과 반환: 데이터의 각 행에 대해 계산 결과 반환

PARTITION BY: 데이터를 그룹화하여 그룹 내에서만 연산을 수행

ORDER BY: 그룹 내에서 순서를 지정하여 정렬한 뒤 연산 수행

OVER 절: Window Function의 범위와 동작 정의

 

 

주요 Window Function

• RANK(), DENSE_RANK(), ROW_NUMBER()
  • 데이터를 정렬한 후 각 행의 순위를 반환
  • ROW_NUMBER()은 순서를 단순히 증가
  • RANK()와 DENSE_RANK()는 중복된 값이 있을 때 순위 계산 방식이 다름
• SUM(), AVG(), COUNT(), MIN(), MAX()
  • 집계 함수와 비슷하지만, 각 행별로 결과를 계산
• LEAD(), LAG()
  • 특정 열의 이전(LAG) 또는 다음(LEAD) 값을 참조
• NTILE(n)
  • 데이터를 n개의 그룹으로 나누고, 각 행에 그룹 번호를 반환
• FIRST_VALUE(), LAST_VALUE()
  • 그룹 내에서 첫 번째(FIRST_VALUE) 또는 마지막(LAST_VALUE) 값을 반환

 

 

SELECT column1, 
       column2, 
       SUM(column3) OVER (PARTITION BY column1 ORDER BY column2) ASrunning_total 
FROM table_name

 

OVER절

PARTITION BY column1: column1을 기준으로 데이터를 그룹화

ORDER BY column2: coumn2를 기준으로 그룹 내에서 정렬

 

SUM(column3)

각 행에 대해 column3 값의 누적 합계를 계산

 

 

 

 

사용예)

 

1. 순위 매기기

각 department_id 내에서 employee_id의 salary순위 반환

SELECT employee_id, 
       department_id, 
       salary, 
       RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) rank 
FROM employees

 

2. 누적 합계

 

각 customer_id의 order_amount를 order_data 순서로 누적 합산한 결과 반환

SELECT order_id, 
       customer_id, 
       order_date, 
       SUM(order_amount) OVER (PARTITION BY customer_id ORDER BY order_date) cumulative_total 
FROM orders

 

 

3. 이전 값과 비교

 

각 product_id의 sales_data에서 sales를 참조

SELECT product_id, 
       sale_date, 
       sales, 
       LAG(sales) OVER (PARTITION BY product_id ORDER BY sale_date) previous_sales 
FROM sales_data

 

 

 

 

집계 함수와 차이점

• 집계 함수: 결과를 하나의 값으로 반환
  • 예: SUM(column)은 그룹당 하나의 결과만 반환.
• Window Function: 각 행별로 결과를 반환
  • 예: SUM(column) OVER ()은 각 행마다 결과를 반환.

 

 

*장점

데이터를 분석하거나 시간 흐름에 따라 변화하는 값 추적용이

복잡한 통계 계산 단순화

다른 SQL 쿼리로는 처리하기 어려운 데이터 간 관계 파악