그룹별 최신 1건 뽑기: SQL 네 가지 방식 비교
고객 상담 화면을 손보던 때였다. 상담원이 고객 목록을 열면 고객마다 가장 최근 주문 한 건이 옆에 떠야 했다. 주문 테이블에는 고객 한 명당 많게는 수천 건이 쌓여 있었는데, 필요한 건 그중 맨 위 한 줄뿐이었다. 처음엔 GROUP BY user_id에 MAX(created_at)를 쓰면 끝날 줄 알았다. 날짜는 나왔다. 그런데 그 날짜에 들어온 주문의 번호와 금액을 같이 꺼낼 방법이 없었다.
이런 문제를 greatest-n-per-group이라고 부른다. 그룹마다 어떤 기준으로 가장 앞선 행 n개를 고르는 문제다. 사용자별 마지막 주문, 게시판별 최신 글, 센서별 마지막 측정값이 모두 같은 모양이다. 푸는 방법은 여럿이다. 그런데 DB마다 쓸 수 있는 문법이 다르고, 인덱스가 있느냐 없느냐에 따라 속도가 크게 벌어진다.
아래에서는 상관 서브쿼리, ROW_NUMBER, DISTINCT ON, LATERAL 네 가지를 PostgreSQL, MySQL, SQLite 기준으로 나란히 놓고 본다.
예제 테이블과 인덱스
테이블은 두 개만 둔다. 사용자 목록과 주문이다.
CREATE TABLE users (
id INTEGER PRIMARY KEY,
name TEXT NOT NULL
);
CREATE TABLE orders (
id INTEGER PRIMARY KEY,
user_id INTEGER NOT NULL REFERENCES users(id),
created_at TIMESTAMP NOT NULL,
amount INTEGER NOT NULL
);
CREATE INDEX idx_orders_user_latest
ON orders (user_id, created_at DESC, id DESC);인덱스는 아래 네 쿼리 모두에 영향을 준다. user_id로 묶고 그 안에서 최신순으로 줄을 세워 두면 「이 사용자의 첫 번째 줄」을 바로 집어 올 수 있다. 끝에 id를 붙인 데는 이유가 있다. 같은 시각에 주문 두 건이 들어오면 created_at만으로는 어느 쪽이 최신인지 정할 수 없다. 기본 키를 마지막 기준으로 두면 결과가 매번 같게 나온다.
GROUP BY만으로는 왜 안 되나
처음 막혔던 지점부터 짚고 간다. SELECT user_id, MAX(created_at), amount FROM orders GROUP BY user_id처럼 집계하지 않은 열을 섞으면 DB마다 반응이 다르다. PostgreSQL은 그 자리에서 오류를 낸다. MySQL은 5.7부터 ONLY_FULL_GROUP_BY가 기본으로 켜져 있어서 역시 거부한다. 문제는 이 모드를 꺼 둔 서버다. 그룹 안의 아무 행에서나 값을 가져온다.
예전 회사의 오래된 정산 서버가 바로 그랬다. 날짜는 최신 주문 것인데 금액은 다른 주문 것이 섞여 나왔다. 오류 메시지가 하나도 없으니 월말 대사에서 숫자가 안 맞을 때까지 아무도 눈치채지 못했다.
SQLite는 조금 특이하다. 집계 함수가 MAX()나 MIN() 하나뿐이면 집계하지 않은 열을 그 최댓값을 가진 행에서 가져온다고 문서에 적혀 있다. 당장은 편하다. 하지만 동점이면 어느 행이 올지 정해져 있지 않고, 다른 DB로 옮기는 순간 깨진다. 그래서 이 동작에는 기대지 않는다.
상관 서브쿼리: 어디서나 돌지만 동점에 약하다
가장 먼저 떠올리는 형태다. 세 DB 어디서나 돈다.
SELECT o.user_id, o.id, o.created_at, o.amount
FROM orders o
WHERE o.created_at = (
SELECT MAX(o2.created_at)
FROM orders o2
WHERE o2.user_id = o.user_id
);상담 화면에 처음 넣은 것도 이 쿼리였다. 배포하고 며칠 뒤 상담원 한 명이 어떤 고객 이름이 목록에 두 번 나온다고 알려 왔다. 들여다보니 일괄 등록 기능으로 같은 시각에 주문 두 건이 들어간 고객이었다. 최댓값과 같은 행이 둘이니 둘 다 나온 것이다. 쿼리 문법은 하나도 틀리지 않았는데 결과가 틀렸다는 점이 허탈했다.
그래서 지금은 값이 아니라 기본 키를 비교하는 형태로 쓴다. 바깥은 주문이 아니라 사용자 테이블에서 시작한다.
SELECT u.id, u.name, o.id AS order_id, o.created_at, o.amount
FROM users u
JOIN orders o ON o.id = (
SELECT o2.id
FROM orders o2
WHERE o2.user_id = u.id
ORDER BY o2.created_at DESC, o2.id DESC
LIMIT 1
);사용자 한 명마다 서브쿼리가 한 번씩 돈다. 횟수만 보면 느릴 것 같지만 앞의 인덱스가 있으면 한 번이 인덱스 탐색 한 번으로 끝난다. 뒤에 나올 LATERAL과 하는 일이 사실상 같다. 인덱스가 없으면 사용자 수만큼 주문 테이블을 훑게 되니 조심해야 한다.
ROW_NUMBER: 표준이고 옮기기 쉽다
윈도 함수로 그룹마다 번호를 매기고 1번만 남긴다.
SELECT user_id, id, created_at, amount
FROM (
SELECT o.*,
ROW_NUMBER() OVER (
PARTITION BY user_id
ORDER BY created_at DESC, id DESC
) AS rn
FROM orders o
) t
WHERE rn = 1;표준 SQL이라 DB를 옮겨도 거의 그대로 쓸 수 있다. MySQL은 8.0부터, SQLite는 3.25.0(2018년 9월 15일)부터 윈도 함수를 지원한다. 개발 환경에서 잘 돌던 이 쿼리를 통계용 MySQL 서버에 올렸다가 문법 오류를 받은 적이 있다. 그 서버만 5.7에 머물러 있었다. 잠깐 당황한 뒤로는 새 문법을 쓰기 전에 SELECT VERSION();부터 확인한다.
MySQL 공식 매뉴얼의 그룹별 최댓값 예제는 RANK()를 쓴다. RANK()는 동점에 같은 순위를 주므로 1위가 둘이면 둘 다 남는다. 동점을 모두 보고 싶을 때는 이쪽이 맞지만, 딱 한 건이 필요하면 ROW_NUMBER()를 쓴다. rn <= 3으로 바꾸면 최근 3건으로 바로 넓힐 수 있다는 것도 장점이다.
성능 면에서는 바깥 WHERE rn = 1로 거르기 전에 모든 행에 번호를 매긴다. 사용자당 주문이 몇 건뿐이면 문제가 없다. 수천 건씩 쌓여 있으면 읽은 행 대부분을 버리는 셈이 된다.
DISTINCT ON: PostgreSQL에서 가장 짧은 답
PostgreSQL에만 있는 문법이다.
SELECT DISTINCT ON (user_id) user_id, id, created_at, amount
FROM orders
ORDER BY user_id, created_at DESC, id DESC;공식 문서의 설명은 간단하다. DISTINCT ON은 지정한 식의 값이 같은 행 묶음에서 첫 행만 남긴다. 그 식은 ORDER BY의 맨 왼쪽 식과 일치해야 한다. 문서 예제도 지역별 최신 날씨 보고를 뽑는 이 문제 그대로다. 문서에는 ORDER BY로 시간 역순을 정하지 않으면 예측할 수 없는 시점의 행이 나온다는 경고도 붙어 있다. 여기서도 동점 기준으로 id DESC를 붙였다.
성격은 ROW_NUMBER와 비슷하다. 정렬된 행을 처음부터 끝까지 지나가며 그룹이 바뀔 때마다 첫 행을 고른다. 인덱스 순서가 ORDER BY와 맞으면 정렬 단계를 건너뛰고 인덱스를 따라 읽는다. 그래도 행 전체를 한 번은 지난다는 점은 같다.
LATERAL: 그룹은 적고 행이 많을 때
LATERAL을 붙이면 FROM 절 서브쿼리가 앞에 나온 테이블의 열을 참조할 수 있다. 사용자 한 명을 꺼낼 때마다 그 사람의 주문을 최신순으로 한 줄만 집어 온다.
SELECT u.id, u.name, o.id AS order_id, o.created_at, o.amount
FROM users u
LEFT JOIN LATERAL (
SELECT id, created_at, amount
FROM orders
WHERE orders.user_id = u.id
ORDER BY created_at DESC, id DESC
LIMIT 1
) AS o ON TRUE;LEFT JOIN과 ON TRUE 조합이라 주문이 한 건도 없는 사용자도 결과에 남는다. 이 쿼리는 PostgreSQL과 MySQL 8.0.14 이상에서 그대로 돈다. SQLite에는 LATERAL이 없어서 앞에서 본 기본 키 비교형 상관 서브쿼리로 대신한다.
사용자는 몇천 명이고 주문은 수백만 건인 테이블에서 ROW_NUMBER 버전이 몇 초씩 걸린 적이 있다. EXPLAIN ANALYZE를 찍어 보니 주문 테이블 전체를 읽고 있었다. LATERAL로 바꾸자 사용자마다 인덱스를 한 번 내려가 한 줄 읽고 끝났고, 실행 시간이 체감할 수 없을 만큼 줄었다. 그 순간은 솔직히 조금 통쾌했다. 반대로 사용자는 많은데 한 사람당 주문이 한두 건이면 탐색 횟수만 늘어나서 이점이 사라진다.
몇 건을 가져올지 바꾸기도 쉽다. 상담 화면은 몇 달 뒤 최근 주문 세 건을 펼쳐 보이도록 요구가 바뀌었는데, 고친 곳은 LIMIT 1의 숫자 하나뿐이었다.
| 방식 | PostgreSQL | MySQL | SQLite | 동점 처리 |
|---|---|---|---|---|
| 상관 서브쿼리(MAX) | 가능 | 가능 | 가능 | 동점 행이 모두 나옴 |
| 상관 서브쿼리(id 비교) | 가능 | 가능 | 가능 | 한 건 |
| ROW_NUMBER | 가능 | 8.0부터 | 3.25.0부터 | 한 건 |
| DISTINCT ON | 가능 | 없음 | 없음 | 한 건 |
| LATERAL | 가능 | 8.0.14부터 | 없음 | 한 건 |
바꾼 쿼리를 검증하는 법
쿼리 모양을 바꿀 때마다 돌려 보는 확인용 쿼리가 있다. 첫째는 사용자마다 결과가 정말 한 줄인지 보는 것이다.
SELECT user_id, COUNT(*)
FROM (/* 새 쿼리 */) t
GROUP BY user_id
HAVING COUNT(*) > 1;한 줄이라도 나오면 동점 기준이 빠진 것이다. 둘째는 옛 쿼리와 새 쿼리의 결과를 PostgreSQL의 EXCEPT로 맞대 보는 것이다.
지난봄 월간 리포트 쿼리를 ROW_NUMBER에서 LATERAL로 옮길 때 이 비교에서 몇십 줄이 어긋났다. 원인은 주문이 한 번도 없는 사용자였다. ROW_NUMBER 버전은 주문 테이블에서 출발하니 그 사람들이 아예 없었고, LEFT JOIN LATERAL 버전에는 주문 칸이 빈 채로 남아 있었다. 리포트에서는 빠지는 게 맞았기에 LEFT JOIN을 그냥 JOIN으로 바꿨다. 결과가 다르다고 해서 늘 새 쿼리가 틀린 건 아니다. 이 비교를 하다 보면 원래 무엇을 원했는지 다시 묻게 된다.
지금 내가 고르는 방식
먼저 그룹 하나에 행이 몇 개씩 붙어 있는지 본다. 사용자 테이블이 따로 있고 한 사람당 주문이 수백, 수천 건이면 LATERAL을 쓴다. SQLite라면 기본 키를 비교하는 상관 서브쿼리를 쓴다. 그룹마다 행이 몇 개 안 되거나 어차피 테이블 전체를 훑는 리포트 쿼리라면 ROW_NUMBER를 쓰고, PostgreSQL에서 짧게 쓰고 싶을 때는 DISTINCT ON을 쓴다.
어느 쪽이든 지키는 습관이 두 가지 있다. 정렬 기준 끝에는 꼭 기본 키를 붙인다. 상담 화면에서 이름이 두 번 뜬 뒤로 생긴 버릇이다. 그리고 쿼리 모양을 고르기 전에 (그룹 키, 정렬 키) 인덱스부터 만든다. 같은 LATERAL이라도 인덱스가 없으면 그냥 느린 반복문일 뿐이다. 문법이 달라도 결국 승부는 인덱스에서 난다. 쿼리를 바꾸고 나면 실행 계획을 한 번 찍어 보고, 전체를 읽는지 그룹마다 한 줄만 읽는지 확인한 다음에 배포한다. MySQL에서도 8.0.18부터는 EXPLAIN ANALYZE로 실제 읽은 행 수를 볼 수 있다.