[MySQL] FIRST_VALUE()

FIRST_VALUE()

그룹 영역을 토대로 첫번째 값을 칼럼에 표시하는 함수이다. 즉, 조회된 세트(집합)에서 어떠한 상황에서도 첫 번째 값을 구하는 함수가 FIRST_VALUE이다. 주의해야 할 점은 OVER()에 ORDER BY 절을 꼭 삽입해야한다는 것이다.

SELECT COL1, COL2, FIRST_VALUE(COL1) OVER(ORDER BY COL2) 
  FROM TABLE_NAME
  
  
SELECT COL1, COL2, FIRST_VALUE(COL1) OVER(PARTITION BY COL1 ORDER BY COL2) 
  FROM TABLE_NAME

https://dev.mysql.com/doc/refman/8.0/en/window-function-descriptions.html#function_first-value

MySQL :: MySQL 8.0 Reference Manual :: 12.21.1 Window Function Descriptions

 

---

FIRST_VALUE() 예시

 

테스트 테이블 및 데이터 생성

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);

 

 

집합 없이 단순 최상단에 있는 칼럼 값 가져오기

SELECT
       employee_name,
       hours,
       FIRST_VALUE(employee_name) OVER (ORDER BY hours) least_over_time
  FROM
       overtime;

 

 

 

집합 기준으로 최상단에 있는 칼럼 값 가져오기

SELECT
       employee_name,
       department,
       hours,
       FIRST_VALUE(employee_name) OVER (PARTITION BY department ORDER BY hours) least_over_time
  FROM 
       overtime;

---

참고 사이트: https://www.mysqltutorial.org/mysql-window-functions/mysql-first_value-function/ 

MySQL FIRST_VALUE Window Function By Practical Examples