Calculating Moving Averages Directly in SQL

1. What a moving average actually does in SQL

Raw data in time series, such as daily revenue, server load or sensor readings, fluctuates heavily from one period to the next. A moving average smooths out this noise by calculating, for every point in time, the mean over a fixed window of preceding or surrounding values. Instead of a single volatile daily value, the moving average reveals the underlying trend, tones down outliers and makes seasonal patterns actually visible.

Traditionally, a moving average was calculated in the application layer: load raw data from the database, iterate over it in Python, PHP or Java, sum values over a window, write the result back. That is unnecessary effort when the database itself supports window functions. A moving average calculated in SQL stays closer to the data, avoids network overhead for large volumes of raw rows, and can be embedded directly into reporting views or materialized views.

This article shows how AVG() OVER() combined with a precise ROWS BETWEEN clause produces any moving average you need, which pitfalls show up at the edges of a time series, and how performance behaves on large data sets.

2. AVG() OVER(): the window function as a foundation

The window function AVG() OVER() is fundamentally different from the aggregating AVG() function combined with GROUP BY. While GROUP BY collapses rows into a single result row, a window function keeps every original row intact. AVG() OVER() calculates, for every row, an average over a related set of rows, the so-called window, without reducing the number of rows in the result. That is exactly the property that makes window functions the right tool for a moving average: every day keeps its own row, but gains the smoothed value as an additional column.

The syntax has three parts: PARTITION BY groups the calculation, for example per store or product category, ORDER BY defines the sequence in which the window is traversed, and the frame clause defines which rows relative to the current row feed into the calculation. Without an explicit frame clause the database picks a default frame that is almost never what you want for a moving average, which is why the frame clause is essential for a correct result.

3. ROWS BETWEEN: defining the window frame precisely

ROWS BETWEEN defines a physical row frame. ROWS BETWEEN 6 PRECEDING AND CURRENT ROW includes exactly the six preceding rows plus the current row in the moving average, forming a seven-day window. The PRECEDING keyword counts rows relative to the current position in the sequence established by ORDER BY, regardless of the actual value the sort column carries. That is what distinguishes ROWS from RANGE, which instead considers value ranges of the sort column. For a classic N-period moving average, ROWS is almost always the right choice.

It matters that the frame clause matches the ORDER BY column exactly. With daily data and no gaps, ROWS BETWEEN 6 PRECEDING AND CURRENT ROW produces a clean seven-day window. But if individual days are missing from the raw data, for example because no sales are booked on weekends, ROWS still refers to the number of existing rows, not calendar days. The window can then span more than seven calendar days. Anyone who needs true calendar windows for a moving average has to pad the time series with a calendar table first so there are no gaps.

4. The N-period pattern for a rolling average

The standard pattern for an N-period moving average combines AVG() with OVER (PARTITION BY ... ORDER BY ... ROWS BETWEEN N-1 PRECEDING AND CURRENT ROW). For a seven-day moving average over daily revenue per store, the query looks like this:

-- 7-day moving average of daily revenue per store
SELECT
    store_id,
    sale_date,
    daily_revenue,
    AVG(daily_revenue) OVER (
        PARTITION BY store_id
        ORDER BY sale_date
        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    ) AS moving_avg_7d
FROM daily_sales
ORDER BY store_id, sale_date;

-- Sample result
-- store_id | sale_date  | daily_revenue | moving_avg_7d
-- 1        | 2026-07-01 | 1200.00       | 1200.00
-- 1        | 2026-07-02 | 980.00        | 1090.00
-- 1        | 2026-07-03 | 1450.00       | 1210.00
-- 1        | 2026-07-04 | 1100.00       | 1182.50
-- 1        | 2026-07-05 | 1600.00       | 1266.00
-- 1        | 2026-07-06 | 1350.00       | 1280.00
-- 1        | 2026-07-07 | 1250.00       | 1275.71
-- 1        | 2026-07-08 | 1400.00       | 1304.29

The result contains, for every store and every day, both the raw daily revenue and the smoothed seven-day moving average in the same row. For the first six days of each store, the window is smaller than seven rows, because PRECEDING cannot reach past the start of the partition. SQL automatically returns an average over the rows actually available, no error and no NULL, which makes the moving average robust even at the edge of a time series.

5. Centered vs. trailing moving average

A trailing moving average like the one above reacts only with a delay to trend changes, because it only includes past values. That is intentional when a report is meant to work only with data up to the current day. For a retrospective analysis of historical data, where future values within the time series already exist, a centered moving average gives a cleaner picture, because it smooths symmetrically around the current row.

A centered moving average replaces ROWS BETWEEN 6 PRECEDING AND CURRENT ROW with ROWS BETWEEN 3 PRECEDING AND 3 FOLLOWING, which also produces a seven-row window, but distributed symmetrically around the current row. The effect: peaks and dips get smoothed at their actual position in the time series instead of only after a delay, which makes centered moving averages particularly attractive for visual trend analysis in dashboards.

-- Centered 7-day moving average (3 rows before and after)
SELECT
    store_id,
    sale_date,
    daily_revenue,
    AVG(daily_revenue) OVER (
        PARTITION BY store_id
        ORDER BY sale_date
        ROWS BETWEEN 3 PRECEDING AND 3 FOLLOWING
    ) AS centered_avg_7d
FROM daily_sales
ORDER BY store_id, sale_date;

-- Note: the last three rows per store have a shrinking window
-- because FOLLOWING cannot reach past the end of the partition

6. Edge cases: start of the time series and NULL handling

At both ends of a time series, the frame clause automatically produces a smaller window instead of returning NULL or an error. ROWS BETWEEN 6 PRECEDING AND CURRENT ROW calculates the moving average for the very first day of a partition simply over the one available row. That is mathematically correct, but can be misleading if a dashboard shows the first six days of a brand new store just as smooth as an established store with a full window.

Anyone who wants to flag these distorted edge values in a report counts the actual window size alongside with COUNT(*) OVER the same frame clause, and either hides rows with a window size below the desired period length or marks them separately. That way the moving average stays technically calculated for every row, while the report only highlights the fully supported values.

-- Flag rows where the moving average window is not yet full
SELECT
    store_id,
    sale_date,
    daily_revenue,
    AVG(daily_revenue) OVER w AS moving_avg_7d,
    COUNT(*) OVER w AS window_size,
    CASE WHEN COUNT(*) OVER w < 7 THEN TRUE ELSE FALSE END AS is_partial_window
FROM daily_sales
WINDOW w AS (
    PARTITION BY store_id
    ORDER BY sale_date
    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
)
ORDER BY store_id, sale_date;
-- Note: WINDOW clause (PostgreSQL, MySQL 8) avoids repeating
-- the same frame definition three times in one query

7. Performance: sorting, indexing and execution plans

A moving average built on a window function internally requires the partition to be sorted by the ORDER BY column before the frame calculation can start. If an index already exists on (store_id, sale_date), the database can read that sort order directly from the index instead of performing it at runtime in memory or on disk. The difference shows up in the execution plan as an index scan instead of a sort step before the window aggregation.

For very large time series with millions of rows per partition, it is worth checking EXPLAIN (ANALYZE) for the database in question. An extra sort step ahead of the window function consumes memory proportional to the partition size and, under tight work_mem in PostgreSQL or sort_buffer_size in MySQL, can spill to disk-based temp files, which slows the moving average down considerably. A composite index that covers the partition column and the sort column in that order is the single most effective measure for a performant calculation.

8. Use case: trend smoothing in a reporting dashboard

In reporting dashboards, the moving average is one of the most used elements, because it condenses daily fluctuations into an understandable trend line. A classic example: a revenue dashboard shows, alongside the raw daily revenue, both a seven-day and a 28-day moving average as two overlaid lines. The short line reacts to weekly trends, the long one to seasonal shifts, and the gap between the two lines shows whether the trend is accelerating or slowing down.

Because both moving averages can be calculated in the same query from the same window function with a different frame size, no extra join and no second query are needed. The database and the reporting tool see a single, wide result table with several moving-average columns side by side. That reduces complexity in the application layer to a minimum: the frontend only has to draw the matching columns into a line chart, without aggregating anything itself.

-- Two moving averages (7-day and 28-day) in a single query
SELECT
    store_id,
    sale_date,
    daily_revenue,
    AVG(daily_revenue) OVER (
        PARTITION BY store_id ORDER BY sale_date
        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    ) AS moving_avg_7d,
    AVG(daily_revenue) OVER (
        PARTITION BY store_id ORDER BY sale_date
        ROWS BETWEEN 27 PRECEDING AND CURRENT ROW
    ) AS moving_avg_28d
FROM daily_sales
ORDER BY store_id, sale_date;

9. Moving average compared: window function vs. self-join

Before window functions became widespread in mainstream databases, a moving average was often calculated with a self-join or a correlated subquery: for every row, the database aggregated over the last N rows again through a join or a subquery. Functionally the result is identical, but execution differs considerably. A self-join with a condition such as sale_date BETWEEN current_day - 6 AND current_day produces a separate scan over up to N matching rows for every row, which leads to quadratic instead of linear runtime on large tables.

A window function, on the other hand, sorts the partition once and then slides a window incrementally across the sorted rows, and many databases reuse the sum of the previous window and only adjust the row entering and the row leaving. That makes a moving average built with AVG() OVER() not only shorter in code, but also, in nearly every case, considerably faster than the equivalent self-join or subquery variant.

-- Old approach: correlated subquery, avoid for large tables
SELECT
    s1.store_id,
    s1.sale_date,
    s1.daily_revenue,
    (
        SELECT AVG(s2.daily_revenue)
        FROM daily_sales s2
        WHERE s2.store_id = s1.store_id
          AND s2.sale_date BETWEEN s1.sale_date - INTERVAL '6 day' AND s1.sale_date
    ) AS moving_avg_7d
FROM daily_sales s1
ORDER BY s1.store_id, s1.sale_date;
-- Runs one subquery per output row, quadratic cost on large tables

In practice, AVG() OVER() with ROWS BETWEEN outperforms every other approach for a moving average, both in readability and in performance. Self-joins and correlated subqueries only remain relevant where a legacy database without window function support is in use, which practically never happens on modern PostgreSQL, MySQL, SQL Server or Oracle versions.

10. Summary

Calculating a moving average directly in SQL comes down to one fixed pattern: AVG() as a window function with PARTITION BY, ORDER BY and an explicit ROWS BETWEEN clause. A trailing window with N-1 PRECEDING AND CURRENT ROW fits live dashboards, a centered window with PRECEDING AND FOLLOWING fits retrospective analysis. At the edges of the time series, the database automatically returns a smaller window, which can be made visible with COUNT(*) OVER.

On the performance side, a composite index on the partition and sort columns pays off, so the database can read the sort order the moving average needs directly from the index. Compared to a self-join or a correlated subquery, the window function variant is not just shorter, it is also structurally faster on large time series, because it scales linearly instead of quadratically.

11. FAQ: Moving Averages in SQL