[SQL] solvesql 지역별 주문의 특징 / COUNT(DISTINCT CASE WHEN) 절 활용 문제 / 피벗테이블, 집계

문제

문제 링크 : https://solvesql.com/problems/characteristics-of-orders/

 

US E-Commerce Records 2020 데이터셋은 미국 이커머스 웹사이트의 판매 데이터 입니다. records 테이블은 주문 번호, 주문 날짜, 주문 지역, 카테고리 등 주문의 상세 정보를 담고 있습니다. 이 데이터를 이용하여 미국의 각 지역별로 어떤 카테고리의 상품이 많이 판매되는지 알아보려고 합니다. region, category 별 주문량을 계산해 출력하는 쿼리를 작성해주세요.

결과 데이터 형식

결과 데이터는 아래와 같은 테이블 형태로 출력되어야 하고, Region 컬럼 기준 오름차순으로 정렬되어 있어야 합니다.

Region	Furniture	Office Supplies	Technology
Central
East
South
West

 

결과 데이터의 각 컬럼은 다음과 같은 의미를 갖습니다.

• Region - 주문 지역
• Furniture - 해당 지역 내 가구(’Furniture’) 주문 수
• Office Supplies - 해당 지역 내 오피스 물품(’Office Supplies’) 주문 수
• Technology - 해당 지역 내 전자기기(’Technology’) 주문 수

 

해답

 

Category는 원래 Furniture, Office Supplies, Technology 로 구성되어 있는 한 개의 컬럼이었는데,

이걸 3개의 컬럼으로 나누어 피봇화 하는 것이 포인트! 

각 컬럼으로 분리하고 이름을 설정하고 집계하기 위해서는 CASE WHEN 구문 사용을 해야 했고

해당 절에서 집계, 중복 처리를 어떻게 하는지 학습할 수 있는 문제였다.

SELECT region AS Region,
  COUNT(DISTINCT CASE WHEN category='Furniture' THEN order_id ELSE NULL END) AS 'Furniture',
  COUNT(DISTINCT CASE WHEN category='Office Supplies' THEN order_id ELSE NULL END) AS 'Office Supplies',
  COUNT(DISTINCT CASE WHEN category='Technology' THEN order_id ELSE NULL END) AS 'Technology'
FROM records
GROUP BY region;

 

Key Point! 

1. CASE WHEN 절을 매 줄 마다 설정하는 것

2. ELSE 뒤에 NULL을 넣어주는 것

3. THEN 뒤에 바로 COUNT 등 집계를 하는 것이 아니라, CASE WHEN 바깥에서 집계함수로 묶어주는 것

4. DISTINCT로 중복 제거할 경우, CASE WHEN 앞에서 묶어주는 것