Summary for Window Functions

Summarize the key insights you’ve explored about window functions in MySQL.

SQL allows us to read a table using TABLE, select rows with a suitable WHERE clause, or groups of rows based on common criteria with GROUP BY. By those means, we can get only so far, though. In particular, retrieving implicit information like computations on pairs of rows is difficult, if not impossible, depending on the problem statement. Hence, SQL introduced window functions that enable us to perform row-based queries seamlessly.

Frame specification

Row-based queries in SQL consist of a window function and a window specification. Four optional components constitute the window specification, namely

  • A name

  • A row partitioning

  • A row ordering within the partitions

  • A frame within the partitions

If present, the frame specification consists of the frame unit and the frame extent. For the former, two available units are ROWS and RANGE. ROWS defines the frame regarding row positions, while RANGE specifies the frame by rows within a range of values. Both units are further defined through the frame’s extent, which has a mandatory start point and an optional endpoint.

Row access inside a partition

For some window functions, a frame specification is not necessary and is even ignored. That is because these window functions already imply the partition as their frame. MySQL provides four window functions to access the rows inside a partition: CUME_DIST(), LAG(), LEAD(), ROW_NUMBER(), and NTILE().

Row access inside a frame

Window functions that grant access to the rows inside the frame can benefit from a frame specification, although unnecessary. The default frame spans the whole partition if that partition is not ordered. Otherwise, all preceding rows, the current row, and its peers are considered part of the frame for an ordered partition. MySQL offers three window functions that provide

  • Access to the first row (i.e., FIRST_VALUE())

  • Access to the last row (i.e., LAST_VALUE())

  • Access to an arbitrary row (i.e., NTH_VALUE())

Ranking inside a partition

Although the order of a partition already implies a definition of the first and last row, explicitly ranking rows inside a partition gives us more fine-grained control. This applies to peers resulting from ties between values about the partition order. To handle this case, MySQL offers three window functions that rank the rows within a partition, namely RANK(), DENSE_RANK(), and PERCENT_RANK(). The first two functions yield absolute ranks with and without gaps, respectively. The latter window function gives relative ranks representing the fraction of rows with smaller values regarding the partition order.

Get hands-on with 1400+ tech skills courses.