Discover published sets by community

A window function performs a calculation across a set of table rows that are somehow related to the current row. Unlike regular aggregate functions, window functions do not collapse the rows into a single output row; they return a value for each row, based on the calculation over a window.

This function assigns a unique sequential integer to rows within a partition of a result set, starting at 1 for the first row in each partition.

LEAD() provides access to a row at a given physical offset that follows the current row, while LAG() provides access to a row at a given physical offset that precedes the current row. They are useful for comparing rows within the same result set.

RANK() assigns ranks to rows within a partition with gaps in ranking numbers for ties, while DENSE_RANK() also ranks rows within a partition, but without gaps in ranking numbers for ties, ensuring consecutive ranks.

The OVER() clause specifies the partitioning and ordering of a window and is used to determine the set of rows used for each calculation by the window function. This is where the 'window' of the window function is defined.

Used within the OVER() clause, the PARTITION BY clause divides the result set into partitions the window function is applied to. Rows within a partition have the same values for the partition columns.

Window functions can be used to calculate running totals by using an aggregate function (like SUM) with the OVER() clause to include all preceding rows in the current partition in the calculation.