Media mobile

Analisi delle serie temporali in PostgreSQL

Jasmin Ludolf

Content Developer, DataCamp

Cos'è una media mobile?

finestra mobile

Medie mobili

Specifica quali righe mediare
Usa la clausola ROWS BETWEEN
UNBOUNDED PRECENDING AND CURRENT ROW: considera tutte le righe precedenti alla corrente

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;

Calcola una media mobile

Calcola la media mobile su cinque righe:
- ROWS BETWEEN 4 PRECEDING AND CURRENT ROW

(1) Riga precedente

(2) Riga precedente

(3) Riga precedente

(4) Riga precedente

(5) Riga corrente

Calcola una media mobile

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;

Calcola una media mobile: risultato

|   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|
...

Calcola una media mobile senza la riga corrente

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;

Vamos praticar!

Analisi delle serie temporali in PostgreSQL