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.
ROWS BETWEEN 3 PRECEDING AND CURRENT ROWis the current row and the three rows before it. Near the top of a partition fewer rows exist, and the function uses the ones that do.- A
ROWSframe counts rows and knows nothing about days. When a day has no row, "the last four rows" reaches one day further back than "the last four days". - A
RANGEbound with an offset is a distance in the units of theORDER BYvalue. SoRANGE BETWEEN 3 PRECEDING AND CURRENT ROWneeds a number to order by, such asUNIX_DATE(completed_date). The boundsCURRENT ROWandUNBOUNDEDhave no offset, and they work with any type. With peers, aROWSframe can start or end in the middle of a tie, and which peer comes first is not defined unless theORDER BYbreaks the tie. - With an
ORDER BYand no frame, the frame isRANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, so it ends at the current row and the rows tied with it.FIRST_VALUE,LAST_VALUEandNTH_VALUEread the first, last and nth row of the frame. The frame can be smaller than the partition. - A
WINDOW name AS (...)clause comes afterFROM,WHERE,GROUP BYandHAVING, and before the finalORDER BY.OVER nameuses it as it is, andOVER (name ROWS BETWEEN ...)starts from it and adds a frame. - BigQuery refuses a frame on
LAG,LEADand the ranking functions, and it refuses to add a frame to a named window that has one already. Keep the frame out of a shared window.
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_idThe 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.