Windowing Functions

Dinesh KarunarathnaB.Sc. (Eng.),MCTS – SQL Server

Senior DBA – Pearson Lankadineshk@ecollege.comhttp://www.dbbitsandpieces.blogspot.com/

IntroductionWhat are the windowing functions

Functions that operates/ manipulates on set of rows

Types of windowing functions Ranking functions - Returns a ranking value for

each row Aggregate functions - Performs calculations on set

of values Analytic functions - Compute moving averages,

running totals, percentages…

Improvements of windowing functions in SQL Server 2012

New functionality in SQL Server 2005

OVER() Clause – Used to define a window or user specified set of rows within a query result set

PARTITION BY Clause - Divides the query result set into partitions

Ranking functions

Changes in aggregate functions

Partitioning a data setPartitioned by LocationID

Partitioned by CustomerID

Ranking functionsROW_NUMBER() -  Sequential number of a

row within a partition of a result setRANK() - Rank of each row within the partitionDENSE_RANK() - rank of rows within the

partition of a result set, without any gaps in the ranking

NTILE() - Distributes the rows in an ordered partition into a specified number of groups

Demo…

Aggregate functions

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

Aggregate functions cont…Enhancements in SQL Serve 2005

SUM(Column1) OVER (PARTITION BY Column2)

Enhancements in SQL Server 2012SUM(Column1) OVER (PARTITION BY

Column2 ORDER BY Column3)

Demo…

New features with SQL Server 2012Analytic functions

FIRST_VALUE()LAST_VALUE()LEAD()LAG()PERCENT_RANK()PERCENTILE_COUNT()PERCENTILE_DISC()CUME_DIST()

New features with SQL Server 2012 cont …

Enhancements of Aggregate functions ODER BY with OVER() clause

Enhancements of defining the partition (window) ROWS/RANGE CURRENT ROW PRECEDING FOLLOWING UNBOUNDED

Demo …

Q & AWhich function can be best used to retrieve

daily increment?

List five new key words used to define a window of a dataset

Thank you.