Window functions are one of SQL's most powerful features for performing calculations across related rows without collapsing them into groups like traditional GROUP BY clauses do. They allow you to compute running totals, rankings, moving averages, and other analytics while preserving individual row details.
The key concept is the OVER() clause, which defines the "window" or subset of rows for the calculation. Think of it as looking through a window at a specific view of your data. The PARTITION BY clause divides your data into separate groups, while ORDER BY determines the sequence within each partition.
For ranking functions like RANK(), DENSE_RANK(), and ROW_NUMBER(), the window function assigns positions based on specified criteria. RANK() gives tied values the same rank and skips subsequent positions, while DENSE_RANK() doesn't skip positions after ties.
Aggregate functions like AVG(), SUM(), and COUNT() can also be used as window functions. When used with OVER(), they calculate values for each row while maintaining the detail level. This is perfect for comparisons like "show each employee's salary alongside their department average."
Window functions execute after the WHERE clause but before ORDER BY in SQL's logical processing order. This means you often need subqueries or CTEs when filtering based on window function results. The window creates a calculated column that you can then use for further filtering or analysis.
1-- Example: Student grades with class statistics
2SELECT
3 student_name,
4 class,
5 grade,
6 RANK() OVER (PARTITION BY class ORDER BY grade DESC) as class_rank,
7 AVG(grade) OVER (PARTITION BY class) as class_average,
8 grade - AVG(grade) OVER (PARTITION BY class) as above_below_avg
9FROM student_grades
10ORDER BY class, class_rank;