In addition to aggregate and ranking functions, you can also query
analytic functions on windows of data. These were added in SQL Server 2012.
There were a total of eight new analytic functions. Only a few of them are very
common.
The FIRST_VALUE() function gives you the value at the beginning of the
partition. Similarly LAST_VALUE() give you the value at the end of the
partition. LAG() goes backwards a specified number of rows in the window. And
LEAD() goes forward a specified number of rows.
There are some analytic functions that work with averages. First let’s
talk about what average means. It is not necessarily the median value, which is
the value in the middle of an ordered group. It is instead the sum of all
values in the group divided by the number of values in the group.
You can get the numerical average using the
PERCENTAGE_CONT(0.5) function. Or you could get the single value in the group
that is closest to that average using the PERCENTAGE_DISC(0.5) function.
