Window Functions

You are viewing an old version of this article. View the current version here.

Window functions are developed for MariaDB 10.2

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 don't implement all things in SQL:2008.

MariaDB:

  • Supports ROWS and RANGE-type frames
    • All kinds of frame bounds are supported, including RANGE PRECEDING|FOLLOWING n frame bounds (unlike PostgreSQL)
    • Does not yet support DATE[TIME] datatype and arithmetic for RANGE-type frames (MDEV-9727)
  • Does not support GROUPS-type frames (I'm actually not aware of any product that does)
  • Does not support frame exclusion (nobody else does, 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)
  • Supported window functions are : ROW_NUMBER, RANK, DENSE_RANK, PERCENT_RANK, CUME_DIST, NTILE
    • (Note that Vicenitu is adding more all the time)
  • Aggregate functions that are supported as window functions: 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.
  • 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

Comments

Comments loading...
Content reproduced on this site is the property of its respective owners, and this content is not reviewed in advance by MariaDB. The views, information and opinions expressed by this content do not necessarily represent those of MariaDB or any other party.