사용자별 최신 주문 한 건씩 뽑으려고 서브쿼리를 겹치다가 윈도 함수로 정리

문제 발생

사용자마다 가장 최근 주문 한 건씩을 뽑으려는데, GROUP BY로는 원하는 결과가 나오지 않았습니다.

SELECT user_id, MAX(created_at), id, amount
FROM orders
GROUP BY user_id;

MAX(created_at)은 맞는데 같은 줄의 id와 amount는 그 주문의 값이 아니었습니다. 그래서 서브쿼리로 다시 조인했습니다.

SELECT o.*
FROM orders o
JOIN (SELECT user_id, MAX(created_at) AS m FROM orders GROUP BY user_id) t
  ON t.user_id = o.user_id AND t.m = o.created_at;

같은 초에 두 건이 들어오면 두 줄이 나왔고, 테이블을 두 번 읽었습니다.

원인 분석

GROUP BY는 여러 행을 한 행으로 접습니다. 접히고 나면 원래 어느 행이었는지는 사라지고, 집계 함수가 만든 값만 남습니다. 그래서 "최댓값"은 알 수 있어도 "최댓값을 가진 그 행"은 알 수 없습니다.

윈도 함수는 접지 않습니다. 행을 그대로 두고 각 행에 대해 지정한 창(파티션) 안에서 계산한 값을 한 컬럼 더 붙여 줍니다. ROW_NUMBER() OVER (PARTITION BY ... ORDER BY ...)는 그룹 안에서의 순번을 매기고, 그중 1번만 고르면 원하는 행 전체를 얻습니다.

해결 방안

1. 파티션 안에서 순번을 매기고 1번만 고릅니다. 테이블을 한 번만 읽습니다.

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

ORDER BY에 id DESC를 덧붙인 것이 중요합니다. 시각이 같은 행이 있어도 순서가 하나로 정해져 결과가 흔들리지 않습니다.

2. 동점을 어떻게 다룰지 함수로 표현합니다.

동점을 모두 보고 싶다면 RANK()를, 무조건 한 건이면 ROW_NUMBER()를 씁니다.

3. 직전 값과 비교할 때는 LAG/LEAD를 씁니다. 자기 조인 없이 이전 행을 볼 수 있습니다.

SELECT id, created_at, amount,
       amount - LAG(amount) OVER (PARTITION BY user_id ORDER BY created_at) AS diff
FROM orders;

4. 인덱스를 파티션·정렬 순서에 맞춥니다. (user_id, created_at DESC) 인덱스가 있으면 정렬 비용이 크게 줄어듭니다. 윈도 함수를 쓴다고 인덱스가 필요 없어지지는 않습니다.
5. WHERE에서는 윈도 함수를 쓸 수 없습니다. 윈도 함수는 WHERE가 끝난 뒤에 계산되므로 WHERE rn = 1은 오류입니다. 위처럼 CTE나 서브쿼리로 한 겹 감싸야 합니다.

공식 문서

• MariaDB: Window Functions
• MariaDB: ROW_NUMBER()
• MySQL: Window Function Concepts and Syntax