PERCENT_RANK (U-SQL)

Article
03/10/2017

Summary

The PERCENT_RANK analytic function calculates the relative rank of a row within a group of rows specified by the windowing expression.

The range of values returned by PERCENT_RANK is greater than or equal to 0 and less than or equal to 1. The first row in any set has a PERCENT_RANK of 0. NULL values are included by default and are treated as the highest possible values.

Use PERCENT_RANK to evaluate the relative standing of a value within the window.

PERCENT_RANK is similar to the CUME_DIST function.

PERCENT_RANK can only be used in the context of the OVER expression.

Syntax

PERCENT_RANK_Expression :=                                                                               
     'PERCENT_RANK' '(' ')'.

Return Type

The return type is double?.

Usage in Windowing Expression

This analytic function can be used in a windowing expression with the following restrictions:

The ROWS clause in the OVER operator is not allowed.

Examples

The example can be executed in Visual Studio with the Azure Data Lake Tools plug-in.
The script can be executed locally. An Azure subscription and Azure Data Lake Analytics account is not needed when executed locally.

The example below is based on the dataset defined below. Ensure your execution includes the rowset variable.

  @employees = 
      SELECT * FROM 
          ( VALUES
          (1, "Noah",   "Engineering", 100, 10000),
          (2, "Sophia", "Engineering", 100, 20000),
          (3, "Liam",   "Engineering", 100, 30000),
          (4, "Amy",    "Engineering", 100, 35000),
          (5, "Emma",   "HR",          200, 8000),
          (6, "Jacob",  "HR",          200, 8000),
          (7, "Olivia", "HR",          200, 8000),
          (8, "Mason",  "Executive",   300, 50000),
          (9, "Ava",    "Marketing",   400, 15000),
          (10, "Ethan", "Marketing",   400, 9000) 
          ) AS T(EmpID, EmpName, DeptName, DeptID, Salary);

Using PERCENT_RANK
The PERCENT_RANK function computes the rank of the employee's salary within a department as a percentage. The PARTITION BY clause is specified to partition the rows in the result set by department. The ORDER BY clause in the OVER clause orders the rows in each partition.

@result =
    SELECT *,
           PERCENT_RANK() OVER(PARTITION BY DeptID ORDER BY Salary) AS PercentRank
    FROM @employees;

OUTPUT @result
TO "/Output/ReferenceGuide/percent_rank/example.csv"
USING Outputters.Csv();

Share via