Dev Learning Lab

The labs on this site need JavaScript to run. The same content is written out below.

Learn › Database › SQL

Window functions

How do you add a rank and a running total without reducing the rows?

GROUP BY combines many rows into one row to compute a total. A window function leaves the rows as they are and adds one value, such as a rank or a running total, next to each row.

Checked against real execution · The SQL of all 24 combinations was run on MySQL 8.4, and the number of rows and the value added to each row were checked against the screen

Concepts in this lab

Window function
This is a function that adds to each row a value computed by looking at several rows related to that row, without combining the rows. You write OVER (...) after the function to say which rows are laid out in which order for the calculation. Unlike GROUP BY, which reduces each group to one row of the result, the number of result rows stays the same, so you can see the original value of each row and its rank or running total on one line. In MySQL, window functions are available from version 8.0. They are allowed only in the select list and in ORDER BY, so to filter on the result of a window function, you wrap the query in a subquery.
ROW_NUMBER, RANK, DENSE_RANK
These are window functions that give rows a number or a rank in the order of the ORDER BY inside OVER. The difference among the three is how they handle rows with the same sort value (ties, which the MySQL documentation calls peers). ROW_NUMBER gives different numbers even to peers. RANK gives peers the same rank and then skips as many ranks (1, 1, 3), and DENSE_RANK gives peers the same rank without skipping (1, 1, 2). With ROW_NUMBER, which of the tied rows gets the lower number is not defined, so if the result must always be the same, add a column whose values are unique to the sort order.
PARTITION BY
This is a clause you write inside the OVER of a window function to divide the rows into groups that have the same value in the columns you name. Each group is called a partition, and the numbers, ranks, and running totals are calculated again from the beginning for each partition. It looks similar to GROUP BY, but the difference is that it does not combine rows. If you do not write PARTITION BY, the whole result is one partition.
LAG and LEAD
These are window functions that fetch the value of a row before or after the current row in an ordered line of rows. LAG fetches the value of the previous row, and LEAD fetches the value of the next row. They let you compare neighboring rows, such as the difference between yesterday and today, without joining the same table one more time. For the first row of a partition (LAG) or the last row (LEAD), where there is no row to fetch, the value is NULL.
GROUP BY, WHERE, HAVING
GROUP BY groups rows that have the same value in the specified columns and computes an aggregate (such as a sum or a count) for each group. WHERE filters individual rows before they are grouped, and HAVING filters groups after grouping. So put conditions on aggregate results in HAVING and all other conditions in WHERE.

Try these

Running with the default settings

  1. FROM score: The 6 rows of the score table are read. Each row is one person's score. Two people have 90 points (Alice and Bob), and two have 70 points (Carol and Eve).
  2. OVER (ORDER BY pts DESC): This decides how the rows are laid out when each value is computed. All 6 rows are lined up from the highest score. The rows are only put in a line. They are not combined.
  3. Alice (90 pts): RANK() adds 1 to the number of rows with a higher score. No row has a higher score than this one, so the rank is 1.
  4. Bob (90 pts): No row has a higher score than this one, so the rank is 1. The score ties with the previous row, so the rank is the same.
  5. Dave (80 pts): The number of rows with a higher score is 2, so the rank is 3. The two tied rows before this one took two places, so rank 2 is skipped.
  6. Carol (70 pts): The number of rows with a higher score is 3, so the rank is 4.
  7. Eve (70 pts): The number of rows with a higher score is 3, so the rank is 4. The score ties with the previous row, so the rank is the same.
  8. Frank (60 pts): The number of rows with a higher score is 5, so the rank is 6. The two tied rows before this one took two places, so rank 5 is skipped.
  9. ORDER BY pts DESC: The result is returned. The 6 rows are still 6 rows, and each row now has one value in the rnk column.

Result of this run

Code

-- score table (6 rows)
-- name  team  pts
-- Alice   A     90
-- Bob   A     90
-- Carol   A     70
-- Dave   B     80
-- Eve   B     70
-- Frank   B     60

SELECT name, team, pts,
       RANK() OVER (ORDER BY pts DESC) AS rnk
  FROM score
 ORDER BY pts DESC;

Assumptions of this model

For all 24 combinations, the same SQL statement as the one on the screen was run on MySQL 8.4 on the PC used to write this lab, and the number of result rows and the value added to each row were checked against the screen. The values with ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW in the running total were checked the same way. SQL does not define which of the tied rows is placed first (see the ROW_NUMBER description in the MySQL documentation), so the order of the two tied people on the screen is just one example, and the check was made on the team, the score, and the added value, not on the names. Window functions are available from MySQL 8.0. The way the step sentences calculate (for example, adding 1 to the number of rows with a higher score) is a plain-words version of each function's definition. A real database sorts the rows and then makes one pass over them to compute the values.

Previous lab: Subqueries · Next lab: Offset and keyset pagination

Concepts in this lab

What this lab shows

Settings

Code


      
    


    

      
Diagram

      

      

    


    

      

      

      

    


  


  

    

      
Run comparison

      

      

    

    

      
Try these