Analytic Functions (U-SQL)

Summary

U-SQL supports a set of SQL-based analytic functions that 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.

In U-SQL, analytics functions can only be used in the following syntactic contexts:

• As a window function in a windowing expression with the OVER operator where it will calculate the value for each window partition.

Some of the analytics functions are type-polymorphic, meaning that they can operate on many different input types and return potentially different types based on their input type.

Analytics functions cannot be nested.

U-SQL provides the following built-in analytic functions:

Syntax

Built_In_Analytic :=                                                                                   
      'CUME_DIST'
|     'FIRST_VALUE' 
|     'LAG' 
|     'LAST_VALUE' 
|     'LEAD' 
|     'PERCENTILE_CONT' 
|     'PERCENTILE_DISC' 
|     'PERCENT_RANK'.

See Also

• OVER Expression (U-SQL)