ANY_VALUE (U-SQL)

Article
03/10/2017

Summary

The ANY_VALUE aggregator arbitrarily picks one value from the group including potentially a null value. While the operation picks an arbitrary value, it does so based on the efficiency of execution and not based on some random sampling.

The identity value is null.

Syntax

ANY_VALUE_Expression :=                                                                                  
      'ANY_VALUE' '(' expression ')'.

Remarks

expression
The C# expression (including column references) that gets aggregated.

Return Type

The type of the input.

Usage in Windowing Expression

This aggregator cannot be used in a windowing expression.

Examples

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

The examples below are 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, "Emma",   "HR",          200, 10000),
          (5, "Jacob",  "HR",          200, 10000),
          (6, "Olivia", "HR",          200, 10000),
          (7, "Mason",  "Executive",   300, 50000),
          (8, "Ava",    "Marketing",   400, 15000),
          (9, "Ethan",  "Marketing",   400, 10000) )
      AS T(EmpID, EmpName, DeptName, DeptID, Salary);

A. Single arbitrary value
The following query selects a single arbitrary EmpName.

@result =
    SELECT ANY_VALUE(EmpName) AS ArbitraryEmployee
    FROM @employees;
    
OUTPUT @result
TO "/Output/ReferenceGuide/any_value/exampleA.csv"
USING Outputters.Csv();

B. Arbitrary value per group
The following query selects an arbitrary EmpName for each DeptName.

@result =
    SELECT DeptName,
           ANY_VALUE(EmpName) AS ArbitraryEmployee
    FROM @employees
    GROUP BY DeptName; 
    
OUTPUT @result
TO "/Output/ReferenceGuide/any_value/exampleB.csv"
USING Outputters.Csv();

Partager via