SQL에서 FIRST_VALUE, LAST_VALUE 함수를 사용하여 첫 번째 및 마지막 값을 가져오는 방법

SQL을 사용한 데이터 분석이나 보고서 작성에서는 데이터 세트의 첫 번째 값이나 마지막 값을 가져오는 것이 자주 요구됩니다. 이를 쉽게 실현하기 위해, FIRST_VALUE 함수와 LAST_VALUE 함수가 유용합니다. 이번 글에서는 이러한 함수들을 사용하여 데이터를 효율적으로 조작하는 방법에 대해 구체적인 예시를 통해 자세히 설명하겠습니다.

FIRST_VALUE 함수의 기본적인 사용법

FIRST_VALUE 함수는 지정된 윈도우 또는 파티션 내에서 첫 번째 값을 가져오는 데 사용됩니다. 이를 통해 데이터 세트의 첫 번째 행의 값을 쉽게 추출할 수 있습니다.

기본적인 문법

SELECT 
    column1,
    FIRST_VALUE(column1) OVER (PARTITION BY column2 ORDER BY column3) AS first_value
FROM 
    table_name;

이 예에서는 column2로 데이터를 파티션을 나누고, column3로 정렬한 후 첫 번째 column1 값을 가져옵니다.

사용 예시

예를 들어, 직원 데이터베이스에서 각 부서의 첫 번째로 입사한 직원의 이름을 가져오고 싶다면, 아래와 같은 쿼리를 사용할 수 있습니다.

SELECT 
    department,
    employee_name,
    FIRST_VALUE(employee_name) OVER (PARTITION BY department ORDER BY hire_date) AS first_hired
FROM 
    employees;

이 쿼리는 각 부서별로 가장 먼저 입사한 직원의 이름을 반환합니다.

FIRST_VALUE 함수의 응용 예시

FIRST_VALUE 함수는 기본적인 사용법 외에도 복잡한 쿼리나 분석에서도 유용합니다. 여기서는 실제 데이터 세트를 사용한 응용 예시를 소개하겠습니다.

판매 데이터에서 첫 번째 판매일을 가져오기

예를 들어, 특정 상품의 판매 데이터에서 각 매장의 첫 번째 판매일을 가져오고 싶다면, 다음과 같은 쿼리를 사용할 수 있습니다.

SELECT 
    store_id,
    product_id,
    sale_date,
    FIRST_VALUE(sale_date) OVER (PARTITION BY store_id, product_id ORDER BY sale_date) AS first_sale_date
FROM 
    sales;

이 쿼리는 각 매장 및 각 상품의 첫 번째 판매일을 반환합니다.

학생 성적 데이터에서 첫 번째 시험 결과 가져오기

학생 성적 데이터를 관리할 때, 각 학생의 첫 번째 시험 결과를 가져올 수 있습니다.

SELECT 
    student_id,
    exam_date,
    score,
    FIRST_VALUE(score) OVER (PARTITION BY student_id ORDER BY exam_date) AS first_exam_score
FROM 
    exam_results;

이 쿼리는 각 학생의 첫 번째 시험 점수를 반환합니다.

트랜잭션 데이터에서 첫 번째 구매 금액 가져오기

고객의 트랜잭션 데이터에서 각 고객의 첫 번째 구매 금액을 가져오는 예시입니다.

SELECT 
    customer_id,
    transaction_date,
    amount,
    FIRST_VALUE(amount) OVER (PARTITION BY customer_id ORDER BY transaction_date) AS first_purchase_amount
FROM 
    transactions;

이 쿼리는 각 고객의 첫 번째 구매 금액을 반환합니다.

LAST_VALUE 함수의 기본적인 사용법

LAST_VALUE 함수는 지정된 윈도우 또는 파티션 내에서 마지막 값을 가져오는 데 사용됩니다. 이를 통해 데이터 세트의 마지막 행의 값을 쉽게 추출할 수 있습니다.

기본적인 문법

SELECT 
    column1,
    LAST_VALUE(column1) OVER (PARTITION BY column2 ORDER BY column3 ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS last_value
FROM 
    table_name;

이 예에서는 column2로 데이터를 파티션을 나누고, column3로 정렬한 후 마지막 column1 값을 가져옵니다. ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING 구문을 사용하여 윈도우 전체를 지정하는 것이 중요합니다.

사용 예시

예를 들어, 직원 데이터베이스에서 각 부서의 마지막으로 입사한 직원의 이름을 가져오고 싶다면, 아래와 같은 쿼리를 사용할 수 있습니다.

SELECT 
    department,
    employee_name,
    LAST_VALUE(employee_name) OVER (PARTITION BY department ORDER BY hire_date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS last_hired
FROM 
    employees;

이 쿼리는 각 부서별로 가장 늦게 입사한 직원의 이름을 반환합니다.

LAST_VALUE 함수의 응용 예시

LAST_VALUE 함수 역시 FIRST_VALUE 함수와 마찬가지로 데이터 분석이나 보고서 작성에서 매우 유용합니다. 여기서는 실제 데이터 세트를 사용한 응용 예시를 소개하겠습니다.

판매 데이터에서 마지막 판매일을 가져오기

예를 들어, 특정 상품의 판매 데이터에서 각 매장의 마지막 판매일을 가져오고 싶다면, 다음과 같은 쿼리를 사용할 수 있습니다.

SELECT 
    store_id,
    product_id,
    sale_date,
    LAST_VALUE(sale_date) OVER (PARTITION BY store_id, product_id ORDER BY sale_date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS last_sale_date
FROM 
    sales;

이 쿼리는 각 매장 및 각 상품의 마지막 판매일을 반환합니다.

학생 성적 데이터에서 마지막 시험 결과 가져오기

학생 성적 데이터를 관리할 때, 각 학생의 마지막 시험 결과를 가져올 수 있습니다.

SELECT 
    student_id,
    exam_date,
    score,
    LAST_VALUE(score) OVER (PARTITION BY student_id ORDER BY exam_date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS last_exam_score
FROM 
    exam_results;

이 쿼리는 각 학생의 마지막 시험 점수를 반환합니다.

트랜잭션 데이터에서 마지막 구매 금액 가져오기

고객의 트랜잭션 데이터에서 각 고객의 마지막 구매 금액을 가져오는 예시입니다.

SELECT 
    customer_id,
    transaction_date,
    amount,
    LAST_VALUE(amount) OVER (PARTITION BY customer_id ORDER BY transaction_date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS last_purchase_amount
FROM 
    transactions;

이 쿼리는 각 고객의 마지막 구매 금액을 반환합니다.

FIRST_VALUE와 LAST_VALUE의 조합

FIRST_VALUE 함수와 LAST_VALUE 함수를 조합하여 데이터 세트의 첫 번째 및 마지막 값을 동시에 가져올 수 있습니다. 이를 통해 예를 들어 기간 중 첫 번째와 마지막 값을 비교하는 등 더욱 고급 분석이 가능합니다.

판매 데이터의 첫 번째 및 마지막 판매일 가져오기

예를 들어, 각 매장의 판매 데이터에서 첫 번째와 마지막 판매일을 가져오는 쿼리는 아래와 같습니다.

SELECT 
    store_id,
    product_id,
    FIRST_VALUE(sale_date) OVER (PARTITION BY store_id, product_id ORDER BY sale_date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS first_sale_date,
    LAST_VALUE(sale_date) OVER (PARTITION BY store_id, product_id ORDER BY sale_date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS last_sale_date
FROM 
    sales;

이 쿼리는 각 매장 및 각 상품의 첫 번째와 마지막 판매일을 반환합니다.

직원 데이터의 첫 번째 및 마지막 입사일 가져오기

직원 데이터베이스에서 각 부서의 첫 번째와 마지막 입사일을 가져오는 경우, 아래와 같은 쿼리를 사용할 수 있습니다.

SELECT 
    department,
    employee_name,
    FIRST_VALUE(hire_date) OVER (PARTITION BY department ORDER BY hire_date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS first_hire_date,
    LAST_VALUE(hire_date) OVER (PARTITION BY department ORDER BY hire_date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS last_hire_date
FROM 
    employees;

이 쿼리는 각 부서별로 가장 먼저 입사한 직원과 가장 늦게 입사한 직원의 이름을 반환합니다.

고객 트랜잭션의 첫 번째 및 마지막 구매 금액 가져오기

고객의 트랜잭션 데이터에서 각 고객의 첫 번째와 마지막 구매 금액을 가져오는 쿼리입니다.

SELECT 
    customer_id,
    FIRST_VALUE(amount) OVER (PARTITION BY customer_id ORDER BY transaction_date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS first_purchase_amount,
    LAST_VALUE(amount) OVER (PARTITION BY customer_id ORDER BY transaction_date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS last_purchase_amount
FROM 
    transactions;

이 쿼리는 각 고객의 첫 번째와 마지막 구매 금액을 반환합니다.

윈도우 함수로서의 사용

FIRST_VALUE와 LAST_VALUE 함수는 윈도우 함수로 사용할 수 있으며, 이를 통해 데이터를 더욱 유연하게 분석하고 조작할 수 있습니다. 윈도우 함수로 사용할 경우 각 행에 대해 첫 번째와 마지막 값을 계산하여 결과로 반환합니다.

윈도우 함수의 기본 개념

윈도우 함수는 쿼리의 결과 집합에 대해 계산을 수행하며, 특정 윈도우 내의 행에 적용됩니다. 윈도우는 PARTITION BY 구문과 ORDER BY 구문을 사용하여 정의됩니다.

기본적인 문법

SELECT 
    column1,
    FIRST_VALUE(column1) OVER (PARTITION BY column2 ORDER BY column3 ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS first_value,
    LAST_VALUE(column1) OVER (PARTITION BY column2 ORDER BY column3 ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS last_value
FROM 
    table_name;

이 문법에서는 column2로 데이터를 파티션을 나누고, column3로 정렬한 후 첫 번째와 마지막 column1 값을 가져옵니다.

사용 예시: 판매 데이터의 윈도우 함수

예를 들어, 각 매장의 판매 데이터에서 윈도우 함수를 사용하여 첫 번째와 마지막 판매일을 가져오려면, 아래의 쿼리를 사용할 수 있습니다.

SELECT 
    store_id,
    product_id,
    sale_date,
    FIRST_VALUE(sale_date) OVER (PARTITION BY store_id ORDER BY sale_date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS first_sale_date,
    LAST_VALUE(sale_date) OVER (PARTITION BY store_id ORDER BY sale_date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS last_sale_date
FROM 
    sales;

이 쿼리는 각 매장의 판매 데이터에서 첫 번째와 마지막 판매일을 반환합니다.

사용 예시: 학생 성적 데이터

학생 성적 데이터에서 각 학생의 첫 번째와 마지막 시험 결과를 가져오는 경우, 다음과 같은 쿼리를 사용할 수 있습니다.

SELECT 
    student_id,
    exam_date,
    score,
    FIRST_VALUE(score) OVER (PARTITION BY student_id ORDER BY exam_date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS first_exam_score,
    LAST_VALUE(score) OVER (PARTITION BY student_id ORDER BY exam_date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS last_exam_score
FROM 
    exam_results;

이 쿼리는 각 학생의 첫 번째와 마지막 시험 점수를 반환합니다.

파티션 분할과 ORDER BY의 사용

FIRST_VALUE와 LAST_VALUE 함수의 강력한 기능 중 하나는 데이터를 파티션으로 분할하여 정렬할 수 있다는 점입니다. 이를 통해 특정 그룹 내에서 첫 번째 및 마지막 값을 가져올 수 있습니다.

파티션 분할의 기본 개념

파티션 분할은 데이터를 특정 기준으로 그룹화하는 것을 의미합니다. 이를 통해 각 그룹 내에서 독립적으로 계산을 수행할 수 있습니다. PARTITION BY 구문을 사용하여 파티션 분할을 수행합니다.

ORDER BY의 기본 개념

ORDER BY 구문을 사용하여 각 파티션 내의 행을 특정 순서로 정렬합니다. 이를 통해 첫 번째와 마지막 값을 정확하게 특定할 수 있습니다.

파티션 분할 및 ORDER BY의 사용 예시

다음은 판매 데이터에서 각 매장의 첫 번째 및 마지막 판매일을 가져오는 쿼리입니다.

SELECT 
    store_id,
    product_id,
    sale_date,
    FIRST_VALUE(sale_date) OVER (PARTITION BY store_id, product_id ORDER BY sale_date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS first_sale_date,
    LAST_VALUE(sale_date) OVER (PARTITION BY store_id, product_id ORDER BY sale_date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS last_sale_date
FROM 
    sales;

이 쿼리는 각 매장 및 각 상품의 첫 번째와 마지막 판매일을 반환합니다.

학생 데이터의 응용 예시

학생 성적 데이터에서 각 학생별 첫 번째와 마지막 시험 결과를 가져오는 경우, 다음과 같이 합니다.

SELECT 
    student_id,
    exam_date,
    score,
    FIRST_VALUE(score) OVER (PARTITION BY student_id ORDER BY exam_date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS first_exam_score,
    LAST_VALUE(score) OVER (PARTITION BY student_id ORDER BY exam_date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS last_exam_score
FROM 
    exam_results;

이 쿼리는 각 학생의 첫 번째와 마지막 시험 점수를 반환합니다.

트랜잭션 데이터의 응용 예시

고객의 트랜잭션 데이터에서 각 고객별 첫 번째와 마지막 구매 금액을 가져오는 경우, 다음과 같이 합니다.

SELECT 
    customer_id,
    transaction_date,
    amount,
    FIRST_VALUE(amount) OVER (PARTITION BY customer_id ORDER BY transaction_date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS first_purchase_amount,
    LAST_VALUE(amount) OVER (PARTITION BY customer_id ORDER BY transaction_date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS last_purchase_amount
FROM 
    transactions;

이 쿼리는 각 고객의 첫 번째와 마지막 구매 금액을 반환합니다.

연습 문제 및 해답

이 글에서 배운 FIRST_VALUE 함수와 LAST_VALUE 함수에 대한 이해를 높이기 위해, 몇 가지 연습 문제를 준비했습니다. 각 문제에 대해 해답 예시도 제시하므로 실제로 시도해 보세요.

문제 1: 각 부문의 첫 번째 및 마지막 입사일을 가져오기

다음 employees 테이블에서 각 부문별 첫 번째 및 마지막 입사일을 가져오는 쿼리를 작성하세요.

-- employees 테이블
+---------+------------+------------+
| emp_id  | department | hire_date  |
+---------+------------+------------+
| 1       | Sales      | 2020-01-01 |
| 2       | Sales      | 2021-05-10 |
| 3       | HR         | 2019-03-15 |
| 4       | HR         | 2020-07-23 |
| 5       | IT         | 2021-01-05 |
+---------+------------+------------+

해답 예시

SELECT 
    department,
    FIRST_VALUE(hire_date) OVER (PARTITION BY department ORDER BY hire_date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS first_hire_date,
    LAST_VALUE(hire_date) OVER (PARTITION BY department ORDER BY hire_date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS last_hire_date
FROM 
    employees;

문제 2: 각 상품의 첫 번째 및 마지막 판매일을 가져오기

다음 sales 테이블에서 각 상품의 첫 번째 및 마지막 판매일을 가져오는 쿼리를 작성하세요.

-- sales 테이블
+---------+------------+------------+
| sale_id | product_id | sale_date  |
+---------+------------+------------+
| 1       | 101        | 2022-01-01 |
| 2       | 102        | 2022-01-05 |
| 3       | 101        | 2022-02-01 |
| 4       | 103        | 2022-01-10 |
| 5       | 102        | 2022-03-01 |
+---------+------------+------------+

해답 예시

SELECT 
    product_id,
    FIRST_VALUE(sale_date) OVER (PARTITION BY product_id ORDER BY sale_date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS first_sale_date,
    LAST_VALUE(sale_date) OVER (PARTITION BY product_id ORDER BY sale_date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS last_sale_date
FROM 
    sales;

문제 3: 각 고객의 첫 번째 및 마지막 구매 금액을 가져오기

다음 transactions 테이블에서 각 고객의 첫 번째 및 마지막 구매 금액을 가져오는 쿼리를 작성하세요.

-- transactions 테이블
+-------------+----------+--------+--------------+
| transaction_id | customer_id | amount | transaction_date |
+-------------+----------+--------+--------------+
| 1           | 1001     | 200    | 2023-01-01   |
| 2           | 1002     | 150    | 2023-01-05   |
| 3           | 1001     | 300    | 2023-02-01   |
| 4           | 1003     | 250    | 2023-01-10   |
| 5           | 1002     | 400    | 2023-03-01   |
+-------------+----------+--------+--------------+

해답 예시

SELECT 
    customer_id,
    FIRST_VALUE(amount) OVER (PARTITION BY customer_id ORDER BY transaction_date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS first_purchase_amount,
    LAST_VALUE(amount) OVER (PARTITION BY customer_id ORDER BY transaction_date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS last_purchase_amount
FROM 
    transactions;

정리

이 글에서는 SQL의 FIRST_VALUE 함수와 LAST_VALUE 함수를 사용하여 데이터 세트의 첫 번째 및 마지막 값을 가져오는 방법을 배웠습니다. 기본적인 사용법부터 응용 예시, 윈도우 함수로서의 사용, 더 나아가 파티션 분할과 ORDER BY의 사용법까지 자세히 설명했습니다. 이러한 함수들을 활용하여 데이터 분석과 보고서 작성이 더욱 효율적으로 이루어질 수 있습니다. 실제 데이터 세트에서 이러한 기술들을 꼭 시도해 보세요.