MySQL의 윈도우 함수(Window Function)는 행(row) 그룹을 기준으로 하여 집계된 결과를 반환하는 함수로, 일반적인 집계 함수와 달리 결과를 그룹별로 나누지 않고 각 행에 대해 여러 값을 계산할 수 있습니다. 즉, 윈도우 함수는 특정 조건에 따라 슬라이딩 윈도우(이동 창)를 만들어 행 간의 상호 관계를 계산하는데 유용합니다.
0. 예시 코드 테이블 생성 및 데이터 삽입
예시 코드에 사용될 테이블 생성 및 데이터를 삽입 하는 sql문입니다.
아래 sql문을 복사하여 테이블 생성 및 데이터를 삽입해주세요.
CREATE TABLE employees (
id INT PRIMARY KEY,
name VARCHAR(50),
department VARCHAR(50),
salary INT
);
INSERT INTO employees (id, name, department, salary) VALUES
(1, 'John', 'HR', 5000),
(2, 'Jane', 'HR', 6000),
(3, 'Alice', 'IT', 7000),
(4, 'Bob', 'IT', 8000),
(5, 'Charlie', 'Finance', 9000),
(6, 'David', 'Finance', 10000),
(7, 'Eve', 'IT', 7500),
(8, 'Frank', 'HR', 5500);
1. 윈도우 함수의 구조
윈도우 함수는 OVER() 절과 함께 사용되며, 기본 구조는 다음과 같습니다.
SELECT column_name,
window_fn() OVER (PARTITION BY 그룹 컬럼 ORDER BY 정렬 기준 컬럼 ROWS BETWEEN 범위)
FROM table_name;OVER() 절의 구조
PARTITION BY
- 데이터를 특정 기준으로 그룹화합니다. 집계 함수의
GROUP BY와 유사하게 작동하지만, 그룹별로 결과를 나누지 않고 각 행에 대해 결과를 반환합니다. - 생략할 경우, 전체 데이터셋을 하나의 그룹으로 간주합니다.
ORDER BY
- 윈도우 내에서 행을 정렬하는 기준을 설정합니다. 순위 함수나 LAG()/LEAD() 함수 등에서 주로 사용됩니다.
ORDER BY를 사용하면 계산 순서가 중요해지므로 누적 합계나 순위 계산 등에 유용합니다.
ROWS BETWEEN
- 윈도우 내에서 특정 범위를 설정할 수 있습니다. 즉, 현재 행을 기준으로 앞뒤 몇 행을 포함할지를 지정합니다.
- 예를 들어, "현재 행부터 이전 2행까지의 합계"를 구하는 등의 분석이 가능합니다.
- 옵션
UNBOUNDED PRECEDING: 처음부터 현재 행까지의 모든 행을 포함CURRENT ROW: 현재 행만 포함UNBOUNDED FOLLOWING: 현재 행부터 마지막 행까지 포함
예를 들어, 3개의 행을 포함하는 윈도우를 정의할 수 있습니다:
ROWS BETWEEN 2 PRECEDING AND 1 FOLLOWING예시 코드
각 부서별(PARTITION BY department)로 급여 순서대로(ORDER BY salary) 누적 합계를 계산(SUM(salary))하는데, 현재 행까지의 급여 총합을 구합니다(ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
SELECT id, name, salary,
SUM(salary) OVER (PARTITION BY department ORDER BY salary ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total
FROM employees;
2. 순위 함수
1 ) ROW_NUMBER()
각 행에 고유한 번호를 부여합니다.
예시 코드
각 부서별로 급여 순서대로 고유한 순번을 매깁니다. 순번은 같은 부서 내에서 급여가 높은 순으로 부여됩니다.
SELECT id, name, department, salary,
ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS row_num
FROM employees;| id | name | department | salary | row_num |
|---|---|---|---|---|
| 6 | David | Finance | 10000 | 1 |
| 5 | Charlie | Finance | 9000 | 2 |
| 4 | Bob | IT | 8000 | 1 |
| 7 | Eve | IT | 7500 | 2 |
| 3 | Alice | IT | 7000 | 3 |
| 2 | Jane | HR | 6000 | 1 |
| 8 | Frank | HR | 5500 | 2 |
| 1 | John | HR | 5000 | 3 |
2 ) RANK()
순위를 매기되, 동일한 값이 있을 경우 같은 순위를 부여하고 다음 순위는 건너뜁니다.
예시 코드
동일한 급여가 있을 경우 같은 순위를 부여하고, 그다음 순위는 건너뜁니다.
SELECT id, name, department, salary,
RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS rank
FROM employees;| id | name | department | salary | rank |
|---|---|---|---|---|
| 6 | David | Finance | 10000 | 1 |
| 5 | Charlie | Finance | 9000 | 2 |
| 4 | Bob | IT | 8000 | 1 |
| 7 | Eve | IT | 7500 | 2 |
| 3 | Alice | IT | 7000 | 3 |
| 2 | Jane | HR | 6000 | 1 |
| 8 | Frank | HR | 5500 | 2 |
| 1 | John | HR | 5000 | 3 |
3 ) DENSE_RANK()
RANK()와 유사하지만, 같은 순위 이후에도 순위가 연속적으로 부여됩니다.
예시 코드
동일한 값에 대해 같은 순위를 부여하지만, 그다음 순위는 연속적으로 부여됩니다.
SELECT id, name, department, salary,
DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS dense_rank
FROM employees;| id | name | department | salary | dense_rank |
|---|---|---|---|---|
| 6 | David | Finance | 10000 | 1 |
| 5 | Charlie | Finance | 9000 | 2 |
| 4 | Bob | IT | 8000 | 1 |
| 7 | Eve | IT | 7500 | 2 |
| 3 | Alice | IT | 7000 | 3 |
| 2 | Jane | HR | 6000 | 1 |
| 8 | Frank | HR | 5500 | 2 |
| 1 | John | HR | 5000 | 3 |
4 ) NTILE(n)
결과를 n개의 그룹으로 나누어 각 그룹에 번호를 매깁니다.
예시 코드
급여를 기준으로 데이터를 2개의 그룹으로 나누고, 각 그룹에 번호를 부여합니다. 상위 절반의 직원들은 그룹 1에, 하위 절반의 직원들은 그룹 2에 속합니다.
SELECT id, name, department, salary,
NTILE(2) OVER (ORDER BY salary DESC) AS ntile_group
FROM employees;| id | name | department | salary | ntile_group |
|---|---|---|---|---|
| 6 | David | Finance | 10000 | 1 |
| 5 | Charlie | Finance | 9000 | 1 |
| 4 | Bob | IT | 8000 | 1 |
| 7 | Eve | IT | 7500 | 2 |
| 3 | Alice | IT | 7000 | 2 |
| 2 | Jane | HR | 6000 | 2 |
| 8 | Frank | HR | 5500 | 2 |
| 1 | John | HR | 5000 | 2 |
5 ) LAG()
특정 행을 기준으로 이전 행의 값을 가져옵니다.
예시 코드
급여를 기준으로 이전 행의 급여를 가져옵니다. 첫 번째 행은 이전 행이 없기 때문에 NULL이 반환됩니다.
SELECT id, name, department, salary,
LAG(salary, 1) OVER (ORDER BY salary) AS prev_salary
FROM employees;| id | name | department | salary | prev_salary |
|---|---|---|---|---|
| 1 | John | HR | 5000 | NULL |
| 8 | Frank | HR | 5500 | 5000 |
| 2 | Jane | HR | 6000 | 5500 |
| 3 | Alice | IT | 7000 | 6000 |
| 7 | Eve | IT | 7500 | 7000 |
| 4 | Bob | IT | 8000 | 7500 |
| 5 | Charlie | Finance | 9000 | 8000 |
| 6 | David | Finance | 10000 | 9000 |
6 ) LEAD()
특정 행을 기준으로 이후 행의 값을 가져옵니다.
예시 코드
SELECT id, name, department, salary,
LEAD(salary, 1) OVER (ORDER BY salary) AS next_salary
FROM employees;급여를 기준으로 다음 행의 급여를 가져옵니다. 마지막 행은 다음 행이 없기 때문에 NULL이 반환됩니다.
| id | name | department | salary | next_salary |
|---|---|---|---|---|
| 1 | John | HR | 5000 | 5500 |
| 8 | Frank | HR | 5500 | 6000 |
| 2 | Jane | HR | 6000 | 7000 |
| 3 | Alice | IT | 7000 | 7500 |
| 7 | Eve | IT | 7500 | 8000 |
| 4 | Bob | IT | 8000 | 9000 |
| 5 | Charlie | Finance | 9000 | 10000 |
| 6 | David | Finance | 10000 | NULL |
2. 집계 함수
1 ) SUM()
특정 그룹의 합계를 구합니다.
예시 코드
SELECT id, name, department, salary,
SUM(salary) OVER (PARTITION BY department ORDER BY salary ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total
FROM employees;각 부서별로 급여 순서대로 누적 합계를 계산합니다.
| id | name | department | salary | running_total |
|---|---|---|---|---|
| 1 | John | HR | 5000 | 5000 |
| 8 | Frank | HR | 5500 | 10500 |
| 2 | Jane | HR | 6000 | 16500 |
| 3 | Alice | IT | 7000 | 7000 |
| 7 | Eve | IT | 7500 | 14500 |
| 4 | Bob | IT | 8000 | 22500 |
| 5 | Charlie | Finance | 9000 | 9000 |
| 6 | David | Finance | 10000 | 19000 |
2 ) AVG()
특정 그룹의 평균을 구합니다.
예시 코드
SELECT id, name, department, salary,
AVG(salary) OVER (PARTITION BY department) AS avg_salary
FROM employees;각 부서별로 급여의 평균을 계산합니다.
| id | name | department | salary | avg_salary |
|---|---|---|---|---|
| 6 | David | Finance | 10000 | 9500 |
| 5 | Charlie | Finance | 9000 | 9500 |
| 4 | Bob | IT | 8000 | 7500 |
| 7 | Eve | IT | 7500 | 7500 |
| 3 | Alice | IT | 7000 | 7500 |
| 2 | Jane | HR | 6000 | 5500 |
| 8 | Frank | HR | 5500 | 5500 |
| 1 | John | HR | 5000 | 5500 |
3 ) COUNT()
특정 그룹의 행 수를 구합니다.
예시 코드
SELECT id, name, department, salary,
COUNT(*) OVER (PARTITION BY department) AS emp_count
FROM employees;각 부서별로 직원 수를 계산합니다.
| id | name | department | salary | emp_count |
|---|---|---|---|---|
| 6 | David | Finance | 10000 | 2 |
| 5 | Charlie | Finance | 9000 | 2 |
| 4 | Bob | IT | 8000 | 3 |
| 7 | Eve | IT | 7500 | 3 |
| 3 | Alice | IT | 7000 | 3 |
| 2 | Jane | HR | 6000 | 3 |
| 8 | Frank | HR | 5500 | 3 |
| 1 | John | HR | 5000 | 3 |
4 ) MAX()
특정 그룹 내에서 최대 값을 구합니다.
예시 코드
SELECT id, name, department, salary,
MAX(salary) OVER (PARTITION BY department) AS max_salary
FROM employees;각 부서별로 급여의 최대 값을 구합니다.
| id | name | department | salary | max_salary |
|---|---|---|---|---|
| 6 | David | Finance | 10000 | 10000 |
| 5 | Charlie | Finance | 9000 | 10000 |
| 4 | Bob | IT | 8000 | 8000 |
| 7 | Eve | IT | 7500 | 8000 |
| 3 | Alice | IT | 7000 | 8000 |
| 2 | Jane | HR | 6000 | 6000 |
| 8 | Frank | HR | 5500 | 6000 |
| 1 | John | HR | 5000 | 6000 |
정리
MySQL의 윈도우 함수는 행과 행 사이의 관계를 쉽게 분석할 수 있는 기능을 제공하며, 순위 계산, 누적 집계, 이전 및 이후 값 참조 등 복잡한 분석을 간편하게 수행할 수 있습니다. 이를 적절하게 활용하면 데이터 분석의 효율성을 크게 높일 수 있습니다. 각 함수의 활용 방법을 이해하고 적용하면 복잡한 쿼리를 쉽게 작성할 수 있습니다.
