移动平均

PostgreSQL 中的时间序列分析

Jasmin Ludolf

Content Developer, DataCamp

什么是移动平均？

滑动窗口

移动平均

指定要取平均的行
使用 ROWS BETWEEN 子句
UNBOUNDED PRECENDING AND CURRENT ROW：从当前行起向前查看所有行

SELECT 
id,
views,
AVG(views) OVER(
    PARTITION BY id
    ORDER BY ts
    ROWS BETWEEN
    UNBOUNDED PRECEDING AND CURRENT ROW)
    AS running_avg_views
FROM dc_news_fact;

计算移动平均

计算5行的移动平均：
- ROWS BETWEEN 4 PRECEDING AND CURRENT ROW

(1) 前一行

(2) 前一行

(3) 前一行

(4) 前一行

(5) 当前行

计算移动平均

SELECT
    id,
    ts,
    views,

    AVG(views) OVER (

      ORDER BY ts

      ROWS BETWEEN 4 PRECEDING AND CURRENT ROW) AS moving_average_five

FROM dc_news_fact;

计算移动平均：结果

|   id|                 ts|views|moving_average_five|
|-----|-------------------|-----|-------------------|
|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| 5.8000000000000000|
...

不含当前行的移动平均

ROWS BETWEEN 5 PRECEDING AND 1 PRECEDING

SELECT
    stock_symbol,
    day,
    daily_avg,
    AVG(daily_avg) OVER(
        PARTITION BY stock_symbol
        ORDER BY day
        ROWS BETWEEN 5 PRECEDING AND 1 PRECEDING
        ) AS avg_prev_five
FROM daily_stock_averages
ORDER BY stock_symbol, day;

让我们练习！

PostgreSQL 中的时间序列分析