カテゴリ：: 半構造化データ関数と構造化データ関数（高次）

REDUCE¶

ラムダ式のロジックに基づいて、配列を単一の値に減らします。

REDUCE 関数は、配列、アキュムレータの初期値、ラムダ関数を受け取ります。配列の各要素にラムダ関数を適用し、各結果でアキュムレータを更新します。すべての要素を処理した後、 REDUCE は最終的なアキュムレータ値を返します。

こちらもご参照ください。: Snowflakeの高階関数でラムダ関数をデータ上で使用する

構文¶

REDUCE( <array> , <init> , <lambda_expression> )

引数¶

array

削除される要素を含む配列。配列は半構造化でも構造化でもかまいません。

init

アキュムレータの初期値。

lambda_expression

各配列要素の削除ロジックを定義するラムダ式。

ラムダ式は以下の構文で指定する必要があります。

<acc> [ <datatype> ] , <value> [ <datatype> ] -> <expr>

acc 引数はアキュムレータであり、 value 引数は配列内で現在処理されている要素です。

戻り値¶

この関数は、任意のデータ型の値を返すことができます。

入力配列が空の場合、この関数はアキュムレータの初期値を返します。

これらの場合、関数は NULL を返します。

入力配列は NULL です。
アキュムレータの初期値は NULL です。
ラムダ関数は NULL を返します。

使用上の注意¶

ラムダ value 引数のデータ型が明示的に指定されている場合、配列要素はラムダ呼び出しの前に指定された型に強制されます。強制については、データ型の変換をご参照ください。
型チェックは、アキュムレータの初期値、アキュムレータのラムダ引数、およびラムダ実行の戻り値がすべて同じ論理型および物理型であることを強制します。この要件を満たすためにキャスティングを使用する場合、3つのうち最大の物理的型が使用されます。
引数 value には、中間 NULL 値が指定されます。例については、配列内の NULL 値のスキップをご参照ください。

例¶

以下の例では、 REDUCE 関数を使用しています。

配列内の合計値の計算¶

REDUCE 関数を使用して、配列の値の合計を返し、アキュムレータの初期値に 0 を指定します。

SELECT REDUCE([1,2,3],
              0,
              (acc, val) -> acc + val
       ) AS sum_of_values;

+---------------+
| SUM_OF_VALUES |
|---------------|
|             6 |
+---------------+

この例は前の例と同じですが、この例では INT 型の構造化配列を指定しています。

SELECT REDUCE([1,2,3]::ARRAY(INT),
              0,
              (acc, val) -> acc + val
       ) AS sum_of_values_structured;

+--------------------------+
| SUM_OF_VALUES_STRUCTURED |
|--------------------------|
|                        6 |
+--------------------------+

REDUCE 関数を使用して、配列の値の合計を返し、アキュムレータの初期値に 10 を指定します。

SELECT REDUCE([1,2,3],
              10,
              (acc, val) -> acc + val
       ) AS sum_of_values_plus_10;

+-----------------------+
| SUM_OF_VALUES_PLUS_10 |
|-----------------------|
|                    16 |
+-----------------------+

配列内の各値の2乗の合計の計算¶

REDUCE 関数を使用して、配列の各値の2乗和を返し、アキュムレータの初期値に 0 を指定します。

SELECT REDUCE([1,2,3],
              0,
              (acc, val) -> acc + val * val
       ) AS sum_of_squares;

+----------------+
| SUM_OF_SQUARES |
|----------------|
|             14 |
+----------------+

配列内の NULL 値のスキップ¶

この例では、 array 引数に NULL 値が含まれています。この配列を REDUCE 関数に渡すと、アキュムレータには中間 NULL 値が指定されます。

REDUCE 関数を使用して配列内の値の合計を返し、配列内の NULL 値をスキップするためにラムダ式のロジックで ZEROIFNULL 関数を使用します。ラムダ式は ZEROIFNULL 関数を使用して、以下のロジックで配列の各値を処理します。

val が NULL の場合、ラムダ式の結果は acc + 0 となります。
val が NULL でない場合、ラムダ式の結果は acc + val となります。

クエリを実行します。

SELECT REDUCE([1,NULL,2,NULL,3,4],
              0,
              (acc, val) -> acc + ZEROIFNULL(val))
  AS sum_of_values_skip_null;

+-------------------------+
| SUM_OF_VALUES_SKIP_NULL |
|-------------------------|
|                      10 |
+-------------------------+

文字列値の生成¶

REDUCE 関数を使用して、配列内の各値を連結して文字列値のリストを返します。

SELECT REDUCE(['a', 'b', 'c'],
              '',
              (acc, val) -> acc || ' ' || val
       ) AS string_values;

+---------------+
| STRING_VALUES |
|---------------|
|  a b c        |
+---------------+

アキュムレーターでの配列の使用¶

ラムダ式のロジックで ARRAY_PREPEND 関数とともに REDUCE 関数を使用すると、入力配列の順序を逆にした配列を返します。

SELECT REDUCE([1, 2, 3, 4],
              [],
              (acc, val) -> ARRAY_PREPEND(acc, val)
       ) AS reverse_order;

+---------------+
| REVERSE_ORDER |
|---------------|
| [             |
|   4,          |
|   3,          |
|   2,          |
|   1           |
| ]             |
+---------------+

条件付きロジックの使用¶

ラムダ式のロジックで IFF 関数とともに REDUCE 関数を使用し、 if-then 式と同様の条件ロジックに基づいてアクションを実行します。この例では、ラムダ式に以下のロジックを使用しています。

配列の値が7より小さい場合、2乗してアキュムレータに追加します。
配列の値が7以上である場合、2乗せずにアキュムレータに追加します。

SELECT REDUCE([5,10,15],
              0,
              (acc, val) -> IFF(val < 7, acc + val * val, acc + val)
       ) AS conditional_logic;

+-------------------+
| CONDITIONAL_LOGIC |
|-------------------|
|                50 |
+-------------------+

テーブル内の要素の配列を単一値にする¶

order_id、 order_date、 order_detail の列を持つ orders という名前のテーブルがあるとします。order_detail 列は、行項目、購入数量、小計の配列です。テーブルには2行のデータがあります。次の SQL ステートメントは、このテーブルを作成し、行を挿入します。

CREATE OR REPLACE TABLE orders AS
  SELECT 1 AS order_id, '2024-01-01' AS order_date, [
    {'item':'UHD Monitor', 'quantity':3, 'subtotal':1500},
    {'item':'Business Printer', 'quantity':1, 'subtotal':1200}
  ] AS order_detail
  UNION
  SELECT 2 AS order_id, '2024-01-02' AS order_date, [
    {'item':'Laptop', 'quantity':5, 'subtotal':7500},
    {'item':'Noise-canceling Headphones', 'quantity':5, 'subtotal':1000}
  ] AS order_detail;

SELECT * FROM orders;

+----------+------------+-------------------------------------------+
| ORDER_ID | ORDER_DATE | ORDER_DETAIL                              |
|----------+------------+-------------------------------------------|
|        1 | 2024-01-01 | [                                         |
|          |            |   {                                       |
|          |            |     "item": "UHD Monitor",                |
|          |            |     "quantity": 3,                        |
|          |            |     "subtotal": 1500                      |
|          |            |   },                                      |
|          |            |   {                                       |
|          |            |     "item": "Business Printer",           |
|          |            |     "quantity": 1,                        |
|          |            |     "subtotal": 1200                      |
|          |            |   }                                       |
|          |            | ]                                         |
|        2 | 2024-01-02 | [                                         |
|          |            |   {                                       |
|          |            |     "item": "Laptop",                     |
|          |            |     "quantity": 5,                        |
|          |            |     "subtotal": 7500                      |
|          |            |   },                                      |
|          |            |   {                                       |
|          |            |     "item": "Noise-canceling Headphones", |
|          |            |     "quantity": 5,                        |
|          |            |     "subtotal": 1000                      |
|          |            |   }                                       |
|          |            | ]                                         |
+----------+------------+-------------------------------------------+

REDUCE 関数を使用して、各注文の全商品の小計を返します。

SELECT order_id,
       order_date,
       REDUCE(o.order_detail,
              0,
              (acc, val) -> acc + val:subtotal
       ) AS subtotal_sum
  FROM orders o;

+----------+------------+--------------+
| ORDER_ID | ORDER_DATE | SUBTOTAL_SUM |
|----------+------------+--------------|
|        1 | 2024-01-01 |         2700 |
|        2 | 2024-01-02 |         8500 |
+----------+------------+--------------+

REDUCE 関数を使用して、各注文で販売された商品のリストを返します。

SELECT order_id,
       order_date,
       REDUCE(o.order_detail,
              '',
              (acc, val) -> val:item || '\n' || acc
       ) AS items_sold
  FROM orders o;

+----------+------------+-----------------------------+
| ORDER_ID | ORDER_DATE | ITEMS_SOLD                  |
|----------+------------+-----------------------------|
|        1 | 2024-01-01 | Business Printer            |
|          |            | UHD Monitor                 |
|          |            |                             |
|        2 | 2024-01-02 | Noise-canceling Headphones  |
|          |            | Laptop                      |
|          |            |                             |
+----------+------------+-----------------------------+

Lambda式でテーブルの列を参照し、テーブルデータの配列要素を減らす¶

ARRAY型の列と INT型の列を持つテーブルを作成します。

CREATE OR REPLACE TABLE reduce_column_ref_demo AS
  SELECT [ 1, 2, 3 ] AS col1, 0 AS col2
  UNION
  SELECT [ 1, 2, 3 ] AS col1, 10 AS col2;

SELECT * FROM reduce_column_ref_demo;

+------+------+
| COL1 | COL2 |
|------+------|
| [    |    0 |
|   1, |      |
|   2, |      |
|   3  |      |
| ]    |      |
| [    |   10 |
|   1, |      |
|   2, |      |
|   3  |      |
| ]    |      |
+------+------+

REDUCE関数を使用して、 col2 の値をアキュムレーターの値に加算して、各行の配列の値の合計を返します。

SELECT REDUCE(col1,
              10,
              (acc, val) -> (acc + col2) + val
       ) AS reduce_col_ref
  FROM reduce_column_ref_demo;

+----------------+
| REDUCE_COL_REF |
|----------------|
|             16 |
|             46 |
+----------------+