Window functions

The OVER clause with PARTITION BY and ORDER BY, ROW_NUMBER, RANK, DENSE_RANK, LAG, LEAD and running totals with SUM … OVER: computing beside each row without collapsing it, unlike GROUP BY.

25 cards
6 topics
Free

Study this deck

The outline, topic by topic

Each topic is weighted the way the exam weighs it.

Concepts
Questions
Sort and link
The numbers
Scenarios
Understand

A few cards from the deck

Window function: what is it?
Show the answer
A function computed over a set of rows related to the current row, without grouping them: every row of the result stays and gets its value.
The OVER (…) clause sets it apart from an aggregate: SUM(montant) with GROUP BY returns one row per group, whereas SUM(montant) OVER (…) returns as many rows as the table.
The order amounts, by increasing id, are 40, 25, 60, 10, 90 and 15. What does the cumul column hold?

```sql
SELECT id, montant,
SUM(montant) OVER (ORDER BY id) AS cumul
FROM commande
ORDER BY id;
`
- 40, 65, 125, 135, 225, 240
- 240, 240, 240, 240, 240, 240
- 40, 25, 60, 10, 90, 15
- 240, 200, 175, 115, 105, 15
Show the answer
40, 65, 125, 135, 225, 240
With an ORDER BY inside OVER, the sum covers rows from the first up to the current one: a running total. Without ORDER BY (OVER ()), every row would get the total of 240.
GROUP BY … the rows to one per group; an aggregate followed by OVER … all the rows.
Show the answer
GROUP BY reduces the rows to one per group; an aggregate followed by OVER keeps all the rows.
With 12 sales in 3 regions, GROUP BY region returns 3 rows, SUM(montant) OVER (PARTITION BY region) returns 12. To compare each sale with its regional total, you need the second form.
PARTITION BY: what is it?
Show the answer
A subclause of OVER that splits the rows into independent partitions: the function starts afresh in each one.
RANK() OVER (PARTITION BY classe ORDER BY note DESC) ranks pupils within their class. Without it, the whole table forms a single partition.

Study this deck