[PostgreSQL] 집계 안의 ORDER BY — string_agg/array_agg 결과 순서를 고정한다

설치·접속: PostgreSQL 설치와 접속

부제: 주문별 상품명을 콤마 리스트로 뽑는 리포트가 실행할 때마다 순서가 바뀌어 diff가 지저분할 때

-- 정렬 없는 string_agg: 입력 순서에 좌우돼 재현이 보장되지 않는다
SELECT order_id, string_agg(product, ', ') AS items
FROM lines GROUP BY order_id ORDER BY order_id;

 order_id |              items
----------+---------------------------------
        1 | Keyboard, Mouse, Monitor, Cable
        2 | Desk, Chair
(2 rows)

-- 집계 함수 괄호 안에 ORDER BY를 넣어 순서를 못박는다
SELECT order_id, string_agg(product, ', ' ORDER BY product) AS items
FROM lines GROUP BY order_id ORDER BY order_id;

 order_id |              items
----------+---------------------------------
        1 | Cable, Keyboard, Monitor, Mouse
        2 | Chair, Desk
(2 rows)

string_agg·array_agg 같은 집계는 입력 행의 순서에 따라 결과가 달라지는데, 순서를 지정하지 않으면 그 순서는 미정의다. 지금은 삽입 순서대로 나오지만, 인덱스 스캔·병렬 처리·플랜 변화에 따라 언제든 뒤바뀔 수 있다 — 매번 순서가 흔들려 리포트 diff가 지저분해지거나 재현 불가 버그의 원인이 된다. 해법은 집계 함수 인자 뒤에 ORDER BY를 넣는 것. 이게 바깥 쿼리의 ORDER BY가 아니라 집계 안쪽의 정렬이라는 점이 핵심이고, MySQL GROUP_CONCAT의 정렬을 Postgres에서 이렇게 한다.

정렬 키는 집계 대상과 다른 컬럼이어도 된다. "담은 순서(added_seq)"나 "가격 높은 순"처럼 표시 값과 무관한 기준으로 배열/문자열 순서를 정한다.

-- 집계 값(product)과 다른 컬럼으로 정렬
SELECT order_id, array_agg(product ORDER BY added_seq) AS by_added
FROM lines GROUP BY order_id ORDER BY order_id;

SELECT order_id,
       string_agg(product, ' > ' ORDER BY price DESC) AS by_price,
       count(*) AS n
FROM lines GROUP BY order_id ORDER BY order_id;

 order_id |            by_added
----------+--------------------------------
        1 | {Mouse,Monitor,Keyboard,Cable}
        2 | {Desk,Chair}

 order_id |              by_price              | n
----------+------------------------------------+---
        1 | Monitor > Keyboard > Mouse > Cable | 4
        2 | Desk > Chair                       | 2
(2 rows)

by_added는 added_seq(담은 순서) 기준, by_price는 가격 내림차순으로 같은 상품들이 다르게 배열된다. 순서가 의미를 갖는 리스트(장바구니 담은 순, 가격 랭킹, 타임라인)를 한 컬럼에 접어 넣을 때, 집계 안 ORDER BY 하나로 결정적인 출력을 보장한다.

이렇게도 쓴다

JSON 배열로 모을 때도 순서를 고정한다. (조합: jsonb_agg)

SELECT order_id, jsonb_agg(product ORDER BY price DESC) AS items_json
FROM lines GROUP BY order_id;

 

FILTER와 함께 조건에 맞는 값만 정렬해 모은다. (조합: FILTER)

SELECT order_id,
       string_agg(product, ', ' ORDER BY price DESC) FILTER (WHERE price >= 50) AS pricey
FROM lines GROUP BY order_id;

 

DISTINCT로 중복을 제거하며 정렬한다(DISTINCT 시 정렬 키는 집계 대상과 일치해야 함).

SELECT string_agg(DISTINCT product, ', ' ORDER BY product) FROM lines;

 

여러 컬럼을 이어붙여 정렬 키로 쓴다.

SELECT order_id, array_agg(product ORDER BY price DESC, product) FROM lines GROUP BY order_id;

 

배열로 모은 뒤 array_to_string으로 후처리하는 대신, 처음부터 string_agg가 간결하다.

SELECT order_id, string_agg(product, ' / ' ORDER BY added_seq) FROM lines GROUP BY order_id;

 

윈도우로 쓰면 러닝 리스트(현재 행까지 누적된 목록)가 된다. (조합: 윈도우)

SELECT product, string_agg(product, ',') OVER (ORDER BY added_seq) FROM lines WHERE order_id=1;