ARRAY_AGG (U-SQL)

まとめ

ARRAY_AGG アグリゲーターによって新しい SQL が作成されます グループの値を項目として含むグループごとの ARRAY 値。 ARRAY_AGGは、グループ内の値の順序を保持していません。 配列を並べ替える必要がある場合は、LINQ OrderBy を使用できます。 ARRAY_AGGと EXPLODE は概念的には逆演算です。

ID 値が null です。

構文

ARRAY_AGG_Expression :=                                                                                  
      'ARRAY_AGG' '(' ['DISTINCT'] expression ')'.

解説

  • DISTINCT
    必要に応じて、集計前にグループ内の式によって返される値の重複を解除できます。

  • expression
    集計される C# 式 (列参照を含む)。 式の型は、SQL を含む 組み込みの U-SQL 型である必要があります 。MAP または SQL。ARRAY

戻り値の型

Sql。ARRAY<T> (T は入力式の型)。

ウィンドウ式での使用法

このアグリゲーターは、 ウィンドウ式では使用できません。

  • この例は、 Azure Data Lake Tools プラグインを使用して Visual Studio で実行できます。
  • スクリプトは ローカルで実行できます。 ローカルで実行する場合、Azure サブスクリプションと Azure Data Lake Analytics アカウントは必要ありません。

A. ARRAY_AGG
次の使用例は、従業員ごとに 1 つのグループとして電話番号を集計します。

@employees = 
    SELECT * FROM 
        ( VALUES
        ("Noah",   "cell:030-0074321"),
        ("Noah",   "office:030-0076545"),
        ("Sophia", "cell:(5) 555-4729"),
        ("Sophia", "office:(5) 555-3745"),
        ("Liam",   "cell:(5) 555-3932"),
        ("Amy",    "cell:(171) 555-7788"),
        ("Amy",    "office:(171) 555-6750"), 
        ("Amy",    "home:(425) 555-6238"),
        ("Justin", "cell:0921-12 34 65"),
        ("Justin", "office:0921-12 34 67"),
        ("Emma",   (string)null),
        ("Jacob",  ""),
        ("Olivia", "cell:88.60.15.31"),
        ("Olivia", "office:88.60.15.32"),
        ("Mason",  "cell:(91) 555 22 82"),
        ("Mason",  "office:(91) 555 91 99"), 
        ("Mason",  "home:(425) 555-2819"),
        ("Ava",    "cell:91.24.45.40"),
        ("Ava",    "office:91.24.45.41"),
        ("Ethan",  "cell:(604) 555-4729"),
        ("Ethan",  "office:(604) 555-3745"),
        ("David",  "cell:(171) 555-1212"),
        ("Andrew", "cell:(1) 135-5555"),
        ("Andrew", "office:(1) 135-4892"),
        ("Jennie", "cell:(5) 555-3392"),
        ("Jennie", "office:(5) 555-7293")
        ) AS T(EmpName, PhoneNumber);
        
@result =
    SELECT EmpName,
           string.Join(", ", ARRAY_AGG(PhoneNumber)) AS PhoneNumbers
    FROM @employees
    WHERE !string.IsNullOrEmpty(PhoneNumber)
    GROUP BY EmpName;

OUTPUT @result
TO "/Output/ReferenceGuide/Aggregate/array_agg/exampleA.csv"
USING Outputters.Csv();

B. EXPLODE - ボーナスの例
この例では を使用しません。ただし、EXPLODE を使用ARRAY_AGGして上記の例の結果を元に戻す方法を示しています。

@result =
    SELECT EmpName,
           new SQL.ARRAY<string>(PhoneNumbers.Split(',')) AS PhoneNumbersArray
    FROM @result;

@exploded =
    SELECT EmpName,
           PhoneNumber.Trim() AS PhoneNumber
    FROM @result
    CROSS APPLY 
    EXPLODE(PhoneNumbersArray) AS r(PhoneNumber);

    OUTPUT @exploded
    TO "/Output/ReferenceGuide/Aggregate/array_agg/exampleB.csv"
    USING Outputters.Csv();

C. ARRAY_AGG - その他の例
この例では、一般的な多対多リレーションシップを扱います。 ここでは、映画に複数のプロデューサーを含めることができます。すべてのプロデューサーを 1 つのレコードに登録したいと考えています。 次のクエリでは、すべてのプロデューサーが集計されます。
この例では、 ユーザー定義アグリゲーターの使用 - genericAggregator B の代替ソリューションを提供します。

@films = 
    SELECT * FROM 
        ( VALUES
        (1, "A Good Year"),
        (2, "American Gangster"),
        (3, "Robin Hood"),
        (4, "The Counselor")
        ) AS T(FilmID, Title);

@producers = 
    SELECT * FROM 
        ( VALUES
        (1, "Ridley Scott"),
        (2, "Brian Grazer"),
        (3, "Russell Crowe"),
        (4, "Nick Wechsler"),
        (5, "Steve Schwartz"),
        (6, "Paula Mae Schwartz")
        ) AS T(ProducerID, Producer);

@films_producers = 
    SELECT * FROM 
        ( VALUES
        (1, 1),
        (2, 1),
        (2, 2),
        (3, 1),
        (3, 2),
        (3, 3),
        (4, 1),
        (4, 4),
        (4, 5),
        (4, 6)
        ) AS T(FilmID, ProducerID);

@result =
    SELECT f.Title,
           COUNT( * ) AS ProducerCount,
           string.Join(", ", ARRAY_AGG(p.Producer)) AS Producers
    FROM @films AS f
         JOIN
             @films_producers AS fp
         ON f.FilmID == fp.FilmID
         JOIN
             @producers AS p
         ON p.ProducerID == fp.ProducerID
    GROUP BY f.Title;

OUTPUT @result
TO "/Output/ReferenceGuide/Aggregate/array_agg/exampleC.csv"
USING Outputters.Csv();

参照