When should I use a window function instead of GROUP BY?
Use a window function when you need the detail rows and the calculation. Use the group by when the calculation is all you want.
CREATE TABLE orders (
id INTEGER PRIMARY KEY,
customer_id INTEGER NOT NULL,
amount DECIMAL(10, 2) NOT NULL
);
So what is the difference in the answer? The group by gives you one row for each customer, with the count and the sum. The window function gives you every order, with the count and the sum beside it.
what you want what you use
------------------------------------- ---------------------------------
the total for each customer GROUP BY
every order with the total beside it a window function
both at once a window function
a running total a window function
the row before this one LAG or LEAD
So the rule of thumb is this. If you only ever look at the totals, the group by is simpler and it is faster. If a person is going to read the rows one by one, and they need to see the total next to each one, then the window function is the right tool.
There is one more thing to know. A group by and a window function can be used in the same query, but you have to be careful, because the window function then works on the grouped rows and not on the original ones. That is the subject of the next question.
The next question in this topic shows the same answer written both ways.