이번 포스팅에서는 PostgreSQL, Greenplum DB의 Window Function 의 함수 특징, 함수별 구문 사용법에 대해서 알아보겠습니다. Window Function을 알아두면 편리하고 또 강력한 SQL query 를 사용할 수 있습니다. 특히 MPP (Massively Parallel Processing) Architecture 의 Greenplum DB 에서는 Window Function 실행 시 분산병렬처리가 되기 때문에 성능 면에서 매우 우수합니다.
Window Function 은 현재 행과 관련된 테이블 행 집합(a set of table rows)에 대해 계산을 수행합니다. 이는 집계 함수(aggregate function)로 수행할 수 있는 계산 유형과 비슷합니다. 그러나 일반적인 집계 함수와 달리 Window Function을 사용하면 행이 단일 출력 행으로 그룹화되지 않으며 행은 별도의 ID를 유지합니다.
아래 화면의 예시는 AVG() 함수로 평균을 구하는데 있어,
(a) 전체 평균 집계: AVG() --> 한 개의 행 반환
(b) 그룹별 평균 집계: AVG() ... GROUP BY --> 그룹의 개수만큼 행 반환
[ PostgreSQL, Greenplum: Aggregation vs. Aggregation by Groups vs. Window Function ]
PostgreSQL, Greenplum: Aggregation vs. Aggregation by Groups vs. Window Function
먼저, 부서(depname), 직원번호(empno), 급여(salary) 의 칼럼을 가지는 간단한 예제 테이블 empsalary 을 만들고 데이터를 입력해보겠습니다.
-- (0) making a sample table as an example
DROP TABLE IF EXISTS empsalary;
CREATE TABLE empsalary (
depname TEXT
, empno INT
, salary INT
);
INSERT INTO empsalary (depname, empno, salary)
VALUES
('sales', 1, 5000)
, ('personnel', 2, 3900)
, ('sales', 3, 4800)
, ('sales', 4, 4800)
, ('personnel', 5, 3500)
, ('develop', 7, 4200)
, ('develop', 8, 6000)
, ('develop', 9, 4500)
, ('develop', 10, 5200)
, ('develop' , 11, 5200);
SELECT * FROM empsalary ORDER BY empno LIMIT 2;
--depname |empno|salary|
-----------+-----+------+
--sales | 1| 5000|
--personnel| 2| 3900|
(1) AVG() vs. AVG() GROUP BY vs. AVG() OVER (PARTITION BY ORDER BY)
아래는 (a) AVG() 로 전체 급여의 전체 평균 집계, (b) AVG() GROUP BY depname 로 부서 그룹별 평균 집계, (c) AVG() OVER (PARTITION BY depname) 의 Window Function을 사용해서 부서 집합 별 평균을 계산해서 직원번호 ID의 행별로 결과를 반환하는 것을 비교해 본 것입니다. 반환하는 행의 개수, 결과를 유심히 비교해보시면 aggregate function 과 window function 의 차이를 이해하는데 도움이 될거예요.
-- (0) aggregation returns 1 row.
SELECT
AVG(salary)
FROM empsalary;
--avg |
-----------------------+
--4710.0000000000000000|
-- (0) aggregation by groups returns rows with the number of groups
SELECT
depname
, AVG(salary)
FROM empsalary
GROUP BY depname
ORDER BY depname;
--depname |avg |
-----------+---------------------+
--develop |5020.000000000000000
--personnel|3700.000000000000000
--sales |4866.6666666666666667|
-- (1) Window functions have an OVER(PARTITION BY xx) clause.
-- any function without an OVER clause is not a window function, but rather an aggregate or single-row (scalar) function.
SELECT
depname
, empno
, salary
, AVG(salary)
OVER (PARTITION BY depname)
AS avg_dep
FROM empsalary;
--depname |empno|salary|avg_dep |
-----------+-----+------+---------------------+
--develop | 7| 4200|5020.0000000000000000|
--develop | 8| 6000|5020.0000000000000000|
--develop | 9| 4500|5020.0000000000000000|
--develop | 10| 5200|5020.0000000000000000|
--develop | 11| 5200|5020.0000000000000000|
--personnel| 5| 3500|3700.0000000000000000|
--personnel| 2| 3900|3700.0000000000000000|
--sales | 4| 4800|4866.6666666666666667|
--sales | 3| 4800|4866.6666666666666667|
--sales | 1| 5000|4866.6666666666666667|
아래는 PostgreSQL, Greenplum 의 Window Function Syntax 구문입니다.
- window_function(매개변수) 바로 다음에 OVER() 가 있으며,
- OVER() 안에 PARTITION BY 로 연산이 실행될 집단(set) 기준을 지정해주고
- OVER() 안에 ORDER BY 로 시간(time), 순서(sequence)가 중요할 경우 정렬 기준을 지정해줍니다.
-- PostgreSQL Window Function Syntax
WINDOW_FUNCTION(arg1, arg2,..) OVER (
[PARTITION BY partition_expression]
[ORDER BY sort_expression [ASC | DESC]
[NULLS {FIRST | LAST }]
)
PostgreSQL, Greenplum Window Function Syntax
PostgreSQL의 Window Functions 중에서 제가 그래도 자주 쓰는 함수로 AVG() OVER(), SUM() OVER(), RANK() OVER(), LAG() OVER(), LEAD() OVER(), FIRST_VALUE() OVER(), LAST_VALUE() OVER(), NTILE() OVER(), ROW_NUMBER() OVER() 등의 일부 함수 (제 맘대로... ㅎㅎ)에 대해서 아래에 예시를 들어보겠습니다.
(2) RANK() OVER ([PARTITION BY] ORDER BY) : 순위
아래 예는 PARTITION BY depname 로 '부서' 집단별로 구분해서, ORDER BY salary DESC로 급여 내림차순으로 정렬한 후의 직원별 순위(rank)를 계산해서 직원 ID 행별로 순위를 반환해줍니다.
PARTITION BY 집단 내에서 ORDER BY 정렬 기준칼럼의 값이 동일할 경우 순위는 동일한 값을 가지며, 동일한 순위의 개수만큼 감안해서 그 다음 순위의 값은 순위가 바뀝니다. (가령, develop 부서의 경우 순위가 1, 2, 2, 4, 5, 로서 동일 순위 '2'가 두명 있고, 급여가 네번째인 사람의 순위는 '4'가 되었음.)
-- (2) You can control the order
-- in which rows are processed by window functions using ORDER BY within OVER.
SELECT
depname
, empno
, salary
, RANK() OVER (PARTITION BY depname ORDER BY salary DESC)
FROM empsalary;
--depname |empno|salary|rank|
-----------+-----+------+----+
--personnel| 2| 3900| 1|
--personnel| 5| 3500| 2|
------------------------------------- set 1 (personnel)
--sales | 1| 5000| 1|
--sales | 3| 4800| 2|
--sales | 4| 4800| 2|
------------------------------------- set 2 (sales)
--develop | 8| 6000| 1|
--develop | 11| 5200| 2|
--develop | 10| 5200| 2|
--develop | 9| 4500| 4|
--develop | 7| 4200| 5|
------------------------------------- set 3 (develop)
(3) SUM() OVER () : PARTITION BY, ORDER BY 는 생략 가능
만약 집단별로 구분해서 연산할 필요가 없다면 OVER() 구문 안에서 PARTITON BY 는 생략할 수 있습니다.
만약 시간이나 순서가 중요하지 않다면 OVER() 구문 안에서 ORDER BY 는 생략할 수 있습니다.
아래 예에서는 SUM(salary) OVER () 를 사용해서 전체 직원의 급여 평균을 계산해서 각 직원 ID의 개수 만큼 행을 반환했습니다.
-- (3) ORDER BY can be omitted if the ordering of rows is not important.
-- It is also possible to omit PARTITION BY,
-- in which case there is just one partition containing all the rows.
SELECT
salary
, SUM(salary) OVER () -- same result
FROM empsalary;
--salary|sum |
--------+-----+
-- 5000|47100|
-- 3900|47100|
-- 4800|47100|
-- 4800|47100|
-- 3500|47100|
-- 4200|47100|
-- 6000|47100|
-- 4500|47100|
-- 5200|47100|
-- 5200|47100|
(4) SUM() OVER (ORDER BY) : ORDER BY 구문이 추가되면 누적 합으로 결과가 달라짐
만약 PARTITION BY 가 없어서 집단별 구분없이 전체 데이터셋을 대상으로 연산을 하게 될 때, SUM(salary) OVER (ORDER BY salary) 처럼 OVER () 절 안에 ORDER BY 를 추가하게 되면 salary 를 기준으로 정렬이 된 상태에서 누적 합이 계산되므로 위의 (3)번과 차이점을 알아두기 바랍니다.
-- (4) But if we add an ORDER BY clause, we get very different results:
SELECT
salary
, SUM(salary) OVER (ORDER BY salary)
FROM empsalary;
--salary|sum |
--------+-----+
-- 3500| 3500|
-- 3900| 7400|
-- 4200|11600|
-- 4500|16100|
-- 4800|25700|
-- 4800|25700|
-- 5000|30700|
-- 5200|41100|
-- 5200|41100|
-- 6000|47100|
(5) LAG(expression, offset) OVER (ORDER BY), LEAD(expression, offset) OVER (ORDER BY)
이번에는 시간(time), 순서(sequence)가 중요한 시계열 데이터(Time Series data) 로 예제 테이블을 만들어보겠습니다. 시계열 데이터에 대해 Window Function 을 사용하게 되면 OVER (ORDER BY timestamp) 처럼 ORDER BY 를 꼭 포함시켜줘야 겠습니다.
-- (5) LAG() over (), LEAD() over ()
-- making a sample TimeSeries table
DROP TABLE IF EXISTS ts;
CREATE TABLE ts (
dt DATE
, id INT
, val INT
);
INSERT INTO ts (dt, id, val) VALUES
('2022-02-10', 1, 25)
, ('2022-02-11', 1, 28)
, ('2022-02-12', 1, 35)
, ('2022-02-13', 1, 34)
, ('2022-02-14', 1, 39)
, ('2022-02-10', 2, 40)
, ('2022-02-11', 2, 35)
, ('2022-02-12', 2, 30)
, ('2022-02-13', 2, 25)
, ('2022-02-14', 2, 15);
SELECT * FROM ts ORDER BY id, dt;
--dt |id|val|
------------+--+---+
--2022-02-10| 1| 25|
--2022-02-11| 1| 28|
--2022-02-12| 1| 35|
--2022-02-13| 1| 34|
--2022-02-14| 1| 39|
--2022-02-10| 2| 40|
--2022-02-11| 2| 35|
--2022-02-12| 2| 30|
--2022-02-13| 2| 25|
--2022-02-14| 2| 15|
LAG(expression, offset) OVER (PARTITION BY id ORDER BY timestamp) 윈도우 함수는 ORDER BY timestamp 기준으로 정렬을 한 상태에서, id 집합 내에서 현재 행에서 offset 만큼 앞에 있는 행의 값(a row which comes before the current row)을 가져옵니다. 아래의 예를 살펴보는 것이 이해하기 빠르고 쉬울거예요.
-- (5-1) LAG() function to access a row which comes before the current row
-- at a specific physical offset.
SELECT
dt
, id
, val
, LAG(val, 1) OVER (PARTITION BY id ORDER BY dt) AS lag_val_1
, LAG(val, 2) OVER (PARTITION BY id ORDER BY dt) AS lag_val_2
FROM ts
;
--dt |id|val|lag_val_1|lag_val_2|
------------+--+---+---------+---------+
--2022-02-10| 1| 25| | |
--2022-02-11| 1| 28| 25| |
--2022-02-12| 1| 35| 28| 25|
--2022-02-13| 1| 34| 35| 28|
--2022-02-14| 1| 39| 34| 35|
--2022-02-10| 2| 40| | |
--2022-02-11| 2| 35| 40| |
--2022-02-12| 2| 30| 35| 40|
--2022-02-13| 2| 25| 30| 35|
--2022-02-14| 2| 15| 25| 30|
LEAD(expression, offset) OVER (PARTITION BY id ORDER BY timestamp) 윈도우 함수는 ORDER BY timestamp 기준으로 정렬을 한 후에, id 집합 내에서 현재 행에서 offset 만큼 뒤에 있는 행의 값(a row which follows the current row)을 가져옵니다. 아래의 예를 살펴보는 것이 이해하기 빠르고 쉬울거예요.
-- (5-2) LEAD() function to access a row that follows the current row,
-- at a specific physical offset.
SELECT
dt
, id
, val
, LEAD(val, 1) OVER (PARTITION BY id ORDER BY dt) AS lead_val_1
, LEAD(val, 2) OVER (PARTITION BY id ORDER BY dt) AS lead_val_2
FROM ts
;
--dt |id|val|lead_val_1|lead_val_2|
------------+--+---+----------+----------+
--2022-02-10| 1| 25| 28| 35|
--2022-02-11| 1| 28| 35| 34|
--2022-02-12| 1| 35| 34| 39|
--2022-02-13| 1| 34| 39| |
--2022-02-14| 1| 39| | |
--2022-02-10| 2| 40| 35| 30|
--2022-02-11| 2| 35| 30| 25|
--2022-02-12| 2| 30| 25| 15|
--2022-02-13| 2| 25| 15| |
--2022-02-14| 2| 15| | |
(6) FIRST_VALUE() OVER (), LAST_VALUE() OVER ()
OVER (PARTITION BY id ORDER BY dt) 로 id 집합 내에서 dt 순서를 기준으로 정렬한 상태에서, FIRST_VALUE() 는 첫번째 값을 반환하며, LAST_VALUE() 는 마지막 값을 반환합니다.
OVER() 절 안에 RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING 은 partion by 집합 내의 처음과 끝의 모든 행의 범위를 다 고려하라는 의미입니다.
-- (6) FIRST_VALUE() OVER (), LAST_VALUE() OVER ()
-- The FIRST_VALUE() function returns a value evaluated
-- against the first row in a sorted partition of a result set.
-- The LAST_VALUE() function returns a value evaluated
-- against the last row in a sorted partition of a result set.
-- The RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING clause defined
-- the frame starting from the first row and ending at the last row of each partition.
SELECT
dt
, id
, val
, FIRST_VALUE(val)
OVER (
PARTITION BY id
ORDER BY dt
RANGE BETWEEN UNBOUNDED PRECEDING
AND UNBOUNDED FOLLOWING) AS first_val
, LAST_VALUE(val)
OVER (
PARTITION BY id
ORDER BY dt
RANGE BETWEEN UNBOUNDED PRECEDING
AND UNBOUNDED FOLLOWING) AS last_val
FROM ts
;
--dt |id|val|first_val|last_val|
------------+--+---+---------+--------+
--2022-02-10| 1| 25| 25| 39|
--2022-02-11| 1| 28| 25| 39|
--2022-02-12| 1| 35| 25| 39|
--2022-02-13| 1| 34| 25| 39|
--2022-02-14| 1| 39| 25| 39|
--2022-02-10| 2| 40| 40| 15|
--2022-02-11| 2| 35| 40| 15|
--2022-02-12| 2| 30| 40| 15|
--2022-02-13| 2| 25| 40| 15|
--2022-02-14| 2| 15| 40| 15|
(7) NTILE(buckets) OVER (PARTITION BY ORDER BY)
NTINE(buckets) 의 buckets 개수 만큼 가능한 동일한 크기(equal size)를 가지는 집단으로 나누어줍니다.
아래 예 NTILE(2) OVER (PARTITION BY id ORDER BY val) 는 id 집합 내에서 val 을 기준으로 정렬을 한 상태에서 NTILE(2) 의 buckets = 2 개 만큼의 동일한 크기를 가지는 집단으로 나누어주었습니다.
짝수개면 정확하게 동일한 크기로 나누었을텐데요, id 집단 내 행의 개수가 5개로 홀수개인데 2개의 집단으로 나누려다 보니 가능한 동일한 크기인 3개, 2개로 나뉘었네요.
-- (7) NTILE() function allows you to divide ordered rows in the partition
-- into a specified number of ranked groups as EQUAL SIZE as possible.
SELECT
dt
, id
, val
, NTILE(2) OVER (PARTITION BY id ORDER BY val) AS ntile_val
FROM ts
ORDER BY id, val
;
--dt |id|val|ntile_val|
------------+--+---+---------+
--2022-02-10| 1| 25| 1|
--2022-02-11| 1| 28| 1|
--2022-02-13| 1| 34| 1|
--2022-02-12| 1| 35| 2|
--2022-02-14| 1| 39| 2|
--2022-02-14| 2| 15| 1|
--2022-02-13| 2| 25| 1|
--2022-02-12| 2| 30| 1|
--2022-02-11| 2| 35| 2|
--2022-02-10| 2| 40| 2|
(8) ROW_NUMBER() OVER (ORDER BY)
아래의 예 ROW_NUMBER() OVER (PARTITION BY id ORDER BY dt) 는 id 집합 내에서 dt 를 기준으로 올림차순 정렬 (sort in ascending order) 한 상태에서, 1 부터 시작해서 하나씩 증가시켜가면서 행 번호 (row number) 를 부여한 것입니다. 집합 내에서 특정 기준으로 정렬한 상태에서 특정 순서/위치의 값을 가져오기 한다거나, 집합 내에서 unique 한 ID 를 생성하고 싶을 때 종종 사용합니다.
-- (8) ROW_NUMBER() : Number the current row within its partition starting from 1.
SELECT
dt
, id
, val
, ROW_NUMBER() OVER (PARTITION BY id ORDER BY dt) AS seq_no
FROM ts
;
--dt |id|val|seq_no|
------------+--+---+------+
--2022-02-10| 1| 25| 1|
--2022-02-11| 1| 28| 2|
--2022-02-12| 1| 35| 3|
--2022-02-13| 1| 34| 4|
--2022-02-14| 1| 39| 5|
--2022-02-10| 2| 40| 1|
--2022-02-11| 2| 35| 2|
--2022-02-12| 2| 30| 3|
--2022-02-13| 2| 25| 4|
--2022-02-14| 2| 15| 5|
이동평균(Moving Average) 는 시계열 데이터를 분석할 때 이상치(Outlier), 특이값, 잡음(Noise) 의 영향을 줄이거나 제거하는 Smoothing 의 목적이나, 또는 미래 예측에 자주 사용됩니다. 개념이 이해하기 쉽고 직관적이기 때문에 실무에서 많이 사용됩니다. 주식 투자를 하는 분이라면 아마도 이동평균에 대해서 익숙할 것입니다.
이동평균에는 가중치를 어떻게 부여하느냐에 따라서 단순이동평균(Simple Moving Average), 가중이동평균(Weighted Moving Average), 지수이동평균(Exponential Moving Average) 등이 있습니다.
이번 포스팅에서는 PostgreSQL, Greenplum DB에서 Window Function 을 사용하여 가중치를 사용하지 않는 (혹은, 모든 값에 동일한 가중치 1을 부여한다고 볼 수도 있는)
(1) 단순이동평균 계산하기 (Calculating a Simple Moving Average)
(2) 처음 이동평균 날짜 모자라는 부분은 NULL 처리하고 단순이동평균 계산하기
(3) 누적 단순이동평균 계산하기 (Calculating a Cumulative Simple Moving Average)
하는 방법을 소개하겠습니다.
PostgreSQL, Greenplum, Simple Moving Average using Window Function
먼저, 세일즈 날짜와 판매금액의 두 개 칼럼으로 구성된, 예제로 사용할 간단한 시계열 데이터(Time Series Data) 테이블을 만들어보겠습니다.
ROUND(avg(), 1) 함수를 사용해서 단순이동평균값에 대해 소수점 첫째자리 반올림을 할 수 있습니다. 그리고 필요 시 단순이동평균 계산할 대상을 조회할 때 WHERE 조건절을 추가할 수도 있습니다.
-- Calculating a Moving Average for last 3 days using Window Function
SELECT
sale_dt
, sale_amt
, ROUND(
AVG(sale_amt)
OVER(
ORDER BY sale_dt
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW), 1
) AS avg_sale_amt
FROM sales
ORDER BY sale_dt;
--sale_dt sale_amt avg_sale_amt
--2021-06-01 230 230.0
--2021-06-02 235 232.5
--2021-06-03 231 232.0
--2021-06-04 244 236.7
--2021-06-05 202 225.7
--2021-06-06 260 235.3
--2021-06-07 240 234.0
--2021-06-08 235 245.0
--2021-06-09 239 238.0
--2021-06-10 242 238.7
--2021-06-11 244 241.7
--2021-06-12 241 242.3
--2021-06-13 246 243.7
--2021-06-14 247 244.7
--2021-06-15 249 247.3
--2021-06-16 245 247.0
--2021-06-17 242 245.3
--2021-06-18 246 244.3
--2021-06-19 245 244.3
--2021-06-20 190 227.0
--2021-06-21 230 221.7
--2021-06-22 235 218.3
--2021-06-23 231 232.0
--2021-06-24 238 234.7
--2021-06-25 241 236.7
--2021-06-26 245 241.3
--2021-06-27 242 242.7
--2021-06-28 243 243.3
--2021-06-29 240 241.7
--2021-06-30 238 240.3
날짜를 X 축으로 놓고, Y 축에는 날짜별 (a) 세일즈 금액, (b) 3일 단순이동평균 세일즈 금액 을 시계열 그래프로 나타내서 비교해보면 아래와 같습니다. 예상했던대로 '3일 단순이동평균' 세일즈 금액이 스파이크(spike) 없이 smoothing 되어있음을 확인할 수 있습니다.
아래 코드는 Jupyter Notebook에서 Python 으로 Greenplum DB에 연결(connect)하여, SQL query 를 해온 결과를 Python pandas의 DataFrame으로 만들어서, matplotlib 으로 시계열 그래프를 그려본 것입니다.
## --- Jupyter Notebook ---
import pandas as pd
import matplotlib.pyplot as plt
## loading ipython, sqlalchemy, spycopg2
%load_ext sql
## Greenplum DB connection
%sql postgresql://dsuser:changeme@localhost:5432/demo
#'Connected: dsuser@demo'
## getting data from Greenplum by DB connection from jupyter notebook
%%sql sam << SELECT
sale_dt
, sale_amt
, ROUND(
AVG(sale_amt)
OVER(
ORDER BY sale_dt
ROWS BETWEEN 2 PRECEDING
AND CURRENT ROW)
, 1
) AS avg_sale_amt
FROM sales
ORDER BY sale_dt;
# * postgresql://dsuser:***@localhost:5432/demo
#30 rows affected.
#Returning data to local variable sam
## converting to pandas DataFrame
sam_df = sam.DataFrame()
sam_df.head()
#sale_dt sale_amt avg_sale_amt
#0 2021-06-01 230 230.0
#1 2021-06-02 235 232.5
#2 2021-06-03 231 232.0
#3 2021-06-04 244 236.7
#4 2021-06-05 202 225.7
## plotting time-series plot
import matplotlib.pyplot as plt
plt.rcParams['figure.figsize'] = [14, 10]
plt.plot(sam_df.sale_dt, sam_df.sale_amt, marker='s', color='r', label='original')
plt.plot(sam_df.sale_dt, sam_df.avg_sale_amt, marker='o', color='b', label='moving average')
plt.title('Simple Moving Average', fontsize=18)
plt.xlabel('Sale Date', fontsize=14)
plt.ylabel('Sale Amount', fontsize=14)
plt.legend(fontsize=12, loc='best')
plt.show()
original data vs. simple moving average
(2) 처음 이동평균 날짜 모자라는 부분은 NULL 처리하고 단순이동평균 계산하기
위의 (1)번에서 '3일 단순이동평균' 값을 계산할 때 시계열 데이터가 시작하는 첫번째와 두번째 날짜에 대해서는 이전 데이터가 존재하지 않기 때문에 '3일치' 데이터가 부족하게 됩니다. (만약 '10일 단순이동평균'을 계산한다고 하면 처음 시작하는 9일치 데이터의 경우 '10일치' 데이터에는 모자라게 되겠지요.)
위의 (1)번에서는 이처럼 '3일치' 데이터가 모자라는 '2021-06-01', '2021-06-02' 일의 경우 '3일치'가 아니라 '1일치', '2일치' 단순이동평균으로 대체 계산해서 값을 채워넣었습니다.
하지만, 필요에 따라서는 '3일치 단순이동평균'이라고 했을 때 이전 데이터가 '3일치'가 안되는 경우에는 단순이동평균을 계산하지 말고 그냥 'NULL' 값으로 처리하고 싶은 경우도 있을 것입니다. 이때 (2-1) CASE WHEH 과 AVG(), OVER() window function을 사용하는 방법, (2-2) LAG(), OVER() window function 을 이용하는 방법의 두 가지를 소개하겠습니다.
(2-1) CASE WHEH 과 AVG(), OVER() window function을 사용하여 단순이동평균 계산하고, 이동평균계산 날짜 모자라면 NULL 처리하는 방법
SELECT
sale_dt
, sale_amt
, CASE WHEN
row_number() OVER(ORDER BY sale_dt) >= 3
THEN
ROUND(
AVG(sale_amt)
OVER(
ORDER BY sale_dt
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW)
, 1)
ELSE NULL END
AS avg_sale_amt
FROM sales
ORDER BY sale_dt;
--sale_dt sale_amt avg_sale_amt
--2021-06-01 230 NULL
--2021-06-02 235 NULL
--2021-06-03 231 232.0
--2021-06-04 244 236.7
--2021-06-05 202 225.7
--2021-06-06 260 235.3
--2021-06-07 240 234.0
--2021-06-08 235 245.0
--2021-06-09 239 238.0
--2021-06-10 242 238.7
--2021-06-11 244 241.7
--2021-06-12 241 242.3
--2021-06-13 246 243.7
--2021-06-14 247 244.7
--2021-06-15 249 247.3
--2021-06-16 245 247.0
--2021-06-17 242 245.3
--2021-06-18 246 244.3
--2021-06-19 245 244.3
--2021-06-20 190 227.0
--2021-06-21 230 221.7
--2021-06-22 235 218.3
--2021-06-23 231 232.0
--2021-06-24 238 234.7
--2021-06-25 241 236.7
--2021-06-26 245 241.3
--2021-06-27 242 242.7
--2021-06-28 243 243.3
--2021-06-29 240 241.7
--2021-06-30 238 240.3
(2-2) LAG(), OVER() window function을 사용하여 단순이동평균 계산하고, 이동평균계산 날짜 모자라면 NULL 처리하는 방법
아래 LAG() 함수를 사용한 방법은 이렇게도 가능하다는 예시를 보여준 것이구요, 위의 (2-1) 과 비교했을 때 'x일 단순이동평균'에서 'x일'이 숫자가 커질 경우 수작업으로 LAG() 함수를 'x일' 날짜만큼 모두 써줘야 하는 수고를 해줘야 하고, 그 와중에 휴먼 에러가 개입될 여지도 있어서 아무래도 위의 (2-1) 방법이 더 나아보입니다.
(3) 누적 단순이동평균 계산하기 (Calculating a Cumulative Simpe Moving Average)
처음 시작하는 날짜부터 해서 누적으로 단순이동 평균 (Cumulative Moving Average) 을 계산하고 싶을 때는 아래처럼 AVG(sale_amt) OVER(ORDER BY sale_dt ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) 처럼 window 범위를 처음부터 현재까지로 설정해주면 됩니다.
아래 예에서 '2021-06-05'일까지의 누적 단순이동평균 값은 아래와 같이 계산되었습니다.
window function은 n개의 행을 input으로 받아서 n개의 행을 가진 output을 반환하는 함수를 말합니다.
지난번 포스팅에서는 dplyr package의 window function 중에서 Ranking and Ordering을 하는 함수들로서 row_number(), min_rank(), dense_rank(), cume_dist(), percent_rank(), ntile() 에 대해서 알아보았습니다. (바로가기 http://rfriend.tistory.com/241)
이번 포스팅에서는 dplyr 패키지의 window function 두번째 시간으로서 특정 칼럼의 행을 위로 올리거나(Lead) 아니면 내리는(Lag) 함수에 대해서 알아보겠습니다.
lead() 나 lag() 함수는 시계열 데이터를 분석할 때 많이 사용하는 편입니다. 특정 그룹id와 날짜/시간 기준으로 정렬(sorting)을 해놓은 다음에, lead() 나 lag() 함수를 가지고 행을 하나씩 내리구요, 직전 날짜/시간 대비 이후의 값의 변화, 차이(difference)를 구하는 식으로 말이지요.
(시계열분석에 특화된 package를 사용하면 더 편하기 하지만.... 데이터 프레임을 가지고 dplyr 패키지의 lead()나 lag() 함수 알아놓는것도 유용해요)
먼저 간단한 벡터를 가지고 lead()와 lag() 사용법을 소개하겠습니다.
(1) lead(x, n = 1L, default = NA, ...) in {dplyr} package
lead() 함수는 벡터 값을 n = 1L (양의 정수값) 의 값 만큼 앞에서 제외하고, 제일 뒤의 n = 1L 값만큼의 값은 NA 로 채워놓은 값을 반환합니다. 이때, n 표기는 생략할 수 있습니다.
> ##-------------------------------------------------
> ## R dplyr package : window function - lead and lag
> ##-------------------------------------------------
> > library(dplyr)
> > # lead()
> x <- c(1:10)
> > lead(x, n = 1)
[1] 2 3 4 5 6 7 8 9 10 NA
> > lead(x, 2)
[1] 3 4 5 6 7 8 9 10 NA NA
(2) lag(x, n = 1L, default = NA, ...) in {dplyr} package
lag() 함수는 lead() 함수와 정반대로 생각하시면 됩니다. lag() 함수의 n = 1L(양의 정수값) 만큼 제일 앞자리부터 뒤로 옮기고, n = 1L 개수 만큼의 자리에 NA 값을 채워넣은 값을 반환합니다.
default = "." 처럼 특정 값을 설정해주면 NA 대신 새로 설정해준 값 혹은 기호가 채워진 값을 반환합니다. (아래 세번째 예의 경우 "."으로 빈 자리가 채워지면서 모든 값에 큰 따옴표("")가 붙으면서 character 형태로 바뀌었습니다)
[문제] 위의 x_df 데이터 프레임의 group 별로 직전 대비 직후 값의 차이가 가장 큰 값(max)과 가장 작은 값을 각각 구하시오. (What are the max and min difference values between x and lag(x) of x_df dataframe by group?)
(0) 예제 데이터 프레임 만들기
분석할 때 보통 벡터 보다는 데이터 프레임을 가지고 많이 하므로 예제 데이터 프레임을 하나 만들어보겠습니다. 'group'이라는 요인(factor)형 변수와 seq_no 이라는 시간 순서를 나타내는 변수, 그리고 각 group별로 5개씩의 관찰값을 가진 숫자형(numeric) 변수 x로 구성된 데이터 프레임입니다.
> ##-- make data frame as an example
> group <- rep(c("A", "B"), each = 5)
> seq_no <- rep(1:5, 2)
> set.seed(1234)
> x <- round(100*runif(10), 1)
> > x_df <- data.frame(group, seq_no, x)
> x_df
group seq_no x
1 A 1 11.4
2 A 2 62.2
3 A 3 60.9
4 A 4 62.3
5 A 5 86.1
6 B 1 64.0
7 B 2 0.9
8 B 3 23.3
9 B 4 66.6
10 B 5 51.4
(1) lag() 하려고 하는 기준대로 정렬이 안되어 있으면 -> 먼저 정렬(sorting) 부터!
예제로 사용하려고 sample(nrow()) 함수로 무작위로 순서를 섞어서 x_df_random 이라는 데이터 프레임을 만들어보았습니다. dplyr 패키지의 arrange() 함수를 가지고 group, seq_no 기준으로 정렬을 해보겠습니다.
> # if data frame is not ordered properly,
> # then arrnage it first by lag criteria
> x_df_random <- x_df[sample(nrow(x_df)),]
> x_df_random
group seq_no x
7 B 2 0.9
5 A 5 86.1
3 A 3 60.9
10 B 5 51.4
2 A 2 62.2
9 B 4 66.6
6 B 1 64.0
1 A 1 11.4
8 B 3 23.3
4 A 4 62.3
> > x_df_seq <- arrange(x_df_random, group, seq_no)
> x_df_seq
group seq_no x
1 A 1 11.4
2 A 2 62.2
3 A 3 60.9
4 A 4 62.3
5 A 5 86.1
6 B 1 64.0
7 B 2 0.9
8 B 3 23.3
9 B 4 66.6
10 B 5 51.4
(2) mutate() 함수와 lag() 함수로 group_x, x_lag 변수 만들기
> # making lagged variable at data frame with mutate() and lag()
> x_df_seq_lag <- mutate(x_df_seq,
+ group_lag = lag(group, 1),
+ x_lag = lag(x, 1))
> > x_df_seq_lag
group seq_no x group_lag x_lag
1 A 1 11.4 <NA> NA
2 A 2 62.2 A 11.4
3 A 3 60.9 A 62.2
4 A 4 62.3 A 60.9
5 A 5 86.1 A 62.3
6 B 1 64.0 A 86.1 <- need to delete this row
7 B 2 0.9 B 64.0
8 B 3 23.3 B 0.9
9 B 4 66.6 B 23.3
10 B 5 51.4 B 66.6
(3) group 과 group_lag 의 값이 서로 다르면 그 행(row)은 filter() 함수로 제외하기
> # if group and group_lag are different, then delete the row
> x_df_seq_lag_2 <- x_df_seq_lag %>%
+ filter(group == group_lag)
> > x_df_seq_lag_2
group seq_no x group_lag x_lag
1 A 2 62.2 A 11.4
2 A 3 60.9 A 62.2
3 A 4 62.3 A 60.9
4 A 5 86.1 A 62.3
5 B 2 0.9 B 64.0
6 B 3 23.3 B 0.9
7 B 4 66.6 B 23.3
8 B 5 51.4 B 66.6
(4) group별로 x와 x_lag 값의 차이가 가장 큰 값(max)과 가장 작은 값(min) 구하기
지지난번 포스팅에서 소개했던 group_by()와 summarise(max()), summarise(min()) 함수를 이용하면 되겠습니다.
> # select max and min of difference between x and x_lag
> x_df_seq_lag_2 %>%
+ group_by(group) %>%
+ summarise(max_lag = max(x - x_lag, na.rm = TRUE),
+ min_lag = min(x - x_lag, na.rm = TRUE))
# A tibble: 2 x 3
group max_lag min_lag
<fctr> <dbl> <dbl>
1 A 50.8 -1.3
2 B 43.3 -63.1
(5) 위의 1~4를 chain operator와 group_by() 를 사용해서 한번에 모두 처리하기.
