숏컷 코드
SELECT
COUNT(*) AS total_orders,
SUM(CASE WHEN status = 'paid' THEN amount ELSE 0 END) AS paid_amount,
COUNT(*) FILTER (WHERE status = 'cancelled') AS cancelled_count
FROM orders;문법
행을 먼저 분류하고 나서 집계한다
CASE WHEN은 집계 함수 안에서 "이 행을 집계에 포함할지, 어떤 값으로 넣을지"를 결정하는 분기 장치입니다. SUM(CASE WHEN status = 'paid' THEN amount ELSE 0 END)는 paid 행만 amount를 더하고 나머지는 0을 넣어, 결과적으로 paid 금액만 합산합니다. "행을 먼저 분류하고 그다음 집계한다"는 사고를 명확히 갖지 않으면 ELSE 0을 빠뜨리거나 조건을 잘못 거는 실수가 나기 쉽습니다.
SELECT
SUM(CASE WHEN status = 'paid' THEN amount ELSE 0 END) AS paid_total,
SUM(CASE WHEN status = 'refund' THEN amount ELSE 0 END) AS refund_total
FROM orders;CASE WHEN과 FILTER는 같은 문제를 다른 방식으로 푼다
조건부 집계는 크게 두 방식으로 씁니다. 값을 바꿔 넣어야 하면 CASE WHEN, 특정 조건 행만 따로 세거나 평균낼 때는 FILTER가 더 직접적입니다. "값을 바꿔서 넣는가"와 "행을 걸러서 집계하는가"로 나누면 선택이 쉬워집니다.
-- 값 자체를 바꿔 넣기
SUM(CASE WHEN status = 'paid' THEN amount ELSE 0 END)
-- 특정 행만 따로 집계
COUNT(*) FILTER (WHERE status = 'paid')FILTER 절은 CASE WHEN보다 의도를 더 분명히 드러낸다
PostgreSQL은 FILTER (WHERE ...) 문법을 지원합니다. COUNT(*) FILTER (WHERE status = 'cancelled')는 SUM(CASE WHEN status = 'cancelled' THEN 1 ELSE 0 END)와 결과가 같지만, 읽는 사람이 "이 집계는 cancelled 행만 센다"는 의도를 즉시 파악할 수 있습니다. 실행 계획상 성능 차이는 거의 없으나, 코드 가독성과 유지보수 면에서 FILTER가 더 유리합니다.
SELECT
COUNT(*) FILTER (WHERE status = 'paid') AS paid_count,
COUNT(*) FILTER (WHERE status = 'cancelled') AS cancelled_count,
AVG(amount) FILTER (WHERE status = 'paid') AS avg_paid
FROM orders;피벗 리포트는 CASE WHEN 조건부 집계로 행을 열로 펼친다
대시보드나 정산 보고서에서 "status별로 각각의 합계를 한 행에 나란히 보여 달라"는 요구가 자주 나옵니다. 이를 피벗이라 부르며, PostgreSQL에는 전용 PIVOT 문법이 없으므로 CASE WHEN 조건부 집계가 표준 방법입니다. GROUP BY와 함께 쓰면 user_id별·날짜별 피벗도 만들 수 있습니다.
SELECT
user_id,
SUM(CASE WHEN status = 'paid' THEN amount ELSE 0 END) AS paid,
SUM(CASE WHEN status = 'pending' THEN amount ELSE 0 END) AS pending,
SUM(CASE WHEN status = 'cancelled' THEN amount ELSE 0 END) AS cancelled
FROM orders
GROUP BY user_id;복잡한 조건부 집계는 CTE로 단계를 나눠야 유지보수가 쉽다
CASE WHEN 블록이 5개 이상 반복되거나 조건 자체가 복잡해지면, 한 쿼리 안에 모든 로직을 쑤셔 넣는 것은 위험합니다. 열 alias가 무엇을 의미하는지 잃기 쉽고, 조건 수정 시 누락이 발생합니다. CTE로 먼저 상태를 분류하거나, 집계 전 중간 결과를 한 번 이름 붙여두면 쿼리 리뷰와 디버깅이 훨씬 쉬워집니다.
WITH order_buckets AS (
SELECT
user_id,
amount,
CASE
WHEN status = 'paid' THEN 'paid'
WHEN status = 'cancelled' THEN 'cancelled'
ELSE 'other'
END AS bucket
FROM orders
)
SELECT
user_id,
SUM(CASE WHEN bucket = 'paid' THEN amount ELSE 0 END) AS paid_total,
SUM(CASE WHEN bucket = 'cancelled' THEN amount ELSE 0 END) AS cancelled_total
FROM order_buckets
GROUP BY user_id;체크포인트
| 상황 | 적합한 선택 |
|---|---|
| 특정 조건 행만 합계/평균 구하기 | SUM(CASE WHEN ... THEN val ELSE 0 END) |
| 특정 조건 행만 개수 세기 | COUNT(*) FILTER (WHERE ...) |
| 상태별 값을 열로 나란히 펼치기 | CASE WHEN 피벗 패턴 + GROUP BY |
| 조건이 복잡하고 열이 많은 리포트 | CTE로 단계 분리 후 집계 |
주의할 점
CASE WHEN이 길어질수록 각 열이 무엇을 의미하는지 파악하기 어려워집니다. alias를 명확히 붙이고,
조건이 많다면 CTE로 한 단계 나눠 두는 것이 버그를 줄이는 실무 습관입니다. 또한 ELSE 절을 생략하면
조건에 맞지 않는 행에서 NULL이 반환되어 SUM 결과가 예상과 달라질 수 있습니다.
SUM(CASE WHEN status = 'paid' THEN amount END)이 표현은 paid가 아닌 행에서 NULL을 반환합니다. SUM에서는 종종 괜찮지만, "0이 들어가길 기대했다"는 의도와는 달라질 수 있어 주의가 필요합니다.
참고 링크
2 sources