WL#9236: Add first batch of SQL window functions

Affects: Server-8.0   —   Status: Complete

Umbrella WL for adding SQL window functions to MySQL.

WHAT
====

Allow use of SQL window functions in MySQL. What are they? Cf. this good 
description culled from the PostgreSQL docs:

"A window function performs a calculation across a set of table rows that are 
somehow related to the current row. This is comparable to the type of calculation 
that can be done with an aggregate function. But unlike regular aggregate 
functions, use of a window function does not cause rows to become grouped into a 
single output row — the rows retain their separate identities. Behind the scenes, 
the window function is able to access more than just the current row of the query 
result."

This is a large feature set and we do not expect that all will be implemented,
at least not in the first release of the feature set.

WHAT THIS IS NOT
================

Addition of new (i.e. missing) aggregate functions: only the already existing
aggregate functions for MySQL will be considered for window functions.
SQL 2003 introduced more aggregate functions that could also be used as
window function.

WHY
===

Support for window functions (a.k.a. analytic functions) is a frequent user 
request. Window functions have long been part of standard SQL (SQL 2003).


BUT CAN'T I DO THE SAME WITH EXISTING MEANS?
============================================

Yes, but performance would be abysmal and expressing the same semantics would lead
to hard to understand queries.

DO OTHERS HAVE WINDOW FUNCTIONS?
================================

yes: Oracle, PostgreSQL, Maria DB, SQL server

REFERENCES
==========

[SQL 2011] - "SQL 2011 ISO/IEC 9075-2:2011(E) JTC 1/SC 32/WG 3"
[SQL 2016] - "SQL 2016 ISO/IEC 9075-2:2016(E) JTC 1/SC 32/WG 3"

Attached file window-functions-intro.pdf as presented to optimizer team
Feb 2016.

An overview which places window function in the context of OLAP capabilities for 
SQL:

http://wwwlgis.informatik.uni-
kl.de/cms/fileadmin/courses/SS2008/NEDM/RDDM.Chapter.06.Windows_and_Query_Function
s_in_SQL.pdf

The PostgreSQL documentation for window functions is also quite readable.

Git branch mysql-trunk-wl9236 contains the prototyping code.

SQL FEATURES
============
Feature T611, "Elementary OLAP operations"
Feature T612, “Advanced OLAP operations”
Feature T614, "NTILE function"
Feature T615, "LEAD and LAG functions"
Feature T616, "Null treatment option for LEAD and LAG functions"
Feature T617, "FIRST_VALUE and LAST_VALUE functions"
Feature T618, "NTH_VALUE function"
Feature T619, "Nested window functions"
Feature T620, "WINDOW clause: GROUPS option"

Main sections discussing window functions in [SQL 2016]:

  4.15.15 Windowed tables
  4.16.1 Introduction to data analysis operations
  4.16.3 Window functions
  4.16.4 Aggregate functions
  6.10 
[ 6.11  ]
  7.4 
  7.15 
  10.9 

but windows are also treated elsewhere in many contexts.

SEE ALSO
========
Old WL#2266 OLAP.
WL#9603 Add remaining non-aggregate window functions
WL#9727 Additional aggregate window functions
WL#10431 Support operator queries with windowing functions

EXAMPLE OF WINDOW FUNCTION
==========================
CREATE TABLE employees(name VARCHAR(20), department INT, salary LONG);
CREATE TABLE departments(id INT, name VARCHAR(20));

INSERT INTO departments VALUES (1, 'Cartoons'), (2, 'Fiction');
INSERT INTO employees VALUES ('donald', 1, 20000),
                             ('mickey', 1, 15000),
                             ('wile e. coyote', 1, 30000),
                             ('swann', 2, 40000),
                             ('ishmael', 2, 40000),
                             ('ahab', 2, 50000);

SELECT e.name, d.name, salary,
       RANK() OVER (PARTITION BY d.id ORDER BY salary DESC) rank_in_dep
       FROM employees e, departments d
       WHERE e.department = d.id
       ORDER BY e.name;


+----------------+----------+--------+-------------+
| name           | name     | salary | rank_in_dep |
+----------------+----------+--------+-------------+
| ahab           | Fiction  | 50000  |           1 |
| donald         | Cartoons | 20000  |           2 |
| ishmael        | Fiction  | 40000  |           2 |
| mickey         | Cartoons | 15000  |           3 |
| swann          | Fiction  | 40000  |           2 |
| wile e. coyote | Cartoons | 30000  |           1 |
+----------------+----------+--------+-------------+
6 rows in set (0.00 sec)