개요
2023.05.14 - [MySQL] 윈도우 함수 - OVER
2023.05.15 - [MySQL] 집계 함수와 OVER 절
2023.05.16 - [MySQL] 순위 함수 (RANK)
위 글에서 윈도우 함수에 대해 정리하고 있는데, 이 글에서는 윈도우 함수 중 비집계 함수에 해당하는 추출 함수에 대해 정리한다.
예시 데이터
아래와 같이 사원 이름(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 |
FIRST_VALUE
파티션 또는 결과 집합 내에서 첫번째 행을 선택하여 반환한다.
FIRST_VALUE (expression) OVER (
[partition_clause]
[order_clause]
[frame_clause]
)
예시로 가장 초과 근무를 적게 한 사원의 이름을 추출한다.
SELECT
employee_name,
hours,
FIRST_VALUE(employee_name) OVER (ORDER BY hours) least_over_time
FROM overtime;
실행 결과
| employee_name | hours | least_over_time |
| ----------------- | ----- | --------------- |
| Steve Patterson | 29 | Steve Patterson |
| Diane Murphy | 37 | Steve Patterson |
| Jeff Firrelli | 40 | Steve Patterson |
| Gerard Bondur | 47 | Steve Patterson |
| Loui Bondur | 49 | Steve Patterson |
| William Patterson | 58 | Steve Patterson |
| Barry Jones | 65 | Steve Patterson |
| Foon Yue Tseng | 65 | Steve Patterson |
| Gerard Hernandez | 66 | Steve Patterson |
| Anthony Bow | 66 | Steve Patterson |
| Mary Patterson | 74 | Steve Patterson |
| Julie Firrelli | 81 | Steve Patterson |
| Leslie Thompson | 88 | Steve Patterson |
| George Vanauf | 89 | Steve Patterson |
| Leslie Jennings | 90 | Steve Patterson |
| Pamela Castillo | 96 | Steve Patterson |
| Larry Bott | 100 | Steve Patterson |
LAST_VALUE
파티션 또는 결과 집합 내에서 마지막 행을 선택하여 반환한다.
LAST_VALUE (expression) OVER (
[partition_clause]
[order_clause]
[frame_clause]
)
예시로 부서 별 초과 근무 시간이 가장 적은 사원의 이름을 추출한다.
SELECT
employee_name,
hours,
LAST_VALUE(employee_name) OVER (PARTITION BY department ORDER BY hours) least_over_time
FROM overtime;
실행 결과
| employee_name | hours | least_over_time |
| ----------------- | ----- | ----------------- |
| Diane Murphy | 37 | Diane Murphy |
| Jeff Firrelli | 40 | Jeff Firrelli |
| Mary Patterson | 74 | Mary Patterson |
| Gerard Bondur | 47 | Gerard Bondur |
| William Patterson | 58 | William Patterson |
| Anthony Bow | 66 | Anthony Bow |
| Leslie Thompson | 88 | Leslie Thompson |
| Leslie Jennings | 90 | Leslie Jennings |
| Loui Bondur | 49 | Loui Bondur |
| Gerard Hernandez | 66 | Gerard Hernandez |
| George Vanauf | 89 | George Vanauf |
| Steve Patterson | 29 | Steve Patterson |
| Foon Yue Tseng | 65 | Foon Yue Tseng |
| Julie Firrelli | 81 | Julie Firrelli |
| Barry Jones | 65 | Barry Jones |
| Pamela Castillo | 96 | Pamela Castillo |
| Larry Bott | 100 | Larry Bott |
NTH_VALUE
파티션 또는 결과 집합 내에서 N번째 행을 선택하여 반환한다.
NTH_VALUE(expression, N) FROM FIRST OVER (
partition_clause
order_clause
frame_clause
)
예시로 부서 별 초과 근무 시간이 두 번째로 많은 사원의 이름을 추출한다.
SELECT
employee_name,
department,
hours,
NTH_VALUE(employee_name, 2) OVER (PARTITION BY department
ORDER BY hours DESC
RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS second_over_time
FROM overtime;
실행 결과
| employee_name | department | hours | second_over_time |
| ----------------- | ---------- | ----- | ----------------- |
| Mary Patterson | Accounting | 74 | Jeff Firrelli |
| Jeff Firrelli | Accounting | 40 | Jeff Firrelli |
| Diane Murphy | Accounting | 37 | Jeff Firrelli |
| Anthony Bow | Finance | 66 | William Patterson |
| William Patterson | Finance | 58 | William Patterson |
| Gerard Bondur | Finance | 47 | William Patterson |
| Leslie Jennings | IT | 90 | Leslie Thompson |
| Leslie Thompson | IT | 88 | Leslie Thompson |
| George Vanauf | Marketing | 89 | Gerard Hernandez |
| Gerard Hernandez | Marketing | 66 | Gerard Hernandez |
| Loui Bondur | Marketing | 49 | Gerard Hernandez |
| Julie Firrelli | Sales | 81 | Foon Yue Tseng |
| Foon Yue Tseng | Sales | 65 | Foon Yue Tseng |
| Steve Patterson | Sales | 29 | Foon Yue Tseng |
| Larry Bott | SCM | 100 | Pamela Castillo |
| Pamela Castillo | SCM | 96 | Pamela Castillo |
| Barry Jones | SCM | 65 | Pamela Castillo |
참고 문서
https://www.mysqltutorial.org/mysql-window-functions/mysql-first_value-function/
728x90