SQL | 윈도우 함수(Window Function) 사용법 정리 | RANK함수, SUM함수

 

0. 윈도우 함수(Window Function)는 다중행 함수

 

▶ 다중 행 함수(Multi-Row Function)

: 여러 행의 그룹에 대해 적용되는 함수

 

▶ 다중 행 함수의 종류(그룹함수, 윈도우 함수)

1) 그룹함수 (집계함수, 고급 집계함수)

- 여러 행을 / 그룹으로 묶을 때 / 묶이는 값들을 하나의 값으로 반환해줌

- SELECT문, HAVING 조건절, Order by절에서 사용 가능

집계 함수	고급 집계 함수
COUNT 함수 (개수)	ROLLUP 함수 (소그룹 별 소계 계산 / 컬럼 명시 순서 중요)
SUM 함수 (합계)	CUBE 함수 (결합 가능한 모든 값에 대한 다차원 소계 계산)
AVG 함수 (평균)	GROUPING SETS 함수 (컬럼들의 개별 소계 계산, 순서 X)
MAX / MIN 함수 (최대 / 최솟값)

 

[참고 게시글]

[IT 교육/SQL] - SQL | Group By절과 집계 함수는 한 세트 | 집계 함수 사용 시 주의 사항

SQL | Group By절과 집계 함수는 한 세트 | 집계 함수 사용 시 주의 사항

 

---

 

2) 윈도우 함수(Window Function)

- 기존 관계형 DB의 경우에는 컬럼 간 연산은 쉽지만 행 간의 연산은 어려움.

- 행 간의 연산을 위해 윈도우 함수 고안됨. 그룹 내의 연산을 쉽게 해줌.

- 중첩(Nested) 사용 불가

- 서브쿼리에서도 사용 가능

 

- 윈도우 함수 종류

순위	RANK / DENSE_RANK / ROW_NUMBER
집계	COUNT / SUM / AVG / MAX / MIN
행 순서	FIRST_VALUE / LAST_VALUE / LAG / LEAD
비율	RATIO_TO_REPORT / PERCENT_RANK / NTILE
통계	CORR / STDDEV / VARIANCE

 

---

 

1. 윈도우 함수(Window Function)

 

▶ 윈도우 함수의 기본 구조

WINDOW_FUNCTION(argument) OVER (PARTITION BY 그룹 기준 컬럼 ORDER BY 정렬 기준)

• WINDOW_FUNCTION : 사용할 윈도우 함수 적기
• argument : 사용할 윈도우 함수의 인수를 작성하거나 생략 가능
• PARTITION BY : 그룹을 나누기 위한 기준 (Group by 절과 유사)
• ORDER BY : 윈도우 함수를 적용할 때 정렬할 기준 컬럼 적기

---

2. RANK 함수 집중 분석

 

2-1) RANK 함수

: RANK 윈도우 함수는 '특정 기준으로 순위를 매겨주는' 기능

: N 번째까지의 대상을 조회하고 싶을 때 사용

RANK() OVER(PARTITION BY 그룹화할 컬럼명 ORDER BY 정렬 기준 컬럼명)

---

2-2) RANK 함수 실습문제

 

▶ ①음식 타입 별로 / ②주문 건수가 가장 많은 상점 / ③3개씩 조회하기

 

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

: Group by절을 이용해 음식 타입과 음식점 명으로 그룹화 한 뒤 / 주문 건수를 COUNT 하고 / 이 세 컬럼을 SELECT문으로 조회함 

SELECT cuisine_type ,
       restaurant_name,
       COUNT(1) cnt_order
FROM food_orders fo
Group By 1, 2

 

 

② Rank() 함수 적용하기

: ① 번에서 만든 쿼리문을 FROM절의 서브쿼리로 넣어서 새로운 쿼리문의 테이블로 만듦 (= 서브쿼리문의 결과를 뽑아 쓸 수 있도록 함)

: 음식 타입과 음식점 명 별로 그룹화되어 있는 테이블에서 / RANK 윈도우 함수를 통해 음식 타입 별로(=PARTITION BY) / 주문 건수가 큰 것을 기준으로(Order by ~ DESC) / 순위(1등, 2등 ... )를 매김 

SELECT cuisine_type,
        cnt_order,
        restaurant_name,
        RANK() OVER(PARTITION BY cuisine_type Order By cnt_order DESC) rank_order
FROM 
(SELECT cuisine_type ,
        restaurant_name,
        COUNT(1) cnt_order
FROM food_orders fo
Group By 1, 2) a

 

 

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

: ②번에서 만든 쿼리문에 WHERE절을 이용해 조건에 따른 값이 조회되도록 함

: 이후 순위 별로 정렬

SELECT cuisine_type,
       restaurant_name,
       cnt_order,
       rank_order
FROM 
(SELECT cuisine_type,
        cnt_order,
        restaurant_name,
        RANK() OVER(PARTITION BY cuisine_type Order By cnt_order DESC) rank_order
FROM 
(SELECT cuisine_type ,
        restaurant_name,
        COUNT(1) cnt_order
FROM food_orders fo
Group By 1, 2) a ) b
WHERE rank_order <= 3
Order By 1, 4

 

---

3. SUM 함수 집중 분석

 

3-1) SUM 함수

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

: 누적합이 필요하거나 카테고리 별 합계컬럼과 원본 컬럼을 함께 이용할 때 유용함

SUM(집계내고 싶은 컬럼명) OVER(PARTITION BY 그룹화할 컬럼명 ORDER BY 정렬 기준 컬럼명)

---

3-2) SUM 함수 실습문제

 

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

 

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

: Group by절을 이용해 음식 타입과 음식점 명으로 그룹화 한 뒤 / 주문 건수를 COUNT 하고 / 이 세 컬럼을 SELECT문으로 조회함 

SELECT cuisine_type ,
        restaurant_name ,
        COUNT(1) cnt_order
FROM food_orders fo 
Group By 1, 2

 

 

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

: ① 번에서 만든 쿼리문을 FROM절의 서브쿼리로 넣어서 새로운 쿼리문의 테이블로 만듦 (= 서브쿼리문의 결과를 뽑아 쓸 수 있도록 함)

: 음식 타입과 음식점 명 별로 그룹화되어 있는 테이블에서 / SUM 윈도우 함수를 통해 음식 타입 별로(=PARTITION BY) /

★해당 음식 타입 내에서의 전체 주문 건수와 /

 ♥ 해당 음식 타입 내의 음식점 명 별 주문 건수를 값이 큰 것을 기준으로(Order by ~ DESC) / 주문 건수를 합해줌

: 비율을 구해야 하기 때문에 ★과 ♥를 다 구해준 것

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) sum_restaurant
FROM 
(SELECT cuisine_type ,
        restaurant_name ,
        COUNT(1) cnt_order
FROM food_orders fo 
Group By 1, 2) a

 

 

③ 각 음식점의 주문 건이 해당 음식 타입에서 차지하는 비율 구하기

: ② 번에서 만든 쿼리문을 FROM절의 서브쿼리로 넣어서 새로운 쿼리문의 테이블로 만듦 (= 서브쿼리문의 결과를 뽑아 쓸 수 있도록 함)

: ♥ ÷ ★ = (sum_restaurant) / (sum_cuisine을 해줌

SELECT cuisine_type,
       restaurant_name,
       cnt_order,
       sum_cuisine,
       sum_restaurant,
       sum_restaurant / sum_cuisine ratio
FROM 
(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) sum_restaurant
FROM 
(SELECT cuisine_type ,
        restaurant_name ,
        COUNT(1) cnt_order
FROM food_orders fo 
Group By 1, 2) a ) b