SQL and MySQL course Β· Module 10: Transactions and advanced SQL
PARTITION BY and window aggregates - rankings within groups
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
| Feature | GROUP BY | Window function (OVER) |
|---|---|---|
| Number of rows | collapses to one per group | keeps all |
| Details visible | no | yes |
| What for? | summaries | row-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. What is PARTITION BY in a window function used for?
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:
Tasks after this lesson
- QuestionThe PARTITION BY clause
- QuestionAggregates with OVER
- Coding taskRanking within categories
- Put in orderA window with PARTITION BY
- Coding taskAverage price in a category
- Click in orderROW_NUMBER in each category
- Put in orderA query with a window function
- QuestionWindow functions and rows
- Coding taskNumbering readers
- Put in orderA full loan transaction
- Click in orderThe full dostepne_ksiazki view definition
- Coding taskA CTE with a ranking
- Put in orderThe start of the ranking CTE
- QuestionWhen to use a transaction