SQLite Window Frame in Window Functions

Summary: in this tutorial, you will learn about the SQLite window frame and how to use it to specify a subset of partitions for calculation.

Introduction to the SQLite window frame

Some window functions use the window frame in their calculations such as FIRST_VALUE(), LAST_VALUE(), and SUM(). A window frame is used to specify how many rows around the current row the window should include.

To define a window frame, you use one of the following syntaxes:

{ RANGE | ROWS } frame_start
{ RANGE | ROWS } BETWEEN frame_start AND frame_end  
Code language: SQL (Structured Query Language) (sql)

The frame_start can take one of the following options:

N PRECEDING
UNBOUNDED PRECEDING
CURRENT ROWCode language: SQL (Structured Query Language) (sql)

And the frame_end can take one of the following options:

CURRENT ROW
UNBOUNDED FOLLOWING
N FOLLOWINGCode language: SQL (Structured Query Language) (sql)

The following picture shows the structure of a frame in a partition: