Compute a running average with a window frame

By Batu · English technical notes

To compute a two-row running average in SQLite, write AVG(value) OVER (ORDER BY position ROWS 1 PRECEDING). With values 2, 4, and 8, the result is 2, 3, and 6.

The important part is the explicit ROWS 1 PRECEDING frame. The naive query omits it and therefore uses SQLite’s default aggregate-window frame: from the beginning of the partition through the current row and its peers. For this unique-position fixture that produces the cumulative averages 2, 3, and 14/3, not a trailing two-row result. It can accidentally appear correct at the first two positions, so the third value is the useful contrasting boundary input.

The corrected statement makes row counting part of the SQL rather than leaving frame semantics implicit. The assertion compares numeric query results, while the print function formats whole-number averages as 2, 3, and 6 for a concise transcript. SQLite documents the default frame and explains that a ROWS frame counts individual rows in its window-functions documentation. Python’s sqlite3 documentation covers the standard-library interface used for the isolated database. No newer Python-specific API is used (Python 3.5+); the SQLite runtime must be 3.25.0+ for window functions.

AI-assistance disclosure: this synthetic example was drafted with AI assistance and is intended to be run and adapted by the reader.

import sqlite3

con = sqlite3.connect(":memory:")
con.execute("CREATE TABLE samples(position INTEGER PRIMARY KEY, value INTEGER)")
seed = [(1, 2), (2, 4), (3, 8)]
con.executemany("INSERT INTO samples VALUES (?, ?)", seed)

print("schema:", con.execute(
    "SELECT sql FROM sqlite_master WHERE name = 'samples'"
).fetchone()[0])
print("seed:", seed)
naive = list(con.execute("""
    SELECT position, AVG(value) OVER (ORDER BY position) AS average
    FROM samples ORDER BY position
"""))
print("naive default frame:", naive)
corrected = list(con.execute("""
    SELECT position, AVG(value) OVER (
        ORDER BY position ROWS 1 PRECEDING
    ) AS average
    FROM samples ORDER BY position
"""))
print("corrected two-row frame:", [(p, int(a)) for p, a in corrected])

assert corrected == [(1, 2.0), (2, 3.0), (3, 6.0)]
con.close()

schema: CREATE TABLE samples(position INTEGER PRIMARY KEY, value INTEGER)
seed: [(1, 2), (2, 4), (3, 8)]
naive default frame: [(1, 2.0), (2, 3.0), (3, 4.666666666666667)]
corrected two-row frame: [(1, 2), (2, 3), (3, 6)]

Sources

SQLite SELECT