SQL | Pivot Table 만들기 | 실습 문제

 

0. SQL로 Pivot Table (피벗테이블) 만들기

 

피벗 테이블을 사용하면 데이터를 한 눈에 쉽게 볼 수 있다는 장점이 있다.

컴퓨터 활용능력 1급 실기 시험을 준비하면서 엑셀에서도 구현해봤던 기억이 나는데

이와 같은 것을 SQL에서도 구현할 수 있다.

 

뭔가 피벗 테이블 하면 왠지 좀 어려울 것 같은 느낌이 드는 분들도 있을 텐데 (← 는 사실 제 얘기..)

이번 기회에 피벗 테이블에 대한 개념을 확실히 잡아보자. 

 

---

 

1. Pivot Table이란?

: 2개 이상의 기준으로 데이터를 집계할 때, 보기 쉽게 배열하여 보여주는 것

: 한 컬럼 안에 있던 데이터 값들을 열로 지정해 주는 것

 

▶ Pivot Table의 기본구조

: 밑에 제가 만들어둔 2. 실습문제 이미지 보면 확실히 이해 가능!!

 

 

[참고 게시글]

▶ 피벗 테이블 설명이 잘 되어있는 게시글 소개

https://im-first-rate.tistory.com/130

[SQL] PIVOT에 대해 알아보고, 꿀팁도 얻어가자.

>> 이 게시글 보면 피벗 테이블 만들 때 왜 MAX / MIN과 SUM 사용하는지 알 수 있음

- 피벗 테이블의 행과 열에 해당하는 값이 1개일 때는 MAX / MIN

- 피벗 테이블에 행과 열에 해당하는 값이 여러 개일 때는 SUM

---

2. Pivot Table(피벗 테이블) 실습문제

 

문제1) 성별, 연령 별 주문 건수 Pivot Table 뷰 만들기

- 나이는 10~59세 사이

- 연령 순으로 내림차순

 

① 성별, 연령 별 주문 건수 집계하기

SELECT c.gender,
       CASE WHEN age BETWEEN 10 AND 19 THEN 10
            WHEN age BETWEEN 20 AND 29 THEN 20
            WHEN age BETWEEN 30 AND 39 THEN 30
            WHEN age BETWEEN 40 AND 49 THEN 40
            WHEN age BETWEEN 50 AND 59 THEN 50
       END age,
       COUNT(1) cnt_order
FROM customers c 
INNER JOIN food_orders fo ON c.customer_id = fo.customer_id
WHERE age BETWEEN 10 AND 59
Group By 1, 2

 

② Pivot Table  구조 만들기

피벗테이블 원리

SELECT age,
       max(if(gender='male', cnt_order, 0)) male,
       max(if(gender='female', cnt_order, 0)) female
FROM 
(SELECT c.gender,
       CASE WHEN age BETWEEN 10 AND 19 THEN 10
            WHEN age BETWEEN 20 AND 29 THEN 20
            WHEN age BETWEEN 30 AND 39 THEN 30
            WHEN age BETWEEN 40 AND 49 THEN 40
            WHEN age BETWEEN 50 AND 59 THEN 50
       END age,
       COUNT(1) cnt_order
FROM customers c 
INNER JOIN food_orders fo ON c.customer_id = fo.customer_id
WHERE age BETWEEN 10 AND 59
Group By 1, 2) a
Group By 1
Order By 1

 

---

 

문제2) 음식점 별, 시간 별 주문 건수 Pivot Table 뷰 만들기

- 주문 시간 15~20시 사이

- 주문 건수 기준 내림차순

 

① 음식점 별, 시간 별 주문 건수 집계하기

SELECT fo.restaurant_name ,
       SUBSTR(p.time, 1, 2) h_time,
       COUNT(1) cnt_order
FROM food_orders fo 
INNER JOIN payments p ON fo.order_id = p.order_id
WHERE SUBSTR(p.time, 1, 2) BETWEEN 15 AND 20
Group By 1, 2

 

② Pivot Table 구조 만들기

피벗테이블 원리

SELECT restaurant_name,
       max(if(h_time='15', cnt_order, 0)) "15",
       max(if(h_time='16', cnt_order, 0)) "16",
       max(if(h_time='17', cnt_order, 0)) "17",
       max(if(h_time='18', cnt_order, 0)) "18",
       max(if(h_time='19', cnt_order, 0)) "19",
       max(if(h_time='20', cnt_order, 0)) "20"
FROM
(SELECT fo.restaurant_name ,
       SUBSTR(p.time, 1, 2) h_time,
       COUNT(1) cnt_order
FROM food_orders fo 
INNER JOIN payments p ON fo.order_id = p.order_id
WHERE SUBSTR(p.time, 1, 2) BETWEEN 15 AND 20
Group By 1, 2) a
Group By 1