Window Frames

By the end of this lesson you can say which rows a window function covers, set that with ROWS BETWEEN, and spot two quiet failures of the default frame.

In Window Functions, ORDER BY inside OVER made an aggregate cumulative. This lesson is the frame behind that rule and where it misbehaves. Examples use deposits(id, day, amount): five rows, two on day 2, none on days 4 and 5.

The frame: which rows the function sees

A frame is the slice of its partition a window function covers for the current row. With no ORDER BY in OVER it is the whole partition. With one, the default is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW: from the start through the current row and its peers, the rows that tie with it on the ORDER BY key.

SELECT id, day, amount,
       SUM(amount) OVER ()                 AS whole,
       SUM(amount) OVER (ORDER BY day)     AS by_day,
       SUM(amount) OVER (ORDER BY day, id) AS by_id
FROM deposits ORDER BY day, id;

1|1|50|150|50|50
2|2|20|150|100|70
3|2|30|150|100|100
4|3|10|150|110|110
5|6|40|150|150|150

Ids 2 and 3 are peers on day, so each one's by_day includes the other: both show 100 and the 70 never appears. A unique tiebreaker (day, id) removes the peers. ROWS counts physical rows where RANGE stretches over peers.

Moving windows with ROWS

A bound is n PRECEDING, CURRENT ROW, n FOLLOWING, UNBOUNDED PRECEDING or UNBOUNDED FOLLOWING. Trailing three rows, and one row each side:

SELECT id, day, amount,
       SUM(amount) OVER (ORDER BY day, id ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS trail,
       SUM(amount) OVER (ORDER BY day, id ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS centred
FROM deposits ORDER BY day, id;

1|1|50|50|70
2|2|20|70|100
3|2|30|100|60
4|3|10|60|80
5|6|40|80|50

The frame is cut at the partition's edge, never padded: row 1 sums one row, not three, and an AVG there would divide by 1.

ROWS counts rows, not days: id 5 is on day 6, yet its sum of 80 reaches back to day 2. The standard's value-based RANGE BETWEEN 2 PRECEDING AND CURRENT ROW, GROUPS and EXCLUDE are missing from SQLite 3.27 (RANGE takes only UNBOUNDED or CURRENT ROW). The calendar version is a correlated subquery:

SELECT d.id, d.day,
       (SELECT SUM(x.amount) FROM deposits x
        WHERE x.day BETWEEN d.day - 2 AND d.day) AS last3days
FROM deposits d ORDER BY d.day, d.id;

1|1|50
2|2|100
3|2|100
4|3|110
5|6|40

FIRST_VALUE, LAST_VALUE, NTH_VALUE

These read one row of the frame. FIRST_VALUE works by default, since the frame starts at the partition's start. LAST_VALUE does not: the frame ends at the current row, so it returns that row's own value (or its last peer's).

SELECT id, amount,
       LAST_VALUE(amount) OVER (ORDER BY day, id) AS wrong,
       LAST_VALUE(amount) OVER (ORDER BY day, id
                                ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS last_all,
       NTH_VALUE(amount, 2) OVER (ORDER BY day, id
                                  ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS second
FROM deposits ORDER BY day, id;

1|50|50|40|20
2|20|20|40|20
3|30|30|40|20
4|10|10|40|20
5|40|40|40|20

wrong is just amount; the full frame fixes it, and NTH_VALUE needs it too.

Naming a window

WINDOW w AS (...) goes after FROM/WHERE/HAVING and before ORDER BY; OVER w reuses it:

SELECT id, amount,
       SUM(amount) OVER w AS run,
       MAX(amount) OVER w AS best
FROM deposits
WINDOW w AS (ORDER BY day, id)
ORDER BY day, id;

1|50|50|50
2|20|70|50
3|30|100|50
4|10|110|50
5|40|150|50

In 3.27 OVER (w ROWS ...) is a syntax error, so windows that differ in frame are written out in full.

Common mistakes

• Running total over a non-unique key. Tied rows both show the total including each other (100 and 100, not 70 and 100). Order by a unique key, or use a ROWS frame.
• LAST_VALUE with no frame. It repeats each row's own value instead of the partition's last.
• Reading n PRECEDING as n days. It is n rows, so a date gap widens the span.
• No tiebreaker in a ROWS window. Tied rows can enter the frame in either order, so sums can vary.

Your exercise

Print four window figures per reading, per sensor. The starter prints val in all four places; the likeliest slip is using one ordering and frame for every window.