[TIL] MySQL Window Function과 Date타입 포맷팅 (24-12-18)

🤖 사전 캠프 8일차 진행 사항 🤖

• 엑셀보다 쉽고 빠른 SQL 5주차 수업
  • 5-1 ~ 5-6
  • 숙제

---

엑셀보다 쉽고 빠른 SQL 5주차 수업

1. Window Function

Window Function 의 기본 구조

window_function(argument) over (partition by 그룹 기준 컬럼 order by 정렬 기준)

•  window_function : 기능 명 정의
• argument : 함수에 따라 작성하거나 생략
• partition by : 그룹을 나누기 위한 기준(group by와 유사)
• order by : window function을 적용할 때 정렬할 컬럼 기준

---

• [실습] 음식 타입별로 주문 건수가 가장 많은 상점 3개씩 조회하기
  • 음식 타입, 음식 주문 별 주문 건수 집계
  • rank 함수 적용
  • 3위까지 조회

select	cuisine_type,
	restaurant_name,
	cnt_order,
	ranking
from 
(
	select 	cuisine_type,
		restaurant_name,
		cnt_order,
		rank() over (partition by cuisine_type order by cnt_order desc) as ranking
	from 
	(
		select	cuisine_type,
			restaurant_name,
			count(*) as cnt_order
		from food_orders
		group by cuisine_type, restaurant_name 
	) a
) b
where ranking <= 3

---

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

select 	cuisine_type,
		restaurant_name,
		cnt_order,
		sum(cnt_order) over (partition by cuisine_type) as sum_cuisine,
		sum(cnt_order) over (partition by cuisine_type order by cnt_order) as cum_cuisine  
from 
(
	select	cuisine_type,
		restaurant_name,
		count(*) as cnt_order
	from food_orders
	group by cuisine_type, restaurant_name 
) a
order by cuisine_type, cnt_order

2. date type

2-1. date()

 varchar 타입의 date 컬럼을 date()함수를 통해 date 타입으로 변경하여 출력할 수 있다.

 

2-2. date_format()

date 타입의 컬럼을 포맷팅할 수 있다.

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

---

• [실습] 음식 주문에서 3월의 주문 건수 구하고 연도별 정렬

select date_format(date(date), '%Y') "년",
       date_format(date(date), '%m') "월",
       date_format(date(date), '%Y-%m') "년월",
       count(1) "주문건수"
from food_orders a inner join payments b on a.order_id=b.order_id
where date_format(date(date), '%m')='03'
group by 1, 2
order by 1

3. 숙제

음식 타입별, 연령별 주문건수 pivot view 만들기 (연령은 10~59세 사이)

1. SQL 기본구조 작성하기
2. Pivot view 를 만들기 위해 필요한 데이터 가공하기
3. Pivot view 문법에 맞추어 수정하기

SELECT	cuisine_type,
	MAX(IF(age = 10, order_cnt, 0)) AS "10대",
	MAX(IF(age = 20, order_cnt, 0)) AS "20대",
	MAX(IF(age = 30, order_cnt, 0)) AS "30대",
	MAX(IF(age = 40, order_cnt, 0)) AS "40대",
	MAX(IF(age = 50, order_cnt, 0)) AS "50대"
FROM
(
	SELECT	cuisine_type,
		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 AS age,
		COUNT(*) AS order_cnt
	FROM food_orders f inner join customers c on f.customer_id = c.customer_id
	WHERE age BETWEEN 10 and 59
	GROUP BY 1, 2
) a
GROUP BY 1