What are analytical functions in SQL?

Question

Can anyone explain the analytical functions in SQL?

Answer 1

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.