이 랩에서는 MySQL 윈도우 함수 (Window Functions) 의 강력함을 탐구합니다. 현재 행과 관련된 테이블 행 집합에 걸쳐 계산을 수행하는 방법을 배우게 됩니다.
샘플 데이터셋을 생성하는 것으로 시작하여 ROW_NUMBER(), SUM(), AVG(), LAG()와 같은 함수를 OVER() 절과 함께 사용하여 고급 데이터 분석을 수행할 것입니다. 이 랩은 이러한 강력한 함수를 이해하고 적용하는 데 도움이 되는 실용적이고 단계별 예제를 제공합니다.
윈도우 함수를 사용하기 전에 데이터베이스와 샘플 데이터가 포함된 테이블이 필요합니다. 이 단계에서는 company라는 데이터베이스와 employees라는 테이블을 생성합니다.
먼저 데스크톱에서 터미널을 엽니다.
root 사용자로 MySQL 서버에 연결합니다. 이 환경은 랩 환경이므로 비밀번호 없이 sudo를 사용하여 연결할 수 있습니다.
sudo mysql -u root연결되면 MySQL 프롬프트 (mysql>) 가 표시됩니다.
이제 company 데이터베이스를 생성하고 해당 데이터베이스로 전환합니다. IF NOT EXISTS 절은 데이터베이스가 이미 존재하는 경우 오류를 방지합니다.
CREATE DATABASE IF NOT EXISTS company;
USE company;다음으로 employees 테이블을 생성합니다. 이 테이블은 직원 ID, 이름, 부서 및 급여를 저장합니다.
CREATE TABLE IF NOT EXISTS employees (
employee_id INT PRIMARY KEY,
employee_name VARCHAR(50),
department VARCHAR(50),
salary DECIMAL(10, 2)
);이제 employees 테이블에 샘플 데이터를 삽입합니다.
INSERT INTO employees (employee_id, employee_name, department, salary) VALUES
(1, 'Alice', 'Sales', 60000.00),
(2, 'Bob', 'Marketing', 55000.00),
(3, 'Charlie', 'Sales', 65000.00),
(4, 'David', 'IT', 70000.00),
(5, 'Eve', 'Marketing', 62000.00)
ON DUPLICATE KEY UPDATE
employee_name = VALUES(employee_name),
department = VALUES(department),
salary = VALUES(salary);데이터가 올바르게 삽입되었는지 확인하려면 employees 테이블의 모든 행을 볼 수 있습니다.
SELECT * FROM employees;출력에는 삽입한 다섯 개의 레코드가 표시되어야 합니다.
+-------------+---------------+------------+----------+
| employee_id | employee_name | department | salary |
+-------------+---------------+------------+----------+
| 1 | Alice | Sales | 60000.00 |
| 2 | Bob | Marketing | 55000.00 |
| 3 | Charlie | Sales | 65000.00 |
| 4 | David | IT | 70000.00 |
| 5 | Eve | Marketing | 62000.00 |
+-------------+---------------+------------+----------+
5 rows in set (0.00 sec)데이터베이스와 테이블이 준비되었으므로 다음 단계로 이동하여 윈도우 함수에 대해 알아보겠습니다.
ROW_NUMBER() 함수는 결과 집합의 파티션 내 각 행에 고유한 순차 정수를 할당합니다. 일반적으로 순위 매기기 및 페이징에 사용됩니다.
기본 구문은 다음과 같습니다.
ROW_NUMBER() OVER (ORDER BY column_name [ASC|DESC])
OVER(): 이 절은 함수의 윈도우 (행 집합) 를 정의합니다.ORDER BY: OVER() 내의 이 절은 행 번호가 할당되는 순서를 지정합니다.MySQL 쉘에서 계속해서 ROW_NUMBER()를 사용하여 직원을 급여 내림차순으로 순위를 매깁니다.
SELECT
employee_name,
department,
salary,
ROW_NUMBER() OVER (ORDER BY salary DESC) AS salary_rank
FROM
employees;이 쿼리는 salary_rank 열을 추가하여 직원을 급여가 높은 순서대로 번호를 매깁니다.
+---------------+------------+----------+-------------+
| employee_name | department | salary | salary_rank |
+---------------+------------+----------+-------------+
| David | IT | 70000.00 | 1 |
| Charlie | Sales | 65000.00 | 2 |
| Eve | Marketing | 62000.00 | 3 |
| Alice | Sales | 60000.00 | 4 |
| Bob | Marketing | 55000.00 | 5 |
+---------------+------------+----------+-------------+
5 rows in set (0.00 sec)보시다시피 David 는 가장 높은 급여를 받기 때문에 순위가 1 입니다. 이는 ROW_NUMBER()를 사용하여 간단한 순위를 생성하는 방법을 보여줍니다.
누계 합계 (running total) 또는 누적 합계는 새로운 숫자가 추가될 때마다 업데이트되는 숫자 시퀀스의 합계입니다. SQL 에서는 SUM() OVER()을 사용하여 이를 계산할 수 있습니다.
구문은 다음과 같습니다.
SUM(column_name) OVER (ORDER BY column_name [ASC|DESC])
이 함수는 ORDER BY 절에 지정된 순서대로 열의 값을 합산합니다.
이제 employee_id로 정렬하여 급여의 누계 합계를 계산해 보겠습니다.
SELECT
employee_name,
salary,
SUM(salary) OVER (ORDER BY employee_id) AS running_total
FROM
employees;결과는 각 직원의 급여와 해당 행까지의 누적 합계를 보여줍니다.
+---------------+----------+---------------+
| employee_name | salary | running_total |
+---------------+----------+---------------+
| Alice | 60000.00 | 60000.00 |
| Bob | 55000.00 | 115000.00 |
| Charlie | 65000.00 | 180000.00 |
| David | 70000.00 | 250000.00 |
| Eve | 62000.00 | 312000.00 |
+---------------+----------+---------------+
5 rows in set (0.00 sec)예를 들어, Bob 의 running_total은 그의 급여와 Alice 의 급여를 합한 값입니다 (60000.00 + 55000.00 = 115000.00). 이는 시간 경과에 따른 판매 또는 비용과 같은 누적 지표를 추적하는 데 유용합니다.
PARTITION BY 절은 결과 집합을 파티션 (그룹) 으로 나누고 각 파티션에 윈도우 함수를 독립적으로 적용합니다. 이는 특정 범주 내에서 계산을 수행하는 데 유용합니다.
구문은 다음과 같습니다.
function() OVER (PARTITION BY column_name ORDER BY ...)
PARTITION BY를 사용하여 각 부서 내 직원을 급여 기준으로 순위를 매겨 보겠습니다.
SELECT
employee_name,
department,
salary,
ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rank_in_dept
FROM
employees;이 쿼리는 데이터를 department별로 파티션한 다음 각 부서 내 직원을 급여 기준으로 순위를 매깁니다.
+---------------+------------+----------+--------------+
| employee_name | department | salary | rank_in_dept |
+---------------+------------+----------+--------------+
| David | IT | 70000.00 | 1 |
| Eve | Marketing | 62000.00 | 1 |
| Bob | Marketing | 55000.00 | 2 |
| Charlie | Sales | 65000.00 | 1 |
| Alice | Sales | 60000.00 | 2 |
+---------------+------------+----------+--------------+
5 rows in set (0.00 sec)각 부서별로 순위가 다시 시작되는 것을 알 수 있습니다. 예를 들어, Eve 와 Charlie 모두 순위가 1 이지만, 각각 'Marketing' 및 'Sales' 부서 내에서의 순위입니다. 이를 통해 전역 순위와 비교하여 더 세분화된 분석이 가능합니다.
LAG() 함수는 현재 행 이전의 지정된 물리적 오프셋에 있는 행에 접근할 수 있게 해줍니다. 이는 현재 행의 값과 이전 행의 값을 비교하는 데 유용합니다.
구문은 다음과 같습니다.
LAG(expression, offset, default_value) OVER (ORDER BY ...)
expression: 검색할 열 또는 표현식입니다.offset: 이전으로 돌아갈 행의 수입니다 (기본값은 1).default_value: 오프셋이 범위를 벗어나는 경우 반환할 값입니다 (예: 첫 번째 행의 경우).employee_id로 정렬된 목록에서 이전 직원의 급여를 찾아보겠습니다.
SELECT
employee_name,
salary,
LAG(salary) OVER (ORDER BY employee_id) AS previous_salary
FROM
employees;이 쿼리는 이전 행의 급여를 검색합니다. 이전 행이 없는 첫 번째 행의 경우 NULL을 반환합니다.
+---------------+----------+-----------------+
| employee_name | salary | previous_salary |
+---------------+----------+-----------------+
| Alice | 60000.00 | NULL |
| Bob | 55000.00 | 60000.00 |
| Charlie | 65000.00 | 55000.00 |
| David | 70000.00 | 65000.00 |
| Eve | 62000.00 | 70000.00 |
+---------------+----------+-----------------+
5 rows in set (0.00 sec)이를 사용하여 연속된 급여 간의 차이를 계산할 수 있습니다. 이전 급여가 NULL인 경우 (첫 번째 행의 경우), 결과도 NULL이 됩니다.
SELECT
employee_name,
salary,
salary - LAG(salary) OVER (ORDER BY employee_id) AS salary_diff
FROM
employees;이 쿼리는 현재 직원의 급여와 이전 직원의 급여 간의 차이를 계산합니다.
+---------------+----------+-------------+
| employee_name | salary | salary_diff |
+---------------+----------+-------------+
| Alice | 60000.00 | NULL |
| Bob | 55000.00 | -5000.00 |
| Charlie | 65000.00 | 10000.00 |
| David | 70000.00 | 5000.00 |
| Eve | 62000.00 | -8000.00 |
+---------------+----------+-------------+
5 rows in set (0.00 sec)이제 몇 가지 주요 윈도우 함수를 연습했습니다. MySQL 쉘을 종료할 수 있습니다.
exit;이 실습에서는 MySQL 윈도우 함수 사용법을 살펴보았습니다. ROW_NUMBER()를 사용하여 행 번호를 할당하는 방법, SUM() OVER()로 누계 합계를 계산하는 방법, PARTITION BY를 사용하여 특정 그룹에 대한 계산을 수행하는 방법, LAG()로 이전 행의 데이터에 접근하는 방법을 배웠습니다.
샘플 데이터셋에 이러한 함수를 적용함으로써 SQL 쿼리 내에서 직접 고급 데이터 분석을 수행하는 실질적인 경험을 쌓았습니다. 이는 데이터에서 복잡한 보고서와 인사이트를 생성하는 데 유용한 기술입니다.
