FIRST_VALUE (U-SQL)

Artykuł
03/10/2017

Summary

The FIRST_VALUE analytic function returns the first value in an ordered set of values provided by the windowing expression.

FIRST_VALUE can only be used in the context of a windowing expression.

Syntax

FIRST_VALUE_Expression :=                                                                                
    'FIRST_VALUE' '(' expression ')'.

Remarks

expression
The expression for which the first value gets calculated for the window..

Return Type

The nullable type of the input.

Usage in Windowing Expression

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

The ORDER BY clause in the OVER operator is required.

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, "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);

A. Using FIRST_VALUE
The following example uses FIRST_VALUE to return the name of the employee that is the highest paid for a given department.

@result =
    SELECT EmpName,
           Salary,
           FIRST_VALUE(EmpName) OVER(ORDER BY Salary DESC) AS HighestPaidEmployee
    FROM @employees
    WHERE DeptID == 100;
   
OUTPUT @result
TO "/Output/ReferenceGuide/first_value/exampleA.csv"
USING Outputters.Csv();

B. Using FIRST_VALUE over partitions
The following example uses FIRST_VALUE to return the highest paid employee compared to other employees within the same department. The PARTITION BY clause partitions the employees by department and the FIRST_VALUE function is applied to each partition independently. The ORDER BY clause specified in the OVER clause determines the logical order in which the FIRST_VALUE function is applied to the rows in each partition.

@result =
    SELECT DeptName,
           EmpName,
           Salary,
           FIRST_VALUE(EmpName) OVER(PARTITION BY DeptID ORDER BY Salary DESC) AS HighestPaidEmployeePerDept
    FROM @employees;

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

Udostępnij za pośrednictwem