Sortering

Gäller för: Databricks SQL Databricks Runtime version 16.1 eller senare

En sortering är en uppsättning regler som avgör hur strängjämförelser utförs. Sorteringar stöder skiftlägesokänsliga, accentokänsliga och avslutande utrymmesokänsliga jämförelser samt språkmedveten strängordning.

Strängar i Azure Databricks representeras som UTF-8 kodade Unicode-tecken. Som standard jämför Azure Databricks strängar med deras binära UTF-8-representation, så kallad UTF8_BINARY sortering. UTF8_BINARY jämförelser är snabba och lämpliga i många fall, men kanske inte är lämpliga för program som kräver språkmedveten sortering eller jämförelser.

Utöver binära jämförelser är ett vanligt användningsfall skiftlägesokänslig matchning. Sorteringen UTF8_LCASE är utformad för detta ändamål. Den konverterar strängar till gemener innan de jämförs med .UTF8_BINARY

För språkmedvetna jämförelser använder Azure Databricks följande tekniker:

• International Components for Unicode-bibliotek (ICU) för beräkningssortering.
• CLDR-tabeller (Common Locale Data Repository) för språkmedveten sortering.
• LDML (Unicode Locale Data Markup Language) för intern kodning av collationer.

Dessa tekniker kapslas in i en uppsättning namngivna sorteringar som är tillgängliga för användning i SQL-instruktioner.

Collationnamn

Azure Databricks tillhandahåller namngivna systemsortering för att förenkla identifieringen. LDML-specifikationer kan vara komplexa att läsa och använda direkt.

Syntax

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

locale
  language_code [ _ script_code ] [ _ country_code ]

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

• UTF8_BINARY

  En binär sortering som jämför strängar byte med byte baserat på deras UTF-8-representation. UTF8_BINARY är standardsorteringen och den enklaste sorteringen i Azure Databricks.

  I den här sorteringen: 'A' (x'65') <'B' (x'66') < ... < 'Z' (x'90'). 'Z' Men (x'90')'a'<(x'97') och 'A' (x'65') <>'a' (x'97'). Tecken som 'Ä' (x'C384') är större än både 'Z' och 'z'.

• UTF8_LCASE

  En lätt, skiftlägeskänslig sortering som konverterar strängar till gemener innan de jämförs med UTF8_BINARY.

  UTF8_LCASE är sorteringen som används för Identifiers i Azure Databricks.

  Till exempel:

  ORDER BY col COLLATE UTF8_LCASE
  

  motsvarar:

  ORDER BY LOWER(col) COLLATE UTF8_BINARY
  

• UNICODE

  ICU-rotspråket, som i CLDR root kallas språkvariant (LDML-specifikation: und-u). Den här sorteringen tillämpar en språkagnostisk ordning som grupperar liknande tecken tillsammans. Till exempel: 'a''b'<<'A'<'Ä'. Den här sorteringen är skiftlägeskänslig och dekorkänslig som standard.

• locale

  En språkmedveten sortering baserat på CLDR-tabeller. Språkvarianten anges som en språkkod, en valfri skriptkod och en valfri landskod. Nationella värden är skiftlägeskänsliga.

  • language_code: En iso 639-1-språkkod med två bokstäver.
  • script_code: En ISO 15924-skriptkod med fyra bokstäver.
  • country_code: En iso 3166-1 alfa-3-landskod med tre bokstäver.

• modifier

  Styr skiftlägeskänslighet, accentkänslighet och avslutande utrymmesbeteende. Modifierare är skiftlägeskänsliga och kan anges i valfri ordning.

  • CS:Skiftlägeskänsliga. Standardbeteende.
  • CI:Skiftlägesokänslig.
  • AS: Dekorkänsligt. Standardbeteende.
  • AI: Dekorfärgsokänslig.

  gäller för: Databricks SQL- Databricks Runtime 16.2 och senare

  • RTRIM: Avslutande utrymmesokänsligt. Trimmar avslutande blanksteg (u0020) före jämförelse.

  gäller för: Databricks SQL- Databricks Runtime 16.2 och senare

  Du kan ange RTRIM, högst en av CS eller CI, och högst en av AS eller AI.

När Azure Databricks bearbetar ett sorteringsnamn normaliserar det namnet genom att ta bort standardvärden. Normaliserar till exempel SR_CYR_SRN_CS_AS till SR.

För att se en lista över sorteringsordningar som stöds, se sorteringsordningar som stöds.

Exempel

-- 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`

Jämföra UTF8_LCASE och UNICODE-sortering

Både UTF8_LCASE och UNICODE-baserade sorteringar som UNICODE_CI och UNICODE_CI_AI stöder skiftlägesokänsliga jämförelser, men de skiljer sig åt i hur de hanterar accenttecken och språkspecifika regler.

Beteendeskillnader

UTF8_LCASE konverterar strängar till gemener och jämför byte med byte med hjälp av UTF8_BINARY. Det är enkelt och snabbt, men behandlar accenttecken som distinkta från deras obevakade former.

UNICODE-baserade sortering använder ICU-biblioteket och CLDR-språkdata. Modifierarna CI och AI utökar detta för att behandla tecken som likvärdiga även om de skiljer sig åt om de är accenter eller båda. Dessa sorteringar är mer noggranna men har högre beräkningskostnader.

När du ska använda varje sortering

• Använd UTF8_LCASE för snabba skiftlägesokänsliga jämförelser när accenttecken ska förbli distinkta. Den här sorteringen passar bra för informationslagermigreringar från system som använder enkel skiftlägeskänslig strängmatchning.

• Används UNICODE_CI för skiftlägesokänsliga jämförelser som följer Unicode-regler, särskilt när data innehåller tecken från flera språk där enkel gemener är otillräckligt.

• Använd UNICODE_CI_AI när både skiftläges- och accentskillnader ska ignoreras. Om en sökning "resume" till exempel ska matcha "résumé". Detta är vanligt i användarriktade sökprogram och flerspråkiga program.

Exempel

-- 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

Standardsammanställning

Standardsortering gäller för STRING literaler, parametermarkörer, funktioner utan STRING parametrar som producerar strängar och definitioner av kolumn, fält eller variabeltyp utan en COLLATE sats.

Standardsortering härleds enligt följande:

• För DDL-instruktioner som ALTER TABLE, CREATE VIEW, CREATE TABLEoch CREATE FUNCTION:

  • Standardsorteringsordning är standardsorteringsordning av objektet som skapas eller ändras.
  • Om ingen DEFAULT COLLATION-sats anges är standardsortering UTF8_BINARY.

• För DML-instruktioner (UPDATE, DELETE FROM, INSERT, MERGE INTO) och Query är UTF8_BINARYstandardsortering .

Sorteringsprioritet

Azure Databricks tillämpar sorteringsprioritetsregler för att avgöra vilken sortering som ska användas för en viss sträng. Fyra prioritetsnivåer definieras:

1. uttrycklig

   Sorteringen tilldelas uttryckligen med uttrycketcollate .

   -- 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. implicit

   Sorteringen tilldelas implicit av ett kolumnnamn, fältnamn, kolumnalias, variabelnamn eller parameternamnreferens , inklusive resultatet av en underfråga där sorteringen inte är Ingen.

   -- 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. Förvald

   En STRING literal, namngiven eller namnlös parametermarkör eller en STRING som skapas av en funktion från en annan typ.

   -- 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()
   

   Den tilldelade sorteringen är standardsortering.

4. Ingen

   Ett STRING resultat av en funktions-, operator- eller set-åtgärd som UNION den tar mer än ett STRING argument med olika implicita sorteringar.

   -- 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
   

Sorteringshärledning

När sortering av ett STRING resultat härleds gäller prioritetsregler på följande sätt:

1. Om uttrycket matchar en av definitionerna ovan är sorteringen och prioriteten enligt definitionen.

2. Om uttrycket är en funktion eller operator med en enda STRING parameter som returnerar en STRINGär sorteringen och prioriteten parameterns STRING .

3. Om uttrycket är en funktion eller operator med två eller flera STRING parametrar:

   1. Om alla parametrar har samma sortering och prioritet använder resultatet den sorteringen och prioriteten.

   2. Om parametrar har olika sortering eller prioritet ska vi låta C1 och C2 vara distinkta sorteringar och D vara standardsortering. Resultatet bestäms av följande tabell:

Exempel

> 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

Begränsningar

Många funktioner för mönstermatchning och regex stöder endast UTF8_BINARY sortering och UTF8_LCASE sortering. Azure Databricks genererar ett fel när dessa funktioner används med andra sorteringar, till exempel UNICODE eller språkspecifika sorter som DE eller FR_CI_AI.

Berörda funktioner inkluderar LIKE, ILIKE, RLIKEoch funktionsfamiljen regexp_* . För strängmatchning på kolumner med andra sorteringar använder du contains funktion, startswith funktion eller endswith funktion.

Besläktad

• collate uttryck
• collation funktion
• collations tabellfunktion