Sortierungsunterstützung

Mithilfe von Sortierung können Sie alternative Regeln für den Vergleich von Zeichenfolgen angeben, damit Daten anhand einer bestimmten Sprache oder anderer benutzerdefinierter Regeln verglichen und sortiert werden.

Unter diesem Thema:

Übersicht

Textzeichenfolgen in Snowflake werden mit dem Zeichensatz UTF-8 gespeichert. Standardmäßig werden Zeichenfolgen anhand der Unicode-Codes verglichen, die die Zeichen in der Zeichenfolge darstellen.

Der Vergleich von Zeichenfolgen auf Grundlage ihrer UTF-8-Zeichenrepräsentation führt jedoch möglicherweise nicht zum gewünschten/erwarteten Verhalten. Beispiel:

  • Wenn Sonderzeichen in einer bestimmten Sprache nicht nach den Sortierungsstandards dieser Sprache sortiert werden, kann das Sortieren zu unerwarteten Ergebnissen führen.

  • Möglicherweise wollen Sie, dass die Zeichenfolgen nach anderen Regeln sortiert werden, z. B. indem ignoriert wird, ob Zeichen in Groß- oder Kleinbuchstaben geschrieben sind.

Mithilfe von Sortierung können Sie explizit die Regeln angeben, die zum Vergleichen von Zeichenfolgen verwendet werden sollen, basierend auf:

  • Unterschiedlichen Gebietsschemata (d. h. unterschiedliche Zeichensätze für verschiedene Sprachen).

  • Unterscheidung nach Groß-/Kleinschreibung (d. h. ob Zeichenfolgenvergleiche verwendet werden sollen, bei denen Groß- und Kleinschreibung berücksichtigt wird oder nicht, ohne dass die Funktionen UPPER oder LOWER zum Konvertieren der Zeichenfolgen explizit aufgerufen werden).

  • Unterscheidung nach Akzent (z. B. ob Z, Ź und Ż als gleicher Buchstabe oder als verschiedene Buchstaben betrachtet werden).

  • Unterscheidung nach Interpunktion (d. h. ob Vergleiche nur Buchstaben oder alle Zeichen enthalten). Wenn ein Vergleich beispielsweise nicht nach Interpunktion unterscheidet, werden A-B-C und ABC als gleichwertig behandelt.

  • Zusätzliche Optionen, z. B. Einstellungen für die Sortierung anhand des ersten Buchstabens in einer Zeichenfolge und das Abschneiden von führenden und/oder nachstehenden Leerzeichen.

Anwendungsgebiete für Sortierung

Die Sortierung kann für eine Vielzahl von Vorgängen verwendet werden, einschließlich (aber nicht beschränkt auf):

Verwendung

Beispiel

Verknüpfung

Einfacher Vergleich

... WHERE column1 = column2 ...

WHERE

Joins

... ON table1.column1 = table2.column2 ...

JOIN

Sortierung

... ORDER BY column1 ...

ORDER BY

Top-K-Sortierung

... ORDER BY column1 LIMIT N ...

LIMIT / FETCH

Aggregation

... GROUP BY ...

GROUP BY

Fensterklauseln

... PARTITION BY ... ORDER BY ...

Fensterfunktionen

Skalarfunktionen

... LEAST(column1, column2, column3) ...

Skalarfunktionen

Aggregatfunktionen

... MIN(column1), MAX(column1) ...

Aggregatfunktionen

Daten-Clustering

... CLUSTER BY (column1) ...

Gruppierungsschlüssel und geclusterte Tabellen

Sortierungssteuerung

Die Sortierungssteuerung ist granular. Sie können die zu verwendende Sortierung explizit angeben für:

  • Ein Konto, mithilfe des Kontoebenenparameters DEFAULT_DDL_COLLATION

  • Alle Spalten in allen einer Datenbank hinzugefügten Tabellen, mithilfe des Befehls ALTER DATABASE

  • Alle Spalten in allen einem Schema hinzugefügten Tabellen, mithilfe des Befehls ALTER SCHEMA

  • Alle Spalten, die einer Tabelle hinzugefügt wurden, mithilfe des Befehls ALTER TABLE

  • Einzelne Spalten in einer Tabelle mit dem Befehl CREATE TABLE.

  • Einen bestimmten Vergleich innerhalb einer SQL-Anweisung (z. B. WHERE col1 = col2). Wenn auf eine Anweisung mehrere Sortierungen angewendet werden, wählt Snowflake die zu verwendende Sortierung anhand der Priorität aus. Weitere Details zur Priorität finden Sie unter Sortierungsrangfolge bei Operationen mit mehreren Zeichenfolgen (unter diesem Thema).

SQL-Konstrukte für Sortierung

COLLATE-Klausel (für Tabellenspaltendefinitionen)

Das Hinzufügen der optionalen COLLATE-Klausel zur Definition einer Tabellenspalte gibt an, dass die angegebene Sortierung für Vergleiche und andere verwandte Vorgänge verwendet wird, die für die Daten in der Spalte ausgeführt werden:

CREATE TABLE <table_name> ( <col_name> <col_type> COLLATE '<collation_specification>'
                            [ , <col_name> <col_type> COLLATE '<collation_specification>' ... ]
                            [ , ... ]
                          )
Copy

Wenn für eine Spalte keine COLLATE-Klausel angegeben ist, verwendet Snowflake die Standardklausel, bei der Zeichenfolgen anhand ihrer UTF-8-Zeichenrepräsentationen verglichen werden.

Außerdem unterstützt Snowflake die Angabe einer leeren Zeichenfolge für die Sortierungsspezifikation (z. B. COLLATE ''), was einer Angabe ohne Sortierung für die Spalte entspricht.

Beachten Sie jedoch, dass die Angabe von COLLATE '' für eine Spalte aus Gründen der Priorität nicht die gleiche Wirkung hat wie die explizite Angabe von COLLATE 'utf8'. Weitere Details dazu finden Sie unter Sortierungsrangfolge bei Operationen mit mehreren Zeichenfolgen (unter diesem Thema).

Verwenden Sie DESCRIBE TABLE, um festzustellen, ob für die Spalten in einer Tabelle eine Sortierung angegeben wurde (oder nutzen Sie die Funktion COLLATION, um gegebenenfalls die Sortierung für eine bestimmte Spalte anzuzeigen).

COLLATE-Funktion

Diese Funktion verwendet die angegebene Sortierung für den eingegebenen Zeichenfolgenausdruck:

COLLATE( <expression> , '[<collation_specification>]' )
Copy

Diese Funktion kann auch mit der infix-Notation aufgerufen werden:

<expression> COLLATE '[<collation_specification>]'
Copy

Diese Funktion ist besonders nützlich, um explizit eine bestimmte Sortierung für eine bestimmte Operation anzugeben (z. B. Sortieren). Sie kann jedoch auch verwendet werden, um:

  • Eine Sortierung in der SELECT-Klausel einer Unterabfrage zu erlauben, sodass alle Operationen in der angegebenen Spalte in der äußeren Abfrage die Sortierung nutzen.

  • Erstellen Sie unter Verwendung von CTAS eine Tabelle mit einer angegebenen Sortierung.

Beispiel:

-- Evaluates using "English case-insensitive" collation:
SELECT * FROM t1 WHERE COLLATE(col1 , 'en-ci') = 'Tango';

-- Sorts the results using German (Deutsch) collation.
SELECT * FROM t1 ORDER BY COLLATE(col1 , 'de');

-- Creates a table with a column using French collation.
CREATE TABLE t2 AS SELECT COLLATE(col1, 'fr') AS col1 FROM t1;

-- Creates a table with a column using French collation.
CREATE TABLE t2 AS SELECT col1 COLLATE 'fr' AS col1 FROM t1;
Copy

COLLATION-Funktion

Diese Funktion gibt die Sortierungsspezifikation zurück, die von einem Ausdruck verwendet wird, einschließlich einer Tabellenspalte:

COLLATION( <expression> )
Copy

Wenn für den Ausdruck keine Sortierung angegeben wurde, gibt die Funktion NULL zurück.

Wenn Sie dies für einen Spaltennamen verwenden, sollten Sie mit DISTINCT verhindern, dass für jede Zeile in der Tabelle eine Ausgabezeile zurückgegeben wird. Beispiel:

SELECT DISTINCT COLLATION(column1) FROM table1;
Copy

Bemerkung

Diese Funktion gibt nur die Sortierungsspezifikation zurück, nicht die Prioritätsstufe. Weitere Details zur Priorität finden Sie unter Sortierungsrangfolge bei Operationen mit mehreren Zeichenfolgen (unter diesem Thema).

Sortierungsspezifikationen

Wenn Sie eine COLLATE-Klausel (für eine Tabellenspalte) oder die Funktion COLLATE (für einen Ausdruck) nutzen, müssen Sie eine Sortierungsspezifikation angeben, die über die für die Spalte/den Ausdruck verwendete Vergleichslogik bestimmt.

Eine Sortierungsspezifikation besteht aus einer Zeichenfolge mit einem oder mehreren durch einen Bindestrich (-) getrennten Spezifizierern, und zwar in Form von:

'<specifier>[-<specifier> ...]'

Folgende Spezifizierer werden unterstützt (Details finden Sie unter Unterstützte Spezifizierer unter diesem Thema):

  • Gebietsschema.

  • Unterscheidung nach Groß-/Kleinschreibung.

  • Unterscheidung nach Akzent.

  • Unterscheidung nach Interpunktion.

  • Präferenz für den ersten Buchstaben.

  • Konvertierung der Groß-/Kleinschreibung

  • Platzsparend.

Bei Spezifizierern wird nicht nach Groß- und Kleinschreibung unterschieden. Sie können in beliebiger Reihenfolge angegeben werden, mit Ausnahme des Gebietsschemas, das bei Verwendung immer an erster Stelle stehen muss.

Spezifikationsbeispiele

Einige Beispiele für Sortierungsspezifikationszeichenfolgen umfassen:

  • 'de': Deutsches Gebietsschema.

  • 'de-ci-pi': Deutsches Gebietsschema mit Vergleichen, bei denen Groß-/Kleinschreibung sowie Interpunktion nicht unterschieden werden.

  • 'fr_CA-ai': Gebietsschema für kanadisches Französisch mit Vergleichen ohne Unterscheidung nach Akzent.

  • 'en_US-trim': Gebietsschema für US-Englisch, wobei führende und nachstehende Leerzeichen vor dem Vergleich abgeschnitten werden.

Sie können auch eine leere Zeichenfolge für eine Sortierungsspezifikation festlegen (z. B. COLLATE '' oder COLLATE(col1, '')), die angibt, dass keine Sortierung verwendet werden soll.

Unterstützte Spezifizierer

Gebietsschema

Gibt die anzuwendenden sprach- und länderspezifischen Regeln an.

Unterstützt gültige Gebietsschema-Zeichenfolgen, die aus einem Sprachcode (erforderlich) und einem Ländercode (optional) in der Form language_country bestehen. Zu Beispielen gehören:

  • en – Englisch.

  • en_US – Amerikanisches Englisch.

  • fr – Französisch

  • fr_CA – kanadisches Französisch.

Darüber hinaus gibt das Pseudo-Gebietsschema utf8 an, dass Unicode-Sortierung verwendet werden soll. Dabei handelt es sich um die Standardeinstellung. Weitere Details dazu finden Sie unter Sortieren nach UTF-8 vs. Sortierung nach Gebietsschema (unter diesem Thema).

Der Gebietsschemaspezifizierer ist optional, muss jedoch, falls verwendet, der erste Spezifizierer in der Zeichenfolge sein.

Unterscheidung nach Groß-/Kleinschreibung

Legt fest, ob beim Vergleichen von Werten nach Groß-/Kleinschreibung unterschieden werden soll. Mögliche Werte:

  • cs – Zwischen Groß- und Kleinschreibung wird unterschieden (Standard).

  • ci – Zwischen Groß- und Kleinschreibung wird nicht unterschieden.

Beispiel:

Sortierungsspezifikation 

Wert

Ergebnis

'en-ci'

Abc = abc

True

'en-cs' / en

Abc = abc

False

Unterscheidung nach Akzent

Legt fest, ob Zeichen mit Akzenten als gleich oder verschieden von ihren Basiszeichen angesehen werden sollen. Mögliche Werte:

  • as – Es wird nach Akzent unterschieden (Standardeinstellung).

  • ai – Es wird nicht nach Akzent unterschieden.

Beispiel:

Sortierungsspezifikation 

Wert

Ergebnis

Anmerkungen

'fr-ai'

E = É

True

'fr-as' / 'fr'

E = É

False

'en-ai'

a = ą

True

Im Englischen werden diese Buchstaben so behandelt, als ob sie nur Akzentunterschiede aufweisen würden. Wenn Sie also keine Unterscheidung nach Akzent angeben, werden die Werte bei Vergleichen als gleich betrachtet.

'pl-ai'

a = ą

False

In der polnischen Sprache werden diese Buchstaben als separate Grundbuchstaben behandelt, sodass sie unabhängig von der Angabe der Unterscheidung nach Akzent immer als ungleich gelten.

'pl-as' / 'pl'

a = ą

False

Beachten Sie, dass die Regeln für die Unterscheidung nach Akzent und die Sortierung von Sprache zu Sprache verschieden sind. In einigen Sprachen wird bei der Sortierung beispielsweise immer nach Akzenten unterschieden; in dem Fall können Sie die Unterscheidung nicht deaktivieren, selbst wenn Sie eine akzentunabhängige Sortierung auswählen.

Unterscheidung nach Interpunktion

Legt fest, ob Nichtbuchstaben eine Rolle spielen. Mögliche Werte:

  • ps – Interpunktionsempfindlich.

  • pi – Interpunktionsunempfindlich.

Beachten Sie, dass die Standardeinstellung vom Gebietsschema abhängt (d. h., wenn die Unterscheidung nach Interpunktion nicht festgelegt ist, werden Regeln je nach dem Gebietsschema verwendet). In den meisten Fällen entsprechen die Regeln ps.

Beispiel:

Sortierungsspezifikation 

Wert

Ergebnis

Anmerkungen

'en-pi'

A-B-C = ABC

True

'en-ps'

A-B-C = ABC

False

Präferenz für den ersten Buchstaben

Legt fest, ob beim Sortieren Groß- oder Kleinbuchstaben an erster Stelle stehen sollen. Mögliche Werte:

  • fl – Kleinbuchstaben zuerst sortiert.

  • fu – Großbuchstaben zuerst sortiert.

Beachten Sie, dass die Standardeinstellung vom Gebietsschema abhängt (d. h., wenn kein Wert angegeben ist, wird eine Sortierung auf Basis des Gebietsschemas verwendet). In den meisten Fällen entspricht die Sortierung fl.

Außerdem hat dieser Spezifizierer keinen Einfluss auf Gleichheitsvergleiche.

Konvertierung der Groß-/Kleinschreibung

Führt dazu, dass Zeichenfolgen vor Vergleichen in Klein- oder Großbuchstaben konvertiert werden. In einigen Fällen ist dies schneller als eine vollständige Sortierung anhand des Gebietsschemas. Mögliche Werte:

  • upper – Konvertieren der Zeichenfolge vor Vergleichen in Großbuchstaben.

  • lower – Konvertieren der Zeichenfolge vor Vergleichen in Kleinbuchstaben.

Beachten Sie, dass dieser Spezifizierer keinen Standardwert hat (d. h. wenn kein Wert angegeben ist, findet keine der Konvertierungen statt).

Platzsparend

Entfernt vor Vergleichen führende/nachstehende Leerzeichen aus Zeichenfolgen. Diese Funktion kann nützlich sein, um Vergleiche durchzuführen, die (außer in äußerst seltenen Fällen) der Semantik des Datentyps SQL CHAR entsprechen.

Mögliche Werte:

  • trim – Entfernen sowohl führender als auch nachstehender Leerzeichen vor Vergleichen.

  • ltrim – Entfernen nur führender Leerzeichen vor Vergleichen.

  • rtrim – Entfernen nur nachstehender Leerzeichen vor Vergleichen.

Beachten Sie, dass dieser Spezifizierer keinen Standardwert hat (d. h. wenn kein Wert angegeben ist, wird keine Kürzung vorgenommen).

Beispiel:

Sortierungsspezifikation 

Wert

Ergebnis

Anmerkungen

'en-trim'

__ABC_ = ABC

True

In diesen Beispielen stehen Unterstriche für Leerzeichen.

'en-ltrim'

__ABC_ = ABC

False

'en-rtrim'

__ABC_ = ABC

False

'en'

__ABC_ = ABC

False

Details zur Sortierungsimplementierung

Vergleich ohne Berücksichtigung der Groß-/Kleinschreibung

Vergleichen von Zeichenfolgen mit Großbuchstaben vs. Vergleichen der ursprünglichen Zeichenfolgen

In einigen Sprachen haben zwei Kleinbuchstaben den gleichen Großbuchstaben. Einige Sprachen unterstützen z. B. sowohl gepunktete als auch nicht gepunktete Formen der Kleinschreibung I (i vs. ı). Das Erzwingen von Großbuchstaben bei Zeichenfolge hat Auswirkungen auf das Vergleichen.

Im Folgenden wird der Unterschied verdeutlicht:

Erstellen Sie die Tabelle:

create or replace table test_table (col1 varchar, col2 varchar);
insert into test_table values ('ı', 'i');
Copy

Führen Sie Abfragen auf den Daten aus:

select col1 = col2,
       COLLATE(col1, 'lower') = COLLATE(col2, 'lower'),
       COLLATE(col1, 'upper') = COLLATE(col2, 'upper')
    from test_table;
+-------------+-------------------------------------------------+-------------------------------------------------+
| COL1 = COL2 | COLLATE(COL1, 'LOWER') = COLLATE(COL2, 'LOWER') | COLLATE(COL1, 'UPPER') = COLLATE(COL2, 'UPPER') |
|-------------+-------------------------------------------------+-------------------------------------------------|
| False       | False                                           | True                                            |
+-------------+-------------------------------------------------+-------------------------------------------------+
Copy

Zeichengewichtungen

Snowflake unterstützt die folgenden Sortierspezifikationen.

  • ICU (International Components for Unicode).

  • Snowflake-spezifische Sortierspezifikationen (z. B. upper und lower).

Bei Vergleichsoperationen ohne Berücksichtigung der Groß-/Kleinschreibung, die durch ICU definiert sind, folgt Snowflake dem Unicode Collation Algorithm (UCA) und berücksichtigt nur die primären und sekundären Gewichtungen, aber nicht die tertiären Gewichtungen, von Unicode-Zeichen. Zeichen, die sich nur in ihren Tertiärgewichtungen unterscheiden, werden als identisch behandelt. So werden bei der en-ci-Sortierspezifikation beispielsweise ein Leerzeichen und ein nicht umgebrochenes Leerzeichen als identisch angesehen.

Sortieren nach UTF-8 vs. Sortierung nach Gebietsschema

Zeichenfolgen werden in Snowflake immer in UTF-8 gespeichert und können alle Zeichen in allen Sprachen darstellen, die von UTF-8 unterstützt werden. Daher lautet die Standardsortierung UTF-8 (d. h. 'utf8').

UTF-8-Sortierung basiert auf der numerischen Darstellung des Zeichens und nicht auf der alphabetischen Rangfolge des Zeichens.

Dies ist analog zur Sortierung nach dem Ordnungswert einzelner ASCII-Zeichen. Dies ist wichtig, da Großbuchstaben Ordnungswerte haben, die kleiner sind als bei Kleinbuchstaben:

A = 65
B = 66
...
a = 97
b = 98
...

Infolgedessen:

  • Wenn Sie in der UTF-8-Reihenfolge sortieren, werden alle Großbuchstaben vor allen Kleinbuchstaben zurückgegeben:

    A, B, …, Y, Z, …, a, b, …, y, z

  • Im Gegensatz dazu wird die Sortierungsspezifikation 'en' alphabetisch sortiert (anstatt die interne UTF-8-Darstellung zu verwenden), sodass sowohl A als auch a vor B und b zurückgegeben werden:

    a, A, b, B, …

Darüber hinaus wirken sich die Unterschiede zwischen den cs- und ci-Spezifizierern zur Unterscheidung nach Groß-/Kleinschreibung auf die Sortierung aus:

  • cs (Unterscheidung nach Groß-/Kleinschreibung) gibt immer die Kleinbuchstabenversion eines Buchstabens vor der Großbuchstabenversion desselben Buchstabens zurück. Beispiel mit 'en-cs':

    a, A, b, B, …

    Beachten Sie, dass die Unterscheidung nach Groß-/Kleinschreibung die Standardeinstellung ist und daher 'en-cs' und 'en' gleichwertig sind.

  • ci (ohne Unterscheidung der Groß-/Kleinschreibung) gibt Versionen der Groß-/Kleinschreibung von Buchstaben in zufälliger Relation zueinander zurück, jedoch noch vor der Groß-/Kleinschreibung von späteren Buchstaben. Beispiel mit 'en-ci':

    A, a, b, B, …

Auch einige nicht alphabetische Zeichen können je nach Sortiereinstellung unterschiedlich sortiert werden. Das folgende Codebeispiel zeigt, dass das Pluszeichen (+) und das Minuszeichen (-) bei verschiedenen Sortiereinstellungen unterschiedlich sortiert werden:

Erstellen Sie die Tabelle:

create or replace table demo (
    no_explicit_collation VARCHAR,
    en_ci VARCHAR COLLATE 'en-ci',
    en VARCHAR COLLATE 'en',
    utf_8 VARCHAR collate 'utf8');
insert into demo (no_explicit_collation) values
    ('-'),
    ('+');
update demo SET
    en_ci = no_explicit_collation,
    en = no_explicit_collation,
    utf_8 = no_explicit_collation;
Copy

Führen Sie Abfragen auf den Daten aus:

select max(no_explicit_collation), max(en_ci), max(en), max(utf_8)
    from demo;
+----------------------------+------------+---------+------------+
| MAX(NO_EXPLICIT_COLLATION) | MAX(EN_CI) | MAX(EN) | MAX(UTF_8) |
|----------------------------+------------+---------+------------|
| -                          | +          | +       | -          |
+----------------------------+------------+---------+------------+
Copy

Sortierungsrangfolge bei Operationen mit mehreren Zeichenfolgen

Wenn Sie eine Operation für zwei (oder mehr) Zeichenfolgen ausführen, können unterschiedliche Sortierungen für unterschiedliche Zeichenfolgen angegeben werden. Das Festlegen der anzuwendenden Sortierung hängt davon ab, wie die Sortierung für einzelne Eingaben und die Rangfolge der einzelnen Spezifizierer angegeben wurden.

Es gibt drei Prioritätsstufen (von der höchsten zur niedrigsten):

Funktion

Die Sortierung wird mithilfe der Funktion COLLATE-Funktion in einer SQL-Anweisung angegeben.

Spalte

Die Sortierung wurde in der Spaltendefinition angegeben.

Keine

Für einen bestimmten Ausdruck/eine bestimmte Spalte ist/wurde keine Sortierung angegeben, oder es wird/wurde eine Sortierung mit einer leeren Spezifizierung verwendet (z. B. COLLATE(col1, '') oder col1 STRING COLLATE '').

Bei der Bestimmung der zu verwendenden Sortierung wird die Sortierungsspezifikation mit der höchsten Priorität verwendet. Wenn verschiedene Sortierungen angegeben sind und dieselbe Prioritätsstufe aufweisen, werden ihre Werte verglichen; wenn sie nicht gleich sind, wird ein Fehler zurückgegeben.

Betrachten Sie beispielsweise eine Tabelle mit den folgenden Sortierungsspezifikationen auf Spaltenebene:

CREATE OR REPLACE TABLE collation_precedence_example(
  col1    VARCHAR,               -- equivalent to COLLATE ''
  col2_fr VARCHAR COLLATE 'fr',  -- French locale
  col3_de VARCHAR COLLATE 'de'   -- German locale
);
Copy

Wenn die Tabelle in einer Anweisung verwendet wird, die zwei Zeichenfolgen miteinander vergleicht, wird die Sortierung wie folgt angewendet:

-- Uses the 'fr' collation because the precedence for col2_fr is higher than
-- the precendence for col1.
... WHERE col1 = col2_fr ...

-- Uses the 'en' collation, because it is explicitly specified in the statement,
-- which takes precedence over the collation for col2_fr.
... WHERE col1 COLLATE 'en' = col2_fr ...

-- Returns an error because the expressions have different collations at the same
-- precedence level.
... WHERE col2_fr = col3_de ...

-- Uses the 'de' collation because collation for col2_fr has been removed.
... WHERE col2_fr COLLATE '' = col3_de ...

-- Returns an error because the expressions have different collations at the same
-- precedence level.
... WHERE col2_fr COLLATE 'en' = col3_de COLLATE 'de' ...
Copy

Bemerkung

Auch wenn die Standardsortierung von Snowflake 'utf8' ist, unterscheidet sich die Angabe einer leeren Zeichenfolge (oder die Angabe keiner Sortierung) von der expliziten Angabe von 'utf8', da die explizite Sortierung eine höhere Priorität hat als keine Sortierung. Die letzten beiden Anweisungen in den folgenden Codebeispielen machen den Unterschied deutlich:

CREATE OR REPLACE TABLE collation_precedence_example2(
  s1 STRING COLLATE '',
  s2 STRING COLLATE 'utf8',
  s3 STRING COLLATE 'fr'
);

-- Uses 'utf8' because s1 has no collation and 'utf8' is the default.
SELECT * FROM collation_precedence_example2 WHERE s1 = 'a';

-- Uses 'utf8' because s1 has no collation and s2 has explicit 'utf8' collation.
SELECT * FROM collation_precedence_example2 WHERE s1 = s2;
Copy

Dieses Beispiel wird fehlerfrei ausgeführt, da s1 keine Sortierung und s3 eine explizite fr-Sortierung hat, sodass die explizite Sortierung Vorrang hat:

SELECT * FROM collation_precedence_example2 WHERE s1 = s3;
+----+----+----+
| S1 | S2 | S3 |
|----+----+----|
+----+----+----+
Copy

Dieses Beispiel verursacht einen Fehler, da für s2 und s3 unterschiedliche Sortierungen auf derselben Prioritätsstufe angegeben sind:

SELECT * FROM collation_precedence_example2 WHERE s2 = s3;
Copy

Ausgabe:

002322 (42846): SQL compilation error: Incompatible collations: 'fr' and 'utf8'
Copy

Eingeschränkte Unterstützung für Sortierung in integrierten Funktionen

Sortierung wird nur in einer Teilmenge von Zeichenfolgenfunktionen unterstützt. Funktionen, bei denen eine Implementierung der Sortierung vernünftigerweise erwartet werden kann, die jedoch keine Sortierung unterstützen, geben bei Verwendung der Sortierung einen Fehler zurück. Diese Fehlermeldungen werden nicht nur beim Aufrufen von COLLATE angezeigt, sondern auch beim Aufrufen einer Zeichenfolgenfunktion für eine Spalte, die in der Anweisung CREATE TABLE oder ALTER TABLE, mit der diese Spalte erstellt wurde, als sortiert definiert wurde.

Gegenwärtig beeinflusst die Sortierung nur einfache Vergleichsoperationen.

Beispiel: POSITION('abc' in COLLATE('ABC', 'en-ci')) findet abc nicht in ABC, obwohl Sortierung ohne Unterscheidung nach Groß-/Kleinschreibung festgelegt ist.

Funktionen, die Sortierung unterstützen

Diese Funktionen unterstützen Sortierung:

Einige dieser Funktionen weisen Einschränkungen bei der Verwendung mit Sortierung auf. Einzelheiten finden Sie in der Dokumentation für die einzelnen Funktionen.

Diese Liste kann im Laufe der Zeit erweitert werden.

Vorsicht

Einige SQL-Operatoren und -Prädikate wie || (Verkettung) und LIKE werden als Funktionen implementiert (und sind als Funktionen verfügbar, z. B. LIKE() und CONCAT()). Wenn ein Prädikat oder Operator als Funktion implementiert ist und die Funktion keine Sortierung unterstützt, unterstützt das Prädikat oder der Operator ebenfalls keine Sortierung.

LIKE ist ein Beispiel für ein Prädikat, das keine Sortierung unterstützt.

Siehe auch Sortierungsbeschränkungen.

Leistungsauswirkungen bei Verwendung der Sortierung

Eine Nutzung von Sortierung kann sich auf die Leistung verschiedener Datenbankvorgänge auswirken:

  • Vorgänge mit Vergleichen werden möglicherweise langsamer.

    Dies kann Auswirkungen auf einfache WHERE-Klauseln sowie Verknüpfungen, Sortierungen, GROUP BY-Operationen usw. haben.

  • Bei Verwendung mit einigen Funktionen in WHERE-Prädikaten kann das Bereinigen von Mikropartitionen weniger effizient ausfallen.

  • Eine Verwendung von Sortierung in einem WHERE-Prädikat, das sich von der für die Spalte angegebenen Sortierung unterscheidet, kann zu einer verringerten Bereinigungseffizienz führen oder die Bereinigung vollständig eliminieren.

Weitere Hinweise zur Verwendung der Sortierung

  • Beachten Sie, dass die folgenden Sortierungsfunktionen trotz der Ähnlichkeit ihrer Namen unterschiedliche Ergebnisse liefern:

    • COLLATE gibt explizit an, welche Sortierung verwendet werden soll.

    • COLLATION zeigt an, welche Sortierung verwendet wird, wenn keine explizit angegeben ist.

  • In einer Spalte mit einer Sortierungsspezifikation können Zeichen verwendet werden, die nicht zum Gebietsschema der Sortierung gehören. Dies kann sich auf die Sortierung auswirken.

    Wenn beispielsweise eine Spalte mit einer COLLATE 'en'-Klausel erstellt wird, können die Daten in der Spalte das nicht englische Zeichen É enthalten. In diesem Fall wird das Zeichen É in der Nähe von E sortiert.

  • Sie können Sortiervorgänge angeben, die nicht unbedingt von Bedeutung sind.

    Sie können beispielsweise festlegen, dass polnische Daten mit französischen Daten anhand einer deutschen Sortierung verglichen werden:

    SELECT ... WHERE COLLATE(French_column, 'de') = Polish_column;
    
    Copy

    Snowflake empfiehlt jedoch, das Feature nicht auf diese Weise zu verwenden, da möglicherweise unerwartete oder unbeabsichtigte Ergebnisse zurückgegeben werden.

  • Sobald eine Tabellenspalte definiert ist, können Sie die Sortierung für die Spalte nicht mehr ändern. Mit anderen Worten: Nachdem eine Spalte mit einer bestimmten Sortierung unter Verwendung einer CREATE TABLE-Anweisung erstellt wurde, können Sie die Sortierung mit ALTER TABLE nicht mehr ändern.

    Sie können jedoch in einer DML-Anweisung eine andere Sortierung angeben, z. B. eine SELECT-Anweisung, die auf die Spalte verweist.

Sortierungsbeschränkungen

Die Sortierung wird nur für Zeichenfolgen bis 8 MB unterstützt.

Obwohl der Snowflake-Datentyp VARCHAR Zeichenfolgen bis 16 MB unterstützt, unterstützt Snowflake die Sortierung nur, wenn die resultierende Zeichenfolge eine maximale Größe von 8 MB aufweist. (Einige Sortieroperationen können zu einer Verlängerung der Zeichenfolge führen).

Einschränkungen bei Sortierung und UDFs

Snowflake unterstützt keine Sortierung mit UDFs (benutzerdefinierte Funktionen):

  • Sie können von einer UDF keinen sortierten Zeichenfolgenwert zurückgeben. Der Server meldet, dass der tatsächliche Rückgabetyp nicht mit dem deklarierten Rückgabetyp kompatibel ist.

  • Wenn Sie einen sortierten Zeichenfolgenwert an eine UDF übergeben, wird die Sortierungsinformation nicht übergeben. Die UDF behandelt die Zeichenfolge wie eine nicht sortierte Zeichenfolge.

Sortierung wird bei Zeichenfolgen in VARIANT, ARRAY oder OBJECT nicht unterstützt

In VARIANT, OBJECT oder ARRAY gespeicherte Zeichenfolgen enthalten keine Sortierungsspezifikation. Deshalb gilt:

  • Beim Vergleich dieser Werte wird immer die Sortierung „utf8“ verwendet.

  • Wenn ein VARCHAR-Wert mit einer Sortierungsspezifikation verwendet wird, um einen ARRAY-, OBJECT- oder VARIANT-Wert zu erstellen, wird die Sortierungsspezifikation nicht beibehalten.

  • Benutzer können dennoch einen in einem ARRAY, OBJECT oder VARIANT gespeicherten Wert vergleichen, indem sie den Wert extrahieren, in VARCHAR umwandeln und eine Sortierungsspezifikation hinzufügen. Beispiel:

    COLLATE(VARIANT_COL:fld1::VARCHAR, 'en-ci') = VARIANT_COL:fld2::VARCHAR
    
    Copy

Beispiele

Die folgende Anweisung erstellt eine Tabelle, die für jede Spalte eine andere Sortierung verwendet:

CREATE TABLE collation_demo (
  uncollated_phrase VARCHAR, 
  utf8_phrase VARCHAR COLLATE 'utf8',
  english_phrase VARCHAR COLLATE 'en',
  spanish_phrase VARCHAR COLLATE 'sp'
  );

INSERT INTO collation_demo (uncollated_phrase, utf8_phrase, english_phrase, spanish_phrase) 
   VALUES ('pinata', 'pinata', 'pinata', 'piñata');
Copy

Die folgende Abfrage für die Tabelle ergibt die erwarteten Werte:

SELECT * FROM collation_demo;
+-------------------+-------------+----------------+----------------+
| UNCOLLATED_PHRASE | UTF8_PHRASE | ENGLISH_PHRASE | SPANISH_PHRASE |
|-------------------+-------------+----------------+----------------|
| pinata            | pinata      | pinata         | piñata         |
+-------------------+-------------+----------------+----------------+
Copy

Die folgende Abfrage findet keine Übereinstimmung, da das Zeichen ñ nicht mit n übereinstimmt:

SELECT * FROM collation_demo WHERE spanish_phrase = uncollated_phrase;
+-------------------+-------------+----------------+----------------+
| UNCOLLATED_PHRASE | UTF8_PHRASE | ENGLISH_PHRASE | SPANISH_PHRASE |
|-------------------+-------------+----------------+----------------|
+-------------------+-------------+----------------+----------------+
Copy

Beim Ändern der Sortierung werden verwandte, aber ungleiche Zeichen (z. B. ñ und n) nicht als gleich behandelt:

SELECT * FROM collation_demo 
    WHERE spanish_phrase = uncollated_phrase COLLATE 'sp';
+-------------------+-------------+----------------+----------------+
| UNCOLLATED_PHRASE | UTF8_PHRASE | ENGLISH_PHRASE | SPANISH_PHRASE |
|-------------------+-------------+----------------+----------------|
+-------------------+-------------+----------------+----------------+
Copy

Die folgenden Beispiele veranschaulichen die Auswirkung der Sortierung auf die Sortierreihenfolge:

INSERT INTO collation_demo (spanish_phrase) VALUES
   ('piña colada'),
   ('Pinatubo (Mount)'),
   ('pint'),
   ('Pinta');
Copy
SELECT spanish_phrase FROM collation_demo 
  ORDER BY spanish_phrase;
+------------------+
| SPANISH_PHRASE   |
|------------------|
| piña colada      |
| piñata           |
| Pinatubo (Mount) |
| pint             |
| Pinta            |
+------------------+
Copy

Die folgende Abfrage kehrt die Reihenfolge von ñ und n um, indem die Sortierung von „sp“ (Spanisch) in „utf8“ geändert wird:

SELECT spanish_phrase FROM collation_demo 
  ORDER BY COLLATE(spanish_phrase, 'utf8');
+------------------+
| SPANISH_PHRASE   |
|------------------|
| Pinatubo (Mount) |
| Pinta            |
| pint             |
| piña colada      |
| piñata           |
+------------------+
Copy

In diesem Beispiel wird gezeigt, wie Sie mit der Funktion COLLATION die Sortierung für einen Ausdruck anzeigen können, z. B. für eine Spalte:

CREATE TABLE collation_demo2 (c1 VARCHAR COLLATE 'fr', c2 VARCHAR COLLATE '');
INSERT INTO collation_demo2 (c1, c2) VALUES
    ('a', 'a'),
    ('b', 'b');
Copy
SELECT DISTINCT COLLATION(c1), COLLATION(c2) FROM collation_demo2;
+---------------+---------------+
| COLLATION(C1) | COLLATION(C2) |
|---------------+---------------|
| fr            | NULL          |
+---------------+---------------+
Copy

Sie können auch DESCRIBE TABLE verwenden, um Sortierungsinformationen für die Spalten in einer Tabelle anzuzeigen:

DESC TABLE collation_demo2;
+------+--------------------------------+--------+-------+---------+-------------+------------+-------+------------+---------+-------------+ 
| name | type                           | kind   | null? | default | primary key | unique key | check | expression | comment | policy name |
|------+--------------------------------+--------+-------+---------+-------------+------------+-------+------------+---------+-------------|
| C1   | VARCHAR(16777216) COLLATE 'fr' | COLUMN | Y     | NULL    | N           | N          | NULL  | NULL       | NULL    | NULL        |
| C2   | VARCHAR(16777216)              | COLUMN | Y     | NULL    | N           | N          | NULL  | NULL       | NULL    | NULL        |
+------+--------------------------------+--------+-------+---------+-------------+------------+-------+------------+---------+-------------+
Copy