What a Window Function Does
A normal aggregate like SUM with GROUP BY collapses many rows into one. A window function computes a value across a set of related rows called a window, yet keeps every original row in the output. You write it with the OVER clause.
The Three Parts
- PARTITION BY splits rows into groups, like one window per customer.
- ORDER BY sets the order inside each partition, which matters for ranking and running totals.
- The frame picks which rows count, such as all rows from the start up to the current one.
Common Functions
- ROW NUMBER gives each row a unique position.
- RANK and DENSE RANK handle ties differently.
- LAG and LEAD read a value from a neighbor row.
- A running total is just SUM OVER an ordered partition.
Key idea
Window functions let you rank rows and compute running totals while keeping every individual row, which GROUP BY cannot do.