Aggregate Functions Collapse Rows and Window Functions Keep Them
An aggregate with GROUP BY returns one row per group and throws the detail away; a window function computes over a group of related rows but returns every original row alongside its answer. Choosing the wrong one is how people end up joining a grouped query back to the detail table, paying for the same scan twice. Window functions cost a sort into window order, while grouping costs a sort or a hash, so the choice shows up as different steps in your plan.
Questions this Concept answers
- Why does a window function often replace a grouped subquery joined back to the detail rows?
Aggregate Functions vs Window Functions: A Comparison
Article: Aggregate Functions vs Window Functions: A Comparison