Introduction

SQLite window functions are functions that operate on a set of rows that are related to the current row. Unlike aggregate functions, window functions do not cause rows to become grouped into a single result row. Instead, window functions retain the row identities. Behind the scenes, window functions can access more than just the current row of the query result.

SQLite supports a wide variety of window functions, including:

• Aggregate window functions: These functions operate on a set of rows and return a single value for the entire set. Some examples of aggregate window functions include SUM(), AVG(), and COUNT().
• Ranking window functions: These functions assign a rank to each row within a set of rows. Some examples of ranking window functions include RANK(), DENSE_RANK(), and ROW_NUMBER().
• Distribution window functions: These functions calculate the distribution of values within a set of rows. Some examples of distribution window functions include PERCENT_RANK(), CUME_DIST(), and NTILE().
• Analytic window functions: These functions perform a calculation on a set of rows and return a value for each row. Some examples of analytic window functions include LEAD(), LAG(), and FIRST().

To use a window function, you need to use the OVER clause. The OVER clause specifies the window that the window function will operate on. The window can be specified by a number of factors, including the partition, the order, and the frame.

The partition specifies how the rows will be grouped. The order specifies the order in which the rows will be processed. The frame specifies the rows that will be included in the window.

Example

• Aggregate window functions:

  • SUM(): Calculates the sum of all values in a window.

    SELECT SUM(quantity) OVER (PARTITION BY product_category) AS total_sales FROM orders;

  • AVG(): Calculates the average of all values in a window.

    SELECT AVG(quantity) OVER (PARTITION BY product_category) AS average_sales FROM orders;

  • COUNT(): Calculates the number of rows in a window.

    SELECT COUNT(*) OVER (PARTITION BY product_category) AS number_of_orders FROM orders;

• Ranking window functions:

  • RANK(): Assigns a rank to each row within a window, starting from 1.

    SELECT product_name, RANK() OVER (ORDER BY quantity DESC) AS rank FROM orders;

  • DENSE_RANK(): Assigns a rank to each row within a window, starting from 1, without skipping any ranks.

    SELECT product_name, DENSE_RANK() OVER (ORDER BY quantity DESC) AS rank FROM orders;

  • ROW_NUMBER(): Assigns a sequential number to each row within a window, starting from 1.

    SELECT product_name, ROW_NUMBER() OVER (ORDER BY quantity DESC) AS rank FROM orders;

• Distribution window functions:

  • PERCENT_RANK(): Calculates the percentage rank of each row within a window. The percentage rank is the number of rows that are equal to or less than the current row, divided by the total number of rows in the window.

    SELECT product_name, PERCENT_RANK() OVER (ORDER BY quantity DESC) AS rank FROM orders;

  • CUME_DIST(): Calculates the cumulative distribution of each row within a window. The cumulative distribution is the number of rows that are equal to or less than the current row, as a percentage of the total number of rows in the window.

    SELECT product_name, CUME_DIST() OVER (ORDER BY quantity DESC) AS rank FROM orders;

  • NTILE(): Divides the rows in a window into a specified number of buckets and assigns each row to a bucket.

    SELECT product_name, NTILE(4) OVER (ORDER BY quantity DESC) AS bucket FROM orders;

• Analytic window functions:

  • LEAD(): Returns the value of the expression at the specified offset from the current row, in the same partition.

    SELECT product_name, quantity, LEAD(quantity, 1) OVER (PARTITION BY product_name ORDER BY quantity DESC) AS next_quantity FROM orders;

  • LAG(): Returns the value of the expression at the specified offset from the current row, in the same partition.

    SELECT product_name, quantity, LAG(quantity, 1) OVER (PARTITION BY product_name ORDER BY quantity DESC) AS previous_quantity FROM orders;

  • FIRST(): Returns the first value of the expression in the window.

    SELECT product_name, quantity, FIRST(quantity) OVER (PARTITION BY product_name) AS first_quantity FROM orders;

Conclusion

For more information on SQLite window functions, you can refer to the SQLite documentation: https://www.sqlite.org/windowfunctions.html.