Overview

Window functions is a group of SQL functions, that operate on a partition or “window” of a result set, returning values for every row within that window. The following window functions and clauses are currently supported by Oxla:

Window Functions

Function Name	Description
COUNT	Counts all the rows or those specified by the given expression
AVG	Calculates the average (arithmetic mean) of a set of numeric values within a window
SUM	Calculates and returns the sum of values from the input column or expression values
MIN	Computes the minimum value of an expression across a set of rows
MAX	Computes the maximum value of an expression across a set of rows
BOOL_AND	Evaluates whether all values within a specified window of rows are true
BOOL_OR	Evaluates whether at least one value within a specified window of rows is true

Ranking Functions

Function Name	Description
ROW_NUMBER	Returns the current row index within its partition (beginning with 1)
RANK	Calculates and returns the rank of a value within a specified group of values
DENSE_RANK	Calculates the percent rank of a value within a group and returns the result
NTILE	Divides an ordered data set into a specified number of approximately equal groups

Distribution Functions

Function Name	Description
CUME_DIST	Calculates the cumulative distribution of a value within a set of values
PERCENT_RANK	Calculates and returns the percent rank of a value within a specified group of values

Value Functions

Function Name	Description
FIRST_VALUE	Returns the first value in an ordered set of values within a specified partition
LAST_VALUE	Returns the last value in an ordered set of values within a specified partition
NTH_VALUE	Returns a value from the nth row in an ordered partition of a result set
LAG	Returns the values for a row located at a defined offset, either above or below the current row within the partition
LEAD	Returns the values for a row located at a defined offset, either above or below the current row within the partition

Window Clause

Clause Name	Description
OVER	Defines the window specification and is mandatory for window functions
WINDOW	Optional clause that defines one or more named window specifications

Important Notes

There are a few essential things to remember when using window functions in Oxla:

• Verify that you can effectively use window functions alongside the PARTITION BY, ORDER BY and FRAME clauses as part of your window specification
• Ensure the window specification chaining is supported by executing the following command: SELECT SUM(i0) OVER w2 FROM tb1 WINDOW w1 AS (PARTITION BY i1), w2 AS (w1 ROWS CURRENT ROW)
• The FRAME clause of the window specification is restricted to the ROWS clause and does not include frame exclusion