SQL Windows Functions

A window function allows the user to perform computation across several table rows associated with the current row. It is similar to an aggregate function as it is also used for calculation. The only difference between a window function and any other regular aggregate function is that when implemented, the window function does not groups rows into a single output row. The identity of the rows on which the operation is performed remains different. The function can easily manage more than just the current row of the query result.

Let's consider an example to compute the running total using the window function. First, we will create a table to perform SQL Window Function. Here we will use the Student table and implement the operation.

The table is as follows:

S_ID	S_Name	S_Dept	S_Country	S_Age
100	Aman	Science	India	19
105	Harsh	Maths	England	20
110	Harshit	Science	India	17
200	Kavya	Social Studies	Canada	18
120	Abhay	Maths	India	21
102	Ravi	English	India	22

Implement the below queries to define and add data values to create the above table.

CREATE TABLE Student (
    S_ID INT,
    S_Name VARCHAR(50),
    S_Dept VARCHAR(50),
    S_Country VARCHAR(50),
    S_Age INT
);

INSERT INTO Student (S_ID, S_Name, S_Dept, S_Country, S_Age)
VALUES (100, 'Aman', 'Science', 'India', 19),
       (105, 'Harsh', 'Maths', 'England', 20),
       (110, 'Harshit', 'Science', 'India', 17),
       (200, 'Kavya', 'Social Studies', 'Canada', 18),
       (120, 'Abhay', 'Maths', 'India', 21),
       (102, 'Ravi', 'English', 'India', 22);

SELECT *
FROM Student;

Output:

Implement the below SQL query to perform aggregate on the S_Age column of the table.

SELECT S_Age, SUM(S_Age) OVER (ORDER BY S_ID) AS running_total
FROM Student;

Output:

As you can see in the above illustration, the query to compute the aggregate is implemented without needing to use the GROUP BY clause.

Let us consider this example to learn the syntax for implementing the window function and understand its functioning.

Window Functions Syntax

Windows functions perform aggregate and rank operations over a specific window of rows. The functions are implemented with an OVER clause to define the records set. It performs two functions:

PARTITION BY Clause: It divides the rows into partitions. Partitions refer to a set of rows that meet specific criteria.

ORDER BY Clause: It arranges the rows within specific partitions into sequential order.

Note: If the user does not specify partitions in the OVER clause, then the ORDER BY clause is implemented for all the records in the table.

The Syntax to implement Windows Functions is as follows:

SELECT coulmn_name1, 
 window_function(cloumn_name2)
 OVER([PARTITION BY column_name1] [ORDER BY column_name3]) AS new_column
FROM table_name;

Here, the window_function refers to any aggregate or ranking function to be implemented by the user; column_name1 is the column to be selected column_name2 is the column on which the window function will be applied. column_name3 represents the column on the basis partition of the record is to be done. The new_column is the Name of the new column, and the table_name is the Name of the table that contains the data.

The SUM function is the first component to compute the aggregate using the Windows function, which is implemented similarly to any other aggregate function. When the OVER clause is used with the SUM function, it is designated as a window function.

SELECT S_Age, SUM(S_Age) OVER (ORDER BY S_ID) AS running_total
FROM Student;

The above query can be translated to return the sum of ages over the entire result order by the beginning of the record. The user can limit the window for which aggregate is being computed from the complete database to separate groups within the database by using the PARTITION BY clause. The query will be changed to as follow:

SELECT S_Age, SUM(S_Age) OVER (PARTITION BY S_Country ORDER BY S_ID) AS running_total
FROM Student
WHERE S_Age >= 20;

Output:

In the above query, the table is grouped based on the Student's Country (S_Country), and the order is done based on S_ID. The running total is performed on the S_Age column for all values greater than or equal to 20.

The above query groups the student based on their respective country. It means the students from different countries act as different tables. It means that the Sum function will compute the aggregate differently for all the partitions.

Note: Implementing the window function along with the standard aggregation in the same query is impossible. As the user cannot use Windows functions in the GROUP BY clause. Some examples of Windows functions are SUM, COUNT, and AVG.

Implementing Aggregate Functions

While implementing the window functions, the user can also implement the same aggregates that will be implemented by the user in any other scenario. These window functions are SUM, COUNT, and AVG.

SELECT
    S_ID,
    S_Name,
    S_Dept,
    S_Country,
    S_Age,
    SUM(S_Age) OVER (PARTITION BY S_Country ORDER BY S_ID) AS country_sum,
    COUNT(S_Age) OVER (PARTITION BY S_Country ORDER BY S_ID) AS country_count,
    AVG(S_Age) OVER (PARTITION BY S_Country ORDER BY S_ID) AS country_average
FROM Student;

Output:

ROW_NUMBER()

ROW_NUMBER() is a predefined SQL function that assigns the sequential number to each row of records. It begins with 1, and the following number is assigned based on the ORDER BY clause used in the SQL statement. The user does not require to specify any parameter in the Row_Number() function.

SELECT
ROW_NUMBER() OVER (
ORDER BY S_ID
) row_num,
S_Name
FROM
Student;

Output:

Ranking Window Functions

The ranking windows functions include the following functions:

RANK()

DENSE_RANK()

ROW_NUMBER()

RANK(): The name function is implemented to assign a rank to all the records within every partition. Rank is assigned such that the first row in each partition is Rank 1. The next record is assigned the next consecutive number as its rank. If two rows have the same value, the same rank will be assigned to the record. The next rank will be skipped when two rows have the same rank.

DENSE_RANK(): It assigns a rank to every row in the partition. It works similarly to the rank function in that the first row in each partition will be assigned rank 1, and if the two rows have the same value, then they will have the same rank. The difference between the RANK() and DENSE_RANK() function is that if two rows have the same rank in DENSE_RANK, the next row will be assigned the next consecutive number as the next records rank. It means that no rank is skipped.

ROW_NUMBER(): It assigns a row number to each record in the table. Consecutive numbers are assigned to all the records in the table. Ranking begins with 1 in each table. If the user implements a partition, the first row will assign 1. No two rows in the same partition can have the same row number.

Note: The user needs to implement the ORDER BY clause when implementing the Rank Window Function in SQL.

For example,

Compute the row no., rank, and dense rank of students in the Student table according to S_Age.

SELECT
    S_ID,
    S_Name,
    S_Dept,
    S_Country,
    S_Age,
    RANK() OVER (ORDER BY S_Age DESC) AS age_rank,
    DENSE_RANK() OVER (ORDER BY S_Age DESC) AS age_dense_rank,
    ROW_NUMBER() OVER (ORDER BY S_Age DESC) AS age_row_number
FROM Student;

Output:

Implementing Ranking Window Function With Partition By Clause

SELECT
    S_ID,
    S_Name,
    S_Dept,
    S_Country,
    S_Age,
    RANK() OVER (PARTITION BY S_Country ORDER BY S_Age DESC) AS country_age_rank,
    DENSE_RANK() OVER (PARTITION BY S_Country ORDER BY S_Age DESC) AS country_age_dense_rank,
    ROW_NUMBER() OVER (PARTITION BY S_Country ORDER BY S_Age DESC) AS country_age_row_number
FROM Student;

Output: