SQL Window Functions

A window function allows for computation across several rows of a result set, similar to an aggregate function. However, rather than returning a single computed result across all of the rows in the result set, a window function returns each row that it operates on along with a value that the window function computes and associates with that row. The result set of a window function is not a grouped or summarized set of rows, as is the case with aggregate functions. Window functions can also be referred to as analytic functions.

A SQL SELECT statement column_list can contain multiple window functions. If you specify a window function then it must be an item in the SELECT column_list or ORDER BY clause. It cannot be used as an expression in a WHERE clause. It also cannot be used in a GROUP BY or HAVING clause. This is because NuoDB first evaluates the expressions in these clauses and then for each row, it executes the window function. Window functions are supported in derived tables and SQL sub-SELECT statements. These sub-SELECT statements can be used as an item in the SELECT statement column_list or in the WHERE clause.


 WINDOW_FUNCTION( arguments ) OVER ( [ partition_clause ] [ order_by_clause ] ) 


arguments passed to the window function. Not all window functions accept arguments. See specific functions below for details on window functions that allow arguments.


A window function contains an OVER clause. This is what differentiates it from other functions. This OVER clause will define how all of the rows being returned by the SQL SELECT statement will be grouped and sorted for calculating the returned value for each row.

The following parameters are contained in the OVER clause:


The partition_clause format is: PARTITION BY column_name [ , column_name , ... ]

The partition_clause is optional.

The partition_clause defines how the SELECT result set is to be grouped for computing the values returned by the window function. In each partition group, every row has the same value for all of the columns defined by the partition_clause. This group of rows is considered the window of rows. The window function returns a value for each row in the partition group, or window, based on some computed value across all rows in the partition group. See each individual window function below for a definition of the computed value for that window function.

If no partition_clause is provided, the window function will return a value for each row based on some computed value across the entire SELECT result set.


The order_by_clause format is: ORDER BY column_name [ ASC | DESC ] [ , column_name [ ASC | DESC ] , ... ]

The order_by_clause is optional for all window functions except the ROW_NUMBER window function.

The order_by_clause defines the order in which the window function computes and returns the value for each row defined in the partition group, or window. If you specify a partition clause, then for each partition group, rows are ordered and assigned computed values based on this order_by_clause.

The order for each column can be specified in either ascending, the default, or descending order.

The order_by_clause is not the same as the SQL SELECT statement ORDER BY clause. Window functions are executed at the end of query execution, just before the SQL SELECT statement ORDER BY clause is evaluated. If there is no SQL SELECT statement ORDER BY clause, then the output rows are ordered by the columns specified by the window function order_by_clause, within the groups defined by the partition_clause, if one is provided. See the last two examples for the AVG() window function. This example uses an ORDER BY clause to change the ordering of output rows after the window function is executed.

See each window function below for syntax and how they each compute values.