SQL Window Function Explained Guide

SQL SERVER WINDOW FUNCTION QUERY IMPLEMENTATION

The syntax for window function in SQL server is:

SELECT column1, 
window_function(column2)
OVER([PARTITION BY column1] [ORDER BY column3]) AS newcolumn
FROM table_name;

This syntax explains how a user can choose a column, and then on the basis of it, partition other columns and then apply the window function on the desired column. Additionally, the users can sort the rows in ascending or descending order as per their requirements. Also, after the function implementation, a new column will add in the table as the resultant column. 

Here is the explanation of clauses in the syntax :

• Window Function: The function a user wishes to implement.
• Column1: the column that will be selected.
• Column2: the column on which the window function will be applied.
• Column3: the column on which the basis of the partition has been done.
• newcolumn: the resultant column that will be displayed after the function execution
• table_name: name of the table in which the whole process is being carried out. 

Now taking other examples for implementing the different window functions in SQL Server. 

Starting with the first type, the aggregate functions.

#1 Aggregate Window Function

The aggregate window function in SQL Server helps for performing operations like sum, average and other similar functions. As well as let’s take a look at the function implementation. 

The syntax for the average window function is:

SELECT Column1, Column2, Column3,
AVG(Column2)OVER(PARTITION BY Column1) AS AVG_Column2
From Tablename

[here you can add the table name and the column names as per your preference and the table data you are using]

The aggregate window function in SQL helps the users to perform operations like average, sum total, minimum value, maximum value, etc. Therefore, after executing the window function in the table, the users will get a resultant column in the original table. Additionally, this syntax was only for the AVG function in a specific table of the dataset. Let’s now proceed with other SQL window function explained operations.

#2 SUM() Function

Then, for using the SUM window function in the SQL Server, a user has to follow the given syntax. 

SELECT
Column_name1,
Column_name2,
Column_name3,
SUM(Column_name3) OVER(PARTITION BY Column_name1) AS total_sum
FROM table_name;

#3 MIN() Function

Also, to find the minimum value in a table row, this window function is useful.

SELECT
Column_name1,
Column_name2,
MIN(Column_name1) OVER(PARTITION BY Column_name2) AS min_value
FROM table_name;

#4 MAX() Function

Similarly, with the help of this function, users can find the maximum value of a window of rows, or partition of rows in a dataset.

SELECT
Column_name1,
Column_name2,
Column_name3,
Column_name4,
MAX(Column_name2) OVER(PARTITION BY Column_name4) AS max_value
FROM table_name;

#5 Rank() Window Function

This function is provides ranking of the rows in the table. The syntax for using the Rank() function is as follows:

SELECT 
ROW_NUMBER() OVER(PARTITION BY Column1 ORDER BY Column2 ASC/DESC) AS Column2,
Column3,
Column4,
RANK() OVER(PARTITION BY Column1 ORDER BY Column2) AS Column5,
DENSE_RANK() OVER(PARTITION BY Column1 ORDER BY Column2) AS Column6
FROM table_name;

Here the terms used are:

RANK() – The rank here signifies the rank for all the rows present in every partition of the table. 

ROW_NUMBER() – The ROW_NUMBER() in the syntax gives a unique number to the rows. 

DENSE_RANK() – The DENSE_RANK() here signifies the row number of each row in a specific partition of the dataset.

[here change the table name, column names, and other required data as per your requirements]

So, with the help of these commands, a user can rank the specified rows in the table, and find the first and the last value of the table, etc.