Сопоставление

Применяется к: Databricks SQL Databricks Runtime 16.1 и более поздним версиям

Коллация — это набор правил, определяющих, как выполняются сравнения строк. Параметры сортировки используются для сравнения строк с учетом нечувствительности к регистру, акцентам или завершающим пробелам, а также для сортировки строк в порядке, учитывающем особенности конкретного языка.

Строки в Azure Databricks представлены как символы UTF-8, закодированные символами Юникода. По умолчанию Azure Databricks сравнивает строки по двоичному представлению UTF8. Это называется сортировкой UTF8_BINARY. Сравнения UTF8_BINARY выполняются быстро и подходят в большинстве случаев, но могут быть неподходящими для всех сценариев, особенно для тех, которые требуют сортировки или сравнения с учетом языковых особенностей.

Помимо учитывающих язык сравнений, распространенным сценарием использования является обеспечение нечувствительности к регистру. Azure Databricks имеет порядок сортировки UTF8_LCASE специально для этой цели. Перед сравнением он преобразует строки в нижний регистр, используя быструю UTF8_BINARY классификацию.

Для сравнения с учетом языка Azure Databricks использует следующие технологии:

• международные компоненты для библиотеки Юникода (ICU) для вычислений сортировки
• Общий репозиторий данных локализации (CLDR) таблицы для определенных параметров сортировки, учитывающих языковую локализацию.
• язык разметки данных Юникода (LDML) для внутреннего кодирования колляций.

Эти технологии инкапсулируются в набор именованных параметров сортировки, которые можно использовать в инструкциях SQL.

Имена параметров сортировки

Поскольку определение сортировок по спецификации LDML может быть сложным и трудным для чтения, в Azure Databricks есть набор более простых в использовании именованных системных сортировок.

Синтаксис

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

locale
  language_code [ _ script_code ] [ _ country_code ]

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

• UTF8_BINARY

  Двоичная сортировка для мета-локали, которая побайтово сравнивает строки на основе их представления байтами в UTF-8. UTF8_BINARY используется по умолчанию и наиболее упрощенная сортировка для сравнения строк в Azure Databricks.

  В этой последовательности 'A' (x'65') < 'B' (x'66') < … < 'Z' (x'90').

  Однако "Z" (x'90') < "a" (x'97'), и "A" (x'65') <> "a" (x'97').

  Кроме того, такие символы, как "Ä" (x'C384), больше, чем "Z" и "z" в этом параметре сортировки.

• UTF8_LCASE

  Легковесная мета-языковая сортировка, нечувствительная к регистру, которая сравнивает строки, используя их байтовое представление UTF-8 после преобразования строк в нижний регистр.

  UTF8_LCASE — это сортировка, которая используется для идентификаторов в Azure Databricks.

  Например:

  ORDER BY col COLLATE UTF8_LCASE
  

  эквивалентно

  ORDER BY LOWER(col) COLLATE UTF8_BINARY
  

• ЮНИКОД

  Корневая локаль ICU.

  Этот способ сортировки, известный в CLDR как "корневая" локаль (спецификация LDML: "und-u"), накладывает языково-нейтральный порядок, который пытается быть интуитивно понятным в целом. В этой сортировке символы группируются похожим образом на символы. Например, "a" "A" < "Ä" << "b". «A» не считается равнозначным «a». Поэтому параметры сортировки чувствительны к регистру. "a" не считается эквивалентным "ä". Поэтому параметры сортировки чувствительны к акценту.

• локаль

  Сортировка с учетом региональных настроек на базе таблиц CLDR.

  Локаль указывается как языковой код, необязательный код сценария и необязательный код страны. locale не чувствителен к регистру.

  • language_code: двухбуквенный код языка ISO 639-1.
  • script_code: четырех буквенный код скрипта ISO 15924.
  • country_code: трехбуквенный код страны ISO 3166-1 alpha-3.

• модификатор

  Указывает поведение сортировки относительно чувствительности регистра и учёта акцентов.

  • CS: с учетом регистра. Поведение по умолчанию.
  • CI: без учета регистра.
  • AS: Чувствительный к акценту. Поведение по умолчанию.
  • ИИ: не учитывает акцент.

  Область применения: Databricks SQL Databricks Runtime 16.2 и более поздних версий

  • RTRIM: нечувствителен к завершающим пробелам. Обрезает конечные пробелы ('u0020') перед сравнением.

  применяется: Databricks SQL Databricks Runtime 16.2 и выше

  Вы можете указать RTRIM, либо CS и/или CI, либо AS или AI не более одного раза и в любом порядке. Модификаторы не учитывают регистр.

При обработке сортировки Azure Databricks нормализует имена параметров сортировки, удаляя значения по умолчанию. Например, SR_CYR_SRN_CS_AS нормализуется для SR.

Список поддерживаемых параметров сортировки см. в разделе Поддерживаемые параметры сортировки.

Примеры

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

Колляция по умолчанию

Параметры сортировки по умолчанию применяются при использовании STRING литералов, маркеров параметров, функций без STRING параметров, генерирующих строки, а также при определении типов столбцов, полей или переменных без условия COLLATE.

Сортировка по умолчанию определяется одним из следующих способов:

• Для инструкций DDL, таких как ALTER TABLE, CREATE VIEWCREATE TABLEи CREATE FUNCTION:

  • Параметры сортировки по умолчанию — это параметры сортировки по умолчанию создаваемого или измененного объекта.
  • Если условие DEFAULT COLLATION не указано, используется сортировка по умолчанию UTF8_BINARY.

• Для DML (UPDATE, DELETE, INSERT, MERGE) и инструкций запросов коллация по умолчанию UTF8_BINARY.

Приоритет сортировки

Чтобы решить, какие параметры сортировки следует использовать для заданной строки Azure Databricks, определяют правила приоритета сортировки.

Правила назначают 4 уровня приоритета параметрам сортировки:

1. явные

   Явная привязка порядка сортировки к строке была произведена с помощью выражения COLLATE.

   примеры

   -- 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. неявный

   Параметры упорядочивания неявно назначаются столбцом , полем , псевдонимом столбца , переменной или ссылкой на параметр процедуры . Это включает результат вложенного запроса, если сортировка не None.

   примеры

   -- 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. по умолчанию

   Литерал STRING, именованный или неименованный маркер параметра ,, или STRING, произведённый функцией из другого типа.

   примеры

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

   Назначенные правила сортировки — это по умолчанию.

4. Нет

   Результат STRING операции функции, оператора или задания (например, UNION), которая принимает несколько аргументов STRING, которые имеют разные неявные параметры сортировки.

   примеры

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

Производная колляции

При определении коллации для результата STRING правила приоритета применяются следующим образом:

Если выражение:

1. соответствует определениям выше

   Параметры сортировки и приоритета определены.

2. — это функция или оператор с одним параметром STRING, возвращающий STRING

   Сортировка и приоритет определяются параметром STRING.

3. — это функция или оператор с двумя или более параметрами STRING

   1. с одинаковыми параметрами сортировки и приоритетом

      Сортировка и очередность соответствуют параметрам STRING.

   2. с различными порядками сортировки или уровнями важности

      Пусть C1 и C2 будут различными параметрами сортировки, а D — параметром сортировки по умолчанию. Приоритет и параметры сортировки определяются следующей таблицей:

      Упорядочение и приоритет	Явный C1	C1 Неявный	D По умолчанию	Нет
      Явный C2	Ошибка	Явный C2	Явный C2	Явный C2
      C2 Неявный	Явный C1	Нет	C2 Неявный	Нет
      D по умолчанию	Явный C1	C1 Неявный	D По умолчанию	Нет
      Нет	Явный C1	Нет	Нет	Нет

Примеры

> 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

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

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

Родственный

• collate выражение
• функцияcollation
• collations табличная функция