SQL window functions are powerful tools that perform calculations across a set of related rows. Unlike aggregate functions that group rows and return a single result, window functions keep all rows and add a calculated column. They use the OVER clause to define the window of rows for calculation.
The OVER clause has three main components. PARTITION BY divides rows into groups, similar to GROUP BY but keeps all rows visible. ORDER BY sorts rows within each partition, which is essential for ranking and sequential functions. The ROWS or RANGE clause can further limit which specific rows within the partition are included in the calculation.
Ranking functions are among the most commonly used window functions. ROW_NUMBER assigns unique sequential numbers to each row. RANK assigns the same rank to tied values but leaves gaps in the sequence. DENSE_RANK also handles ties but without gaps. These functions are typically used with ORDER BY to rank rows based on specific criteria like salary or performance scores.
Window functions also include aggregate functions like SUM and AVG when used with the OVER clause. These create running totals and moving averages without collapsing rows. Value functions like LAG and LEAD allow you to access values from previous or next rows, which is useful for comparing current values with historical data or calculating period-over-period changes.
To summarize what we've learned about SQL window functions: They perform calculations across related rows without collapsing them into groups. The OVER clause defines the calculation window using PARTITION BY, ORDER BY, and frame specifications. Common functions include ranking functions for ordering data and aggregate functions for running calculations. Window functions are essential tools for advanced analytics and reporting in modern SQL databases.