"Drift"와 "Shift"는 시계열 데이터 분석에서 중요한 두 가지 개념이며, 시간에 따른 데이터의 다양한 변화 또는 변동을 나타냅니다. 

 

이번 포스팅에서는 

 

1. 시계열 데이터의 Drift 란 무엇인가? 

2. 시계열 데이터의 Shift 란 무엇인가? 

3. 시계열 데이터의 Drift와 Shift 분석은 어떻게 하나?

4. Python 을 이용한 시계열 데이터 Drift, Shift 생성/ 시각화 및 탐지 예시

 

에 대해서 소개하겠습니다. 

 

 

Time Series - Drift, Shift

 


1. 시계열 데이터에서의 Drift 란 무엇인가?  

 

- 정의: Drift 는 시계열 데이터가 시간에 따라 느리고 점진적이며 종종 선형적으로 변화는 것(a slow, gradual, and often linear change in the time series data over time)을 의미합니다. 이는 한 방향으로 지속적인 변화 경향, 즉 상승 또는 하락하는 경향입니다. 

- 예시: 재무 데이터에서, 주식 가치가 몇 년 동안 지속적으로 상승하거나 하락하는 경우, 이는 드리프트의 예입니다. 

- 중요성: 드리프트를 이해하고 식별하는 것은 정확한 예측 및 장기적인 추세를 반영하는 모델을 만드는 데 중요합니다. 드리프트를 무시하면 시간이 지남에 따라 점점 더 부정확해지는 모델이 될 수 있습니다. 




2. 시계열 데이터에서의 Shift 란 무엇인가? 

 

- 정의: Shfit, 종종 "레벨 시프트(level shift)"로 언급되며, 시계열의 평균 또는 분산에서 갑작스러운 변화 (a sudden change in the mean or variance of a time series)를 의미합니다. 드리프트와 달리, 시프트는 갑작스럽게 발생하며 언제든지 일어날 수 있습니다.  

- 유형: 평균 시프트 (Mean Shift)와 분산 시프트 (Variance Shift) 로 나누어볼 수 있습니다. 

  -. 평균 시프트 (Mean Shift): 시리즈의 평균 수준이 갑자기 변경되는 경우입니다. 예를 들어, 성공적인 마케팅 캠페인으로 인해 판매량이 갑자기 증가하는 경우가 이에 해당합니다.  

  -. 분산 시프트 (Variance Shift): 시리즈의 변동성이 변경되는 경우입니다, 예를 들어 주식 가격의 변동성 증가와 같은 경우입니다. 

- 중요성: 시프트를 감지하는 것은 시계열 데이터에서 갑작스러운 변화를 이해하고 대응하는 데 필수적입니다. 시프트는 중대한 사건, 시스템의 구조적 변화 또는 데이터에 영향을 미치는 외부 요인의 변화를 나타낼 수 있습니다.

 

 

3. 시계열 데이터의 Drift와 Shift 분석은 어떻게 하나? 

 

시계열 데이터에서 드리프트와 시프트를 분석하는 것은 시간이 지남에 따라 데이터에서 점진적인 (Drift) 및 급격한 (Shift) 변화를 식별하고 정량화하는 것을 포함합니다. 이 목적을 위해 다양한 방법과 알고리즘이 사용되며, 각각은 데이터 유형과 분석 요구 사항에 적합합니다. 여기에 주요한 몇 가지를 소개합니다. 


3-1. 통계적 공정 관리 방법 (SPC, Statistical Process Control) 

- 제어 차트 (Control Charts): 평균 또는 분산의 변화를 탐지하기 위해 CUSUM (Cumulative Sum Control Chart) 및 EWMA(Exponentially Weighted Moving Average)와 같은 차트가 사용됩니다. 품질 관리에서 널리 사용됩니다. 

- Shewhart 제어 차트 (Shewhart Control Charts): 큰 시프트는 감지할 수 있지만 작은 시프트나 드리프트에는 덜 민감합니다. 


3-2. 시계열 분석 방법 (Time Series Analysis Methods)

- 이동 평균(Moving Average): 단기 변동을 평활화하고 장기 추세나 주기(long-term trends or cycles)를 강조하는 데 도움이 됩니다. 

- 시계열 분해 방법 (Time Series Decomposition Methods): STL (Seasonal and Trend decomposition using Loess)과 같은 기술은 시계열을 추세(Trend), 계절성(Seasonality) 및 잔차(Residual) 요소로 분해하여 드리프트와 시프트를 식별하는 데 도움이 됩니다. 


3-3. 변화점 감지 알고리즘 (Change Point Detection Algorithms)

- Bai-Perron 검정: 선형 모델에서 여러 중단점을 감지하는 데 사용됩니다.

- 변화점 분석 (Changepoint Analysis)PELT (Pruned Exact Linear Time), 이진 분할 (Binary Segmentation), 세그먼트 이웃 (Segment Neighborhoods)과 같은 방법은 대규모 데이터셋에서 다중 변화점을 감지하도록 설계되었습니다.

- CUSUM (Cumulative Sum Control Chart) 및 EWMA (Exponentially Weighted Moving Average): 평균 또는 분산의 변화를 식별하는 변화점 감지에 적응할 수 있습니다.


3-4. 기계 학습 접근 방식 

- 지도 학습 (Supervised Learning): 변화점 전후를 나타내는 레이블이 있는 데이터가 있을 경우, 변화점을 감지하기 위해 기계 학습 모델을 훈련할 수 있습니다. 

- 비지도 학습 (Unsupervised Learning): 클러스터링(예: k-means clustering, DBSCAN)과 같은 기술은 데이터 분포의 변화를 감지하여 시프트를 식별하는 데 사용될 수 있습니다. 


3-5. 신호 처리 기술 (Signal Processing Techniques)

- 푸리에 변환 (Fourier Transform): 주파수 영역의 시프트를 식별하는 데 유용합니다. 

- 웨이블릿 변환 (Wavelet Transform): 비정상 시계열에서 급격한 및 점진적인 변화를 모두 감지하는 데 효과적입니다. 


3-6. 베이지안 방법 (Bayesia Methods) 

- 베이지안 실시간 변화점 감지 (Bayesian Online Change Point Detection): 실시간 데이터 스트림에서 변화점을 감지하는 데 유용한 확률적 접근 방식입니다. 


3-7. 계량경제학에서의 구조적 분석 검정 

- Dickey-Fuller 검정: 시계열 샘플에서 단위근을 테스트하는 데 사용되며, 이는 종종 드리프트의 징후입니다. 

- Chow 검정: 특정 시점에서 구조적 중단이 있는지 여부를 결정하기 위해 설계되었습니다. 


3-8. 회귀 기반 방법 (Regression-based Methods)

- 선형 회귀 (Linear Regression): 기울기 계수 (slope coefficient)를 평가하여 드리프트를 탐지할 수 있습니다. 

- 분할 회귀 (Segmented Regression): 알려지지 않은 시점에서 회귀 모델의 변화를 탐지하는 데 유용합니다. 


적용 방법은 데이터의 특성 (노이즈 수준, 샘플 크기, 계절성 존재 여부 등)과 분석 요구 사항 (실시간 감지, 작은 변화에 대한 민감도 등)에 따라 다릅니다. 실제로 이러한 방법들의 조합이 종종 사용되어 결과를 검증하고 시계열 데이터에 대한 종합적인 이해를 얻습니다. 


요약하자면, Drift와 Shift 분석은 시계열 데이터에서의 변화가 점진적인지 또는 갑작스러운지를 이해하는 데 도움이 됩니다. 이러한 이해는 시계열 데이터를 기반으로 한 정확한 모델링, 예측 및 의사결정에 있어 중요합니다. 



4. Python 을 이용한 시계열 데이터 Drift, Shift 생성/ 시각화 및 탐지 예시 

 

4-1. Python을 이용한 시계열 데이터 Drift, Mean Shift, Variance Shift 생성/ 시각화

 

## Creating a time series with Drift, Mean Shift, Variance Shift
import numpy as np
import pandas as pd
import matplotlib.pyplot as plt

# Setting a seed for reproducibility
np.random.seed(1004)

# (1) Creating a time series with drift
time = np.arange(100)
drift = 0.5 * time  # Linear drift, Trend
noise = np.random.normal(0, 0.5, 100)
time_series_drift = drift + noise


# (2) Creating a time series with Mean Shift
shift_point = 50
time_series_mean_shift = np.random.normal(0, 0.5, 100)
time_series_mean_shift[shift_point:] += 10  # Adding a shift at the midpoint


# (3) Creating a time series with Variance Shift
first_variance = 0.5
second_variance = 5
time_series_first = np.random.normal(0, first_variance, 50)
time_series_second = np.random.normal(0, second_variance, 50)
time_series_var_shift = np.concatenate((time_series_first, time_series_second), axis=0)


# Plotting the time series
plt.figure(figsize=(8, 12))

plt.subplot(3, 1, 1)
plt.plot(time, time_series_drift)
plt.title("Time Series with Drift")

plt.subplot(3, 1, 2)
plt.plot(time, time_series_mean_shift)
plt.title("Time Series with Mean Shift")

plt.subplot(3, 1, 3)
plt.plot(time, time_series_var_shift)
plt.title("Time Series with Variance Shift")

plt.tight_layout()
plt.show()

 

Time Series - Drift, Mean Shift, Variance Shift

 

 

 

4-2. Python 으로 회귀모형 기반 Drift 분석

 

## Analysis of Time Series Drift using Linear Regression Model
from sklearn.linear_model import LinearRegression

# Reshape data for sklearn
X = time.reshape(-1, 1)
y = time_series_drift

# Linear regression
model = LinearRegression()
model.fit(X, y)

# Slope coefficients
print('---' * 10)
print(f'Slope Coefficient: {model.coef_[0]:.3f}')
print('---' * 10)

# Predicted trend line
trend_line = model.predict(X)

# Plotting
plt.figure(figsize=(6, 4))
plt.plot(time, time_series_drift, label='Time Series')
plt.plot(time, trend_line, label='Trend Line', color='red')
plt.title("Drift Detection")
plt.legend()
plt.show()

 

Detecting Time Series Drift using Linear Regression Method

 

 

 

4-3. Python으로 시계열 Shift 분석

 

- Mean Shift 탐지

 

# Detect Mean Shift
def detect_mean_shift(series, window=5):
    for i in range(window, len(series) - window):
        before = series[i-window:i]
        after = series[i:i+window]
        if np.abs(np.mean(after) - np.mean(before)) > 9:  # Threshold for shift
            return i
    return None

mean_shift_point_detected = detect_mean_shift(time_series_mean_shift)

print("----" * 10)
print(f"Mean Shift detected at point: {mean_shift_point_detected}")
print("----" * 10)

# ----------------------------------------
# Shift detected at point: 50
# ----------------------------------------

 

 

 

- PELT (Pruned Exact Linear Time), Binary Segment 알고리즘을 이용한 변곡점 탐지

 

##-- install "ruptures" module at terminal 
!pip install ruptures


## -- change point detection using PELT (Pruned Exact Linear Time) Algorithm
# Specify the PELT model and fit it to the data
model = "l2"  # Change to "rbf" for the radial basis function cost model
algo = rpt.Pelt(model=model).fit(time_series_mean_shift)

# Retrieve the change points
result = algo.predict(pen=10)  # Adjust the penalty value as needed

# Print the detected change points
print("Change points:", result)
# Change points: [50, 100]


## -- change point detection using Binary Segmentation Algorithm
# Specify the Binary Segment model and fit it to the data
model = "l2"  # Change to "rbf" for the radial basis function cost model
algo = rpt.Binseg(model=model).fit(time_series_mean_shift)

# Retrieve the change points
result = algo.predict(pen=10)  # Adjust the penalty value as needed

# Print the detected change points
print("Change points:", result)
# Change points: [50, 100]

 

 

 

- Variance Shift 탐지 

 

# Detect Variance Shift
def detect_var_shift(series, window=5):
    for i in range(window, len(series) - window):
        before = series[i-window:i]
        after = series[i:i+window]
        if np.abs(np.var(after) - np.var(before)) > 30:  # Threshold for shift
            return i + int(window/2) + 1
    return None

var_shift_point_detected = detect_var_shift(time_series_var_shift)

print("----" * 10)
print(f"Variance Shift detected at point: {var_shift_point_detected}")
print("----" * 10)

# ----------------------------------------
# Variance Shift detected at point: 50
# ----------------------------------------

 

 

 

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

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

 

728x90
반응형
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

 

 

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

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

 

728x90
반응형
Posted by Rfriend
,