사전캠프 10일차

랭킹

 

음식타입별로 주문건수가 가장많은 상점 3개씩 조회

select cuisine_type, restaurant_name,cnt_order,ranking

from(

select cuisine_type,restaurant_name,cnt_order,

rank() over (partition by cuisine_type order by cnt_order desc) ranking

from

(

select cuisine_type,restaurant_name,count(1) cnt_order

from food_orders

group by 1,2

)a

)b

where ranking<=3

 

누적합

sum(cnt_order) over (partition by cuisine_type)

cuisine_type컬럼에서 같은 애들(american 등)끼리 cnt_order의 합을 구한다

sum(cnt_order) over (partition by cuisine_type order by cnt_order) 

cuisine_type컬럼에서 같은 애들(american 등)끼리 cnt_order의 누적합을 구한다 sum over partition by order by 

order by 들어가면 누적합임.

 

날짜데이터

SELECT cuisine_type,ages,COUNT(2),

max

(

if

(ages=10대,cnt_order,0))

"10대"

,

max(if(ages=20대,cnt_order,0 )) "20대",

max(if(ages=30대,cnt_order,0 )) "30대",

max(if(ages=40대,cnt_order,0 )) "40대",

max(if(ages=50대,cnt_order,0 )) "50대"

FROM

(select cuisine_type,age,cnt_order,

case when age>=10 and age<20 then '10대'

when age>=20 and age<30 then '20대'

when age>=30 and age<40 then '30대'

when age>=40 and age<50 then '40대'

when age>=50 and age<60 then '50대'

ELSE 0 end ages

FROM

(select cuisine_type,age,count(1) cnt_order

from food_orders f inner join customers c on f.customer_id =c.customer_id

group by 1,2)a

)b

group by 1

order by 1

 

과제

SELECT cuisine_type,

sum(

if

(

ages

=

'10대'

,

cnt_order

,

0

))

"10대"

,

sum(if(ages='20대',cnt_order,0)) "20대",

sum(if(ages='30대',cnt_order,0)) "30대",

sum(if(ages='40대',cnt_order,0)) "40대",

sum(if(ages='50대',cnt_order,0)) "50대"

FROM

(select cuisine_type,age,cnt_order,

case when age>=10 and age<20 then '10대'

when age>=20 and age<30 then '20대'

when age>=30 and age<40 then '30대'

when age>=40 and age<50 then '40대'

when age>=50 and age<60 then '50대'

ELSE age end ages

FROM

(select cuisine_type,age,count(1) cnt_order

from food_orders f inner join customers c on f.customer_id =c.customer_id

group by 1,2

)a

)b

group by 1

order by 1