스파르타 AI-8기 TIL(9/25)
When doing multiple operations at once, I must use Subquery.
Ex.
select price/quantity
from
(
select price, quantity
from food_orders fo
) a
It's the same as
select price/quantity
from food_orders fo
but, it's just an example
first, It will firstly get the data from the () statement which is
(
select price, quantity
from food_orders fo
) a -> I must give a name of the () statement which is 'a'(it doesn't have to be 'a' all the time.)
then it will get price and quantity data from food_orders fo
Then it will automatically use it again through select price/quantity statement
So, I made my code
select if(price_rate < 0, 'cheap', 'expensive') price_rate
from
(
select price - 15000 price_rate
from food_orders fo
) a
First, it will get data from the () statement which is
Select price - 15000 -> price_rate = It will deduct 15000 for each piece of data.
Then if(price_rate < 0, 'cheap', 'expensive') price_rate
-> It shows the data if the data is lower or higher than 0
I had to make a little complex problem
Calculate the total order quantity and order amount of the restaurant, and calculate the commission discount rate based on the order quantity
(Discount conditions
Quantity 5 or less → 10%
Quantity more than 15, total order amount 300,000 or more → 0.5%
Otherwise, 1%)
select restaurant_name,
total_quantity,
total_price,
case when total_quantity <= 5 then 10
when total_quantity > 15 and total_price >= 300000 then 0.05
else 0.01 end '수수료'
from
(select restaurant_name,
sum(quantity) total_quantity,
sum(price) total_price
from food_orders fo
group by 1
) a
I did it by myself
And the answer was this....there are some points I need to change (10 -> 0.1, 0.05 -> 0.005)
select restaurant_name,
case when sum_of_quantity<=5 then 0.1
when sum_of_quantity>15 and sum_of_price>=300000 then 0.005
else 0.01 end ratio_of_add
from
(
select restaurant_name,
sum(quantity) sum_of_quantity,
sum(price) sum_of_price
from food_orders
group by 1
) a
And the answer was this....there are some points I need to change (10 -> 0.1, 0.05 -> 0.005)
When the necessary data is located in another table(JOIN)
It's like connecting the tables based on the join data
LEFT JOIN -> Based on the joint column, even if there is no data on one table, It will get all the left data from the table
-> (there are 2 tables, even if there is no data on table 1, It will get all the data from table 2(as well as the joint data)
INNER JOIN -> Only the joint data will be printed
Ex.
Table 1 -> data 1,2,3,4,5
Table 2 -> data 1,2,3,4
Left JOIN -> get data 1,2,3,4,5 (based on the left table)
INNER JOIN -> get data 1,2,3,4
Ex.
select *
from food_orders left join payments on food_orders.order_id=payments.order_id
food_orders -> left table, which means, it will get the all data from food_orders table as well as joint data with payments tables
on -> specify which column I want to paste it into.
food_orders.order_id = payments.order_id-> meaning that I'm going to use order_id column in food_orders and payments tables.
Frame -> (name of table 1) left(inner) join (name of table 2) on (name of table 1).(joint column) = (name of table 2).(joint column)
Tips -> The name of the table can be changed
Ex.
select *
from food_orders f left join payments d on f.order_id=d.order_id
Also,
Must specify the column where I got it.
select
A.order_id,
A.customer_id,
A.restaurant_name,
A.price,
B.name,
B.age,
B.gender
from food_orders A left join customers B on A.customer_id = B.customer_id
(name of the table).(name of the column) -> This is how to get the column in JOIN
When there is a specific condition, I can use it like
select distinct A.order_id, -> (distinct all the chosen data)
A.customer_id,
A.restaurant_name,
A.price,
B.name,
B.age,
B.gender
from food_orders A left join customers B on A.customer_id = B.customer_id
order by A.order_id
As well as Arithmetic operations
select distinct A.order_id, -> (distinct all the chosen data)
A.customer_id,
A.restaurant_name,
A.price * B.age total,
B.name,
B.gender
from food_orders A left join customers B on A.customer_id = B.customer_id
order by A.order_id
H.W
1. Segmentation based on average food order amount and average age of customers per restaurant
Average food order amount: 5,000 / 10,000 / 30,000 / Over 30,000
Average age: ~ 20s / 30s / 40s / 50s and above
Retrieve only when data is available in both tables, sorted in ascending order by restaurant name
1. must use inner join
2. must use subquery
result:
select restaurant_name,
case when avg_age < 30 then '20대'
when avg_age < 40 then '30대'
when avg_age < 50 then '40대'
else '고령' end age_line,
case when avg_price <= 5000 then 'price_1'
when avg_price > 5000 and avg_price <= 10000 then 'price_2'
when avg_price > 10000 and avg_price <=30000 then 'price_3'
else 'price_4' end 'price_group'
from
(select F.restaurant_name,
avg(F.price) avg_price,
avg(C.age) avg_age
from food_orders F inner join customers C on F.customer_id = C.customer_id
group by 1
) a
order by 1
Good night!