展開演算子¶
展開演算子は、リストを表すクエリ式をリスト内の個々の値に展開します。現在、Snowflakeでサポートされている展開演算子はスプレッド演算子(**
)のみです。
スプレッド¶
スプレッド演算子は 配列 を個々の値のリストに展開します。この演算子は、以下のような場合に便利です。
IN 句を含むクエリ を含むクエリ。
引数を使用して値の配列を提供する SQL ユーザー定義 関数。
バインド変数を使用して値の配列を提供するSnowflake Scripting ストアドプロシージャ。Snowflake Scriptingでのバインド変数の使用に関する詳細については、 SQL ステートメントでの変数の使用(バインド) および SQL ステートメントでの引数の使用(バインド) をご参照ください。
これらのユースケースの詳細については、 Snowflake SQL スプレッド演算子(**)の紹介 ブログ投稿をご参照ください。
構文¶
** <array>
制限事項¶
入力は定数値の配列でなければならず、リテラル値の配列でも、リテラル値の配列を表すバインド変数でも可能です。
半構造化配列の各値は VARIANT 型です。VARIANT には、他のデータ型の値を含めることができます。スプレッド演算子は、 VARIANT に格納される値に対して以下のデータ型をサポートしています。
SQL 以外の言語で書かれたユーザー定義関数やストアドプロシージャでは、スプレッド演算子は使用できません。
非常に大きな配列をスプレッド演算子で展開すると、パフォーマンスが低下する可能性があります。
例¶
いくつかの例では、以下のテーブルのデータを使用します。
テーブルを作成してデータを挿入します。
CREATE OR REPLACE TABLE spread_demo (col1 INT, col2 VARCHAR);
INSERT INTO spread_demo VALUES
(1, 'a'),
(2, 'b'),
(3, 'c'),
(4, 'd'),
(5, 'e');
SELECT * FROM spread_demo;
+------+------+
| COL1 | COL2 |
|------+------|
| 1 | a |
| 2 | b |
| 3 | c |
| 4 | d |
| 5 | e |
+------+------+
以下の例では、スプレッド演算子を使用しています。
IN 句のリテラル値の配列を展開する¶
先に作成した spread_demo
テーブルのクエリで、スプレッド演算子を使用して数値の配列を展開します。
SELECT * FROM spread_demo
WHERE col1 IN (** [3, 4])
ORDER BY col1;
+------+------+
| COL1 | COL2 |
|------+------|
| 3 | c |
| 4 | d |
+------+------+
文字列の配列をスプレッド演算子で展開します。
SELECT * FROM spread_demo
WHERE col2 IN (** ['b', 'd'])
ORDER BY col1;
+------+------+
| COL1 | COL2 |
|------+------|
| 2 | b |
| 4 | d |
+------+------+
INTEGER 値と展開された配列値が混在するクエリでは、 IN 句を使用します。
SELECT * FROM spread_demo
WHERE col1 IN (** [1, 2], 4, 5)
ORDER BY col1;
+------+------+
| COL1 | COL2 |
|------+------|
| 1 | a |
| 2 | b |
| 4 | d |
| 5 | e |
+------+------+
システム定義の関数呼び出しでリテラル値の配列を展開する¶
COALESCE 関数の呼び出しで文字列の配列を展開します。
SELECT COALESCE(** [NULL, NULL, 'my_string_1', 'my_string_2']) AS first_non_null;
+----------------+
| FIRST_NON_NULL |
|----------------|
| my_string_1 |
+----------------+
GREATEST 関数の呼び出しで数値の配列を展開します。
SELECT GREATEST(** [1, 2, 5, 4, 5]) AS greatest_value;
+----------------+
| GREATEST_VALUE |
|----------------|
| 5 |
+----------------+
SQL ユーザー定義関数のバインド変数でスプレッド演算子を使用する¶
スプレッド演算子を使用する SQL ユーザー定義関数を作成します。この関数は引数として配列を受け取り、その配列の値を展開して、以前に作成した spread_demo
テーブルをクエリします。
CREATE OR REPLACE FUNCTION spread_function_demo(col_1_values ARRAY)
RETURNS TABLE(
col1 INT,
col2 VARCHAR)
AS
$$
SELECT * FROM spread_demo
WHERE col1 IN (** col_1_values)
ORDER BY col1
$$;
関数を使用してテーブルをクエリします。
SELECT * FROM TABLE(spread_function_demo([1, 3, 5]));
+------+------+
| COL1 | COL2 |
|------+------|
| 1 | a |
| 3 | c |
| 5 | e |
+------+------+
Snowflake Scriptingストアドプロシージャでスプレッド演算子とバインド変数を使用する¶
スプレッド演算子を使用するSnowflake Scriptingストアドプロシージャを作成します。このストアドプロシージャは引数として配列を受け取り、その配列の値をバインド変数に展開して、先に作成した spread_demo
テーブルをクエリします。
CREATE OR REPLACE PROCEDURE spread_sp_demo(col_1_values ARRAY)
RETURNS TABLE(
col1 INT,
col2 VARCHAR)
LANGUAGE SQL
AS
$$
DECLARE
res RESULTSET;
BEGIN
res := (SELECT * FROM spread_demo
WHERE col1 IN (** :col_1_values)
ORDER BY col1);
RETURN TABLE(res);
END;
$$;
ストアドプロシージャを呼び出します。
CALL spread_sp_demo([2, 4]);
+------+------+
| COL1 | COL2 |
|------+------|
| 2 | b |
| 4 | d |
+------+------+