Window Functions in SQL Explained (with Examples)

Window functions are tools that help us to see and understand data specially. In this article, we will learn the window functions in SQL, and aggregate window functions, along with examples to learn it better.

What are Window Functions in SQL?

Imagine you have a list of information (like grades in a class), and you want to know more than just individual grades. Window functions let you zoom out and look at groups of grades together. They help you do things like find the average grade in your class or see if someone's grade is higher or lower compared to others.

Window functions in SQL are analytical functions that perform calculations across a specified range of rows related to the current row within a result set. It includes various statistical and aggregate functions, such as SUM(), AVG(), MIN(), MAX(), ROW_NUMBER(), RANK(), and others.

Here are the examples of SQL window functions:

Unlike aggregate functions that work on the entire result set, window functions operate on a subset of rows defined by an OVER() clause. This clause allows you to create a "window" of rows using options like PARTITION BY, which divides the result set into partitions, and ORDER BY, which defines the order within each partition.

These functions often result in more concise SQL code that makes it more readable and maintainable compared to using subqueries. They are important for performing advanced analytical tasks, such as calculating rankings, percentiles, running totals, moving averages, and other metrics that involve analyzing data across different rows in a result set.

What are Aggregate Window Functions?

Aggregate window functions in SQL enable the calculation of aggregate values over specific windows of data, providing better insights than traditional aggregate functions. These functions utilize the OVER() clause to define the window, allowing the computation of aggregates for distinct groups within the dataset.

Commonly used aggregate window functions include SUM(), AVG(), MIN(), and MAX(), among others.

For example, if you have a table of sales data with information about different regions, aggregate window functions can help you calculate not only the total sales across all regions but also the subtotals for each specific region. The PARTITION BY clause assists in segmenting the data, and the aggregation occurs within those defined partitions.

Syntax:

SELECT

column1,

column2,

...,

-- Apply window function

AGGREGATE_FUNCTION(column) OVER (PARTITION BY partition_column ORDER BY order_column) AS result_column

FROM

your_table;

Performing a window aggregate function example for average marks per student

Table: Marks

SQL Query:

SELECT

StudentID,

StudentName,

Subject,

Marks,

AVG(Marks) OVER (PARTITION BY StudentID) AS AvgMarksPerStudent

FROM

marks;

Output:

In this example, the AVG() window function is still used with the PARTITION BY clause, but now the result includes both the student ID and name. The output will show the average marks for each subject along with the overall average marks per student.

Examples of Window Functions in SQL

We're going to look at different examples that use window functions in the "marks" table. These examples will help us understand how window functions work and how they can give us useful information from the data.

Example 1: Calculating the cumulative sum of marks for each student in each subject.

We will use the same table as above.

SQL Query:

SELECT

StudentID,

StudentName,

Subject,

Marks,

SUM(Marks) OVER (PARTITION BY StudentID, Subject ORDER BY Marks) AS CumulativeMarks

FROM

marks;

Output:

Example 2: Calculating the Rank of Students by Subject

SQL Query:

SELECT

StudentID,

StudentName,

Subject,

Marks,

RANK() OVER (PARTITION BY Subject ORDER BY Marks DESC) AS SubjectRank

FROM

marks;

Output:

Example 3: Finding the Subject with the Highest Marks for Each Student

SQL Query:

SELECT

StudentID,

StudentName,

Subject,

Marks,

MAX(Marks) OVER (PARTITION BY StudentID) AS HighestSubjectMarks

FROM

marks;

Output:

List of Window Functions in SQL

Now, let us check out some of the commonly used window functions in SQL databases. These help us look at our data in more detail. They're like special features that allow us to perform clever calculations and comparisons within specific groups of our data. 

1. ROW_NUMBER():
   • Gives each row a unique number.
   • Helpful for creating a kind of ID for each row.
2. RANK():
   • Assigns a rank to each row.
   • Similar to ROW_NUMBER() but handles tied rows differently
3. DENSE_RANK():
   • Similar to RANK() but without leaving gaps in rank values for tied rows.
4. NTILE(n):
   • Divide the data into 'n' parts.
   • Assign a group number to each part.
5. SUM(column) OVER (PARTITION BY ...):
   • Adds up values in a specific column over groups you define.
   • Useful for finding cumulative or group totals.
6. AVG(column) OVER (PARTITION BY ...):
   • Finds the average of values in a specific column over groups you define.
   • Useful for cumulative or group averages.
7. MIN(column) OVER (PARTITION BY ...):
   • Find the smallest value in a specific column over the groups you define.
   • Useful for getting the smallest value in a group.
8. MAX(column) OVER (PARTITION BY ...):
   • Finds the largest value in a specific column over groups you define.
   • Useful for getting the largest value in a group.
9. LEAD(column, offset, default_value) OVER (PARTITION BY ... ORDER BY ...):
   1. Gets the value of a column for a future row.
   2. Useful for comparing a row with the next one.
10. LAG(column, offset, default_value) OVER (PARTITION BY ... ORDER BY ...):
    • Gets the value of a column for a previous row.
    • Useful for comparing a row with the one before it.

These functions help you analyze and understand your data by considering specific groups or patterns within it. They're like tools that give you a closer look at different aspects of your information.

Conclusion

In a nutshell, SQL window functions are like weapons for making our data talk. They help us zoom in on specific parts of our information, making calculations and comparisons more exciting. Whether we are counting rows, ranking stuff, or figuring out averages, these functions add a cool factor to our queries. if you need to learn more, our SQL tutors are available 24/7 to help you online.