[SQL : Window function ] SUM , MAX , ROW_NUMBER( ) ,RANK( ), DENSE_RANK( ) , LEAD, LAG

Window function

---

01. window function 와 groupby 

: groupby 함수와 유사하다.

하지만, 원래 있던 값이 아니라, 새로운 값이 나와서 그룹을 묶어주게 된다.

mysql> SELECT SUM(profit) AS total_profit
       FROM sales;
+--------------+
| total_profit |
+--------------+
|         7535 |
+--------------+
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 함수는 한줄로 요약해서 보여주는 것이아닌, 각각의 row에 결과물을 찍어주는 것이다.

위와 결과는 같지만, 합치지 않고, 모든 row를 찍어서 보여준다.

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 |
+------+---------+------------+--------+--------------+----------------+

 

다양한 집계함수 뿐만 아니라, 윈도우 함수에서 제공하는 특이한 함수들도 이용할 수 있다.

 

 

02. window 함수의 모양새

함수(컬럼) over ( PARTITION BY 어떤 것으로 묶을지 기준이 되는 컬럼 ORDER BY 컬럼 )

 

ex, SUM ( profit ) OVER ( PARTITION BY contry  [ ORDER BY 컬럼 ] )  

 

- PARTITION BY

- ORDER BY 

: 둘다 있어도, 하나만 있어도, 둘다 없어도 가능함

 

03. 다양한 window 함수의 방법

1 ) 집계함수 

a. MAX

SELECT id, Name, Salary, DepartmentId, Max(salary) OVER (PARTITION BY DepartmentId ) As MaxSalary
From Employee

 

b. SUM

- line 이 고유한 값일 때, ORDER BY 로 순서로 더하게 된다면, CUMSUM 처럼 활용할 수 있다. 

SELECT id, line , 
		sum(kg) over ( order by line ) AS Cumsum
FROM Elevator

- PARTITION BY id 를 한다면 , id가 바뀌는 순간 값이 초기화 됨을 알 수 있다. 

SELECT id, line , 
		sum(kg) over ( order by line PARTITION BY id ) AS Cumsum
FROM Elevator

 

 

2) 순위함수 

: 데이터의 순위를 정하는 함수 

: ( ) 안에 , 인자를 하나도 받지 않음 

 

a.  ROW_NUMBER( ) : val 이 커짐에 따라서 순위가 늘어나게 됨  EX , 1, 2, 3 ,4 ,5

b. RANK( ) : 등수가 중복될 경우, 다음의 등수는 존재하지 않는다. ex, 1, 1 , 3, 4, 4, 4, 7, 8

c. DENSE_RANK( ) : 등수가 중복되더라도 다음의 등수가 나온다. ex, 1, 1, 2, 3, 3, 3, 4, 5

 

SELECT val
	, ROW_NUMBER ( ) OVER ( ORDER BY val ) AS 'row_number'
    , RANK ( ) OVER ( ORDER BY val ) AS 'rank'
    , DENSE_RANK( ) OVER ( ORDER BY val ) AS 'dense_rank'
FROM sample

- 이렇게 쓰면, 위와 같은 코드이지만 더욱 간단하게 쓸 수 있다. 

SELECT
  val,
  ROW_NUMBER() OVER w AS 'row_number',
  RANK()       OVER w AS 'rank',
  DENSE_RANK() OVER w AS 'dense_rank'
FROM numbers
WINDOW w AS (ORDER BY val);

 

3 ) 데이터 위치 바꾸기

: 값들을 몇칸씩 밀거나, 당기는 함수 

a. LEAD( )  : 데이터를 당기는 함수 

b. LAG( ) : 데이터를 미는 함수

SELECT Id
	, RecordDate
    , Temperature
    # recorddate 를 기준으로 TEMPERATURE을 미뤄주라
    , LAG ( Temperature ) OVER ( ORDER BY RecordDate ) AS 'lag'
    # recorddate 를 기준으로 당겨주어라
    , LEAD ( Temperature ) OVER ( ORDER BY RecordDate ) AS 'lead'
FROM sample

결과

id	recordDate	Temperature	lag	lead
1	2015-01-01	10	NULL	25
2	2015-01-02	25	10	20
3	2015-01-03	20	25	30
4	2015-01-04	30	20	28
5	2015-01-05	28	30	NULL

 

- 옵션을 넣어주기 : n 칸씩 미루거나, 당겨주기 

SELECT Id
	, RecordDate
    , Temperature
    , LAG ( Temperature , 2 ) OVER ( ORDER BY RecordDate ) AS 'lag'
    , LEAD ( Temperature , 2 ) OVER ( ORDER BY RecordDate ) AS 'lead'
FROM sample

 

- defalt 값 넣기

LAG ( Temperature , 2, 0 ) 이라면, null 대신 0 이 넣어지게 된다. 

 

 

 

+ ) 누적합 구하기

a. 윈도우 함수 사용

SUM ( 컬럼 ) OVER ( ORDER BY 컬럼 )

SELECT id, line , 
		sum(kg) over ( order by line ) AS Cumsum
FROM Elevator

 

SELECT id, line , 
		sum(kg) over ( order by line PARTITION BY id ) AS Cumsum
FROM Elevator

 

b. 윈도우 함수를 사용하지 않고 구하기 - 조인 + 그룹 바이 

SELECT e1.id, e1.Name, e1.kg, e1.Line, SUM(e2.kg) AS CumSum
FROM Elevator el
INNDER JOIN Elevator e2 on e1.id = e2.id 
							and e2.line >= e2.line
                            
GROUP BY 1,2,3,4 # e1.id, e1.Name, e1.kg, e1.Line, SUM(e2.kg) AS CumSum

 

c. 윈도우 함수를 사용하지 않고 구하기 - select 절 서브쿼리 

SELECT e1.Id, e1.Name, e1.kg, e1.Line,
		( SELECT SUM(e2.kg)
        	FROM Elevator e2
            WHERE e1.ID = e2.ID
            	and e1.Line >= e2.line ) as CumSum
FROM Elevator e1

---

출처 :

dev.mysql.com/doc/refman/8.0/en/window-functions-usage.html