close
The Wayback Machine - https://web.archive.org/web/20241001123442/https://www.geeksforgeeks.org/window-functions-in-sql/
Open In App

Window functions in SQL

Last Updated : 22 Aug, 2024
Summarize
Comments
Improve
Suggest changes
Like Article
Like
Save
Share
Report
News Follow

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 column_name1, 
 window_function(column_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
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

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
Deep 25 Sales 30,000 25,000
Pradeep 22 Sales 20,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

SQL Server Window Functions ROWS vs. RANGE
We will cover the important concept of the MS SQL Server which is the difference between Row and Range. The confusion between the row and range and where it is used will be eradicated by reading this article. So, be with the flow of the article for a better understanding and keep practicing side by side. Understanding this topic will help you in be
5 min read
Window Functions in PL/SQL
A window function is sometimes referred to as an "Analytic Function". It is generally used to perform calculations on some specific set of rows related to each row in our result set. There are some real-world applications of the window function, they are sales analysis, inventory management, time series analysis, and so on. In short, the window fun
6 min read
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
Postgre Window Functions
PostgreSQL is an advanced relational database system that supports both relational (SQL) and non-relational (JSON) queries. It is free and open-source. Among its most powerful features is its capability to manage window functions which execrate the querying and analysis tasks which could not be possible at all using the structured Query Language (S
6 min read
MySQL Window Functions
MySQL Window Functions are advanced SQL capabilities that enable expensive calculations across sets of rows related to the current row. Aggregate functions collapse the result set. These functions, in general, permit ranking, running totals, moving averages, and access to data from other rows within the same result set. Window functions are particu
6 min read
Functions in MySQL | Numeric, String and Date Time Functions in MySQL
In MySQL, functions play a crucial role in performing various operations on data, such as calculations, string manipulations, and date handling. These built-in functions simplify complex queries and data transformations, making it easier to manage and analyze data within a database. In this article, we will look at these different categories of MyS
4 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
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
2 min read
Date manipulating functions in SQL
To manipulate and fetch date values from the table, oracle has inbuilt date functions. Here, we are going to cover Date manipulating functions in SQL. ADD_MONTHS : It will add the total number of months specified in n to the date parameter and Returns date. Syntax : ADD_MONTHS(d, n) Example - Select ADD_MONTHS(SYSDATE, 4) "Add months" from dual; Ou
2 min read
How to Use HAVING With Aggregate Functions in SQL?
SQL provides many built-in functions to perform tasks. There are 4 types of functions Date functions, Char functions, Numeric functions, and Aggregate functions. Here we will be looking at aggregate functions and how to use them with the HAVING keyword. Aggregate Functions are database built-in functions that act on multiple rows of the table and p
4 min read
How to Use SELECT With Aggregate Functions in SQL?
In SQL, mainly five aggregate functions are provided. These help us to process the table data in the desired way. All these are illustrated below. For this article, we will be using the Microsoft SQL Server as our database. Step 1: Create a Database. For this use the below command to create a database named GeeksForGeeks. Query: CREATE DATABASE Gee
2 min read
SQL Server Group Functions
The group function in SQL Server provides a powerful tool for performing calculations on groups of rows, allowing you to group data based on specific criteria. This function is important when you want to analyze and summarize information from multiple records in a data structure. The basic group functions are COUNT, SUM, AVG, MAX, and MIN. Syntax:
3 min read
SQL - Statistical Functions
SQL statistical functions are essential tools for extracting meaningful insights from databases. These functions, such as AVG(), SUM(), COUNT(), MIN(), MAX(), STDDEV(), and VAR(), enable users to perform statistical calculations on numeric data. Whether determining averages, sums, counts, or measures of variability, these functions empower efficien
4 min read
SQL | NULL functions
In the database, null values serve as placeholders for data that is either missing or not available. a null value is a flexible data type that can be placed in the column of any data type, including string, int, blob, and CLOB datatypes. It is not a component of any specific data type. Null values are helpful when cleaning the data prior to explora
5 min read
SQL | Character Functions with Examples
Character functions accept character inputs and can return either characters or number values as output. SQL provides a number of different character datatypes which includes - CHAR, VARCHAR, VARCHAR2, LONG, RAW, and LONG RAW. The various datatypes are categorized into three different datatypes : VARCHAR2 - A variable-length character datatype whos
6 min read
Date and Time Functions in PL/SQL
Date and Time Functions in PL/SQL are useful for date and time operations like extracting date components, performing date arithmetic, and converting between date formats. Date and Time Functions in PL/SQLThere are many useful Date and Time functions in PL/SQL. A table of such functions along with their description is given below: S.noFunctionDescr
4 min read
SQL MIN() and MAX() Functions
The SQL MIN() and MAX() functions are the aggregate functions in SQL, that operate on group of data and return one output. The SQL Min() function returns the minimum value of the selected columns, and the SQL MAX() function is used to return the maximum value of the selected columns. In this article, we will understand the concept of MIN() and MAX(
3 min read
Deterministic and Nondeterministic Functions in SQL Server
Deterministic and nondeterministic functions are two fundamental concepts in SQL Server that determine how a function behaves when called with a specific set of input values. Deterministic functions always return the same result every time they are called with a fixed set of input values and given the same state of the database. On the other hand,
2 min read
PL/SQL Functions
PL/SQL functions are reusable blocks of code that can be used to perform specific tasks. They are similar to procedures but must always return a value. A function in PL/SQL contains:Function Header: The function header includes the function name and an optional parameter list. It is the first part of the function and specifies the name and paramete
4 min read
SQL Server Mathematical Functions (SQRT, PI, SQUARE, ROUND, CEILING & FLOOR)
SQL Server mathematical functions are used to perform mathematical calculations. These functions are designed to simplify complex calculations and make it easier to analyze and manipulate data. Here, we will cover the syntax and examples of various mathematical functions like SQRT(), PI(), SQUARE(), and many more Mathematical Functions in SQL Serve
1 min read
SQL Aggregate functions
SQL Aggregate functions are functions where the values of multiple rows are grouped as input on certain criteria to form a single value result of more significant meaning. It is used to summarize data, by combining multiple values to form a single result. SQL Aggregate functions are mostly used with the GROUP BY clause of the SELECT statement. Vari
2 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: 116CHAR_LENGTH(): Doesn't work for SQL Server. Use LEN() for SQL Server. This function is used to f
4 min read
SQL General Functions
SQL general functions are built-in tools provided by SQL databases to perform a variety of operations on data. These functions are crucial for manipulating, analyzing, and transforming data efficiently. In this article, We will learn about the what are the SQL General Functions along with the examples and so on. SQL General FunctionsSQL general fun
5 min read
SQL | Date Functions (Set-1)
SQL Date Functions are essential for managing and manipulating date and time values in SQL databases. They provide tools to perform operations such as calculating date differences, retrieving current dates and times and formatting dates. In this article, We will learn about the various SQL Date Functions with the help of examples and so on. SQL Dat
5 min read
SQL | Date Functions (Set-2)
SQL Date Functions are powerful tools that allow users to manipulate, extract, and format date and time values within SQL databases. In this article, We will learn about What are SQL Date Functions along with various examples and so on. What are SQL Date Functions?SQL Date Functions are built-in functions that help manipulate, extract, and format d
4 min read
Categories of SQL Functions
SQL functions are powerful tools that help streamline queries, perform operations, and manipulate data efficiently. They are essential for handling various tasks within SQL queries and database management. Functions in SQL can be broadly categorized into predefined (built-in) functions and user-defined functions. In this article, We will learn abou
4 min read
SQL Date and Time Functions
In SQL, dates are complicated for newbies, since while working with a database, the format of the data in the table must be matched with the input data to insert. In various scenarios instead of date, datetime (time is also involved with date) is used. For storing a date or a date and time value in a database,MySQL offers the following data types:
3 min read
SQL SERVER – Input and Output Parameter For Dynamic SQL
An Input Parameter can influence the subset of rows it returns from a select statement within it. A calling script can get the value of an output parameter. An aggregate function or any computational expression within the stored process can be used to determine the value of the output parameter. A parameter whose value is given into a stored proced
3 min read
Article Tags :