Window Functions

What the previous lesson’s computations had in common was that they changed the row set: traversal produced new rows, grouping reduced rows to one per group. In some questions, though, the rows should be preserved, and a value that looks at its neighbors added next to each one.

“Each loan transaction, alongside that member’s total transaction count” is a question like this. Grouping cannot give this directly: once GROUP BY is applied, the detail rows vanish and what is left is one row per group. Preserving the detail means the grouped result has to be joined back with the detail. Window functions make this join unnecessary: they run an aggregate function over each row’s own field of view, without swallowing the rows.

The Same Function, Two Uses

When an OVER clause is added after an aggregate function, the function turns into a window function. If OVER is left empty, the window is the entire result set; if PARTITION BY is given, the result set is split into partitions and each row sees only its own partition.

sqlite3 -box -header <<'SQL'
CREATE TABLE loan(id INTEGER PRIMARY KEY, book_id INT, member_id INT, pickup TEXT, returned TEXT);
INSERT INTO loan VALUES
  (1,1,1,'2024-03-01','2024-03-15'),(2,3,1,'2024-03-01','2024-03-20'),
  (6,2,2,'2024-03-01','2024-03-12'),(3,5,1,'2024-03-04','2024-03-18'),
  (7,4,2,'2024-03-06','2024-03-25'),(4,7,1,'2024-03-11',NULL),
  (8,6,2,'2024-03-11','2024-03-19'),(23,2,7,'2024-03-11','2024-03-16'),
  (5,9,1,'2024-03-18','2024-03-29'),(24,5,7,'2024-03-18','2024-03-24'),
  (9,8,2,'2024-03-21',NULL);

SELECT member_id, COUNT(*) AS count FROM loan GROUP BY member_id ORDER BY member_id;
SELECT id, member_id, pickup, COUNT(*) OVER (PARTITION BY member_id) AS member_count
FROM loan ORDER BY member_id, pickup LIMIT 6;
SQL

┌───────────┬───────┐
│ member_id │ count │
├───────────┼───────┤
│ 1         │ 5     │
│ 2         │ 4     │
│ 7         │ 2     │
└───────────┴───────┘
┌────┬───────────┬────────────┬──────────────┐
│ id │ member_id │   pickup   │ member_count │
├────┼───────────┼────────────┼──────────────┤
│ 1  │ 1         │ 2024-03-01 │ 5            │
│ 2  │ 1         │ 2024-03-01 │ 5            │
│ 3  │ 1         │ 2024-03-04 │ 5            │
│ 4  │ 1         │ 2024-03-11 │ 5            │
│ 5  │ 1         │ 2024-03-18 │ 5            │
│ 6  │ 2         │ 2024-03-01 │ 4            │
└────┴───────────┴────────────┴──────────────┘

The first query reduced eleven rows to three. The second preserved all eleven rows and wrote each member’s total next to it — the 5, 4, and 2 that the first query produced now appear next to the rows that belong to them. PARTITION BY does the same partitioning as GROUP BY; the difference is whether the rows get swallowed as a result of that partitioning.

The order of evaluation explains this distinction. Window functions run after WHERE, GROUP BY, and HAVING have been applied. Two consequences follow: a window function’s input is the already-filtered set, and a window function’s result cannot be used in the same query’s WHERE clause. If filtering on a window result is needed, the query is wrapped in a common table expression and the filtering is done on the outside.

Frame Definition

When ORDER BY is added inside OVER, the window becomes ordered and the concept of a frame comes into play: what a row sees is no longer the whole partition but an ordered slice of it. A frame is defined in one of two ways:

• ROWS: bounds are measured by row count. Two rows back from the current row means physically two rows.
• RANGE: bounds are measured by value. Rows that share the same value in the sort key — peers — enter the frame together, or not at all.

If ORDER BY is given and no frame is written, the default frame is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. The difference shows up when the sort key has repeated values. The query below places three frames side by side on the same data:

sqlite3 -box -header <<'SQL'
CREATE TABLE loan(id INTEGER PRIMARY KEY, book_id INT, member_id INT, pickup TEXT, returned TEXT);
INSERT INTO loan VALUES
  (1,1,1,'2024-03-01','2024-03-15'),(2,3,1,'2024-03-01','2024-03-20'),
  (6,2,2,'2024-03-01','2024-03-12'),(3,5,1,'2024-03-04','2024-03-18'),
  (7,4,2,'2024-03-06','2024-03-25'),(4,7,1,'2024-03-11',NULL),
  (8,6,2,'2024-03-11','2024-03-19'),(23,2,7,'2024-03-11','2024-03-16'),
  (5,9,1,'2024-03-18','2024-03-29'),(24,5,7,'2024-03-18','2024-03-24'),
  (9,8,2,'2024-03-21',NULL);

SELECT pickup,
       COUNT(*) OVER (ORDER BY pickup) AS default_frame,
       COUNT(*) OVER (ORDER BY pickup
                      ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS row_based,
       COUNT(*) OVER (ORDER BY pickup
                      ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS sliding_three
FROM loan ORDER BY pickup, id;
SQL

┌────────────┬───────────────┬───────────┬───────────────┐
│   pickup   │ default_frame │ row_based │ sliding_three │
├────────────┼───────────────┼───────────┼───────────────┤
│ 2024-03-01 │ 3             │ 1         │ 1             │
│ 2024-03-01 │ 3             │ 2         │ 2             │
│ 2024-03-01 │ 3             │ 3         │ 3             │
│ 2024-03-04 │ 4             │ 4         │ 3             │
│ 2024-03-06 │ 5             │ 5         │ 3             │
│ 2024-03-11 │ 8             │ 6         │ 3             │
│ 2024-03-11 │ 8             │ 7         │ 3             │
│ 2024-03-11 │ 8             │ 8         │ 3             │
│ 2024-03-18 │ 10            │ 9         │ 3             │
│ 2024-03-18 │ 10            │ 10        │ 3             │
│ 2024-03-21 │ 11            │ 11        │ 3             │
└────────────┴───────────────┴───────────┴───────────────┘

Three columns produced three different answers from the same data.

In the first column, all three of March 1st’s rows got the value 3: a value-based frame counts rows sharing the same date as a single set of peers and gives all of them the total at the end of that set. This is the answer to the question “the total as of the end of March 1st,” and it is not affected by the rows’ order in the table.

The second column is row-based: each row adds one to the previous one, proceeding 1, 2, 3. Rows sharing the same date getting different values shows that the result depends on ordering. If the sort key does not fully separate the rows, this column’s value is not deterministic: the same query could give a different distribution within a group of three, run on the engine reading the rows in a different order, in a different version, or under a different plan. If separation is required, a column that guarantees uniqueness is added to the ORDER BY key.

The third column is a sliding window: the current row plus the two before it. In the first two rows the frame had not yet filled, so the values 1 and 2 came out; after that a constant 3 remained. Unlike a running total, a sliding window is local: the distant past falls out of the frame.

This difference in the three behaviors shows that a frame is not a detail. The same function, the same ordering, and the same data written as three expressions produced three separate columns; which one is correct depends on the question being asked.

Running Total and Sliding Average

If frames were rewritten for every column, a query would stop being readable. The WINDOW clause names frame definitions; only the name is written after OVER:

sqlite3 -box -header <<'SQL'
CREATE TABLE loan(id INTEGER PRIMARY KEY, book_id INT, member_id INT, pickup TEXT, returned TEXT);
INSERT INTO loan VALUES
  (1,1,1,'2024-03-01','2024-03-15'),(2,3,1,'2024-03-01','2024-03-20'),
  (6,2,2,'2024-03-01','2024-03-12'),(3,5,1,'2024-03-04','2024-03-18'),
  (7,4,2,'2024-03-06','2024-03-25'),(4,7,1,'2024-03-11',NULL),
  (8,6,2,'2024-03-11','2024-03-19'),(23,2,7,'2024-03-11','2024-03-16'),
  (5,9,1,'2024-03-18','2024-03-29'),(24,5,7,'2024-03-18','2024-03-24'),
  (9,8,2,'2024-03-21',NULL);

SELECT pickup,
       CAST(SUM(julianday(COALESCE(returned,'2024-03-31')) - julianday(pickup))
            OVER cumulative AS INT) AS running_total_days,
       ROUND(AVG(julianday(COALESCE(returned,'2024-03-31')) - julianday(pickup))
             OVER sliding, 1) AS sliding_average
FROM loan
WINDOW cumulative AS (ORDER BY pickup ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW),
       sliding AS (ORDER BY pickup ROWS BETWEEN 2 PRECEDING AND CURRENT ROW)
ORDER BY pickup, id;
SQL

┌────────────┬────────────────────┬─────────────────┐
│   pickup   │ running_total_days │ sliding_average │
├────────────┼────────────────────┼─────────────────┤
│ 2024-03-01 │ 14                 │ 14.0            │
│ 2024-03-01 │ 33                 │ 16.5            │
│ 2024-03-01 │ 44                 │ 14.7            │
│ 2024-03-04 │ 58                 │ 14.7            │
│ 2024-03-06 │ 77                 │ 14.7            │
│ 2024-03-11 │ 97                 │ 17.7            │
│ 2024-03-11 │ 105                │ 15.7            │
│ 2024-03-11 │ 110                │ 11.0            │
│ 2024-03-18 │ 121                │ 8.0             │
│ 2024-03-18 │ 127                │ 7.3             │
│ 2024-03-21 │ 137                │ 9.0             │
└────────────┴────────────────────┴─────────────────┘

The value being measured is how many days each transaction lasted; for records not yet returned, a count-through date is assumed. The first column is the cumulative total of these durations, and it keeps rising. The second column is the average duration of the last three transactions, and it rises and falls: after mid-March, as short transactions start arriving, the average drops from 17.7 to 7.3. A running total hides a trend; a sliding average reveals it — the two are separate pieces of information read from the same data.

The WINDOW clause is standard and is written at the end of the query, before ORDER BY. In an engine that does not support it, the definitions are copied out explicitly into OVER; the meaning does not change.

Access to a Neighboring Row

Some functions exist only as window functions; they have no aggregate counterpart. LAG and LEAD fetch the value of a row a given number of positions before or after the current one, within the same partition:

sqlite3 -box -header <<'SQL'
CREATE TABLE loan(id INTEGER PRIMARY KEY, book_id INT, member_id INT, pickup TEXT, returned TEXT);
INSERT INTO loan VALUES
  (1,1,1,'2024-03-01','2024-03-15'),(2,3,1,'2024-03-01','2024-03-20'),
  (6,2,2,'2024-03-01','2024-03-12'),(3,5,1,'2024-03-04','2024-03-18'),
  (7,4,2,'2024-03-06','2024-03-25'),(4,7,1,'2024-03-11',NULL),
  (8,6,2,'2024-03-11','2024-03-19'),(23,2,7,'2024-03-11','2024-03-16'),
  (5,9,1,'2024-03-18','2024-03-29'),(24,5,7,'2024-03-18','2024-03-24'),
  (9,8,2,'2024-03-21',NULL);

SELECT member_id, pickup,
       LAG(pickup) OVER (PARTITION BY member_id ORDER BY pickup) AS previous,
       CAST(julianday(pickup) - julianday(LAG(pickup) OVER (PARTITION BY member_id ORDER BY pickup))
            AS INT) AS days_between
FROM loan ORDER BY member_id, pickup;
SQL

┌───────────┬────────────┬────────────┬──────────────┐
│ member_id │   pickup   │  previous  │ days_between │
├───────────┼────────────┼────────────┼──────────────┤
│ 1         │ 2024-03-01 │            │              │
│ 1         │ 2024-03-01 │ 2024-03-01 │ 0            │
│ 1         │ 2024-03-04 │ 2024-03-01 │ 3            │
│ 1         │ 2024-03-11 │ 2024-03-04 │ 7            │
│ 1         │ 2024-03-18 │ 2024-03-11 │ 7            │
│ 2         │ 2024-03-01 │            │              │
│ 2         │ 2024-03-06 │ 2024-03-01 │ 5            │
│ 2         │ 2024-03-11 │ 2024-03-06 │ 5            │
│ 2         │ 2024-03-21 │ 2024-03-11 │ 10           │
│ 7         │ 2024-03-11 │            │              │
│ 7         │ 2024-03-18 │ 2024-03-11 │ 7            │
└───────────┴────────────┴────────────┴──────────────┘

The first row of every partition has no previous row; LAG gives a null value there, and subtracting with a null value also produces a null value. Not crossing the partition boundary is part of the frame’s definition: when member_id changes, history resets, and one member’s last transaction is not considered a neighbor of another member’s first.

The same result could be produced with a correlated subquery — “the largest of the dates smaller than this row’s” — but that phrasing performs a search for every row. A window function sorts the partition once and proceeds in a single pass.

Summary

• The OVER clause turns an aggregate function into a window function that preserves rows; PARTITION BY does the same partitioning as GROUP BY without swallowing rows.
• Window functions run after WHERE, GROUP BY, and HAVING; filtering on their result requires wrapping the query in a common table expression.
• A frame is measured by row count with ROWS, by value with RANGE; if ORDER BY is given and no frame is written, the default is RANGE … CURRENT ROW, and peer rows get the same result.
• On the same data, the default frame, a row-based running total, and a three-row sliding window produced three separate columns; which is correct depends on the question asked.
• LAG and LEAD access a neighboring row within a partition and do not cross the partition boundary.

Next Step

In this lesson, a window ran a computation over rows but did not give the rows a rank. Yet questions like “the three members who borrowed the most” call for a rank number, and immediately after that a decision has to be made: should two members with an equal count get the same rank, or different ones? The next lesson places row number, rank, and dense rank side by side on the same data and shows the difference in their tie behavior.