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

Важно

Эта функция доступна в общедоступной предварительной версии.

применимо: 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 Runtime 16.2 и позже

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

  применимо: 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.

Параметры сортировки по умолчанию определяются в 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

Родственный

• выражения сортировки
• функция упорядочивания
• функция сопоставления таблиц