PARTITION BY, ORDER BY, ROWS/RANGE
MySQL 윈도우 함수에서 PARTITION BY, ORDER BY, ROWS/RANGE는 데이터를 나누고 정렬해서 분석하는 데 꼭 필요한 도구입니다. 복잡한 통계나 누적 합계를 구할 때 아주 유용하죠. 이 포스팅에서는 각각이 어떤 역할을 하는지, 어떻게 쓰는지 알아봅시다!
1. PARTITION BY
-
PARTITION BY는 데이터를 특정 컬럼 기준으로 그룹처럼 나누는 기능이에요. 이렇게 나눠진 그룹 안에서 윈도우 함수가 따로따로 계산됩니다.- 예를 들어:
PARTITION BY column1, column2라고 하면,column1과column2의 값이 같은 행들끼리 묶여서 같은 그룹이 됩니다.
- 예를 들어:
FUNCTION() OVER (PARTITION BY column1, column2, ...)2. ORDER BY
-
ORDER BY는 그룹 안의 데이터를 정렬하는 기능이에요. 누적 합이나 순위 매기기를 할 때 어떤 순서로 계산할지를 정하는 데 아주 중요해요.- 예를 들어: 날짜순이나 금액순 등 원하는 기준으로 정렬해서 그 순서대로 계산이 됩니다.
FUNCTION() OVER (PARTITION BY column1, column2 ... ORDER BY column3 [ASC|DESC])3. ROWS / RANGE
-
ROWS나RANGE는ORDER BY와 같이 쓰이면서, 몇 개의 행을 계산에 포함할지를 정하는 기능이에요.ROWS는 실제 몇 행 앞이나 뒤까지를 포함할지를 말하고,RANGE는 값의 범위를 기준으로 정해요.
-
예시:
ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING: 현재 행을 기준으로 앞뒤 1개씩 포함해서 계산합니다.RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW: 처음부터 현재 행까지 포함합니다.
-- ROWS 예시
FUNCTION() OVER (
PARTITION BY column1, column2
ORDER BY column3
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
)
-- RANGE 예시
FUNCTION() OVER (
PARTITION BY column1, column2
ORDER BY column3
RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
)ROW/RANGE 용어 간단 정리
- UNBOUNDED PRECEDING: 파티션의 맨 처음 행부터 시작
- UNBOUNDED FOLLOWING: 파티션의 마지막 행까지 포함
- CURRENT ROW: 지금 계산 중인 행
- n PRECEDING / FOLLOWING: 지금 행 기준으로 n행 앞/뒤까지
ROWS와 RANGE의 차이, 감 잡기 쉽게 설명하면?
-
ROWS는 진짜 행(row) 단위로 계산해요. 정렬된 순서대로 봤을 때 정확히 몇 번째인지를 기준으로 범위를 지정하는 거죠. 그래서 중복된 값이 있어도 상관없이 행 위치만 따져요.- 예: "지금 이 행 포함해서 바로 앞의 2개까지!"
-
RANGE는 정렬 기준 값(ORDER BY) 이 같은 행들을 전부 묶어서 계산해요. 즉, 값이 같으면 여러 행이 한꺼번에 포함될 수 있어요.- 예: "지금 이 행의 날짜랑 같은 날짜 가진 애들까지 전부 포함!"
👉 그래서 RANGE는 중복값이 있을 때 그룹처럼 작동하는 반면, ROWS는 순번대로 딱딱 끊어서 계산한다는 점이 핵심 차이예요.
예제: 회원별, 대여 날짜별 누적 대여 횟수 계산
book_rentals 테이블에서 각 회원이 언제 몇 번이나 책을 빌렸는지 누적해서 보여주는 쿼리예요.
사용 스키마
| 컬럼명 | 타입 | 설명 |
|---|---|---|
member_id | INT | 도서관 회원 ID |
rental_date | DATETIME | 책을 빌린 날짜 |
book_id | INT | 대여한 책 ID |
쿼리 예시
SELECT
member_id,
rental_date,
COUNT(*) OVER (
PARTITION BY member_id
ORDER BY rental_date
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS cumulative_rentals
FROM book_rentals;결과 예시:
| member_id | rental_date | cumulative_rentals |
|---|---|---|
| 101 | 2024-01-03 10:15:00 | 1 |
| 101 | 2024-01-10 14:20:00 | 2 |
| 101 | 2024-01-22 09:45:00 | 3 |
| 102 | 2024-01-05 13:00:00 | 1 |
| 102 | 2024-02-02 11:30:00 | 2 |
설명
- 각 행마다 따로 윈도우를 만들어서
COUNT(*)을 계산하고 있어요. PARTITION BY member_id: 회원별로 따로 누적합을 계산합니다.ORDER BY rental_date: 대여 날짜 순서대로 정렬해서 계산됩니다.ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW: 맨 처음부터 지금 행까지 포함해서 계산해요.- 결과적으로
cumulative_rentals는 해당 회원이 지금까지 몇 번 책을 빌렸는지를 누적해서 보여줍니다.
FUNCTION()
참고로 COUNT(*) 자리에는 다양한 함수가 적용될 수 있습니다: SUM(expression), AVG(expression), MIN(expression), MAX(expression), ROW_NUMBER(), RANK(), DENSE_RANK(), LEAD(expression, offset, default), LAG(expression, offset, default), FIRST_VALUE(expression), LAST_VALUE(expression) 등등
* 모르는 함수는 나중에 더 배울테니 걱정 노노!
RANGE 활용 예제 모음
1. 누적 금액 합계 구하기
orders 테이블에서 고객별로 주문일 기준 누적 주문 금액을 계산하는 예제입니다. 동일한 날짜에 여러 주문이 있는 경우에도 합쳐서 계산됩니다.
SELECT
customer_id,
order_date,
amount,
SUM(amount) OVER (
PARTITION BY customer_id
ORDER BY order_date
RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS cumulative_amount
FROM orders;사용 스키마 (예시)
| 컬럼명 | 타입 | 설명 |
|---|---|---|
customer_id | INT | 고객 ID |
order_date | DATE | 주문 날짜 |
amount | DECIMAL | 주문 금액 |
🔍
RANGE를 사용했기 때문에, 같은order_date를 가진 행들은 한꺼번에 집계됩니다.
2. 동일한 점수 포함 순위 구하기 (RANGE + RANK)
시험 결과 테이블에서 점수 기준으로 등수를 매기되, 같은 점수는 같은 등수로 처리하는 RANK() 함수 예제입니다.
SELECT
student_id,
test_score,
RANK() OVER (
ORDER BY test_score
RANGE BETWEEN CURRENT ROW AND CURRENT ROW
) AS score_rank
FROM scores;사용 스키마 (예시)
| 컬럼명 | 타입 | 설명 |
|---|---|---|
student_id | INT | 학생 ID |
test_score | INT | 시험 점수 |
📊
RANGE CURRENT ROW는 현재 점수와 같은 값을 가진 모든 행을 한꺼번에 묶어 같은 등수를 줍니다.
이렇게 이번에는 PARTITION BY, ORDER BY, ROWS/RANGE에 대해서 알아보았습니다! 다음번에는 LEAD(), LAG(), FIRST_VALUE(), LAST_VALUE() 문법을 배워봅시다! 그럼 🙌
