AI Hints
Homeโ€บSQL Tutorialโ€บWindow Functions
SQL ยท Lesson 62 of 126

Window Functions

Numbering rows without grouping.

โฑ 2 min readโ–ถ Video at 1:28:54๐ŸŽ“ Free
Watch this lesson in the SQL courseWatch on YouTube โ†—Full SQL course

Group by is great, but it has a cost. It squashes rows together. Ask for the average price per genre, and you lose the individual books. But what if Helen wants to see every book, and its genre's average, side by side? That's what window functions do. They calculate across a group of rows, without squashing them.

Window Functions
From the video: Window Functions
  • GROUP BY squashes rows
  • Keep every row
  • And still see the group's number

What GROUP BY gives

Here's the group by version. Run it. One row per genre. The individual books are gone.

query.sqlsql
SELECT genre, ROUND(AVG(price), 2) AS genre_avg
FROM books
GROUP BY genre
LIMIT 3;
Output
โ”Œโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ฌโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”
โ”‚  genre   โ”‚ genre_avg โ”‚
โ”œโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ผโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ค
โ”‚ Business โ”‚ 21.99     โ”‚
โ”‚ Cooking  โ”‚ 24.82     โ”‚
โ”‚ Fantasy  โ”‚ 17.82     โ”‚
โ””โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ดโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”˜

OVER and PARTITION BY

Now the window version. The same average, followed by over. Inside the brackets, partition by genre means, work out the average separately for each genre. But every book keeps its own row. Run it. Each cooking book shows the cooking average. Each technology book shows the technology average.

query.sqlsql
SELECT title,
       genre,
       price,
       ROUND(AVG(price) OVER (PARTITION BY genre), 2) AS genre_avg
FROM books
WHERE genre IN ('Cooking', 'Technology');
Output
โ”Œโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ฌโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ฌโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ฌโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”
โ”‚          title           โ”‚   genre    โ”‚ price โ”‚ genre_avg โ”‚
โ”œโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ผโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ผโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ผโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ค
โ”‚ The Quiet Kitchen        โ”‚ Cooking    โ”‚ 24.99 โ”‚ 24.82     โ”‚
โ”‚ Spice Roads              โ”‚ Cooking    โ”‚ 27.99 โ”‚ 24.82     โ”‚
โ”‚ One Pan Dinners          โ”‚ Cooking    โ”‚ 21.49 โ”‚ 24.82     โ”‚
โ”‚ Python for Curious Minds โ”‚ Technology โ”‚ 34.99 โ”‚ 32.32     โ”‚
โ”‚ SQL in a Weekend         โ”‚ Technology โ”‚ 29.99 โ”‚ 32.32     โ”‚
โ”‚ Data for Everyone        โ”‚ Technology โ”‚ 31.99 โ”‚ 32.32     โ”‚
โ””โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ดโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ดโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ดโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”˜

Compare each row to its group

Now the useful part. Because each row still has its own price, we can compare it to the group. Price, minus the genre average. Run it. The Silent Orchard is one eighty eight above the mystery average. Murder at Mill Lane is two dollars thirteen below.

query.sqlsql
SELECT title,
       price,
       ROUND(price - AVG(price) OVER (PARTITION BY genre), 2) AS vs_genre
FROM books
WHERE genre = 'Mystery';
Output
โ”Œโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ฌโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ฌโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”
โ”‚        title        โ”‚ price โ”‚ vs_genre โ”‚
โ”œโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ผโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ผโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ค
โ”‚ The Silent Orchard  โ”‚ 14.99 โ”‚ 1.88     โ”‚
โ”‚ The Last Lighthouse โ”‚ 13.99 โ”‚ 0.88     โ”‚
โ”‚ Murder at Mill Lane โ”‚ 10.99 โ”‚ -2.13    โ”‚
โ”‚ The Painted Door    โ”‚ 12.49 โ”‚ -0.63    โ”‚
โ””โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ดโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ดโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”˜

Window versus GROUP BY

Think of a window as a frame you slide over the rows. Partition by decides which rows share a frame. The function looks through the frame, but the rows themselves stay where they are.

Summary

A window function adds over, and partition by sets the groups. Every row stays. Next, numbering rows.

Key takeaways
  • function OVER (...)
  • PARTITION BY = the groups
  • Rows are not squashed
Next up: ROW_NUMBER โ€” numbering rows in order