How to Use SQL Window Functions

These are a set of functions which help in applying aggregation/ranking functions over a subset of records from the entire records of a table. Pease note, this function only creates an additional column in the “select” result set. The original data in the table remains intact.

Elements of a Window Function

When preforming Widows functions, here are the keywords/functions which we need to use:

• Aggregate/Rank Function: This is a function which will be applied on the set of records
• OVER: This is the keyword which helps to implement the window function
• PARTITION BY: This helps us to create the set of records from all records
• ORDER BY: This helps us to sort the partition/set of records internally

Let’s take a look at the data.

Let’s assume below is the student’s data. Here we have students, subject name and marks obtained for each subject for each student.

What is highest score by student?

Now whenever we see/listen on getting highest/maximum (aggregate function: max), the first thing which comes to mind is “Group By”. So first let’s try that and see how we get the result set.

The “Group By” is only partially fulfilling the requirement, as you can see the entire rows are clubbed together as per the individual subject name values and highest marks are displayed among them. If you closely observe the requirements, not only we need the highest marks, but we also need to see all the records as well. Now this is where the Window functions come to the rescue.

Now as you see, we are able to get the highest marks (in the last column) and also the rows are intact. This will help in analyzing the marks of different students with just a glance. Below are the example queries using other aggregate functions with windows functions.

Here is the syntax

SELECT

columns,

windows_function(numerical_column)

OVER(PARTITION BY grouping_column ORDER BY grouping_column)

FROM table_name

There are also ranking functions, which need to be used in collaboration with the windows functions. This will help in various data transformations.

Let’s See How A Window Function Differs From a Group BY

Ranking Functions

The syntax for ranking functions is not terribly different. We in this case usually do not pass the column of aggregation into the Rank function. It will exist on its own and order by argument provides the ranking mechanism. Take the query below to give you an idea of how this function works. Finally, we can specify if we would like the ranking to be ascending or descending.

select *, rank() over(order by marks desc) from table

This is the basic structure of the ranking function. However, we can see a real-world query below to indicate what results you would produce.