Ch05. CASE문 - 조건부 집계

"하나의 쿼리로, 전체 주문 건수와 함께 "결제 완료(COMPLETED)", "배송(SHIPPED)", "주문 대기(PENDING)" 상태의 주문이 각각 몇 건인지 별도의 컬럼으로 나누어 보고 싶다."

 

뽑고 싶은 보고서

"피벗 테이블(Pivot Table)"이라는 이름은 데이터를 다양한 관점에서 "회전(pivot)" 시켜 분석할 수 있는 기능 때문에 붙여진 이름이다.

 

UNION으로 합치는 방법

"UNION"을 사용하면 여러 "SELECT" 문의 결과를 하나로 합칠 수 있다. 이때 각 "SELECT" 문의 컬럼 수가 같아야 하며, 컬럼의 데이터 타입도 호환되어야 한다.

-- Union
select 
    'Total' as category,
    count(*)
from orders

union all

select 
	status as category, 
    count(*)
from orders
group by status
;

쿼리 결과

• 이 방법은 각 상태별 통계와 전체 합계를 하나의 결과 집합으로 보여주지만, 우리가 원하는 "각 상태별 카운트가 별도의 컬럼으로 나오는" 보고서 형식과는 차이가 있다. 이 모양을 옆으로 돌려서 피벗 테이블의 형태가 되어야 한다

 

서브 쿼리를 사용하는 방법

각 상태별 카운트를 서브 쿼리로 미리 구한 다음, 메인 쿼리에서 이 값들을 조인하거나 활용하는 방법이 있다.

select 
	(select count(*) from orders) as total_orders,
    (select count(*) from orders where status = 'COMPLETED') as completed_count,
    (select count(*) from orders where status = 'SHIPPED') as shipped_count,
    (select count(*) from orders where status = 'PENDING') as pending_count

쿼리 결과

• 이 방법은 우리가 목표로 하는 보고서 형식과 같은 결과를 보여준다. 각 카운트가 별도의 컬럼으로 잘 분리되어 있다.
• 이 방법은 우리가 원하는 결과를 주지만, 각 값을 얻기 위해 "orders" 테이블을 총 4번이나 읽어오는(FROM orders) 심각한 성능 문제를 가지고 있다.
  • 데이터가 많아질수록 매우 비효율적이다.

CASE를 품은 집계 함수

 

패턴 1: COUNT(CASE ...)

COUNT(CASE WHEN status = 'COMPLETED' THEN 1 END)

• COUNT 함수는 "NULL"이 아닌 모든 값을 센다는 특징을 이용한다.
• status가 "COMPLETED"이면 CASE 문은 숫자 "1"을 반환한다.
• 그 외의 경우(ELSE가 없으므로), CASE 문은 "NULL"을 반환한다.
• 결과적으로 "COUNT" 함수는 "status"가 "COMPLETED"인 행의 개수만 세게 된다.

패턴 2: SUM(CASE ...)

SUM(CASE WHEN status = 'COMPLETED' THEN 1 ELSE 0 END)

• "status"가 "COMPLETED"이면 CASE 문은 "1"을, 그 외에는 "0"을 반환한다.
• "SUM" 함수가 이 "1"과 "0"들을 모두 더하면, 그 합계는 결국 "COMPLETED" 상태인 주문의 총 개수가 된다.

전체 주문 상태 요약하기

먼저 "orders" 테이블 전체를 대상으로, 각 상태별 주문 건수를 집계해 보자. "GROUP BY"는 아직 필요 없다.

select 
	count(*) as total_orders,
    sum(case when status = 'COMPLETED' then 1 else 0 end) as completed_count,
    sum(case when status = 'SHIPPED' then 1 else 0 end) as shipped_count,
    sum(case when status = 'PENDING' then 1 else 0 end) as pending_count
from orders;

쿼리 결과

• 단 하나의 쿼리로 행으로 흩어져 있던 "status" 정보를 열로 변환하여, 한눈에 파악하기 쉬운 피벗(Pivot) 보고서를 만들어냈다

GROUP BY와 함께 사용하기 (피벗 테이블)

"상품 카테고리별로, 상태별 주문 건수를 집계하라"

select 
	p.category,
    count(*) as total_orders,
	sum(case when status = 'COMPLETED' then 1 else 0 end) as completed_count,
    sum(case when status = 'SHIPPED' then 1 else 0 end) as shipped_count,
    sum(case when status = 'PENDING' then 1 else 0 end) as pending_count
from orders o
join products p on o.product_id = p.product_id
group by p.category;

 

쿼리 결과

• "전자기기"는 주문은 많지만 배송 상태이거나 대기중인 건이 많은 반면, "도서"는 주문 건수는 적지만 모두 처리가 완료되었다는 인사이트를 단번에 얻을 수 있다.