Window Functions
The usage of window functions in TiDB is similar to that in MySQL 8.0. For details, see MySQL Window Functions.
Because window functions reserve additional words in the parser, TiDB provides an option to disable window functions. If you receive errors parsing SQL statements after upgrading, try setting tidb_enable_window_function=0.
The window functions listed here can be pushed down to TiFlash.
Except for GROUP_CONCAT() and APPROX_PERCENTILE(), TiDB supports all GROUP BY aggregate functions. In addition, TiDB supports the following window functions:
| Function name | Feature description |
|---|---|
CUME_DIST() | Returns the cumulative distribution of a value within a group of values. |
DENSE_RANK() | Returns the rank of the current row within the partition, and the rank is without gaps. |
FIRST_VALUE() | Returns the expression value of the first row in the current window. |
LAG() | Returns the expression value from the row that precedes the current row by N rows within the partition. |
LAST_VALUE() | Returns the expression value of the last row in the current window. |
LEAD() | Returns the expression value from the row that follows the current row by N rows within the partition. |
NTH_VALUE() | Returns the expression value from the N-th row of the current window. |
NTILE() | Divides a partition into N buckets, assigns the bucket number to each row in the partition, and returns the bucket number of the current row within the partition. |
PERCENT_RANK() | Returns the percentage of partition values that are less than the value in the current row. |
RANK() | Returns the rank of the current row within the partition. The rank may be with gaps. |
ROW_NUMBER() | Returns the number of the current row in the partition. |