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 FunctionsDescription
COUNTCounts all the rows or those specified by the given expression.
DENSE_RANKCalculates the percent rank of a value within a group and returns the result
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
PERCENT_RANKCalculates and returns the percent rank of a value within a specified group of values
RANKCalculates and returns the rank of a value within a specified group of values
ROW_NUMBERReturns the current row index within its partition (beginning with 1)
SUMCalculates and returns the sum of values from the input column or expression values
Window ClausesDescription
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