How to Calculate Moving Average in PostgreSQL

Last updated on May 17th, 2020 at 06:12 am

Moving Average or Rolling Average allows you to calculate average over a moving period of time, like last 7 days. Calculating moving average over time gives smoother trend, compared to tracking daily numbers. Here’s the SQL query to calculate moving average in PostgreSQL.

Table of Contents

How to Calculate Moving Average in PostgreSQL

Here’s how to calculate moving average in PostgreSQL. Let’s say you have the following table

postgres=# create table sales(order_date date,sale int);

postgres=# insert into sales values('2020-04-01',210),
           ('2020-04-02',125),('2020-04-03',150),('2020-04-04',230),
           ('2020-04-05',200),('2020-04-10',220),('2020-04-06',25),
           ('2020-04-07',215),('2020-04-08',300),('2020-04-09',250);

postgres=# select * from sales;
 order_date | sale
------------+------
 2020-04-01 |  210
 2020-04-02 |  125
 2020-04-03 |  150
 2020-04-04 |  230
 2020-04-05 |  200
 2020-04-10 |  220
 2020-04-06 |   25
 2020-04-07 |  215
 2020-04-08 |  300
 2020-04-09 |  250

Bonus Read : How to Import CSV file in PostgreSQL

Let’s say you want to calculate moving average in PostgreSQL for last 5 days. PostgreSQL allows you to calculate rolling average with the help of Window Functions. Here’s the query to calculate moving average in PostgreSQL for past 5 days. We will look at it in detail

SELECT a.order_date,a.sale, 
       AVG(a.sale)
       OVER(ORDER BY a.order_date ROWS BETWEEN 4 PRECEDING AND CURRENT ROW)
       AS avg_sales
       FROM sales a ;
 order_date | sale |      avg_sales
------------+------+----------------------
 2020-04-01 |  210 | 210.00
 2020-04-02 |  125 | 167.50
 2020-04-03 |  150 | 161.66
 2020-04-04 |  230 | 178.75
 2020-04-05 |  200 | 183.00
 2020-04-06 |   25 | 146.00
 2020-04-07 |  215 | 164.00
 2020-04-08 |  300 | 194.00
 2020-04-09 |  250 | 198.00
 2020-04-10 |  220 | 202.00

In the above query, AVG function calculates average value of sale column. When we use it with Window function OVER, it calculates average only for the time window defined by us.

To calculate moving average in PostgreSQL, we first sort the rows chronologically using ORDER BY clause. Then we define our Window for calculating average, using ROWS BETWEEN 4 PRECEDING AND CURRENT ROW. It means that for each row calculate average for only the current row and preceding 4 rows. So for each row only the past 5 days’ values are considered.

You can also add filters and round average values by adding WHERE clause and ROUND function in the above SQL query.

 SELECT a.order_date,a.sale, 
       round(AVG(a.sale)
       OVER(ORDER BY a.order_date ROWS BETWEEN 4 PRECEDING AND CURRENT ROW),2) 
       AS avg_sales
       FROM sales a
       WHERE condition;

Bonus Read: Calculate Month over Month Growth in PostgreSQL

How to Calculate 30-day moving average in PostgreSQL

Similarly, if you want to calculate 30-day moving average in PostgreSQL, you can modify the above query, by considering 29 preceding rows and current row

SELECT a.order_date,a.sale, 
       AVG(a.sale)
       OVER(ORDER BY a.order_date ROWS BETWEEN 29 PRECEDING AND CURRENT ROW)
       AS avg_sales
       FROM sales a ;

How to Calculate 3-month moving average in PostgreSQL

If you have daily sales data, and want to calculate 3-month moving average in PostgreSQL, you can modify the above query, by considering 89 preceding rows and current row

SELECT a.order_date,a.sale, 
       AVG(a.sale)
       OVER(ORDER BY a.order_date ROWS BETWEEN 89 PRECEDING AND CURRENT ROW)
       AS avg_sales
       FROM sales a ;

Let’s say you have monthly data, instead of daily data, and want to calculate rolling average for past 3 months

postgres=# create table monthly_sales(order_month date,sale int);

postgres=# insert into monthly_sales values('2019-12-01',120),
           ('2020-01-30',250),('2020-02-28',150),('2020-03-31',300),
           ('2020-04-30',200),('2020-05-31',200),('2020-06-30',250),
           ('2020-07-31',150),('2020-08-31',300),('2020-09-30',200);

postgres=# select * from monthly_sales;
 order_month | sale
-------------+------
 2019-12-01  |  120
 2020-01-30  |  250
 2020-02-28  |  150
 2020-03-31  |  300
 2020-04-30  |  200
 2020-05-31  |  200
 2020-06-30  |  250
 2020-07-31  |  150
 2020-08-31  |  300
 2020-09-30  |  200

Bonus Read: How to Calculate Retention Rate in SQL

We use the same logic as above, to calculate rolling average in PostgreSQL. First, we sort rows chronologically, and then use OVER window function to calculate average for preceding 2 rows and current row.

SELECT a.order_month,a.sale,
        round(AVG(a.sale)
        OVER(ORDER BY a.order_month ROWS BETWEEN 2 PRECEDING AND CURRENT ROW),2) 
        AS avg_sales
        FROM monthly_sales a ;

 order_month | sale | avg_sales
-------------+------+-----------
 2019-12-01  |  120 |    120.00
 2020-01-30  |  250 |    185.00
 2020-02-28  |  150 |    173.33
 2020-03-31  |  300 |    233.33
 2020-04-30  |  200 |    216.67
 2020-05-31  |  200 |    233.33
 2020-06-30  |  250 |    216.67
 2020-07-31  |  150 |    200.00
 2020-08-31  |  300 |    233.33
 2020-09-30  |  200 |    216.67

Bonus Read: How to Create Pivot Table in PostgreSQL

You can also add filters by including WHERE clause in the above SQL query.

 SELECT a.order_month,a.sale,
        round(AVG(a.sale)
        OVER(ORDER BY a.order_month ROWS BETWEEN 2 PRECEDING AND CURRENT ROW),2) 
        AS avg_sales
        FROM monthly_sales a
        WHERE condition;

You can customize the above query to calculate moving average in PostgreSQL, as per your requirements.

After you calculate moving average in PostgreSQL, you can use a charting tool to plot it on a line chart and share it with your team. Here’s an example of a line chart that visualizes moving average, created using Ubiq.