Skip to content

SQL - PostgreSQL - Moving Average

Source: sql-snippets/moving-average.md at main · count/sql-snippets (github.com)

View an interactive version of this snippet here.

Description

Moving averages are quite simple to calculate in PostgreSQL, using the AVG window function. Here is an example query with a 7-day moving average over a total of 31 days, calculated for 1 dimension (color):

WITH data AS (
    SELECT *,
           CASE WHEN num <= 0.5 THEN 'blue' ELSE 'red' END AS str
    FROM (SELECT generate_series('2021-01-01'::TIMESTAMP, '2021-01-31'::TIMESTAMP, '1 day') AS timeseries,
                 RANDOM() AS num
         ) t
)

SELECT   timeseries,
         str,
         num,
         AVG(num) OVER (PARTITION BY str ORDER BY timeseries ROWS BETWEEN 7 PRECEDING AND CURRENT ROW) AS mov_avg
FROM     data
ORDER BY timeseries, str;

Output:

timeseries str num mov_avg
2021-01-01 00:00:00.000000 red 0.6724503329790004 0.6724503329790004
2021-01-02 00:00:00.000000 blue 0.02015057538457654 0.02015057538457654
2021-01-03 00:00:00.000000 blue 0.19436280964817954 0.10725669251637804
2021-01-04 00:00:00.000000 blue 0.3170075569498252 0.17717364732752708
2021-01-05 00:00:00.000000 blue 0.45274767058829823 0.24606715314271987
2021-01-06 00:00:00.000000 red 0.5295068450998173 0.6009785890394088
2021-01-07 00:00:00.000000 red 0.5856947203861544 0.5958839661549907
2021-01-08 00:00:00.000000 blue 0.23106543649677036 0.24306680981352996
2021-01-09 00:00:00.000000 red 0.8057297084321 0.648345401724268
2021-01-10 00:00:00.000000 blue 0.18782650473713147 0.23386009230079688
2021-01-11 00:00:00.000000 blue 0.012715414005509018 0.20226799540147006
2021-01-12 00:00:00.000000 blue 0.41378600069740656 0.22870774606346211
2021-01-13 00:00:00.000000 red 0.7848015045167251 0.6756366222827594
2021-01-14 00:00:00.000000 blue 0.3062660702635327 0.26447218292333163
2021-01-15 00:00:00.000000 red 0.7321478245155468 0.685055155988224
2021-01-16 00:00:00.000000 blue 0.20009039295143083 0.26518813083623805
2021-01-17 00:00:00.000000 red 0.9851023962729109 0.7279190474574649
2021-01-18 00:00:00.000000 blue 0.42790603927394955 0.2790504411267536
2021-01-19 00:00:00.000000 red 0.7966662316864124 0.7365124454860834
2021-01-20 00:00:00.000000 red 0.8913488809896606 0.7638747639874159
2021-01-21 00:00:00.000000 red 0.6797595409599531 0.7826563509699329
2021-01-22 00:00:00.000000 red 0.9692422752366809 0.8305997953262487
2021-01-23 00:00:00.000000 blue 0.3873783130847208 0.2708792714388064
2021-01-24 00:00:00.000000 red 0.960779246012315 0.8499809875237756
2021-01-25 00:00:00.000000 red 0.6482284656189883 0.8329093576615585
2021-01-26 00:00:00.000000 red 0.8471274360131815 0.8472818090987628
2021-01-27 00:00:00.000000 red 0.5269376445697667 0.7900112151358698
2021-01-28 00:00:00.000000 blue 0.07696316291711724 0.2516164872413498
2021-01-29 00:00:00.000000 blue 0.1035287644690932 0.241079269707845
2021-01-30 00:00:00.000000 red 0.7339086567504438 0.7821665182688737
2021-01-31 00:00:00.000000 red 0.8169741358988709 0.772869675132525

Backlinks:

list from [[SQL - PostgreSQL - Moving Average]] AND -"Changelog"