240829 TIL SQL 5주차

윤수용·2024년 8월 29일

TIL

목록 보기

4/89

1. SQL로 Pivot Table 만들어보기

1) Pivot Table 이란?

- 2개 이상의 기준으로 데이터를 집계할 때, 보기 쉽게 배열하여 보여주는 것
- 특정 컬럼들의 데이터 간의 관계를 시각적으로 잘 보여줌

2) Pivot Table 의 기본 구조

column1	column2	column3	...
criteria1
criteria2
⁝

3) [실습] 연령별, 성별 주문건수 Pivot Table 만들기 (10~59세 사이, 연령 순으로 내림차순)

select age,
	max(if(gender='male', order_count, 0)) male,
    max(if(gender='female', order_count, 0)) female
from
	(
    select c.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) as order_count
    from food_orders fo inner join customers c on fo.customer_id = c.customer_id
    where c.age between 10 and 59
    group by 1, 2
    ) a
group by 1
order by age

<결과>

age	male	female
10대	84	94
20대	97	106
30대	81	82
40대	97	96
50대	103	77

2. Window Function - RANK, SUM

1) Window Function 이란?

각 행의 관계를 정의하기 위한 함수로 그룹 내의 연산을 쉽게 만듦
복잡한 Subquery 문이나, 여러번의 연산을 수행하지 않도록 해줌

2) Window Function 의 기본 구조

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

3) N번째까지의 대상을 조회하고 싶을 때, Rank

[실습] 음식 타입별로 주문 건수가 가장 많은 상점 3개씩 조회하기

select cuisine_type,
       restaurant_name,
       order_count,
       rn ranking
from
  (
  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
  ) b
where rn<=3
order by 1, 4

cuisine_type	restaurant_name	order_count	ranking
American	Shake Shack	219	1
American	Blue Ribbon Fried Chicken	96	2
American	Five Guys	29	3
Chinese	RedFarm Broadway	59	1
Chinese	RedFarm Hudson	55	2
Chinese	Han Dynasty	46	3

4) 전체에서 차지하는 비율, 누적합을 구할 때, Sum

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

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, restaurant_name) 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, cum_cuisine