[SQL - Frequently Used Code] 그룹별로 첫번째/마지막 값 추출

2025.02.24 - [프로그래밍/SQL, Hive, SAS 관련 정보] - [SQL] Table of Contents

---

제가 연구실에서나 현업에서 SQL를 사용하면서 많이 사용했던 기능입니다. 사실 Window함수의 일종인데, SAS의 First/Last기능이랑 헷갈리기도 하고 자주 구글링하게 되는 코드인 것 같아서 따로 정리해봅니다. 

https://trillionver2.tistory.com/entry/SQL-%EA%B8%B0%EC%B4%88-Window%ED%95%A8%EC%88%98

[SQL 기초] Window함수

---

예제 데이터 생성

CREATE TABLE sales (
    id NUMBER,
    customer_name VARCHAR2(50),
    sales_date DATE,
    sales_amount NUMBER
);

INSERT INTO sales VALUES (1, 'Alice', TO_DATE('2023-01-01', 'YYYY-MM-DD'), 100);
INSERT INTO sales VALUES (2, 'Bob', TO_DATE('2023-01-02', 'YYYY-MM-DD'), 200);
INSERT INTO sales VALUES (3, 'Alice', TO_DATE('2023-01-03', 'YYYY-MM-DD'), 150);
INSERT INTO sales VALUES (4, 'Bob', TO_DATE('2023-01-04', 'YYYY-MM-DD'), 250);
INSERT INTO sales VALUES (5, 'Alice', TO_DATE('2023-01-05', 'YYYY-MM-DD'), 300);
COMMIT;

---

일단 오늘 포스팅의 주요 대상인 Window 함수 First_value/Last_value를 사용하는 방식은 아래와 같습니다. 

SELECT 
    customer_name,
    sales_date,
    sales_amount,
    FIRST_VALUE(sales_date) OVER (PARTITION BY customer_name ORDER BY sales_date) AS first_purchase_date,
    LAST_VALUE(sales_date) OVER (
        PARTITION BY customer_name 
        ORDER BY sales_date 
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS last_purchase_date
FROM sales;

 

결과는 아래와 같이 나올 것입니다. 

Customer Name	Sales Date	Sales Amount	First Purchase Date	Last Purchase Date
Alice	2023-01-01	100	2023-01-01	2023-01-05
Alice	2023-01-03	150	2023-01-01	2023-01-05
Alice	2023-01-05	300	2023-01-01	2023-01-05
Bob	2023-01-02	200	2023-01-02	2023-01-04
Bob	2023-01-04	250	2023-01-02	2023-01-04

 

제가 어제 포스팅했던 Tableau의 Fixed/Exclude의 기능이랑 뭔가 비슷한것도 같고 그렇죠? 

 

보너스로 똑같은 기능 : "그룹별로 첫번째/마지막 값을 뽑아낸다" 을 구현할 수 있는 다른 방법을 몇 가지만 더 살펴보겠습니다. 

1) MIN/MAX 활용 : 만약 테이블에 이미 그룹별 '첫번째~마지막'을 판별할 수 있는 칼럼값이 이미 있다면 단순하게 MIN/MAX를 적용할 수 있습니다. 지금은 Sales Date가 그렇기 때문에 바로 MIN/MAX를 적용할 수 있습니다. 

SELECT 
    customer_name,
    MIN(sales_date) AS first_purchase_date,
    MAX(sales_date) AS last_purchase_date
FROM sales
GROUP BY customer_name;

 

2) KEEP, DENSE_RANK 활용 : KEEP은 특정 칼럼을 기준으로 정렬해서 최대/최소값을 갖는 행을 찾고, 찾아낸 행 안에 있는 다른 칼럼의 값을 활용하고자 할 때 사용합니다. 

SELECT 
    customer_name,
    MIN(sales_date) AS first_purchase_date,
    MAX(sales_date) AS last_purchase_date,
    MIN(sales_amount) KEEP (DENSE_RANK FIRST ORDER BY sales_date) AS first_sales_amount,
    MIN(sales_amount) KEEP (DENSE_RANK LAST ORDER BY sales_date) AS last_sales_amount
FROM sales
GROUP BY customer_name;

이 때 KEEP바로 앞에서 MIN과 같은 집계함수를 사용하는 것은 아래와 같이 여러행이 존재할 수도 있기 때문입니다. 

customer_name	sales_date	sales_amount
Alice	2023-01-01	100
Alice	2023-01-01	150

 

3) Sub-query활용 : 마지막으로 조금 복잡해지는 것 같긴하지만 가장 직관적인 방법. (연관)서브쿼리를 활용하는 방법입니다. 

SELECT 
    s1.customer_name,
    s1.sales_date AS first_purchase_date,
    s1.sales_amount AS first_sales_amount
FROM sales s1
WHERE s1.sales_date = (
    SELECT MIN(s2.sales_date)
    FROM sales s2
    WHERE s1.customer_name = s2.customer_name
);

 

연관서브 쿼리에 대한 내용은 아래 포스팅을 참조하세요.

2024.11.01 - [프로그래밍/SQL, Hive, SAS 관련 정보] - [SQL 기초] Sub-Query