[TIL] 데이터 분석 사전캠프 6일차 - JOIN 실습

JOIN 문법

-- LEFT JOIN
select 조회 할 컬럼
from 테이블1 a left join 테이블2 b on a.공통컬럼명=b.공통컬럼명

-- INNER JOIN
select 조회 할 컬럼
from 테이블1 a inner join 테이블2 b on a.공통컬럼명=b.공통컬럼명

select distinct a,b,c : a의 중복값을 없애는 것이 아니라 abc 조합의 중복값을 없앰

출처: 스파르타 코딩클럽

이렇게 하면 정답~ 인데 코드 정리가 좀 필요해보임

select 음식타입,
sum(가격)"원래가격",
sum(가격*경로할인율)"할인가격",
sum(가격)-sum(가격*경로할인율)"할인적용가격"
from
(
select fo.cuisine_type "음식타입",
fo.price "가격",
a.할인율 "경로할인율"
from food_orders fo left join 
(
select customer_id ,age,
case when age>=50 then (age-50)*0.005 else 0 end "할인율" from  customers c) a
on fo.customer_id = a.customer_id)b
group by 음식타입
order by sum(가격*경로할인율);

 

select restaurant_name,
       case when price <=5000 then 'price_group1'
            when price >5000 and price <=10000 then 'price_group2'
            when price >10000 and price <=30000 then 'price_group3'
            when price >30000 then 'price_group4' end price_group,
       case when age <30 then 'age_group1'
            when age between 30 and 39 then 'age_group2'
            when age between 40 and 49 then 'age_group3'
            else 'age_group4' end age_group
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

집계함수는 group by절과 함께 사용