오늘 배운 내용
엑셀보다 쉽고 빠른 SQL
3-5 SQL로 간단한 User segmentation 해보기
select case when (age between 10 and 19) and gender='male' then '10대 남성'
when (age between 10 and 19) and gender='female' then '10대 여성'
when (age between 20 and 29) and gender='male' then '20대 남성'
when (age between 20 and 29) and gender='female' then '20대 여성' end "고객 분류",
name,
age,
gender
from customers
where age between 10 and 29
3-6 조건문으로 서로 다른 식을 적용한 수수료 구해보기
select case when delivery_time > 30 then price*0.1*if(addr like '%서울%', 1.1, 1)
when delivery_time between 26 and 30 then price*0.05*if(addr like '%서울%', 1.1, 1)
else 0 end "수수료",
restaurant_name,
order_id,
price,
delivery_time,
addr
from food_orders
3-7 SQL문에 문제가 없는 것 같은데 왜 오류가 나나요?
data type 오류
숙제
select order_id,
restaurant_name,
day_of_the_week,
delivery_time,
case when day_of_the_week = 'Weekday' then if(delivery_time < 25, 'On-time', 'Late')
when day_of_the_week = 'Weekend' then if(delivery_time < 30, 'On-time', 'Late') end "지연여부"
from food_orders
4-1 포맷 변경과 조건문 복습하고, 이번 수업 내용 맛보기
4-2 여러번의 연산을 한 번의 SQL 문으로 수행하기
select order_id, restaurant_name, if(over_time>=0, over_time, 0) over_time
from
(
select order_id, restaurant_name, food_preparation_time-25 over_time
from food_orders
) a
4-3 User segmentation 와 조건별 수수료를 subquery로 결합해 보기
select restaurant_name,
price_per_plate*ratio_of_add "수수료"
from
(
select restaurant_name,
case when price_per_plate<5000 then 0.005
when price_per_plate between 5000 and 19999 then 0.01
when price_per_plate between 20000 and 29999 then 0.02
else 0.03 end ratio_of_add,
price_per_plate
from
(
select restaurant_name, avg(price/quantity) price_per_plate
from food_orders
group by 1
) a
) b
4-4 복잡한 연산을 Subquery로 수행하기
select restaurant_name,
sum_price,
sum_quantity,
case when sum_quantity<=5 then 0.1
when sum_quantity>15 and sum_price>=300000 then 0.005
else 0.01 end discount_rate
from
(
select restaurant_name,
sum(price) sum_price,
sum(quantity) sum_quantity
from food_orders
group by 1
) a
4-5 필요한 데이터가 서로 다른 테이블에 있을 때 조회하기
JOIN : 테이블 합치기
select *
from food_orders left join payments on food_orders.order_id=payments.order_id
4-6 JOIN으로 두 테이블의 데이터 조회하기
SELECT distinct c.name,
c.age,
c.gender,
f.restaurant_name
from food_orders f left join customers c on f.customer_id = c.customer_id
order by c.name
4-7 JOIN으로 두 테이블의 값을 연산하기
select cuisine_type,
sum(price) price,
sum(price*discount_rate) discounted_price
from
(
select f.cuisine_type,
f.price,
c.age,
(c.age-50)*0.005 discount_rate
from food_orders f left join customers c on f.customer_id = c.customer_id
where c.age >= 50
) a
group by 1
order by 3 desc
'스파르타코딩클럽' 카테고리의 다른 글
1) 돈을 벌기 위해 일을 합시다! (2) | 2024.09.26 |
---|---|
Day 8 Today I Learned (1) | 2024.09.26 |
Day 6 Today I Learned (2) (1) | 2024.09.24 |
Day 6 Today I Learned (0) | 2024.09.24 |
Day 5 Today I Learned (1) | 2024.09.23 |