集計でサマリを作る

목표

GROUP BY, HAVING, FILTER, date_trunc 를 써서 원본 행을 의미 있는 요약으로 접을 수 있게 됩니다.

왜 중요한가

집계는 SQL 에서 가장 자주 쓰이면서 가장 조용히 틀리는 영역입니다. 오류가 나지 않고 숫자만 이상해지기 때문입니다.

세 가지를 특히 조심해야 합니다. 첫째, 집계 함수는 NULL 을 무시하므로 평균의 분모가 예상과 다를 수 있습니다. 둘째, 이 데이터에는 반품 전표가 음수 수량으로 섞여 있어 그대로 합하면 판매량이 줄어듭니다. 셋째, 시간대를 포함한 컬럼을 월 단위로 자를 때 기준 시간대를 명시하지 않으면 서버 설정에 따라 답이 달라집니다.

처리 순서를 외워 두면 대부분의 혼란이 정리됩니다. FROM, WHERE, GROUP BY, 집계, HAVING, SELECT, ORDER BY 순서입니다. WHERE 는 접기 전이고 HAVING 은 접은 뒤라는 사실 하나로 "왜 WHERE 에 count 를 못 쓰나"가 설명됩니다.

단계

1. 주문 상태별 건수를 담는 뷰 v_status_count 를 만듭니다. 컬럼은 status, order_count 입니다.
2. status 가 paid, shipped, delivered 인 주문만 대상으로 채널별 매출 합계를 담는 뷰 v_channel_revenue 를 만듭니다. 컬럼은 channel, revenue 입니다.
3. 주문이 5건 이상인 고객만 담는 뷰 v_big_customers 를 만듭니다. 컬럼은 customer_id, order_count 입니다.
4. 채널별로 결제 완료 건수와 취소 건수를 한 번에 담는 뷰 v_status_split 을 만듭니다. 컬럼은 channel, paid_count, cancelled_count 이며 모든 채널이 남아야 합니다.
5. 완료 상태 주문의 월별 매출을 담는 뷰 v_monthly_revenue 를 만듭니다. 컬럼은 month(date 형), revenue 이며, 월을 자를 때 ordered_at AT TIME ZONE 'Asia/Seoul' 을 기준으로 합니다.
6. 카테고리별 평균 가격을 소수점 없이 반올림해 담는 뷰 v_category_avg 를 만듭니다. 컬럼은 category, avg_price 입니다.
7. 판매 수량 상위 10개 상품을 담는 뷰 v_top_products 를 만듭니다. 컬럼은 product_id, name, sold_qty 이며 수량이 양수인 행만 합산하고, 정렬은 수량 내림차순, 같으면 product_id 오름차순입니다.
8. 등급별 고객 수, 매출, 1인당 매출을 담는 뷰 v_tier_arpu 를 만듭니다. 컬럼은 tier, customer_count, revenue, arpu 이며, 매출은 완료 상태 주문만 세고 주문이 없는 고객도 분모에 포함합니다. arpu 는 소수점 두 자리로 반올림합니다.

참고

• 처리 순서: FROM → WHERE → GROUP BY → 집계 → HAVING → SELECT → ORDER BY
• count(*) FILTER (WHERE 조건) 문법을 4번에서 씁니다.
• 흔한 실수 1: 7번에서 음수 수량을 그대로 더하면 순위가 달라집니다.
• 흔한 실수 2: 8번에서 조인 때문에 고객이 여러 번 세어지지 않도록 중복을 제거해야 합니다.

상태별 주문 건수

주문 상태별 건수를 담는 뷰 v_status_count 를 만듭니다. 컬럼은 status, order_count 입니다.

무엇을 기준으로 접을지 정하고, 행을 세는 집계 함수를 씁니다.

채널별 매출 합계

status 가 paid, shipped, delivered 인 주문만 대상으로 채널별 매출 합계를 담는 뷰 v_channel_revenue 를 만듭니다. 컬럼은 channel, revenue 입니다.

취소와 환불, 대기 주문은 매출에서 빠져야 합니다. 접기 전에 거르는 절이 어디인지 생각해 보세요.

주문이 많은 고객만 남기기

주문이 5건 이상인 고객만 담는 뷰 v_big_customers 를 만듭니다. 컬럼은 customer_id, order_count 입니다.

집계 결과에 대한 조건은 WHERE 로 걸 수 없습니다. 접은 뒤에 거르는 절이 따로 있습니다.

한 번의 스캔으로 두 가지 세기

채널별로 결제 완료 건수와 취소 건수를 한 번에 담는 뷰 v_status_split 을 만듭니다. 컬럼은 channel, paid_count, cancelled_count 이며 모든 채널이 남아야 합니다.

집계 함수 뒤에 조건을 붙일 수 있는 절이 있습니다. 채널은 모두 남아야 합니다.

월별 매출 집계

완료 상태 주문의 월별 매출을 담는 뷰 v_monthly_revenue 를 만듭니다. 컬럼은 month(date 형), revenue 이며, 월을 자를 때 ordered_at AT TIME ZONE 'Asia/Seoul' 을 기준으로 합니다.

시간대를 포함한 컬럼을 자를 때는 기준 시간대를 명시해야 어디서 돌려도 같은 답이 나옵니다.

카테고리별 평균가

카테고리별 평균 가격을 소수점 없이 반올림해 담는 뷰 v_category_avg 를 만듭니다. 컬럼은 category, avg_price 입니다.

소수점 없이 반올림하는 함수를 씁니다. 자릿수를 지정하지 않으면 정수로 반올림됩니다.

판매량 상위 10개 상품

판매 수량 상위 10개 상품을 담는 뷰 v_top_products 를 만듭니다. 컬럼은 product_id, name, sold_qty 이며 수량이 양수인 행만 합산하고, 정렬은 수량 내림차순, 같으면 product_id 오름차순입니다.

이 데이터에는 반품이 음수 수량으로 들어가 있습니다. 그대로 더하면 순위가 뒤집힙니다.

등급별 1인당 매출

등급별 고객 수, 매출, 1인당 매출을 담는 뷰 v_tier_arpu 를 만듭니다. 컬럼은 tier, customer_count, revenue, arpu 이며, 매출은 완료 상태 주문만 세고 주문이 없는 고객도 분모에 포함합니다. arpu 는 소수점 두 자리로 반올림합니다.

주문이 없는 고객도 분모에 들어가야 합니다. 조인 때문에 고객이 여러 번 세어지지 않게 하세요.