SQL 마무리

Window Function - RANK, SUM

각 행의 관계를 정의하기 위한 함수로 그룹 내의 연산을 쉽게 만들어준다.

• 다음과 같은 경우를 생각해보자
  • 한식 식당 중에서 주문건수가 많은 순으로 순위를 매기는 경우
  • 한식 식당 전체 주문건수 중 A 식당이 차지하는 비율
  • 2건 이상 주문을 한 소비자 중 처음 주문한 식당과 2번째로 주문한 식당을 같이 조회
• 기본 SQL 구조로 해결하기 위해서는 복잡한 Subquery 문을 이용하거나 여러번의 연상을 수행해줘야 하지만 Window Function 기능을 이용하면 조금 더 편리하게 해결할 수 있다.

1. Window Function 의 기본 구조

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

   • window_function : 기능 명을 사용한다. (sum, avg 등이 있다.)
   • argument : 함수에 따라 작성하거나 생략한다.
   • partition by : 그룹을 나누기 위한 기준으로 group by 절과 유사
   • order by : window function 을 적용할 때 정렬할 컬럼 기준을 적어준다.

2. N 번째까지의 대상을 조회하고 싶을 때 (RANK)

   • '특정기준으로 순위를 매겨주는' 기능
   • 주문 건수별 순위 매기기, 결제 시간이 빠른 순으로 순위 매기기 등이 가능
   • 음식 타입별로 주문건수가 가장 많은 상점 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)
       as ranking
   from
   (
   select cuisine_type, restaurant_name,
       count(order_id) as cnt_order
   from food_orders
   group by cuisine_type, restaurant_name
   ) as a
   ) as b
   where ranking <= 3;

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

   • 앞서 배운 합계를 구하는 기능과 동일
   • 누적합이 필요하거나 카테고리별 합계컬럼과 원본컬럼을 함께 이용할 때 유용하게 사용 가능하다.
   • 각 음식점의 주문건이 해당 음식 타입에서 차지하는 비율을 구하고, 주문건수가 낮은 순으로 정렬했을 때 누적 합 구하기

   select restaurant_name,
       cuisine_type,
       cnt_order,
       sum(cnt_order) over (partition by cuisine_type) as sum_cuisine,
       sum(cnt_order) over (partition by cuisine_type order by cnt_order,
       restaurant_name) as cum_cuisine
   from 
   (
   select restaurant_name,
       cuisine_type,
       count(order_id) as cnt_order
   from food_orders
   group by restaurant_name, cuisine_type
   ) as a
   order by cuisine_type, cnt_order, cum_cuisine

---

포맷 함수

SQL 의 연산은 숫자, 문자 외에도 날짜도 가능하다.

1. 날짜 데이터의 이해

   • 문자타입, 숫자타입과 같이 날짜 데이터도 특정 타입을 가지고 있다.
   • 년, 월, 일, 시, 분, 초 등의 값을 모두 가지고 있으며 월, 주, 일 등으로 포맷을 변경할 수도 있다.

2. 날짜 데이터의 여러 포맷

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

   select date(date) as date_type,
       date
   from payments;

   • date type 을 data_format 을 이용하여 년,월,일,주 로 조회하기
     • 년 : Y(4자리), y(2자리)
     • 월 : M, m
     • 일 : d, e
     • 요일 : w

   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;

3. 날짜 데이터 실습

   • 년도, 월을 포함하여 데이터 가공

   select date_format(date(date), '%Y') y,
      date_format(date(date), '%m') m,
      order_id
   from payments;

   • 년도, 월별 주문건수 구하기

   select date_format(date(date), '%Y') as y,
      date_format(date(date), '%m') as m,
      count(a.order_id)
   from food_orders as a inner join payments as b
   on a.order_id = b.order_id
   group by y, m;

   • 3월 조건으로 지정하고, 년도별로 정리하기

   select date_format(date(date), '%Y') as y,
      date_format(date(date), '%m') as m,
      count(a.order_id)
   from food_orders as a inner join payments as b
   on a.order_id = b.order_id
   where date_format(date(date), '%m') = 03
   group by y, m
   order by y;