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.

## 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 aWHERE;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 BETWEEN29 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 BETWEEN89 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_monthROWS 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 aWHERE;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.

If you want to create charts, dashboards & reports from PostgreSQL database, you can try Ubiq. We offer a 14-day free trial.