Frames, LAST_VALUE and named windows

A running total looks at every earlier row, and LAG looks at one neighbour. A frame picks the rows in between, such as the last few rows, and it is also the reason LAST_VALUE often returns the wrong row.

How it works

For each row, the window builds a frame, a slice of its partition, and the function reads only that slice. A ROWS frame counts rows from the current one. A RANGE frame works on the value of the ORDER BY column. Rows with the same value are called peers, and they go into a frame or out of it together.

Common mistake

The default frame ends at the current row and the rows tied with it. With a unique ORDER BY, LAST_VALUE(x) OVER (ORDER BY d) returns x from the row it is on, and with ties it returns one value on all the tied rows. The query runs and the column looks plausible. To reach the end of the partition the frame needs an upper bound past the current row, or the order can be reversed so that FIRST_VALUE reads the row you want.

Worked example

SELECT completed_date, defect_summary.total_count AS defects,
  SUM(defect_summary.total_count) OVER w AS defects_so_far,
  SUM(defect_summary.total_count) OVER (w ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) AS defects_last_two
FROM `learn.mot.fact_mot_test`
WHERE completed_date BETWEEN '2023-01-01' AND '2024-12-31'
  AND vehicle_id = 'VEH000002'
WINDOW w AS (ORDER BY completed_date, completed_timestamp, mot_test_id)
ORDER BY completed_date, completed_timestamp, mot_test_id

The window w orders the tests of VEH000002. defects_so_far uses it as it is, and defects_last_two adds a frame of the current test and the one before it.

The frame is not part of w, so a function that refuses a frame could still use w.

All BigQuery lessons