Window Functions

MariaDB starting with 10.2

Window functions were first introduced in MariaDB 10.2.0.

Window functions perform calculations across a set of rows related to the current row

Title Description
Window Functions Overview Window functions perform calculations across a set of rows related to the current row.
AVG Returns the average value.
BIT_AND Bitwise AND.
BIT_OR Bitwise OR.
BIT_XOR Bitwise XOR.
COUNT Returns count of non-null values.
CUME_DIST Window function that returns the cumulative distribution of a given row.
DENSE_RANK Rank of a given row with identical values receiving the same result, no skipping.
FIRST_VALUE Returns the first result from an ordered set.
JSON_ARRAYAGG Returns a JSON array containing an element for each value in a given set of JSON or SQL values.
JSON_OBJECTAGG Returns a JSON object containing key-value pairs.
LAG Accesses data from a previous row in the same result set without the need for a self-join.
LAST_VALUE Returns the last value in a list or set of values.
LEAD Accesses data from a following row in the same result set without the need for a self-join.
MAX Returns the maximum value.
MEDIAN Window function that returns the median value of a range of values.
MIN Returns the minimum value.
NTH_VALUE Returns the value evaluated at the specified row number of the window frame.
NTILE Returns an integer indicating which group a given row falls into.
PERCENT_RANK Window function that returns the relative percent rank of a given row.
PERCENTILE_CONT Continuous percentile.
PERCENTILE_DISC Discrete percentile.
RANK Rank of a given row with identical values receiving the same result.
ROW_NUMBER Row number of a given row with identical values receiving a different result.
STD Population standard deviation.
STDDEV Population standard deviation.
STDDEV_POP Returns the population standard deviation.
STDDEV_SAMP Standard deviation.
SUM Sum total.
VAR_POP Population standard variance.
VAR_SAMP Returns the sample variance.
VARIANCE Population standard variance.
Aggregate Functions as Window Functions It is possible to use aggregate functions as window functions.
ColumnStore Window Functions Summary of window function use with the ColumnStore engine
Window Frames Some window functions operate on window frames.
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.

© 2021 MariaDB
Licensed under the Creative Commons Attribution 3.0 Unported License and the GNU Free Documentation License.
https://mariadb.com/kb/en/window-functions/