누적 평균

PostgreSQL을 활용한 시계열 분석

Jasmin Ludolf

Content Developer, DataCamp

누적 평균이란?

누적 평균:
- 일련의 값에 대한 평균

슬라이딩 윈도우

|values|running_average|
|------|---------------|
|     1|              1|

|     5|              3|

|     7|           4.33|

누적 평균 계산하기

SELECT 
    id,
    ts,
    views,
    AVG(views) OVER (ORDER BY ts)
      AS running_avg_views

FROM dc_news_fact
WHERE id = '12121'
ORDER BY ts;

|   id|                 ts|views|running_avg_views|
|-----|-------------------|-----|-----------------|
|12121|2015-12-27 17:14:16| null|             null|
|12121|2015-12-27 17:34:16|   23|23.00000000000000|
|12121|2015-12-27 17:54:16|   10|16.50000000000000|
|12121|2015-12-27 18:14:16|    8|13.66666666666666|
|12121|2015-12-27 18:34:16|    4|11.25000000000000|
|12121|2015-12-27 18:54:16|    7|10.40000000000000|
|12121|2015-12-27 19:14:16|    0|8.666666666666666|
...

여러 시계열에 대한 누적 평균

SELECT 
    id,
    ts,
    views,
    AVG(views) OVER (ORDER BY ts)
      AS running_avg_views
FROM dc_news_fact
WHERE id = '12121'
ORDER BY ts;

SELECT 
    id,
    ts,
    views,
    AVG(views) OVER(
      PARTITION BY id ORDER BY ts)
      AS running_avg_views
FROM dc_news_fact
ORDER BY id, ts;

여러 시계열에 대한 누적 평균: 결과

|   id|                 ts|views|  running_avg_views|
|-----|-------------------|-----|-------------------|
|12121|2015-12-27 17:14:16|     |                   |
|12121|2015-12-27 17:34:16|   23|23.0000000000000000|
...             ...           ...        ...
|12211|2015-12-28 03:17:18|     |                   |
|12211|2015-12-28 03:37:18|    4| 4.0000000000000000|
|12211|2015-12-28 03:57:18|    2| 3.0000000000000000|
|12211|2015-12-28 04:17:18|    2| 2.6666666666666667|
...

연습해 봅시다!

PostgreSQL을 활용한 시계열 분석