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

빌드노트

PostgreSQL array_agg 배열 순서가 다르면 ORDER BY 위치부터 본다

array_agg의 원소 순서는 집계 안의 ORDER BY로 정하고, 최종 결과 행의 순서는 바깥 ORDER BY로 정한다. PostgreSQL 18.6에서 두 입력 방향, 동점 보조 키, 그룹 정렬을 비교하고 관찰값과 문서상 보장을 구분한다.

array_agg로 만든 배열의 순서가 예상과 다르면 ORDER BY가 어디에 있는지 먼저 확인한다. 쿼리 마지막의 정렬은 배열을 담은 결과 행에 적용된다. 배열 안의 원소를 정렬하려면 array_agg(label ORDER BY position, id)처럼 집계 함수의 괄호 안에 순서를 적어야 한다. 이 예에서는 position이 표시 순번이고 id가 동점일 때 사용할 고유 번호다. PostgreSQL 18 집계 표현식 문서 (새 탭에서 열림)가 이 문법을 설명한다.

삽입할 때 정렬했거나 정렬된 서브쿼리를 넘겼다는 사실만으로 이 조건을 대신할 수는 없다. 공식 문서는 정렬된 서브쿼리를 바로 집계하면 보통 원하는 순서가 나오지만, 바깥 단계의 조인 같은 추가 처리에서 집계 전에 순서가 바뀔 수 있다고 설명한다. 기본 집계 입력 순서는 미지정이다. 집계 함수의 입력 순서 설명 (새 탭에서 열림)

입력을 반대로 정렬하니 배열도 달라졌다

PostgreSQL 18.6의 새 실습용 DB에서 여섯 개의 합성 행으로 비교했다. 따라 하려면 PostgreSQL에 접속할 SQL 클라이언트를 준비하고, 아래 SQL을 같은 세션에서 차례로 실행한다. 임시 테이블은 세션이 끝나면 사라진다.

다음 입력은 두 그룹을 만들고, 각 그룹에 같은 표시 순번을 가진 행을 넣는다. id는 기본 키이므로 중복되지 않는다.

CREATE TEMP TABLE agg_item (
    id integer PRIMARY KEY,
    group_id integer NOT NULL,
    position integer NOT NULL,
    label text NOT NULL
);
INSERT INTO agg_item (id, group_id, position, label) VALUES
    (30, 1, 1, 'beta'),
    (10, 1, 1, 'alpha'),
    (20, 1, 2, 'gamma'),
    (60, 2, 2, 'zeta'),
    (40, 2, 1, 'delta'),
    (50, 2, 1, 'epsilon');

첫 번째 그룹에서 집계 안의 정렬을 생략하고, 서브쿼리의 ID 정렬 방향만 바꿔 본다.

SELECT array_agg(label) AS labels
FROM (SELECT * FROM agg_item WHERE group_id = 1 ORDER BY id ASC) AS s;

SELECT array_agg(label) AS labels
FROM (SELECT * FROM agg_item WHERE group_id = 1 ORDER BY id DESC) AS s;

첫 SELECT는 {alpha,gamma,beta}, 둘째는 {beta,gamma,alpha}를 반환했다. 이 단순한 쿼리에서는 서브쿼리가 정렬한 순서대로 배열에 들어갔다. 표시 순번인 position 순으로 모으라는 조건은 어느 집계에도 없다.

이 비교는 같은 SQL을 실행할 때마다 배열이 저절로 섞인다는 재현이 아니다. 입력의 정렬 방향을 직접 바꾼 실험이다. 내림차순 쿼리를 그대로 세 번 더 실행했을 때는 모두 {beta,gamma,alpha}였다. 다른 환경에서도 두 출력이 같거나 반복 실행이 안정적일 수 있다. 그 관찰만으로 집계 순서가 보장됐다고 판단하지 않는다.

표시 순번이 같으면 무엇을 먼저 넣을까

입력 정렬에 기대는 대신 집계 안에 표시 순번과 보조 키를 함께 적는다. 다음은 ID 내림차순 입력을 받아도 배열은 position, 그다음 id 오름차순으로 만들도록 바꾼 쿼리다.

SELECT array_agg(label ORDER BY position, id) AS labels
FROM (SELECT * FROM agg_item WHERE group_id = 1 ORDER BY id DESC) AS s;

결과는 {alpha,beta,gamma}였다. 서브쿼리를 ORDER BY id ASC로 바꾼 경우도 같은 결과였다. gamma의 ID가 beta보다 작아도 표시 순번이 2이므로 뒤에 놓인다.

ORDER BY position만 쓰면 alpha와 beta는 둘 다 1이라 어느 쪽이 먼저인지 정하지 못한다. 실제로 두 입력 방향에 이 정렬만 적용했을 때는 각각 {alpha,beta,gamma}와 {beta,alpha,gamma}가 나왔다. 이 차이가 모든 실행에서 나타나야 하는 것은 아니다. 모든 정렬 키가 같은 행의 순서는 구현에 따라 달라질 수 있다. SELECT의 ORDER BY 규칙 (새 탭에서 열림)

여기서는 고유한 id를 두 번째 기준으로 넣어 동점을 끝냈다. 반대로 ORDER BY position, id DESC를 적용하면 동점인 두 원소만 뒤집혀 {beta,alpha,gamma}가 됐다. 실제 쿼리에서도 보조 키의 방향을 정하고, 그 키가 집계 입력 안에서 동점을 끝내는지 확인해야 한다.

바깥 ORDER BY가 움직이는 것은 그룹 행이다

배열 안의 순서와 화면에 보여 줄 그룹 순서는 따로 지정한다. 다음 쿼리는 원소를 position, id 오름차순으로 모으고, 완성된 두 그룹 행은 group_id 내림차순으로 반환한다.

SELECT group_id, array_agg(label ORDER BY position, id) AS labels
FROM agg_item
GROUP BY group_id
ORDER BY group_id DESC;

실제 출력에서는 그룹 2가 먼저 나오지만 각 배열은 앞에서 선언한 원소 순서를 유지했다.

group_id|labels
2|{delta,epsilon,zeta}
1|{alpha,beta,gamma}

마지막 줄만 ORDER BY group_id ASC로 바꾸면 그룹 1이 먼저 나왔다. 배열 내용은 각각 그대로였다. 바깥 ORDER BY는 SELECT가 만든 결과 행을 정렬한다는 행 정렬 문서 (새 탭에서 열림)의 설명과 맞는다. 그룹 없이 배열 하나만 반환하는 쿼리 뒤에 ORDER BY labels를 붙여도 배열 원소를 다시 정렬하지 않는다. 내림차순 입력에 그렇게 실행한 관찰값도 {beta,gamma,alpha}였다.

수정할 때는 필요한 원소 순서를 array_agg 안에 적고, 동점 기준까지 정한 뒤, 그룹 표시 순서를 마지막 ORDER BY에 별도로 남긴다. 이번 비교는 PostgreSQL 18.6과 여섯 합성 행에서 확인한 SQL 동작이다. 조인·인덱스·병렬 실행 계획을 바꿔 같은 쿼리의 순서가 달라지는 상황이나 성능 차이까지 측정한 것은 아니다.

← 전체 글 보기