오라클/SQL FIRST_VALUE, LAST_VALUE 함수: 시작 레코드와 마지막 레코드의 값 조회하기

오라클 SQL에서 FIRST_VALUE와 LAST_VALUE 함수는 특정 컬럼을 기준으로 조회된 레코드 중 첫 번째 값과 마지막 값을 쉽게 가져올 수 있도록 도와주는 윈도우 함수(Window Function)입니다. 이를 활용하면 그룹별로 가장 높은 값이나 낮은 값을 효율적으로 조회할 수 있습니다.

이번 글에서는 FIRST_VALUE와 LAST_VALUE 함수의 개념을 설명하고, 다양한 예제와 함께 활용법을 알아보겠습니다.

---

1. FIRST_VALUE, LAST_VALUE 함수 개요

1) FIRST_VALUE, LAST_VALUE 함수란?

• FIRST_VALUE: 선택된 레코드들 중 특정 컬럼을 기준으로 첫 번째(가장 앞선) 값을 출력합니다.
• LAST_VALUE: 선택된 레코드들 중 특정 컬럼을 기준으로 마지막 값을 출력합니다. 다만, 기본적으로 현재 레코드 기준의 마지막 값을 반환하는 특징이 있습니다.

2) 활용 사례

이 두 함수는 다음과 같은 데이터 조회 시 유용합니다.

• 각 반별로 석차 1등과 마지막 학생의 이름 또는 점수 조회
• 회사에서 부서별로 가장 높은 급여와 가장 낮은 급여를 받는 직원 조회
• 그룹별로 특정 기준(날짜, 점수 등)의 첫 번째와 마지막 값을 확인

---

2. FIRST_VALUE, LAST_VALUE 함수 사용법

1) 기본 사용법

SELECT문에서 FIRST_VALUE와 LAST_VALUE를 윈도우 함수로 사용하여 원하는 데이터를 조회할 수 있습니다.

기본 구문:

SELECT 컬럼X, 컬럼Y, ... ,
       FIRST_VALUE(컬럼K) OVER(ORDER BY 컬럼M) AS first_value,
       LAST_VALUE(컬럼K) OVER(ORDER BY 컬럼M) AS last_value
FROM 테이블A;

• ORDER BY 컬럼M을 기준으로 정렬했을 때 첫 번째 값과 현재 레코드의 마지막 값이 출력됩니다.

2) 내림차순 정렬 시 사용

내림차순(역순) 정렬 시 첫 번째 값과 마지막 값이 반대로 바뀝니다.

SELECT 컬럼X, 컬럼Y, ... ,
       FIRST_VALUE(컬럼K) OVER(ORDER BY 컬럼M DESC) AS first_value,
       LAST_VALUE(컬럼K) OVER(ORDER BY 컬럼M DESC) AS last_value
FROM 테이블A;

• ORDER BY 컬럼M DESC를 사용하면 가장 큰 값이 FIRST_VALUE로, 가장 작은 값이 LAST_VALUE로 출력됩니다.

3) 그룹별 조회 (PARTITION BY)

특정 그룹별로 첫 번째 값과 마지막 값을 구하려면 PARTITION BY를 사용해야 합니다.

SELECT 컬럼X, 컬럼Y, ... ,
       FIRST_VALUE(컬럼K) OVER(PARTITION BY 컬럼L ORDER BY 컬럼M) AS first_value,
       LAST_VALUE(컬럼K) OVER(PARTITION BY 컬럼L ORDER BY 컬럼M) AS last_value
FROM 테이블A;

• PARTITION BY 컬럼L을 사용하면 해당 컬럼 기준으로 그룹을 나누어 계산합니다.

4) 그룹의 마지막 값 조회 (ROWS BETWEEN 사용)

기본적으로 LAST_VALUE 함수는 현재 레코드를 기준으로 마지막 값을 반환합니다. 하지만 그룹의 전체 마지막 값을 얻으려면 ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING 옵션을 추가해야 합니다.

SELECT 컬럼X, 컬럼Y, ... ,
       FIRST_VALUE(컬럼K) OVER(PARTITION BY 컬럼L ORDER BY 컬럼M) AS first_value,
       LAST_VALUE(컬럼K) OVER(PARTITION BY 컬럼L ORDER BY 컬럼M
                          ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS last_value
FROM 테이블A;

---

3. 예제 실습

이제 실제 예제를 통해 사용법을 익혀보겠습니다. 사용 테이블은 employees 테이블입니다.

1) 전체 직원 중 첫 번째/마지막 직원 이름 조회

SELECT employee_id, first_name, salary,
       FIRST_VALUE(first_name) OVER(ORDER BY salary) AS first_value,
       LAST_VALUE(first_name) OVER(ORDER BY salary) AS last_value
FROM employees;

결과 분석:

• first_value 컬럼: 가장 낮은 급여를 받는 직원의 이름이 모든 행에 출력됨.
• last_value 컬럼: 각 레코드의 현재 급여와 같은 급여를 받는 마지막 직원의 이름이 출력됨.

2) 부서별 첫 번째/마지막 급여 조회

SELECT employee_id, first_name, job_id, salary,
       FIRST_VALUE(salary) OVER(PARTITION BY job_id ORDER BY salary) AS first_value,
       LAST_VALUE(salary) OVER(PARTITION BY job_id ORDER BY salary) AS last_value
FROM employees
ORDER BY job_id, salary;

결과 분석:

• first_value: 각 job_id 그룹 내에서 가장 낮은 급여.
• last_value: 각 job_id 그룹 내에서 현재 행의 마지막 급여.

3) 그룹의 마지막 급여 값 강제 지정

SELECT employee_id, first_name, job_id, salary,
       FIRST_VALUE(salary) OVER(PARTITION BY job_id ORDER BY salary) AS first_value,
       LAST_VALUE(salary) OVER(PARTITION BY job_id ORDER BY salary
                              ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS last_value
FROM employees
ORDER BY job_id, salary;

결과 분석:

• first_value: 각 job_id 그룹 내에서 가장 낮은 급여.
• last_value: 각 job_id 그룹 내에서 가장 높은 급여가 모든 행에 출력됨.

---

4. 정리 및 마무리

• FIRST_VALUE: 정렬된 결과에서 첫 번째 값을 출력.
• LAST_VALUE: 정렬된 결과에서 현재 행 기준 마지막 값을 출력.
• PARTITION BY: 특정 그룹별로 첫 번째/마지막 값을 계산.
• ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING: 그룹 전체 마지막 값을 출력하도록 설정.

이러한 윈도우 함수를 활용하면 SQL에서 데이터를 더욱 효율적으로 조회할 수 있습니다. 다양한 조건과 조합을 적용하여 원하는 결과를 도출해보세요!