Analytic Functions

Additionally, it is possible to use UNBOUNDED PRECEDING, CURRENT ROW, and UNBOUNDED FOLLOWING clauses to specify the window boundaries.

Exasol also supports a short form for window frames that use CURRENT ROW. For example, RANGE UNBOUNDED PRECEDING is equivalent to RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW.

For ROWS or GROUPS, the computed expr in expr PRECEDING or expr FOLLOWING has to be a numeric value greater or equal 0 with scale 0. For RANGE the computed expr in expr PRECEDING or expr FOLLOWING has to be a numeric (or interval) value greater or equal 0.

The following example shows the different results by using window_frame_units:

SELECT 
  part, ord, arg, 
  LISTAGG(arg, ',') OVER (PARTITION BY part ORDER BY ord ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) LISTAGG_ROWS, 
  LISTAGG(arg, ',') OVER (PARTITION BY part ORDER BY ord RANGE BETWEEN 1 PRECEDING AND 1 FOLLOWING) LISTAGG_RANGE, 
  LISTAGG(arg, ',') OVER (PARTITION BY part ORDER BY ord GROUPS BETWEEN 1 PRECEDING AND 1 FOLLOWING) LISTAGG_GROUPS 
FROM ex_table ORDER BY part, ord;

Result

Window Frame Exclusion Clause

The window_frame_clause may also contain the window_frame_exclusion clause, which makes it possible to exclude certain rows from the window before the computation of the analytic function.

If the query does not contain a window_frame_exclusion clause, the default behavior is EXCLUDE NO OTHERS.

The following example shows the different results by using a named window clause: 

SELECT
  id, department, hire_date, starting_salary,
  AVG(starting_salary) OVER w2 AVG,
  MIN(starting_salary) OVER w2 MIN_STARTING_SALARY,
  MAX(starting_salary) OVER (w1 ORDER BY hire_date)
FROM employee_table
WINDOW w1 as (PARTITION BY department), w2 as (w1 ORDER BY hire_date)
ORDER BY department, hire_date;

Result

Syntax

analytic_functions::=

over_clause::=

partition_clause::=

order_clause::=

window_frame_clause::=

window_frame_exclusion::=

window_clause::=

Usage Notes

• Analytic functions are always evaluated after WHERE, GROUP BY, and HAVING clause, but before the global ORDER BY clause.
• The results within each partition are computed independently from the other partitions in the result set.
• In the window_frame_clause, the lower boundary of the window must lie before, or can be the same as the upper boundary according to the sorting order of the rows. Exasol returns an error if this condition is violated.
• If the analytic function uses DISTINCT, the function must not contain an order_clause and must not contain a window_frame_clause.
• The use of a non-default window_frame_exclusion clause is only possible in combination with an order_clause.
• The use of window frame unit RANGE in combination with expr PRECEDING or expr FOLLOWING requires an order_clause with a single order expression.
• Exasol allows the use of analytic functions only in the select list, in a QUALIFY clause, or in the global ORDER BY clause.
• It is possible to replace the contents of the over_clause partially or completely by referring to a named_window identifier. To use such a window it is necessary to define the identifier and the associate window specification parts (for example, a partition_clause) in the Named Window Clause.
• Exasol does not support nested analytic functions.
• The position of the window_clause is after HAVING in a SELECT statement.
• It is not possible to override a previously defined window_name identifier.
• The scope of the window_clause is only the current SELECT statement. It does not include subqueries.
• It is possible to combine several named window specifications. However, the combination has to contain only one partition_clause, one order_clause, and one window_frame_clause.

• If a named window specification contains an order_clause, you cannot combine it with a partition_clause.

• If a named window specification contains a window_frame_clause, you cannot combine it with any other analytic function clause.

Example

The input table share_closing_prices in the example lists closing prices of shares of different companies over 10 days. The following query generates a moving average of the closing price for each share for the previous 4 days and the current day.

SELECT 
    COMPANY, TRADING_DATE, CLOSING_PRICE, 
    AVG(CLOSING_PRICE) over (PARTITION BY COMPANY ORDER BY TRADING_DATE ROWS BETWEEN 4 PRECEDING AND CURRENT ROW) MOVING_AVG
FROM shares_closing_prices;

Result

The analytic function used in this example is AVG. The query partitions the table by COMPANY and orders the data by TRADING_DATE. The result for each row is the average of the CLOSING_PRICE for previous 4 days and the current day.

Supported Analytic Functions

Exasol supports the following analytic functions:

ANY

AVG

CORR

COUNT

COVAR_POP

COVAR_SAMP

CUME_DIST

DENSE_RANK

EVERY

FIRST_VALUE

GROUP_CONCAT

LAG

LAST_VALUE

LEAD

LISTAGG

MAX

MEDIAN

MIN

MUL

NTH_VALUE

NTILE

PERCENT_RANK

PERCENTILE_CONT

PERCENTILE_DISC

RANK

RATIO_TO_REPORT

REGR_FUNCTIONS

ROW_NUMBER

SOME

STDDEV

STDDEV_POP

STDDEV_SAMP

SUM

VAR_POP

VAR_SAMP

VARIANCE