Menu

PostgreSQL DISTINCT ON: 그룹별 첫 행 하나만 가져오기

SELECT DISTINCT ON (key) ... ORDER BY key, sort는 고객별 최신 주문처럼 각 그룹의 첫 행을 남깁니다. PostgreSQL에만 있는 기능입니다.

이 페이지에는 실행 가능한 에디터가 있습니다. 편집하고 실행하면 결과를 바로 볼 수 있습니다.

SELECT DISTINCT ON (expr) ...은 expr의 고유한 값마다 한 행을 돌려줍니다. ORDER BY 순서로 그 그룹의 첫 번째 행입니다. 먼저 그룹 키로 정렬하고, 그다음 무엇이 "첫 번째"인지 정하는 컬럼으로 정렬하세요. 고객별 최신 주문이라면 ORDER BY customer, created_at DESC입니다.

세 행이 나옵니다. asha는 주문 2, ben은 4, chen은 5로, 각 고객의 가장 최근 주문입니다. DISTINCT ON은 PostgreSQL 확장입니다. 다른 데이터베이스에서는 아래에 나오는 윈도우 함수 형태가 필요합니다.

남길 행을 정하는 ORDER BY

PostgreSQL은 행을 정렬한 뒤, DISTINCT ON 값이 바뀔 때마다 첫 번째 행을 남깁니다. 그래서 다음과 같습니다.

  • ORDER BY는 DISTINCT ON 식으로 시작해야 합니다. 그렇지 않으면 PostgreSQL이 "SELECT DISTINCT ON expressions must match initial ORDER BY expressions" 오류를 냅니다.
  • 그 식 뒤에 ORDER BY가 더 없으면 어떤 행이 남을지 예측할 수 없고, 실행할 때마다 달라질 수 있습니다.

created_at DESC를 total DESC로 바꾸면 같은 쿼리가 고객별 가장 큰 주문을 돌려줍니다.

이제 asha의 행은 주문 1(40), ben의 행은 주문 3(60)입니다.

동점은 의도적으로 정리하기

정렬 컬럼 값이 같은 행이 둘이면 어느 쪽이든 남을 수 있습니다. 기본 키처럼 고유한 컬럼을 마지막 정렬 키로 추가하면 결과가 항상 같아집니다.

행 2와 3은 둘 다 95점입니다. id를 마지막 키로 두면 행 2가 남습니다.

여러 컬럼으로 그룹 나누기

식을 여러 개 나열하면 조합마다 한 행을 얻습니다.

센서와 종류의 쌍마다 최신 측정값이 남습니다. s1 humidity 40, s1 temp 22, s2 temp 19입니다.

ROW_NUMBER로 쓰는 이식 가능한 방법

ROW_NUMBER()는 파티션 안의 행에 번호를 매깁니다. 1번만 남기면 DISTINCT ON과 같은 결과가 나오며, MySQL 8, SQL Server, SQLite에서도 동작합니다.

SELECT customer, id, total, created_at
FROM (
    SELECT o.*,
           ROW_NUMBER() OVER (PARTITION BY customer ORDER BY created_at DESC) AS rn
    FROM orders o
) ranked
WHERE rn = 1;

그룹별 상위 n개(rn <= 3)가 필요하거나 쿼리를 다른 데이터베이스에서 실행해야 할 때 이 형태를 쓰세요. PARTITION BY는 윈도우 함수 페이지에서 설명합니다.

빠르게 만들기

DISTINCT ON은 입력을 정렬하므로, 큰 테이블에서는 ORDER BY와 맞는 인덱스를 만드세요.

CREATE INDEX orders_customer_latest ON orders (customer, created_at DESC);

그러면 PostgreSQL은 정렬하지 않고 순서대로 행을 읽을 수 있습니다. 그룹이 적고 그룹마다 행이 많다면, 같은 인덱스에서 고객마다 LIMIT 1을 가져오는 LATERAL 조인이 보통 더 적은 행을 읽습니다. LATERAL은 조인 페이지에서 다룹니다. 실제 데이터에서 EXPLAIN ANALYZE로 두 방법을 비교해 보세요.

자주 묻는 질문

DISTINCT와 DISTINCT ON은 무엇이 다른가요?

DISTINCT는 선택한 모든 컬럼이 똑같은 행을 제거합니다. DISTINCT ON (a)는 a의 값마다 한 행을 남기며, 나머지 컬럼은 그 그룹에서 정렬상 가장 앞에 오는 행에서 가져옵니다.

DISTINCT ON은 표준 SQL인가요?

아니요. PostgreSQL 확장이라 MySQL이나 SQL Server에서는 동작하지 않습니다. 이식 가능한 방법은 서브쿼리에서 ROW_NUMBER() OVER (PARTITION BY ... ORDER BY ...)를 쓰고 행 번호 1만 거르는 것입니다.

"SELECT DISTINCT ON expressions must match initial ORDER BY expressions" 오류는 왜 나나요?

ORDER BY는 DISTINCT ON과 같은 식으로, 같은 순서로 시작해야 합니다. ORDER BY created_at DESC만 쓰지 말고 DISTINCT ON (customer) ... ORDER BY customer, created_at DESC처럼 쓰세요.

DISTINCT ON으로 그룹별 상위 3개 행을 가져올 수 있나요?

아니요. 그룹마다 정확히 한 행만 돌려줍니다. 상위 n개가 필요하면 ROW_NUMBER()로 행에 번호를 매기고 rn <= 3을 남기거나, LIMIT 3을 쓴 LATERAL 서브쿼리를 쓰세요.

Coddy 프로그래밍 언어 일러스트

Coddy로 코딩 배우기

시작하기