How to Calculate a Moving Average in PostgreSQL (With Example)

A moving average is simply the average of a set of numbers during a specific time period.

Often in PostgreSQL you may want to calculate a moving average for a particular column in a table.

You can use the following basic syntax to do so:

SELECT *,
       AVG(sales_amount) OVER (
        ORDER BY sales_date
        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
       ) AS moving_avg
FROM sales;

This particular example will calculate the moving average of the current row and the 2 previous rows in the column named sales_amount of the table named sales.

To use a different number of periods for the moving average, simply change the 2 to a different number.

The following example shows how to use this syntax in practice.

Example: How to Calculate Moving Average in PostgreSQL

Suppose that we create the following table named sales that contains information about the total number of sales made by an employee at a company during eight consecutive days:

-- create table named 'sales'
CREATE TABLE sales (
  sales_date DATE,
  sales_amount INT
);

-- add rows to the table
INSERT INTO sales VALUES ('2024-01-01', 22);
INSERT INTO sales VALUES ('2024-01-02', 25);
INSERT INTO sales VALUES ('2024-01-03', 28);
INSERT INTO sales VALUES ('2024-01-04', 36);
INSERT INTO sales VALUES ('2024-01-05', 30);
INSERT INTO sales VALUES ('2024-01-06', 45);
INSERT INTO sales VALUES ('2024-01-07', 42);
INSERT INTO sales VALUES ('2024-01-08', 39);

-- view all rows in table
SELECT * FROM sales;

 sales_date | sales_amount 
------------+--------------
 2024-01-01 |           22
 2024-01-02 |           25
 2024-01-03 |           28
 2024-01-04 |           36
 2024-01-05 |           30
 2024-01-06 |           45
 2024-01-07 |           42
 2024-01-08 |           39

Suppose that we would like to calculate a 3-day moving average of values in the sales_amount column.

We can use the following syntax to do so:

SELECT *,
       AVG(sales_amount) OVER (
        ORDER BY sales_date
        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
       ) AS moving_avg
FROM sales;

 sales_date | sales_amount |     moving_avg      
------------+--------------+---------------------
 2024-01-01 |           22 | 22.0000000000000000
 2024-01-02 |           25 | 23.5000000000000000
 2024-01-03 |           28 | 25.0000000000000000
 2024-01-04 |           36 | 29.6666666666666667
 2024-01-05 |           30 | 31.3333333333333333
 2024-01-06 |           45 | 37.0000000000000000
 2024-01-07 |           42 | 39.0000000000000000
 2024-01-08 |           39 | 42.0000000000000000

The new column named moving_average contains the 3-day moving average of sales in the sales_amount column.

For example, we can see:

• The 3-day moving average of sales on 2024-01-03 was (22+25+28) / 3 = 25.
• The 3-day moving average of sales on 2024-01-04 was (25+28+36) / 3 = 29.667.
• The 3-day moving average of sales on 2024-01-05 was (28+36+30) / 3 = 31.333.

And so on.

Note: For rows that did not have at least 2 previous periods, the moving average was simply calculated using as many days that were available.

Additional Resources

The following tutorials explain how to perform other common tasks in PostgreSQL:

PostgreSQL: How to Select Rows in Date Range
PostgreSQL: How to Extract Date from a Timestamp
PostgreSQL: How to Extract Month from Date