Ordenação

Aplica-se ao: Databricks SQL Databricks Runtime 16.1 e versões posteriores

Uma ordenação é um conjunto de regras que determina como comparações de cadeias de caracteres são executadas. As ordenações dão suporte a comparações que não diferenciam maiúsculas de minúsculas, não diferenciam ênfase e diferenciam espaço à direita, bem como ordenação de cadeia de caracteres com reconhecimento de linguagem.

As cadeias de caracteres em Azure Databricks são representadas como caracteres UTF-8 codificados Unicode. Por padrão, Azure Databricks compara cadeias de caracteres por sua representação utf-8 binária, conhecida como ordenação UTF8_BINARY. UTF8_BINARY as comparações são rápidas e apropriadas em muitos casos, mas podem não ser adequadas para aplicativos que exigem classificação ou comparações com reconhecimento vocal.

Além das comparações binárias, um caso de uso comum não diferencia maiúsculas de minúsculas. A UTF8_LCASE ordenação foi projetada para essa finalidade. Ele converte cadeias de caracteres em letras minúsculas antes de compará-las usando UTF8_BINARY.

Para comparações com reconhecimento vocal, Azure Databricks usa as seguintes tecnologias:

• Biblioteca de Componentes Internacionais para Unicode (UTI) para computar ordenação.
• Tabelas CLDR (Common Locale Data Repository) para ordenação com reconhecimento de localidade.
• LDML (Linguagem de Marcação de Dados de Localidade) Unicode para codificar ordenações internamente.

Essas tecnologias são encapsuladas em um conjunto de ordenações nomeadas disponíveis para uso em instruções SQL.

Nomes de ordenações

Azure Databricks fornece ordenações de sistema nomeadas para simplificar a identificação. As especificações LDML podem ser complexas de ler e usar diretamente.

Sintaxe

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

locale
  language_code [ _ script_code ] [ _ country_code ]

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

• UTF8_BINARY

  Uma ordenação binária que compara cadeias de caracteres byte byte com base em sua representação UTF-8. UTF8_BINARY é a ordenação padrão e mais leve em Azure Databricks.

  Nesta ordenação: 'A' (x'65') <'B' (x'66') < ... < 'Z' (x'90'). No entanto, 'Z' (x'90')'a'<(x'97') e 'A' (x'65') <>'a' (x'97'). Caracteres como 'Ä' (x'C384') são maiores que ambos 'Z' e 'z'.

• UTF8_LCASE

  Uma ordenação leve e que não diferencia maiúsculas de minúsculas que converte cadeias de caracteres em minúsculas antes de compará-las usando UTF8_BINARY.

  UTF8_LCASE é a ordenação usada para Identifiers em Azure Databricks.

  Por exemplo:

  ORDER BY col COLLATE UTF8_LCASE
  

  é equivalente a:

  ORDER BY LOWER(col) COLLATE UTF8_BINARY
  

• UNICODE

  A localidade raiz da UTI, conhecida em CLDR como a root localidade (especificação LDML: und-u). Essa ordenação aplica uma ordem independente de linguagem que agrupa caracteres semelhantes. Por exemplo: 'a''b'<<'A'<'Ä'. Essa ordenação diferencia maiúsculas de minúsculas e diferencia ênfase por padrão.

• locale

  Uma ordenação com reconhecimento de localidade com base em tabelas CLDR. A localidade é especificada como um código de idioma, um código de script opcional e um código de país opcional. Os valores de localidade não diferenciam maiúsculas de minúsculas.

  • language_code: um código de idioma ISO 639-1 de duas letras.
  • script_code: um código de script ISO 15924 de quatro letras .
  • country_code: um código de país ISO 3166-1 alfa-3 de três letras .

• modifier

  Controla a confidencialidade de maiúsculas e minúsculas, a sensibilidade do acento e o comportamento de espaço à direita. Modificadores não diferenciam maiúsculas de minúsculas e podem ser especificados em qualquer ordem.

  • CS: diferencia maiúsculas de minúsculas. O comportamento padrão.
  • CI: não diferencia maiúsculas de minúsculas.
  • AS: sensível ao destaque. O comportamento padrão.
  • AI: sem ênfase.

  Aplica-se a: Databricks SQL Databricks Runtime 16.2 e versões superiores

  • RTRIM: sem diferenciação de espaço à direita. Corta espaços à direita (u0020) antes da comparação.

  Aplica-se a: Databricks SQL Databricks Runtime 16.2 e versões superiores

  Você pode especificarRTRIM, no máximo um ouCICS, no máximo, um ou ASAI.

Quando Azure Databricks processa um nome de ordenação, ele normaliza o nome removendo padrões. Por exemplo, SR_CYR_SRN_CS_AS normaliza para SR.

Para obter uma lista de ordenações com suporte, confira Ordenações com suporte.

Exemplos

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

Comparar UTF8_LCASE e ordenações UNICODE

As UTF8_LCASE ordenações baseadas em UNICODE, como UNICODE_CI e UNICODE_CI_AI que dão suporte a comparações que não diferenciam maiúsculas de minúsculas, mas diferem na forma como lidam com caracteres acentuados e regras específicas de localidade.

Diferenças de comportamento

UTF8_LCASE converte cadeias de caracteres em letras minúsculas e as compara byte byte usando UTF8_BINARY. É leve e rápido, mas trata personagens acentuados como distintos de suas formas não acentuadas.

As ordenações baseadas em UNICODE usam a biblioteca de UTI e os dados de localidade CLDR. Os CI modificadores e os AI modificadores estendem isso para tratar os caracteres como equivalentes, mesmo quando diferem no caso, no acento ou em ambos. Essas ordenações são mais completas, mas têm um custo computacional mais alto.

Quando usar cada ordenação

• Use UTF8_LCASE para comparações rápidas que não diferenciam maiúsculas de minúsculas quando caracteres acentuados devem permanecer distintos. Essa ordenação é adequada para migrações de data warehouse de sistemas que usam correspondência de cadeias de caracteres simples que não diferenciam maiúsculas de minúsculas.

• Use UNICODE_CI para comparações que não diferenciam maiúsculas de minúsculas que seguem regras Unicode, especialmente quando os dados incluem caracteres de vários idiomas em que a minúscula simples é insuficiente.

• Use UNICODE_CI_AI quando as diferenças de maiúsculas e minúsculas e de ênfase devem ser ignoradas. Por exemplo, se uma pesquisa for "resume" correspondente "résumé". Isso é comum em aplicativos multilíngues e de pesquisa voltados para o usuário.

Exemplos

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

Ordenação padrão

A ordenação padrão se aplica a STRING literais, marcadores de parâmetro, funções sem STRING parâmetros que produzem cadeias de caracteres e definições de tipo de coluna, campo ou variável sem uma COLLATE cláusula.

A ordenação padrão é derivada da seguinte maneira:

• Para instruções DDL, como ALTER TABLE, CREATE VIEWe CREATE TABLECREATE FUNCTION:

  • A ordenação padrão é a ordenação padrão do objeto que está sendo criado ou alterado.
  • Se nenhuma DEFAULT COLLATION cláusula for especificada, a ordenação padrão será UTF8_BINARY.

• Para instruções DML (UPDATE, DELETE FROM, , INSERTMERGE INTO) e Consulta, a ordenação padrão é UTF8_BINARY.

Precedência da ordenação

Azure Databricks aplica regras de precedência de ordenação para determinar qual ordenação usar para uma determinada cadeia de caracteres. Quatro níveis de precedência são definidos:

1. Explícito

   A ordenação é atribuída explicitamente usando collate a expressão.

   -- 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. Implícito

   A ordenação é atribuída implicitamente por um nome de coluna, nome do campo, alias de coluna, nome da variável ou referência de nome de parâmetro , incluindo o resultado de uma subconsulta em que a ordenação não é Nenhuma.

   -- 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. Padrão

   Um STRING literal, nomeado ou sem nome marcadores de parâmetro, ou um STRING produzido por uma função de outro tipo.

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

   A ordenação atribuída é a ordenação padrão.

4. Nenhuma

   Um STRING resultado de uma função, operador ou operação de conjunto, como UNION essa, usa mais de um STRING argumento com ordenações implícitas diferentes.

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

Derivação de ordenação

Ao derivar a ordenação de um STRING resultado, as regras de precedência se aplicam da seguinte maneira:

1. Se a expressão corresponder a uma das definições acima, a ordenação e a precedência serão definidas.

2. Se a expressão for uma função ou operador com um único STRING parâmetro retornando um STRING, a ordenação e a precedência serão as STRING do parâmetro.

3. Se a expressão for uma função ou operador com dois ou mais STRING parâmetros:

   1. Se todos os parâmetros tiverem a mesma ordenação e precedência, o resultado usará essa ordenação e precedência.

   2. Se os parâmetros tiverem ordenações ou precedência diferentes, vamos C1 e C2 ser ordenações distintas e D ser a ordenação padrão. O resultado é determinado pela tabela a seguir:

Exemplos

> 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

Limitações

Muitas funções regex e de correspondência de padrões dão suporte apenas UTF8_BINARY a ordenações e à UTF8_LCASE correspondência de padrões. Azure Databricks gera um erro quando essas funções são usadas com outras ordenações, como UNICODE ou ordenações específicas de localidade, como DE ou FR_CI_AI.

As funções afetadas incluem LIKE, ILIKEe RLIKEa família de regexp_* funções. Para correspondência de cadeia de caracteres em colunas com outras ordenações, use contains função, startswith função ou endswith função.

Relacionados

• collate expressão
• collation função
• função de tabela collations