[PostgreSQL, Greenplum] 10분 단위 구간별로 시계열 데이터를 집계/요약하기 (aggregating time series values by 10 minutes time span using PostgreSQL, Greenplum)

이번 포스팅에서는 PostgreSQL, Greenplum Database를 이용해서 시계열 데이터를 특정 시간 단위 구간(예: 10분 단위, 1시간 단위, 1일 단위, 1달 단위, 1년 단위 등) 별로 집계/ 요약하는 방법을 소개하겠습니다. 

 

(* python pandas로 특정 시간 단위 구간별로 시계열 데이터를 집계하는 방법은 https://rfriend.tistory.com/495 참조)

 

-- 사전 준비 작업 --

(1) '년-월-일' date dimension table 만들기

(2) '시간:분' time dimension table 만들기

(3) '년-월-일 시간:분' cross-join 한  date & time dimension table 만들기

(4) 예제 시계열 데이터(time series) table 만들기

    ==> '날짜-시간' 기준정보를 '시계열 거래 데이터'에 merge 하기

 

-- 10분 단위 구간별로 시계열 데이터 집계/ 요약하기 --

(5) 10분 단위 구간별로 첫번째 행 값(first row value), 마지막 행 값(last row value) 구하기

(6) 10분 단위 구간별로 합(sum), 누적합(cumulative sum) 구하기 

(7) 10분 단위 구간별로 최소값(min), 1사분위수(1st quantile), 중위수(median), 3사분위수(3rd quantile), 최대값(max), 범위(range) 구하기

(8) 10분 단위 구간별로 평균(mean), 표본 분산(sample variance), 표본 표준편차(sample standard deviation) 구하기

(9) 10분 단위 구간별로 수량 가중 평균 가격 구하기 (amount-weighted average of price)

 

 

(1) '년-월-일' date dimension table 만들기

 

날짜(년-월-일)를 기준으로 년(year), 분기(quarter), 월(month), 주(week), 일(day), 공휴일(holiday) 등의 다양한 관점의 group by 할 수 있는 구분자를 생성해놓은 테이블을 만들어보겠습니다. 날짜(년-월-일) 관련 기준 정보, 매핑 테이블이라고 보면 되겠습니다. 

 

아래 예제는 2019년 12월1일 ~ 2019년 12월 31일 까지의 31일 기간의 날짜 데이터 기준정보를 만들었습니다. 

만약 2019년 1월1일~2019년 12월 31일 까지의 365일 전체의 날짜 데이터 기준정보를 만들고 싶다면 from 절을 아래와 같이 수정해주면 됩니다. 

 

[ 2019년 1월 1일~2019년 12월 31일 (365일) 기간 일 별 기준정보 테이블 만드는 FROM 절 generate query ]

 

FROM (

SELECT '2019-01-01'::DATE + SEQUENCE.DAY AS datum

FROM generate_series(0, 364) AS SEQUENCE(DAY)

GROUP BY SEQUENCE.DAY

     ) DQ

 

 

아래의 postgresql wiki 페이지의 sql query를 참고하였습니다. 

* reference: https://wiki.postgresql.org/wiki/Date_and_Time_dimensions

 

------------------------ -- date dimension table ------------------------ -- create 'dim_date' table DROP TABLE IF EXISTS dim_date; CREATE TABLE dim_date ( date DATE NOT NULL,  year INT NOT NULL,  month INT NOT NULL,  month_nm VARCHAR(9) NOT NULL,  day INT NOT NULL,  day_of_year INT NOT NULL,  weekday_nm VARCHAR(9) NOT NULL,  calender_week INT NOT NULL,  formatted_date CHAR(12) NOT NULL,  quartal CHAR(2) NOT NULL,  year_quartal CHAR(7) NOT NULL,  year_month CHAR(7) NOT NULL,  year_calendar_week CHAR(7) NOT NULL,  weekend VARCHAR(7) NOT NULL,  holiday_korea VARCHAR(20)     NOT NULL,  period VARCHAR(20)     NOT NULL,  cwstart DATE NOT NULL,  cwend DATE NOT NULL,  month_start DATE NOT NULL,  month_end DATE NOT NULL ); -- create index on 'date' CREATE INDEX dim_date_date_idx ON dim_date(date); -- insert generate date dimension data (from 2019-01-01 to 2019-12031) -- into 'dim_date' table INSERT INTO dim_date ( SELECT datum AS date, EXTRACT(YEAR FROM datum)::INT AS year, EXTRACT(MONTH FROM datum)::INT AS month, -- Localized month name to_char(datum, 'TMMonth') AS month_nm, EXTRACT(DAY FROM datum)::INT AS day, EXTRACT(doy FROM datum)::INT AS day_of_year, -- Localized weekday to_char(datum, 'TMDay') AS weekday_nm, -- ISO calendar week EXTRACT(week FROM datum)::INT AS calendar_week, to_char(datum, 'dd. mm. yyyy') AS formatted_date, 'Q' || to_char(datum, 'Q') AS quartal, to_char(datum, 'yyyy/"Q"Q') AS year_quartal, to_char(datum, 'yyyy/mm') AS year_month, -- ISO calendar year and week to_char(datum, 'iyyy/IW') AS year_calendar_week, -- Weekend CASE WHEN EXTRACT(isodow FROM datum) IN (6, 7) THEN 'Weekend'  ELSE 'Weekday' END  AS weekend, -- holidays for Korea in year 2019     CASE WHEN to_char(datum, 'MMDD')         IN ('0101', '0205', '0206', '0207', '0301', '0505', '0512',               '0606', '0815', '0912', '0913', '0914', '1003', '1009', '1225') THEN 'Holiday' ELSE 'No holiday' END AS holiday_korea, -- Some periods of the year, adjust for your organisation and country CASE WHEN to_char(datum, 'MMDD') BETWEEN '0701' AND '0831'         THEN 'Summer break'     WHEN to_char(datum, 'MMDD') BETWEEN '1115' AND '1225'         THEN 'Christmas season'     WHEN to_char(datum, 'MMDD') > '1225' OR to_char(datum, 'MMDD') <= '0106'         THEN 'Winter break' ELSE 'Normal' END AS period, -- ISO start and end of the week of this date : from monday to sunday datum + (1 - EXTRACT(isodow FROM datum))::INTEGER AS CWStart, -- monday datum + (7 - EXTRACT(isodow FROM datum))::INTEGER AS CWEnd,   -- sunday -- Start and end of the month of this date datum + (1 - EXTRACT(DAY FROM datum))::INTEGER AS month_start, ((datum + (1 - EXTRACT(DAY FROM datum))::INTEGER + '1 month'::INTERVAL)::DATE     - '1 day'::INTERVAL)::DATE AS month_end FROM ( SELECT '2019-12-01'::DATE + SEQUENCE.DAY AS datum FROM generate_series(0, 30) AS SEQUENCE(DAY) GROUP BY SEQUENCE.DAY      ) DQ ORDER BY 1); SELECT * FROM dim_date ORDER BY date;

 

 

 

(2) '시간:분' time dimension table 만들기

 

시간('시간:분')을 기준으로 시간(time), 분(minute), 10분 단위 구간(time_span_min_10), 1시간 단위 구간(time_span_hour_1), 일 구분 범주(day_category) 등으로 구분해서 집계/요약할 수 있는 구분자, 기준 정보, 매핑 테이블을 만들어보겠습니다. 

 

* reference: https://wiki.postgresql.org/wiki/Date_and_Time_dimensions

 

------------------------ -- time dimension table ------------------------ -- create dimension table 'dim_time' DROP TABLE IF EXISTS dim_time; CREATE TABLE dim_time ( time_of_day CHAR(6) NOT NULL, hour INT NOT NULL,  minute INT NOT NULL, time_span_min_10 CHAR(15) NOT NULL,  time_span_hour_1 CHAR(15) NOT NULL,  day_category VARCHAR(15) NOT NULL ); -- create index on 'time_of_day' CREATE INDEX dim_time_time_of_day_idx ON dim_time(time_of_day); -- insert generated time_of_day data into 'dim_time' table INSERT INTO dim_time ( SELECT  TO_CHAR(MINUTE, 'hh24:mi') AS time_of_day, -- Hour of the day (0 - 23) EXTRACT(HOUR FROM MINUTE)::INTEGER AS hour,  -- Minute of the day (0 - 1439) (EXTRACT(HOUR FROM MINUTE)*60 +     EXTRACT(MINUTE FROM MINUTE))::INT AS minute, -- Extract and format 10 minutes time span TO_CHAR(     MINUTE -     (EXTRACT(MINUTE FROM MINUTE)::INTEGER % 10       || 'minutes')::INTERVAL, 'hh24:mi') || ' – ' || TO_CHAR(     MINUTE -     (EXTRACT(MINUTE FROM MINUTE)::INTEGER % 10       || 'minutes')::INTERVAL + '10 minutes'::INTERVAL, 'hh24:mi') AS time_span_min_10,  -- Extract and format 1 hour time span TO_CHAR(     MINUTE -     (EXTRACT(MINUTE FROM MINUTE)::INTEGER % 60       || 'minutes')::INTERVAL, 'hh24:mi') || ' – ' || TO_CHAR(     MINUTE -     (EXTRACT(MINUTE FROM MINUTE)::INTEGER % 60       || 'minutes')::INTERVAL + '60 minutes'::INTERVAL, 'hh24:mi') AS time_span_hour_1,  -- Names of day periods CASE WHEN to_char(MINUTE, 'hh24:mi') BETWEEN '06:00' AND '08:59'         THEN 'morning'     WHEN to_char(MINUTE, 'hh24:mi') BETWEEN '09:00' AND '11:59'         THEN 'AM'     WHEN to_char(MINUTE, 'hh24:mi') BETWEEN '12:00' AND '17:59'         THEN 'PM'     WHEN to_char(MINUTE, 'hh24:mi') BETWEEN '18:00' AND '21:59'         THEN 'evening'     ELSE 'night' END AS day_category FROM (SELECT '00:00'::TIME + (SEQUENCE.MINUTE || ' minutes')::INTERVAL         AS MINUTE FROM generate_series(0,1439) AS SEQUENCE(MINUTE) GROUP BY SEQUENCE.MINUTE      ) DQ ORDER BY 1 ); SELECT * FROM dim_time ORDER BY time_of_day LIMIT 60;

 

 

 

(3) '년-월-일 시간:분' cross-join 한  date & time dimension table 만들기

 

(1)번에서 만든 dim_date 테이블과 (2)번에서 만든 dim_time 테이블을 cross join 하여 'dim_date_time' 테이블을 만들어보겠습니다. 

 

31일 (dim_date) * 24시간 * 60분 (dim_time) = 44,640 개의 전체 행 (total number of rows) 을 가지는 '년-월-일 시간:분' (dim_date_time) 기준정보 매핑 테이블을 가지고 이제 년 ~ 분 단위를 넘나들면서 원하는 시간 단위 구간별로 시계열 데이터를 집계/ 요약할 수 있습니다. 

 

앞으로 join 의 기준이 될 '년-월-일 시간:분'(dt) 칼럼에 대해서는 빠른 join을 위해서 index 를 생성해주었습니다. 

 

--------------------------------------- -- cross join b/w dim_date and dim_time --------------------------------------- DROP TABLE IF EXISTS dim_date_time; CREATE TABLE dim_date_time AS ( SELECT  to_char((dim_date.date || ' ' || dim_time.time_of_day)::timestamp,         'yyyy-mm-dd hh24:mi') AS dt , dim_date.* , dim_time.* FROM dim_date CROSS JOIN dim_time ); -- create index on 'dt' CREATE INDEX dim_date_time_dt_idx ON dim_date_time(dt); SELECT * FROM dim_date_time ORDER BY dt LIMIT 5; SELECT COUNT(*) FROM dim_date_time; 44640

 

 

 

(4) 예제 시계열 데이터(time series) table 만들기 ==> '날짜-시간' 기준 정보 merge 하기

 

간단한 예제로 '년-월-일 시간:분'(dt), 가격(price), 수량(amt) 의 3개 칼럼을 가지는 거래 시계열 데이터 테이블을 만들어보겠습니다. 

 

--------------------- -- Transaction table --------------------- -- create ts table DROP TABLE IF EXISTS ts; CREATE TABLE ts ( dt  TEXT NOT NULL , price NUMERIC NOT NULL  , amt INT NOT NULL ); -- insert transaction data into ts table INSERT INTO ts VALUES  ('2019-12-19 00:00', 12, 4),  ('2019-12-19 00:02', 21, 2), ('2019-12-19 00:04', 41, 1), ('2019-12-19 00:06', 79, 4), ('2019-12-19 00:08', 61, 2), ('2019-12-19 00:10', 81, 1), ('2019-12-19 00:12', 24, 3), ('2019-12-19 00:14', 62, 1), ('2019-12-19 00:16', 76, 3), ('2019-12-19 00:18', 63, 1), ('2019-12-19 00:20', 95, 2), ('2019-12-19 00:22', 82, 1), ('2019-12-19 00:24', 82, 3), ('2019-12-19 00:26', 70, 1), ('2019-12-19 00:28', 30, 4), ('2019-12-19 00:30', 33, 1), ('2019-12-19 00:32', 22, 2), ('2019-12-19 00:34', 77, 3), ('2019-12-19 00:36', 58, 3), ('2019-12-19 00:38', 96, 3),  ('2019-12-19 09:02', 10, 2), ('2019-12-19 09:08', 50, 3), ('2019-12-19 14:04', 20, 1), ('2019-12-19 16:00', 10, 5), ('2019-12-19 21:00', 30, 4) ; SELECT * FROM ts ORDER BY dt;

 

 

다음으로, '시계열 거래 데이터'(ts table)에 '날짜-시간 기준정보'(dim_date_time table) 를 '년-월-일 시간:분'(dt) 칼럼을 기준으로 병합(left outer join)해보겠습니다. 이제 특정 시간 단위 구간별로 집계, 요약할 수 있는 데이터 준비가 되었습니다. 

 

※ 만약 '시계열 거래 데이터'에 특정 날짜-시간대에 값이 존재하던지 말던지 간에 동일한 특정 시간 단위 구간별로 값을 집계, 요약하고자 한다면, (즉, '시계열 거래 데이터'가 없으면 NULL이나 '0'으로 집계) LEFT OUTER JOIN 할 때 왼쪽에 '날짜-시간 기준정보'(dim_date_time) 테이블을 놓고, 오른쪽에 '시계열 거래 데이터'(ts) 테이블을 놓고 left outer join 을 해주어야 합니다. 

 

------------------------------------ -- merge 'date-time' dimension table ------------------------------------ DROP TABLE IF EXISTS ts_dt; CREATE TABLE ts_dt AS ( SELECT  a.* , b.date , b.time_span_min_10 , b.time_span_hour_1 , b.day_category FROM ts a  LEFT OUTER JOIN dim_date_time b ON a.dt = b.dt ); SELECT * FROM ts_dt ORDER BY dt LIMIT 5;

 

 

 

(5) 10분 단위 구간별로 첫번째 행 값(first-row value), 마지막 행 값(last row value) 구하기

 

시계열 데이터를 시간 기준으로 정렬했을 때 10분 단위 구간(10 minutes time span)별로 첫번째 행의 값(first row's value)을 구하는 방법에는 두가지가 있습니다. 

 

첫번째 방법은 ROW_NUMBER() OVER (PARTITION BY date, time_span_min_10 ORDER BY dt) 의 window function을 사용하는 것입니다. (오름차순 정렬 order by dt)

 

두번째 방법은 DISTINCT ON (date, time_span_min_10) 함수를 사용하는 것입니다. 

 

-- (1) first, last by time_span_min_109 -- (1-1-1) way 1: first using ROW_NUMBER() WITH time_span_min_10_tmp AS ( SELECT  date , time_span_min_10 , price , ROW_NUMBER() OVER (PARTITION BY date, time_span_min_10 ORDER BY dt)         AS row_num FROM ts_dt ) SELECT  date , time_span_min_10 , price AS price_10m_first FROM time_span_min_10_tmp WHERE row_num = 1 ; -- (1-1-2) way 2: first using DISTINCT ON SELECT DISTINCT ON (date, time_span_min_10) date , time_span_min_10 , price AS price_10m_first FROM ts_dt ORDER  BY date, time_span_min_10, dt, price;

 

 

시간 기준으로 정렬했을 때 10분 단위 구간(10 minutes time span)별 마지막 행의 값(last row's value)을 구하는 두가지 방법은 위에서 소개한 첫번째 행의 값을 구하는 방법에서 정렬 기준을 정반대로 해서, 내림차순 정렬(ORDER BY dt DESC) 을 해주면 됩니다. 

 

-- (1-2-1) way 1: last using ROW_NUMBER() WITH time_span_min_10_tmp AS ( SELECT  date , time_span_min_10 , price , ROW_NUMBER()         OVER (PARTITION BY date, time_span_min_10 ORDER BY dt DESC)         AS row_num FROM ts_dt ) SELECT  date , time_span_min_10 , price AS price_10m_last FROM time_span_min_10_tmp WHERE row_num = 1 ; -- (1-2-2) way 2: last using DISTINCT ON SELECT  DISTINCT ON (date, time_span_min_10) date , time_span_min_10     , price AS price_10m_last FROM ts_dt ORDER  BY date, time_span_min_10, dt DESC, price;

 

 

 

(6) 10분 단위 구간별로 합(sum), 누적합(cumulative sum) 구하기

 

10분 단위 구간(10 minutes time span) 별로 가격(price) 칼럼의 합(sum)을 먼저 구하고, ==> 시간 순서대로 정렬된 상태에서 10분 단위 구간별로 SUM() OVER () window function을 사용해서 누적 합(cumulative sum)을 구해보겠습니다. 

 

-- (2) sum, cumulative sum -- sum, cumulative sum by time_span_min_10 WITH price_10m_sum_tmp AS ( SELECT  date , time_span_min_10 , sum(price) AS price_10m_sum FROM ts_dt GROUP BY 1, 2 ) SELECT  date , time_span_min_10 , price_10m_sum , SUM(price_10m_sum)  OVER ( ORDER BY time_span_min_10  ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)  AS price_10m_cumsum  FROM price_10m_sum_tmp ;

 

 

집계/ 요약을 하는 시간 단위 구간을 위의 10분에서 이번에는 1 시간 (1 hour time span) 단위 구간별로 변경해서 가격 합(sum)과 가격 누적합(cumulative sum)을 구해보겠습니다. 위의 (3)번에서 '날짜-시간 기준정보 테이블'(dim_date_time table)에서 '1시간 단위 구간' 칼럼 (time_span_hour_1) 을 만들어놓았었기 때문에 group by 집계/ 요약하는 기준이 되는 칼럼을 'time_span_min_10'을 'time_span_hour_1'으로 변경해주기만 하면 됩니다. 

 

-- sum, cumulative sum by time_span_hour_1 WITH price_1h_sum_tmp AS ( SELECT  date , time_span_hour_1 , sum(price) AS price_1h_sum FROM ts_dt GROUP BY 1, 2 ) SELECT  date , time_span_hour_1 , price_1h_sum , SUM(price_1h_sum)  OVER ( ORDER BY time_span_hour_1 ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS price_1h_cumsum FROM price_1h_sum_tmp;

 

 

 

(7) 10분 단위 구간별로 최소값(min), 1사분위수(1st quantile), 중앙값(median),         3사분위수(3rd quantile), 최대값(max), 범위(range) 구하기

 

최소값은 min(), 최대값은 max(), 1사분위수/중앙값/3사분위수는 percentile_disc(), 범위는 max() - min() 함수를 이용해서 time_span_min_10 으로 group by 집계, 요약하였습니다. 

 

1/2/3 사분위수(quantile) 구하는 percentile_disc(0.25 / 0.5 / 0.75) within group (order by price) 함수를 사용해서 IQR ( = q3 - q1) 구할 수도 있습니다. 

 

-- (3) min, q1, median, q3, max, range by time_span_min_10 SELECT  date  , time_span_min_10 , MIN(price) AS price_10m_min , PERCENTILE_DISC(0.25) WITHIN GROUP (ORDER BY price) AS price_10m_q1 , PERCENTILE_DISC(0.5) WITHIN GROUP (ORDER BY price) AS price_10m_median , PERCENTILE_DISC(0.75) WITHIN GROUP (ORDER BY price) AS price_10m_q3 , MAX(price) AS price_10m_max , MAX(price) - MIN(price) AS price_10m_range FROM ts_dt GROUP BY 1, 2 ORDER BY 1, 2;

 

 

 

(8) 10분 단위 구간별로 평균(mean), 분산(variance), 표준편차(standard deviation) 구하기

 

10분 단위 구간(10 minutes time span) 별로 표본 분산(sample variance)은 variance() 함수, 표본 표준편차(sample standard deviation)은 stddev() 함수를 이용하여 구하였습니다. 

 

10분 단위 구간별로 만약 행의 개수가 1개만 있는 경우 분산, 표준편차를 구할 수 없으므로 NULL 값을 반환하게 되는데요,  이런 경우 coalesce() 를 사용해서 '0'으로 채워주었습니다. 

 

-- (4) mean, variance, standard deviation by time_span_min_10 SELECT  date  , time_span_min_10 , AVG(price) AS price_10m_mean , COALESCE(VARIANCE(price), 0) AS price_10m_var , COALESCE(STDDEV(price), 0) AS price_10m_stddev FROM ts_dt GROUP BY 1, 2 ORDER BY 1, 2;

 

 

 

(9) 10분 단위 구간별로 수량 가중 평균 가격 구하기 (amount-weighted average of price)

 

 

-- (5) amount-weighted average price WITH price_mult_amt_tmp AS ( SELECT  dt , date  , time_span_min_10 , amt , (price * amt) AS price_mult_amt FROM ts_dt ) SELECT  date  , time_span_min_10 , ROUND((sum(price_mult_amt) / sum(amt)), 2) AS price_10m_amount_weighted_avg FROM price_mult_amt_tmp GROUP BY 1, 2 ORDER BY 1, 2;

 

 

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

이번 포스팅이 도움이 되었다면 아래의 '공감~

'를 꾹 눌러주세요. :-)