✅Window Function 의 사례와 기본 구조

  • Window Function 은 각 행의 관계를 정의하기 위한 함수로 그룹 내의 연산을 쉽게 만들어준다.
  • 예를 들어 다음의 경우를 생각해보자
    • 한식 식당 중에서 주문건수가 많은 순으로 순위를 매기고 싶은데?
    • 한식 식당 전체 주문건수 중에서 A 식당이 차지하는 비율을 알고 싶은?
    • 2건 이상 주문을 한 소비자 중에, 처음 주문한 식당과 2번째로 주문한 식당을 같이 조회할 수 있을까?
  • 기본 SQL 구조로 해결하기 위해서는 복잡하게 Subquery 문을 이용하거나, 여러 번의 연산을 수행해줘야 하지만, 자체적으로 제공해주는 기능을 이용하면 조금 더 편리하다.

 

Window Function 의 기본 구조

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

 

 

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

  • Rank 는 이름에서 유추할 수 있듯이 ‘특정 기준으로 순위를 매겨주는’ 기능이다.
  • 예를 들어, 주문 건수별 순위 매기기, 결제 시간이 빠른 순으로 순위 매기기 등이 가능하다.

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

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

select cuisine_type, 
	   restaurant_name, 
	   count(*) as cnt_order
from food_orders
group by 1, 2

 

 

Rank 함수 적용하기

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(*) as cnt_order
    from food_orders
    group by 1, 2
) a;

 

 

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(*) as cnt_order
	from food_orders
	group by 1, 2
	) a
) b
where ranking <= 3;

 

 

 

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

  • - Sum 은 앞서 배운 합계를 구하는 기능과 동일하다.
  • - 다만, 누적합이 필요하거나 카테고리별 합계컬럼와 원본 컬럼을 함께 이용할 때 유용하게 사용할 수 있니다.

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

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

select cuisine_type, 
	   restaurant_name, 
	   count(*) as cnt_order
from food_orders
group by 1, 2

 

 

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

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(*) as cnt_order
	from food_orders
	group by 1, 2
) a
order by cuisine_type, cnt_order;

 

 

누적합 고쳐보기

강의 자료엔 코드가 제대로 포함되지 않아 수정하여 작성

위 정답에선 cnt_order 가 동일한 값을 일괄로 더해 sum_cuisine, cum_cuisine으로 출력되는 문제가 있다.

문제 상황 이해   

이 상황은 SQL에서 `WINDOW` 함수(`SUM` 등)를 사용할 때, `SUM`으로 동일한 `cnt_order` 값을 가진 여러 행이 있을 경우, SQL 엔진은 이 값을 한꺼번에 더하는 현상이 발생한다. cnt_order의 순서를 결정할 명확한 기준이 없으므로 발생한 문제이다.

 

 

해결 방법

이 문제를 해결하려면 `ORDER BY` 절에 `cnt_order` 외에 추가적인 열에 순서를 부여할 수 있는 `restaurant_name`을 포함시켜야 한다. 이렇게 하면 동일한 `cnt_order` 값을 가진 행들이 명확하게 순서가 정해져 누적합이 정상적으로 처리된다.

 

누적합을 순서대로 표기하기 위해 order by에 restaurant_name 을 추가해준다.

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(*) as cnt_order
	from food_orders
	group by 1, 2
) a
order by cuisine_type, cnt_order, restaurant_name;

 

 

 

 

티스토리툴바