select i.ingredient_type, sum(f.total_order) as TOTAL_ORDER
from first_half f left join icecream_info i on f.flavor = i.flavor
group by i.ingredient_type
order by f.total_order
역시, 간단한 문제였다
루시와 엘라 찾기
마찬가지로 간단한 문제인데,
여러 이름을 찾아야 하기 때문에
where name like 를 여러번 적는것 보다
where name in 을 써주면 간단하다.
select animal_id, name, sex_upon_intake
from animal_ins
where name in ('Lucy','Ella','Pickle','Rogan','Sabrina','Mitty')
order by 1
SELECT *
from food_orders fo inner join customers c on fo.customer_id = c.customer_id
두 table을 join 해준다
customers 의 내용이 null이라면 카운트할 이유가 없기 때문에 inner join을 써줌
10~59세만 해당하기 때문에 where 에서 걸러주고,
나이대를 case when 으로 나눠준다
SELECT cuisine_type,
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 sage
from food_orders fo inner join customers c on fo.customer_id = c.customer_id
where age >= 10 and age <= 59
when ~ 복붙을 할 때는 언제나 꼼꼼히 수정해야함
(case 도 같이 복사하면 즉시 에러)
그 다음 group by 1,2 로 음식별, 나이대별로 그룹정렬을 해준 뒤
count(1) 을 추가해서 음식점별, 나이대 별 주문 수량을 count
이후 서브쿼리로 넘어가서 pivot view를 구성해주면 마무리인데
이 부분에서 에러가 계속 나와서 엄청 헤맸다
메인쿼리 끝에 명칭지정을 안해주면 에러가 나는것임.. 왜 그런지는 모르겠지만
(메인쿼리 ) a 이 a가 꼭 필요
SELECT cuisine_type,
max(if(sage='10', count_order, 0)) "10대",
max(if(sage='20', count_order, 0)) "20대",
max(if(sage='30', count_order, 0)) "30대",
max(if(sage='40', count_order, 0)) "40대",
max(if(sage='50', count_order, 0)) "50대"
from
(
SELECT cuisine_type,
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 sage,
count(1) count_order
from food_orders fo inner join customers c on fo.customer_id = c.customer_id
where age >= 10 and age <= 59
group by 1, 2
) a
group by 1
CASE WHEN END : case when 조건1 then 값(수식)1 when 조건2 then 값(수식)2 else 값(수식)3 end
오늘 학습 내용
Subquery 를 활용하여 복잡한 연산을 수행한다
Join 을 활용하여 여러개의 테이블에 있는 데이터를 한 번에 조회하고 연산한다
Subquery 는 한 번에 하기 힘든 여러 연산을 차근 차근 풀어서 쓸 때 사용한다고 이해했다.
컬럼의 평균값(AVG) 을 구한다음에 조건에 따라 해당 평균값에 추가로 연산해야 하는 경우 라던지,
조건 자체를 연산의 결과로 하고 싶을 때 사용한다.
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
이 경우는,
restaurant_name 별로 quantity의 합, price의 합계를 먼저 구한다음
Subquery를 활용하여 추가로 합계된 값을 조건에 따라 case 로 나눠 표시해주도록 한 내용이다.
꽤 복잡하고 실습 과정에서 생각을 정리하는 시간이 길어졌다.
이해는 됐지만 정리가 제대로 안되었기 때문인데,
우선도를 파악하는 것이 중요했다.
제시된 문제에 따라 가장 먼저 mainquery에 기재할 내용과 이후 subquery 를 통해 마무리할 최종결과값을 나누어 생각해야한다.
그렇기 때문에, subquery를 잘 활용하기 위해선 문법보다는 우선 문제 자체를 해석하는 능력이 중요하다.
식당별 평균 음식 주문 금액과 주문자의 평균 연령을 기반으로 Segmentation 하기
- 평균 음식 주문 금액 기준 : 5,000 / 10,000 / 30,000 / 30,000 초과
- 평균 연령 : ~ 20대 / 30대 / 40대 / 50대 이상
위 문제를 보고 나는 10분 가량은 아무것도 할 수 없었다.
문제 자체를 이해를 못했기 때문이다...
(문제가 다소 불친절한 감도 있지만)
어디서 부터 시작해야 하지?
식당별 이니까, restaurant_name 을 group by 해야하고,
평균 음식 주문 금액은 avg(price), 평균 연령은 avg(age) 로 구해놓고 subquery 로 넘어간다.
subquery 에서 나머지 조건에 해당하는 부분을 case when 구문으로 나누어 표시되도록 하면 끝 !
SELECT restaurant_name,
avg_price,
avg_age,
case when avg_price <= 5000 then 'price_group1'
when avg_price > 5000 and avg_price <= 10000 then 'price_group2'
when avg_price > 10000 and avg_price <= 30000 then 'price group3'
when avg_price > 30000 then 'price group4' end 'price group',
case when avg_age < 30 then 'age_group1'
when avg_age >= 30 and avg_age < 40 then 'age_group2'
when avg_age >= 40 and avg_age < 50 then 'age_group3'
else 'age_group4' end 'age group'
FROM
(
SELECT f.restaurant_name ,
f.price,
c.age,
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
order by 1
) T
위와 같이 풀이 해 보았다. 잘 작동하는 듯 하다.
추가로, Join 에 대해서도 학습했는데,
한 테이블 내에서가 아닌, 여러 테이블에서 데이터를 가져와야 하는 경우 사용한다.
따라서 테이블을 불러오는 From 뒤에 사용되며,
from food_orders a left join customers b on a.customer_id=b.customer_id
위와 같이 From 메인 테이블 left join 불러올 테이블 on 메인 테이블.기준컬럼 = 불러울테이블.기준컬럼
