SQL 고급 강의 - Window 함수

1. Window 함수란?

Window 함수란, 행과 관련된 행 간의 연산을 수행하는 함수이다. GROUP BY를 이용한 집계함수와 기능적으로는 유사하나, 집계 기준 별로 결과물이 출력되지 않고 각각의 row 별로 결과물이 출력된다는 점에서 차이가 있다.

 

-- GROUP BY를 활용한 집계함수

mysql> SELECT country, SUM(profit) AS country_profit
       FROM sales
       GROUP BY country
       ORDER BY country;
+---------+----------------+
| country | country_profit |
+---------+----------------+
| Finland |           1610 |
| India   |           1350 |
| USA     |           4575 |
+---------+----------------+

-- Window 함수

mysql> SELECT
         year, country, product, profit,
         SUM(profit) OVER() AS total_profit,
         SUM(profit) OVER(PARTITION BY country) AS country_profit
       FROM sales
       ORDER BY country, year, product, profit;
+------+---------+------------+--------+--------------+----------------+
| year | country | product    | profit | total_profit | country_profit |
+------+---------+------------+--------+--------------+----------------+
| 2000 | Finland | Computer   |   1500 |         7535 |           1610 |
| 2000 | Finland | Phone      |    100 |         7535 |           1610 |
| 2001 | Finland | Phone      |     10 |         7535 |           1610 |
| 2000 | India   | Calculator |     75 |         7535 |           1350 |
| 2000 | India   | Calculator |     75 |         7535 |           1350 |
| 2000 | India   | Computer   |   1200 |         7535 |           1350 |
| 2000 | USA     | Calculator |     75 |         7535 |           4575 |
| 2000 | USA     | Computer   |   1500 |         7535 |           4575 |
| 2001 | USA     | Calculator |     50 |         7535 |           4575 |
| 2001 | USA     | Computer   |   1200 |         7535 |           4575 |
| 2001 | USA     | Computer   |   1500 |         7535 |           4575 |
| 2001 | USA     | TV         |    100 |         7535 |           4575 |
| 2001 | USA     | TV         |    150 |         7535 |           4575 |
+------+---------+------------+--------+--------------+----------------+

 

Window 함수는 SELECT와 ORDER BY에만 쓸 수 있다. Window 함수의 실행은 쿼리문 순서에 따라 FROM, WHERE, GROUP BY, HAVING가 작동한 후 SELECT, ORDER BY 전에 일어난다. 따라서 Window 함수 결과값을 WHERE, GROUP BY, HAVING에 활용할 수 없다.

해당 내용은 https://jjjk84.tistory.com/50 문제를 확인하면 더 이해하기 쉽다.

 

만일 Window 함수 결과값을 WHERE 조건으로 활용하고 싶다면 FROM 절에 Subquery로 활용하여 쓸 수 있는 방법도 있다.

 

출처 : https://dev.mysql.com/doc/refman/8.0/en/window-functions-usage.html

 

2. Window 함수 문법

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

PARTITION BY와 ORDER BY는 기능에 따라 생략 가능하며 PARTITION BY는 GROUP BY와 유사한 기능이다.

 

 

3. Window 함수 예시

 

1) 집계함수로 활용하기

 

1. MAX (A) OVER (PARTITION BY B)

컬럼 B 별로 컬럼 A의 최대값을 출력

 

2. SUM (A) OVER (ORDER BY B)

컬럼 B 순서대로 컬럼 A 누적합

 

3. SUM (A) OVER (ORDER BY B PARTITION C)

컬럼 C 별로 컬럼 B 순서대로 컬럼 A 누적합

 

2) Window 함수에서만 지원되는 기능

 

1. 순서 출력

공통점 : ()를 써주되, 안을 비워놓음

 

- ROW_NUMBER() OVER (ORDER BY 기준)

중복되는 순위 없이 1부터 부여 (1, 2, 3...)

 

- RANK() OVER (ORDER BY 기준)

기준값이 동일할 경우, 중복 순위 적용 (1, 1, 3...)

 

- DENSE_RANK() OVER (ORDER BY 기준)

기준값이 동일할 경우, 비는 값 없이 중복 순위 적용 (1, 1, 2...)

 

2. 데이터 위치 바꾸기

 

- LAG (A, i, j) OVER (ORDER BY B)

컬럼 B 기준으로 컬럼 A를 i 칸씩 밀기, 맨처음 NULL 값은 j로 설정

 

- LEAD (A, i, j) OVER (ORDER BY B)

컬럼 B 기준으로 컬럼 A를 i 칸씩 당기기, 맨처음 NULL 값은 j로 설정