[SQL] 윈도우 함수

 

오늘은 윈도우 함수를 정리해보려고 합니다 :)

윈도우 함수란?

행과 행간의 관계를 쉽게 정의하기 위해 만든 함수

 

[Index]

1. 윈도우 함수란?

2. 집계 함수 [SUM, MAX]

3. 순위 [ROW_NUMBER(), RANK(), DENSE_RANK()]

4. 데이터 위치 [LEAD(), LAG()]

 

 

 

---

 

 

 

 1. 윈도우 함수란?

 

• 행과 행의 관계를 정의하기 위해 사용하는 함수
• 집계 함수(SUM, MAX, MIN ...)를 사용할 때, 윈도우 절을 이용하면 집계 대상이 되는 레코드 범위 지정이 가능하다. 
• RDBMS의 행과 행의 관계를 연산하거나 정의하는 것이 어려운 문제를 해결하기 위해 제시된 것이다.

 

  [윈도우 함수 구조]

  함수(컬럼명)  OVER  (PARTITION BY 컬럼명 ORDER BY 컬럼명)

PARTITION BY	특정 기준에 의해 소그룹으로 나눈다.
ORDER BY	특정 항목을 기준으로 정렬한다.

 

 

 

 

2. 집계 함수

 

MAX(컬럼1)  OVER  (PARTITION BY 컬럼2)

컬럼2별로 컬럼1의 최댓값을 구한다.

 

예시)

<code>

SELECT customer_id
     , order_id
     , customer_name
     , price
     , MAX(price) OVER (PARTIION BY order_id) AS price_max
FROM orders

<result>

customer_id	order_id	customer_name	price	price_max
1	1	Jenny	5000	5000
2	2	Sunny	8000	10000
3	2	Amy	10000	10000
4	4	Sam	6000	11000
5	4	Jack	11000	11000

order_id가 1인 행의 price는 5000밖에 없으므로, 최댓값이 5000

order_id가 2인 행의 price는 8000, 10000이므로, 최댓값이 10000

order_id가 4인 행의 price는 6000, 11000이므로, 최댓값이 11000

따라서 order_id가 1인 경우 price_max값이 5000, order_id가 2인 경우 price_max값이 10000, order_id가 4인 경우 price_max값이 11000이다. 

 

SUM(컬럼1)  OVER  (ORDER BY 컬럼2 PARTITION BY 컬럼3)

컬럼3별로 컬럼1의 누적합을 구하고, 컬럼2를 기준으로 오름차순 정렬된다.

 

예시)

<code>

SELECT customer_id
     , order_id
     , customer_name
     , price
     , SUM(price) OVER (ORDER BY price PARTITION BY order_id) AS CumSum
FROM orders

<result>

customer_id	order_id	customer_name	price	CumSum
1	1	Jenny	5000	5000
2	2	Sunny	8000	8000
3	2	Amy	10000	18000
4	4	Sam	6000	6000
5	4	Jack	11000	17000

customer_id가 3인 행의 CumSum값은 8000+10000=18000이고,

customer_id가 5인 행의 CumSum값은 6000+11000=17000이다.

order_id별로 누적합을 구한다.

 

 

 

 

3. 순위

 

RANK()

ORDER BY를 포함한 쿼리문에서 특정 컬럼의 순위를 구하는 함수, 동일한 값에 대해

같은 순위를 부여하고 중간 순위를 비운다  →  1,1,3,4,5

 

예시)

<code>

SELECT score
     , RANK() OVER (ORDER BY score DESC) 'rank'
FROM exam

<result>

score	rank
94	1
94	1
88	3
82	4
77	5
77	5
77	5
70	8

score가 94인 행이 2개 존재하므로, 중간 순위인 2위를 비워놓고 다음 순위는 3위이다.

score가 77인 행이 3개 존재하므로, 중간 순위인 6위, 7위를 비워놓고 다음 순위는 8위이다.

 

 

 

DENSE_RANK()

RANK와 작동법을 동일하나, 동일한 값에 대해 같은 순위를 부여하고 중간 순위를 비우지 않는다.  → 1,1,2,3,4

 

예시)

<code>

SELECT score
     , DENSE_RANK() OVER (ORDER BY score) AS 'dense_rank'
FROM exam

<result>

score	dense_rank
94	1
94	1
88	2
82	3
77	4
77	4
77	4
70	5

score가 94인 행이 2개 존재하지만, 중간 순위인 2위를 비우지 않고 다음 순위는 2위이다.

score가 77인 행이 3개 존재하지만, 중간 순위인 5위, 6위를 비우지 않고 다음 순위는 5위이다.

 

 

 

 

ROW_NUMBER()

RANK, DENSE_RANK는 동일한 값에 동일 순위를 부여하지만,

ROW_NUMBER는 동일한 값이어도 고유한 순위를 부여한다.  → 1, 2, 3, 4, 5

 

예시)

<code>

SELECT score
     , ROW_NUMBER() OVER (ORDER BY score) AS 'row_number'
FROM exam

<result>

score	row_number
94	1
94	2
88	3
82	4
77	5
77	6
77	7
70	8

ROW_NUMBER()은 동일한 값이 있어도 동일한 순위를 부여하지 않고, 고유의 순위를 부여한다.

 

 

 

 

4. 데이터 위치

 

LEAD()

LEAD(컬럼명, 칸 수, 디폴트)  OVER  (PARTITION BY 컬럼명 ORDER BY 컬럼명)

지정한 칸 수 이후 행의 값을 가져온다. 

칸 수의 default값은 1이고, 세번째 인자는 가져올 행이 없을 경우의 값을 지정해주는 것이다.

 

예시)

<code>

SELECT customer_id
     , order_id
     , customer_name
     , price
     , LEAD(price) OVER (ORDER BY customer_id) AS price_lead
FROM orders

<result>

customer_id	order_id	customer_name	price	price_lead
1	1	Jenny	5000	8000
2	2	Sunny	8000	10000
3	2	Amy	10000	6000
4	4	Sam	6000	11000
5	4	Jack	11000	NULL

customer_id가 1인 행을 설명하자면, 다음 행인 customer_id가 2인 행의 price값 8000을 가져온다.

 

 

LAG()

LAG(컬럼명, 칸 수, 디폴트)  OVER  (PARTITION BY 컬럼명 ORDERY BY 컬럼명)

지정한 칸 수 이전 행의 값을 가져온다.

칸 수의 default값은 1이고, 세번째 인자는 가져올 행이 없을 경우의 값을 지정해주는 것이다.

 

예시)

<code>

SELECT customer_id
     , order_id
     , customer_name
     , price
     , LAG(price, 2) OVER (ORDER BY customer_id) AS price_lag2
FROM orders

<result>

customer_id	order_id	customer_name	price	price_lag2
1	1	Jenny	5000	NULL
2	2	Sunny	8000	NULL
3	2	Amy	10000	5000
4	4	Sam	6000	8000
5	4	Jack	11000	10000

customer_id가 1인 행을 설명하자면, 2칸 이전 행은 존재하지 않기 때문에, null값을 가져온다.

customer_id가 4인 행을 설명하자면, 2칸 이전 행인 customer_id가 2인 행의 price값 8000을 가져온다.

 

 

 

 

---

 

본 게시글은 인프런의 '[백문이불여일타] 데이터 분석을 위한 고급 SQL' 강의를 듣고 저의 생각을 바탕으로 강의 내용을 재구성하여 작성한 것입니다. 😀