본문 바로가기
빌드노트 검색

빌드노트

PostgreSQL DISTINCT ON으로 최신 행을 골랐는데 과거 행이 나온다

DISTINCT ON은 그룹마다 첫 행을 남기므로 최신 시각과 동률 ID 규칙을 ORDER BY에 적어야 한다. PostgreSQL 18.6에서 시각·ID 정렬 방향, 잘못된 선두 정렬의 42P10 오류, 대표행 선택 뒤 표시 순서를 실제 비교했다.

PostgreSQL에서 DISTINCT ON (group_key)로 그룹마다 한 행을 조회했는데 과거 행이 남는다면 ORDER BY를 확인한다. DISTINCT ON은 최신 시각을 찾아 주는 기능이 아니라, 같은 그룹에서 첫 행만 남기는 기능이다. 최신 시각을 먼저 놓고, 시각까지 같을 때 어느 행을 선택할지도 정렬에 적어야 한다.

그룹별 최신 행을 고르고 같은 시각이면 큰 ID를 남기려는 경우, 정렬은 ORDER BY group_key, occurred_at DESC, id DESC가 된다. PostgreSQL 18의 DISTINCT ON 문서 (새 탭에서 열림)는 그룹 기준이 ORDER BY의 맨 왼쪽 정렬식과 맞아야 하며, 그 뒤의 정렬식으로 그룹 안의 우선순위를 정한다고 설명한다.

같은 시각의 두 행 중 ID가 큰 행을 남긴다

다음은 별도 임시 컨테이너의 PostgreSQL 18.6에서 실행한 합성 예제다. PostgreSQL 서버에 연결된 psql이나 SQL 콘솔에서 그대로 실행할 수 있다. 테이블을 만들거나 데이터를 저장하지 않는다. events는 이 쿼리 안에서만 사용하는 여섯 행의 이름이다.

alpha에는 10시인 행이 두 개, beta에는 11시인 행이 두 개 있다. 여기서는 시각이 가장 늦은 행을 선택하고, 같은 시각이면 ID가 큰 행을 선택한다고 정한다. 큰 ID가 실제로 더 늦게 발생했다는 뜻은 아니다. 동률에서 한 행을 고르기 위해 정한 규칙이다. 입력 시각에는 NULL이 없고 ID는 그룹 안에서 유일하다.

WITH events(group_key, id, occurred_at) AS (
  VALUES
    ('alpha', 11, TIMESTAMP '2026-10-20 09:00:00'),
    ('alpha', 12, TIMESTAMP '2026-10-20 10:00:00'),
    ('alpha', 13, TIMESTAMP '2026-10-20 10:00:00'),
    ('beta', 21, TIMESTAMP '2026-10-20 08:00:00'),
    ('beta', 22, TIMESTAMP '2026-10-20 11:00:00'),
    ('beta', 23, TIMESTAMP '2026-10-20 11:00:00')
)
SELECT DISTINCT ON (group_key) group_key, id, occurred_at
FROM events
ORDER BY group_key, occurred_at DESC, id DESC;

실제 결과를 CSV로 기록하면 다음과 같다. SQL 콘솔의 표시 형식이 달라도 선택된 값은 비교할 수 있다.

group_key,id,occurred_at
alpha,13,2026-10-20 10:00:00
beta,23,2026-10-20 11:00:00

첫 정렬식인 group_key는 DISTINCT ON의 그룹 기준과 맞춘다. 같은 그룹 안에서는 occurred_at DESC로 최신 시각을 앞에 둔다. 시각까지 같은 행에는 다음 정렬식인 id DESC가 적용된다. ORDER BY 문서 (새 탭에서 열림)는 앞의 정렬식에서 같은 값인 행들을 뒤의 정렬식으로 비교한다고 설명한다. 끝의 DESC 하나가 앞의 모든 열에도 적용되는 것은 아니다.

시각 방향과 동률 규칙을 각각 바꿔 본다

앞 쿼리의 VALUES와 SELECT는 그대로 두고 마지막 ORDER BY 줄만 다음 조건으로 바꿔 각각 실행했다.

  • ORDER BY group_key, occurred_at ASC, id DESC;에서는 alpha의 11과 beta의 21이 선택됐다. 시각이 이른 행을 앞에 놓았기 때문이다. 정렬 방향을 생략해도 기본값은 ASC이므로 최신 행을 고르는 식이 되지 않는다.
  • ORDER BY group_key, occurred_at DESC, id ASC;에서는 12와 22가 선택됐다. 최신 시각은 맞지만, 같은 시각에서 작은 ID를 남기는 규칙으로 바뀌었다.
  • ORDER BY group_key, occurred_at DESC;에서도 이번 실행은 12와 22를 반환했다. 다만 이 쿼리는 동률인 ID 중 어느 쪽을 남길지 지정하지 않았다. 이 숫자를 기대 결과로 고정하면 안 된다.

완전한 정렬을 쓴 첫 쿼리는 VALUES의 여섯 행을 역순으로 배치해 다시 실행해도 13과 23을 반환했다. 이는 이 입력에서 확인한 결과다. 동률 기준을 생략한 쿼리나 ORDER BY가 없는 쿼리가 몇 번 같은 값을 반환하는 것은 선택 규칙의 보장이 아니다. 어느 행을 앞에 둘지 정하라는 판단은 공식 문서의 규칙에 근거한다.

최신 결과를 위에 표시하려다 선두 정렬을 바꾸면 오류가 난다

beta의 11시 행을 alpha의 10시 행보다 위에 표시하려고 첫 쿼리의 마지막 줄을 다음처럼 바꾸면 조회 자체가 실패한다.

ORDER BY occurred_at DESC, group_key, id DESC;

이 변형은 실행 시 SQLSTATE 42P10으로 실패했다. 핵심 오류 메시지는 다음과 같았다.

SELECT DISTINCT ON expressions must match initial ORDER BY expressions

DISTINCT ON (group_key)인데 정렬의 맨 앞을 occurred_at으로 바꿨기 때문이다. 처음 쿼리처럼 group_key를 맨 앞에 두면 그룹별 선택은 맞지만, 결과 목록은 alpha 다음 beta 순서다. 그룹별 대표행 선택과 선택된 결과의 표시 순서가 다르면, 대표행을 고른 조회를 하위쿼리로 감싸서 바깥에서 다시 정렬한다.

처음 예제의 WITH events(...) AS (...) 부분은 그대로 두고, 그 아래 SELECT 전체를 다음으로 교체한다.

SELECT group_key, id, occurred_at
FROM (
  SELECT DISTINCT ON (group_key) group_key, id, occurred_at
  FROM events
  ORDER BY group_key, occurred_at DESC, id DESC
) AS chosen
ORDER BY occurred_at DESC, group_key;

실행 결과는 beta의 23·11시가 먼저, alpha의 13·10시가 나중이었다. 안쪽 정렬이 고르는 행은 그대로이고 바깥 정렬이 두 결과의 표시 순서를 바꾼다.

자기 조회에서도 먼저 그룹 기준, 최신성을 나타내는 시각, 동률을 끝내는 키를 확인한다. 마지막 키까지 같은 후보가 여러 개라면 아직 한 행을 고르는 규칙이 덜 정해진 것이다. 같은 정렬을 넣어도 예상과 다르면 실제 필터를 통과한 후보들의 시각과 ID를 함께 조회해 비교한다.

이번 비교는 NULL 없는 timestamp 값과 그룹 안에서 유일한 정수 ID를 사용한 단일 연결 실험이다. NULL 시각의 우선순위, 시간대 변환, 동시 데이터 변경과 실제 테이블의 인덱스 성능은 검증하지 않았다.

← 전체 글 보기