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)
- All kinds of frame bounds are supported, including
- 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
orNULLS 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.
- Aggregate functions with
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
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.