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 Function	Description
AVG	Calculates the average (arithmetic mean) of a set of numeric values within a window
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
COUNT	Counts all the rows or those specified by the given expression
CUME_DIST	Calculates the cumulative distribution of a value within a set of values
DENSE_RANK	Calculates the percent rank of a value within a group and returns the result
FIRST_VALUE	Returns the first value in an ordered set of values within a specified partition
LAG	Returns the values for a row located at a defined offset, either above or below the current row within the partition
LAST_VALUE	Returns the last value in an ordered set of values within a specified partition
LEAD	Returns the values for a row located at a defined offset, either above or below the current row within the partition
MAX	Computes the maximum value of an expression across a set of rows
MIN	Computes the minimum value of an expression across a set of rows
NTH_VALUE	Returns a value from the nth row in an ordered partition of a result set
NTILE	Divides an ordered data set into a specified number of approximately equal groups
PERCENT_RANK	Calculates and returns the percent rank of a value within a specified group of values
RANK	Calculates and returns the rank of a value within a specified group of values
ROW_NUMBER	Returns the current row index within its partition (beginning with 1)
SUM	Calculates and returns the sum of values from the input column or expression values

---

Window Clause	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