Window Functions

50 min

Window functions perform calculations across a set of table rows related to the current row, without collapsing rows like aggregate functions do. Window functions enable you to compute values based on a 'window' of rows around the current row. They're powerful for ranking, running totals, moving averages, and comparing rows. Understanding window functions enables you to write sophisticated analytical queries. Window functions are available in modern SQL databases (PostgreSQL, SQL Server, MySQL 8.0+, etc.).

The `OVER()` clause defines the window of rows for the function to operate on. OVER() can specify partitioning (PARTITION BY), ordering (ORDER BY), and frame specification (ROWS/RANGE BETWEEN). Partitioning divides rows into groups, ordering determines row order within partitions, and framing defines which rows are included in calculations. Understanding OVER() is essential for using window functions effectively.

Common ranking window functions include `ROW_NUMBER()`, `RANK()`, and `DENSE_RANK()`. ROW_NUMBER() assigns unique sequential numbers to rows. RANK() assigns ranks with gaps for ties. DENSE_RANK() assigns ranks without gaps for ties. Understanding ranking functions enables you to assign positions to rows. These functions require ORDER BY in the OVER() clause.

`LAG()` and `LEAD()` access data from previous or subsequent rows, enabling comparisons between rows. LAG() accesses the previous row, LEAD() accesses the next row. These functions are useful for calculating differences, trends, and sequential comparisons. Understanding LAG/LEAD enables you to analyze row-to-row relationships. You can specify offset (how many rows back/forward) and default values.

Aggregate window functions (SUM, AVG, COUNT, etc.) can be used as window functions with OVER(). Unlike regular aggregates, window aggregates don't collapse rows—they compute values for each row based on the window. Window aggregates enable running totals, moving averages, and cumulative calculations. Understanding window aggregates enables you to compute aggregations while preserving row detail.

Best practices include using window functions for analytical queries, understanding frame specifications (ROWS vs RANGE), using PARTITION BY to create logical groups, and recognizing that window functions execute after WHERE, GROUP BY, and HAVING. Understanding window functions enables you to write powerful analytical queries that would be difficult with regular SQL.

Key Concepts

Window functions perform calculations across rows without collapsing them.
OVER() clause defines the window (partitioning, ordering, framing).
ROW_NUMBER(), RANK(), DENSE_RANK() assign ranks to rows.
LAG() and LEAD() access previous/next row data.
Window functions preserve all rows unlike aggregate functions.

Learning Objectives

Master

Using window functions with OVER() clause
Ranking rows with ROW_NUMBER(), RANK(), DENSE_RANK()
Accessing previous/next rows with LAG() and LEAD()
Using aggregate functions as window functions

Develop

Analytical query thinking
Understanding window function concepts
Designing sophisticated analytical queries

Tips

Use PARTITION BY to create logical groups for window calculations.
Use ORDER BY in OVER() for ranking and LAG/LEAD functions.
Understand frame specifications (ROWS vs RANGE) for precise control.
Window functions execute after WHERE, GROUP BY, HAVING.

Common Pitfalls

Not using ORDER BY in OVER() when required (ranking, LAG/LEAD).
Confusing window functions with aggregate functions.
Not understanding frame specifications, getting unexpected results.
Using window functions when simple aggregates would suffice.

Summary

Window functions perform calculations across rows without collapsing them.
OVER() clause defines partitioning, ordering, and framing.
Ranking functions assign positions, LAG/LEAD access adjacent rows.
Understanding window functions enables powerful analytical queries.
Window functions are essential for modern SQL analytics.

Exercise

Write a query using window functions to rank products by price within each category.

SELECT 
    ProductName,
    Category,
    Price,
    RANK() OVER (PARTITION BY Category ORDER BY Price DESC) as PriceRank
FROM Products
ORDER BY Category, PriceRank;

Window Functions