Window function (SQL)

A window function in SQL performs a calculation across a set of table rows that are related to the current row. This set of rows is known as the "window" or "window frame." Unlike aggregate functions, which reduce the number of rows in the result set, window functions do not. Instead, they add calculated values to each row, based on the window's data.

Window functions use the OVER() clause to define the window. This clause can specify partitioning, ordering, and framing. Partitioning divides the rows into partitions, and the window function is applied to each partition independently. Ordering determines the order in which the window function operates on the rows within a partition. Framing defines a subset of rows within a partition that are included in the window.

Common window functions include:

• Ranking functions: RANK(), DENSE_RANK(), ROW_NUMBER(), and NTILE() assign ranks to rows within a partition.
• Value functions: LAG() and LEAD() access data from preceding or succeeding rows, respectively. FIRST_VALUE(), LAST_VALUE(), and NTH_VALUE() return values from the first, last, or nth row in a window.
• Aggregate functions as window functions: SUM(), AVG(), MIN(), MAX(), and COUNT() can be used as window functions to compute aggregate values over a window of rows.

Window functions are typically used for tasks such as calculating running totals, moving averages, ranking rows within groups, and comparing values across rows. They provide a powerful way to perform complex calculations without resorting to subqueries or complex joins.