Window Functions in SQL
Introduction
Window function is a type of advanced function that allows us to perform calculations across a specific set of rows related to the current row. These calculations happen within a defined window of data, and they are particularly useful for aggregates, rankings, and cumulative totals without altering the dataset.
Characteristics:
Calculations across a window:
Window functions operate on a defined "window" of rows, typically related to the current row, as opposed to standard aggregate functions which operate on the entire dataset.
Maintaining individual row data:
Unlike aggregate functions, window functions preserve the individual rows in the result set, allowing for calculations to be performed without losing row context.
Variety of calculations:
Window functions can be used for various purposes, including ranking, cumulative sums, moving averages, and other analytical tasks
Types of window functions in SQL:
1. Aggregate Window Functions
Aggregate window functions calculate aggregates over a window of rows while retaining individual rows. Common aggregate functions include:
SUM(): Sums values within a window.
AVG(): Calculates the average value within a window.
COUNT(): Counts the rows within a window.
MAX(): Returns the maximum value in the window.
MIN(): Returns the minimum value in the window.
2. Ranking Window Functions
These functions provide rankings of rows within a partition based on specific criteria. Common ranking functions include:
RANK(): Assigns ranks to rows, skipping ranks for duplicates.
DENSE_RANK(): Assigns ranks to rows without skipping rank numbers for duplicates.
ROW_NUMBER(): Assigns a unique number to each row in the result set.
Percent_Rank():Assigns the rank number of each row in a partition as a percentage.
NTILE(): Distributes the rows of a partition into a specified number of buckets.
CUME_DIST(): The cumulative distribution: the percentage of rows less than or equal to the current row.
3.Time Series Functions
The time-series window functions include:
LEAD() :used to access data from rows that come after the current row within a result set based on a specific column order.
LAG() : used to access data from rows that come before the current row within a result set based on a specific column order.
FIRST_VALUE():Returns the first value in an ordered set of values
The OVER clause is key to defining this window. It :
Partitions the data into different sets using the PARTITION BY clause ,and
Orders them by using the ORDER BY clause.
These windows enable functions like SUM(), AVG(), ROW_NUMBER(), RANK(), and DENSE_RANK() to be applied in a sophisticated manner.
Syntax
SELECT column_name1,
window_function(column_name2)
OVER([PARTITION BY column_name1] [ORDER BY column_name3]) AS new_column
FROM table_name;
Key Terms in the above syntax:
window_function= any aggregate or ranking function
column_name1= column to be selected
column_name2= column on which window function is to be applied
column_name3= column on whose basis partition of rows is to be done
new_column= Name of new column
table_name= Name of table
Lets look into few examples by considering subject_info Dataset.
The AVG() function calculates the average Temperature for each athlete using the PARTITION BY Sex clause.
If we specify the PARTITION BY clause by a column(s) then the result-set will be divided into different windows of the value of that column(s).
2
As you can see from the above image, the ID with the smallest Row number value is 1. Then 1 is added as its row number which is followed by the next ID.
3.
This result set was partitioned based on the Humidity column. Then we used the ORDER BY clause to sort the athlete records based on their Age in descending order in each partition. After that, we applied the RANK function.
Whereas Dense Rank() function assigns rank to each row within partition. Just like rank function first row is assigned rank 1 and rows having same value have same rank. The difference between RANK() and DENSE_RANK() is that in DENSE_RANK(), for the next rank after two same rank, consecutive integer is used, no rank is skipped.
Lag Function example
As shown in the image, the first row in the Humidity partition does not have a previous value (that is, no record comes before it) so that's why null was returned. Then in case of next row, it has a previous record, so it returns the previous value which is 54.
5.
In this case, NTILE(4) divides the students into 4 quartiles based on their scores.
Other Use cases of Windows functions:
Calculating Running Totals
This query calculates the cumulative total of Temperature for each day, ordered by Age.
Calculating Moving Averages
This query calculates a moving average of the temperature for each row in the subject_info table using a sliding window of 3 rows.
Challenges:
Partitioning Error: Ensure that the PARTITION BY clause is used correctly. If no partition is defined, the entire result set is treated as a single window.
ORDER BY Within the Window: The ORDER BY clause within the window function determines the order of calculations. Always verify that it aligns with the logic of your calculation.
Performance Considerations: Window functions can be computationally expensive, especially on large datasets. Always ensure that your window functions are optimized and, if necessary, combined with appropriate indexes.
Conclusion:
Window functions are a powerful SQL feature that allow you to perform row wise calculations across a set of related rows,, all while keeping each individual row in the result.
We can:
Use ROW_NUMBER(), RANK(), and DENSE_RANK() for ranking rows within groups.
Use LAG() and LEAD() to compare values across rows (previous/next).
Use SUM(), AVG(), and other aggregations with OVER() for running totals or moving averages.
Use NTILE(n) to divide data into percentiles or buckets.
Always define PARTITION BY and ORDER BY carefully to control the window's scope and order.
Combine with subqueries or CTEs to filter or isolate ranked or aggregated results.
