Open In App

Window functions in SQL

Last Updated : 29 Apr, 2024
Improve
Improve
Like Article
Like
Save
Share
Report

Window functions apply to aggregate and ranking functions over a particular window (set of rows). OVER clause is used with window functions to define that window. OVER clause does two things : 

  • Partitions rows to form a set of rows. (PARTITION BY clause is used) 
  • Orders rows within those partitions into a particular order. (ORDER BY clause is used) 

Note: If partitions aren’t done, then ORDER BY orders all rows of the table. 

Syntax:  

SELECT coulmn_name1, 
 window_function(cloumn_name2)
 OVER([PARTITION BY column_name1] [ORDER BY column_name3]) AS new_column
FROM table_name;
 
 
window_function= any aggregate or ranking function    
column_name1= column to be selected
coulmn_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

Aggregate Window Function
Various aggregate functions such as SUM(), COUNT(), AVERAGE(), MAX(), and MIN() applied over a particular window (set of rows) are called aggregate window functions. 

Consider the following employee table : 

Name Age Department Salary
Ramesh 20 Finance 50, 000
Deep 25 Sales 30, 000
Suresh 22 Finance 50000
Ram 28 Finance 20, 000
Pradeep 22 Sales 20, 000

Example – 
Find average salary of employees for each department and order employees within a department by age. 

SELECT Name, Age, Department, Salary, 
 AVG(Salary) OVER( PARTITION BY Department) AS Avg_Salary
 FROM employee

This outputs the following:

Name Age Department Salary Avg_Salary
Ramesh 20 Finance 50,000 40,000
Suresh 22 Finance 50,000 40,000
Ram 28 Finance 20,000 40,000
Pradeep 22 Sales 20,000 25,000
Deep 25 Sales 30,000 25,000

Notice how all the average salaries in a particular window have the same value.

Let’s consider another case: 

SELECT Name, Age, Department, Salary, 
 AVG(Salary) OVER( PARTITION BY Department ORDER BY Age) AS Avg_Salary
 FROM employee

Here we also order the records within the partition as per age values and hence the average values change as per the sorted order.
The output of above query will be :  

Name Age Department Salary Avg_Salary
Ramesh 20 Finance 50,000 50,000
Suresh 22 Finance 50,000 50,000
Ram 28 Finance 20,000 40,000
Pradeep 22 Sales 20,000 20,000
Deep 25 Sales 30,000 25,000

Hence, we should be careful while adding order by clauses to window functions with aggregates.

Ranking Window Functions : 
Ranking functions are, RANK(), DENSE_RANK(), ROW_NUMBER() 

  • RANK() – 
    As the name suggests, the rank function assigns rank to all the rows within every partition. Rank is assigned such that rank 1 given to the first row and rows having same value are assigned same rank. For the next rank after two same rank values, one rank value will be skipped. For instance, if two rows share­ rank 1, the next row gets rank 3, not 2.
     
  • DENSE_RANK() – 
    It 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. 
     
  • ROW_NUMBER() – 
    ROW_NUMBER() gives e­ach row a unique number. It numbers rows from one­ to the total rows. The rows are put into groups base­d on their values. Each group is called a partition. In e­ach partition, rows get numbers one afte­r another. No two rows have the same­ number in a partition. This makes ROW_NUMBER() differe­nt from RANK() and DENSE_RANK(). ROW_NUMBER() uniquely identifies e­very row with a sequential inte­ger number. This helps with diffe­rent kinds of data analysis.

Note – 
ORDER BY() should be specified compulsorily while using rank window functions. 

Example – 
Calculate row no., rank, dense rank of employees is employee table according to salary within each department. 

SELECT 
    ROW_NUMBER() OVER (PARTITION BY Department ORDER BY Salary DESC) AS emp_row_no, 
    Name,  
    Department, 
    Salary,
    RANK() OVER(PARTITION BY Department ORDER BY Salary DESC) AS emp_rank,
    DENSE_RANK() OVER(PARTITION BY Department ORDER BY Salary DESC) AS emp_dense_rank
FROM 
    employee;

The output of above query will be :  

emp_row_no Name Department Salary emp_rank emp_dense_rank
1 Ramesh Finance 50, 000 1 1
2 Suresh Finance 50, 000 1 1
3 Ram Finance 20, 000 3 2
1 Deep Sales 30, 000 1 1
2 Pradeep Sales 20, 000 2 2

So, we can see that as mentioned in the definition of ROW_NUMBER() the row numbers are consecutive integers within each partition. Also, we can see difference between rank and dense rank that in dense rank there is no gap between rank values while there is gap in rank values after repeated rank. 


Previous Article
Next Article

Similar Reads

How to Find the Length of a Series Using Window Functions in SQL
In data analysis, understanding the sequential patterns within datasets is crucial for informed decision-making. SQL's window functions offer a powerful toolset for such tasks, allowing efficient calculations across related rows. This article delves into utilizing window functions to determine the length of series within datasets. Beginning with an
5 min read
Difference between Structured Query Language (SQL) and Transact-SQL (T-SQL)
Structured Query Language (SQL): Structured Query Language (SQL) has a specific design motive for defining, accessing and changement of data. It is considered as non-procedural, In that case the important elements and its results are first specified without taking care of the how they are computed. It is implemented over the database which is drive
2 min read
Configure SQL Jobs in SQL Server using T-SQL
In this article, we will learn how to configure SQL jobs in SQL Server using T-SQL. Also, we will discuss the parameters of SQL jobs in SQL Server using T-SQL in detail. Let's discuss it one by one. Introduction :SQL Server Agent is a component used for database task automation. For Example, If we need to perform index maintenance on Production ser
7 min read
SQL general functions | NVL, NVL2, DECODE, COALESCE, NULLIF, LNNVL and NANVL
In this article, we'll be discussing some powerful SQL general functions, which are - NVL, NVL2, DECODE, COALESCE, NULLIF, LNNVL and NANVL. These functions work with any data type and pertain to the use of null values in the expression list. These are all single row function i.e. provide one result per row. NVL(expr1, expr2) : In SQL, NVL() convert
4 min read
SQL Server Mathematical functions (SQRT, PI, SQUARE, ROUND, CEILING & FLOOR)
Mathematical functions are present in SQL server which can be used to perform mathematical calculations. Some commonly used mathematical functions are given below: 1. SQRT(): SQRT() function is the most commonly used function. It takes any numeric values and returns the square root value of that number. Syntax: SELECT SQRT(..value..) Example: 2. PI
1 min read
SQL | Numeric Functions
Numeric Functions are used to perform operations on numbers and return numbers. Following are the numeric functions defined in SQL: ABS(): It returns the absolute value of a number. Syntax: SELECT ABS(-243.5); Output: 243.5 SQL> SELECT ABS(-10); +--------------------------------------+ | ABS(10) +--------------------------------------+ | 10 +---
3 min read
Functions in PL/SQL
Function can be used as a part of SQL expression i.e. we can use them with select/update/merge commands. One most important characteristic of a function is that, unlike procedures, it must return a value. Syntax: Creating a function CREATE [OR REPLACE] FUNCTION function_name [(parameter_name type [, ...])] // this statement is must for functions RE
4 min read
SQL | String functions
String functions are used to perform an operation on input string and return an output string. Following are the string functions defined in SQL: ASCII(): This function is used to find the ASCII value of a character. Syntax: SELECT ascii('t'); Output: 116 CHAR_LENGTH(): Doesn't work for SQL Server. Use LEN() for SQL Server. This function is used to
4 min read
SQL | Date Functions (Set-2)
In SQL, dates are complicated for newbies, since while working with a database, the format of the date in the table must be matched with the input date in order to insert. In various scenarios instead of date, datetime (time is also involved with date) is used. Some of the date functions have been already discussed in the Set-1. In this post, the r
3 min read
SQL | Advanced Functions
SQL (Structured Query Language) offers a wide range of advanced functions that allow you to perform complex calculations, transformations, and aggregations on your data. Aggregate Functions In database management an aggregate function is a function where the values of multiple rows are grouped together as input on certain criteria to form a single
3 min read