SQL - Window Function, 날짜 포맷

SQL 5주차

5강. 업무 시작을 단축시켜 주는 마법의 문법(Window Function - Rank, Sum)

Window Function은 행을 단위별로 묶어서 연산을 쉽게 만들어준다.

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

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

2. Rank 함수 적용하기

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

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 는 한 쌍으로 반드시 같이 사용한다.
over() 안에서 인자 partition by 는 음식 타입별로 랭킹을 구할 것이다 라는 것을 명시한다.
order by 에서는 어떤 기분로 묶어줄것인지 명시하고 뒤에 오름차순이나 내린차순을 정한다. 마지막엔 별명을 명시한다.

위의 식 같은 경우에는, 음식 타입별로 묶어준다고 해서, American 타입의 음식점중에서의 랭킹 1위부터 꼴지까지 나오고,
American 이 끝나면 Chinese 에서 다시 랭킹 1위부터 꼴지 까지 내림차순으로 나오게 된다.
이때의 내림차순은 기준이 cnt_order 이기 때문에 카운트가 많을 수록 랭킹이 높기 때문에 카운트를 내림차순으로 정렬하면 랭킹이 오름차순으로 정렬되게 된다.

3번의 내용인 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

2번까지의 내용을 서브쿼리문으로 묶어주고, 조회할 데이터 컬럼을 select 한 뒤 전체구문 마지막에 where절로 조건을 넣어서 ranking 을 분류한다.


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

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

2. 카테고리별 합, 카테고리별 누적합 구하기

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(cnt_order) over(partition by cuisine_type order by cnt_order) cum_cuisine 이 부분이 누적합을 구하는 부분이다.

 

마지막에 적혀있는 order by cuisine_type, cnt_order 부분은, 조회가 잘 되었는지 확인하기 위한 컬럼을 추가하는 것.

---

6강. 날짜 포멧과 조건까지 SQL로 한번에 끝내기(포맷 함수)

SQL엔 문자타입, 숫자타입, 날짜 데이터도 특정한 타입을 가지고 있다.

실습예제1) 날짜 데이터의 여러 포맷

1. yyyy-mm-dd 형식의 문자열 형태의 컬럼을 date type 으로 변경하기

select date,
       date(date) change_date
from payments

 

 

사진에서 컬럼명 왼쪽의 타입 부분을 보면 시계모양으로 바뀐것을 확인할 수 있다.

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

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_format(date(date), '%Y') "년" 이 부분은 데이터중에서 %Y 로 년도만 남겨주겠다라는 의미이다.

사진에서 요일 컬럼을 보면 0이라고 나온 부분은 일요일을 의미하고, 6은 토요일을 의미한다.

실습예제2)

1. 년도, 월을 포함하여 데이터 가공하기
        
2. 년도, 월별 주문건수 구하기
 
3. 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

 

3번의 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

---

5주차 숙제

음식 타입별, 연령별 주문건수 pivot view 만들기 (연령은 10~59세 사이)
1. SQL 기본구조 작성하기
2. Pivot view 를 만들기 위해 필요한 데이터 가공하기
3. Pivot view 문법에 맞추어 수정하기

서브쿼리: food_orders 와 customers 테이블을 조인하여, 연령대별(age)로 그룹화를 하고, 각 그룹의 주문 횟수(order_count)
를 계산합니다. 연령대는 age 를 기준으로 계산된 범위(10,20,30,40,50)에 따라 할당됩니다.

메인쿼리: 서브쿼리의 결과를 사용하여, 각 요리유형(cuisine_type)별로 연령대별 주문횟수의 최대값을 계산합니다.
이는 MAX 함수와 IF 문을 조합하여 각 연령대별 컬럼(10대, 20대, 30대, 40대, 50대)에 대한 최대 주문 횟수를 구합니다.

1. SQL 기본구조 작성하기

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

2. Pivot view 를 만들기 위해 필요한 데이터 가공 및 문법에 맞추어 수정

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

이로써 5주과정의 SQL 기본 문법 공부는 끝이 났다.


















select t.cuisine_type,
        max(if(t.age_group = '10대',t.order_count,0)) as '10대',
        max(if(t.age_group = '20대',t.order_count,0)) as '20대',
        max(if(t.age_group = '30대',t.order_count,0)) as '30대',
        max(if(t.age_group = '40대',t.order_count,0)) as '40대',
        max(if(t.age_group = '50대',t.order_count,0)) as '50대'
from
(
select f.cuisine_type,
        case when c.age between 10 and 19 then as '10대'
              when c.age between 20 and 29 then as '20대'
              when c.age between 30 and 39 then as '30대'
              when c.age between 40 and 49 then as '40대'
              when c.age between 50 and 59 then as '50대'
              end as age_group,
              count(f.order_id) as order_count
from food_orders f inner join customers c on f.customer_id=c.customer_id
where c.age between 10 and 59
group by f.cuisine_type, age_group
)a
group by t.cuisine_type;