시계열 데이터의 세분성 조정하기

PostgreSQL을 활용한 시계열 분석

Jasmin Ludolf

Content Developer, DataCamp

시계열 데이터

데이터 유형과 시간 데이터
순서가 있음
일정 기간에 걸쳐 수집됨
대표 예시: 주가, 기온

x축에 시간을 표시한 선 그래프 예시입니다.

시계열 데이터: 열차 시간표

SELECT station, arrival_time
FROM train_schedule
WHERE train_id = 324

|station      |arrival_time|
|-------------|------------|
|San Francisco|    07:59:00|
|22nd Street  |    08:03:00|
|Millbrae     |    08:16:00|
|Hillsdale    |    08:24:00|
|Redwood City |    08:31:00|
|Palo Alto    |    08:37:00|
...

다변량 시계열

SELECT year_month, t_monthly_min, t_monthly_max, t_monthly_avg
FROM temperature_stations AS ts
JOIN temperatures_monthly AS tm USING(station_id)
WHERE station_id = 1

|year_month|t_monthly_min|t_monthly_max|t_monthly_avg|
|----------|-------------|-------------|-------------|
|2010-01-01|          6.7|         20.3|         13.6|
|2010-02-01|          8.3|         20.8|         14.8|
|2010-03-01|          9.1|         23.9|         17.0|
|2010-04-01|         11.3|         27.1|         20.0|
|2010-05-01|         15.0|         32.1|         24.8|
...

시간 세분성

세분성이 높을수록 측정이 정밀해짐
일반적인 세분성 단위: 초, 분, 시간, 일, 주, 월, 분기, 연

SELECT ts, views
FROM dc_news_fact
WHERE id = '12561'
ORDER BY ts;

|ts                 |views|
|-------------------|-----|
|2015-12-29 11:40:23| 4161|
|2015-12-29 12:00:23|  407|
|2015-12-29 12:20:23|    0|
|2015-12-29 12:40:23|  778|
|2015-12-29 13:00:23|  396|
...

시간 세분성 변경하기

DATE_TRUNC(): 시계열 데이터의 세분성을 변경함
- 예: 세기, 10년, 분기, 마이크로초 등

DATE_TRUNC(field, source, time zone)
- field = 세분성 값(예: "hour")
- source = 대상 데이터
- time zone = 기본값은 현재 설정

시간 세분성 변경하기

SELECT
    DATE_TRUNC('hour', ts) AS hour,

    SUM(VIEWS) AS views

FROM dc_news_fact
WHERE id = '12561'
GROUP BY hour
ORDER BY hour;

|hour               |views|
|-------------------|-----|
|2015-12-29 09:00:00|    0|
|2015-12-29 10:00:00|    0|
|2015-12-29 11:00:00| 4161|
|2015-12-29 12:00:00| 1185|
|2015-12-29 13:00:00| 1146|
|2015-12-29 14:00:00|  697|
|2015-12-29 15:00:00| 1013|
|2015-12-29 16:00:00|  956|
|2015-12-29 17:00:00| 1307|
|2015-12-29 18:00:00|  700|
...

시간 세분성 추출하기

DATE_PART(): 특정 데이터를 추출함

DATE_PART(field, source)
- field = 세분성 값(예: "hour")
- source = 대상 데이터: 타임스탬프 또는 구간

EXTRACT()도 특정 데이터를 추출함
- DATE_PART() 대신 사용 권장
- DATE_PART()는 정밀도가 낮은 배정밀도 값을 반환함

시간 단위 세분성

SELECT
    DATE_PART('hour', ts)
    AS hour_of_day,
    SUM(VIEWS) AS views

FROM dc_news_fact
WHERE id = '12561'
GROUP BY hour_of_day
ORDER BY hour_of_day;

|hour_of_day|views|
|-----------|-----|
|          0|  500|
|          1|  500|
|          2|  400|
|          3|  400|
|          4| 1200|
|          5|  100|
|          6|  100|
|          7| 2000|
|          8|  200|
|          9| 2500|
...

요일 단위 세분성

SELECT
    EXTRACT(dow FROM ts)
    AS day_of_week,
    SUM(VIEWS) AS views
FROM dc_news_fact
WHERE id = '12561'
GROUP BY day_of_week
ORDER BY day_of_week;

요일: 일요일(0)부터 토요일(6)까지

|day_of_week|views|
|-----------|-----|
|          2|15265|
|          3|13100|
|          4| 1200|

연습해 봅시다!

PostgreSQL을 활용한 시계열 분석