집계 — GROUP BY 가 실제로 하는 일
한 줄 요약
GROUP BY 는 행을 그룹으로 접고, 접힌 뒤에는 그룹을 대표하는 값만 남는다. 그래서 SELECT 목록에는 그룹의 키, 키에 딸린 값, 집계 함수만 올 수 있다. 집계는 오류 없이 틀리는 연산이므로, 결과를 믿기 전에 접기 전과 뒤의 합계가 맞는지 확인한다.
왜 이게 필요했나
월간 리포트의 채널별 매출이 재무팀 숫자와 3% 다르다는 연락이 왔다. 쿼리는 오류 없이 돌았고 숫자도 그럴듯하다. 뜯어 보니 원인이 셋이었다. 반품 전표가 음수 수량으로 섞여 있었고, 월을 자를 때 서버 시간대(UTC)를 써서 매월 1일 0시부터 9시까지의 한국 주문이 전달로 넘어가 있었고, 주문 표를 주문 상세 표와 조인한 뒤 주문 금액을 더해 상품이 여러 개인 주문의 금액이 여러 번 더해져 있었다.
"채널별 매출" 이나 "상태별 주문 수" 같은 질문은 개별 행이 아니라 요약을 원한다. 집계는 여러 행을 하나로 접는 연산이고, 그 접는 기준이 GROUP BY 다. 접고 나면 원래 행이 보이지 않으므로, 위 사례처럼 잘못 접힌 결과는 겉보기로 구별되지 않는다. 그래서 동작 원리와 함께 확인하는 방법을 같이 익혀야 한다.
어떻게 동작하나
처리 순서를 기억하면 헷갈리는 부분이 대부분 풀린다.
FROM -> WHERE -> GROUP BY -> aggregate -> HAVING -> SELECT -> ORDER BY -> LIMIT
- WHERE 는 접기 전, HAVING 은 접은 뒤 걸린다. 그래서
WHERE count(*) > 5는 쓸 수 없다. 그 시점에는 아직 셀 대상이 없기 때문이다. 행 단위 조건은 WHERE 에 두는 편이 좋다. 접을 행 자체가 줄어 일이 적어진다. - PostgreSQL 에서는 SELECT 에서 붙인 별칭을 ORDER BY 와 GROUP BY 에서 쓸 수 있지만 WHERE 와 HAVING 에서는 쓸 수 없다. 거기서는 식을 다시 적어야 한다.
- SELECT 목록의 컬럼은 GROUP BY 에 있거나 집계 함수 안에 있어야 한다. 예외가 하나 있다. 어떤 표의 기본 키로 묶었다면 그 표의 다른 컬럼은 키에 함수적으로 종속되어 그룹마다 값이 하나뿐이므로, PostgreSQL 은 그 컬럼을 GROUP BY 에 적지 않아도 받아 준다.
집계 함수는 NULL 을 무시한다. avg(price) 는 NULL 인 행을 분모에서도 뺀다. count(*) 는 행을 모두 세지만 count(price) 는 price 가 NULL 이 아닌 행만 센다. 그리고 행이 하나도 없으면 count 를 뺀 집계 함수는 0 이 아니라 NULL 을 돌려준다. sum 도 마찬가지다. 0 이 필요하면 coalesce(sum(x), 0) 처럼 명시한다.
FILTER 절은 같은 그룹에서 조건별로 다른 집계를 할 때 쓴다. 조건이 참인 행만 그 집계 함수에 들어간다.
SELECT channel,
count(*) FILTER (WHERE status = 'paid') AS paid_count,
count(*) FILTER (WHERE status = 'cancelled') AS cancelled_count
FROM orders
GROUP BY channel;
같은 일을 WHERE 로 하려면 쿼리를 두 번 돌려 조인해야 하고, 한쪽에 없는 채널이 결과에서 빠지는 문제까지 따로 처리해야 한다.
시간 단위 집계에서는 시간대를 반드시 못 박는다. timestamptz 컬럼에 date_trunc('month', ordered_at) 을 쓰면 세션의 TimeZone 설정 기준으로 자른다. 같은 쿼리가 접속한 도구에 따라 다른 답을 내는 이유다. ordered_at AT TIME ZONE 'Asia/Seoul' 은 그 시각이 서울에서 몇 시였는지를 시간대 없는 타임스탬프로 돌려주므로, 그것을 자르면 어디서 돌려도 같은 답이 나온다. PostgreSQL 은 세 번째 인자로 시간대를 받는 date_trunc 도 제공한다.
현장에서 무엇이 잘못되나
조인이 행을 부풀린다. 주문 1건에 상품이 3개면 주문과 주문 상세를 조인한 결과에는 그 주문이 3행으로 나온다. 여기서 주문 금액을 더하면 세 번 더해진다. 증상은 매출이 평균 상품 수만큼 부풀어 있는 것이다. 주문 단위 금액은 주문 표에서 먼저 집계하거나, 조인 전에 필요한 단위로 접는다.
LEFT JOIN 이 조용히 INNER JOIN 이 된다. 주문이 없는 고객까지 분모에 넣으려고 고객 표를 기준으로 LEFT JOIN 했는데, WHERE 에 주문 표 컬럼 조건(o.status = 'paid')을 걸면 주문이 없는 고객 행은 그 조건에서 NULL 이 되어 걸러진다. 조건은 ON 절로 옮겨야 한다. 같은 쿼리에서 count(*) 를 쓰면 주문이 없는 고객도 1로 세므로 주문 수를 셀 때는 count(o.id) 를 쓴다.
음수와 결측을 모르고 더한다. 반품 전표가 음수 수량으로 들어 있으면 sum(quantity) 가 판매량을 조용히 줄이고 상위 상품 순위가 뒤집힌다. 집계 전에 부호와 NULL 부터 본다.
월 경계가 9시간 어긋난다. 한국 시간은 UTC 보다 9시간 빠르다. UTC 로 월을 자르면 매월 1일 오전 9시 전의 한국 주문이 전달 매출로 잡힌다. 월별 합계를 모두 더하면 전체와 같으므로 합계 검증으로는 드러나지 않고, 월마다 조금씩만 다르다.
평균의 평균을 낸다. 매장별 평균 객단가를 다시 평균하면 주문이 10건인 매장과 1만 건인 매장이 같은 무게를 갖는다. 전체 평균이 필요하면 합계를 합계로 나눈다.
정수 나눗셈과 반올림 타입. PostgreSQL 에서 정수끼리 나누면 소수점 아래가 버려진다. 100 * a / b 가 0 이나 99 로 나오면 이것을 의심하고 100.0 * a / b 처럼 한쪽을 numeric 으로 만든다. 또 소수점 자릿수를 받는 round(x, 2) 는 numeric 에 대해 정의되어 있어, double precision 값에 쓰면 함수가 없다는 오류가 난다. round(x::numeric, 2) 로 바꾼다.
어떻게 확인하나
집계 결과를 내기 전에 네 가지를 대조한다.
-- 1. fan-out: rows vs distinct keys after the join
SELECT count(*), count(DISTINCT o.id)
FROM orders o JOIN order_items i ON i.order_id = o.id;
-- 2. sign and nulls before summing
SELECT min(quantity), max(quantity), count(*) - count(quantity) AS null_qty
FROM order_items;
-- 3. which time zone this session uses
SHOW TimeZone;
-- 4. the grouped totals must add up to the ungrouped total
SELECT (SELECT sum(total_amount) FROM orders) AS all_rows,
(SELECT sum(revenue) FROM (SELECT channel, sum(total_amount) AS revenue
FROM orders GROUP BY channel) g) AS by_group;
첫 쿼리에서 두 숫자가 다르면 조인이 행을 부풀리고 있다. 둘째 쿼리의 최솟값이 음수이거나 NULL 개수가 0 이 아니면 그 행을 어떻게 다룰지 먼저 정한다. 넷째 쿼리의 두 값이 다르면 그룹으로 접는 과정에서 행이 빠지거나 겹친 것이다. 그리고 평균이나 1인당 값을 낼 때는 분모에 무엇을 넣었는지를 결과와 함께 적는다. 주문이 없는 고객을 넣는지 빼는지에 따라 숫자가 크게 달라지고, 둘 다 맞는 답일 수 있기 때문이다.
다음 이론에서 볼 것
바로 뒤의 읽기에서 행을 접지 않고 순위와 누적을 계산하는 윈도우 함수를 배운다. 그 뒤 실습에서 상태별 건수, 채널별 매출, HAVING 과 FILTER, 서울 기준 월별 집계, 음수 수량을 뺀 상위 상품, 주문이 없는 고객까지 분모에 넣은 등급별 1인당 매출을 차례로 만들며, 이 절의 함정을 하나씩 직접 밟아 본다.