Aveek Das takes a look at a few analytic window functions available since SQL Server 2012:
Since the introduction of SQL Server 2012, the analytic functions were added to the SQL Server database engine. Any version of SQL Server after SQL Server 2012 can execute analytic queries on it. These functions are used to calculate an aggregated value from the dataset but are based on a specific set of rows instead of the entire dataset. As compared to aggregate functions like SUM, COUNT, AVG, etc. which return scalar records, these functions can return multiple records based on the conditions. The most common examples of using these functions are to find moving averages, running totals, etc. SQL Server supports the following analytic functions.
1. CUME_DIST – Find the cumulative distribution of a numerical column
2. FIRST_VALUE – Finds the first value of a column from the group and prints the same for each row
3. LAST_VALUE – Finds the last value of a column from the group and prints the same for each row
4. LAG – Reads values after the specified number of rows for a column
5. LEAD – Reads values before the specified number of rows for a column
Click through for examples of how each works.