SQL Window function vs Group by
A simplified explanation of SQL Window function with visuals
A window function performs an operation across a set of rows in a table, also known as partitions (subgroups). It’s called window precisely because the set of rows is called window.
The above is textbook definition. Let me take a stab and explain it in group by terms… Think of it as a recursive GROUP BY but in one single statement with the added functionality to create multiple subsets of the data. Not following? keep reading.
In this visualization, aggregation on the left is via GROUP BY where it combines and then hides individual rows being aggregated. Whereas, Window Function, on the right, has access to individual rows and can add attributes from those rows into the result set.
Window function is powerful
This is an another compelling visual that shows how efficient and powerful a window function is compared GROUP BY. Unlike GROUP BY, Window functions can calculate running totals and moving averages via ranking and value functionality.
