SQL Server: Window Functions

I am sure most of us have heard about Window Function in SQL Server 2005 and later. If you are aware and how to use, a short revision for u and if not, go ahead and understand it. J

These functions are called set functions and helps us to analyze how the result(s) is/are formed. Meaning, when the “Window” has been formed, analytic function calculates on aggregated value based on the records in the “window”. These function will run only for the given “window” or set of data.

Consider this example, when you want to use aggregate function using Group By clause and if every SELECT column has to be part of GROUP BY clause if there is no aggregate function application. By doing this, we have to put either logical thinking how aggregated are derived based on the given underlying data. For e.g.

Employee ID | Employee Name | Salary | Department

00001 | ABC | 500000 | SSE

00002 | XYZ | 450000 | SSE

00003 | PQR | 550000 | TL

00004 | MNO | 560000 | TL

Problem: Get the average salary based on Department

Solution: SELECT E.DEPARTMENT, AVG(Salary) as AverageSalaryByDepartment FROM EMPLOYEE E GROUP BY DEPARTMENT

Result:

Salary | Department

475000 | SSE

555000 | TL

But, if you want to include Employee data in the solution:

SELECT E.EmployeeName, E.DEPARTMENT, AVG(Salary) as AverageSalaryByDepartment FROM EMPLOYEE E GROUP BY DEPARTMENT

This will surely change the resultset and you cannot determine how average function (2 records) derived its value from underlying data (4 records).

That’s when Window Function comes into picture – OVER() and/or OVER(PARTITION BY)

· Ranking functions:

o ROW_NUMBER: Returns the sequential number of a row within a partition of a result set, starting at 1 for the first row in each partition.

WITH OrderedOrders AS

(

SELECT SalesOrderID, OrderDate,

ROW_NUMBER() OVER (ORDER BY OrderDate) AS ‘RowNumber’

FROM Sales.SalesOrderHeader

)

SELECT *

FROM OrderedOrders

o WHERE RowNumber BETWEEN 50 AND 60;

o RANK: Returns the rank of each row within the partition of a result set. The rank of a row is one plus the number of ranks that come before the row in question.

SELECT i.ProductID, p.Name, i.LocationID, i.Quantity

,RANK() OVER

(PARTITION BY i.LocationID ORDER BY i.Quantity DESC) AS ‘RANK’

FROM Production.ProductInventory i

INNER JOIN Production.Product p

ON i.ProductID = p.ProductID

ORDER BY p.Name;

§

o DENSE_RANK: Returns the rank of rows within the partition of a result set, without any gaps in the ranking. The rank of a row is one plus the number of distinct ranks that come before the row in question.

SELECT i.ProductID, p.Name, i.LocationID, i.Quantity,

DENSE_RANK() OVER (PARTITION BY i.LocationID ORDER BY i.Quantity) AS DENSE_RANK

FROM Production.ProductInventory i

INNER JOIN Production.Product p

ON i.ProductID = p.ProductID

ORDER BY Name;

o NTILE: Distributes the rows in an ordered partition into a specified number of groups. The groups are numbered, starting at one. For each row, NTILE returns the number of the group to which the row belongs.

SELECT p.FirstName, p.LastName

,NTILE(4) OVER(ORDER BY SalesYTD DESC) AS ‘Quartile’

,s.SalesYTD, a.PostalCode

FROM Sales.SalesPerson s

INNER JOIN Person.Person p

ON s.BusinessEntityID = p.BusinessEntityID

INNER JOIN Person.Address a

ON a.AddressID = p.BusinessEntityID

WHERE TerritoryID IS NOT NULL

AND SalesYTD <> 0;

· Aggregate functions:

o OVER():Determines the partitioning and ordering of the rowset before the associated window function is applied.

SELECT p.FirstName, p.LastName

,ROW_NUMBER() OVER(ORDER BY SalesYTD DESC) AS ‘Row Number’

,s.SalesYTD, a.PostalCode

FROM Sales.SalesPerson s

INNER JOIN Person.Person p

ON s.BusinessEntityID = p.BusinessEntityID

INNER JOIN Person.Address a

ON a.AddressID = p.BusinessEntityID

WHERE TerritoryID IS NOT NULL

AND SalesYTD <> 0;

With upcoming “Denali”, there are lot of improvements on set functions. Look forward to it.

https://connect.microsoft.com/SQLServer/feedback/details/254393/over-clause-enhancement-request-distinct-clause-for-aggregate-functions

https://connect.microsoft.com/SQLServer/feedback/details/254390/over-clause-enhancement-request-top-over

Enjoy T-SQLing!

Advertisements

About Jaggi
love technology, always updated on the latest and current happenings, seminars, tech.Ed, virtual days! Be Yourself!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: