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
COUNTCounts all the rows or those specified by the given expression
AVGCalculates the average (arithmetic mean) of a set of numeric values within a window
SUMCalculates and returns the sum of values from the input column or expression values
MINComputes the minimum value of an expression across a set of rows
MAXComputes the maximum value of an expression across a set of rows
BOOL_ANDEvaluates whether all values within a specified window of rows are true
BOOL_OREvaluates whether at least one value within a specified window of rows is true

Ranking Functions

Function NameDescription
ROW_NUMBERReturns the current row index within its partition (beginning with 1)
RANKCalculates and returns the rank of a value within a specified group of values
DENSE_RANKCalculates the percent rank of a value within a group and returns the result
NTILEDivides an ordered data set into a specified number of approximately equal groups

Distribution Functions

Function NameDescription
CUME_DISTCalculates the cumulative distribution of a value within a set of values
PERCENT_RANKCalculates and returns the percent rank of a value within a specified group of values

Value Functions

Function NameDescription
FIRST_VALUEReturns the first value in an ordered set of values within a specified partition
LAST_VALUEReturns the last value in an ordered set of values within a specified partition
NTH_VALUEReturns a value from the nth row in an ordered partition of a result set
LAGReturns the values for a row located at a defined offset, either above or below the current row within the partition
LEADReturns the values for a row located at a defined offset, either above or below the current row within the partition

Window Clause

Clause NameDescription
OVERDefines the window specification and is mandatory for window functions
WINDOWOptional 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