In SQL Server, the analytic functions are used to calculate aggregate values on a set of rows. These analytic functions are similar to aggregate functions but analytic functions can return multiple rows for each group like computing moving averages, running totals, percentages or top-N results in a group.
Here are some of the analytic functions supported in the SQL Server:
- CUME_DIST () – to return the cumulative distribution of a value in a group of values
- FIRST_VALUE () – returns the first value from the particular column that is sorted by ORDER BY
- LAST_VALUE () – returns the last value from the particular column that is sorted by ORDER BY (opposite to FIRST_VALUE ())
- LEAD () – to find the values from the specific column for next to N rows
- LAG () – to find the values from the specific column for the previous N rows (opposite to LEAD ())
If you want to learn more about analytic functions in T-SQL, you can sign up for this SQL Certification program by Intellipaat.