Snowflake SQL und Skripting lernen

Kapitel 4. Arbeiten mit Sets

Diese Arbeit wurde mithilfe von KI übersetzt. Wir freuen uns über dein Feedback und deine Kommentare: [e-mail]

Obwohl du mit deinen Daten Zeile für Zeile arbeiten kannst, geht es bei relationalen Datenbanken eigentlich nur um Mengen. In diesem Kapitel geht es um die Verwendung von Mengenoperatoren, mit denen du Daten aus mehreren Ergebnismengen kombinieren kannst. Ich beginne mit einem kurzen Überblick über die Mengenlehre und zeige dann, wie die Mengenoperatoren union, intersect und except verwendet werden können, um mehrere Datensätze miteinander zu kombinieren.

Mengenlehre Fibel

Vielleicht erinnerst du dich aus dem Matheunterricht an Diagramme wie das in Abbildung 4-1 gezeigte.

Abbildung 4-1. Vereinigung von zwei Mengen

Der schattierte Bereich in Abbildung 4-1 stellt die Vereinigung der Mengen A und B dar, wobei der überlappende Bereich nur einmal enthalten ist. Hier ist eine weitere Möglichkeit, die Operation union zu veranschaulichen, indem du zwei Mengen von ganzen Zahlen verwendest:

A = {1, 2, 4, 7, 9}
B = {3, 5, 7, 9}
A union B = {1, 2, 3, 4, 5, 7, 9}

In diesem Beispiel enthält Menge A 5 ganze Zahlen und Menge B enthält 4 ganze Zahlen. Jede Menge hat ein paar einzigartige Werte, aber beide Mengen haben die Werte 7 und 9 gemeinsam. Die union von A und B ergibt insgesamt 7 Werte, wobei die ganzen Zahlen 7 und 9 nur einmal enthalten sind.

Als Nächstes schauen wir uns den Bereich an, den sich zwei Mengen teilen, den sogenannten Schnittpunkt. Abbildung 4-2 zeigt die grafische Darstellung eines Schnittpunkts.

Abbildung 4-2. Schnittpunkt von zwei Sets

Wenn du dieselben Zahlenmengen wie im letzten Beispiel verwendest, kannst du sehen, dass die Schnittmenge von A und B nur die Zahlen 7 und 9 enthält:

A = {1, 2, 4, 7, 9}
B = {3, 5, 7, 9}
A intersect B = {7, 9}

Wenn es keine Überschneidung zwischen den beiden Mengen gibt, wäre die Schnittmenge leer, und intersect ist der einzige Mengenoperator, der eine leere Menge ergeben kann.

Das dritte und letzte Diagramm in Abbildung 4-3 veranschaulicht eine Operation, bei der der Inhalt einer Menge ohne Überschneidungen mit einer anderen Menge zurückgegeben wird.

Abbildung 4-3. Minus von zwei Mengen

Diese Operation, die auch als except bekannt ist (du kannst aber auch minus verwenden), liefert die gesamte Menge A ohne Überschneidungen mit Menge B. Hier ist die gleiche Operation mit den numerischen Mengen:

A = {1, 2, 4, 7, 9}
B = {3, 5, 7, 9}
A except B = {1, 2, 4}

Von den drei Mengenoperationen ist except die einzige, die ein anderes Ergebnis liefert, wenn du die Reihenfolge der Mengen vertauschst. Hier ist das gleiche Beispiel, aber mit dem Ergebnis von B except A:

A = {1, 2, 4, 7, 9}
B = {3, 5, 7, 9}
B except A = {3, 5}

Diese drei Set-Operatoren decken viele Situationen ab, aber wie würdest du den in Abbildung 4-4 gezeigten Fall beschreiben?

Abbildung 4-4. Geheimnisvolle Operation

Die in Abbildung 4-4 gezeigte Operation kann nicht mit einem der drei zuvor beschriebenen Set-Operatoren erreicht werden. Sie kann jedoch durch die Kombination mehrerer Mengenoperatoren erreicht werden. Hier sind zwei verschiedene Möglichkeiten, um den schattierten Bereich in Abbildung 4-4 zu erzeugen:

(A union B) except (A intersect B)
  -- or --
(A except B) union (B except A)

In den nächsten Abschnitten dieses Kapitels erfährst du, wie du diese Set-Operatoren einsetzen kannst.

Die Gewerkschaft Operator

Mit dem Operator union kannst du zwei Datensätze kombinieren. Hier ist ein einfaches Beispiel:

PUBLIC>select 1 as numeric_col, 'ABC' as string_col
       union
       select 2 as numeric_col, 'XYZ' as string_col;
+-------------+------------+
| NUMERIC_COL | STRING_COL |
|-------------+------------|
|           1 | ABC        |
|           2 | XYZ        |
+-------------+------------+

Einfacher geht's nicht: Zwei einzeilige Ergebnismengen ohne überlappende Werte werden zu einer zweizeiligen Ergebnismenge kombiniert. Diese Art von Anweisung wird als Verbundabfrage bezeichnet, weil sie zwei unabhängige Abfragen enthält, die durch einen Mengenoperator getrennt sind. Bevor wir uns fortgeschritteneren Beispielen zuwenden, hier einige Einschränkungen in Bezug auf die beiden zu kombinierenden Mengen:

• Beide Sets müssen die gleiche Anzahl von Spalten enthalten.
• Die Datentypen der Spalten müssen übereinstimmen.

Hier siehst du, wie Snowflake reagiert, wenn die beiden Sets eine unterschiedliche Spaltenanzahl haben:

PUBLIC>select 1 as numeric_col, 'ABC' as string_col
       union
       select 2 as numeric_col, 'XYZ' as string_col, 
         99 as extra_col;
001789 (42601): SQL compilation error:
invalid number of result columns for set operator ...

In diesem Fall siehst du eine sehr spezifische Fehlermeldung von Snowflake, aber hier ist der Fehler, der ausgegeben wird, wenn die Spaltentypen nicht übereinstimmen:

PUBLIC>select 1 as numeric_col, 'ABC' as string_col
       union
       select 'XYZ' as numeric_col, 2 as string_col;
100038 (22018): Numeric value 'ABC' is not recognized

Diese Fehlermeldung ist allgemeiner gehalten, aber sie besagt, dass der Wert 'ABC' mit dem in der ersten Spalte der ersten Abfrage verwendeten Datentyp (die Zahl 1) nicht übereinstimmt.

Wie bereits erwähnt, entfernt der union Operator beim Aufbau der Ergebnismenge Duplikate, so dass Überschneidungen nur einmal dargestellt werden. Hier sind die beiden zuvor verwendeten Mengen ({1, 2, 4, 7, 9} und {3, 5, 7, 9}), die mit der Subklausel values erstellt und dann mit union kombiniert wurden:

PUBLIC>select integer_val
       from (values (1), (2), (4), (7), (9))
       as set_a (integer_val)
       union
       select integer_val
       from (values (3), (5), (7), (9))
       as set_b (integer_val);
+-------------+
| INTEGER_VAL |
|-------------|
|           1 |
|           2 |
|           4 |
|           7 |
|           9 |
|           3 |
|           5 |
+-------------+

Wie du siehst, enthalten beide Mengen 7 und 9, aber diese Werte erscheinen nur einmal in der Ergebnismenge, weil der union Operator die Werte sortiert und Duplikate entfernt.

Dies ist zwar das Standardverhalten, aber es gibt Fälle, in denen du nicht möchtest, dass die Duplikate entfernt werden. In diesem Fall kannst du union all verwenden:

PUBLIC>select integer_val
       from (values (1), (2), (4), (7), (9))
       as set_a (integer_val)
       union all
       select integer_val
       from (values (3), (5), (7), (9))
       as set_b (integer_val);
+-------------+
| INTEGER_VAL |
|-------------|
|           1 |
|           2 |
|           4 |
|           7 |
|           9 |
|           3 |
|           5 |
|           7 |
|           9 |
+-------------+

Die Ergebnismenge umfasst nun neun statt sieben Zeilen, wobei die doppelten 7er und 9er die zusätzlichen zwei Zeilen bilden.

Die Beispieldatenbank enthält Daten aus den Jahren 1992 bis 1998; hier ist eine Abfrage, die union verwendet, um alle Kunden zu finden, die 1992 oder 1993 Bestellungen über 350.000 $ aufgegeben haben:

PUBLIC>select distinct o_custkey
       from orders
       where o_totalprice > 350000
         and date_part(year, o_orderdate) = 1992
       union
       select distinct o_custkey
       from orders
       where o_totalprice > 350000
         and date_part(year, o_orderdate) = 1993;
+-----------+
| O_CUSTKEY |
|-----------|
|    114856 |
|    116683 |
|      4334 |
|     61790 |
|     24275 |
|     20665 |
|    130745 |
|     70054 |
|    122047 |
|    123934 |
... <847 rows omitted>
|    102304 |
|    106813 |
|    109519 |
|    120437 |
|    131131 |
|    130987 |
|    108745 |
|      5330 |
|     95137 |
|     59665 |
+-----------+

Diese Abfrage hätte auch mit einer einzigen Filterbedingung und ohne den union Operator geschrieben werden können, aber diese Form der Abfrage wird nützlich sein, um die Ergebnisse von intersect und except in den nächsten beiden Abschnitten zu vergleichen.

Der intersect Operator

Wie weiter oben im Kapitel beschrieben (siehe "Mengenlehre-Fibel"), wird der intersect Operator verwendet, um die Schnittmenge zwischen zwei Datensätzen zu finden. Hier ist ein Beispiel mit den beiden numerischen Datensätzen von vorhin:

PUBLIC>select integer_val
       from (values (1), (2), (4), (7), (9))
       as set_a (integer_val)
       intersect
       select integer_val
       from (values (3), (5), (7), (9))
       as set_b (integer_val);
+-------------+
| INTEGER_VAL |
|-------------|
|           7 |
|           9 |
+-------------+

Die Überschneidung der Mengen A und B sind die Werte 7 und 9; alle anderen Werte sind für jede Menge einzigartig.

Im vorherigen Abschnitt hat die Abfrage der Tabelle Orders mit union 867 Zeilen ergeben. Schauen wir uns an, was passiert, wenn union für dieselbe Abfrage in intersect geändert wird:

PUBLIC>select distinct o_custkey
       from orders
       where o_totalprice > 350000
         and date_part(year, o_orderdate) = 1992
       intersect
       select distinct o_custkey
       from orders
       where o_totalprice > 350000
         and date_part(year, o_orderdate) = 1993;
+-----------+ 
| O_CUSTKEY |
|-----------|
|    100510 |
+-----------+

Es gibt zwar 867 verschiedene Kunden, die 1992 oder 1993 Aufträge über 350.000 $ erteilt haben, aber nur einer von ihnen hat in beiden Jahren solche Aufträge erteilt.

Die Ausnahme Operator

Der dritte und letzte der Mengenoperatoren ist except, der verwendet wird, um Zeilen aus Menge A ohne Überschneidungen mit Menge B zurückzugeben. Hier ist ein Beispiel mit den beiden numerischen Datensätzen von vorhin:

PUBLIC>select integer_val
       from (values (1), (2), (4), (7), (9))
       as set_a (integer_val)
       except
       select integer_val
       from (values (3), (5), (7), (9))
       as set_b (integer_val);

+-------------+ 
| INTEGER_VAL |
|-------------|
|           1 |
|           2 |
|           4 |
+-------------+

Diese Operation ergibt die Menge {1, 2, 4}, also die drei Werte, die in Menge A gefunden wurden, aber nicht in Menge B. Wenn du die Reihenfolge der beiden Mengen vertauschst, erhältst du ein anderes Ergebnis:

PUBLIC>select integer_val
       from (values (3), (5), (7), (9))
       as set_b (integer_val)
       except
       select integer_val
       from (values (1), (2), (4), (7), (9))
       as set_a (integer_val);
+-------------+ 
| INTEGER_VAL |
|-------------|
|           3 |
|           5 |
+-------------+

Im Gegensatz zu union und intersect, die unabhängig davon, welche Menge oberhalb oder unterhalb des Mengenoperators liegt, die gleichen Ergebnisse liefern, musst du beachten, dass A except B normalerweise andere Ergebnisse liefert als B except A.

Zurück zur Abfrage von Orders: Hier sind die Ergebnisse, wenn du except verwendest:

PUBLIC>select distinct o_custkey
       from orders
       where o_totalprice > 350000
         and date_part(year, o_orderdate) = 1992
       except
       select distinct o_custkey
       from orders
       where o_totalprice > 350000
         and date_part(year, o_orderdate) = 1993;
+-----------+
| O_CUSTKEY |
|-----------|
|    134878 |
|    125183 |
|     44240 |
|     79138 |
|     71119 |
... <444 rows omitted>
|     74929 |
|    149240 |
|     23587 |
|     79999 |
|     46490 |
+-----------+

Diese Abfrage liefert 454 Kunden und wenn die Reihenfolge der Sets umgekehrt wird (Reihenfolge ab 1993 zuerst), liefert die Abfrage 412 Zeilen.

Betriebsregeln festlegen

In den folgenden Abschnitten findest du einige Regeln, die du bei der Arbeit mit Mengenoperatoren beachten solltest.

Sortieren von zusammengesetzten Abfrageergebnissen

Wenn du möchtest, dass deine Ergebnisse sortiert werden, wenn du Set-Operatoren verwendest, musst du die folgenden Regeln befolgen:

• Es darf nur eine order by Klausel geben, und die muss am Ende der Anweisung stehen.
• Wenn du Spaltennamen in der order by Klausel angibst, musst du die Spaltennamen/Aliase aus der ersten Abfrage verwenden.

Was den zweiten Punkt betrifft, so können die einzelnen Abfragen, die als Teil einer zusammengesetzten Anweisung verwendet werden, Daten aus verschiedenen Tabellen mit unterschiedlichen Spaltennamen abrufen. Daher bestimmen die in der ersten Abfrage verwendeten Spaltennamen, was in der order by Klausel angegeben werden kann. Hier ist ein Beispiel:

PUBLIC>select distinct o_orderdate from orders
       intersect
       select distinct l_shipdate from lineitem
       order by o_orderdate;
+-------------+ 
| O_ORDERDATE |
|-------------|
| 1992-01-03  |
| 1992-01-04  |
| 1992-01-05  |
| 1992-01-06  |
| 1992-01-07  |
... <2,394 rows omitted>
| 1998-07-29  |
| 1998-07-30  |
| 1998-07-31  |
| 1998-08-01  |
| 1998-08-02  |
+-------------+

In diesem Beispiel muss die Spalte, nach der sortiert werden soll, nach o_orderdate und nicht nach l_shipdate sortiert werden. Das passiert, wenn du l_shipdate als Sortierspalte angibst:

PUBLIC>select distinct o_orderdate from orders
       intersect
       select distinct l_shipdate from lineitem
       order by l_shipdate;
000904 (42000): SQL compilation error: error line 4 at position 9
invalid identifier 'L_SHIPDATE'

Um in diesen Fällen Verwirrung zu vermeiden, kannst du für beide Abfragen und übereinstimmende Spaltenaliase angeben und den Alias in deiner order by Klausel verwenden.

Operation Precedence einstellen

Wenn deine zusammengesetzte Abfrage mehr als zwei Abfragen enthält, die unterschiedliche Mengenoperatoren verwenden, musst du dir überlegen, in welcher Reihenfolge du die Abfragen platzierst, um die gewünschten Ergebnisse zu erzielen. Abbildung 4-5 zeigt die "Mystery Operation", die bereits in Abbildung 4-4 vorgestellt wurde.

Abbildung 4-5. Geheimnisvolle Operation

Schauen wir uns an, wie das mit den beiden einfachen numerischen Sets, die in diesem Kapitel verwendet werden, erreicht werden kann:

PUBLIC>select integer_val
       from (values (1), (2), (4), (7), (9))
       as set_a (integer_val);
+-------------+ 
| INTEGER_VAL |
|-------------|
|           1 |
|           2 |
|           4 |
|           7 |
|           9 |
+-------------+

PUBLIC>select integer_val
       from (values (3), (5), (7), (9))
       as set_b (integer_val);
+-------------+
| INTEGER_VAL |
|-------------|
|           3 |
|           5 |
|           7 |
|           9 |
+-------------+

Wie bereits erwähnt, gibt es mehrere Möglichkeiten, den schattierten Bereich in Abbildung 4-5 zu erzeugen, der in diesem Fall die Menge {1, 2, 3, 4, 5} ist, aber wir wählen die zweite:

(A except B) union (B except A)

So könnte das für die beiden numerischen Sets aussehen:

PUBLIC>select integer_val
       from (values (1), (2), (4), (7), (9))
       as set_a (integer_val)
       except
       select integer_val
       from (values (3), (5), (7), (9))
       as set_b (integer_val)
       union
       select integer_val
       from (values (3), (5), (7), (9))
       as set_b (integer_val)
       except
       select integer_val
       from (values (1), (2), (4), (7), (9))
       as set_a (integer_val);
+-------------+ 
| INTEGER_VAL |
|-------------|
|           3 |
|           5 |
+-------------+

Alle Teile scheinen an ihrem Platz und in der richtigen Reihenfolge zu sein, aber die Ergebnisse scheinen nicht korrekt zu sein. Das Problem liegt darin, dass der Server diese Vorgänge von oben nach unten ausführt, obwohl sie zusammengefügt werden müssen. Die beiden Operationen except müssen unabhängig voneinander durchgeführt werden, und die beiden resultierenden Mengen können dann mit union zu zusammengefügt werden. Dazu musst du Klammern verwenden:

PUBLIC>(select integer_val
        from (values (1), (2), (4), (7), (9))
        as set_a (integer_val)
        except
        select integer_val
        from (values (3), (5), (7), (9))
        as set_b (integer_val)
        )
       union
       (select integer_val
        from (values (3), (5), (7), (9))
        as set_b (integer_val)
        except
        select integer_val
        from (values (1), (2), (4), (7), (9))
        as set_a (integer_val)
        );
+-------------+ 
| INTEGER_VAL |
|-------------|
|           1 |
|           2 |
|           4 |
|           5 |
|           3 |
+-------------+

Wenn du zusammengesetzte Abfragen mit drei oder mehr Set-Operatoren erstellst, solltest du überlegen, wie die verschiedenen Operationen gruppiert werden müssen, um die gewünschten Ergebnisse zu erzielen.

Nachbereitung

In diesem Kapitel hast du gelernt, wie die Mengenoperatoren union, intersect und except verwendet werden können, um Überschneidungen zwischen mehreren Datensätzen zu erkennen. Außerdem hast du Strategien kennengelernt, wie du diese Operatoren kombinieren kannst, um komplexe Beziehungen zwischen Datensätzen zu erkennen. Auch wenn du die Mengenoperatoren in späteren Kapiteln nicht mehr in vielen Beispielen sehen wirst, solltest du diese leistungsstarken Werkzeuge im Hinterkopf behalten, wenn du Datenanalysen durchführst oder Berichte erstellst.

Teste dein Wissen

Die folgenden Übungen dienen dazu, dein Verständnis der Mengenoperatoren zu testen. Die Lösungen findest du in "Kapitel 4" im Anhang B.

Übung 4-1

Gegeben sind die folgenden zwei Sets:

A = {3, 5, 7, 9}
	
B = {4, 5, 6, 7, 8}

Welche Mengen werden bei jeder der folgenden Operationen erzeugt?

• A Gewerkschaft B
• A Vereinigung alle B
• A schneidet B
• A außer B
• B außer A

Übung 4-2

Schreibe eine zusammengesetzte Abfrage, die die Namen aller Regionen (Region.r_name) und Nationen (Nation.n_name) zurückgibt, die mit dem Buchstaben A beginnen.

Übung 4-3

Ändere die Abfrage aus Übung 4-2 so ab, dass sie nach Namen sortiert (die Standardsortierung ist in Ordnung).

Übung 4-4

Gegeben sind die folgenden drei Sets:

A = {3, 5, 7, 9}
	
B = {4, 5, 6, 7, 8}

C = {8, 9, 10}

Welche Menge wird bei der folgenden Operation zurückgegeben?

(A except B) intersect C