Mit Sortierung arbeiten

Mit Sammlungen den Überblick behalten Sie können Inhalte basierend auf Ihren Einstellungen speichern und kategorisieren.

Informationen zur Sortierung

Die Sortierung bestimmt, wie Strings bei von einer Sortierung unterstützten Vorgänge sortiert und verglichen werden. Wenn Sie eine Sortierung nach einem von der Sortierung unterstützten Vorgang anpassen möchten, müssen Sie mindestens einem String im Vorgang eine Sortierspezifikation angeben. Einige Vorgänge können die Sortierung nicht verwenden, können aber die Sortierung durchlaufen.

Von der Sortierung betroffene Vorgänge

Wenn ein Vorgang von der Sortierung betroffen ist, berücksichtigt der Vorgang die Sortierung während des Vorgangs. Diese Abfragevorgänge sind von der Sortierung betroffen, wenn Strings sortiert und verglichen werden:

Vorgänge Hinweise
Durch Sortierung unterstützte Vergleichsvorgänge
Join-Vorgänge
ORDER BY In der Klausel WINDOW wird ORDER BY nicht bei der Sortierung unterstützt.
GROUP BY
WINDOW für Fensterfunktionen In der -Klausel WINDOW werden ORDER BY und PARTITION BY nicht bei der Sortierung unterstützt.
Von der Sortierung unterstützte Skalarfunktionen
Von der Sortierung unterstützte Aggregatfunktionen
Vorgänge festlegen

Vorgänge, bei denen die Sortierung weitergegeben wird

Die Sortierung kann einige Abfragevorgänge an andere Teile einer Abfrage weitergeben. Wenn die Sortierung einen Vorgang in einer Abfrage durchläuft, wird dies als Weitergabe bezeichnet. Während der Weitergabe:

  • Wenn eine Eingabe keine Sortierspezifikation oder eine leere Sortierspezifikation enthält und eine andere Eingabe eine explizit definierte Sortierung enthält, wird diese für alle Eingaben verwendet.
  • Alle Eingaben mit einer nicht leeren Sortierspezifikation müssen die gleiche Sortierart haben. Andernfalls wird ein Fehler ausgegeben.

BigQuery verfügt über mehrere Funktionen, Operatoren und Ausdrücke, die die Sortierung weiterleiten können.

Im folgenden Beispiel wird die Sortierspezifikation 'und:ci' von der Spalte character zum Vorgang ORDER BY weitergegeben.

-- With collation
SELECT *
FROM UNNEST([
  COLLATE('B', 'und:ci'),
  'b',
  'a'
]) AS character
ORDER BY character

+-----------+
| character |
+-----------+
| a         |
| B         |
| b         |
+-----------+
-- Without collation
SELECT *
FROM UNNEST([
  'B',
  'b',
  'a'
]) AS character
ORDER BY character

+-----------+
| character |
+-----------+
| B         |
| a         |
| b         |
+-----------+

Functions

Mit diesen Funktionen kann die Sortierung weitergegeben werden:

Funktion Hinweise
AEAD.DECRYPT_STRING
ANY_VALUE
ARRAY_AGG Die Sortierung von Eingabeargumenten wird als Sortierung im Arrayelement weitergegeben.
ARRAY_TO_STRING Die Sortierung von Arrayelementen wird an die Ausgabe weitergegeben.
COLLATE
CONCAT
FORMAT Die Sortierung von format_string in den zurückgegebenen String wird weitergegeben.
FORMAT_DATE Die Sortierung von format_string in den zurückgegebenen String wird weitergegeben.
FORMAT_DATETIME Die Sortierung von format_string in den zurückgegebenen String wird weitergegeben.
FORMAT_TIME Die Sortierung von format_string in den zurückgegebenen String wird weitergegeben.
FORMAT_TIMESTAMP Die Sortierung von format_string in den zurückgegebenen String wird weitergegeben.
GREATEST
LAG
LEAD
LEAST
LEFT
LOWER
LPAD
MAX
MIN
NET.HOST
NET.PUBLIC_SUFFIX
NET.REG_DOMAIN
NORMALIZE
NORMALIZE_AND_CASEFOLD
REPEAT
REPLACE
REVERSE
RIGHT
RPAD
SOUNDEX
SPLIT Die Sortierung von Eingabeargumenten wird als Sortierung im Arrayelement weitergegeben.
STRING_AGG
SUBSTR
UPPER

Operatoren

Mit diesen Operatoren kann die Sortierung weitergegeben werden:

Operator Hinweise
||-Verkettungsoperator
Array-Abo-Operator An die Ausgabe weitergegeben.
Set-Operatoren Die Sortierung einer Ausgabespalte wird durch die Sortierungen der Eingabespalten an derselben Position festgelegt.
STRUCT-Feldzugriffsoperator Beim Abrufen von STRUCT wird die Sortierung im Feld STRUCT als Ausgabesortierung weitergegeben.
UNNEST Die Sortierung des Eingabearrayelements wird an die Ausgabe weitergegeben.

Ausdrücke

Diese Ausdrücke ermöglichen die Weitergabe der Sortierung:

Ausdruck Hinweise
ARRAY Wenn Sie ein ARRAY erstellen, wird die Sortierung der Eingabeargumente an die Felder im ARRAY weitergegeben.
CASE
CASE Expr
COALESCE
IF
IFNULL
NULLIF
STRUCT Wenn Sie einen STRUCT erstellen, wird die Sortierung der Eingabeargumente an die Felder im STRUCT weitergegeben.

Wo Sie eine Sortierspezifikation zuweisen können

Diese von Sortierungen unterstützten Typen können einer Sortierspezifikation zugewiesen werden:

  • Ein STRING
  • Ein STRING-Feld in einem STRUCT
  • Ein STRING-Element in einem ARRAY

Außerdem gilt:

  • Sie können einem Schema beim Erstellen oder Ändern eine Standardsortierspezifikation zuweisen. Dadurch wird allen zukünftigen Tabellen, die dem Schema hinzugefügt werden, eine Standardsortierspezifikation zugewiesen, wenn die Tabellen keine eigenen Standardsortierspezifikationen haben.
  • Sie können einer Tabelle beim Erstellen oder Ändern eine Standardsortierspezifikation zuweisen. Dadurch wird allen zukünftigen Spalten, die eine Sortierung unterstützen und der Tabelle hinzugefügt werden, eine Sortierspezifikation zugewiesen, wenn die Spalten keine Sortierspezifikationen haben. Dies überschreibt eine Standardsortierspezifikation für ein Schema.
  • Sie können einem von einer Sortierung unterstützten Typ in einer Spalte eine Sortierspezifikation zuweisen. Eine Spalte, die in ihrem Spaltenschema einen von einer Sortierung unterstützten Typ enthält, ist eine von einer Sortierung unterstützte Spalte. Dies überschreibt eine Standardsortierspezifikation für eine Tabelle.
  • Sie können einem von einer Sortierung unterstützten Abfragevorgang eine Sortierspezifikation zuweisen.
  • Sie können mit der Funktion COLLATE einem von einer Sortierung unterstützten Ausdruck eine Sortierspezifikation zuweisen. Dadurch werden alle zuvor festgelegten Sortierspezifikationen überschrieben.

Zusammenfassung:

Sie können eine Standardsortierspezifikation für ein Schema definieren. Beispiel:

CREATE SCHEMA (...)
DEFAULT COLLATE 'und:ci'

Sie können eine Standardsortierspezifikation für eine Tabelle definieren. Beispiel:

CREATE TABLE (...)
DEFAULT COLLATE 'und:ci'

Sie können eine Sortierspezifikation für eine von einer Sortierung unterstützte Spalte definieren. Beispiel:

CREATE TABLE (
  case_insensitive_column STRING COLLATE 'und:ci'
)

Mit dem Feature COLLATE können Sie eine Sortierspezifikation für einen von der Sortierung unterstützten Ausdruck angeben. Beispiel:

SELECT COLLATE('a', 'und:ci') AS character

DDL-Anweisungen

Ort Support Hinweise
Schema CREATE SCHEMA Erstellen Sie ein Schema und fügen Sie dem Schema optional eine Standardsortierspezifikation hinzu.
Schema ALTER SCHEMA Aktualisiert die Standardsortierspezifikation für ein Schema.
Tabelle CREATE TABLE Erstellen Sie eine Tabelle und fügen Sie optional eine Standardsortierspezifikation zu einer Tabelle oder eine Sortierspezifikation zu einem von einer Sortierung unterstützten Typ in einer Spalte hinzu.

Eine Spalte, bei der CLUSTERING angewendet wird, kann nicht sortiert werden.

Sie können beim Erstellen einer Tabelle von einer Sortierung unterstützte Funktionen mit AS SELECT verwenden.
Tabelle ALTER TABLE Aktualisieren Sie die Standardsortierspezifikation auf den Typ in einer Tabelle, der von einer Sortierung unterstützt wird.
Spalte ADD COLUMN Fügen Sie eine Sortierspezifikation zu einem von einer Sortierung unterstützten Typ in einer neuen Spalte in einer vorhandenen Tabelle hinzu.

Datentypen

Typ Support Hinweise
Datentyp STRING Sie können eine Sortierspezifikation direkt auf diesen Datentyp anwenden.
Datentyp STRUCT Sie können eine Sortierspezifikation auf ein STRING-Feld in einem STRUCT anwenden. Ein STRUCT kann STRING-Felder mit unterschiedlichen Sortierspezifikationen haben.
Datentyp ARRAY Sie können eine Sortierspezifikation auf ein STRING-Element in einem ARRAY anwenden. Ein ARRAY kann STRING-Elemente mit unterschiedlichen Sortierspezifikationen haben.

Verwenden Sie die Funktion COLLATE, um eine Sortierspezifikation auf Ausdrücke anzuwenden, die von einer Sortierung unterstützt werden.

Funktionen, Operatoren und bedingte Ausdrücke

Functions

Typ Support Hinweise
Skalar COLLATE
Skalar ENDS_WITH
Skalar GREATEST
Skalar INSTR
Skalar LEAST
Skalar REPLACE
Skalar SPLIT
Skalar STARTS_WITH
Skalar STRPOS
Aggregate COUNT Dieser Operator ist nur von der Sortierung betroffen, wenn die Eingabe das Argument DISTINCT enthält.
Aggregate MAX
Aggregate MIN

Operatoren

Support Hinweise
<
<=
>
>=
=
!=
[NOT] BETWEEN
[NOT] IN Dieser Operator unterstützt im Allgemeinen die Sortierung. [NOT] IN UNNEST wird jedoch nicht unterstützt. Wenn er mit einer Liste verwendet wird, muss mindestens ein Element in der Liste vorhanden sein.
[NOT] IN

Bedingte Ausdrücke

Support
CASE
CASE Expr
NULLIF

Die vorhergehenden von einer Sortierung unterstützten Vorgänge (Funktionen, Operatoren und bedingte Ausdrücke) können Eingaben mit explizit definierten Sortierspezifikationen für von einer Sortierung unterstützten Typen enthalten. In einem von einer Sortierung unterstützten Vorgang:

  • Alle Eingaben mit einer nicht leeren, explizit definierten Sortierspezifikation müssen identisch sein. Andernfalls wird ein Fehler ausgegeben.
  • Wenn eine Eingabe keine explizit definierte Sortierung enthält und eine andere Eingabe eine explizit definierte Sortierung enthält, wird die explizit für beide verwendet.

Beispiel:

-- Assume there is a table with this column declaration:
CREATE TABLE table_a
(
    col_a STRING COLLATE 'und:ci',
    col_b STRING COLLATE '',
    col_c STRING,
    col_d STRING COLLATE 'und:ci'
);

-- This runs. Column 'b' has a collation specification and the
-- column 'c' does not.
SELECT STARTS_WITH(col_b_expression, col_c_expression)
FROM table_a;

-- This runs. Column 'a' and 'd' have the same collation specification.
SELECT STARTS_WITH(col_a_expression, col_d_expression)
FROM table_a;

-- This runs. Even though column 'a' and 'b' have different
-- collation specifications, column 'b' is considered the default collation
-- because it's assigned to an empty collation specification.
SELECT STARTS_WITH(col_a_expression, col_b_expression)
FROM table_a;

-- This works. Even though column 'a' and 'b' have different
-- collation specifications, column 'b' is updated to use the same
-- collation specification as column 'a'.
SELECT STARTS_WITH(col_a_expression, COLLATE(col_b_expression, 'und:ci'))
FROM table_a;

-- This runs. Column 'c' does not have a collation specification, so it uses the
-- collation specification of column 'd'.
SELECT STARTS_WITH(col_c_expression, col_d_expression)
FROM table_a;

Details zur Sortierspezifikation

Eine Sortierspezifikation bestimmt, wie Strings in von einer Sortierung unterstützten Vorgängen sortiert und verglichen werden. Sie können die Unicode-Sortierspezifikation und:ci für Typen, die von einer Sortierung unterstützt werden definieren.

Wenn keine Sortierspezifikation definiert ist, wird die Standardsortierung verwendet. Weitere Informationen finden Sie in folgenden Abschnitten:

Standardsortierspezifikation

Wenn eine Sortierspezifikation nicht zugewiesen ist oder leer ist, wird die Sortierung 'binary' verwendet. Die binäre Sortierung gibt an, dass der Vorgang Daten in Unicode-Codepoint-Reihenfolge zurückgeben soll. Sie können die binäre Sortierung nicht explizit festlegen.

Unicode-Sortierspezifikation

collation_specification:
  'language_tag:collation_attribute'

Eine Unicode-Sortierspezifikation gibt an, dass der Vorgang den Unicode-Sortieralgorithmus verwendet, um Strings zu sortieren und zu vergleichen. Die Sortierspezifikation kann ein STRING-Literal oder ein Abfrageparameter sein.

Das Sprach-Tag

Das Sprach-Tag bestimmt, wie Strings im Allgemeinen sortiert und verglichen werden. Zulässige Werte für language_tag sind:

  • und: Ein Sprach-String, der die unbestimmte Sprache darstellt. und ist ein spezielles Sprach-Tag, das in der IANA-Registry für Sprach-Tags definiert ist und zur Angabe einer unbestimmten Sprache verwendet wird. Diese wird auch als root-Sprache bezeichnet und kann als standardmäßige Unicode-Sortierung betrachtet werden. Sie definiert eine vernünftige, sprachunabhängige Sortierung.

Das Sortierattribut

Zusätzlich zum Sprach-Tag muss die Spezifikation der Unicode-Sortierung ein collation_attribute haben, das zusätzliche Regeln für das Sortieren und Vergleichen von Strings ermöglicht. Zulässige Werte sind:

  • ci: Bei der Sortierung wird die Groß-/Kleinschreibung nicht berücksichtigt.

Beispiel für die Sortierspezifikation

So sieht das Sortierattribut ci aus, wenn es mit dem Sprachtag und in der Funktion COLLATE verwendet wird:

COLLATE('orange1', 'und:ci')

Vorsichtsmaßnahmen

  • Unterschiedliche Strings können als gleich betrachtet werden. Beispiel: (LATEINISCHER GROSSBUCHSTABE SCHARFES S) wird mit 'SS' auf primärer Ebene als gleichwertig betrachtet, d. h. 'ẞ1' < 'SS2'. Dies entspricht der Funktionsweise einer Nichtberücksichtigung der Groß-/Kleinschreibung.
  • Es gibt eine Vielzahl von Unicode-Codepoints (Satzzeichen, Symbole usw.), die so behandelt werden, als wären sie nicht vorhanden. Daher werden Strings mit und ohne sie identisch sortiert. Beispielsweise wird der Formatsteuerungscode U+2060 ignoriert, wenn die folgenden Strings sortiert werden:

    SELECT *
    FROM UNNEST([
      COLLATE('oran\u2060ge1', 'und:ci'),
      COLLATE('\u2060orange2', 'und:ci'),
      COLLATE('orange3', 'und:ci')
    ]) AS fruit
    ORDER BY fruit
    
    +---------+
    | fruit   |
    +---------+
    | orange1 |
    | orange2 |
    | orange3 |
    +---------+
    
  • Die Reihenfolge kann sich ändern. Die Unicode-Spezifikation der und-Sortierung kann sich gelegentlich ändern, was sich auf die Sortierreihenfolge auswirken kann.

Beschränkungen

Einschränkungen für unterstützte Funktionen werden in den vorherigen Abschnitten erfasst. Es folgen jedoch einige allgemeine Beschränkungen, die zu beachten sind:

  • und:ci und leere Sortiervorgänge werden unterstützt, jedoch keine anderen Sortierspezifikationen.
  • Vorgänge und Funktionen, die die Sortierung nicht unterstützen, erzeugen einen Fehler, wenn sie mit sortierten Werten übereinstimmen.
  • Sie können eine nicht leere Sortierung für ein Clustering-Feld nicht mithilfe eines API-Aufrufs oder einer DDL-Abfrage festlegen.

    CREATE TABLE mydataset.mytable
    (
      word STRING COLLATE 'und:ci',
      number INT64
    )
    CLUSTER BY word;
    
    -- User error:
    -- “CLUSTER BY STRING column word with
    -- collation und:ci is not supported"
    
  • Eine materialisierte Ansicht mit sortierten Ausgabespalten wird nicht unterstützt.

    CREATE MATERIALIZED VIEW bigquery.public.data.samples.shakespeare
    AS SELECT COLLATE('abc', 'und:ci') x;
    
    -- User error:
    -- “Creating MATERIALIZED VIEW with collation
    -- on the output column is not supported”
    
  • Eine Ansicht mit sortierten Ausgabespalten wird nicht unterstützt.

    CREATE VIEW bigquery.public.data.samples.shakespeare
    AS SELECT COLLATE('abc', 'und:ci') x;
    
    -- User error:
    -- "Creating VIEW with collation on the output column
    -- is not supported"
    
  • Nachdem eine Ansicht oder eine materialisierte Ansicht erstellt und eine Spaltensortierung der Basistabelle geändert wurde, um die Spalte zu sortieren, wird die Ansicht oder materialisierte Ansicht ungültig und kann nicht abgefragt werden.

    CREATE TABLE dataset.base (
      col_no_collation STRING,
      col INT64
    )
    AS SELECT 'a', 1
    UNION ALL SELECT 'b', 2
    UNION ALL SELECT 'A', 3
    UNION ALL SELECT 'B', 4;
    
    CREATE VIEW/MATERIALIZED VIEW dataset.view AS SELECT * FROM dataset.base;
    
    -- Change the collation of string column of the base table so
    -- that the string column of view/materialized view is collated.
    ALTER TABLE dataset.base ALTER COLUMN col_no_collation SET DATA TYPE STRING COLLATE 'und:ci';
    
    -- Should return an error since the column of view/materialized
    -- contains collation.
    SELECT * FROM dataset.view ORDER BY col;
    
    -- User error:
    -- "Collation on the output column for view dataset.view is
    -- not supported"
    
  • Sie können keine materialisierte Ansicht mit Sortierschlüsseln in einer Aggregatfunktion erstellen.

    CREATE TABLE dataset.base (
      col_no_collation STRING,
      col INT64
    )
    AS SELECT 'a', 1
    UNION ALL SELECT 'b', 2
    UNION ALL SELECT 'A', 3
    UNION ALL SELECT 'B', 4;
    
    CREATE VIEW/MATERIALIZED VIEW dataset.view AS SELECT * FROM dataset.base;
    
    -- Change the collation of string column of the base table so
    -- that the string column of view/materialized view is
    -- collated.
    ALTER TABLE dataset.base ALTER COLUMN col_no_collation SET DATA TYPE STRING COLLATE 'und:ci';
    
    -- Should return an error since the column of view/materialized
    -- contains collation.
    SELECT * FROM dataset.view ORDER BY col;
    
    -- User error:
    -- "Collation on the output column for view dataset.view is
    -- not supported"
    
  • Eine Tabellenfunktion mit sortierten Ausgabespalten wird nicht unterstützt.

    CREATE TABLE FUNCTION bigquery.public.data.samples.shakespeare()
    AS SELECT COLLATE('abc', 'und:ci') x;
    
    -- User error:
    -- "Creating TABLE FUNCTION with collation on the output
    -- column is not supported"
    
  • Benutzerdefinierte Funktionen (UDFs) können keine sortierten Argumente annehmen.

    CREATE FUNCTION tmp_dataset.f(x STRING) AS (x);
    
    SELECT tmp_dataset.f(col_ci)
    FROM shared_dataset.table_collation_simple
    
    -- User error:
    -- “Collation is not allowed on argument x ("und:ci").
    -- Use COLLATE(arg, '') to remove collation at [1:8]”
    
  • Wenn eine SELECT-Abfrage mit einer benutzerdefinierten Zieltabelle Ausgabespalten mit Sortierung erzeugt, wird ein Fehler erzeugt.

    -- The following query returns an error since the resulting schema of
    -- the query has a column string_ci with collation ‘und:ci’.
    SELECT string_ci FROM collated_table
    
  • BigQuery BI Engine unterstützt keine Tabellen mit Sortierung oder der COLLATE-Funktion.