What are window functions, and how do they differ from GROUP BY?
A window function calculates something across a set of rows, and it leaves every row in the answer.
So how does that differ from a group by? A group by collapses the rows. Ten orders for one customer become one row, and you cannot see the individual orders any more. A window function calculates the same kind of thing and keeps all ten rows, adding the calculation beside each one.
CREATE TABLE orders (
id INTEGER PRIMARY KEY,
customer_id INTEGER NOT NULL,
amount DECIMAL(10, 2) NOT NULL,
placed_on TEXT NOT NULL
);
-- A group by. One row for each customer.
SELECT customer_id, COUNT(*) AS order_count, SUM(amount) AS total
FROM orders
GROUP BY customer_id;
-- A window function. Every row, with the total beside it.
SELECT id, customer_id, amount, SUM(amount) OVER (PARTITION BY customer_id) AS customer_total
FROM orders
ORDER BY customer_id, id;
So the two parts of a window function are the partition and the order. The word PARTITION BY says which rows are grouped together for the calculation, and it is the same idea as the group by. The word ORDER BY inside the window says the order that the rows are in for the calculation, and that is the part that a group by cannot do at all.
So the rule to remember is this. A group by answers "how many in each group". A window function answers "and what is the total for my group", without losing the rows.
The next two questions in this topic put it to work and look at the frame.