Sql - OVER and PARTITION BY
Code Life
By Pierre Marsaa
A column that sees the others
With what you learnt, comparing each Codémon with the average level takes a subquery — a GROUP BY alone would not do, since it collapses the rows into one per group.
A window function does it in one line: it keeps every row and adds a column computed from the other rows. Start with the average itself:
↗️
Loading editor...
↶
↷
AVG(level) OVER () is the usual AVG, followed by OVER (). The parentheses after OVER describe the window — the set of rows the function looks at — and empty parentheses mean all the rows of the result.
Nothing is grouped: 8 Codémons in, 8 rows out, each one carrying the average, 11.5. And since that average now sits on every row, the comparison is a plain subtraction:
↗️
Loading editor...
↶
↷
Aquachu is 3.5 levels above the average, Plantachu 3.5 below — one query, no subquery, every row kept.
Here the window was the whole table. The next step is to give each trainer a window of their own.
Window vs GROUP BY
GROUP BY answers "one number per group" and loses the rows. A window function answers "each row, plus a number computed from its neighbours" — and keeps them all. Every aggregate you know (COUNT, SUM, AVG, MIN, MAX) can be followed by OVER.
Verify