Abfrageanweisungen gehen eine oder mehrere Tabellen oder Ausdrücke durch und geben die berechneten Ergebniszeilen zurück. In diesem Thema wird die Syntax für SQL-Abfragen in Cloud Spanner SQL beschrieben.
SQL-Syntax
query_statement: [ statement_hint_expr ][ table_hint_expr ][ join_hint_expr ]
query_expr statement_hint_expr: '@{' statement_hint_key = statement_hint_value [, ...] '}' statement_hint_key: { USE_ADDITIONAL_PARALLELISM | OPTIMIZER_VERSION | LOCK_SCANNED_RANGES } query_expr: [ WITH with_clause ] { select | ( query_expr ) | query_expr set_op query_expr } [ ORDER BY expression [{ ASC | DESC }] [, ...] ] [ LIMIT count [ OFFSET skip_rows ] ] select: SELECT [ AS { STRUCT | VALUE } ] [{ ALL | DISTINCT }] { [ expression. ]* [ EXCEPT ( column_name [, ...] ) ]
[ REPLACE ( expression [ AS ] column_name [, ...] ) ]
| expression [ [ AS ] alias ] } [, ...] [ FROM from_clause[, ...] ] [ WHERE bool_expression ] [ GROUP BY expression [, ...] ] [ HAVING bool_expression ] set_op: UNION { ALL | DISTINCT } | INTERSECT { ALL | DISTINCT } | EXCEPT { ALL | DISTINCT } table_hint_expr: '@{' table_hint_key = table_hint_value '}' table_hint_key: { FORCE_INDEX | GROUPBY_SCAN_OPTIMIZATION }
Notationsregeln
- Eckige Klammern "[ ]" zeigen optionale Klauseln an.
- Runde Klammern "( )" sind tatsächlich im Code vorhandene Klammern.
- Der senkrechte Strich "|" zeigt ein logisches ODER an.
- Geschweifte Klammern "{ }" umschließen einen Optionssatz.
- Ein Komma gefolgt von Auslassungspunkten in eckigen Klammern "[, ... ]" zeigt an, dass das vorhergehende Element in einer durch Kommas getrennten Liste wiederholt werden kann.
Für Anweisungen unterstützte Hinweise
Die folgenden Hinweise werden für Abfrageanweisungen unterstützt:
Hint-Taste | Mögliche Werte | Beschreibung |
---|---|---|
USE_ADDITIONAL_PARALLELISM |
TRUE FALSE (Standardeinstellung) |
Wenn TRUE , wird die Ausführungs-Engine nach Möglichkeit mehr Parallelität verwenden.
Da hierdurch möglicherweise für andere Vorgänge weniger Ressourcen vorhanden sind, verwenden Sie diesen Hinweis nicht, wenn Sie latenzempfindliche Vorgänge für dieselbe Instanz ausführen.
|
OPTIMIZER_VERSION | 1 nach N|latest | Führt die Abfrage mit der angegebenen Optimierungstool-Version aus. Mögliche Werte sind 1 bis N (die neueste Optimierungstool-Version) oder latest . Wenn der Hinweis nicht festgelegt ist, wird das Optimierungstool für die Version ausgeführt, die in den Datenbankoptionen festgelegt oder über die Client-API angegeben wurde. Wenn keine dieser Optionen festgelegt ist, verwendet das Optimierungstool standardmäßig die neueste Version.In Bezug auf die Versionseinstellung hat der von der Client-API festgelegte Wert Vorrang vor dem Wert in den Datenbankoptionen und der von diesem Hinweis festgelegte Wert hat Vorrang vor allem anderen. Weitere Informationen finden Sie unter Abfrageoptimierungstool. |
LOCK_SCANNED_RANGES |
exclusive shared (Standardeinstellung) |
Mit diesem Hinweis können Sie eine exklusive Sperre für eine Reihe von Bereichen anfordern, die von einer Transaktion gescannt werden. Die Aufrechterhaltung einer ausschließlichen Sperre hilft in Szenarien mit hohen Schreibkonflikten, d. h., dass mehrere Transaktionen gleichzeitig versuchen, dieselben Daten zu lesen und zu schreiben. Das führt zu einer Vielzahl von Abbruchen. auf.
Ohne den Hinweis ist es möglich, dass mehrere gleichzeitige Transaktionen gemeinsame Sperren erhalten und dann versuchen, ein Upgrade auf exklusive Sperren auszuführen. Dies führt zu einem "Deadlock", weil die gemeinsame Sperre jeder Transaktion verhindert, dass die anderen Transaktionen ein exklusives Upgrade ausführen. Cloud Spanner bricht alle bis auf eine der Transaktionen ab. Wenn Sie mit diesem Hinweis eine exklusive Sperre anfordern, erhält eine Transaktion die Sperre und die Ausführung wird fortgesetzt. Andere Transaktionen wiederum warten auf die Sperre. Der Durchsatz ist weiterhin begrenzt, da die in Konflikt stehenden Transaktionen jeweils nur eine Transaktion ausführen können. In diesem Fall führt Cloud Spanner jedoch immer nur eine einzige Transaktion aus, wodurch Zeit gespart und der Vorgang andernfalls abgebrochen und wiederholt wird. Transaktionen. Dieser Hinweis wird für alle Anweisungstypen sowie für die DML unterstützt. Cloud Spanner erzwingt immer die Serialisierbarkeit. Hinweise im Sperrmodus können beeinflussen, welche Transaktionen bei laufenden Arbeitslasten warten oder abbrechen, aber die Isolationsebene nicht ändern. Da es sich nur um einen Hinweis handelt, sollte sie nicht als mutex angesehen werden. Mit anderen Worten: Sie sollten keine exklusiven Sperren von Cloud Spanner als gegenseitiger Ausschlussmechanismus für die Ausführung von Code außerhalb von Cloud Spanner verwenden. Weitere Informationen finden Sie unter Sperren. |
Beispieltabellen
In den folgenden Tabellen wird das Verhalten verschiedener Abfrageklauseln in dieser Referenz veranschaulicht.
Tabelle „Roster“ (Teilnehmerliste)
Die Tabelle Roster
enthält eine Liste der Spielernamen (LastName
) und die eindeutige ID, die der Schule (SchoolID
) zugewiesen ist. Sie sieht so aus:
+-----------------------+
| LastName | SchoolID |
+-----------------------+
| Adams | 50 |
| Buchanan | 52 |
| Coolidge | 52 |
| Davis | 51 |
| Eisenhower | 77 |
+-----------------------+
Sie können diese WITH
-Klausel verwenden, um einen temporären Tabellennamen für die Beispiele in dieser Referenz zu emulieren:
WITH Roster AS
(SELECT 'Adams' as LastName, 50 as SchoolID UNION ALL
SELECT 'Buchanan', 52 UNION ALL
SELECT 'Coolidge', 52 UNION ALL
SELECT 'Davis', 51 UNION ALL
SELECT 'Eisenhower', 77)
SELECT * FROM Roster
Tabelle „PlayerStats“ (Spielerstatistik)
Die Tabelle PlayerStats
enthält eine Liste der Spielernamen (LastName
) und die eindeutige ID der Gegner, gegen die in einem bestimmten Spiel gespielt wurde (OpponentID
), sowie die Anzahl der Punkte, die der jeweilige Spieler in diesem Spiel erzielt hat (PointsScored
).
+----------------------------------------+
| LastName | OpponentID | PointsScored |
+----------------------------------------+
| Adams | 51 | 3 |
| Buchanan | 77 | 0 |
| Coolidge | 77 | 1 |
| Adams | 52 | 4 |
| Buchanan | 50 | 13 |
+----------------------------------------+
Sie können diese WITH
-Klausel verwenden, um einen temporären Tabellennamen für die Beispiele in dieser Referenz zu emulieren:
WITH PlayerStats AS
(SELECT 'Adams' as LastName, 51 as OpponentID, 3 as PointsScored UNION ALL
SELECT 'Buchanan', 77, 0 UNION ALL
SELECT 'Coolidge', 77, 1 UNION ALL
SELECT 'Adams', 52, 4 UNION ALL
SELECT 'Buchanan', 50, 13)
SELECT * FROM PlayerStats
Tabelle „TeamMascot“ (Teammaskottchen)
Die Tabelle TeamMascot
enthält eine Liste eindeutiger Schul-IDs (SchoolID
) und das Maskottchen für die jeweilige Schule (Mascot
).
+---------------------+
| SchoolID | Mascot |
+---------------------+
| 50 | Jaguars |
| 51 | Knights |
| 52 | Lakers |
| 53 | Mustangs |
+---------------------+
Sie können diese WITH
-Klausel verwenden, um einen temporären Tabellennamen für die Beispiele in dieser Referenz zu emulieren:
WITH TeamMascot AS
(SELECT 50 as SchoolID, 'Jaguars' as Mascot UNION ALL
SELECT 51, 'Knights' UNION ALL
SELECT 52, 'Lakers' UNION ALL
SELECT 53, 'Mustangs')
SELECT * FROM TeamMascot
SELECT-Liste
SELECT [ AS { typename | STRUCT | VALUE } ] [{ ALL | DISTINCT }] { [ expression. ]* [ EXCEPT ( column_name [, ...] ) ]
[ REPLACE ( expression [ AS ] column_name [, ...] ) ]
| expression [ [ AS ] alias ] } [, ...]
Die SELECT
-Liste bestimmt die Spalten, die von der Abfrage zurückgegeben werden. Ausdrücke in der SELECT
-Liste können auf Spalten in einem der from_item
-Elemente in der entsprechenden FROM
-Klausel verweisen.
Jedes Element in der SELECT
-Liste ist eines der folgenden:
*
expression
expression.*
SELECT *
SELECT *
, häufig als "Select Star" bezeichnet, erzeugt für jede Spalte eine Ausgabespalte, die nach Ausführung der vollständigen Abfrage sichtbar ist.
SELECT * FROM (SELECT "apple" AS fruit, "carrot" AS vegetable);
+-------+-----------+
| fruit | vegetable |
+-------+-----------+
| apple | carrot |
+-------+-----------+
SELECT expression
Bei Elementen in einer SELECT
-Liste kann es sich um Ausdrücke handeln. Diese Ausdrücke ergeben einen einzelnen Wert und erzeugen eine Ausgabespalte mit einem optionalen expliziten alias
.
Wenn der Ausdruck über keinen expliziten Alias verfügt, erhält er einen impliziten Alias gemäß den Regeln für implizite Aliasse, sofern möglich. Andernfalls ist die Spalte anonym und Sie können an anderer Stelle in der Abfrage nicht anhand des Namens darauf verweisen.
SELECT expression.*
Ein Element in einer SELECT
-Liste kann auch die Form expression.*
haben. Dies erzeugt eine Ausgabespalte für jede Spalte oder jedes Feld der obersten Ebene von expression
.
Der Ausdruck muss entweder ein Tabellenalias sein oder einen einzelnen Wert eines Datentyps mit Feldern ergeben, wie z. B. STRUCT.
Die folgende Abfrage erzeugt eine Ausgabespalte für jede Spalte in der Tabelle groceries
mit dem Alias g
.
WITH groceries AS
(SELECT "milk" AS dairy,
"eggs" AS protein,
"bread" AS grain)
SELECT g.*
FROM groceries AS g;
+-------+---------+-------+
| dairy | protein | grain |
+-------+---------+-------+
| milk | eggs | bread |
+-------+---------+-------+
Weitere Beispiele:
WITH locations AS
(SELECT STRUCT("Seattle" AS city, "Washington" AS state) AS location
UNION ALL
SELECT STRUCT("Phoenix" AS city, "Arizona" AS state) AS location)
SELECT l.location.*
FROM locations l;
+---------+------------+
| city | state |
+---------+------------+
| Seattle | Washington |
| Phoenix | Arizona |
+---------+------------+
WITH locations AS
(SELECT ARRAY<STRUCT<city STRING, state STRING>>[("Seattle", "Washington"),
("Phoenix", "Arizona")] AS location)
SELECT l.LOCATION[offset(0)].*
FROM locations l;
+---------+------------+
| city | state |
+---------+------------+
| Seattle | Washington |
+---------+------------+
Modifikatoren für *-Operator
SELECT * EXCEPT
Eine SELECT * EXCEPT
-Anweisung gibt die Namen einer oder mehrerer Spalten an, die vom Ergebnis auszuschließen sind. Alle übereinstimmenden Spaltennamen werden in der Ausgabe ausgelassen.
WITH orders AS
(SELECT 5 as order_id,
"sprocket" as item_name,
200 as quantity)
SELECT * EXCEPT (order_id)
FROM orders;
+-----------+----------+
| item_name | quantity |
+-----------+----------+
| sprocket | 200 |
+-----------+----------+
SELECT * REPLACE
Eine SELECT * REPLACE
-Anweisung gibt eine oder mehrere expression AS identifier
-Klauseln an. Jede Kennzeichnung muss mit einem Spaltennamen der SELECT *
-Anweisung übereinstimmen. In der Ausgabespaltenliste wird die Spalte, die mit der Kennzeichnung in einer REPLACE
-Klausel übereinstimmt, durch den Ausdruck in dieser REPLACE
-Klausel ersetzt.
Eine SELECT * REPLACE
-Anweisung ändert nicht die Namen oder die Reihenfolge der Spalten.
Sie kann jedoch den Wert und den Werttyp ändern.
WITH orders AS
(SELECT 5 as order_id,
"sprocket" as item_name,
200 as quantity)
SELECT * REPLACE ("widget" AS item_name)
FROM orders;
+----------+-----------+----------+
| order_id | item_name | quantity |
+----------+-----------+----------+
| 5 | widget | 200 |
+----------+-----------+----------+
WITH orders AS
(SELECT 5 as order_id,
"sprocket" as item_name,
200 as quantity)
SELECT * REPLACE (quantity/2 AS quantity)
FROM orders;
+----------+-----------+----------+
| order_id | item_name | quantity |
+----------+-----------+----------+
| 5 | sprocket | 100 |
+----------+-----------+----------+
Umgang mit doppelten Zeilen
Sie können die Ergebnisse, die von einer SELECT
-Abfrage zurückgegeben werden, folgendermaßen ändern.
SELECT DISTINCT
Eine SELECT DISTINCT
-Anweisung verwirft doppelte Zeilen und gibt nur die verbleibenden Zeilen zurück. SELECT DISTINCT
kann keine Spalten der folgenden Typen zurückgeben:
STRUCT
ARRAY
SELECT ALL
Eine SELECT ALL
-Anweisung gibt alle Zeilen zurück, einschließlich doppelter Zeilen.
SELECT ALL
ist die Standardeinstellung von SELECT
.
STRUCTs mit SELECT verwenden
Abfragen, die einen
STRUCT
am Stammverzeichnis des Rückgabetyps zurückgeben, werden in Cloud Spanner APIs nicht unterstützt. Die folgende Abfrage wird beispielsweise nur als Unterabfragen unterstützt:SELECT STRUCT(1, 2) FROM Users;
Die Rückgabe eines Arrays von Structs wird unterstützt. Die folgenden Abfragen werden in Cloud Spanner APIs beispielsweise unterstützt:
SELECT ARRAY(SELECT STRUCT(1 AS A, 2 AS B)) FROM Users;
SELECT ARRAY(SELECT AS STRUCT 1 AS a, 2 AS b) FROM Users;
Abfrageformen, die einen
ARRAY<STRUCT<...>>
-Wert vom TypNULL
oder einen Wert vom TypARRAY<STRUCT<...>>
mit einem Element, dasNULL
ist, zurückgeben können, werden jedoch in Cloud Spanner APIs nicht unterstützt. Die folgende Abfrage wird nur als Unterabfrage unterstützt:SELECT ARRAY(SELECT IF(STARTS_WITH(Users.username, "a"), NULL, STRUCT(1, 2))) FROM Users;
Weitere Beispiele zum Abfragen von STRUCTs
in einem ARRAY
finden Sie unter STRUCT-Elemente in einem ARRAY abfragen.
Weitere Informationen finden Sie auch in den Hinweisen zur Verwendung von STRUCTs
in Unterabfragen.
Wertetabellen
In Cloud Spanner SQL ist eine Wertetabelle eine Tabelle, deren Zeilentyp ein einzelner Wert ist. In einer regulären Tabelle besteht jede Zeile aus Spalten, die jeweils einen Namen und einen Typ haben. In einer Wertetabelle ist der Zeilentyp nur ein einzelner Wert und es gibt keine Spaltennamen.
In Cloud Spanner erfolgen Wertetabellen hauptsächlich als Ausgabe des Operators UNNEST
oder einer Unterabfrage. Die WITH
-Klausel führt eine Wertetabelle durch, wenn die verwendete Unterabfrage eine Wertetabelle erzeugt. Cloud Spanner unterstützt keine Wertetabellen als Basistabellen in Datenbankschemas und unterstützt keine Rückgabe von Werttabellen in Abfrageergebnissen. Daher werden Wertetabellen, die Abfragen erzeugen, nicht als Abfragen der obersten Ebene unterstützt.
In Kontexten, in denen eine Abfrage mit genau einer Spalte erwartet wird, kann stattdessen eine Wertetabellenabfrage verwendet werden. Für skalare Unterabfragen und Array-Unterabfragen (siehe Unterabfragen) ist normalerweise eine einspaltige Abfrage erforderlich. In Cloud Spanner SQL können sie jedoch auch mit einer Wertetabellenabfrage verwendet werden.
Eine Abfrage erstellt eine Wertetabelle, wenn sie SELECT AS
und eine der folgenden Syntaxen verwendet:
SELECT AS STRUCT
SELECT AS STRUCT expr [[AS] struct_field_name1] [,...]
Diese Abfrage erzeugt eine Wertetabelle mit einem STRUCT-Zeilentyp. Dabei entsprechen die STRUCT-Feldnamen und -Typen den in der SELECT
-Liste erzeugten Spaltennamen und -typen.
Beispiel:
SELECT ARRAY(SELECT AS STRUCT 1 a, 2 b)
SELECT AS STRUCT
kann in einer skalaren oder Array-Unterabfrage verwendet werden, um einen einzelnen STRUCT-Typ zu erstellen, der mehrere Werte zusammenfasst. Skalare Unterabfragen und Array-Unterabfragen (siehe Unterabfragen) dürfen normalerweise nicht mehrere Spalten zurückgeben, können aber eine einzelne Spalte mit dem STRUCT-Typ zurückgeben.
Anonyme Spalten sind zulässig.
Beispiel:
SELECT AS STRUCT 1 x, 2, 3
Die obige Abfrage erzeugt STRUCT-Werte vom Typ STRUCT<int64 x, int64, int64>.
. Das erste Feld hat den Namen x
, das zweite und dritte Felder sind anonym.
Im obigen Beispiel wird das gleiche Ergebnis ausgegeben wie durch die Abfrage SELECT AS VALUE
mit einem Struct Constructor:
SELECT AS VALUE STRUCT(1 AS x, 2, 3)
Doppelte Spalten sind zulässig.
Beispiel:
SELECT AS STRUCT 1 x, 2 y, 3 x
Die obige Abfrage erzeugt STRUCT-Werte vom Typ STRUCT<int64 x, int64 y, int64 x>.
. Das erste und dritte Feld haben den gleichen Namen (x
), das zweite Feld den Namen y
.
Im obigen Beispiel wird das gleiche Ergebnis ausgegeben wie durch die Abfrage SELECT AS VALUE
mit einem Struct Constructor:
SELECT AS VALUE STRUCT(1 AS x, 2 AS y, 3 AS x)
SELECT AS VALUE
SELECT AS VALUE
erzeugt eine Wertetabelle aus jeder SELECT
-Liste, die genau eine Spalte erzeugt. Anstelle einer Ausgabetabelle mit einer einzigen Spalte (möglicherweise mit einem Namen) wird eine Wertetabelle erzeugt, deren Zeilentyp dem Werttyp entspricht, der in der SELECT
-Spalte erzeugt wurde. Jeder Alias, den die Spalte hatte, wird in der Wertetabelle verworfen.
Beispiel:
SELECT AS VALUE 1
Die obige Abfrage erzeugt eine Tabelle mit dem Zeilentyp INT64.
Beispiel:
SELECT AS VALUE STRUCT(1 AS a, 2 AS b) xyz
Die obige Abfrage erzeugt eine Tabelle mit dem Zeilentyp STRUCT<a int64, b int64>
.
Beispiel:
SELECT AS VALUE v FROM (SELECT AS STRUCT 1 a, true b) v WHERE v.b
Bei einer Wertetabelle v
als Eingabe filtert die obige Abfrage bestimmte Werte in der WHERE
-Klausel heraus und erzeugt dann eine Wertetabelle mit genau demselben Wert wie in der Eingabetabelle. Wenn in der obigen Abfrage SELECT AS VALUE
nicht verwendet wird, unterscheidet sich das Ausgabetabellenschema von dem Eingabetabellenschema, da die Ausgabetabelle eine reguläre Tabelle mit einer Spalte namens v
sein würde, die 101}Eingabewert.
Aliasse
Unter Aliasse verwenden finden Sie Informationen zu Syntax und Sichtbarkeit für Aliasse in der Liste SELECT
.
FROM-Klausel
FROM from_clause[, ...] from_clause: from_item [ tablesample_operator ] from_item: { table_name [ table_hint_expr ] [ as_alias ] | join_operation | ( query_expr ) [ table_hint_expr ] [ as_alias ] | field_path | unnest_operator | with_query_name [ table_hint_expr ] [ as_alias ] } table_hint_expr: '@{' table_hint_key = table_hint_value '}' table_hint_key: { FORCE_INDEX | GROUPBY_SCAN_OPTIMIZATION } as_alias: [ AS ] alias
Die FROM
-Klausel gibt die Tabelle bzw. die Tabellen an, aus denen Zeilen abgerufen werden sollen, und legt fest, wie diese Zeilen verknüpft werden sollen, um einen einzelnen Stream an Zeilen für die Verarbeitung in der restlichen Abfrage zu erzeugen.
tablesample_operator
Siehe TABLESAMPLE-Operator.
table_name
Der Name einer vorhandenen Tabelle.
SELECT * FROM Roster;
Tabellenhinweise
Die folgenden Hinweise werden für Tabellen unterstützt:
Hint-Taste | Mögliche Werte | Beschreibung |
---|---|---|
FORCE_INDEX |
String. Der Name eines vorhandenen Indexes in der Datenbank oder _BASE_TABLE , damit die Basistabelle anstelle eines Indexes verwendet wird. |
Hinweis: |
GROUPBY_SCAN_OPTIMIZATION |
TRUE FALSE |
Die Group-by-Scan-Optimierung kann Abfragen beschleunigen, wenn sie Diese Optimierung wird normalerweise angewendet, wenn das Optimierungsprogramm entscheidet, dass dadurch die Abfrage effizienter wird. Der Hinweis setzt diese Entscheidung außer Kraft. Wenn der Hinweis auf |
Im folgenden Beispiel wird gezeigt, wie beim Lesen aus einer Tabelle ein sekundärer Index verwendet wird. Dabei wird eine Indexanweisung in der Form @{FORCE_INDEX=index_name}
an den Tabellennamen angehängt:
SELECT s.SingerId, s.FirstName, s.LastName, s.SingerInfo FROM Singers@{FORCE_INDEX=SingersByFirstLastName} AS s WHERE s.FirstName = "Catalina" AND s.LastName > "M";
Sie können mehrere Indexe in einer Abfrage berücksichtigen, auch wenn nur ein einzelner Index für jeden eindeutigen Tabellenverweis unterstützt wird. Beispiel:
SELECT s.SingerId, s.FirstName, s.LastName, s.SingerInfo, c.ConcertDate FROM Singers@{FORCE_INDEX=SingersByFirstLastName} AS s JOIN Concerts@{FORCE_INDEX=ConcertsBySingerId} AS c ON s.SingerId = c.SingerId WHERE s.FirstName = "Catalina" AND s.LastName > "M";
Weitere Informationen zu Indexanweisungen erhalten Sie im Abschnitt "Sekundärindexe".
join_operation
Siehe JOIN-Vorgang.
query_expr
( query_expr ) [ [ AS ] alias ]
ist die Unterabfrage einer Tabelle.
field_path
In der FROM
-Klausel ist field_path
jeder Pfad, der in ein Feld innerhalb eines Datentyps aufgelöst wird. field_path
kann beliebig tief in eine verschachtelte Datenstruktur führen.
Beispiele für gültige field_path
-Werte:
SELECT * FROM T1 t1, t1.array_column;
SELECT * FROM T1 t1, t1.struct_column.array_field;
SELECT (SELECT ARRAY_AGG(c) FROM t1.array_column c) FROM T1 t1;
SELECT a.struct_field1 FROM T1 t1, t1.array_of_structs a;
SELECT (SELECT STRING_AGG(a.struct_field1) FROM t1.array_of_structs a) FROM T1 t1;
Feldpfade in der FROM
-Klausel müssen mit einem Array-Feld enden. Außerdem dürfen Feldpfade keine Arrays vor dem Ende des Pfads enthalten. Der Pfad array_column.some_array.some_array_field
ist beispielsweise ungültig, da er vor dem Ende des Pfads ein Array enthält.
unnest_operator
Siehe UNNEST-Operator.
with_query_name
Die Abfragenamen in einer WITH
-Klausel (siehe WITH-Klausel) verhalten sich wie Namen von temporären Tabellen, auf die Sie an beliebiger Stelle in der FROM
-Klausel verweisen können.
Im Beispiel unten sind subQ1
und subQ2
with_query_names
.
Beispiel:
WITH
subQ1 AS (SELECT * FROM Roster WHERE SchoolID = 52),
subQ2 AS (SELECT SchoolID FROM subQ1)
SELECT DISTINCT * FROM subQ2;
Die WITH
-Klausel blendet permanente Tabellen mit dem gleichen Namen während der Dauer der Abfrage aus, es sei denn, Sie qualifizieren den Tabellennamen. Beispiel:
db.Roster
.
UNNEST-Operator
unnest_operator: { UNNEST( array_expression ) | UNNEST( array_path ) | array_path } [ table_hint_expr ] [ as_alias ] [ WITH OFFSET [ as_alias ] ]
Der Operator UNNEST
nimmt ein ARRAY
und gibt eine Tabelle mit einer Zeile je Element im ARRAY
zurück.
Sie können UNNEST
aber auch außerhalb der FROM
-Klausel mit dem IN
-Operator verwenden.
Bei Eingabe-ARRAY
s der meisten Elementtypen enthält die Ausgabe von UNNEST
in der Regel eine Spalte. Diese eine Spalte hat einen optionalen alias
, mit dem Sie an anderer Stelle in der Abfrage auf die Spalte verweisen können. ARRAYS
mit folgenden Elementtypen geben mehrere Spalten zurück:
- STRUCT
UNNEST
löst die Reihenfolge der Elemente im Eingabe-ARRAY
auf. Verwenden Sie die optionale WITH OFFSET
-Klausel, um eine zweite Spalte mit den Array-Elementindexen zurückzugeben (siehe unten).
Verschiedene Möglichkeiten zur Verwendung von UNNEST
, einschließlich Konstruktion, vereinfachte Darstellung und Filterung, finden Sie unter Working with arrays
.
UNNEST und STRUCTs
Für ein Eingabe-ARRAY
mit STRUCT
-Elementen gibt UNNEST
eine Zeile für jedes Element STRUCT
mit einer separaten Spalte für jedes Feld in dem Element STRUCT
zurück. Der Alias für jede Spalte ist der Name des entsprechenden Felds im Element STRUCT
.
Beispiel:
SELECT *
FROM UNNEST(ARRAY<STRUCT<x INT64, y STRING>>[(1, 'foo'), (3, 'bar')]);
+---+-----+
| x | y |
+---+-----+
| 3 | bar |
| 1 | foo |
+---+-----+
Da der Operator UNNEST
eine Wertetabelle zurückgibt, können Sie durch Angabe eines Alias für UNNEST
eine Bereichsvariable definieren, auf die Sie an anderer Stelle in der Abfrage verweisen können. Wenn Sie auf eine Bereichsvariable in der Liste SELECT
verweisen, gibt die Abfrage ein STRUCT
-Element zurück, das alle Felder des ursprünglichen Elements STRUCT
in der Eingabetabelle enthält.
Beispiel:
SELECT *, struct_value
FROM UNNEST(ARRAY<STRUCT<x INT64, y STRING>>[(1, 'foo'), (3, 'bar')])
AS struct_value;
+---+-----+--------------+
| x | y | struct_value |
+---+-----+--------------+
| 3 | bar | {3, bar} |
| 1 | foo | {1, foo} |
+---+-----+--------------+
Explizite und implizite UNNEST
Das Auflösen der Verschachtelung eines ARRAY
kann explizit oder implizit erfolgen.
Beim expliziten Auflösen einer Verschachtelung muss array_expression
einen ARRAY
-Wert zurückgeben. Es muss aber nicht in ein ARRAY
aufgelöst werden. Das Schlüsselwort UNNEST
ist erforderlich.
Beispiel:
SELECT * FROM UNNEST ([1, 2, 3]);
Beim impliziten Auflösen einer Verschachtelung muss array_path
in ein ARRAY
aufgelöst werden. Das Schlüsselwort UNNEST
ist optional.
Beispiel:
SELECT x
FROM mytable AS t,
t.struct_typed_column.array_typed_field1 AS x;
In diesem Szenario kann array_path
beliebig tief in eine Datenstruktur führen. Allerdings muss das letzte Feld vom Typ ARRAY
sein. Es kann kein vorheriges Feld im Ausdruck vom Typ ARRAY
verwendet werden, da es nicht möglich ist, ein benanntes Feld aus einem ARRAY
zu extrahieren.
UNNEST und NULLs
UNNEST
verarbeitet den Wert NULL folgendermaßen:
- NULL und leere Arrays erzeugen null Zeilen.
- Ein Array, das NULL-Werte enthält, erzeugt Zeilen, die NULL-Werte enthalten.
Die optionale WITH OFFSET
-Klausel gibt eine separate Spalte zurück, die den „Offset“-Wert (d. h., die Zählung beginnt bei null) für jede Zeile enthält, die von dem UNNEST
-Vorgang erzeugt wird. Diese Spalte hat einen optionalen alias
. Der Standardalias ist „offset“.
Beispiel:
SELECT * FROM UNNEST ( ) WITH OFFSET AS num;
Operator TABLESAMPLE
tablesample_clause:
TABLESAMPLE sample_method (sample_size percent_or_rows )
sample_method:
{ BERNOULLI | RESERVOIR }
sample_size:
numeric_value_expression
percent_or_rows:
{ PERCENT | ROWS }
partition_by:
PARTITION BY partition_expression [, ...]
Beschreibung
Mit dem Operator TABLESAMPLE
können Sie eine Stichprobe eines Datensatzes auswählen.
Dieser Operator ist nützlich, wenn Sie mit Tabellen arbeiten, die große Datenmengen enthalten, und Sie keine genauen Antworten benötigen.
sample_method
: Wenn Sie den OperatorTABLESAMPLE
verwenden, müssen Sie den Stichprobenalgorithmus angeben:BERNOULLI
: Jede Zeile wird individuell mit der Wahrscheinlichkeit ausgewählt, die in der Klauselpercent
angegeben ist. Als Ergebnis erhalten Sie ungefährN * percent/100
Zeilen.RESERVOIR
: Nimmt als Parameter eine tatsächliche Stichprobengröße K (ausgedrückt als Anzahl von Reihen). Wenn die Eingabe kleiner als K ist, wird die gesamte Eingabebeziehung ausgegeben. Ist die Eingabe dagegen größer als K, wird eine Stichprobe genau der Größe K ausgegeben, wobei jede Stichprobe der Größe K gleich wahrscheinlich ist.
sample_size
: Die Größe der Stichprobe.percent_or_rows
: Beim OperatorTABLESAMPLE
müssen SieROWS
oderPERCENT
auswählen. Wenn SiePERCENT
wählen, muss der Wert zwischen 0 und 100 liegen. BeiROWS
muss der Wert größer oder gleich 0 sein.
Beispiele
In den folgenden Beispielen wird die Verwendung des Operators TABLESAMPLE
veranschaulicht.
Auswahl aus einer Tabelle mit der Stichprobenmethode RESERVOIR
:
SELECT MessageId
FROM Messages TABLESAMPLE RESERVOIR (100 ROWS);
Auswahl aus einer Tabelle mit der Stichprobenmethode BERNOULLI
:
SELECT MessageId
FROM Messages TABLESAMPLE BERNOULLI (0.1 PERCENT);
Verwenden Sie TABLESAMPLE
mit einer Unterabfrage:
SELECT Subject FROM
(SELECT MessageId, Subject FROM Messages WHERE ServerId="test")
TABLESAMPLE BERNOULLI(50 PERCENT)
WHERE MessageId > 3;
Verwenden Sie einen TABLESAMPLE
-Vorgang mit einem Join zu einer anderen Tabelle.
SELECT S.Subject
FROM
(SELECT MessageId, ThreadId FROM Messages WHERE ServerId="test") AS R
TABLESAMPLE RESERVOIR(5 ROWS),
Threads AS S
WHERE S.ServerId="test" AND R.ThreadId = S.ThreadId;
JOIN-Vorgang
join_operation: { cross_join_operation | join_operation_with_condition } cross_join_operation: from_item CROSS JOIN [ join_hint_expr ] from_item join_operation_with_condition: from_item [ join_type ] [ join_method ] JOIN [ join_hint_expr ] from_item [ { on_clause | using_clause } ] join_type: { [INNER] | CROSS | FULL [OUTER] | LEFT [OUTER] | RIGHT [OUTER] }
join_method:
{ HASH }
join_hint_expr: '@{' join_hint_key = join_hint_value [, ...] '}' join_hint_key: { FORCE_JOIN_ORDER | JOIN_METHOD } on_clause: ON bool_expression using_clause: USING ( join_column [, ...] )
Der JOIN
-Vorgang führt zwei from_item
-Elemente zusammen, damit die SELECT
-Klausel diese Elemente als einzelne Quelle abfragen kann. Die Klauseln join_type
und ON
oder USING
(eine "Join-Bedingung") geben an, wie Zeilen aus den beiden from_item
-Elementen kombiniert oder verworfen werden, um eine einzelne Quelle zu bilden.
Alle JOIN
-Vorgänge erfordern einen join_type
. Wenn kein join_type
mit einem JOIN
-Vorgang angegeben wird, wird ein INNER JOIN
ausgeführt.
Ein JOIN
-Vorgang erfordert eine Join-Bedingung, es sei denn, eine der folgenden Bedingungen ist wahr:
join_type
istCROSS
.- Bei einem oder beiden
from_item
-Elementen handelt es sich nicht um eine Tabelle, z. B. einarray_path
oderfield_path
.
Join-Hinweise
Die folgenden Hinweise werden für JOIN
unterstützt:
Hint-Taste | Mögliche Werte | Beschreibung |
---|---|---|
FORCE_JOIN_ORDER |
TRUE FALSE (Standardeinstellung) |
Wenn diese Option auf "true" festgelegt ist, verwenden Sie die in der Abfrage angegebene Join-Reihenfolge. |
JOIN_METHOD |
HASH_JOIN APPLY_JOIN |
Wählen Sie bei der Implementierung eines logischen Joins eine bestimmte Alternative aus, die für die zugrundeliegende Join-Methode verwendet werden soll. Weitere Informationen finden Sie im Abschnitt "Join-Methoden". Für einen HASH-Join verwenden Sie entweder HASH JOIN oder JOIN@{JOIN_METHOD=HASH_JOIN} , aber nicht beides. |
HASH_JOIN_BUILD_SIDE |
BUILD_LEFT BUILD_RIGHT |
Gibt an, welche Seite des Hash-Joins als Build-Seite verwendet wird. Kann nur mit JOIN_METHOD=HASH_JOIN verwendet werden |
BATCH_MODE |
TRUE (default) FALSE |
Wird verwendet, um einen Batch-Join für einen zeilenweisen Apply-Join zu deaktivieren. Kann nur mit JOIN_METHOD=APPLY_JOIN verwendet werden.
|
Join-Methoden
Join-Methoden sind spezifische Implementierungen der verschiedenen logischen Join-Typen. Einige Join-Methoden sind nur für bestimmte Join-Typen verfügbar. Die Auswahl der zu verwendenden Join-Methode hängt von den Besonderheiten Ihrer Abfrage und der abgefragten Daten ab. Die beste Möglichkeit herauszufinden, ob eine bestimmte Join-Methode bei der Leistung Ihrer Abfrage hilfreich ist, besteht darin, die Methode auszuprobieren und den resultierenden Abfrageausführungsplan anzuzeigen. Weitere Informationen finden Sie unter Abfrageausführungsoperatoren, insbesondere in den Abschnitten zu den Operatoren "Apply" und "Hash Join".
Join-Methode | Beschreibung | Operanden |
---|---|---|
HASH_JOIN |
Der Hash-Join-Operator erstellt eine Hash-Tabelle von einer Seite (der Build-Seite) und Prüfungen in der Hash-Tabelle für alle Elemente auf der anderen Seite (der Prüfungsseite). | Für verschiedene Join-Typen werden verschiedene Varianten verwendet. Zeigen Sie den Abfrage-Ausführungsplan für Ihre Abfrage an, um zu sehen, welche Variante verwendet wird. Weitere Informationen erhalten Sie im Abschnitt "Hash-Join-Operator". |
APPLY_JOIN |
Der Apply-Join-Operator ruft jedes Element von einer Seite (der Eingabeseite) ab und wertet die Unterabfrage auf der anderen Seite (der Kartenseite) unter Verwendung der Werte des Elements von der Eingabeseite aus. | Für verschiedene Join-Typen werden verschiedene Varianten verwendet. Cross Apply wird für den Inner Join verwendet und Outer Apply für Left Joins. Weitere Informationen finden Sie in den Abschnitten zu den Operatoren Cross Apply und Outer Apply. |
[INNER] JOIN
Ein INNER JOIN
(oder einfach nur JOIN
) berechnet effektiv das kartesische Produkt der beiden from_item
-Elemente und verwirft alle Zeilen, die nicht mit der Join-Bedingung übereinstimmen. "Effektiv" bedeutet, dass ein INNER JOIN
implementiert werden kann, ohne tatsächlich das kartesische Produkt zu berechnen.
FROM A INNER JOIN B ON A.w = B.y
Table A Table B Result
+-------+ +-------+ +---------------+
| w | x | * | y | z | = | w | x | y | z |
+-------+ +-------+ +---------------+
| 1 | a | | 2 | k | | 2 | b | 2 | k |
| 2 | b | | 3 | m | | 3 | c | 3 | m |
| 3 | c | | 3 | n | | 3 | c | 3 | n |
| 3 | d | | 4 | p | | 3 | d | 3 | m |
+-------+ +-------+ | 3 | d | 3 | n |
+---------------+
FROM A INNER JOIN B USING (x)
Table A Table B Result
+-------+ +-------+ +-----------+
| x | y | * | x | z | = | x | y | z |
+-------+ +-------+ +-----------+
| 1 | a | | 2 | k | | 2 | b | k |
| 2 | b | | 3 | m | | 3 | c | m |
| 3 | c | | 3 | n | | 3 | c | n |
| 3 | d | | 4 | p | | 3 | d | m |
+-------+ +-------+ | 3 | d | n |
+-----------+
Beispiel
Diese Abfrage führt ein INNER JOIN
für die Tabellen Roster
und TeamMascot
aus.
SELECT Roster.LastName, TeamMascot.Mascot
FROM Roster JOIN TeamMascot ON Roster.SchoolID = TeamMascot.SchoolID;
+---------------------------+
| LastName | Mascot |
+---------------------------+
| Adams | Jaguars |
| Buchanan | Lakers |
| Coolidge | Lakers |
| Davis | Knights |
+---------------------------+
CROSS JOIN
CROSS JOIN
gibt das kartesische Produkt der beiden from_item
-Elemente zurück. Anders gesagt: Jede Zeile aus dem ersten from_item
-Element wird mit jeder Zeile aus dem zweiten from_item
-Element kombiniert.
Wenn die Zeilen der beiden from_item
-Elemente unabhängig sind, hat das Ergebnis M * N Zeilen, wenn ein from_item
-Element M Zeilen und das andere N Zeilen umfasst. Dies gilt auch für den Fall, dass eines der from_item
-Elemente null Zeilen enthält.
FROM A CROSS JOIN B
Table A Table B Result
+-------+ +-------+ +---------------+
| w | x | * | y | z | = | w | x | y | z |
+-------+ +-------+ +---------------+
| 1 | a | | 2 | c | | 1 | a | 2 | c |
| 2 | b | | 3 | d | | 1 | a | 3 | d |
+-------+ +-------+ | 2 | b | 2 | c |
| 2 | b | 3 | d |
+---------------+
Sie können korrelierte CROSS JOIN
s verwenden, um ARRAY
-Spalten zu vereinfachen. In diesem Fall variieren die Zeilen des zweiten from_item
-Elements für jede Zeile des ersten from_item
-Elements.
FROM A CROSS JOIN A.y
Table A Result
+-------------------+ +-----------+
| w | x | y | -> | w | x | y |
+-------------------+ +-----------+
| 1 | a | [P, Q] | | 1 | a | P |
| 2 | b | [R, S, T] | | 1 | a | Q |
+-------------------+ | 2 | b | R |
| 2 | b | S |
| 2 | b | T |
+-----------+
CROSS JOIN
s können explizit so geschrieben werden:
FROM a CROSS JOIN b
Oder implizit als durch Kommas getrennter Cross-Join:
FROM a, b
Durch Kommas getrennte Cross Joins dürfen nicht in runde Klammern geschrieben werden:
FROM a CROSS JOIN (b, c) // INVALID
Weitere Informationen zum Verhalten eines durch Komma getrennten Kreuzprodukts in einer Join-Sequenz finden Sie unter JOIN-Sequenzen.
Beispiele
Diese Abfrage führt einen expliziten CROSS JOIN
für die Tabellen Roster
und TeamMascot
aus.
SELECT Roster.LastName, TeamMascot.Mascot
FROM Roster CROSS JOIN TeamMascot;
+---------------------------+
| LastName | Mascot |
+---------------------------+
| Adams | Jaguars |
| Adams | Knights |
| Adams | Lakers |
| Adams | Mustangs |
| Buchanan | Jaguars |
| Buchanan | Knights |
| Buchanan | Lakers |
| Buchanan | Mustangs |
| ... |
+---------------------------+
Diese Abfrage führt einen durch Kommas getrennten Cross Join durch, der dieselben Ergebnisse wie der explizite CROSS JOIN
oben liefert:
SELECT Roster.LastName, TeamMascot.Mascot
FROM Roster, TeamMascot;
FULL [OUTER] JOIN
Ein FULL OUTER JOIN
(oder einfach nur FULL JOIN
) gibt alle Felder für alle Zeilen in beiden from_item
-Elementen zurück, die mit der Join-Bedingung übereinstimmen.
FULL
gibt an, dass alle Zeilen aus beiden from_item
-Elementen zurückgegeben werden, selbst wenn sie die Join-Bedingung nicht erfüllen.
OUTER
gibt Folgendes an: Wenn eine bestimmte Zeile aus einem from_item
mit keiner Zeile im anderen from_item
übereinstimmt, gibt die Zeile den Wert NULL für alle Spalten aus dem anderen from_item
zurück.
FROM A FULL OUTER JOIN B ON A.w = B.y
Table A Table B Result
+-------+ +-------+ +---------------------------+
| w | x | * | y | z | = | w | x | y | z |
+-------+ +-------+ +---------------------------+
| 1 | a | | 2 | k | | 1 | a | NULL | NULL |
| 2 | b | | 3 | m | | 2 | b | 2 | k |
| 3 | c | | 3 | n | | 3 | c | 3 | m |
| 3 | d | | 4 | p | | 3 | c | 3 | n |
+-------+ +-------+ | 3 | d | 3 | m |
| 3 | d | 3 | n |
| NULL | NULL | 4 | p |
+---------------------------+
FROM A FULL OUTER JOIN B USING (x)
Table A Table B Result
+-------+ +-------+ +--------------------+
| x | y | * | x | z | = | x | y | z |
+-------+ +-------+ +--------------------+
| 1 | a | | 2 | k | | 1 | a | NULL |
| 2 | b | | 3 | m | | 2 | b | k |
| 3 | c | | 3 | n | | 3 | c | m |
| 3 | d | | 4 | p | | 3 | c | n |
+-------+ +-------+ | 3 | d | m |
| 3 | d | n |
| 4 | NULL | p |
+--------------------+
Beispiel
Diese Abfrage führt ein FULL JOIN
für die Tabellen Roster
und TeamMascot
aus.
SELECT Roster.LastName, TeamMascot.Mascot
FROM Roster FULL JOIN TeamMascot ON Roster.SchoolID = TeamMascot.SchoolID;
+---------------------------+
| LastName | Mascot |
+---------------------------+
| Adams | Jaguars |
| Buchanan | Lakers |
| Coolidge | Lakers |
| Davis | Knights |
| Eisenhower | NULL |
| NULL | Mustangs |
+---------------------------+
LEFT [OUTER] JOIN
Das Ergebnis eines LEFT OUTER JOIN
(oder einfach nur LEFT JOIN
) für zwei from_item
-Elemente behält immer alle Zeilen des from_item
auf der linken Seite des JOIN
-Vorgangs bei, selbst wenn keine Zeilen im from_item
auf der rechten Seite dem JOIN-Prädikat entsprechen.
LEFT
gibt an, dass alle Zeilen des linken from_item
zurückgegeben werden. Wenn eine bestimmte Zeile des linken from_item
mit keiner Zeile im rechten from_item
übereinstimmt, gibt die Zeile für alle Spalten aus dem rechten from_item
den Wert NULL zurück. Zeilen vom rechten from_item
, die mit keiner Zeile im linken from_item
übereinstimmen, werden verworfen.
FROM A LEFT OUTER JOIN B ON A.w = B.y
Table A Table B Result
+-------+ +-------+ +---------------------------+
| w | x | * | y | z | = | w | x | y | z |
+-------+ +-------+ +---------------------------+
| 1 | a | | 2 | k | | 1 | a | NULL | NULL |
| 2 | b | | 3 | m | | 2 | b | 2 | k |
| 3 | c | | 3 | n | | 3 | c | 3 | m |
| 3 | d | | 4 | p | | 3 | c | 3 | n |
+-------+ +-------+ | 3 | d | 3 | m |
| 3 | d | 3 | n |
+---------------------------+
FROM A LEFT OUTER JOIN B USING (x)
Table A Table B Result
+-------+ +-------+ +--------------------+
| x | y | * | x | z | = | x | y | z |
+-------+ +-------+ +--------------------+
| 1 | a | | 2 | k | | 1 | a | NULL |
| 2 | b | | 3 | m | | 2 | b | k |
| 3 | c | | 3 | n | | 3 | c | m |
| 3 | d | | 4 | p | | 3 | c | n |
+-------+ +-------+ | 3 | d | m |
| 3 | d | n |
+--------------------+
Beispiel
Diese Abfrage führt ein LEFT JOIN
für die Tabellen Roster
und TeamMascot
aus.
SELECT Roster.LastName, TeamMascot.Mascot
FROM Roster LEFT JOIN TeamMascot ON Roster.SchoolID = TeamMascot.SchoolID;
+---------------------------+
| LastName | Mascot |
+---------------------------+
| Adams | Jaguars |
| Buchanan | Lakers |
| Coolidge | Lakers |
| Davis | Knights |
| Eisenhower | NULL |
+---------------------------+
RIGHT [OUTER] JOIN
Das Ergebnis von RIGHT OUTER JOIN
(oder einfach nur RIGHT JOIN
) ist dem Ergebnis von LEFT OUTER JOIN
ähnlich und verhält sich symmetrisch.
FROM A RIGHT OUTER JOIN B ON A.w = B.y
Table A Table B Result
+-------+ +-------+ +---------------------------+
| w | x | * | y | z | = | w | x | y | z |
+-------+ +-------+ +---------------------------+
| 1 | a | | 2 | k | | 2 | b | 2 | k |
| 2 | b | | 3 | m | | 3 | c | 3 | m |
| 3 | c | | 3 | n | | 3 | c | 3 | n |
| 3 | d | | 4 | p | | 3 | d | 3 | m |
+-------+ +-------+ | 3 | d | 3 | n |
| NULL | NULL | 4 | p |
+---------------------------+
FROM A RIGHT OUTER JOIN B USING (x)
Table A Table B Result
+-------+ +-------+ +--------------------+
| x | y | * | x | z | = | x | y | z |
+-------+ +-------+ +--------------------+
| 1 | a | | 2 | k | | 2 | b | k |
| 2 | b | | 3 | m | | 3 | c | m |
| 3 | c | | 3 | n | | 3 | c | n |
| 3 | d | | 4 | p | | 3 | d | m |
+-------+ +-------+ | 3 | d | n |
| 4 | NULL | p |
+--------------------+
Beispiel
Diese Abfrage führt ein RIGHT JOIN
für die Tabellen Roster
und TeamMascot
aus.
SELECT Roster.LastName, TeamMascot.Mascot
FROM Roster RIGHT JOIN TeamMascot ON Roster.SchoolID = TeamMascot.SchoolID;
+---------------------------+
| LastName | Mascot |
+---------------------------+
| Adams | Jaguars |
| Buchanan | Lakers |
| Coolidge | Lakers |
| Davis | Knights |
| NULL | Mustangs |
+---------------------------+
ON-Klausel
Die ON
-Klausel enthält einen bool_expression
. Eine kombinierte Zeile (das Ergebnis aus dem Join von zwei Zeilen) stimmt mit der Join-Bedingung überein, wenn bool_expression
den Wert TRUE zurückgibt.
FROM A JOIN B ON A.x = B.x
Table A Table B Result (A.x, B.x)
+---+ +---+ +-------+
| x | * | x | = | x | x |
+---+ +---+ +-------+
| 1 | | 2 | | 2 | 2 |
| 2 | | 3 | | 3 | 3 |
| 3 | | 4 | +-------+
+---+ +---+
Beispiel
Diese Abfrage führt ein INNER JOIN
für die Tabellen Roster
und TeamMascot
aus.
SELECT Roster.LastName, TeamMascot.Mascot
FROM Roster JOIN TeamMascot ON Roster.SchoolID = TeamMascot.SchoolID;
+---------------------------+
| LastName | Mascot |
+---------------------------+
| Adams | Jaguars |
| Buchanan | Lakers |
| Coolidge | Lakers |
| Davis | Knights |
+---------------------------+
USING-Klausel
Die USING
-Klausel erfordert eine column_list
einer oder mehrerer Spalten, die in beiden Eingabetabellen vorkommen. Sie führt einen Übereinstimmungsvergleich an dieser Spalte durch. Die Zeilen stimmen mit der Join-Bedingung überein, wenn der Übereinstimmungsvergleich den Wert TRUE zurückgibt.
FROM A JOIN B USING (x)
Table A Table B Result
+---+ +---+ +---+
| x | * | x | = | x |
+---+ +---+ +---+
| 1 | | 2 | | 2 |
| 2 | | 3 | | 3 |
| 3 | | 4 | +---+
+---+ +---+
Beispiel
Diese Abfrage führt ein INNER JOIN
für die Tabellen Roster
und TeamMascot
aus.
Diese Anweisung gibt die Zeilen aus Roster
und TeamMascot
zurück, wobei Roster.SchooldID
mit TeamMascot.SchooldID
identisch ist. Die Ergebnisse enthalten eine einzelne SchooldID
-Spalte.
SELECT * FROM Roster INNER JOIN TeamMascot USING (SchoolID);
+----------------------------------------+
| SchoolID | LastName | Mascot |
+----------------------------------------+
| 50 | Adams | Jaguars |
| 52 | Buchanan | Lakers |
| 52 | Coolidge | Lakers |
| 51 | Davis | Knights |
+----------------------------------------+
Äquivalenz von ON und USING
Die Schlüsselwörter ON
und USING
sind nicht äquivalent, aber ähnlich.
ON
gibt mehrere Spalten zurück, während USING
eine zurückgibt.
FROM A JOIN B ON A.x = B.x
FROM A JOIN B USING (x)
Table A Table B Result ON Result USING
+---+ +---+ +-------+ +---+
| x | * | x | = | x | x | | x |
+---+ +---+ +-------+ +---+
| 1 | | 2 | | 2 | 2 | | 2 |
| 2 | | 3 | | 3 | 3 | | 3 |
| 3 | | 4 | +-------+ +---+
+---+ +---+
Obwohl ON
und USING
nicht äquivalent sind, geben sie dieselben Ergebnisse zurück, wenn Sie die zurückzugebenden Spalten angeben.
SELECT x FROM A JOIN B USING (x);
SELECT A.x FROM A JOIN B ON A.x = B.x;
Table A Table B Result
+---+ +---+ +---+
| x | * | x | = | x |
+---+ +---+ +---+
| 1 | | 2 | | 2 |
| 2 | | 3 | | 3 |
| 3 | | 4 | +---+
+---+ +---+
JOIN-Sequenzen
Die FROM
-Klausel kann mehrere JOIN
-Vorgänge in einer Sequenz enthalten. JOIN
s sind von links nach rechts gebunden. Beispiel:
FROM A JOIN B USING (x) JOIN C USING (x)
-- A JOIN B USING (x) = result_1
-- result_1 JOIN C USING (x) = result_2
-- result_2 = return value
Sie können auch Klammern verwenden, um JOIN
s zu gruppieren:
FROM ( (A JOIN B USING (x)) JOIN C USING (x) )
-- A JOIN B USING (x) = result_1
-- result_1 JOIN C USING (x) = result_2
-- result_2 = return value
Mit Klammern können Sie JOIN
s so gruppieren, dass sie in einer anderen Reihenfolge gebunden sind:
FROM ( A JOIN (B JOIN C USING (x)) USING (x) )
-- B JOIN C USING (x) = result_1
-- A JOIN result_1 = result_2
-- result_2 = return value
Wenn durch Komma getrennte Kreuzprodukte in einer Abfrage mit einer JOIN-Sequenz vorhanden sind, werden diese von links nach rechts gruppiert wie andere JOIN
-Typen auch:
FROM A JOIN B USING (x) JOIN C USING (x), D
-- A JOIN B USING (x) = result_1
-- result_1 JOIN C USING (x) = result_2
-- result_2 CROSS JOIN D = return value
Nach einem durch Kommas getrennten Join darf kein RIGHT JOIN
oder FULL JOIN
stehen:
FROM A, B RIGHT JOIN C ON TRUE // INVALID
FROM A, B FULL JOIN C ON TRUE // INVALID
FROM A, B JOIN C ON TRUE // VALID
WHERE-Klausel
WHERE bool_expression
Die Klausel WHERE
filtert die Ergebnisse der Klausel FROM
.
Es werden nur Zeilen einbezogen, deren bool_expression
als TRUE
ausgewertet wird. Zeilen, deren bool_expression
als NULL
oder FALSE
ausgewertet wird, werden verworfen.
Die Auswertung einer Abfrage mit einer WHERE
-Klausel wird in der Regel in dieser Reihenfolge ausgeführt:
FROM
WHERE
GROUP BY
und AggregationHAVING
WINDOW
QUALIFY
DISTINCT
ORDER BY
LIMIT
Die WHERE
-Klausel kann nur auf Spalten verweisen, die über die FROM
-Klausel verfügbar sind, und kann nicht auf SELECT
-Listenaliasse verweisen.
Beispiele
Diese Abfrage gibt alle Zeilen aus der Tabelle Roster
zurück, in denen die Spalte SchoolID
den Wert 52
enthält:
SELECT * FROM Roster
WHERE SchoolID = 52;
bool_expression
kann mehrere Unterbedingungen enthalten:
SELECT * FROM Roster
WHERE STARTS_WITH(LastName, "Mc") OR STARTS_WITH(LastName, "Mac");
Ausdrücke in einem INNER JOIN
haben einen entsprechenden Ausdruck in der WHERE
-Klausel. Eine Abfrage mit INNER
JOIN
und ON
hat beispielsweise einen entsprechenden Ausdruck mit CROSS JOIN
und WHERE
. Die folgenden beiden Abfragen sind beispielsweise äquivalent:
SELECT Roster.LastName, TeamMascot.Mascot
FROM Roster INNER JOIN TeamMascot
ON Roster.SchoolID = TeamMascot.SchoolID;
SELECT Roster.LastName, TeamMascot.Mascot
FROM Roster CROSS JOIN TeamMascot
WHERE Roster.SchoolID = TeamMascot.SchoolID;
GROUP BY-Klausel
GROUP BY expression [, ...]
Die GROUP BY
-Klausel fasst Zeilen in einer Tabelle mit nicht eindeutigen Werten für expression
in der GROUP BY
-Klausel zusammen. Wenn in der Quelltabelle mehrere Zeilen nicht eindeutige Werte für expression
haben, werden diese mit der GROUP BY
-Klausel zu einer einzelnen Zeile zusammengefasst. GROUP BY
wird im Allgemeinen verwendet, wenn in der SELECT
-Liste Aggregatfunktionen vorhanden sind oder Redundanzen in der Ausgabe ausgeschlossen werden sollen. Der Datentyp von expression
muss gruppierbar sein.
Beispiel:
SELECT SUM(PointsScored), LastName
FROM PlayerStats
GROUP BY LastName;
Die GROUP BY
-Klausel kann sich auf Ausdrucksnamen in der SELECT
-Liste beziehen. Die GROUP BY
-Klausel lässt auch die ordinalen Verweise auf Ausdrücke in der SELECT
-Liste unter Verwendung von Ganzzahlen zu. Der Wert 1
verweist auf den ersten Ausdruck in der SELECT
-Liste, der Wert 2
auf den zweiten Ausdruck usw. Sie können Ordinalzahlen und Ausdrucksnamen in der Ausdrucksliste kombinieren.
Beispiel:
SELECT SUM(PointsScored), LastName, FirstName
FROM PlayerStats
GROUP BY LastName, FirstName;
Die Abfrage oben entspricht der folgenden Abfrage:
SELECT SUM(PointsScored), LastName, FirstName
FROM PlayerStats
GROUP BY 2, FirstName;
GROUP BY
-Klauseln können auch auf Aliasse verweisen. Wenn eine Abfrage Aliasse in der SELECT
-Klausel enthält, überschreiben diese Aliasse die Namen in der entsprechenden FROM
-Klausel.
Beispiel:
SELECT SUM(PointsScored), LastName as last_name
FROM PlayerStats
GROUP BY last_name;
HAVING-Klausel
HAVING bool_expression
Die Klausel HAVING
filtert die Ergebnisse, die von GROUP BY
oder der Aggregation generiert wurden. GROUP BY
oder eine Aggregation muss in der Abfrage vorhanden sein. Wenn die Aggregation vorhanden ist, wird die HAVING
-Klausel einmal für jede aggregierte Zeile in der Ergebnismenge ausgewertet.
Es werden nur Zeilen einbezogen, deren bool_expression
als TRUE
ausgewertet wird. Zeilen, deren bool_expression
als NULL
oder FALSE
ausgewertet wird, werden verworfen.
Die Auswertung einer Abfrage mit einer HAVING
-Klausel wird in der Regel in dieser Reihenfolge ausgeführt:
FROM
WHERE
GROUP BY
und AggregationHAVING
WINDOW
QUALIFY
DISTINCT
ORDER BY
LIMIT
Die HAVING
-Klausel kann auf Spalten, die über die FROM
-Klausel verfügbar sind, sowie auf Aliasse in der SELECT
-Liste verweisen. Ausdrücke, auf die in der HAVING
-Klausel verwiesen wird, müssen entweder in der GROUP BY
-Klausel auftreten oder das Ergebnis einer Aggregatfunktion sein:
SELECT LastName
FROM Roster
GROUP BY LastName
HAVING SUM(PointsScored) > 15;
Wenn eine Abfrage Aliasse in der SELECT
-Klausel enthält, setzen diese Aliasse die Namen in einer FROM
-Klausel außer Kraft.
SELECT LastName, SUM(PointsScored) AS ps
FROM Roster
GROUP BY LastName
HAVING ps > 0;
Obligatorische Aggregation
Eine Aggregation muss nicht in der HAVING
-Klausel selbst vorhanden sein, sie muss aber mindestens in einer der folgenden Formen vorliegen:
Aggregatfunktion in der SELECT
-Liste
SELECT LastName, SUM(PointsScored) AS total
FROM PlayerStats
GROUP BY LastName
HAVING total > 15;
Aggregatfunktion in der HAVING-Klausel
SELECT LastName
FROM PlayerStats
GROUP BY LastName
HAVING SUM(PointsScored) > 15;
Aggregation in der SELECT
-Liste und HAVING
-Klausel
Wenn Aggregatfunktionen sowohl in der SELECT
-Liste als auch der HAVING
-Klausel vorhanden sind, müssen die Aggregatfunktionen und die Spalten, auf die sie verweisen, nicht miteinander übereinstimmen. Im folgenden Beispiel unterscheiden sich die beiden Aggregatfunktionen COUNT()
und SUM()
voneinander; sie verwenden auch unterschiedliche Spalten.
SELECT LastName, COUNT(*)
FROM PlayerStats
GROUP BY LastName
HAVING SUM(PointsScored) > 15;
ORDER BY-Klausel
ORDER BY expression [{ ASC | DESC }] [, ...]
Die ORDER BY
-Klausel gibt eine Spalte oder einen Ausdruck als Sortierkriterium für die Ergebnismenge an. Wenn keine ORDER BY-Klausel vorhanden ist, ist die Reihenfolge der Ergebnisse einer Abfrage nicht festgelegt. Spaltenaliasse einer FROM
-Klausel oder SELECT
-Liste sind zulässig. Wenn eine Abfrage Aliasse in der SELECT
-Klausel enthält, überschreiben diese Aliasse die Namen in der entsprechenden FROM
-Klausel.
Optionale Klauseln
ASC | DESC
: sortiert die Ergebnisse in aufsteigender oder absteigender Reihenfolge vonexpression
-Werten.ASC
ist der Standardwert.
Beispiele
Verwenden Sie die Standardsortierreihenfolge (aufsteigend).
SELECT x, y
FROM (SELECT 1 AS x, true AS y UNION ALL
SELECT 9, true)
ORDER BY x;
+------+-------+
| x | y |
+------+-------+
| 1 | true |
| 9 | true |
+------+-------+
Verwenden Sie die absteigende Sortierreihenfolge.
SELECT x, y
FROM (SELECT 1 AS x, true AS y UNION ALL
SELECT 9, true)
ORDER BY x DESC;
+------+-------+
| x | y |
+------+-------+
| 9 | true |
| 1 | true |
+------+-------+
Eine Sortierung nach mehreren Spalten ist möglich. Im folgenden Beispiel wird die Ergebnismenge zuerst nach SchoolID
und dann nach LastName
sortiert:
SELECT LastName, PointsScored, OpponentID
FROM PlayerStats
ORDER BY SchoolID, LastName;
Die folgenden Regeln gelten beim Sortieren von Werten:
- NULL-Werte: In Bezug auf die
ORDER BY
-Klausel sind NULL-Werte der kleinstmögliche Wert. NULL-Werte werden also inASC
-Sortierungen als Erstes und inDESC
-Sortierungen als Letztes angezeigt. - Gleitkommadatentypen: Weitere Informationen zur Sortierung und Gruppierung für diese Datentypen finden Sie unter Gleitkommasemantik.
Wenn die ORDER BY
-Klausel gemeinsam mit Set-Operatoren verwendet wird, gilt sie für die Ergebnismenge der gesamten Abfrage und nicht nur für die nächstgelegene SELECT
-Anweisung. Aus diesem Grund kann es nützlich sein (obwohl nicht erforderlich), runde Klammern zu verwenden, um die Abgrenzung des Bereichs von ORDER
BY
anzuzeigen.
Die folgende Abfrage ohne runde Klammern:
SELECT * FROM Roster
UNION ALL
SELECT * FROM TeamMascot
ORDER BY SchoolID;
entspricht der folgenden Abfrage mit runden Klammern:
( SELECT * FROM Roster
UNION ALL
SELECT * FROM TeamMascot )
ORDER BY SchoolID;
Sie entspricht allerdings nicht der folgenden Abfrage, bei der die ORDER BY
-Klausel nur für die zweite SELECT
-Anweisung gilt:
SELECT * FROM Roster
UNION ALL
( SELECT * FROM TeamMascot
ORDER BY SchoolID );
Sie können auch Ganzzahlliterale als Spaltenverweise in ORDER BY
-Klauseln verwenden. Ein Ganzzahlliteral wird zur Ordnungszahl (z. B. Zählung beginnt bei 1) in der SELECT
-Liste.
Die folgenden beiden Abfragen entsprechen beispielsweise einander:
SELECT SUM(PointsScored), LastName
FROM PlayerStats
ORDER BY LastName;
SELECT SUM(PointsScored), LastName
FROM PlayerStats
ORDER BY 2;
COLLATE
Mit der Klausel COLLATE
können Sie verfeinern, wie Daten nach einer ORDER
BY
-Klausel sortiert werden sollen. Die Sortierung bezieht sich auf eine Reihe von Regeln, die bestimmen, wie STRINGs gemäß den Konventionen und Standards einer bestimmten Sprache, Region oder eines bestimmten Landes verglichen werden. Diese Regeln definieren möglicherweise die richtige Zeichenfolge mit Optionen zum Festlegen der Groß- und Kleinschreibung.
Sie können Ihrer Anweisung wie dargestellt eine Sortierung hinzufügen:
SELECT ...
FROM ...
ORDER BY value COLLATE collation_string
Ein collation_string
enthält einen collation_name
und kann ein optionales collation_attribute
als Suffix haben, das durch einen Doppelpunkt getrennt ist. Der collation_string
ist ein Literal oder ein Parameter. Normalerweise besteht dieser Name aus zwei Buchstaben, die für die Sprache stehen. Optional können ein Unterstrich und zwei Buchstaben für die Region folgen, z. B. en_US
. Diese Namen werden vom Common Locale Data Repository (CLDR) definiert.
Eine Anweisung kann auch einen collation_name
aus unicode
enthalten. Dieser Wert bedeutet, dass die Anweisung Daten mit der Standard-Unicode-Sortierung zurückgeben soll.
Zusätzlich zu collation_name
kann ein collation_string
ein optionales collation_attribute
als Suffix enthalten, das durch einen Doppelpunkt getrennt ist. Dieses Attribut gibt an, ob die Datenvergleiche die Groß- und Kleinschreibung berücksichtigen sollen. Zulässige Werte sind cs
, wenn die Groß- und Kleinschreibung berücksichtigt, und ci
, wenn die Groß- und Kleinschreibung nicht berücksichtigt werden soll. Wenn collation_attribute
nicht angegeben ist, werden die CLDR-Standardwerte verwendet.
Beispiele
Sortierergebnisse, die Englisch (Kanada) verwenden:
SELECT Place
FROM Locations
ORDER BY Place COLLATE "en_CA"
Sortierergebnisse, die einen Parameter verwenden:
#@collate_param = "arg_EG"
SELECT Place
FROM Locations
ORDER BY Place COLLATE @collate_param
Mehrere COLLATE
-Klauseln in einer Anweisung verwenden:
SELECT APlace, BPlace, CPlace
FROM Locations
ORDER BY APlace COLLATE "en_US" ASC,
BPlace COLLATE "ar_EG" DESC,
CPlace COLLATE "en" DESC
Groß- und Kleinschreibung bei der Sortierung nicht berücksichtigen:
SELECT Place
FROM Locations
ORDER BY Place COLLATE "en_US:ci"
Standardmäßige Unicode-Sortierung ohne Berücksichtigung von Groß- und Kleinschreibung:
SELECT Place
FROM Locations
ORDER BY Place COLLATE "unicode:ci"
Set-Operatoren
UNION { ALL | DISTINCT } | INTERSECT { ALL | DISTINCT } | EXCEPT { ALL | DISTINCT }
Beim Einrichten von Operatoren werden Ergebnisse aus zwei oder mehr Eingabeabfragen zu einer einzigen Ergebnismenge kombiniert. Sie müssen ALL
oder DISTINCT
angeben. Wenn Sie ALL
angeben, werden alle Zeilen beibehalten. Wenn Sie DISTINCT
angeben, werden doppelte Zeilen verworfen.
Wenn eine gegebene Zeile R genau m Mal in der ersten Eingabeabfrage und n Mal in der zweiten Eingabeabfrage vorhanden ist (m >= 0, n >= 0), geschieht Folgendes:
- Für
UNION ALL
wird R genau m + n-mal im Ergebnis angezeigt. - Bei
INTERSECT ALL
wird R genauMIN(m, n)
im Ergebnis angezeigt. - Bei
EXCEPT ALL
wird R genauMAX(m - n, 0)
im Ergebnis angezeigt. - Bei
UNION DISTINCT
wirdDISTINCT
nach der Berechnung vonUNION
berechnet. Deshalb wird R genau einmal angezeigt. - Bei
INTERSECT DISTINCT
wirdDISTINCT
berechnet, nachdem das obige Ergebnis berechnet wurde. - Bei
EXCEPT DISTINCT
wird die Zeile R einmal in der Ausgabe angezeigt, wenn m > 0 und n = 0 ist. - Bei mehr als zwei Eingabeabfragen werden die oben genannten Vorgänge verallgemeinert und die Ausgabe ist die gleiche, wie wenn die Eingaben inkrementell von links nach rechts kombiniert werden würden.
Es gelten die folgenden Regeln:
- Für andere Set-Operationen als
UNION ALL
müssen alle Spaltentypen den Gleichheitsvergleich unterstützen. - Die Eingabeabfragen auf jeder Seite des Operators müssen die gleiche Anzahl an Spalten zurückgeben.
- Die Operatoren ordnen die Spalten, die von jeder Eingabeabfrage zurückgegeben werden, entsprechend den Positionen der Spalten in den jeweiligen
SELECT
-Listen paarweise an. Das heißt, dass die erste Spalte in der ersten Eingabeabfrage mit der ersten Spalte in der zweiten Eingabeabfrage paarweise angeordnet wird. - In der Ergebnismenge werden immer die Spaltennamen aus der ersten Eingabeabfrage verwendet.
- In der Ergebnismenge werden immer die Obertypen der Eingabetypen in den entsprechenden Spalten verwendet, d. h. paarweise angeordnete Spalten müssen ebenfalls entweder den gleichen Datentyp oder einen gemeinsamen Obertyp haben.
- Trennen Sie unterschiedliche Set-Operationen durch Klammern. Set-Operationen wie
UNION ALL
undUNION DISTINCT
sind in diesem Fall unterschiedlich. Wenn sich durch die Anweisung dieselbe Set-Operation nur wiederholt, sind keine Klammern erforderlich.
Beispiele:
query1 UNION ALL (query2 UNION DISTINCT query3)
query1 UNION ALL query2 UNION ALL query3
Ungültig:
query1 UNION ALL query2 UNION DISTINCT query3
query1 UNION ALL query2 INTERSECT ALL query3; // INVALID.
UNION
Der Operator UNION
kombiniert die Ergebnismengen von zwei oder mehr Eingabeabfragen, indem Spalten der Ergebnismenge von jeder Abfrage paarweise angeordnet und vertikal verkettet werden.
INTERSECT
Der Operator INTERSECT
gibt Zeilen zurück, die in den Ergebnismengen der linken und rechten Eingabeabfragen gefunden werden. Anders als bei EXCEPT
spielt die Positionierung der Eingabeabfragen (links oder rechts des Operators INTERSECT
) hier keine Rolle.
EXCEPT
Der Operator EXCEPT
gibt Zeilen aus der linken Eingabeabfrage zurück, die in der rechten Eingabeabfrage nicht vorhanden sind.
Beispiel:
SELECT * FROM UNNEST(ARRAY<int64>[1, 2, 3]) AS number
EXCEPT DISTINCT SELECT 1;
+--------+
| number |
+--------+
| 2 |
| 3 |
+--------+
LIMIT- und OFFSET-Klauseln
LIMIT count [ OFFSET skip_rows ]
LIMIT
gibt einen nicht negativen count
-Wert des Typs INT64 an und es werden nicht mehr als count
-Zeilen zurückgegeben. LIMIT
0
gibt 0 Zeilen zurück.
Wenn eine Set-Operation vorliegt, wird LIMIT
angewendet, nachdem die Set-Operation ausgewertet wurde.
OFFSET
gibt eine nicht negative Anzahl von Zeilen an, die übersprungen werden sollen, bevor LIMIT
angewendet wird. skip_rows
ist vom Typ INT64.
Diese Klauseln akzeptieren nur Literal- oder Parameterwerte. Die von LIMIT
und OFFSET
zurückgegebenen Zeilen werden nur angegeben, wenn diese Operatoren nach ORDER BY
verwendet werden.
Beispiele:
SELECT *
FROM UNNEST(ARRAY<STRING>['a', 'b', 'c', 'd', 'e']) AS letter
ORDER BY letter ASC LIMIT 2
+---------+
| letter |
+---------+
| a |
| b |
+---------+
SELECT *
FROM UNNEST(ARRAY<STRING>['a', 'b', 'c', 'd', 'e']) AS letter
ORDER BY letter ASC LIMIT 3 OFFSET 1
+---------+
| letter |
+---------+
| b |
| c |
| d |
+---------+
WITH-Klausel
WITH with_clause with_clause: with_subquery[, ...] with_subquery: with_query_name AS ( query_expr )
Die Klausel WITH
bindet die Ergebnisse einer oder mehrerer benannter Unterabfragen an temporäre Tabellennamen.
Jeder eingeführte Tabellenname ist in nachfolgenden SELECT
-Ausdrücken innerhalb desselben Abfrageausdrucks sichtbar. Dazu gehören die folgenden Arten von SELECT
-Ausdrücken:
- Beliebige
SELECT
-Ausdrücke in nachfolgendenWITH
-Bindungen SELECT
-Ausdrücke der obersten Ebene im Abfrageausdruck auf beiden Seiten eines festgelegten Operators wieUNION
SELECT
-Ausdrücke in Unterabfragen innerhalb desselben Abfrageausdrucks
Beispiel:
WITH subQ1 AS (SELECT SchoolID FROM Roster),
subQ2 AS (SELECT OpponentID FROM PlayerStats)
SELECT * FROM subQ1
UNION ALL
SELECT * FROM subQ2
WITH
wird in einer Unterabfrage nicht unterstützt.
Dies gibt einen Fehler zurück:
SELECT account
FROM (
WITH result AS (SELECT * FROM NPCs)
SELECT *
FROM result)
Die WITH
-Klausel wird in DML-Anweisungen nicht unterstützt.
Temporäre Tabellen, die von der WITH
-Klausel definiert werden, werden im Arbeitsspeicher gespeichert.
Cloud Spanner SQL weist allen von einer Abfrage erstellten temporären Tabellen dynamisch Speicher zu. Wenn die verfügbaren Ressourcen nicht ausreichen, schlägt die Abfrage fehl.
Aliase verwenden
Ein Alias ist ein temporärer Name für eine Tabelle, Spalte oder einen Ausdruck, die in einer Abfrage vorhanden sind. Sie können explizite Aliasse in der SELECT
-Liste oder der FROM
-Klausel einführen, oder Cloud Spanner SQL wird einen impliziten Alias für einige Ausdrücke ableiten.
Ausdrücke ohne expliziten oder impliziten Alias sind anonym und die Abfrage kann darauf nicht mit dem Namen verweisen.
Explizite Aliasse
Sie können explizite Aliasse entweder in der FROM
-Klausel oder der SELECT
-Liste einfügen.
In einer FROM
-Klausel können Sie explizite Aliasse für jedes Objekt einfügen, einschließlich Tabellen, Arrays, Unterabfragen und UNNEST
-Klauseln. Verwenden Sie hierzu [AS] alias
. Der Suchbegriff AS
ist optional.
Beispiel:
SELECT s.FirstName, s2.SongName
FROM Singers AS s, (SELECT * FROM Songs) AS s2;
Sie können explizite Aliasse für jeden Ausdruck in der SELECT
-Liste einfügen. Verwenden Sie hierzu [AS] alias
. Der Suchbegriff AS
ist optional.
Beispiel:
SELECT s.FirstName AS name, LOWER(s.FirstName) AS lname
FROM Singers s;
Implizite Aliasse
Wenn in der SELECT
-Liste ein Ausdruck ohne explizites Alias vorhanden ist, weist Cloud Spanner SQL ein implizites Alias gemäß den folgenden Regeln zu. Es können mehrere Spalten mit demselben Alias in der SELECT
-Liste vorkommen.
- Bei Kennungen ist der Alias die Kennung. Beispiel:
SELECT abc
impliziertAS abc
. - Bei Pfadausdrücken ist der Alias die letzte Kennung im Pfad. Beispiel:
SELECT abc.def.ghi
impliziertAS ghi
. - Beim Feldzugriff mit dem "Punkt"-Element als Feldzugriffsoperator ist der Alias der Feldname. Beispiel:
SELECT (struct_function()).fname
impliziertAS fname
.
In allen anderen Fällen ist kein impliziter Alias vorhanden, d. h. die Spalte ist anonym und es kann darauf nicht mit dem Namen verwiesen werden. Die Daten dieser Spalte werden trotzdem zurückgegeben und die angezeigten Abfrageergebnisse weisen möglicherweise ein erzeugtes Label für diese Spalte auf. Dieses Label kann allerdings nicht als Alias verwendet werden.
In einer FROM
-Klausel müssen from_item
-Elemente keinen Alias haben. Es gelten die folgenden Regeln:
- Wenn ein Ausdruck vorhanden ist, der kein explizites Alias hat, weist Cloud Spanner SQL in den folgenden Fällen ein implizites Alias zu:
-
Bei Kennungen ist der Alias die Kennung. Beispiel:
FROM abc
impliziertAS abc
. -
Bei Pfadausdrücken ist der Alias die letzte Kennung im Pfad. Beispiel:
FROM abc.def.ghi
impliziertAS ghi
. - Die mit
WITH OFFSET
erstellte Spalte hat den impliziten Aliasoffset
. - Tabellen-Unterabfragen haben keine impliziten Aliasse.
FROM UNNEST(x)
hat keinen impliziten Alias.
Alias-Sichtbarkeit
Nach dem Einfügen eines expliziten Alias in eine Abfrage bestehen Einschränkungen in Bezug auf die Stellen, an denen Sie in der Abfrage auf dieses Alias verweisen können. Diese Einschränkungen in Bezug auf die Alias-Sichtbarkeit sind das Ergebnis der Namensbereichsdefinitionsregeln von Cloud Spanner SQL.
Sichtbarkeit in der FROM-Klausel
Cloud Spanner SQL verarbeitet Aliasse in einer FROM
-Klausel von links nach rechts und Aliasse sind nur für nachfolgende Pfadausdrücke in einer FROM
-Klausel sichtbar.
Beispiel:
Angenommen in der Tabelle Singers
ist eine Spalte Concerts
des Typs ARRAY
vorhanden.
SELECT FirstName
FROM Singers AS s, s.Concerts;
Ungültig:
SELECT FirstName
FROM s.Concerts, Singers AS s; // INVALID.
Aliasse in FROM
-Klauseln sind nicht für Unterabfragen in derselben FROM
-Klausel sichtbar. Unterabfragen in einer FROM
-Klausel dürfen keine korrelierten Verweise auf andere Tabellen in derselben FROM
-Klausel enthalten.
Ungültig:
SELECT FirstName
FROM Singers AS s, (SELECT (2020 - ReleaseDate) FROM s) // INVALID.
Sie können jeden Spaltennamen einer Tabelle in der FROM
-Klausel als Alias an jeder Stelle in der Abfrage verwenden – mit oder ohne Qualifizierung des Tabellennamens.
Beispiel:
SELECT FirstName, s.ReleaseDate
FROM Singers s WHERE ReleaseDate = 1975;
Wenn die FROM
-Klausel einen expliziten Alias enthält, müssen Sie im Rest der Abfrage den expliziten Alias statt des impliziten Alias verwenden (siehe Implizite Aliasse). Ein Tabellenalias ist nützlich wegen seiner Kürze oder zur Vermeidung von Mehrdeutigkeit in Fällen wie Selbstverknüpfungen, in denen dieselbe Tabelle während der Abfrageverarbeitung mehrmals durchsucht wird.
Beispiel:
SELECT * FROM Singers as s, Songs as s2
ORDER BY s.LastName
Ungültig – ORDER BY
verwendet nicht den Tabellenalias:
SELECT * FROM Singers as s, Songs as s2
ORDER BY Singers.LastName; // INVALID.
Sichtbarkeit in der SELECT-Liste
Aliasse in der Liste SELECT
sind nur für die folgenden Klauseln sichtbar:
GROUP BY
-KlauselORDER BY
-KlauselHAVING
-Klausel
Beispiel:
SELECT LastName AS last, SingerID
FROM Singers
ORDER BY last;
Sichtbarkeit in der GROUP BY-, ORDER BY- und HAVING-Klausel
Diese drei Klauseln GROUP BY
, ORDER BY
und HAVING
können nur auf die folgenden Werte verweisen:
- Tabellen in der
FROM
-Klausel und alle zugehörigen Spalten. - Aliasse in der
SELECT
-Liste.
GROUP BY
und ORDER BY
können auch auf eine dritte Gruppe verweisen:
- Ganzzahlliterale, die auf Elemente in der
SELECT
-Liste verweisen. Die Ganzzahl1
verweist auf das erste Element in derSELECT
-Liste,2
auf das zweite Element usw.
Beispiel:
SELECT SingerID AS sid, COUNT(Songid) AS s2id
FROM Songs
GROUP BY 1
ORDER BY 2 DESC;
Die vorherige Abfrage ist äquivalent mit:
SELECT SingerID AS sid, COUNT(Songid) AS s2id
FROM Songs
GROUP BY sid
ORDER BY s2id DESC;
Doppelte Aliasse
Eine SELECT
-Liste oder Unterabfrage mit mehreren expliziten oder impliziten Aliassen desselben Namens ist zulässig, solange in der Abfrage nicht an anderer Stelle auf den Aliasnamen verwiesen wird, da der Verweis mehrdeutig wäre.
Beispiel:
SELECT 1 AS a, 2 AS a;
+---+---+
| a | a |
+---+---+
| 1 | 2 |
+---+---+
Mehrdeutige Aliasse
Cloud Spanner SQL gibt einen Fehler an, wenn der Zugriff auf einen Namen mehrdeutig ist, was bedeutet, dass er in mehr als ein eindeutiges Objekt in der Abfrage oder in einem Tabellenschema aufgelöst werden kann, einschließlich des Schemas einer Zieltabelle.
Beispiele:
Diese Abfrage enthält Spaltennamen, die zwischen den Tabellen miteinander in Konflikt stehen, da sowohl die Tabelle Singers
als auch die Tabelle Songs
eine Spalte mit dem Namen SingerID
enthält:
SELECT SingerID
FROM Singers, Songs;
Diese Abfrage enthält Aliasse, die in der GROUP BY
-Klausel mehrdeutig sind, da sie in der SELECT
-Liste dupliziert sind:
SELECT FirstName AS name, LastName AS name,
FROM Singers
GROUP BY name;
Diese Abfrage enthält Aliasse, die in der SELECT
-Liste und der FROM
-Klausel mehrdeutig sind, da sie denselben Namen haben. Angenommen table
hat die Spalten x
, y
und z
. z
ist vom Typ STRUCT und enthält die Felder v
, w
und x
.
Beispiel:
SELECT x, z AS T
FROM table AS T
GROUP BY T.x;
Der Alias T
ist mehrdeutig und erzeugt einen Fehler, weil T.x
in der GROUP
BY
-Klausel entweder auf table.x
oder auf table.z.x
verweisen kann.
Ein Name ist in GROUP BY
, ORDER BY
oder HAVING
nicht mehrdeutig, wenn er sowohl ein Spaltenname als auch ein Alias der SELECT
-Liste ist. Voraussetzung hierfür ist, dass der Name in dasselbe zugrunde liegende Objekt aufgelöst wird.
Beispiel:
SELECT LastName, BirthYear AS BirthYear
FROM Singers
GROUP BY BirthYear;
Der Alias BirthYear
ist nicht mehrdeutig, da er in dieselbe zugrunde liegende Spalte Singers.BirthYear
aufgelöst wird.
Implizite Aliasse
Wenn in der SELECT
-Liste ein Ausdruck ohne explizites Alias vorhanden ist, weist Cloud Spanner SQL ein implizites Alias gemäß den folgenden Regeln zu. Es können mehrere Spalten mit demselben Alias in der SELECT
-Liste vorkommen.
- Bei Kennungen ist der Alias die Kennung. Beispiel:
SELECT abc
impliziertAS abc
. - Bei Pfadausdrücken ist der Alias die letzte Kennung im Pfad. Beispiel:
SELECT abc.def.ghi
impliziertAS ghi
. - Beim Feldzugriff mit dem "Punkt"-Element als Feldzugriffsoperator ist der Alias der Feldname. Beispiel:
SELECT (struct_function()).fname
impliziertAS fname
.
In allen anderen Fällen ist kein impliziter Alias vorhanden, d. h. die Spalte ist anonym und es kann darauf nicht mit dem Namen verwiesen werden. Die Daten dieser Spalte werden trotzdem zurückgegeben und die angezeigten Abfrageergebnisse weisen möglicherweise ein erzeugtes Label für diese Spalte auf. Dieses Label kann allerdings nicht als Alias verwendet werden.
In einer FROM
-Klausel müssen from_item
-Elemente keinen Alias haben. Es gelten die folgenden Regeln:
- Wenn ein Ausdruck vorhanden ist, der kein explizites Alias hat, weist Cloud Spanner SQL in den folgenden Fällen ein implizites Alias zu:
- Bei Kennungen ist der Alias die Kennung. Beispiel:
FROM abc
impliziertAS abc
. - Bei Pfadausdrücken ist der Alias die letzte Kennung im Pfad. Beispiel:
FROM abc.def.ghi
impliziertAS ghi
. - Die mit
WITH OFFSET
erstellte Spalte hat den impliziten Aliasoffset
.
- Bei Kennungen ist der Alias die Kennung. Beispiel:
- Tabellen-Unterabfragen haben keine impliziten Aliasse.
FROM UNNEST(x)
hat keinen impliziten Alias.
Bereichsvariablen
In Cloud Spanner SQL ist eine Bereichsvariable ein Tabellenausdrucksalias in der FROM
-Klausel. Manchmal wird eine Bereichsvariable als table alias
bezeichnet. Mit einer Bereichsvariable können Sie auf Zeilen verweisen, die über einen Tabellenausdruck gescannt werden.
Ein Tabellenausdruck steht für ein Element in der FROM
-Klausel. Als Rückgabe erhalten Sie dann eine Tabelle.
Häufige Elemente, die dieser Ausdruck darstellen kann, sind Tabellen, Werttabellen, Unterabfragen, Joins und eingeklammerte Joins.
Im Allgemeinen liefert eine Bereichsvariable einen Verweis auf die Zeilen eines Tabellenausdrucks. Eine Bereichsvariable kann verwendet werden, um eine Spaltenreferenz zu qualifizieren und die zugehörige Tabelle eindeutig zu identifizieren, z. B. range_variable.column_1
.
Wenn Sie auf eine einzelne Bereichsvariable verweisen, ohne ein Spaltensuffix anzugeben, ist das Ergebnis eines Tabellenausdrucks der Zeilentyp der zugehörigen Tabelle.
Wertetabellen haben explizite Zeilentypen. Bei Bereichsvariablen, die sich auf Wertetabellen beziehen, ist der Ergebnistyp daher der Zeilentyp der Wertetabelle. Andere Tabellen haben keine expliziten Zeilentypen. Bei diesen Tabellen ist der Bereichsvariablentyp ein dynamisch definierter STRUCT
-Typ, der alle Spalten der Tabelle enthält.
Beispiele
In diesen Beispielen wird die WITH
-Klausel verwendet, um eine temporäre Tabelle mit dem Namen Grid
zu emulieren. Diese Tabelle enthält die Spalten x
und y
. Eine Bereichsvariable mit dem Namen Coordinate
verweist auf die aktuelle Zeile, während die Tabelle gescannt wird. Coordinate
kann für den Zugriff auf die gesamte Zeile oder auf Spalten der Zeile verwendet werden.
Im folgenden Beispiel wird die Spalte x
aus der Bereichsvariable Coordinate
ausgewählt, wodurch dann die Spalte x
aus der Tabelle Grid
ausgewählt wird.
WITH Grid AS (SELECT 1 x, 2 y)
SELECT Coordinate.x FROM Grid AS Coordinate;
+---+
| x |
+---+
| 1 |
+---+
Im folgenden Beispiel werden alle Spalten der Bereichsvariable Coordinate
ausgewählt, wodurch dann alle Spalten der Tabelle Grid
ausgewählt werden.
WITH Grid AS (SELECT 1 x, 2 y)
SELECT Coordinate.* FROM Grid AS Coordinate;
+---+---+
| x | y |
+---+---+
| 1 | 2 |
+---+---+
Im folgenden Beispiel wird die Bereichsvariable Coordinate
ausgewählt, die auf die Zeilen in Tabelle Grid
verweist. Da Grid
keine Wertetabelle ist, ist der Ergebnistyp von Coordinate
ein STRUCT
-Typ, der alle Spalten aus Grid
enthält.
WITH Grid AS (SELECT 1 x, 2 y)
SELECT Coordinate FROM Grid AS Coordinate;
+--------------+
| Coordinate |
+--------------+
| {x: 1, y: 2} |
+--------------+
Anhang A: Beispieldaten
Diese Beispiele enthalten Anweisungen, die Abfragen für die Tabellen Roster
und TeamMascot
sowie PlayerStats
ausführen.
GROUP BY-Klausel
Beispiel:
SELECT LastName, SUM(PointsScored)
FROM PlayerStats
GROUP BY LastName;
LastName | SUM |
---|---|
Adams | 7 |
Buchanan | 13 |
Coolidge | 1 |
UNION
Der UNION
-Operator kombiniert die Ergebnismenge von zwei oder mehreren SELECT
-Anweisungen, indem Spalten der Ergebnismenge von jeder SELECT
-Anweisung paarweise angeordnet und vertikal verkettet werden.
Beispiel:
SELECT Mascot AS X, SchoolID AS Y
FROM TeamMascot
UNION ALL
SELECT LastName, PointsScored
FROM PlayerStats;
Ergebnisse:
X | Y |
---|---|
Jaguars | 50 |
Knights | 51 |
Lakers | 52 |
Mustangs | 53 |
Adams | 3 |
Buchanan | 0 |
Coolidge | 1 |
Adams | 4 |
Buchanan | 13 |
INTERSECT
Diese Abfrage gibt die Nachnamen zurück, die in Roster und PlayerStats vorhanden sind.
SELECT LastName
FROM Roster
INTERSECT ALL
SELECT LastName
FROM PlayerStats;
Ergebnisse:
LastName |
---|
Adams |
Coolidge |
Buchanan |
EXCEPT
Die folgende Abfrage gibt die Nachnamen in Roster zurück, die nicht in PlayerStats vorhanden sind.
SELECT LastName
FROM Roster
EXCEPT DISTINCT
SELECT LastName
FROM PlayerStats;
Ergebnisse:
LastName |
---|
Eisenhower |
Davis |
Wenn Sie die Reihenfolge der SELECT
-Anweisungen umkehren, werden die Nachnamen unter "PlayerStats" zurückgegeben, die nicht unter "Roster" vorhanden sind:
SELECT LastName
FROM PlayerStats
EXCEPT DISTINCT
SELECT LastName
FROM Roster;
Ergebnisse:
(empty)