An SQLite window function performs a calculation on a set of rows that are related to the current row. Unlike an aggregate function, a window function does not cause rows to become grouped into a single result row. a window function retains the row identities. Behind the scenes, window functions can access more than just the current row of the query result.
The following picture illustrates the differences between aggregate functions and window functions:
The window functions are divided into three categories: value window functions, ranking window functions, and aggregate window functions as shown in the following picture:
Window functions are also known as analytic functions. The following table shows all window functions supported by SQLite:
| Name | Description |
|---|---|
| CUME_DIST | Compute the cumulative distribution of a value in an ordered set of values. |
| DENSE_RANK | Compute the rank for a row in an ordered set of rows with no gaps in rank values. |
| FIRST_VALUE | Get the value of the first row in a specified window frame. |
| LAG | Provide access to a row at a given physical offset that comes before the current row. |
| LAST_VALUE | Get the value of the last row in a specified window frame. |
| LEAD | Provide access to a row at a given physical offset that follows the current row. |
| NTH_VALUE | Return the value of an expression evaluated against the row N of the window frame in the result set. |
| NTILE | Divide a result set into a number of buckets as evenly as possible and assign a bucket number to each row. |
| PERCENT_RANK | Calculate the percent rank of each row in an ordered set of rows. |
| RANK | Assign a rank to each row within the partition of the result set. |
| ROW_NUMBER | Assign a sequential integer starting from one to each row within the current partition. |