17. SQL Window Function - ( RANK와, SUM )

김요한·2024년 6월 26일

sql

엑셀보다 쉽고 빠른 SQL(내일배움캠프)

목록 보기

16/17

Window Function 이해

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

1. 한식 식당 중에서 주문건수가 많은 순으로 순위를 매기고 싶을 때
1. 한식 식당 전체 주문건수 중에서 A 식당이 차지하는 비율을 알고 싶을 때
1. 2건 이상 주문을 한 소비자 중에, 처음 주문한 식당과 2번째로 주문한 식당을 같이 조회할 때

이때 쓰는 것이 RANK와 SUM 함수이다.

기본 SQL 구조로 해결하기 위해서는 복잡하게 Subquery 문을 이용하거나, 여러번의 연산을 수행해줘야 하지만, 자체적으로 제공해주는 기능을 이용하면 조금 더 편리하다 이 기능들이 Window function 으로 제공되고 있다.

Window Function 의 기본 구조

window_function(argument) over (partition by 그룹 기준 컬럼 order by 정렬 기준)
window_function : 기능 명을 사용해준다. (sum, avg 와 같이 기능명이 있습니다)

argument : 함수에 따라 작성하거나 생략할 수도 있다.

partition by : 그룹을 나누기 위한 기준입니다. group by 절과 유사하며 해당 컬럼을 기준으로 파티션으로 나눈다고 생각하면 편하다.

order by : window function 을 적용할 때 정렬 할 컬럼 기준을 적어준다.

예제 1

Rank 함수 적용하여 음식 타입별로 주문 건수가 가장 많은 상점 3개씩 조회하자

select cuisine_type,
       restaurant_name,
       order_count,
       rn "순위"
from
(
select cuisine_type,
       restaurant_name,으로 정렬한다.
       rank() over (partition by cuisine_type order by order_count desc) rn,
       order_count  # cuisine_type을 기준으로 순위를 매기며 주문수량(order_count)순 으로 정렬한다.
(                         # rank 함수에 ()를 비워도 된다.
from                      # rank() over는 한 쌍 이라고 생각하자.
select cuisine_type, restaurant_name, count(1) order_count
from food_orders
group by 1, 2
) a
) b
where rn<=3   #주문건수 가장 많은 상점 3개씩 조회
order by 1, 4

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

select cuisine_type,
       restaurant_name,
       order_count,
       sum(order_count) over (partition by cuisine_type) sum_cuisine_type,
       # SUM() 함수는 총 매출을 계산 OVER() 절은 Date열을 기준으로 정렬된 행 창을 정의
       sum(order_count) over (partition by cuisine_type 
       order by order_count, restaurant_name) cumulative_sum
from   # over(order by order_count, restaurant_name) 행을 정렬하는 데 사용되는 열
(
select cuisine_type, restaurant_name, count(1) order_count
from food_orders
group by 1, 2
) a