SQL and MySQL course Β· Module 10: Transactions and advanced SQL

PARTITION BY and window aggregates - rankings within groups

2 min read Β·
In this lesson4

You already know ROW_NUMBER() and RANK() computed over the whole table. Now we will climb to the very top of the Watchtower: we will learn to divide rows into groups and compute window functions separately within each group. That is what the PARTITION BY clause is for.

PARTITION BY - a separate window for each group

Imagine you want a ranking of books by price, but within each category separately. Without PARTITION BY we would number all the books together. With it - the counter resets at each new category:

SELECT tytul, kategoria_id, cena,
       ROW_NUMBER() OVER (PARTITION BY kategoria_id ORDER BY cena DESC) AS miejsce
FROM ksiazki;

In category 1 the most expensive book gets miejsce = 1, the next 2, and so on. When we move to category 2, the counter starts over. It is like a separate ranking on each shelf.

Order inside PARTITION BY

In the OVER (...) clause we first write PARTITION BY (what we split by) and then ORDER BY (how we order within the group):

OVER (PARTITION BY kategoria_id ORDER BY cena DESC)

Aggregates as window functions

The most interesting part is that ordinary aggregates - AVG, SUM, COUNT, MAX, MIN - can also work as window functions. You just add OVER (...). Then they do not collapse the rows but write the result alongside each one:

SELECT tytul, kategoria_id, cena,
       AVG(cena) OVER (PARTITION BY kategoria_id) AS srednia_kat
FROM ksiazki;

Each book keeps its row, but next to it the average price of its category appears. This way you can see at once whether a given book is more expensive or cheaper than the average on its shelf - without a separate GROUP BY query.

A window versus GROUP BY

FeatureGROUP BYWindow function (OVER)
Number of rowscollapses to one per groupkeeps all
Details visiblenoyes
What for?summariesrow-by-row comparisons

This is the Library's most powerful analytical tool. With PARTITION BY the Keeper sees both the whole and the detail at once - and nothing escapes notice.

Spotted a mistake in this lesson?

Check yourself

Answer the questions from this lesson. Pick an answer to see right away whether it is correct.

  1. 1. What is PARTITION BY in a window function used for?

  2. 2. What will AVG(cena) OVER (PARTITION BY kategoria_id) do in a query?

These are 2 of 4 questions for this lesson. Solve the rest in the game.

Hands-on tasks in the game

  • Code editor

    Write a query that retrieves tytul, kategoria_id and cena from the ksiazki table, and additionally adds a column aliased as miejsce containing the result of ROW_NUMBER() OVER (PARTITION BY kategoria_id ORDER BY cena DESC).

  • Horizontal ordering

    Arrange in one line the window clause with a split by category and ordering by price:

  • Code editor

    Write a query that retrieves tytul, kategoria_id and cena from the ksiazki table, and additionally adds a column aliased as srednia_kat containing the result of AVG(cena) OVER (PARTITION BY kategoria_id).

  • Click in order

    Click the elements in order to build a window function with a split and an ordering:

  • Vertical ordering

    Arrange the lines of a query with a window function in the correct order (top to bottom):

  • Code editor

    Write a query that retrieves imie and nazwisko from the czytelnicy table, and additionally adds a column aliased as numer containing the result of ROW_NUMBER() OVER (ORDER BY data_zapisu).

  • Vertical ordering

    Arrange the full loan transaction in the correct order (top to bottom):

  • Click in order

    Click the elements in order to build the dostepne_ksiazki view definition:

  • Code editor

    Write a CTE named ranking that selects tytul, cena and ROW_NUMBER() OVER (ORDER BY cena DESC) AS numer from the ksiazki table. Then, in the main query, retrieve all columns from ranking. Use the syntax WITH ranking AS (SELECT ...) SELECT * FROM ranking;

  • Horizontal ordering

    Arrange in one line the beginning of a CTE named ranking:

  1. QuestionThe PARTITION BY clause
  2. QuestionAggregates with OVER
  3. Coding taskRanking within categories
  4. Put in orderA window with PARTITION BY
  5. Coding taskAverage price in a category
  6. Click in orderROW_NUMBER in each category
  7. Put in orderA query with a window function
  8. QuestionWindow functions and rows
  9. Coding taskNumbering readers
  10. Put in orderA full loan transaction
  11. Click in orderThe full dostepne_ksiazki view definition
  12. Coding taskA CTE with a ranking
  13. Put in orderThe start of the ranking CTE
  14. QuestionWhen to use a transaction

Useful articles