Analytic Functions (U-SQL)
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:
Built_In_Analytic := 'CUME_DIST' | 'FIRST_VALUE' | 'LAG' | 'LAST_VALUE' | 'LEAD' | 'PERCENTILE_CONT' | 'PERCENTILE_DISC' | 'PERCENT_RANK'.