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