[SQL 문제풀이] 4. SQL로 Pivot Table 구현

wakelight23 2024. 10. 17. 18:21

Pivot Table?

● 2개 이상의 기준으로 데이터를 집계할 때, 보기 쉽게 배열해서 나타내는 것

- 기본 구조

	구분 칼럼
집계 기준	데이터

- 예시

집계기준 : 일자, 시간

	1시 (구분 칼럼)	2시	3시	4시
10월 1일(집계 기준)	4 (데이터)	2	4	7
10월 2일	5	10	5	8
10월 3일	7	3	6	9

● 문제 : 음식점별 시간별 주문건수 Pivot Table 뷰 만들기

(조건 : 15~20시 사이, 20시 주문건수 기준 내림차순 정렬)

예시 TABEL 1 : 주문정보 / TABEL 2 : 계산정보

	주문번호	음식점명	주문시간
1	1001	식스맨	15:12
2	1002	피자리아	16:13
3	1003	스케스시	17:30
4	1004	단리향	18:20
5	1005	식스맨	16:36
6	1006	스케스시	19:20
7	1007	식스맨	20:30
8	1008	식스맨	15:44

1. 음식점별, 시간별 주문건수 집계

SELECT a.음식점명,
SUBSTR(b.주문시간, 1, 2) hh,
COUNT(1) cnt_order
FROM 주문정보 a INNER JOIN 계산정보 b ON a.주문번호=b.주문번호
WHERE SUBSTR(b.주문시간, 1, 2) BETWEEN 15 and 20
GROUP BY 1, 2

- 우선 음식점명, 주문시간, 주문건수를 집계할 SubQuery문 작성

- SUBSTR로 '분'은 없애고 '시'만 남기기

- WHERE 절로 15시에서 20시 사이만 조회

-결과물

	음식점명	주문시간	cnt_order
1	식스맨	15	2
2	피자리아	16	1
3	스케스시	17	1
4	단리향	18	1
5	식스맨	16	1
6	스케스시	19	1
7	식스맨	20	1

2. Pivot view 구조 만들기

SELECT 음식점명,
MAX(IF(hh='15', cnt_order, 0)) "15",
MAX(IF(hh='16', cnt_order, 0)) "16",
MAX(IF(hh='17', cnt_order, 0)) "17",
MAX(IF(hh='18', cnt_order, 0)) "18",
MAX(IF(hh='19', cnt_order, 0)) "19",
MAX(IF(hh='20', cnt_order, 0)) "20"
FROM (
   SELECT a.음식점명,
   SUBSTR(b.주문시간 1, 2) hh,
   COUNT(1) cnt_order
   FROM 주문정보 a INNER JOIN 계산정보 b on a.주문번호=b.주문번호
   WHERE SUBSTR(b.주문시간, 1, 2) BETWEEN 15 and 20
   GROUP bY 1, 2
   ) a
GROUP BY 1
ORDER BY 7 DESC

- 음식점별 시간별로 분류된 값의 최대값을 Pivot Table에 작성

- SubQuery로 필요한 정보 정리

- 음식점명 기준으로 조회

- 20시 기준으로 ORDER BY 7(7번째 칼럼) DESC 로 내림차순 정렬

-결과물

	음식점명	15	16	17	18	19	20
1	식스맨	2	1	0	0	0	1
2	피자리아	0	1	0	0	0	0
3	스케스시	0	0	1	0	1	0
4	단리향	0	0	0	1	0	0