Window functions

Lesson 17 of 21 · Part 4 · Stronger queries · about 20 minutes

GROUP BY squeezes each group into one row. For example, how many products each category has:

1SELECT category, COUNT(*) AS products2FROM products3GROUP BY category4ORDER BY category;

Ten products became 4 rows, and each product's own name and price are gone. Sometimes we want both: every product still on its own row, with its place in the whole table or its category's average price written next to it. That's what window functions do.

OVER: no rows disappear

Put OVER () after an aggregate function and it no longer squeezes the rows into one. Instead, it writes the value it works out next to every row:

1SELECT name, price,2    ROUND(AVG(price) OVER (), 2) AS avg_price,3    price - ROUND(AVG(price) OVER (), 2) AS diff4FROM products5ORDER BY id;

Still 10 rows. Nothing inside OVER () means "use all the rows together": every row sees the average price of all products, 19.08, so you can tell at a glance which ones cost more than average, and by how much.

The range inside the parentheses of OVER is called the window: the rows a row can "see" while its value is worked out.

ROW_NUMBER: giving places

ROW_NUMBER() gives each row a number: 1, 2, 3… The order to number them in goes inside OVER (ORDER BY …):

1SELECT name, price,2    ROW_NUMBER() OVER (ORDER BY price DESC) AS n3FROM products4ORDER BY n;

The most expensive one is number 1. Notice the two ORDER BYs: the one inside OVER decides how rows are numbered, the last one decides the order the result is shown in. They can differ, say numbering by price but showing by id.

Window functions are worked out after WHERE, at SELECT time: the unwanted rows go first, and what's left gets numbered.

1SELECT name,2    ROW_NUMBER() OVER (ORDER BY price DESC) AS n3FROM products4WHERE category = 'Electronics'5ORDER BY n;

Ties: RANK and DENSE_RANK

What if two rows have the same value? ROW_NUMBER() still gives them different numbers, and which comes first is anyone's guess. To give equal values the same place, use RANK() or DENSE_RANK(). Here is how many orders each customer placed:

1WITH counts AS (2    SELECT customer_id, COUNT(*) AS orders3    FROM orders4    GROUP BY customer_id5)6SELECT customer_id, orders,7    RANK() OVER (ORDER BY orders DESC) AS place,8    DENSE_RANK() OVER (ORDER BY orders DESC) AS dense_place9FROM counts10ORDER BY orders DESC, customer_id;

Three customers placed 2 orders and share 2nd place. The difference is the next one: RANK() skips the places they took up, so the next is 5th; DENSE_RANK() doesn't skip, so the next is 3rd.

Note: You can't call the alias rank. In MySQL 8 RANK is a reserved word, and AS rank gives the syntax error ERROR 1064. Pick another name, like place.

PARTITION BY: each group on its own

OVER (PARTITION BY category) splits the rows into blocks by category, and each block is worked out on its own. Below, next to each product is the average price of its own category:

1SELECT name, category, price,2    ROUND(AVG(price) OVER (PARTITION BY category), 2) AS category_avg3FROM products4ORDER BY category, price;

Compare it with the GROUP BY at the top: the groups are the same, but there are still 10 rows.

PARTITION BY and ORDER BY can go together: a ranking inside each category, with every block starting again from 1:

1SELECT category, name, price,2    ROW_NUMBER() OVER (PARTITION BY category ORDER BY price DESC) AS n3FROM products4ORDER BY category, n;

The top of each group

For the most expensive product in each category, keep only the rows where n = 1. Putting it straight into WHERE doesn't work:

1SELECT category, name, price2FROM products3WHERE ROW_NUMBER() OVER (PARTITION BY category ORDER BY price DESC) = 1;

ERROR 3593 (HY000): You cannot use the window function 'row_number' in this context.'

WHERE runs before window functions (lesson 10 showed the order things run in), so there are no places yet. The way out: work the places out first with WITH, then filter outside:

1WITH ranked AS (2    SELECT category, name, price,3        ROW_NUMBER() OVER (PARTITION BY category ORDER BY price DESC) AS n4    FROM products5)6SELECT category, name, price7FROM ranked8WHERE n = 19ORDER BY category;

"Number first, then take number 1" comes up all the time: each customer's first order, each month's best-selling product. They're all found this way.

Running totals: SUM() OVER

SUM() with OVER (ORDER BY …) adds up from the first row to this one: a running total. Below is each order's amount (cancelled ones left out), and how much was sold up to and including that order:

1WITH totals AS (2    SELECT o.id, o.order_date, SUM(oi.quantity * oi.unit_price) AS total3    FROM orders AS o4    JOIN order_items AS oi ON oi.order_id = o.id5    WHERE o.status <> 'cancelled'6    GROUP BY o.id, o.order_date7)8SELECT id, order_date, total,9    SUM(total) OVER (ORDER BY order_date) AS running_total10FROM totals11ORDER BY order_date;

The running_total on the last row is the total of all orders. Take the ORDER BY out of OVER, and every row shows that total.

Window functions came in MySQL 8 (the old 5.7 doesn't have them); PostgreSQL, SQL Server and SQLite have them too, written the same way.

Practice

Write the query, then press Check. A hint costs nothing; the answer is there when you need it. Don't forget the ORDER BY inside OVER.

1SELECT name, salary2FROM employees;

1SELECT category, name, price2FROM products3ORDER BY category;

1SELECT customer_id, order_date2FROM orders;

What you learned

• A window function writes a worked-out value next to every row, and the number of rows stays the same; GROUP BY squeezes a group into one row.
• OVER () uses all the rows together; OVER (PARTITION BY column) works each group out on its own.
• ROW_NUMBER() numbers rows; RANK() gives ties the same place and skips after them; DENSE_RANK() gives ties the same place without skipping.
• The ORDER BY inside OVER decides the numbering, the last ORDER BY decides how the result is shown; write both.
• A window function can't go in WHERE: work it out first with WITH, then filter outside.
• SUM() OVER (ORDER BY …) keeps a running total.