Sortierung

Wichtig

Dieses Feature befindet sich in der Public Preview.

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

Eine Sortierung ist eine set von Regeln, die bestimmt, wie Zeichenfolgenvergleiche ausgeführt werden. Sortierungen werden verwendet, um Zeichenfolgen ohne Beachtung von Groß-/Kleinschreibung oder Akzenten zu vergleichen oder um sie in einer bestimmten sprachspezifischen Reihenfolge zu sortieren.

Zeichenfolgen in Azure Databricks werden als Unicode-Zeichen mit UTF-8--Codierung dargestellt. Standardmäßig vergleicht Azure Databricks Zeichenfolgen anhand ihrer binären UTF8-Darstellung. Dies wird als UTF8_BINARY-Sortierung bezeichnet. UTF8_BINARY-Vergleiche sind schnell und in vielen Fällen angemessen, aber möglicherweise nicht für alle Anwendungen geeignet, insbesondere dann nicht, wenn sprachspezifische Sortierungen oder Vergleiche erforderlich sind.

Neben sprachspezifischen Vergleichen ist ein weiterer gängiger Anwendungsfall die Anforderung von Vergleichen ohne Beachtung der Groß-/Kleinschreibung. Azure Databricks bietet speziell für diesen Zweck die UTF8_LCASE-Sortierung. Es konvertiert Zeichenfolgen in Kleinbuchstaben, bevor sie mithilfe der schnellen UTF8_BINARY-Sortierung verglichen werden.

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

• International Components for Unicode (ICU)-Bibliothek zur Berechnung der Sortierung
• Common Locale Data Repository (CLDR)tables für Sortierungen für spezifische Gebietsschemata.
• Unicode Locale Data Markup Language (LDML), um Kollationen intern zu codieren.

Diese Technologien sind in einer set benannter Sortierungen gekapselt, die in SQL-Anweisungen verwendet werden können.

Sortierungsnamen

Da die Identifizierung von Sortierungen anhand ihrer LDML-Spezifikation komplex und schwierig zu lesen sein kann, bietet Azure Databricks eine set benannter Systemsortierungen, die einfacher zu verwenden sind.

Syntax

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

locale
  language_code [ _ script_code ] [ _ country_code ]

modifier
  { CS | CI | AS | AI }

• UTF8_BINARY

  Eine metalokale binäre Sortierung, die Zeichenfolgen Byte für Byte basierend auf der UTF-8-Bytedarstellung vergleicht. UTF8_BINARY ist die Standardsortierung und die einfachste Sortierung für den Zeichenfolgenvergleich in Azure Databricks.

  In dieser Sortierung „A“ (x'65') < „B“ (x'66') < ... < „Z“ (x'90').

  Jedoch „Z“ (x'90') < „a“ (x'97') und „A“ (x'65') <> „a“ (x'97').

  Darüber hinaus sind Zeichen wie "Ä" (x'C384') größer als "Z" und "z" in dieser Sortierung.

• UTF8_LCASE

  Eine einfache, Sortierung (mit Meta-Gebietsschema) ohne Beachtung der Groß- und Kleinschreibung, die Zeichenketten anhand ihrer UTF-8-Byte-Darstellung vergleicht, nachdem sie in Kleinbuchstaben umgewandelt wurden.

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

  Beispiel:

  ORDER BY col COLLATE UTF8_LCASE
  

  entspricht

  ORDER BY LOWER(col) COLLATE UTF8_BINARY
  

• UNICODE

  Das ICU-Stammgebietsschema.

  Diese Sortierung, die in CLDR als „root“-Gebietsschema (LDML-Spezifikation: „und-u“) bezeichnet wird, verwendet eine sprachunabhängige Sortierreihenfolge, die insgesamt intuitiv sein soll. In dieser Sortierung werden ähnliche Zeichen gruppiert. Beispiel: 'a' < 'A' < 'Ä' < 'b'. „A“ wird nicht als Äquivalent zu „a“ betrachtet. Daher wird bei der Sortierung die Groß-/Kleinschreibung beachtet. "a" gilt nicht als gleichwertig mit 'ä'. Daher ist die Sortierung akzentsensitiv.

• locale

  Eine gebietsschemaspezifische Sortierung basierend auf den CLDR-tables.

  Das Gebietsschema wird in Form eines Sprachcodes, eines optionalen Skriptcodes und eines optionalen Ländercodes angegeben. Für locale wird die Groß-/Kleinschreibung nicht beachtet.

  • language_code: Ein aus zwei Buchstaben bestehender ISO 639-1-Sprachcode.
  • script_code: Ein aus vier Buchstaben bestehender ISO 15924-Skriptcode.
  • country_code: Ein aus drei Buchstaben bestehender ISO 3166-1 alpha-3-Ländercode.

• Modifizierer

  Gibt das Sortierungsverhalten in Bezug auf die Groß-/Kleinschreibung und Unterscheidung nach Akzent an.

  • CS: Groß-/Kleinschreibung wird beachten. Das Standardverhalten.
  • CI: Groß-/Kleinschreibung wird nicht beachtet.
  • AS: Unterscheidung nach Akzent. Das Standardverhalten.
  • AI: Keine Unterscheidung nach Akzent.

  Sie können entweder CS oder CIangeben, und entweder AS oder AI höchstens einmal und in beliebiger Reihenfolge. Bei den Modifizierern selbst wird die Groß-/Kleinschreibung nicht beachtet.

Bei der Verarbeitung von Sortierungen normalisiert Azure Databricks Sortierungsnamen, indem es Standardwerte entfernt. Beispielsweise wird SR_CYR_SRN_CS_AS auf SRnormalisiert.

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

Beispiele

-- You can fully qualify collations, and case doesn't matter.
system.builtin.unicode

-- Since all collations are system defined you don't need to qualify them
unicode

-- Using 2-letter language code only for german collation
DE

-- Using 2-letter language code and 3-letter country code for french-canadian collation
-- It is common to use lower case 2-letter language codes and upper case 3-letter country codes
-- But collation names are case insensitive
fr_CAN

-- Using 2-letter language code and 4-letter script code and 3-letter country code for traditional chinese in Macao
zh_Hant_MAC

-- Using a 2 letter german language code and 2 modifiers for case insensitive and accent insensitive
-- So 'Ä', 'A', and 'a' are all considered equal
de_CI_AI

-- Using back ticks is allowed, but unnecessary for builtin collations
`UTF8_BINARY`

Standardsortierung

Die Standardsortierung wird angewendet, wenn STRING-Literale, Parametermarkierungen, Funktionen ohne Zeichenfolgen, die STRING-parameters produzieren, sowie bei der Definition von column-, Feld- oder Variablentypen ohne eine COLLATE-Klausel verwendet werden.

Die Standardsortierung wird aus UTF8_BINARY abgeleitet.

Rangfolge von Sortierungen

Um zu entscheiden, welche Sortierung für eine bestimmte Zeichenfolge verwendet werden soll, definiert Azure Databricks Regeln für die Rangfolge von Sortierungen.

Die Regeln weisen 4 Rangstufen für Sortierungen zu:

1. Explizit

   Die Sortierung wurde mit dem Ausdruck COLLATE explizit einer Zeichenfolge zugewiesen.

   Beispiele

   -- Force fast 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 durch die column, ein Feld, einencolumn-Alias, eine Variable oder -Routineparameterreferenz implizit zugewiesen. Dies schließt das Ergebnis einer Unterabfrage ein, solange die Sortierung nicht None ist.

   Beispiele

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

3. Standard

   Ein STRING-Literal, eine benannte oder unbenannte -Parametermarkierung oder eine STRING, die durch eine Funktion aus einem anderen Typ erzeugt wird.

   Beispiele

   -- A literal string has the default collation
   'Hello'
   
   -- :parm1 is a parameter marker using session default collation
   EXECUTE IMMEDIATE 'SELECT :parm1' USING 'Hello' AS parm1;
   
   -- ? is a parameter marker using session default collation
   EXECUTE IMMEDIATE 'SELECT ?' USING 'Hello';
   
   -- The result of a cast of a non-STRING to a STRING is a STRING with 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')
   
   -- The collation of the session_user STRING is the default collation
   session_user()
   

   Die zugewiesene Sortierung ist die Standardsortierung.

4. None

   Ein STRING-Ergebnis einer Funktion, eines Operators oder einer set-Operation (z. B. UNION), die mehr als ein STRING-Argument akzeptiert, die unterschiedliche implizite Sortierungen aufweisen.

   Beispiele

   -- 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 excplicit 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 werden die Sortierungsrangfolgeregeln wie folgt angewendet:

Wenn der Ausdruck:

1. entspricht den obigen Definitionen.

   Die Sortierung und Rangfolge sind wie festgelegt.

2. ist eine Funktion oder ein Operator mit einem einzelnen STRING-Parameter, der eine STRING zurückgibt

   Die Sortierung und Rangfolge ist die der STRING-Parameters.

3. ist eine Funktion oder ein Operator, der über zwei oder mehr STRING-parameters verfügt

   1. mit denselben Sortierungen und Rangfolgen

      Die Sortierung und Rangfolge entspricht der der STRING-parameters.

   2. mit unterschiedlichen Sortierungen oder Rangfolgen

      Lassen Sie C1 und C2 unterschiedliche Sortierungen sein und D die Standardsortierung sein. Die Rangfolge und die Sortierung werden durch die folgende table bestimmt:

      Sortierung und Rangfolge	C1 Explicit	C1 Implicit	D Default	Nichts
      C2 Explicit	Fehler	C2 Explicit	C2 Explicit	C2 Explicit
      C2 Implicit	Explicit C1	Nichts	C2 Implicit	Nichts
      D Default	C1 Explicit	C1 Implicit	D Default	Nichts
      None	C1 Explicit	Nichts	Nichts	Nichts

Beispiele

> 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

-- Function modifying 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 collation German collides with implicit collation French
-- The result is 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

-- The explicit collation wins over no collation
> SELECT collation('Ciao' COLLATE IT || (fr || de)) FROM words;
  UTF8_BINARY

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

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

Verwandte Themen

• Sortierungsausdruck
• Sortierungsfunktion
• Sortierungs-table-Funktion