# How to Calculate Moving Average in PostgreSQL

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 a
WHERE condition;```

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

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