Sortierung

Gilt für: Databricks SQL Databricks Runtime 16.1 und höher

Eine Sortierung ist eine Reihe von Regeln, die bestimmt, wie Zeichenfolgenvergleiche ausgeführt werden. Sortierungen unterstützen die Groß-/Kleinschreibung, die Akzenterkennung und nachfolgende Vergleiche zwischen Leerzeichen und die Zeichenfolgenreihenfolge mit Spracherkennung.

Zeichenfolgen in Azure Databricks werden als UTF-8 codiert Unicodezeichen dargestellt. Standardmäßig vergleicht Azure Databricks Zeichenfolgen anhand ihrer binären UTF-8-Darstellung, die als UTF8_BINARY Sortierung bezeichnet wird. UTF8_BINARY Vergleiche sind in vielen Fällen schnell und geeignet, eignen sich aber möglicherweise nicht für Anwendungen, die sprachfähige Sortierungen oder Vergleiche erfordern.

Über binäre Vergleiche hinaus ist bei einem gängigen Anwendungsfall der Abgleich zwischen Groß- und Kleinschreibung nicht zu beachten. Die UTF8_LCASE Sortierung ist für diesen Zweck konzipiert. Sie konvertiert Zeichenfolgen in Kleinbuchstaben, bevor sie mit der Verwendung verglichen werden UTF8_BINARY.

Für sprachfähige Vergleiche verwendet Azure Databricks die folgenden Technologien:

• Internationale Komponenten für Unicode(ICU) -Bibliothek zum Berechnen der Sortierung.
• Common Locale Data Repository (CLDR) -Tabellen für gebietsschemabasierte Sortierung.
• Unicode Locale Data Markup Language (LDML), um Kollationen intern zu codieren.

Diese Technologien werden in einer Reihe von benannten Sortierungen gekapselt, die für die Verwendung in SQL-Anweisungen verfügbar sind.

Sortierungsnamen

Azure Databricks bietet benannte Systemsortierungen zur Vereinfachung der Identifizierung. LDML-Spezifikationen können komplex sein, um direkt zu lesen und zu verwenden.

Syntax

{ UTF8_BINARY |
  UTF8_LCASE |
  { UNICODE | locale } [ _ modifier [...] ] }

locale
  language_code [ _ script_code ] [ _ country_code ]

modifier
  { CS | CI | AS | AI | RTRIM }

• UTF8_BINARY

  Eine binäre Sortierung, die Zeichenfolgen byte byte anhand ihrer UTF-8-Darstellung vergleicht. UTF8_BINARY ist die Standardsortierung und die einfachste Sortierung in Azure Databricks.

  In dieser Sortierung: 'A' (x'65') <'B' (x'66') < ... < 'Z' (x'90'). 'Z' (x'90')'a'<(x'97') und 'A' (x'65') <>'a' (x'97'). Zeichen wie 'Ä' (x'C384') sind größer als beide 'Z' und 'z'.

• UTF8_LCASE

  Eine einfache Sortierung ohne Groß-/Kleinschreibung, die Zeichenfolgen in Kleinbuchstaben konvertiert, bevor sie mit der Verwendung UTF8_BINARYverglichen werden.

  UTF8_LCASE ist die Sortierung, die für Identifiers in Azure Databricks verwendet wird.

  Beispiel:

  ORDER BY col COLLATE UTF8_LCASE
  

  entspricht:

  ORDER BY LOWER(col) COLLATE UTF8_BINARY
  

• UNICODE

  Das ICU-Stammgebietsschema, das in CLDR als root Gebietsschema (LDML-Spezifikation: ) bezeichnet wird. und-u Diese Sortierung wendet eine sprachunabhängige Reihenfolge an, in der ähnliche Zeichen gruppiert werden. Beispiel: 'a''Ä'<'b''A'<<. Bei dieser Sortierung wird standardmäßig zwischen Groß- und Kleinschreibung unterschieden.

• locale

  Eine gebietsschemabasierte Sortierung basierend auf CLDR-Tabellen. Das Gebietsschema wird in Form eines Sprachcodes, eines optionalen Skriptcodes und eines optionalen Ländercodes angegeben. Gebietsschemawerte werden ohne Groß-/Kleinschreibung unterschieden.

  • language_code: Ein zweiseitiger ISO 639-1-Sprachcode .
  • script_code: Ein vierseitiger ISO 15924-Skriptcode .
  • country_code: Iso 3166-1 alpha-3 Ländercode mit drei Buchstaben.

• modifier

  Steuert die Groß-/Kleinschreibung, die Akzentempfindlichkeit und das nachfolgende Leerzeichenverhalten. Modifizierer werden ohne Groß-/Kleinschreibung unterschieden und können in beliebiger Reihenfolge angegeben werden.

  • CS: Groß-/Kleinschreibung wird beachtet. Das Standardverhalten.
  • CI: Groß-/Kleinschreibung wird nicht beachtet.
  • AS: Akzentsensitiv. Das Standardverhalten.
  • AI: Akzentunempfindlich.

  Gilt für: Databricks SQL Databricks Runtime 16.2 und höher

  • RTRIM: Nachgestelltes Leerzeichen wird nicht beachtet. Schneidet nachfolgende Leerzeichen (u0020) vor dem Vergleich ab.

  Gilt für: Databricks SQL Databricks Runtime 16.2 und höher

  Sie können höchstens eine oder CS mehrere von oder CImehrere AIAS angebenRTRIM.

Wenn Azure Databricks einen Sortierungsnamen verarbeitet, wird der Name normalisiert, indem Standardeinstellungen entfernt werden. Normalisiert z. B SR_CYR_SRN_CS_AS . auf SR.

Eine Liste der unterstützten Sortierungen finden Sie unter Unterstützte Sortierungen.

Beispiele

-- Fully qualified collation names are supported; case doesn't matter.
system.builtin.unicode

-- All collations are system-defined and do not require qualification.
unicode

-- Two-letter language code for German collation.
DE

-- Two-letter language code and three-letter country code for French Canadian collation.
fr_CAN

-- Two-letter language code, four-letter script code, and three-letter country code
-- for Traditional Chinese in Macao.
zh_Hant_MAC

-- German collation with case-insensitive and accent-insensitive modifiers.
-- 'Ä', 'A', and 'a' are all considered equal.
de_CI_AI

-- Backticks are allowed but not required for built-in collations.
`UTF8_BINARY`

Vergleichen UTF8_LCASE und UNICODE-Sortierungen

Sowohl als auch UTF8_LCASE UNICODE-basierte Sortierungen wie UNICODE_CI z. B. vergleiche zwischen Groß- und UNICODE_CI_AI Kleinschreibung unterstützen, unterscheiden sich jedoch darin, wie akzentierte Zeichen und gebietsschemaspezifische Regeln behandelt werden.

Verhaltensunterschiede

UTF8_LCASEkonvertiert Zeichenfolgen in Kleinbuchstaben und vergleicht sie mit Byte byte.UTF8_BINARY Es ist leicht und schnell, behandelt jedoch akzentierte Zeichen als anders als ihre unaccented Formen.

Unicode-basierte Sortierungen verwenden die ICU-Bibliothek und CLDR-Gebietsschemadaten. Die CI Modifizierer AI erweitern dies, um Zeichen auch dann als gleichwertig zu behandeln, wenn sie sich im Fall, Akzent oder beides unterscheiden. Diese Sortierungen sind gründlicher, weisen jedoch höhere Rechenkosten auf.

Wann jede Sortierung verwendet werden soll

• Wird UTF8_LCASE für schnelle Vergleiche zwischen Groß- und Kleinschreibung verwendet, wenn Akzentzeichen voneinander getrennt bleiben sollen. Diese Sortierung eignet sich gut für Data Warehouse-Migrationen von Systemen, die den Abgleich einfacher Zeichenfolgen ohne Groß-/Kleinschreibung verwenden.

• Wird UNICODE_CI für Vergleiche zwischen Groß- und Kleinschreibung verwendet, die Unicode-Regeln befolgen, insbesondere, wenn Daten Zeichen aus mehreren Sprachen enthalten, bei denen einfache Unterschreibung unzureichend ist.

• Wird verwendet UNICODE_CI_AI , wenn Sowohl Groß- als auch Akzentunterschiede ignoriert werden sollen. Wenn z. B. eine Suche übereinstimmen "resume""résumé"soll. Dies ist bei benutzerorientierten Such- und mehrsprachigen Anwendungen üblich.

Beispiele

-- UTF8_LCASE lowercases then compares bytes.
-- Accented and unaccented characters are not equivalent.
> SELECT 'Cafe' = 'café' COLLATE UTF8_LCASE;
  false

> SELECT 'Café' = 'café' COLLATE UTF8_LCASE;
  true

-- UNICODE_CI is case-insensitive but accent-sensitive.
-- 'Café' equals 'café' (case differs) but not 'Cafe' (accent differs).
> SELECT 'Café' = 'cafe' COLLATE UNICODE_CI;
  false

> SELECT 'Café' = 'café' COLLATE UNICODE_CI;
  true

-- UNICODE_CI_AI is case-insensitive and accent-insensitive.
-- 'Café' matches 'cafe' because both case and accent differences are ignored.
> SELECT 'Cafe' = 'café' COLLATE UNICODE_CI_AI;
  true

> SELECT 'resume' = 'résumé' COLLATE UNICODE_CI_AI;
  true

-- UTF8_LCASE sorts by lowercase byte order.
-- UNICODE collations sort by linguistic similarity.
> SELECT col FROM VALUES('Banana'), ('apple'), ('Ångström'), ('äpfel') AS t(col)
    ORDER BY col COLLATE UTF8_LCASE;
  apple
  Banana
  Ångström
  äpfel

> SELECT col FROM VALUES('Banana'), ('apple'), ('Ångström'), ('äpfel') AS t(col)
    ORDER BY col COLLATE UNICODE_CI;
  apple
  Ångström
  äpfel
  Banana

Standardsortierung

Die Standardsortierung gilt für STRING Literale, Parametermarkierungen, Funktionen ohne STRING Parameter, die Zeichenfolgen erzeugen, sowie Spalten-, Feld- oder Variablentypdefinitionen ohne Klausel COLLATE .

Die Standardsortierung wird wie folgt abgeleitet:

• Für DDL-Anweisungen wie ALTER TABLE, , CREATE VIEW, CREATE TABLEund CREATE FUNCTION:

  • Die Standardsortierung ist die Standardsortierung des Objekts, das erstellt oder geändert wird.
  • Wenn keine DEFAULT COLLATION Klausel angegeben ist, lautet UTF8_BINARYdie Standardsortierung .

• Für DML-Anweisungen (UPDATE, DELETE FROM, , INSERTMERGE INTO) und Query ist UTF8_BINARYdie Standardsortierung .

Rangfolge von Sortierungen

Azure Databricks wendet Rangfolgeregeln an, um zu bestimmen, welche Sortierung für eine bestimmte Zeichenfolge verwendet werden soll. Es werden vier Rangfolgeebenen definiert:

1. Explizit

   Die Sortierung wird explizit mithilfe des collate Ausdrucks zugewiesen.

   -- Force binary collation to check whether a VIN matches a Ferrari.
   vin COLLATE UTF8_BINARY LIKE 'ZFF%'
   
   -- Force German collation to order German first names.
   ORDER BY vorname COLLATE DE
   

2. Implizit

   Die Sortierung wird implizit einem Spaltennamen, Feldnamen, Spaltenalias, Variablennamen oder Parameternamenverweis zugewiesen, einschließlich des Ergebnisses einer Unterabfrage, bei der die Sortierung nicht "None" ist.

   -- Use the collation of the column as defined.
   employee.name LIKE 'Mc%'
   
   -- Use the collation of the variable as defined.
   translate(session.tempvar, 'Z', ',')
   

3. Standard

   Ein STRING Literal, benannte oder unbenannte Parametermarkierungen oder eine STRING von einer Funktion aus einem anderen Typ erzeugte.

   -- A literal string has the default collation.
   'Hello'
   
   -- :parm1 is a parameter marker using the session default collation.
   EXECUTE IMMEDIATE 'SELECT :parm1' USING 'Hello' AS parm1;
   
   -- ? is a parameter marker using the session default collation.
   EXECUTE IMMEDIATE 'SELECT ?' USING 'Hello';
   
   -- The result of casting a non-STRING to STRING uses the default collation.
   CAST(5 AS STRING)
   
   -- The date is converted to a string using the default collation.
   to_char(DATE'2016-04-08', 'y')
   
   -- session_user() returns a STRING with the default collation.
   session_user()
   

   Die zugewiesene Sortierung ist die Standardsortierung.

4. Keine

   Ein STRING Ergebnis einer Funktion, eines Operators oder eines Satzvorgangs, z UNION . B. das mehrere STRING Argumente mit unterschiedlichen impliziten Sortierungen akzeptiert.

   -- Concatenating two strings with different explicit collations results in no collation.
   SELECT fr || de AS freutsch FROM VALUES('Voulez vous ' COLLATE FR, 'Kartoffelsupp...' COLLATE DE) AS T(fr, de)
   
   -- A union of two strings with different explicit collations results in no collation.
   SELECT 'Voulez vous ' COLLATE FR UNION ALL SELECT 'Kartoffelsupp...' COLLATE DE
   

Ableiten der Sortierung

Beim Ableiten der Sortierung für ein STRING Ergebnis gelten Rangfolgeregeln wie folgt:

1. Wenn der Ausdruck mit einer der obigen Definitionen übereinstimmt, sind die Sortierung und Rangfolge wie definiert.

2. Wenn es sich bei dem Ausdruck um eine Funktion oder einen Operator handelt, der einen einzelnen STRING Parameter zurückgibt STRING, sind die Sortierung und Rangfolge der STRING Parameter.

3. Wenn der Ausdruck eine Funktion oder ein Operator mit zwei oder mehr STRING Parametern ist:

   1. Wenn alle Parameter dieselbe Sortierung und Rangfolge aufweisen, verwendet das Ergebnis diese Sortierung und Rangfolge.

   2. Wenn Parameter unterschiedliche Sortierungen oder Rangfolgen aufweisen, lassen C1 und C2 unterscheiden Sie sich voneinander und D stellen Sie die Standardsortierung dar. Das Ergebnis wird durch die folgende Tabelle bestimmt:

Beispiele

> SELECT 'hello' = 'hello   ' COLLATE UNICODE_RTRIM;
  true

> CREATE TABLE words(fr STRING COLLATE FR, de STRING COLLATE DE, en STRING COLLATE EN);
> INSERT INTO words VALUES ('Salut', 'Hallo', 'Hello');

-- A literal string has the default collation.
> SELECT collation('Ciao');
  UTF8_BINARY

-- A function producing a STRING has the default collation.
> SELECT collation(user());
  UTF8_BINARY

-- A function that modifies a STRING passes the collation through.
> SELECT collation(upper('Ciao'));
  UTF8_BINARY

-- Implicit collation (French) wins over default collation.
> SELECT collation(fr || 'Ciao') FROM words;
  FR

-- Explicit collation (French) wins over implicit collation (German).
> SELECT collation('Salut' COLLATE FR || de) FROM words;
  FR

-- Implicit German collides with implicit French; the result has no collation.
> SELECT collation(de || fr) FROM words;
  null

-- Explicit collation (French) wins over default collation (Italian).
> SELECT collation('Salut' COLLATE FR || 'Ciao');
  FR

-- Explicit collation (French) collides with explicit collation (German).
> SELECT collation('Salut' COLLATE FR || 'Hallo' COLLATE DE);
  COLLATION_MISMATCH.EXPLICIT

-- Explicit collation wins over no collation.
> SELECT collation('Ciao' COLLATE IT || (fr || de)) FROM words;
  IT

-- Implicit collation (English) does not win over None.
> SELECT collation(en || (fr || de)) FROM words;
  null

-- Explicit collation (English) wins over implicit collation anywhere in the expression.
> SELECT collation((fr || ltrim('H' COLLATE EN, fr)) || fr) FROM words;
  EN

Einschränkungen

Viele Musterabgleichs- und Regex-Funktionen unterstützen nur UTF8_BINARY und UTF8_LCASE Sortierungen. Azure Databricks löst einen Fehler aus, wenn diese Funktionen mit anderen Sortierungen wie UNICODE oder gebietsschemaspezifischen Sortierungen wie DE oder FR_CI_AI verwendet werden.

Betroffene Funktionen umfassen LIKE, , ILIKE, RLIKEund die regexp_* Funktionsfamilie. Verwenden Sie containsfür den Zeichenfolgenabgleich für Spalten mit anderen Sortierungen Funktionen, startswith Funktionen oder endswith Funktionen.

Verwandte Themen

• collate-Ausdruck
• collation Funktion
• collations Tabellenfunktion