Window Functions

Introduction

TODO: introduction and explanation.

Scope

Window functions were introduced in SQL:2003, and their definition was expanded in subsequent versions of the standard. The last expansion was in the latest version of the standard, SQL:2011.

Most database products support a subset of the standard, they implement some functions defined as late as in SQL:2011, and at the same time leave some parts of SQL:2008 unimplemented.

MariaDB:

• Supports ROWS and RANGE-type frames
  • All kinds of frame bounds are supported, including RANGE PRECEDING|FOLLOWING n frame bounds (unlike PostgreSQL or MS SQL Server)
  • Does not yet support DATE[TIME] datatype and arithmetic for RANGE-type frames (MDEV-9727)
• Does not support GROUPS-type frames (it seems that no popular database supports it, either)

• Does not support frame exclusion (no other database seems to support it, either) (MDEV-9724)
• Does not support explicit NULLS FIRST or NULLS LAST.
• Does not support nested navigation in window functions (this is VALUE_OF(expr AT row_marker [, default_value) syntax)

• The following window functions are supported:
  • "Streamable" window functions: ROW_NUMBER, RANK, DENSE_RANK,
  • Window functions that can be streamed once the number of rows in partition is known: PERCENT_RANK, CUME_DIST, NTILE

• Aggregate functions that are currently supported as window functions are: COUNT, SUM, AVG, BIT_OR, BIT_AND, BIT_XOR.
• Aggregate functions with DISTINCT specifier (e.g. COUNT( DISTINCT x)) are not supported as window functions.

Links

• MDEV-6115 is the main jira task for window functions development. Other tasks are are attached as sub-tasks
• bb-10.2-mdev9543 is the feature tree for window functions
• Testcases are in mysql-test/t/win*.test