SQL 실전 · 집계와 윈도우 함수 · 이론

집계 — GROUP BY 가 실제로 하는 일

한 줄 요약

GROUP BY 는 행을 그룹으로 접고, 접힌 뒤에는 그룹을 대표하는 값만 남으므로, SELECT 목록에는 그룹의 키이거나 집계 함수인 것만 올 수 있다.

왜 이게 필요했나

"채널별 매출"이나 "상태별 주문 수" 같은 질문은 개별 행이 아니라 요약을 원한다. 집계는 여러 행을 하나로 접는 연산이고, 그 접는 기준이 GROUP BY 다.

어떻게 동작하나

처리 순서를 기억하면 헷갈리는 부분이 대부분 풀린다.

FROM → WHERE → GROUP BY → 집계 → HAVING → SELECT → ORDER BY → LIMIT

WHERE 는 접기 전, HAVING 은 접은 뒤 걸린다. 그래서 WHERE count(*) > 5 는 쓸 수 없다. 그 시점에는 아직 셀 대상이 없기 때문이다.
SELECT 가 GROUP BY 뒤에 오므로, SELECT 에서 만든 별칭을 HAVING 에서 쓰지 못하는 엔진이 있다. 반면 ORDER BY 는 SELECT 뒤라 별칭을 쓸 수 있다.

집계 함수는 NULL 을 무시한다. avg(price) 는 NULL 인 행을 분모에서도 뺀다. 그래서 "평균이 이상하다"는 문의의 상당수가 NULL 행 때문이다. 0 으로 취급하고 싶다면 avg(coalesce(price, 0)) 처럼 명시해야 한다.

FILTER 절을 알아 두면 코드가 깔끔해진다. 같은 그룹에서 조건별로 다른 집계를 하고 싶을 때 쓴다.

SELECT channel,       count(*) FILTER (WHERE status = 'paid')      AS paid_count,       count(*) FILTER (WHERE status = 'cancelled') AS cancelled_countFROM ordersGROUP BY channel;

이것을 WHERE 로 하려면 쿼리를 두 번 돌려 조인해야 한다. 한 번의 스캔으로 끝난다는 점이 성능 차이를 만든다.

시간 단위 집계에서는 시간대를 반드시 명시해야 한다. ordered_at 이 시간대를 포함한 타입이라면 date_trunc('month', ordered_at) 은 서버의 현재 시간대 설정에 따라 결과가 달라진다. ordered_at AT TIME ZONE 'Asia/Seoul' 처럼 기준을 못 박아야 어디서 돌려도 같은 답이 나온다.

현장에서 만나는 모습

seed 데이터에는 반품 전표가 음수 수량으로 들어가 있다. sum(quantity) 를 아무 생각 없이 쓰면 판매량이 조용히 줄어들고 상위 상품 순위가 뒤집힌다. 집계 전에 데이터의 부호와 결측을 먼저 확인하는 습관이 리포트 사고를 막는다.

또 하나. 평균을 낼 때 분모가 무엇인지 늘 물어야 한다. 등급별 1인당 매출을 구할 때 주문이 없는 고객을 분모에 넣을지 뺄지에 따라 숫자가 크게 달라지고, 둘 다 맞는 답일 수 있다. 다만 어느 쪽인지 정의를 남겨야 한다.

다음 이론에서 볼 것

다음 읽기에서 집계로 행을 접지 않고 순위와 누적을 계산하는 윈도우 함수를 배웁니다. 그 뒤 실습에서 상태별 건수, 채널별 매출, 월별 집계와 상위 상품을 함께 구하고 음수 수량이 결과를 어떻게 바꾸는지 확인합니다.