3일차 (24-06-26) TIL 학습기록

 

오늘 목표

• SQL 4강
• SQL 5강
• 코드카타 2개 (level 4)

---

강의 내용

서브쿼리, 조인 활용법

SELECT		restaurant_name
			,total_q
			,total_p
			,CASE WHEN total_q <=5 THEN 0.1
			WHEN total_q >15 AND total_p >=300000 THEN 0.005
			ELSE 0.01
			END rate
FROM
(SELECT		restaurant_name
			,SUM(quantity) total_q
			,SUM(price) total_p 
FROM		food_orders fo 
GROUP BY	1) a


SELECT 		*
FROM 		orders o 
LEFT
JOIN		customers c 
ON			(o.user_id = c.customer_id)


#1) [실습] 한국 음식의 주문별 결제 수단과 수수료율을 조회하기
SELECT  price
		,vat
FROM 	food_orders fo
JOIN	payments p 
USING	(order_id)
WHERE	cuisine_type = 'Korean'


#2) [실습] 고객의 주문 식당 조회하기

SELECT		DISTINCT 
			name
			,age
			,gender
			,restaurant_name
FROM		customers c 
JOIN		food_orders fo 
USING		(customer_id)


#1) [실습] 주문 가격과 수수료율을 곱하여 주문별 수수료 구하기

SELECT  price*vat
FROM 	food_orders fo
LEFT
JOIN	payments p 
USING	(order_id)
WHERE	cuisine_type = 'Korean'


#1) [실습] 주문 가격과 수수료율을 곱하여 주문별 수수료 구하기

SELECT 		order_id
			,cuisine_type
			,age
			,price 
			,discount
			,(1-discount)*price 
FROM 		food_orders fo 
JOIN		(SELECT  	customer_id 
			,age
			,CASE WHEN age >= 50 THEN (age-50)*0.005
			 ELSE 0 END 'discount'
			FROM 		customers c ) a
USING		(CUSTOMER_ID)
ORDER BY	discount DESC
#서브쿼리와의 JOIN도 가능

 

 

#4주차 숙제
SELECT 		DISTINCT restaurant_name
			,CASE WHEN price <= 5000 THEN 1
				 WHEN 5000<price AND price <= 10000 THEN 2
				 WHEN 10000<price AND price <= 30000 THEN 3
				 ELSE 4 END 'price_num'
			,CASE WHEN age<30 THEN 1
				  WHEN 30<=age AND age<40 THEN 2
				  WHEN 40<=age AND age<50 THEN 3
				  ELSE 4 END 'age_num'
FROM 		
(
SELECT a.restaurant_name,
       avg(price) price,
       avg(age) age
FROM food_orders a INNER JOIN customers b ON a.customer_id=b.customer_id
GROUP BY 1
) t
ORDER BY 1

 

 

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


SELECT 	name,
		age,
		CASE WHEN age<15 THEN 15
			 WHEN age>=80 THEN 80
			 ELSE AGE END re_age
FROM	customers c

 

 

 

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

 

 

PARTITION BY

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 fo 
INNER
JOIN	customers c 
ON		fo.customer_id = c.customer_id 
GROUP BY 1, 2) a
GROUP BY 1
ORDER BY 1 DESC

 

 

#5주차 숙제
SELECT		cuisine_type
			,MAX(if(age_seg = 10, cnt, 0)) "10대"
			,MAX(if(age_seg = 20, cnt, 0)) "20대"
			,MAX(if(age_seg = 30, cnt, 0)) "30대"
			,MAX(if(age_seg = 40, cnt, 0)) "40대"
			,MAX(if(age_seg = 50, cnt, 0)) "50대"
FROM
(SELECT 		cuisine_type
			,CASE WHEN age>=10 AND age<20 THEN 10
				 WHEN age>=20 AND age<30 THEN 20
				 WHEN age>=30 AND age<40 THEN 30
				 WHEN age>=40 AND age<50 THEN 40
				 ELSE 50 END 'age_seg'
			,COUNT(1) cnt 
FROM 		customers c 
INNER JOIN	food_orders fo
USING		(customer_id)
GROUP BY 	1,2) a
GROUP BY 1

 

 

 

04. MySQL에서 백틱(`)과 따옴표(’’, “”)의 사용

 💡 MySQL에서 어떨 때 백틱, 따옴표를 사용할까요?

• ☑️ 백틱을 이용해 식별자를 감싸요.
  • 기본적으로 MySQL에서 백틱(`)은 주로 데이터베이스, 테이블, 컬럼 이름 등 객체의 이름을 감쌀 때 사용합니다.
  • 객체 이름이 MySQL의 예약어이거나, 공백이나 특수문자를 포함하는 경우에는 사용해야합니다. (예약어 목록:링크)
  • MySQL의 예약어를 객체의 이름으로 지정하는 것은 지양하는 것이 좋지만, 불가피하게 사용해야할 경우엔 백틱을 사용해서 구문 오류(syntax error)를 피해 주시면 됩니다.

   -> mysql이 파악해서 에러가 안 날 수도 있지만, 웬만하면 써두는 게 좋음