PARTITION BY and window aggregates - rankings within groups

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:

1SELECT tytul, kategoria_id, cena,
2       ROW_NUMBER() OVER (PARTITION BY kategoria_id ORDER BY cena DESC) AS miejsce
3FROM 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):

1OVER (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:

1SELECT tytul, kategoria_id, cena,
2       AVG(cena) OVER (PARTITION BY kategoria_id) AS srednia_kat
3FROM 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.