Window Functions

The Idea

A window function computes over a set of rows related to the current one, then attaches the answer to that row. OVER (ORDER BY …) gives you running totals and rankings; PARTITION BY restarts the calculation per group. Nothing is folded away, so the detail stays.

Real-World Example

A bakery's day book. Each line still shows one day's loaves, and beside it someone has pencilled the week-to-date total and a note that Thursday was the busiest day — without erasing a single row.

The Code

import sqlite3
db = sqlite3.connect(":memory:")
db.executescript("""CREATE TABLE bake (day TEXT, loaves INT);
  INSERT INTO bake VALUES ('03-04',40),('03-05',55),('03-06',35),('03-07',70);""")
for row in db.execute("""
  SELECT day, loaves,
         SUM(loaves) OVER (ORDER BY day) AS so_far,
         RANK()      OVER (ORDER BY loaves DESC) AS busiest
  FROM bake ORDER BY day"""):
    print(row)
# ('03-04', 40, 40, 3)
# ('03-05', 55, 95, 2)
# ('03-06', 35, 130, 4)
# ('03-07', 70, 200, 1)

import sqlite3
db = sqlite3.connect(":memory:")
db.executescript("""CREATE TABLE t (n INT);
INSERT INTO t VALUES (5),(5),(2);""")
print(db.execute(
"SELECT n, RANK() OVER (ORDER BY n DESC) FROM t ORDER BY n DESC").fetchall())