Compute Moving Average in PostgreSQL

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.

Understanding Moving Averages

We will go deeper into explaining moving averages but before that let's define what they are. Calculating a moving average is a statistical technique that allows an analyst to look at data in subsets and at each point create an average of these subsets. It performs this role specifically in reducing short-term fluctuations and drawing attention to the long-term trends and cycles within the series. This allows not only for the periodic data of the prices but also, creates a constantly updated average price figure.

There are two types of moving averages, simple moving averages (SMA), and exponential moving averages (EMA).

Simple Moving Average (SMA)

The simple moving average (SMA) computes the arithmetic mean of a given price set over any particular number of days in the past. It is referred to as "simple" due to the fact it is nothing but the arithmetic mean of all observations.

Here's how SMA is calculated:

• Find out the number of data points you would like to include in the calculation. Let’s consider that if you had to compute a 10-day SMA, you would use the past 10 data points.
  
• Sum up the digits of all data points of the chosen time period.
  
• Divide the sum by the number of data points in the period.

The formula for SMA is:

​Where:

• P_i = Price of the i-th data point
• n = Number of data points in the chosen period

Example:

Suppose we have a series of daily closing prices of a stock for a week (7 days):

Let's calculate the 3-day SMA for the given data.

1. Choose a Period: We'll choose a 3-day period.
2. Calculate SMA for Each Day:
For the 1st day: SM A1 = (100 + 105 + 110) / 3 = 105
For the 2nd day SM A2 = (105 + 110 + 115) / 3 = 110
For the 3rd day SM A3 = (110 + 115 + 120) / 3 = 115
For the 4th day: SM A4 = (115 + 120 + 125) / 3 = 120
For the 5th day: SM A5  = (120 + 125 + 130) / 3 = 125
So, the 3-day SMA series would be: 
[105,110,115,120,125]

Exponential Moving Average (EMA)

Exponential Moving Average (EMA) is a more complicated method of moving average calculation that assigns more weight to recent prices in an attempt to make them more responsive to new information. To calculate an Exponential Moving Average (EMA), the Simple Moving Average (SMA) over the suggested time duration is calculated first. However, different from the ordinary SMA, EMA gives the newer data points a higher weight and other older data points a lower weight.

Here's how EMA is calculated:

• Determine a smoothing factor.
  
• α, which determines the rate at which the weights decrease exponentially. The value of α typically ranges between 0 and 1.
  
• Start with the SMA for the first data point in the series. Then, for each subsequent data point, calculate the EMA using the formula:

Where:

• EM A_i = Exponential Moving Average for the i-th data point
• P_i = Price of the i-th data point
• α = Smoothing factor

Example:

Let's calculate the 3-day EMA for the given data, assuming a smoothing factor α=0.5.

Calculate the SMA for the First Day (Initial EMA):
Since we're starting with the first day's data point, the SMA and EMA would be the same: 
EM A1 = SM A1 = 105
Calculate EMA for Each Subsequent Day:
For the 2nd day:
EM A2 =(105×0.5)+(105×(1−0.5))=105
For the 3rd day:
EM A3 =(110×0.5)+(105×(1−0.5))=107.5
For the 4th day:
EM A4 =(115×0.5)+(107.5×(1−0.5))=111.25
For the 5th day:
EM A5 =(120×0.5)+(111.25×(1−0.5))=115.625
So, the 3-day EMA series would be: [105, 105, 107.5, 111.25, 115.625]

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.

Conclusion

Thus, by the end, PostgreSQL proves that it can handle computing moving averages in an effective way. By leveraging window functions, we can easily calculate various types of moving averages to gain insights from our data. To be able to manage time-related data which includes the analysis of stock prices, sales data, and other indicators, the knowledge of how to compute moving averages in PostgreSQL can give you all the edge that you need.