Erweiterungsoperatoren¶
Erweiterungsoperatoren erweitern einen Abfrageausdruck, der eine Liste darstellt, in die einzelnen Werte der Liste. Derzeit ist der Spread-Operator (**) der einzige von Snowflake unterstützte Erweiterungsoperator.
Spread¶
Der Spread-Operator erweitert ein Array in eine Liste von Einzelwerten. Dieser Operator ist für die folgenden Anwendungsfälle nützlich:
Abfragen, die IN-Klauseln enthalten.
Aufrufe von systemdefinierten Funktionen, die eine Liste von Werten als Argumente akzeptieren, wie z. B. COALESCE, GREATEST und LEAST.
Benutzerdefinierte SQL-Funktionen, die ein Argument verwenden, um ein Array von Werten bereitzustellen.
Gespeicherte -Snowflake Scripting-Prozeduren, die eine Bindungsvariable verwenden, um ein Array von Werten bereitzustellen. Weitere Informationen zur Verwendung von Bindungsvariablen in Snowflake Scripting finden Sie unter Verwenden einer Variablen in einer SQL-Anweisung (Bindung) und Verwenden eines Arguments in einer SQL-Anweisung (Bindung).
Weitere Informationen zu diesen Anwendungsfällen finden Sie in dem Blogbeitrag Snowflake stellt SQL Spread Operator (**) vor.
Syntax¶
** <array>
Einschränkungen¶
Die Eingabe muss ein Array mit konstanten Werten sein, bei dem es sich um ein Array mit Literalwerten oder eine Bindungsvariable handeln kann, die ein Array mit Literalwerten darstellt.
Jeder Wert in einem semistrukturierten Array ist vom Typ VARIANT. Ein VARIANT-Wert kann einen Wert eines beliebigen anderen Datentyps enthalten. Der Spread-Operator unterstützt die folgenden Datentypen für den im VARIANT-Wert gespeicherten Wert:
Numerisch (zum Beispiel INTEGER und NUMERIC)
Zeichenfolge und binär (zum Beispiel VARCHAR und BINARY)
Logisch (zum Beispiel BOOLEAN)
Datum und Uhrzeit (zum Beispiel DATE, TIME und TIMESTAMP)
Benutzerdefinierte Funktionen und gespeicherte Prozeduren, die in anderen Sprachen als SQL geschrieben wurden, können den Spread-Operator nicht verwenden.
Das Erweitern sehr großer Arrays mit dem Spread-Operator kann die Leistung beeinträchtigen.
Beispiele¶
Einige der Beispiele verwenden die Daten aus der folgenden Tabelle:
Erstellen Sie eine Tabelle, und fügen Sie Daten ein:
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 |
+------+------+
Die folgenden Beispiele verwenden den Spread-Operator.
Array von Literalwerten in einem Aufruf einer systemdefinierten Funktion erweitern
Spread-Operator mit einer Bindungsvariablen in einer benutzerdefinierten SQL-Funktion verwenden
Array von Literalwerten in einer IN-Klausel erweitern¶
Erweitern Sie ein Zahlen-Array mit dem Spread-Operator in einer Abfrage der zuvor erstellten Tabelle spread_demo:
SELECT * FROM spread_demo
WHERE col1 IN (** [3, 4])
ORDER BY col1;
+------+------+
| COL1 | COL2 |
|------+------|
| 3 | c |
| 4 | d |
+------+------+
Erweitern Sie ein Array von Zeichenfolgen mit dem Spread-Operator:
SELECT * FROM spread_demo
WHERE col2 IN (** ['b', 'd'])
ORDER BY col1;
+------+------+
| COL1 | COL2 |
|------+------|
| 2 | b |
| 4 | d |
+------+------+
Verwenden Sie eine IN-Klausel in einer Abfrage mit einer Mischung aus INTEGER-Werten und erweiterten Array-Werten:
SELECT * FROM spread_demo
WHERE col1 IN (** [1, 2], 4, 5)
ORDER BY col1;
+------+------+
| COL1 | COL2 |
|------+------|
| 1 | a |
| 2 | b |
| 4 | d |
| 5 | e |
+------+------+
Array von Literalwerten in einem Aufruf einer systemdefinierten Funktion erweitern¶
Erweitern Sie ein Array von Zeichenfolgen in einem Aufruf der COALESCE-Funktion:
SELECT COALESCE(** [NULL, NULL, 'my_string_1', 'my_string_2']) AS first_non_null;
+----------------+
| FIRST_NON_NULL |
|----------------|
| my_string_1 |
+----------------+
Erweitern Sie ein Array von Zahlen in einem Aufruf der GREATEST-Funktion:
SELECT GREATEST(** [1, 2, 5, 4, 5]) AS greatest_value;
+----------------+
| GREATEST_VALUE |
|----------------|
| 5 |
+----------------+
Spread-Operator mit einer Bindungsvariablen in einer benutzerdefinierten SQL-Funktion verwenden¶
Erstellen Sie eine benutzerdefinierte SQL-Funktion, die den Spread-Operator verwendet. Die Funktion verwendet ein Array als Argument und erweitert dann die Array-Werte, um die zuvor erstellte Tabelle spread_demo abzufragen:
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
$$;
Fragen Sie die Tabelle mit der Funktion ab:
SELECT * FROM TABLE(spread_function_demo([1, 3, 5]));
+------+------+
| COL1 | COL2 |
|------+------|
| 1 | a |
| 3 | c |
| 5 | e |
+------+------+
Spread-Operator mit einer Bindungsvariablen in einer gespeicherten Snowflake Scripting-Prozedur verwenden¶
Erstellen Sie eine gespeicherte Snowflake Scripting-Prozedur, die den Spread-Operator verwendet. Die gespeicherte Prozedur verwendet ein Array als Argument und erweitert dann die Array-Werte in einer Bindungsvariablen, um die zuvor erstellte Tabelle spread_demo abzufragen:
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;
$$;
Rufen Sie die gespeicherte Prozedur auf:
CALL spread_sp_demo([2, 4]);
+------+------+
| COL1 | COL2 |
|------+------|
| 2 | b |
| 4 | d |
+------+------+