Window functions

Window functions

Window functions are distinguished by the presence of an OVER() clause and come in three varieties:

·         Ranking window functions

ü  ROW_NUMBER

ü  RANK

ü  DENSE_RANK

ü  NTILE

·         Aggregate window functions

ü  MIN, MAX, AVG, SUM

ü  COUNT, COUNT_BIG

ü  CHECKSUM_AGG

ü  STDEV, STDEVP, VAR, VARP

·         Analytic window functions

ü  LAG, LEAD

ü  FIRST_VALUE, LAST_VALUE

ü  PERCENT_RANK, PERCENTILE_CONT, PERCENTILE_DISC, CUME_DIST

The ranking and aggregate window functions were introduced in SQL Server 2005, and considerably extended in SQL Server 2012. The analytic window functions are introduced in SQL Server 2012.  Analytic functions compute an aggregate value based on a group of rows. However, unlike aggregate functions, they can return multiple rows for each group. You can use analytic functions to compute moving averages, running totals, percentages or top-N results within a group.