Analytic Functions: Better Performance Extended Functionality

Detail name	Value
Changelog Number	7458
Type	New Feature
Status	Resolved
Fix Versions	Exasol 6.2.0, Exasol 6.2.dev3
Resolution Date	2019-02-08

Exasol 6.2 contains a completely overhauled analytic function framework.
The new framework speeds up the execution of analytic functions and includes all analytic function features defined by the SQL 2016 Standard.

 Please consider the changed behavior that accompanies these improvements and extensions (section Changed Behaviour)

Main performance improvements

• Faster execution of analytic functions with ORDER BY in the OVER clause.
• Faster execution of queries with several analytic functions.
• Faster execution of complex analytic functions like REGR_INTERCEPT.

New Features

• Besides ROWS, the new version supports the additional window frame units RANGE and GROUPS. For example:

SELECT MAX(salary) OVER (PARTITION BY dep ORDER BY startdate
    ROWS UNBOUNDED PRECEDING) FROM dataset;

SELECT MAX(salary) OVER (PARTITION BY dep ORDER BY startdate
    RANGE UNBOUNDED PRECEDING) FROM dataset;

SELECT MAX(salary) OVER (PARTITION BY dep ORDER BY startdate
    GROUPS UNBOUNDED PRECEDING) FROM dataset;

• The new version supports the two previously missing window boundaries <value> PRECEDING and <value> FOLLOWING. This allows the computation of sliding windows in Exasol and extends the number of possible window frame configurations from previously 4 to 13:
• The new version supports all exclude clauses of the SQL 2016 Standard:

• UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
• UNBOUNDED PRECEDING AND CURRENT ROW
• CURRENT ROW AND CURRENT ROW
• CURRENT ROW AND UNBOUNDED FOLLOWING
• UNBOUNDED PRECEDING AND <value> PRECEDING
• UNBOUNDED PRECEDING AND <value> FOLLOWING
• <value> PRECEDING AND <value> PRECEDING
• <value> PRECEDING AND CURRENT ROW
• <value> PRECEDING AND <value> FOLLOWING
• <value> PRECEDING AND UNBOUNDED FOLLOWING
• CURRENT ROW AND <value> FOLLOWING
• <value> FOLLOWING and <value> FOLLOWING
• <value> FOLLOWING and UNBOUNDED FOLLOWING

SELECT MAX(salary) OVER (PARTITION BY dep ORDER BY startdate
    RANGE UNBOUNDED PRECEDING EXCLUDE CURRENT ROW) FROM dataset;

SELECT MAX(salary) OVER (PARTITION BY dep ORDER BY startdate
    RANGE UNBOUNDED PRECEDING EXCLUDE TIES) FROM dataset;

SELECT MAX(salary) OVER (PARTITION BY dep ORDER BY startdate
    RANGE UNBOUNDED PRECEDING EXCLUDE GROUP) FROM dataset;

• The new version supports named windows to allow compact queries with better readability:

SELECT MIN(salary) OVER (w), MAX(salary) OVER (w) FROM dataset
    WINDOW w as (PARTITION BY dep ORDER BY startdate RANGE UNBOUNDED PRECEDING);

• The new version supports eight additional analytic functions:
• The new version supports the IGNORE NULLS option for the following functions:
• The new version supports implicit casts of arguments for analytic functions.

• ANY
• CUME_DIST
• EVERY
• LISTAGG
• NTH_VALUE
• NTILE
• PERCENT_RANK
• SOME (alias for ANY)

• FIRST_VALUE
• LAG
• LAST_VALUE
• LEAD
• NTH_VALUE

Changed Behavior

• Two new reserved keywords:

• GROUPS
• LISTAGG

• Analytic functions no longer automatically sort the result set. Example:

CREATE OR REPLACE TABLE dataset(id int, dep varchar(100), startdate timestamp, salary double);
INSERT INTO dataset VALUES(3,'A', '2018-03-01', 6500);
INSERT INTO dataset VALUES(2,'A', '2018-02-01', 5500);
INSERT INTO dataset VALUES(1,'A', '2018-01-01', 4500);

SELECT id, SUM(salary) OVER (PARTITION BY dep ORDER BY startdate 
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) from dataset;

Up to 6.1.x the result has been:

ID  SUM(salary) OVER
--  -----------------
 1               4500
 2              10000
 3              16500

Starting from 6.2.0 the result is:

ID  SUM(salary) OVER
--  -----------------
 3              16500
 2              10000
 1               4500

• Exasol uses a new default window frame for ordered analytic functions. This changes the results for analytic function computations with non-unique orders. Example:

CREATE OR REPLACE TABLE dataset2(id int, dep varchar(100), startdate timestamp, salary double);
INSERT INTO dataset2 VALUES(1,'B', '2019-01-01', 4000);
INSERT INTO dataset2 VALUES(2,'B', '2019-01-01', 5000);
INSERT INTO dataset2 VALUES(3,'B', '2019-01-03', 6000);

-- Ordered analytic function with default window:
SELECT id, SUM(salary) OVER (PARTITION BY dep ORDER BY startdate) from dataset2 ORDER BY startdate;

-- Up to 6.1.x this has been equivalent to:
SELECT id, SUM(salary) OVER (PARTITION BY dep ORDER BY startdate
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) from dataset2 ORDER BY startdate;

-- Starting from 6.2.0 and according to the SQL 2016 standard this is equivalent to:
SELECT id, SUM(salary) OVER (PARTITION BY dep ORDER BY startdate
    RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) from dataset2 ORDER BY startdate;

If the ordering is not deterministic due to duplicate values in the ordering columns, then the result of the analytic functions is undefined. Therefore, it is implementation dependent. The result may vary across subsequent executions of the same query.
For the provided example data this leads to the following possible results in 6.1 (depending on the data distribution):

1. Variant:
ID  SUM(salary) OVER
--  -----------------
 1               4000
 2               9000
 3              15000
2. Variant:
ID  SUM(salary) OVER
--  -----------------
 2               5000
 1               9000
 3              15000

In 6.2 the query with default window frame leads to identical results for rows with identical order values regardless of the data distribution. Thus, with respect to the missing ordering, the result in 6.2 is:

ID  SUM(salary) OVER
--  -----------------
 1               9000
 2               9000
 3              15000

It is not possible to recreate the old behavior even with the use of the window frame ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW in 6.2 due to varying data distribution.
For example:

SELECT id, SUM(salary) OVER (PARTITION BY dep ORDER BY startdate
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) from dataset2 ORDER BY startdate;

This query produces 4 different variants in 6.2.

1. Variant:
ID  SUM(salary) OVER
--  -----------------
 1               4000
 2               9000
 3              15000

2. Variant:
ID  SUM(salary) OVER
--  -----------------
 2               5000
 1               9000
 3              15000

3. Variant:
ID  SUM(salary) OVER
--  -----------------
 1               9000
 2               5000
 3              15000

4. Variant:
ID  SUM(salary) OVER
--  -----------------
 2               9000
 1               4000
 3              15000

Changed behavior