11일차_엑셀보다 쉽고 빠른 SQL 5-5 ~ 5-7 실무형 SQL(Window Function: Rank, Sum)

윈도우 함수란 하나의 행을 하나의 그룹으로 묶어서 순위를 매기거나 합계를 구하는 방법이다. 예를 들어 한식 식당 중 주문 건수가 많은 순으로 순위를 매기거나 A식당이 차지하는 비율을 구해낼 수 있다.

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

rank함수를 쓸 때에는 over와 무조건 한 쌍으로 쓴다. rank의 괄호 안에는 아무것도 안 적어도 된다. 그냥 순위를 매기는 것이기 때문.

대신 over 뒤의 괄호에 어떤 단위로 묶어줄것인지 partition by에 적는다. 여기서는 partition by cuisine_type이므로 음식타입별로 순위를 구해달라는 뜻이다.

order by는 어떤 기준으로 순위를 구할것인지를 정해주는 것이다. 여기서는 order by cnt_order이므로 주문 수에 따라 순위를 구했다.

즉 정리하자면, cuisine_type별로 주문 건수에 따라서 내림차순으로 순위를 매겨달라는 명령문이다.

출력된 데이터를 보면 식당 타입별로 주문 건수가 많은 순위로 rank가 매겨져 있는 걸 확인할 수 있다. abc순서라서 American식당의 순서가 매겨지고 그 아래에 Chinese식당의 순서가 매겨져 있는 걸 확인할 수 있다.

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

서브쿼리로 또 한 번 묶어주고, select에 보고 싶은 행들을 적어주고 마지막 where절에 ranking이 3이하인 것들로 조건을 걸어주면 3위까지만 잘라서 출력할 수 있다.

각 음식점의 주문 건이 해당 음식 타입에서 차지하는 비율을 구하고, 주문건이 낮은 순으로 정렬했을 때 누적 합 구하기

SELECT cuisine_type,
       restaurant_name,
       cnt_order,
       sum(cnt_order) over (partition by cuisine_type) sum_cuisine,
       sum(cnt_order) over (partition by cuisine_type order by cnt_order) cum_cuisine
from
(
select cuisine_type,
       restaurant_name,
       count(1) cnt_order
from food_orders
group by 1, 2
) a
order by cuisine_type, cnt_order

sum도 over와 한 쌍으로 같이 써준다. 음식 타입별로 주문 건수를 모두 합해달라는 명령문이다.
그 밑에는 음식 타입별로 주문 건수의 누적 합을 구해달라는 명령문이다. 여기는 order by를 쓴다는 것을 기억..
맨 마지막에 order by cuisine_type, cnt_order는 없어도 알아서 묶이기는 하던데 왜 작성한건지는 잘 모르겠다..

SELECT date,
       date(date) change_date
from payments

date함수를 이용해서 연산을 쉽게할 수 있다.
원래 date컬럼은 문자형식이기 때문에 날짜 형식의 date로 변환해준다.
date(date)는 괄호 안의 문자 형식인 date컬럼을 날짜 형식으로 바꿔달라는 명령문이다.
date 문자컬럼이 change_date인 날짜형식으로 바뀌었다.

연산을 하거나 누락된 값을 확인하기 위해 정렬할 때는 날짜 형식이 좋고, 년, 월만 따로 뽑아낼 때는 문자 형식이 좋다.

select date(date) date_type,
       date_format(date(date), '%Y') "년",
       date_format(date(date), '%m') "월",
       date_format(date(date), '%d') "일",
       date_format(date(date), '%w') "요일"
from payments

date컬럼을 date형식으로 변환해주고, date_format절을 이용하여 년, 월, 일, 요일만 조회할 수 있다.

년도별 3월의 주문건수 구하기

select date_format(date(date), '%Y') "년",
       date_format(date(date), '%m') "월",
       date_format(date(date), '%Y%m') "년월",
       count(1) "주문건수"
from food_orders f inner join payments p on f.order_id=p.order_id
group by 1, 2, 3

일단 년, 월, 년월과 주문건수를 조회하였다.

select date_format(date(date), '%Y') "년",
       date_format(date(date), '%m') "월",
       date_format(date(date), '%Y%m') "년월",
       count(1) "주문건수"
from food_orders f inner join payments p on f.order_id=p.order_id
where date_format(date(date), '%m')='03'
group by 1, 2, 3
order by 1

where절을 이용해 3월만 따로 뽑고 년도 순으로 정리하였다.

[실습 문제]
음식 타입별, 연령별 주문건수 pivot view 만들어 봅시다. (연령은 10~59세 사이)

SELECT cuisine_type,
       max(if(age between 10 and 19, cnt_order, 0)) "10대",
       max(if(age between 20 and 29, cnt_order, 0)) "20대",
       max(if(age between 30 and 39, cnt_order, 0)) "30대",
       max(if(age between 40 and 49, cnt_order, 0)) "40대",
       max(if(age between 50 and 59, cnt_order, 0)) "50대"
from
(
select cuisine_type,
       age,
       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 age_band,
       count(1) cnt_order
from food_orders f inner join customers c on f.customer_id=c.customer_id
where age between 10 and 59 
group by 1, age_band
) a
group by 1

아래는 정답 코드

select cuisine_type,
       max(if(age=10, order_count, 0)) "10대",
       max(if(age=20, order_count, 0)) "20대",
       max(if(age=30, order_count, 0)) "30대",
       max(if(age=40, order_count, 0)) "40대",
       max(if(age=50, order_count, 0)) "50대"
from 
(
select a.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 age,
       count(1) order_count
from food_orders a inner join customers b on a.customer_id=b.customer_id
where age between 10 and 59
group by 1, 2
) t
group by 1

실습 문제 너무 어렵다ㅠㅠㅠ연습을 열심히 해야 할듯ㅠㅠㅠ