DuckDB Calculate Moving Average

What is a Moving Average?

The moving average (MA) is a statistical tool used to analyze data points by creating a series of averages of different subsets of the full data set. It's commonly used in time series analysis, particularly in finance and economics, to smooth out short-term fluctuations and highlight longer-term trends or cycles.

There are different types of moving averages, but the two most common ones are:

Simple Moving Average (SMA) - Calculated by taking the arithmetic mean of a given number of data points.
Exponential Moving Average (EMA) - Gives more weight to recent data points, making it more responsive to recent price changes.

For example, a 3-day SMA in stock prices would take the average of the stock prices for the last 3 days, then move forward one day and recalculate the average for the next 3 days, and so on. This moving window is highlighted in green in the example below.

Moving Average SQL Example

The leftmost table contains the original close price by date for the stock XOM (Exxon Mobil Corp) with added columns showing moving average and the values used to calculate that average:

CREATE TABLE stocks AS SELECT * FROM read_parquet('https://www.timestored.com/data/sample/stocks/hive/pre=X/stocks_0.parquet');
SELECT date,close, 
        avg(close) OVER(ORDER BY date ASC ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) as three_day_moving_average,
        list(close) OVER(ORDER BY date ASC ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) as three_day_list
        FROM (SELECT * FROM stocks WHERE SYM='XOM' AND date BETWEEN '2024-01-01' AND '2024-01-11') ORDER BY date DESC;

You can see how the moving average smooths a graph in this 7-day and 30day moving average trend indicators:

SELECT date,close, 
        avg(close) OVER(ORDER BY date ASC ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) as seven_row_moving_average,
        avg(close) OVER(ORDER BY date ASC ROWS BETWEEN 29 PRECEDING AND CURRENT ROW) as thirty_row_moving_average,
        FROM (SELECT * FROM stocks WHERE SYM='XOM' AND date BETWEEN '2024-01-01' AND '2024-06-11') ORDER BY date DESC;

Window functions and types

Window functions can use multiple rows to calculate a value for each row. DuckDB Window functions are blocking operators, i.e., they require their entire input to be buffered, making them one of the most memory-intensive operators in SQL.

Particular interesting window functions for stocks and finance include:

Name	Description
`first_value(expr[ IGNORE NULLS])`	Returns `expr` evaluated at the row that is the first row (with a non-null value of `expr` if `IGNORE NULLS` is set) of the window frame.
`last_value(expr[ IGNORE NULLS])`	Returns `expr` evaluated at the row that is the last row (among rows with a non-null value of `expr` if `IGNORE NULLS` is set) of the window frame.
`rank()`	The rank of the current row with gaps; same as `row_number` of its first peer.

Rows or DAYS

Notice in the table above how between 2024-01-05 and 2024-01-08 there is a gap for the weekend. This is because the example above used ROWS as the window function. You may want to filter on DAYS rather than rows. To do this we would use the below SQL.
Notice how I'm specifying and naming the window threew, this allows reusing the window name rather than repeating code.

SELECT date,close, 
        avg(close) OVER threew as three_day_average,
        list(close) OVER threew as three_day_list,
        list(close) OVER(ORDER BY date ASC ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) as three_row_list,
    FROM stocks
        WHERE SYM='XOM' AND date BETWEEN '2024-01-01' AND '2024-01-11'
        WINDOW threew AS (ORDER BY date ASC RANGE BETWEEN INTERVAL 2 DAYS  PRECEDING AND INTERVAL 0 DAYS FOLLOWING)
        ORDER BY date DESC;

If you get an error saying "cannot copy window..." see this page.

DuckDB Calculate Moving Average

What is a Moving Average?

Moving Average SQL Example

Window functions and types

Rows or DAYS

See Also