Window Function (윈도우 함수) - MySQL

Window Function (윈도우 함수) - 집계 결과를 여러 행에 걸쳐 출력

 

- 왜 사용하는 가?

• GROUP BY 와 달리 모든 행을 유지하면서 행 단위로 누적합, 순위, 이전값 등 계산 가능
• 즉, 모든 행과 집계정보를 함께 보고 싶을 때 사용

 

- Window Function 의 종류

 

- 구조

<윈도우 함수>() OVER ( # 윈도우 함수임을 나타내는 키워드
  [PARTION BY <컬럼>] # 데이터를 그룹으로 나눔
  [ORDER BY <컬럼>] # 순서 기준을 지정
)

 

- 예시

 

1. ROW_NUMBER( ) : 순위를 1부터 부여 (중복 시에도 무조건 증가) (동점이 있어도 1, 2, 3, 4)

-- 각 유저별 최근 1건 주문 추출
SELECT
	user_id,
    order_date,
    amount,
    ROW_NUMBER() OVER (PARTITION BY user_id, ORDER BY order_date) AS row_num
FROM orders

 

 

2. RANK( ) : 동점 순위 부여하고 다음 순위는 건너뜀 (1, 1, 3, 4)

-- 유저별로 주문 금액의 순위를 계산 (단, 다음 순위 건너뜀)
SELECT
	user_id,
    amount,
    RANK() OVER (PARTITION BY user_id, ORDER BY amount DESC) AS rank_amount
FROM orders

 

 

3. DENSE_RANK( ) : 동점 순위 부여하되 다음 순위는 건너뛰지 않음 (1, 1, 2, 3)

-- 유저별로 주문 금액의 순위를 계산 (단, 다음 순위 건너뛰지 않음)
SELECT
	user_id,
    amount,
    DENSE_RANK() OVER (PARTITION BY

 

 

4. SUM( ) / AVG( ) : 누적합 계산 / 누적 평균 계산

-- 유저별 누적 구매액 계산
-- 유저별 누적 평균 구매액 계산
SELECT
	user_id,
    order_date,
    SUM(amount) OVER (PARTITION BY user_id ORDER BY order_date) AS cumulative_sum,
    AVG(amount) OVER (PARTITION BY user_id ORDER BY order_date) AS cumulative_avg
FROM orders

 

 

5. LAG( ) / LEAD( ) : 이전값 / 다음값 참조

-- 유저와 이전유저, 다음유저의 결제 금액을 출력
SELECT
	user_id,
    order_date,
    amount,
    LAG(amount) OVER (PARTITION BY user_id ORDER BY order_date) AS prev_amount,
    LEAD(amount) OVER (PARTITION BY user_id ORDER BY order_date) AS next_amount
FROM orders

 

예시 테이블 :

user_id	order_date	amount
A	2024-01-01	100
A	2024-01-03	300
A	2024-01-05	200

 

출력 결과 : 

user_id	order_date	amont	prev_amount	next_amount
A	2024-01-01	100	NULL	300
A	2024-01-03	300	100	200
A	2024-01-05	200	300	NULL

 

 

6. NTILE(n) : n등분한 후 각 행이 소속해 있는 그룹 부여

-- 상위 50%, 하위 50% 구분

SELECT
	user_id,
    amount,
    NTILE(2) OVER (ORDER BY amount DESC) AS quartile # 전체를 2등분
FROM orders

 

 

7. FIRST_VALUE( ) : 그룹 내 첫번째 값

-- 유저별 첫 주문금액
SELECT
	user_id,
    order_date,
    amount,
    FIRST_VALUE(amount) OVER (PARTITION BY user_id ORDER BY order_date) AS first_order
FROM orders

 

 

8. LAST_VALUE( ) : 그룹 내 마지막번째 값

-- 유저별 마지막 주문 금액
SELECT
	user_id,
    order_date,
    amount,
    LAST_VALUE(amount) OVER 
    	(PARTITION BY user_id 
        ORDER BY order_date
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS last_amount
FROM orders

 

ROWS BETWEEN으로 범위를 지정해줘야한다고 한다. 그렇지 않으면 현재 행 기준에서 가장 마지막 값이 출력되기 때문라는데... 아직은 잘 모르겠다. ROWS, RANGE는 다음 글에서 제대로 공부해야 겠다.

 

 

9. COUNT( ) : 누적 갯수 계산

-- 유저별 누적 주문 횟수
SELECT
	user_id,
    order_date,
    COUNT(*) OVER (PARTITION BY user_id ORDER BY order_date_ AS order_count
FROM orders