SQLite Window Functions

SQLite window functions provide computing power across the group associated with the current row. Window functions are different from aggregate functions, which perform calculations on each group and return a row for each group, window functions do not output each group to a row and combine the calculation results of each group into each row in this group.

All window functions rely on OVER clauses, which can be used to group all rows and sort rows within groups. The calling syntax of the window function is as follows:

window_function(args)
OVER (
  [PARTITION BY partition_column_list]
  [ORDER BY order_column_list]
)

In this Syntax:

  • The window_function(args) is the window function that needs to be called. In addition to specific window functions, you can also use aggregate functions here.
  • The PARTITION BY specifies the column name that needs to be partitioned. It is optional. Defaults to one partition for the entire result set.
  • The ORDER BY specifies the sort column name. It is optional.

SQLite provides many window functions that can be used to obtain information such as rank, row number, cumulative distribution, bucket rank, etc.

  1. cume_dist

    The SQLite cume_dist() function returns the cumulative distribution of the current row.

  2. dense_rank

    The SQLite dense_rank() function returns the rank within the partition in which the current row is located, starting at 1, with no gap.

  3. first_value

    The SQLite first_value() function returns the evaluated value from the first row of the window frame associated with the current row.

  4. lag

    The SQLite lag() function returns the value from the specified row before the current row in the partition where the current row is located.

  5. last_value

    The SQLite last_value() function returns the evaluated value from the last row of the window frame associated with the current row.

  6. lead

    The SQLite lead() function returns the value from the specified row after the current row in the partition where the current row is located.

  7. nth_value

    The SQLite nth_value() function returns the evaluated value from the specified row of the window frame associated with the current row.

  8. ntile

    The SQLite ntile() function divides all rows in the partition into the specified number of buckets as evenly as possible, and returns the rank of the bucket where the current row is located.

  9. percent_rank

    The SQLite percent_rank() function returns the relative rank within the partition where the current row is located, that is (rank() - 1) / (number of rows partition - 1).

  10. rank

    The SQLite rank() function returns the rank within the partition in which the current row is located, starting at 1, with gaps.

  11. row_number

    The SQLite row_number() function returns the row number of the current row within the partition where the current row is located, starting from 1.