개요
2023.05.14 - [MySQL] 윈도우 함수 - OVER
2023.05.15 - [MySQL] 집계 함수와 OVER 절
2023.05.16 - [MySQL] 순위 함수 (RANK)
2023.05.17 - [MySQL] 추출 함수 (FIRST_VALUE/LAST_VALUE)
위 글에서 윈도우 함수에 대해 정리하고 있는데, 이 글에서는 윈도우 함수 중 비집계 함수에 해당하는 LAG, LEAD 함수에 대해 정리한다.
예시 데이터
아래와 같이 사원 이름(employee_name), 부서(department), 시간(hours)으로 구성된 초과근무 시간 테이블을 예시 데이터로 사용한다.
DDL
CREATE TABLE overtime (
employee_name varchar(50) NOT NULL,
department varchar(50) NOT NULL,
hours int NOT NULL,
PRIMARY KEY (employee_name, department)
);
INSERT INTO overtime (employee_name, department, hours)
VALUES ('Diane Murphy', 'Accounting', 37),
('Mary Patterson', 'Accounting', 74),
('Jeff Firrelli', 'Accounting', 40),
('William Patterson', 'Finance', 58),
('Gerard Bondur', 'Finance', 47),
('Anthony Bow', 'Finance', 66),
('Leslie Jennings', 'IT', 90),
('Leslie Thompson', 'IT', 88),
('Julie Firrelli', 'Sales', 81),
('Steve Patterson', 'Sales', 29),
('Foon Yue Tseng', 'Sales', 65),
('George Vanauf', 'Marketing', 89),
('Loui Bondur', 'Marketing', 49),
('Gerard Hernandez', 'Marketing', 66),
('Pamela Castillo', 'SCM', 96),
('Larry Bott', 'SCM', 100),
('Barry Jones', 'SCM', 65);| employee_name | department | hours |
| ----------------- | ---------- | ----- |
| Anthony Bow | Finance | 66 |
| Barry Jones | SCM | 65 |
| Diane Murphy | Accounting | 37 |
| Foon Yue Tseng | Sales | 65 |
| George Vanauf | Marketing | 89 |
| Gerard Bondur | Finance | 47 |
| Gerard Hernandez | Marketing | 66 |
| Jeff Firrelli | Accounting | 40 |
| Julie Firrelli | Sales | 81 |
| Larry Bott | SCM | 100 |
| Leslie Jennings | IT | 90 |
| Leslie Thompson | IT | 88 |
| Loui Bondur | Marketing | 49 |
| Mary Patterson | Accounting | 74 |
| Pamela Castillo | SCM | 96 |
| Steve Patterson | Sales | 29 |
| William Patterson | Finance | 58 |
LAG
LAG 함수는 결과 집합의 현재 행에서 이전 행의 데이터에 접근한다. LAG 함수에 전달한 offset을 이용해 몇 개 행 이전 값을 가져올지 지정할 수 있다.
LAG(<expression>[,offset[, default_value]]) OVER (
PARTITION BY expr,...
ORDER BY expr [ASC|DESC],...
)
예시로 사원 정보를 초과 근무 시간을 기준으로 오름차순으로 나열하고 n번째 사원과 n-1번째 사원의 초과 근무 시간 차이를 구한다.
SELECT
employee_name,
hours,
IFNULL(hours - LAG(hours) OVER (ORDER BY hours), 0) AS overtime_gap
FROM overtime;
실행 결과
| employee_name | hours | overtime_gap |
| ----------------- | ----- | ------------ |
| Steve Patterson | 29 | 0 |
| Diane Murphy | 37 | 8 |
| Jeff Firrelli | 40 | 3 |
| Gerard Bondur | 47 | 7 |
| Loui Bondur | 49 | 2 |
| William Patterson | 58 | 9 |
| Barry Jones | 65 | 7 |
| Foon Yue Tseng | 65 | 0 |
| Gerard Hernandez | 66 | 1 |
| Anthony Bow | 66 | 0 |
| Mary Patterson | 74 | 8 |
| Julie Firrelli | 81 | 7 |
| Leslie Thompson | 88 | 7 |
| George Vanauf | 89 | 1 |
| Leslie Jennings | 90 | 1 |
| Pamela Castillo | 96 | 6 |
| Larry Bott | 100 | 4 |
LEAD
LEAD 함수는 LAG 함수와 반대로 결과 집합의 현재 행에서 이후 행의 데이터에 접근한다. 함수에 전달한 offset을 이용해 몇 개 행 이전 값을 가져올지 지정할 수 있다.
LEAD(<expression>[,offset[, default_value]]) OVER (
PARTITION BY (expr)
ORDER BY (expr)
)
예시로 부서 별로 사원 정보를 초과 근무 시간을 기준으로 내림차순 정렬하고 n번째 사원과 n-1번째 사원의 초과 근무 시간 차이를 구한다.
SELECT
employee_name,
department,
hours,
IFNULL(hours - LEAD(hours) OVER (PARTITION BY department
ORDER BY hours DESC), 0) AS overtime_gap
FROM overtime;
실행 결과
| employee_name | department | hours | overtime_gap |
| ----------------- | ---------- | ----- | ------------ |
| Mary Patterson | Accounting | 74 | 34 |
| Jeff Firrelli | Accounting | 40 | 3 |
| Diane Murphy | Accounting | 37 | 0 |
| Anthony Bow | Finance | 66 | 8 |
| William Patterson | Finance | 58 | 11 |
| Gerard Bondur | Finance | 47 | 0 |
| Leslie Jennings | IT | 90 | 2 |
| Leslie Thompson | IT | 88 | 0 |
| George Vanauf | Marketing | 89 | 23 |
| Gerard Hernandez | Marketing | 66 | 17 |
| Loui Bondur | Marketing | 49 | 0 |
| Julie Firrelli | Sales | 81 | 16 |
| Foon Yue Tseng | Sales | 65 | 36 |
| Steve Patterson | Sales | 29 | 0 |
| Larry Bott | SCM | 100 | 4 |
| Pamela Castillo | SCM | 96 | 31 |
| Barry Jones | SCM | 65 | 0 |
참고 문서
12.20.1 Window Function Descriptions
https://www.mysqltutorial.org/mysql-window-functions/mysql-lag-function/
https://www.mysqltutorial.org/mysql-window-functions/mysql-lead-function/