Day 8 Today I Learned

오늘 배운 내용

엑셀보다 쉽고 빠른 SQL

5-1 Subquery, JOIN 복습하고, 이번 수업 내용 맛보기

5-2 조회한 데이터에 아무 값이 없다면 어떻게 해야할까?

1. 없는 값을 제외해주기

select restaurant_name,
		avg(rating) avg_rating,
		avg(if(rating<>'Not given', rating, null)) avg_rating2
from food_orders
group by 1

2. 다른 값을 대신 사용하기

select a.order_id,
		a.customer_id,
		a.price,
		b.name,
		b.age,
		coalesce(b.age, 30) "null 제거",
		b.gender
from food_orders a left join customers b on a.customer_id = b.customer_id 
where b.age is null

5-3 조회한 데이터가 상식적이지 않은 값을 가지고 있다면 어떻게 해야할까?

select name,
		age,
		case when age < 15 then 15
		when age >= 80 then 80
		else age end re_age
from customers

5-4 SQL로 Pivot Table 만들어보기

select restaurant_name,
		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 f.restaurant_name,
		substr(p.time, 1, 2) hh,
		count(1) cnt_order
from food_orders f inner join payments p on f.order_id = p.order_id
where substr(p.time, 1, 2) between 15 and 20
group by 1, 2 
) a
group by 1
order by 7 DESC

max 왜 쓰는지 모르겠음

select age,
		max(if(gender='male', cnt_order,0)) "male",
		max(if(gender='female', cnt_order, 0)) "female"
from
(
select 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 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
order by 1 desc

5-5 업무 시작을 단축시켜 주는 마법의 문법

rank() over : 랭킹 구하기

select cuisine_type,
		restaurant_name,
		cnt_order,
		rank() over (partition by cuisine_type order by cnt_order desc) ranking
from 
(
select cuisine_type,
		restaurant_name,
		count(1) cnt_order
from food_orders
group by 1, 2 
) a

sum() over : 파티션별 합, 누적합

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

5-6 날짜 포맷과 조건까지 SQL 로 한 번에 끝내기

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

 

느낀점

일단 이걸로 SQL의 기초 강의는 다 들었다. 뒷부분에 가서는 이해가 안가는 부분도 약간 있었지만 문제를 푸는데는 문제가 없을 것 같다.