PostgreSQL: DISTINCT ON

※ PostgreSQL: DISTINCT ON.

 

안녕하세요. 듀스트림입니다.

 

오늘 포스팅은 PostgreSQL 고유 확장 기능인 DISTINCT ON에 대한 내용입니다.

 

알아두면 꽤 편합니다.

---

DISTINCT ON은 표준 ANSI SQL에 속하지 않으며, PostgreSQL 고유 확장 기능입니다.

표준 방식은 ROW_NUMBER()를 사용합니다.

---

표준 방식

SELECT user_id, event_time, status
FROM (
  SELECT
    user_id,
    event_time,
    status,
    ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY event_time DESC) AS rn
  FROM user_events
) t
WHERE rn = 1;

---

DISTINCT ON 사용

SELECT DISTINCT ON (user_id) user_id, event_time, status
FROM user_events
ORDER BY user_id, event_time DESC;

 

훨씬 간결하죠? 실행계획을 봐도 노드 뎁스가 표준보다 하나 작아서 가독성이 좋습니다.

 

SELECT

---

+ 단일 컬럼만 가능하냐는 질문을 받아서 추가 내용 작성합니다.

• DISTINCT ON은 1개의 컬럼만 가능한 게 아니라, 하나의 그룹 키 집합을 여러 컬럼으로 만들 수 있습니다.
• ROW_NUMBER()의 PARTITION BY에 배열된 여러 컬럼도 당연히 쓸 수 있고 콤마로 나열하면 됩니다.

문법은:

SELECT DISTINCT ON (col1, col2, col3, ...)
       ...
FROM   ...
ORDER BY col1, col2, col3, ... , <tie-breakers...>;

• DISTINCT ON (col1, col2) → (col1, col2) 조합이 같은 것들 중 첫 번째 행만 선택
• 첫 번째가 뭔지는 ORDER BY로 결정
  • ORDER BY의 맨 앞 컬럼들은 반드시 DISTINCT ON에 있는 컬럼들과 같은 순서여야 함
  • 그 뒤에 정렬 컬럼을 붙여서 "대표로 뽑힐 행"을 결정

예시:

-- (user_id, order_date) 기준으로 한 행만 선택하고 싶다.
-- 같은 (user_id, order_date) 안에서 amount가 가장 큰 행을 뽑고 싶다면:
SELECT DISTINCT ON (user_id, order_date)
       user_id,
       order_date,
       amount
FROM   orders
ORDER BY user_id, order_date, amount DESC;

• (user_id, order_date)가 같은 행들 중에서 amount DESC 기준으로 가장 큰 행만 남김.

정리
• DISTINCT ON (컬럼1, 컬럼2, ...) 형태로 여러 컬럼 지정 가능
• ORDER BY는 DISTINCT ON 컬럼들 + 대표 행을 고를 정렬 기준까지 함께 적어야 함.

 

ROW_NUMBER()로 작성하면:

SELECT *
FROM (
    SELECT
        user_id,
        order_date,
        amount,
        ROW_NUMBER() OVER (
            PARTITION BY user_id, order_date
            ORDER BY amount DESC
        ) AS rn
    FROM orders
) t
WHERE rn = 1;

• (user_id, order_date) 별로 그룹 나누고, amount DESC 큰 것부터 rn = 1 부여 → 대표 행 선택

---

비교 표

방식	그룹 키 정의	대표 행 선택 기준	특징
DISTINCT ON	DISTINCT ON (c1, c2)	ORDER BY ...	PostgreSQL 전용, 문법 간단
ROW_NUMBER()	PARTITION BY c1, c2	ORDER BY ...	표준 SQL, 다른 DB에도 사용 가능

---

오늘은 여기까지~