MySQL 윈도우 함수(Window Functions) 활용 실전 예제

데이터 분석의 요구사항이 복잡해짐에 따라, 전통적인 SQL 집계 방식만으로는 한계에 부딪히는 경우가 많습니다. MySQL 8.0부터 본격적으로 지원되기 시작한 윈도우 함수(Window Functions)는 이러한 분석 쿼리의 패러다임을 바꾸는 강력한 기능입니다. 기존의 GROUP BY를 사용한 집계 함수가 여러 행을 하나의 요약된 행으로 축소하는 반면, 윈도우 함수는 결과 집합의 개별 행은 그대로 유지하면서, 각 행이 속한 특정 그룹(파티션 또는 윈도우) 내의 다른 행들과의 관계를 바탕으로 순위, 누적 합계, 이동 평균, 행 간 값 비교 등 다양한 계산을 수행하여 새로운 컬럼을 추가할 수 있게 해줍니다. 이를 통해 과거에는 복잡한 서브쿼리나 비효율적인 셀프 조인을 사용해야만 가능했던 분석 로직을 훨씬 간결하고 직관적이며 효율적인 방식으로 구현할 수 있게 되었습니다.

본 글에서는 윈도우 함수의 기본 구문인 OVER() 절의 구성 요소(PARTITION BY, ORDER BY, 프레임 절)를 살펴보고, 순위 함수, 집계 함수, 값 함수 등 주요 윈도우 함수의 종류와 구체적인 활용 예제를 통해 그 강력한 기능을 탐색하며, 사용 시의 장점과 성능 관련 고려사항까지 폭넓게 다루어 보고자 합니다.

---

윈도우 함수의 기본 구문

윈도우 함수는 OVER() 절과 함께 사용됩니다.

<윈도우 함수>() OVER (
    [PARTITION BY <파티션 컬럼>]
    [ORDER BY <정렬 컬럼>]
    [<윈도우 프레임 절>]
)

• <윈도우 함수>(): 사용할 윈도우 함수 (예: RANK(), SUM(), LAG()).
• OVER(): 윈도우 함수임을 명시하는 필수 키워드.
• PARTITION BY <파티션 컬럼> (선택 사항): 전체 행 집합을 특정 컬럼 기준으로 여러 개의 파티션(그룹)으로 나눕니다. 윈도우 함수는 각 파티션 내에서 독립적으로 계산됩니다. 생략하면 전체 행 집합이 하나의 파티션이 됩니다.
• ORDER BY <정렬 컬럼> (선택 사항): 각 파티션 내에서 행의 순서를 정의합니다. 순위 함수나 누적 계산, 프레임 정의 등에 필수적입니다.
• <윈도우 프레임 절> (선택 사항): 현재 행을 기준으로 함수 계산에 포함될 행의 범위(프레임)를 더 세밀하게 정의합니다. (예: ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW - 현재 행까지의 누적)

윈도우 함수의 종류 및 예제

1. 순위 함수 (Ranking Functions):

• ROW_NUMBER(): 파티션 내에서 순서대로 고유한 순번을 부여합니다. (동점 없음)
• RANK(): 순위를 부여하며, 동점인 경우 같은 순위를 부여하고 다음 순위는 건너<0xEB><0x9B><0x81>니다. (예: 1, 2, 2, 4)
• DENSE_RANK(): RANK()와 유사하지만, 동점자 다음 순위를 건너<0xEB><0x9B><0x81>지 않습니다. (예: 1, 2, 2, 3)
• NTILE(N): 파티션을 N개의 그룹으로 나누고 각 행이 속한 그룹 번호를 반환합니다.

예시: 부서별 급여 순위 매기기

SELECT
    emp_no,
    dept_no,
    salary,
    RANK() OVER (PARTITION BY dept_no ORDER BY salary DESC) AS dept_salary_rank,
    DENSE_RANK() OVER (PARTITION BY dept_no ORDER BY salary DESC) AS dept_salary_dense_rank,
    ROW_NUMBER() OVER (PARTITION BY dept_no ORDER BY salary DESC) AS dept_salary_row_num
FROM dept_emp de
JOIN salaries s ON de.emp_no = s.emp_no AND s.to_date = '9999-01-01'
WHERE de.to_date = '9999-01-01';

 

2. 집계 함수 (Aggregate Functions) as Window Functions

SUM(), AVG(), COUNT(), MAX(), MIN() 등 일반 집계 함수를 OVER() 절과 함께 사용하여 파티션 내의 집계 값을 각 행에 표시할 수 있습니다.

 

예시: 각 직원의 급여와 해당 부서의 평균 급여 함께 보기

SELECT
    emp_no,
    dept_no,
    salary,
    AVG(salary) OVER (PARTITION BY dept_no) AS avg_dept_salary
FROM dept_emp de
JOIN salaries s ON de.emp_no = s.emp_no AND s.to_date = '9999-01-01'
WHERE de.to_date = '9999-01-01';

 

예시: 날짜별 매출과 누적 매출 계산

SELECT
    order_date,
    daily_sales,
    SUM(daily_sales) OVER (ORDER BY order_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cumulative_sales
FROM daily_sales_summary;
-- ROWS BETWEEN ... : 윈도우 프레임 절, '처음부터 현재 행까지'를 의미

 

3. 값 함수 (Value Functions)

• LAG(컬럼, N, 기본값): 파티션 내에서 현재 행보다 N번째 앞에 있는 행의 컬럼 값을 가져옵니다. (기본값 N=1)
• LEAD(컬럼, N, 기본값): 파티션 내에서 현재 행보다 N번째 뒤에 있는 행의 컬럼 값을 가져옵니다. (기본값 N=1)
• FIRST_VALUE(컬럼): 파티션 내에서 첫 번째 행의 컬럼 값을 가져옵니다.
• LAST_VALUE(컬럼): 파티션 내에서 마지막 행의 컬럼 값을 가져옵니다. (프레임 정의 주의 필요)

예시: 직원의 현재 급여와 이전 급여 비교

SELECT
    emp_no,
    from_date,
    salary,
    LAG(salary, 1, 0) OVER (PARTITION BY emp_no ORDER BY from_date) AS previous_salary
FROM salaries
ORDER BY emp_no, from_date;

 

윈도우 함수의 장점 및 고려사항

• 장점
  • 복잡한 분석 쿼리를 간결하게 작성할 수 있습니다.
  • 서브쿼리나 셀프 조인을 대체하여 가독성을 높이고 성능을 개선할 수 있는 경우가 많습니다.
  • 행 단위 계산과 집계 계산을 동시에 수행할 수 있습니다.
• 고려사항
  • MySQL 버전: MySQL 8.0 이상에서 완전하게 지원됩니다.
  • 성능: 윈도우 함수는 내부적으로 정렬이나 파티셔닝 작업을 수행하므로, 대용량 데이터 처리 시 리소스를 많이 소모할 수 있습니다. PARTITION BY와 ORDER BY 절에 사용되는 컬럼에 인덱스가 있으면 성능 향상에 도움이 될 수 있습니다.
  • 프레임 정의: LAST_VALUE 함수나 누적 계산 시 원하는 결과를 얻으려면 윈도우 프레임 절(ROWS 또는 RANGE)을 명확하게 정의해야 할 수 있습니다. 기본 프레임은 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW이지만, ORDER BY 유무에 따라 달라질 수 있으므로 주의가 필요합니다.
  • 메모리 사용: 파티션 크기가 너무 크면 메모리 사용량이 증가할 수 있습니다.

---

윈도우 함수는 MySQL 8.0 이후 SQL 기반 데이터 분석의 가능성을 크게 확장시킨 혁신적인 기능입니다. 각 행의 개별성을 유지하면서도 관련 그룹(파티션) 내에서의 순위( RANK, DENSE_RANK, ROW_NUMBER ), 누적 값 계산( SUM, AVG 등 + OVER() ), 그리고 행 간의 상대적 위치에 따른 값 참조( LAG, LEAD, FIRST_VALUE, LAST_VALUE ) 등을 가능하게 함으로써, 복잡했던 분석 쿼리를 놀랍도록 간결하고 가독성 높게 개선할 수 있습니다. 과거 서브쿼리나 셀프 조인에 의존해야 했던 많은 분석 시나리오에서 윈도우 함수는 더 나은 성능과 유지보수성을 제공할 잠재력을 지닙니다.

하지만 이 강력한 기능은 내부적으로 데이터를 파티셔닝하고 정렬하는 작업을 수반하므로, 특히 대용량 데이터에 적용할 때는 성능에 대한 고려가 필수적입니다. PARTITION BY 및 ORDER BY 절에 사용되는 컬럼에 적절한 인덱스를 생성하는 것은 성능 최적화의 기본이며, 원하는 결과를 정확히 얻기 위해서는 윈도우 프레임( ROWS 또는 RANGE )의 동작 방식을 명확히 이해하고 명시적으로 지정해야 할 때도 있습니다. 따라서 윈도우 함수의 편리함과 강력함을 적극 활용하되, 항상 EXPLAIN을 통해 실행 계획을 점검하고 리소스 사용량(메모리, CPU)을 모니터링하며 최적의 성능을 유지하기 위한 노력을 병행하는 것이 중요합니다. 윈도우 함수는 이제 현대적인 데이터 분석 환경에서 SQL 사용자라면 반드시 숙지하고 활용해야 할 핵심 도구라고 할 수 있습니다.