Amey-Thakur Window functions
Window functions answer questions that otherwise force a self-join or a subquery per row: what rank is this, what was the previous value, how much of the total is this. They compute across a set of rows while leaving every row in the output, which is what separates them from aggregation.
Method
- Choose the frame deliberately. PARTITION BY defines the group and ORDER BY defines the sequence within it. Most surprising results come from a missing partition or an order that is not deterministic.
- Know which ranking you mean. ROW_NUMBER gives a unique sequence, RANK leaves gaps after ties, and DENSE_RANK does not. Picking one without deciding how ties should behave produces defensible-looking nonsense.
- Reach for LAG and LEAD instead of self-joins. Comparing a row to the previous one is a window, not a join, and it stays correct when rows are missing or duplicated.
- Understand the default frame for running totals. With an ORDER BY present, aggregates default to the rows from the start of the partition to the current row, which is what makes a running sum work and what surprises people expecting the whole partition.
- Filter after the window, not inside it. WHERE runs before the window, so filtering on a rank means computing it in a subquery or CTE first and filtering outside (see common-table-expressions).
- Break ties explicitly. An ORDER BY that does not uniquely determine order gives results that can differ between runs, which is the hardest class of bug to reproduce.
Boundaries
- Windows add per-row context; they do not reduce rows. Use GROUP BY when the output really should be one row per group (see sql-aggregation).
- Large partitions can be expensive because they may require sorting the whole set (see sql-optimization).
- Support and syntax vary between engines, particularly around frames and named windows.