Computing Moving Averages in PostgreSQL

In computing moving average in PostgreSQL, window functions of the PostgreSQL database will be largely used. Window functions enable us to conduct operations on rows associated with the current row, and the related records.

Example 1: Compute the moving average of the prices column over a window of 3 days.

Step 1: Create Sample Data

Let’s create a sample table named stock_prices and insert some data into it.

CREATE TABLE stock_prices (
    date DATE,
    price NUMERIC
);

INSERT INTO stock_prices (date, price) VALUES
    ('2024-04-01', 100),
    ('2024-04-02', 110),
    ('2024-04-03', 105),
    ('2024-04-04', 120),
    ('2024-04-05', 125),
    ('2024-04-06', 130),
    ('2024-04-07', 128);

Stock Prices Table

Step 2: Calculate Moving Average

Now, let’s compute the moving average of the price column over a window of, say, 3 days.

SELECT
    date,
    price,
    AVG(price) OVER (
        ORDER BY date
        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    ) AS moving_average
FROM
    stock_prices;

In this SQL query:

• ORDER BY date ensures that the rows are ordered by the date column.
• ROWS BETWEEN 2 PRECEDING AND CURRENT ROW defines the window size for the moving average calculation. In this case, it considers the current row and the two preceding rows.

Step 3: Results

You can see a result set containing the original date, price, and the corresponding moving average.

Example 2: Calculate the moving average of daily temperatures recorded in a weather database.

Step 1: Create Sample Data

Let’s create a sample table named daily_temperatures and insert some data into it.

CREATE TABLE daily_temperatures (
    date DATE,
    temperature NUMERIC
);

INSERT INTO daily_temperatures (date, temperature) VALUES
    ('2024-04-01', 20),
    ('2024-04-02', 22),
    ('2024-04-03', 24),
    ('2024-04-04', 23),
    ('2024-04-05', 25),
    ('2024-04-06', 27),
    ('2024-04-07', 26);

Temperature Table

Step 2: Calculate Moving Average

Now, let’s compute the moving average of the temperature column over a window of 3 days.

SELECT
    date,
    temperature,
    AVG(temperature) OVER (
        ORDER BY date
        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    ) AS moving_average
FROM
    daily_temperatures;

In this SQL query, the process is similar to the previous example. We’re ordering the rows by the date column and specifying a window of 3 days for the moving average calculation.

Step 3: Results

You can see below the original date, temperature, and the corresponding moving average for each day.

PostgreSQL is an advanced relational database system that supports both relational (SQL) and non-relational (JSON) queries. It is free and open-source.

The moving average helps to level the price data over a specified period by creating a constantly updated average price. PostgreSQL which, is an impressive open-source relational database management system, gives moving average computation some powerful features. In this article, we will talk about how you can create moving averages with PostgreSQL.