[실습] SQL로 Pivot Table 만들기

• 음식점별 시간별 주문 건수 Pivot Table 뷰 만들기
  • 15 ~ 20시 사이, 20시 주문 건수 기준 내림차순

SELECT 
	restaurant_name
	-- GROUP BY로 묶어주었기 때문에 집계함수 사용
	, MAX(IF(pay_time='15',cnt_orders,0)) '15'
	, MAX(IF(pay_time='16',cnt_orders,0)) '16'
	, MAX(IF(pay_time='17',cnt_orders,0)) '17'
	, MAX(IF(pay_time='18',cnt_orders,0)) '18'
	, MAX(IF(pay_time='19',cnt_orders,0)) '19'
	, MAX(IF(pay_time='20',cnt_orders,0)) '20'
FROM 
	(	-- 베이스 데이터 구성
		SELECT
			f.restaurant_name 
			, HOUR(p.time) pay_time
			, COUNT(1) cnt_orders
		FROM 
			food_orders f 
			LEFT JOIN payments p
			ON f.order_id = p.order_id
		WHERE HOUR(p.time) BETWEEN 15 AND 20
		GROUP BY 
			restaurant_name, pay_time) a
GROUP BY 1
-- alias = 숫자로 작성 → 억음 부호 사용
ORDER BY `20` DESC;

• 성별, 연령별 주문건수 Pivot Table 뷰 만들기
  • 나이는 10 ~ 59세 사이, 연령 순으로 내림차순

SELECT 
	age "나이"
	, MAX(IF(gender='male',order_cnt,0)) "남성"
	, MAX(IF(gender='female',order_cnt,0)) "여성"
FROM 
	(	-- 베이스 데이터 구성
	SELECT 
			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
			, c.gender 
			, COUNT(1) order_cnt
	FROM 
		food_orders f 
		LEFT JOIN customers c
		ON f.customer_id = c.customer_id 
	WHERE age BETWEEN 10 AND 59
	GROUP BY 1,2) a
GROUP BY 1
ORDER BY 1 DESC;

• COUNT( ) 공식 문서 설명

Window 함수 - RANK, SUM

• Window 함수
  • 각 행의 관계를 정의하기 위한 함수 → 그룹 내의 연산을 쉽게 할 수 있도록 도와줌

-- 기본 구조
WINDOW_FUNCTION(argument) OVER(PARTITION BY 그룹 기준 컬럼 ORDER BY 정렬 기준)

• RANK( ) OVER( )

  • ORDER BY 컬럼 기준으로 랭크 산정
  • [실습] 음식 타입 별로 주문 건수가 가장 많은 상점 3개씩 조회하기

  SELECT 
  	*
  FROM 
  (
  	SELECT 
  		RANK() OVER(PARTITION BY cuisine_type ORDER BY cnt_order DESC) ranking
  		, cuisine_type
  		, restaurant_name
  		, cnt_order
  	FROM(
  			SELECT 
  				cuisine_type 
  				, restaurant_name
  				, COUNT(*) cnt_order
  			FROM 
  				food_orders 
  			GROUP BY 2)a
  )b
  WHERE ranking <=3;
  

• SUM( ) OVER( )

  • 전체에서 차지하는 비율, 누적합을 구할 때 사용
  • [실습] 각 음식의 주문 건이 해당 음식 타입에서 차지하는 비율을 구하고, 주문 건이 낮은 순으로 정렬했을 때 누적합

  SELECT
  	cuisine_type
  	, restaurant_name
  	, cnt_orders
  	 -- 카테고리별 주문 수 합계 구하기
  	, SUM(cnt_orders) 
  		OVER(PARTITION BY cuisine_type) sum_cuisine
  		-- 카테고리별 누적합 구하기
  	, SUM(cnt_orders) 
  		OVER(PARTITION BY cuisine_type ORDER BY cnt_orders) cum_quisine
  FROM 
  (
  	SELECT 
  		cuisine_type 
  		, restaurant_name
  		, COUNT(*) cnt_orders
  	FROM 
  		food_orders fo 
  	GROUP BY 1,2
  )a
  ORDER BY 
  	cuisine_type, cnt_orders;
  

포맷 함수

1. 날짜 데이터의 이해

   • 문자 타입, 숫자 타입과 같이 날짜 데이터도 특정한 타입을 가지고 있음
   • 년, 월, 일, 시, 분, 초 등의 값을 모두 가지고 있으며 목적에 따라 ‘월’, ‘주’, ‘일’ 등으로 포맷을 변경할 수도 있음

2. [실습] 날짜 데이터의 여러 포맷

   select date(date) date_type,
          date_format(date(date), '%Y') "년",
          date_format(date(date), '%m') "월",
          date_format(date(date), '%d') "일",
          # 일요일은 0, 월요일은 1
          date_format(date(date), '%w') "요일"
   from payments
   

3. [실습] 년도별 3월의 주문건수

   SELECT 
   	DATE_FORMAT(date, '%Y-%m') change_date
   	, COUNT(*) cnt_orders
   FROM 
   	food_orders f 
   	INNER JOIN payments p 
   	ON f.order_id = p.order_id 
   WHERE DATE_FORMAT(date, '%Y-%m') LIKE '%03'
   GROUP BY 1
   ORDER BY 1;