SQL 3탄 실전편
배운 코딩 적용시키기
SQL 코딩에 대해서 기초를 배웠으니, 아래 과제를 같이 한 번 풀어보자!
실습1번
음식점별 / 시간별 / 주문건수 Pivot Table뷰 만들기
(15~20시 사이, 20시 주문건수 기준 내림차순)
select rest,
총주문수,
max(if(주문시간='15', 총주문수, 0)) "15",
max(if(주문시간='16', 총주문수, 0)) "16",
max(if(주문시간='17', 총주문수, 0)) "17",
max(if(주문시간='18', 총주문수, 0)) "18",
max(if(주문시간='19', 총주문수, 0)) "19",
max(if(주문시간='20', 총주문수, 0)) "20"
from
(
select rest,
주문시간,
sum(orders) as "총주문수"
from
(
select f.restaurant_name as rest,
p.time as time,
count(1) as orders,
cast(substr(p.time,1,2) as unsigned) as "주문시간"
from food_orders f inner join payments p on f.order_id=p.order_id
group by 1, 2
) a
group by 1
) b
group by 1, 2
실습2번
성별, 연령별 주문건수 pivot table 뷰 만들기
(나이는 10~59세 사이, 연령순으로 내림차순)
select gender, 총주문수,
if(age between 10 and 19, 총주문수,0) as "10대_구매량",
if(age between 20 and 29, 총주문수, 0) as "20대_구매량",
if(age between 30 and 39, 총주문수, 0) as "30대_구매량",
if(age between 40 and 49, 총주문수, 0) as "40대_구매량",
if(age between 50 and 59, 총주문수, 0) as "50대_구매량"
from
(
select c.gender gender,
c.age age,
count(*) as "총주문수"
from food_orders f left join customers c on f.customer_id=c.customer_id
where c.age between 10 and 59
group by 1, 2
order by c.age DESC
) a
실습3번.
음식 타입별로, 음식점별 주문 건수 가장 많은 상점 3개씩 집계
select rest,
cuisine_type,
count_order,
ranking
from
(
select rest,
cuisine_type,
count_order,
rank()over (partition by cuisine_type order by count_order desc) ranking
from
(
select restaurant_name rest,
cuisine_type,
count(1) count_order
from food_orders
group by 1, 2
) a
group by 1, 2, 3
) b
where ranking<=3
group by 1, 2
실습4번.
각 음식점의 주문건이 / 해당 음식 타입에서/ 차지하는 비율을 구하고, 주문건이 낮은 순으로 정렬했을 때, 누적 합 구하기.
select restaurant_name,
cuisine_type,
count_order,
sum(count_order) over (partition by cuisine_type) as "요리별_합계",
sum(count_order) over (partition by cuisine_type order by count_order) as "누적오더"
from
(
select restaurant_name,
cuisine_type,
count(1) count_order
from food_orders
group by 1, 2
) a
group by 1, 2
실습 5번.
년도별 3월 총주문수 구하기
정답:
select 년, 월, 년월, count_order
from
(
select date_format(date(date), '%Y') "년",
date_format(date(date), '%m') "월",
date_format(date(date), '%Y%m') "년월",
count(1) count_order
from payments
group by 1, 2, 3
order by date_format(date(date), '%Y') desc
) a
where 월=3
실습 5번.
음식타입별, 연령별 주문건수 pivot view만들기
(연령은 10~59세 사이)
select f.cuisine_type,
if(c.age between 10 and 19, count(1), 0) "10대",
if(c.age between 20 and 29, count(1), 0) "20대",
if(c.age between 30 and 39, count(1), 0) "30대",
if(c.age between 40 and 49, count(1), 0) "40대",
if(c.age between 50 and 59, count(1), 0) "50대",
count(1) count_order
from food_orders f inner join customers c on f.customer_id=c.customer_id
where c.age between 10 and 59
group by 1
