SQL 문제 풀이(1) - 지역별 주문의 특징(solvesql, 피벗테이블)

이번 글은 SQL 실전 문제 풀이이다.

 

📝목차

1. 문제 소개
2. 접근 1 - 원핫 인코딩 + SUM
3. 접근 2 - COUNT(DISTINCT order_id)
4. CHAT‑GPT의 답변

 

정답 바로가기

 

1. 문제 소개

이번 문제는 solvesql 사이트> 데이터리안 SQL 캠프 입문반> 3페이지에서 접할 수 있다.

난이도는 3으로 중간 정도이며, 처음 SQL 문제를 접하는 사람에게는 약간 어렵게 느껴질 수도 있다.

(사실 내가 그랬다 ㅎㅎ)

그리고 왜인지 정답을 제공하지 않기 때문에 어려움을 겪는 사람들은 이 글을 참고하길 바란다.

 

https://solvesql.com/problems/characteristics-of-orders/

https://solvesql.com/problems/characteristics-of-orders/

해당 문제의 유형은 피벗 테이블을 생성하는 것이다.

지역(region)과 주문 유형(category)에 따라 주문량을 표시하면 된다.

---

피벗 테이블이란?

 

다음과 같이 나열되어 있는 데이터를 특정 정보를 기준 삼아 요약하여 나타내는 것이다.

주문 ID	지역	주문한 품목
1	서울	가구
2	부산	가구
3	울산	전자 제품
4	충주	주방 용품
5	부산	전자 제품
6	서울	가구

위의 표를 지역과 주문한 품목을 기준으로 피벗 테이블을 형성하면 아래와 같다.

	가구	전자제품	주방용품
서울	2	0	0
부산	1	1	0
울산	0	1	0
충주	0	0	1

---

대부분의 피벗 테이블 문제는 SQL의 CASE WHEN 문법을 사용하여 해결할 수 있다.

CASE WHEN은 기존의 열과 조건문을 이용하여 새로운 열을 만들고 싶을 때 사용하는 문법이다.

답은 다소 복잡하지만 기본적인 과정은 간단하다.

1. CASE WHEN을 통해서 주문 유형별로 새로운 열을 만든다.
    (주문 유형이 Furniture, Office Supplies, Technology 이므로 총 3개의 열)
    Furniture인지 아닌지를 표시하는 열, Office Supplies인지 아닌지를 표시하는 열, Technology인지 아닌지 표시하는 열
2. 새로 만든 열을 GROUP BY(지역에 따라)와 집계 함수를 통해 지역별 개수를 센다.

 

CASE WHEN이 잘 기억이 안난다면 이전 SQL 기본 문법 정리 글을 참고하자.

2025.07.21 - [분류 전체보기] - SQL 기본 문법 정리(1)

SQL 기본 문법 정리(1)

 

나는 CASE WHEN을 사용해

  1. 조건에 해당하는 경우 1로 표시하여 합산하는 방법

  2. 조건에 해당하는 경우 ORDER_ID를 표시한 뒤 COUNT 하는 방법

두 가지 접근 방법을 사용하였다.

2. 접근 1 - 원핫 인코딩 + SUM

처음에는 CASE WHEN을 사용하여 조건에 해당하면 1 아니면 NULL값을 입력하도록 한 뒤, 해당 열을 SUM 하여 주문 유형의 합을 구하였다.

 

가. CASE WHEN 을 사용하여 새 열을 만들기

SELECT 
  CASE WHEN category = 'Furniture' THEN 1 END AS Furniture,
  CASE WHEN category = 'Office Supplies' THEN 1 END AS [Office Supplies],
  CASE WHEN category = 'Technology' THEN 1 END AS Technology
FROM records

위 쿼리를 통해 새로운 열을 만들면 다음과 같은 결과가 나타남.

	Furniture	Office Supplies	Technology
1	1
2	1
3			1
4		1

해당 데이터가 Funiture에 대한 주문일 경우 Funiture 열에 1이 표시된다.

이렇게 조건에 해당하는 경우를 1, 나머지 경우를 0(보통 원핫인코딩의 경우 0으로 표시하지만 이번에는 아무 값도 입력하지 않은 NULL로 표시)으로 표시하여 범주형 데이터를 표시하는 방법을 원핫인코딩이라고 한다.

 

나. GROUP BY와 SUM을 통해 지역별 주문유형 합계 구하기

SELECT REGION Region,
  SUM(CASE WHEN category = 'Furniture' THEN 1 END) AS Furniture,
  SUM(CASE WHEN category = 'Office Supplies' THEN 1 END) AS [Office Supplies],
  SUM(CASE WHEN category = 'Technology' THEN 1 END) AS Technology
FROM records 
GROUP BY REGION
ORDER BY REGION ASC

* CASE WHEN에서 ELSE를 따로 입력하지 않으면 아무 값도 입력되지 않으므로 NULL취급이 됨.

* SUM 집계 함수의 경우 NULL 값은 제외, 다른 집계 함수도 NULL값은 제외하여 계산함.

 

CASE WHEN을 통해 나온 결과는 해당하는 경우 1을 가지고 있기 때문에 해당 열을 SUM 하면 해당하는 경우의 총개수를 구할 수 있다. 이를  REGION에 따라 GROUP BY 하면 지역에 따른 합계를 구할 수 있다.

이렇게 하면 원하는 형태의 피벗 테이블을 얻게 된다.

 

하지만!
중요한 점을 놓쳐
이 방법은 오답이다.

 

해당 데이터에는 중복된 주문이 기록되어 있는 경우가 있으나, 해당 방법은 중복을 모두 포함하여 합계를 구하는 오류를 범한다.

 

3. 접근 2 - COUNT(DISTINCT order_id)

SQL에는 중복을 포함하지 않고 고유한 갯수만 세는 방법이 존재하는데...

COUNT(DISTINCT 집계하고 싶은 열)

 

COUNT(DISTINCT ~)를 사용하면 중복 제외한 개수를 셀 수 있다.

그리고 주문의 PRIMARY KEY(고유번호)는 ORDER_ID이기 때문에 ORDER_ID의 고유 개수를 COUNT 하면 답을 얻을 수 있다.

 

기본적인 방식은 접근 1과 동일하다.

  1. CASE WHEN을 사용하여 새로운 열을 만들고

  2. COUNT 집계 함수를 통해 갯수를 세기

 

하지만 달라진 것은 CASE WHEN 안에서 THEN 뒤에 1이 아닌 ORDER_ID를 입력하는 것.

	Furniture	Office Supplies	Technology
1	ORDER1
2	ORDER2
3			ORDER3
4		ORDER4

이렇게 조건에 해당하는 경우 1이 아닌 주문 번호가 들어가게 되고, 이후 COUNT(DISTINCT ~)를 사용해 중복 제외 개수를 셀 수 있다.

최종 정답

SELECT REGION Region,
  count(DISTINCT CASE WHEN category = 'Furniture' THEN order_id END) AS Furniture,
  count(DISTINCT CASE WHEN category = 'Office Supplies' THEN order_id END) AS [Office Supplies],
  count(DISTINCT CASE WHEN category = 'Technology' THEN order_id END) AS Technology
FROM records 
GROUP BY REGION
ORDER BY REGION ASC

*ORDER BY는 지역 알파벳에 따라 오름차순으로 정렬

*정답에는 Region으로 표시되어 있지만, 원래 테이블에는 region으로 적혀있기 때문에 꼭 AS를 통해 Region으로 표시해주어야 정답처리가 된다.

4. CHAT‑GPT의 답변

문제 자체에 중복에 대한 언급이 없어서인지 CHAT-GPT도 접근 1과 같은 답변을 내놓았다.(ㅋㅋㅋ 나와 같은 생각을...)

하지만 중복을 제외해달라고 하니 바로 접근 2의 답변을 제시했다.(똑똑하군)

접근 1을 수정하여 답을 내달라고 했더니 아주 복잡한 쿼리가...

궁금한 사람만 눌러보길 바란다.

order_id가 겹치는걸 제외하는 경우

WITH unique_orders AS (
  -- order_id가 한 번만 등장하는 것만 추출
  SELECT
    order_id
  FROM records
  GROUP BY order_id
  HAVING COUNT(*) = 1
)
SELECT
  r.region   AS Region,
  SUM(CASE WHEN r.category = 'Furniture'       THEN 1 ELSE 0 END) AS Furniture,
  SUM(CASE WHEN r.category = 'Office Supplies' THEN 1 ELSE 0 END) AS "Office Supplies",
  SUM(CASE WHEN r.category = 'Technology'      THEN 1 ELSE 0 END) AS Technology
FROM records r
JOIN unique_orders u
  ON r.order_id = u.order_id     -- 중복 없는 주문만 남김
GROUP BY r.region
ORDER BY r.region;

 

order_id, region, category 모두 겹치는 경우 제외하기(참고로 이 경우는 오답으로 나옴)

WITH dedup AS (
  SELECT DISTINCT order_id, region, category
  FROM records
)
SELECT
  region AS Region,
  SUM(CASE WHEN category = 'Furniture'       THEN 1 ELSE 0 END) AS Furniture,
  SUM(CASE WHEN category = 'Office Supplies' THEN 1 ELSE 0 END) AS "Office Supplies",
  SUM(CASE WHEN category = 'Technology'      THEN 1 ELSE 0 END) AS Technology
FROM dedup
GROUP BY region
ORDER BY region;

 

이상 첫번째 SQL 문제 풀이를 마친다. 앞으로 다양한 사이트의 다양한 문제를 리뷰할 테니 많은 관심 부탁!