(SQL) 주요 Window 함수인 PARTITION BY, OVER 개념 구문 및 예시

지난 글에서 설명드린 여러 가지 Window 함수 중 PARTITION BY, OVER에 대해 자세히 알아보겠습니다.

 

🔻(참고)Window 함수 종류🔻

(SQL) 윈도우 함수 (Window Fuction) 개념 및 종류

 

1. PARTITION BY를 사용하는 경우

• PARTITION BY는 OVER 절의 하위 절입니다.
• OVER 구문은 윈도우 함수를 적용할 함수의 바로 뒤, PARTITION BY의 앞에 나옵니다.
• 윈도우 함수는 데이터베이스 전체의 변화 양상을 추적하거나 모든 행을 그대로 유지하여 세밀하게 데이터를 보관할 때 유용하게 활용됩니다. 
• PARTITION BY 와 OVER는 주로 집계 함수에서 사용되는 구문입니다.

출처 ❘ Udacity

• 위의 예시는 Amazon이 누적 주문 금액을 월별로 계산하는 함수입니다.
• 윈도우 함수에서 PARTITION BY는 일종의 GROUP BY 와 유사하게 작용하여 월별 누적 매출을 계산해냅니다.
• 따라서 PARTITION BY 뒤에는 그룹으로 묶을 기준이 명시됩니다. 여기서는 월 단위입니다.
• 위 빨간색 네모 구문이 월별 누적 매출을 계산할 수 있게 해줍니다. 

2. PARTITION BY 구문

출처 ❘ Udacity

• 1단계) 윈도우 함수를 이용하여 시간에 따른 변화의 양상을 추적하고 측정할 때는 가장 먼저 집계 함수(Aggreegate_function)을 입력해야 합니다. 흔히 SUM, AVERAGE, COUNT 등의 집계 함수로 주로 사용되며 PARTITION BY 구문의 가장 앞부분에 나옵니다.
• 2단계) OVER은 항상 PARTITION BY 절의 앞에 위치합니다.
• 3단계) PATRITION BY 가 입력됩니다.
• 4단계) (선택적 조건) 윈도우 함수는 흔히 날짜 정보를 파악하거나 시간에 따른 변화를 추적하는데 사용되므로 ORDER BY로 시간에 따라 결과를 정렬하는 것이 적합한 경우가 많습니다.
• 5단계) (선택적 조건) 새로운 열에 이름을 붙일 때 AS 를 사용합니다.

 

3. 예시

문제 1) 날짜 잘림 없이 주문 시간 동안 누계 'standard_amt_usd'('orders' 테이블에 있음)를 생성합니다. 최종 테이블에는 두 개의 열이 있어야 합니다. 하나는 새 행마다 추가되는 금액이고 다른 하나는 누계입니다.

SELECT standard_amt_usd,
	SUM(standard_amt_usd) OVER (PARTITION BY occurred_at) AS running_total
FROM orders

 

 

 

문제2) 파티션을 포함하도록 이전 문제의 쿼리를 수정합니다. 여전히 주문 시간 동안 standard_amt_usd의 누계(orders 테이블에서)를 생성하지만, 이번에는 날짜가 연도별로 occurred_at을 자르고 동일 연도가 잘린 occurred_at 변수로 분할합니다. 최종 테이블에는 다음 세 개의 열이 있어야 합니다.

• 각 행에 대해 금액이 추가되는 열
• 잘린 날짜에 대한 열
• 연도 내 누적 합계가 있는 마지막 열

SELECT standard_amt_usd,
DATE_TRUNC('year', occurred_at) as year,
SUM(standard_amt_usd) OVER (PARTITION BY DATE_TRUNC('year', occurred_at) ORDER BY occurred_at) AS running_total
FROM orders