Windows functions in SQL server

The windows functions are an advance feature of SQL server that allows us to manipulate and perform advance analytics without writing the complex query. It applies aggregate ranking and analytics function over a particular windows and over clause is used with window function function to define the windows. It is much different than the traditional aggregate functions. In the windows function there is no restrictions to the columns that appear in the select list and no group by clause is required. It will return the all rows and adding the new columns for the aggregate column. In other hand when we are using the only aggregate function we will lose the details columns.   We will get the details column which we have used in the group by clause.

Syntax of windows function  

The OVER clause determines which rows make up the window. The over clause has three possible component

·         Partition by: This is dividing the rows into different partition. The windows function operate independently within the each partition. This indicates the name of the windows function which we want to use. And in the partition expression we will use the column name.

·         Order by: Use to order row by partition.

·         Frame: The number of rows that the windows function will work on the specified by clause.

Let’s see the below example

For the demo I am using AdventureWorks2019

Table: Sales.SalesOrderDetail having below columns. 

SUM (Aggregate function without window function)

Getting total sales by product wise.

select ProductID,sum(LineTotal) as total_sales from Sales.SalesOrderDetail group by ProductID order by ProductID

 
See here we are losing the others column.

SUM (Aggregate function with window function)

When we are using the windows function we are not losing the other columns. We are adding the new column in the output. See below.

select SalesOrderID,SalesOrderDetailID,CarrierTrackingNumber,           OrderQty,ProductID,SpecialOfferID,UnitPrice,           UnitPriceDiscount,LineTotal,           SUM(LineTotal) OVER (partition by ProductID order by ProductID) as total_sales           ,rowguid,ModifiedDate           from Sales.SalesOrderDetail

 
See below

In normal Aggregate function we are losing the columns but when we are using the windows function we are not losing any columns. 

 

example

Type of Windows functions

Below are the window functions

·         Aggregate

o   Avg()

o   Max()

o   Min()

o   Sum()

o   Count()

·         Ranking

o   Row_number()

o   Rank()

o   Dense_rank()

o   Percent_rank()

o   Percentile_count()

o   Percentile_disc()

o   NTILE()

o   Cume_dist()

o   Count_big()

·         Analytical or value window function

o   Lag()

o   Lead()

o   First_value()

o   Last_value()

o   NTH_value()