이번 포스팅에서는 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 --> 그룹의 개수만큼 행 반환

   (c) Window Function: AVG() OVER (PARTITION BY)  --> ID의 개수만큼 행 반환

별로 차이를 비교해보았습니다. 

 

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

 

 

[ Reference ]

* PostgreSQL Window Functions
   : https://www.postgresql.org/docs/9.6/tutorial-window.html

* PostgreSQL Window Functions Tutorial
   : https://www.postgresqltutorial.com/postgresql-window-function/

 

 

이번 포스팅이 많은 도움이 되었기를 바랍니다. 

행복한 데이터 과학자 되세요!  :-)

 

반응형
Posted by Rfriend

댓글을 달아 주세요

이동평균(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) 테이블을 만들어보겠습니다.  

 

-- creating a sample table
DROP TABLE IF EXISTS sales;
CREATE TABLE sales (
	sale_dt date
	, sale_amt int
) DISTRIBUTED RANDOMLY;

INSERT INTO sales VALUES 
('2021-06-01', 230)
, ('2021-06-02', 235)
, ('2021-06-03', 231)
, ('2021-06-04', 244)
, ('2021-06-05', 202)
, ('2021-06-06', 260)
, ('2021-06-07', 240)
, ('2021-06-08', 235)
, ('2021-06-09', 239)
, ('2021-06-10', 242)
, ('2021-06-11', 244)
, ('2021-06-12', 241)
, ('2021-06-13', 246)
, ('2021-06-14', 247)
, ('2021-06-15', 249)
, ('2021-06-16', 245)
, ('2021-06-17', 242)
, ('2021-06-18', 246)
, ('2021-06-19', 245)
, ('2021-06-20', 190)
, ('2021-06-21', 230)
, ('2021-06-22', 235)
, ('2021-06-23', 231)
, ('2021-06-24', 238)
, ('2021-06-25', 241)
, ('2021-06-26', 245)
, ('2021-06-27', 242)
, ('2021-06-28', 243)
, ('2021-06-29', 240)
, ('2021-06-30', 238);

SELECT * FROM sales ORDER BY sale_dt LIMIT 5;

--sale_dt        sale_amt
--2021-06-01	230
--2021-06-02	235
--2021-06-03	231
--2021-06-04	244
--2021-06-05	202

 

 

(1) 단순이동평균 계산하기 (Calculating a Simple Moving Average) 

 

현재 날짜를 기준으로 2일전~현재날짜 까지 총 3일 기간 동안의 값을 사용하여 단순 이동평균을 구해보겠습니다. 

 

moving average for last 3 days = (Xt + Xt-1 + Xt-2) / 3

 

PostgreSQL 의 9.0 이상의 버전에서는 AVG()와  OVER() 의 Window Function을 사용하여 매우 편리하게 단순이동평균 (Simple Moving Average)을 계산할 수 있습니다. 

 

시계열 데이터는 시간의 순서가 중요하므로 OVER(ORDER BY sale_dt)  에서 먼저 날짜를 기준으로 정렬을 해주어야 합니다.

 

OVER(ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) 로 2일전~현재날짜 까지 총 3일 간의 Window 를 대상으로 평균을 계산하는 것을 지정해줍니다. 

 

가령, 아래의 '2021-06-03' 일의 3일 단순이동평균값은 아래와 같이 '2021-06-01', '2021-06-02', '2021-06-03' 일의 3일치 세일즈 판매금액의 평균이 되겠습니다. 

 

* 3일 단순이동평균('2021-06-03') = (230 + 235 + 231) / 3 = 232.0

 

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에서 PostgreSQL, Greenplum DB connect 하여 데이터 가져오는 방법은 https://rfriend.tistory.com/577, https://rfriend.tistory.com/579 참조)

 

## --- 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) 방법이 더 나아보입니다. 

 

-- Calculating a Simple Moving Average using LAG() Window Function

SELECT 
	sale_dt
	, sale_amt
	, ROUND(
			(sale_amt::NUMERIC 
				+ LAG(sale_amt::NUMERIC, 1) OVER(ORDER BY sale_dt) 
				+ LAG(sale_amt::NUMERIC, 2) OVER(ORDER BY sale_dt)
			)/3
			, 1) 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

 

 

 

(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'일까지의 누적 단순이동평균 값은 아래와 같이 계산되었습니다. 

 

Cumulative simple moving average('2021-06-05') = (230 + 235 + 231 + 244 + 202) / 5 = 228.4

 

-- Calculating a Cumulative Moving Average
SELECT 
	sale_dt
	, sale_amt
	, ROUND(
		AVG(sale_amt) 
		OVER(
			ORDER BY sale_dt 
			ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
			, 1) AS avg_cum_sale_amt
FROM sales 
ORDER BY sale_dt;

--sale_dt  sale_amt  avg_cum_sale_amt
--2021-06-01	230	230.0
--2021-06-02	235	232.5
--2021-06-03	231	232.0
--2021-06-04	244	235.0
--2021-06-05	202	228.4
--2021-06-06	260	233.7
--2021-06-07	240	234.6
--2021-06-08	235	234.6
--2021-06-09	239	235.1
--2021-06-10	242	235.8
--2021-06-11	244	236.5
--2021-06-12	241	236.9
--2021-06-13	246	237.6
--2021-06-14	247	238.3
--2021-06-15	249	239.0
--2021-06-16	245	239.4
--2021-06-17	242	239.5
--2021-06-18	246	239.9
--2021-06-19	245	240.2
--2021-06-20	190	237.7
--2021-06-21	230	237.3
--2021-06-22	235	237.2
--2021-06-23	231	236.9
--2021-06-24	238	237.0
--2021-06-25	241	237.1
--2021-06-26	245	237.4
--2021-06-27	242	237.6
--2021-06-28	243	237.8
--2021-06-29	240	237.9
--2021-06-30	238	237.9

 

 

이번 포스팅이 많은 도움이 되었기를 바랍니다. 

행복한 데이터 과학자 되세요. :-)

 

반응형
Posted by Rfriend

댓글을 달아 주세요

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 형태로 바뀌었습니다)

 

 

> # lag()
> x <- c(1:10)
> 
> lag(x, n = 1)
 [1] NA  1  2  3  4  5  6  7  8  9
> 
> lag(x, 2)
 [1] NA NA  1  2  3  4  5  6  7  8
> 
> lag(x, 2, default = ".")
 [1] "." "." "1" "2" "3" "4" "5" "6" "7" "8"

 

 

 

 

 


 

[문제] 위의 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() 를 사용해서 한번에 모두 처리하기



library(dplyr)


## sample data

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)


## max and min of (x - x_lag)

x_df %>% 

  arrange(group, seq_no) %>% 

  group_by(group) %>% 

  summarise(max_lag = max(x - lag(x, 1), na.rm = TRUE), 

            min_lag = min(x - lag(x, 1), na.rm = TRUE))


# # A tibble: 2 x 3

# group max_lag min_lag

# <chr>   <dbl>   <dbl>

#   1 A        50.8    -1.3

# 2 B        43.3   -63.1

 



그리 어렵지 않지요? 

많은 도움이 되었기를 바랍니다.

 

다음번 포스팅에서는 dplry package window function 세번째로 시간으로 Cumulative aggregates 를 하는데 사용하는 cumall(), cumany(), cummean() 함수, 그리고 Recycled aggregates 에 대해서 알아보겠습니다.

 

이번 포스팅이 도움이 되었다면 아래의 '공감 ~♡'를 눌러주세요.

(Tistory 가 포스팅별로 조회수를 알려주는 기능이 없다보니 '공감♡' 개수로 참고하려고)

 

 

반응형
Posted by Rfriend

댓글을 달아 주세요

  1. 김송아 2020.11.09 10:55  댓글주소  수정/삭제  댓글쓰기

    안녕하세요! r friend님. 이렇게 계속 답글로 질문드리게 되네요. 혹시 이글을 보신다면 한가지 여쭤보고 싶은게 있습니다.
    예시에서 group과 group_lag의 값이 다르면 해당 로우를 제외하는 방법을 알려주셨는데요. 제가 가진 자료 같은 경우에는 계속해서 데이터를 축적해나가는 경우라 해당로우를 제외할수 없는 경우가 발생합니다..T_T 여러가지 방법을 생각해 보았지만 제가 실력이 부족해서 정답이 떠오르지 않아 이렇게 문의 드립니다.. T_T

  2. 김송아 2020.11.09 10:56  댓글주소  수정/삭제  댓글쓰기

    모르고 엔터를 쳐서 이어서 다시 적습니다;

    혹시 이럴경우에 해당로우를 제외하지 않고 다른값으로 치환한다거나 하는 방법이 있을까요?


    감사합니다

    • Rfriend 2020.11.11 19:36 신고  댓글주소  수정/삭제

      안녕하세요 김송아 님,

      제가 이번주 월요일에 핸드폰을 바꾸었는데요, Tistory 어플만 다운로드 해놓고 로그인 하는걸 깜빡해서 댓글이 달린줄을 오늘 저녁에서야 알았습니다. 답변 늦어서 죄송합니다.

      아래 코드처럼 chain operator(%>%)와 group_by(), summarise(), lag() 함수를 한꺼번에 이어서 사용하면 원본 데이터(x_df)는 안건드린채로 원하는 결과를 얻을 수 있습니다. (포스팅 본문 마지막에 업데이트 해두었습니다.)

      # max and min of (x - x_lag)
      x_df %>%
      arrange(group, seq_no) %>%
      group_by(group) %>%
      summarise(max_lag = max(x - lag(x, 1), na.rm = TRUE),
      min_lag = min(x - lag(x, 1), na.rm = TRUE))