MySQL Window Functions

MySQL window functions provide computing power across the group associated with the current row. Window functions are different from aggregate functions, which perform calculations on each group and return a row for each group, window functions do not output each group to a row and combine the calculation results of each group into each row in this group.

All window functions rely on OVER clauses, which can be used to group all rows and sort rows within groups. The syntax of calling a window function is as follows:

window_function(args)
OVER (
  [PARTITION BY partition_column_list]
  [ORDER BY order_column_list]
)

In this Syntax:

  • The window_function(args) is the window function that needs to be called. In addition to specific window functions, you can also use aggregate functions here.
  • The PARTITION BY specifies the column name that needs to be partitioned. It is optional. Defaults to one partition for the entire result set.
  • The ORDER BY specifies the sort column name. It is optional.

MySQL provides many window functions that can be used to obtain information such as rank, row number, cumulative distribution, bucket rank, etc.

  1. CUME_DIST

    The MySQL CUME_DIST() function returns the cumulative distribution of the current row.

  2. DENSE_RANK

    The MySQL DENSE_RANK() function returns the rank within the partition in which the current row is located, starting at 1, with no gap.

  3. FIRST_VALUE

    The MySQL FIRST_VALUE() function returns the evaluated value from the first row of the window frame associated with the current row.

  4. LAG

    The MySQL LAG() function returns the value from the specified row before the current row in the partition where the current row is located.

  5. LAST_VALUE

    The MySQL LAST_VALUE() function returns the evaluated value from the last row of the window frame associated with the current row.

  6. LEAD

    The MySQL LEAD() function returns the value from the specified row after the current row in the partition where the current row is located.

  7. NTH_VALUE

    The MySQL NTH_VALUE() function returns the evaluated value from the specified row of the window frame associated with the current row.

  8. NTILE

    The MySQL NTILE() function divides all rows in the partition into the specified number of buckets as evenly as possible, and returns the rank of the bucket where the current row is located.

  9. PERCENT_RANK

    The MySQL PERCENT_RANK() function returns the relative rank within the partition where the current row is located, that is (rank() - 1) / (number of rows partition - 1).

  10. RANK

    The MySQL RANK() function returns the rank within the partition in which the current row is located, starting at 1, with gaps.

  11. ROW_NUMBER

    The MySQL ROW_NUMBER() function returns the row number of the current row within the partition where the current row is located, starting from 1.