[데이터분석 부트캠프] SQL #6. PIVOT TABLE, Window Function(RANK, SUM), 날짜 포맷 함수

* PIVOT TABLE

ex. 성별, 연령별 주문건수 Pivot Table 뷰 만들기 (나이는 10~59세 사이, 연령 순으로 내림차순)

select age,

            max(if(gender='male', order_count, 0)) male,

            max(if(gender='female', order_count, 0)) female

from

(

select b.gender,

            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 b.age between 10 and 59

group by 1, 2

) a

group by 1

order by age desc

 

* Window Function(RANK, SUM)

- window_function(argument) over (partition by 그룹 기준 컬럼 order by 정렬 기준

- Rank

ex1. 음식 타입별, 음식점별 주문 건수 집계하기

select cuisine_type,

            restaurant_name,

            rank() over (partition by cuisine_type order by order_count desc) rn,

            order_count

from

(

select cuisine_type,

            restaurant_name,

            count(1) order_count

from food_orders

group by 1, 2

) a

 

ex2. 3위까지 조회하고, 음식 타입별, 순위별로 정렬하기

select cuisine_type,

            restaurant_name,

            order_count, 

            ranking

from

(

select cuisine_type,

            restaurant_name,

            rank() over (partition by cuisine_type order by order_count desc) ranking,

            order_count

from

(

select cuisine_type,

            restaurant_name,

            count(1) order_count

from food_orders

group by 1, 2

) a

) b

where ranking <= 3

group by 1, 4

 

- SUM (누적합)

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

select cuisine_type,

            restaurant_name,

            order_count,

            sum(order_count) over (partition by cuisine_type) sum_cuisine_type,

            sum(order_count) over (partition by cuisine_type order by order_count, restaurant_name) cum_sum

from

(

select cuisine_type,

            restaurant_name,

            count(1) order_count

from food_orders

group by 1, 2

) a

 

* 날짜 포맷 함수

- yyyy-mm-dd 형식의 컬럼을 date type 으로 변경하기

select date(date) date_type,

           date

from payments

 

- date type 을 date_format 을 이용하여 년, 월, 일, 주 로 조회해보기

select date(date) date_type,

           date_format(date(date), '%Y') "년",      --------- Y: 2025, y: 25

            date_format(date(date), '%m') "월",

            date_format(date(date), '%d') "일",

            date_format(date(date), '%w') "요일"

from payments