Window function is a function which uses values from one or multiple rows to return a value for each row. The term Window describes the set of rows in the database on which the function will operate using an OVER() clause. The main advantage of using Window functions over regular aggregate functions is- Window functions do not cause rows to become grouped into a single output row, the rows retain their separate identities and an aggregated value will be added to each row.

TYPES OF WINDOW FUNCTIONS-

• Aggregate Window Functions

SUM(), MAX(), MIN(), AVG(). COUNT()

• Ranking Window Functions

RANK(), DENSE_RANK(), ROW_NUMBER(), NTILE()

• Value Window Functions

LAG(), LEAD(), FIRST_VALUE(), LAST_VALUE()

SYNTAX OF WINDOW FUNCTIONS-

window_function ( [ ALL ] Expression )

OVER ( [ PARTITION BY partition_list ] [ ORDER BY order_list] )

Parameter Explanation

window_function: It indicates the name of your window function.

ALL: It is an optional keyword that is used to count all values along with duplicates. We cannot use the DISTINCT keyword in window functions.

Expression: It is the name of the column or expression on which window functions is operated. In other words, it is the column name for which we calculate an aggregated value.

OVER: It specifies the window clauses for aggregate functions. It mainly contains two expressions partition by and order by, and it always has an opening and closing parenthesis even there is no expression.

PARTITION BY partition_list: This clause divides the rows into partitions, and then a window function is operated on each partition. Here we need to provide columns for the partition after the PARTITION BY clause. If we want to specify more than one column, we must separate them by a comma operator. SQL Server will group the entire table when this clause is not specified, and values will be aggregated accordingly.

ORDER BY order_list: It is used to specify the order of rows within each partition. When this clause is not defined, SQL Server will use the ORDER BY for the entire table.

Here is an example of how to use the ROW_NUMBER() window function to assign a unique number to each row within a result set, starting at 1 for the first row:

Example-

Table-students

Find the average marks of students for each subject and order students within a subject by age.

SELECT Student_name, Age, Subject, Marks,

AVG(Marks) OVER(PARTITION BY Subject ORDER BY Age) AS Avg_Marks

FROM students;