Introduction to Window Functions
You'll learn what window functions are, how they differ from GROUP BY, and when to use them for row-level calculations.
What Are Window Functions?
Window functions perform calculations across a set of rows (a "window") that are related to the current row, without collapsing those rows into a single output row. Think of them as "looking through a window" at nearby rows while keeping your current row intact.
Unlike GROUP BY, which collapses multiple rows into summary rows, window functions return one result for each input row while still allowing you to reference aggregate or analytical values across related rows.
Window Functions vs GROUP BY
With GROUP BY, if you aggregate 100 rows into 10 groups, you get 10 output rows—the original detail is lost.
With window functions, you keep all 100 rows and add calculated columns that reference aggregates or rankings across those rows. You can see both the individual row and its context within the larger dataset simultaneously.
When to Use Window Functions
Use window functions when you need:
- Running totals or moving averages while preserving individual transactions
- Rankings (1st, 2nd, 3rd) alongside the actual data values
- Comparisons between a row and an aggregate (e.g., "How does this sale compare to the average?")
- Row-level analytics that require context from other rows without losing detail
For example: showing each employee's salary and their department's average salary in the same result row—impossible with just GROUP BY, but natural with window functions.
Key Takeaway: Window functions let you perform aggregate-like calculations and rankings while maintaining the original row-level detail, making them essential for analytical queries that need both granular data and contextual insights.