Advanced window frames

The frame tells a window aggregate exactly which neighbouring rows to include. It is separate from the partition.

ROWS frames

ROWS measures positions in the ordered row sequence. UNBOUNDED PRECEDING begins at the partition’s first row. A fixed number PRECEDING/FOLLOWING describes a moving row neighborhood.

RANGE and peers

RANGE works with ordering values and includes peer rows with equal sort values at a current-row boundary. Available offset forms depend on the engine. On equal salaries, ROWS and RANGE can produce different cumulative totals.

GROUPS and exclusion

SQLite also supports GROUPS frames and EXCLUDE options. These are not supported identically across engines. Know whether you mean physical rows, peer groups, or a value interval before selecting syntax.

Gaps and islands

A continuous run can be found by comparing a sequence value with its ROW_NUMBER, or by using LAG to flag breaks and a cumulative sum to number islands. Define what counts as a gap and remove duplicates first.