Suporte para agrupamento

O agrupamento permite especificar regras alternativas para comparar cadeias de caracteres, que podem ser usadas para comparar e classificar dados de acordo com um idioma específico ou outras regras especificadas pelo usuário.

Visão geral

As seções a seguir explicam o que é agrupamento e como usá-lo ao comparar cadeias de caracteres:

Compreendendo o agrupamento

As cadeia de caracteres de texto do Snowflake são armazenadas usando o conjunto de caracteres UTF-8 e, por padrão, as cadeias são comparadas de acordo com os códigos Unicode que representam os caracteres na cadeia.

No entanto, a comparação de cadeias de caracteres com base em suas representações de caracteres UTF-8 pode não apresentar o comportamento desejado/esperado. Por exemplo:

  • Se caracteres especiais em um determinado idioma não forem classificados de acordo com os padrões de classificação desse idioma, então a classificação pode retornar resultados inesperados.

  • Você pode querer que as cadeias de caracteres sejam classificadas por outras regras, como ignorar a diferenciação entre maiúsculas ou minúsculas.

O agrupamento permite que você especifique explicitamente as regras a serem usadas para comparar cadeias de caracteres, com base em:

  • Locais diferentes (ou seja, conjuntos de caracteres diferentes para idiomas diferentes).

  • Diferenciação entre maiúsculas e minúsculas (isto é, usar comparações de cadeias de caracteres diferenciando ou não maiúsculas e minúsculas sem chamar explicitamente as funções UPPER ou LOWER para converter as cadeias de caracteres).

  • Identificação de acentos (por exemplo, se Z, Ź e Ż são considerados a mesma letra ou letras diferentes).

  • Identificação de pontuação (ou seja, se as comparações usam apenas letras ou incluem todos os caracteres). Por exemplo, se uma comparação não identificar pontuação, então A-B-C e ABC são tratados como equivalentes.

  • Opções adicionais, como preferências de classificação baseadas na primeira letra de uma cadeia de caracteres e corte de espaços em branco no começo (à esquerda) e/ou no fim (à direita).

Utilizações de agrupamento

O agrupamento pode ser usado em uma grande variedade de operações, incluindo (mas não se limitando a):

Uso

Exemplo

Link

Comparação simples

... WHERE column1 = column2 ...

WHERE

Junções

... ON table1.column1 = table2.column2 ...

JOIN

Classificação

... ORDER BY column1 ...

ORDER BY

Classificação Top-K

... ORDER BY column1 LIMIT N ...

LIMIT / FETCH

Agregação

... GROUP BY ...

GROUP BY

Cláusulas de janela

... PARTITION BY ... ORDER BY ...

Funções de janela

Funções escalares

... LEAST(column1, column2, column3) ...

Funções escalares

Funções de agregação

... MIN(column1), MAX(column1) ...

Funções de agregação

Clustering de dados

... CLUSTER BY (column1) ...

Chaves de clustering e tabelas clusterizadas

Controle de agrupamento

O controle de agrupamento é granular. Você pode especificar explicitamente o agrupamento a ser usado para:

  • Uma conta, usando o parâmetro de nível de conta DEFAULT_DDL_COLLATION.

  • Todas as colunas em todas as tabelas adicionadas a um banco de dados, usando o comando ALTER DATABASE.

  • Todas as colunas em todas as tabelas adicionadas a um esquema, usando o comando ALTER SCHEMA.

  • Todas as colunas adicionadas a uma tabela, usando o comando ALTER TABLE.

  • Colunas individuais em uma tabela, usando o comando CREATE TABLE.

  • Uma comparação específica em uma instrução SQL (por exemplo, WHERE col1 = col2). Se forem aplicados múltiplos agrupamentos a uma instrução, o Snowflake determina o agrupamento a ser usado com base na precedência. Para obter mais detalhes sobre precedência, consulte Precedência de agrupamento em operações com várias cadeias de caracteres (neste tópico).

Construções de agrupamento SQL

Você pode usar as seguintes construções SQL para agrupamento:

Cláusula COLLATE (para definições de colunas da tabela)

Adicionar a cláusula opcional COLLATE à definição de colunas de uma tabela indica que o agrupamento especificado é usado para comparações e outras operações relacionadas que são realizadas nos dados da coluna:

CREATE TABLE <table_name> ( <col_name> <col_type> COLLATE '<collation_specification>'
                            [ , <col_name> <col_type> COLLATE '<collation_specification>' ... ]
                            [ , ... ]
                          )
Copy

Se nenhuma cláusula COLLATE for especificada para uma coluna, o Snowflake usa o padrão, que compara as cadeias de caracteres com base em suas representações de caracteres UTF-8.

Além disso, o Snowflake oferece suporte à especificação de uma cadeia de caracteres vazia para a especificação de agrupamento (por exemplo, COLLATE ''), o que equivale a não especificar um agrupamento para a coluna.

Entretanto, observe que, devido à precedência, especificar COLLATE '' para uma coluna não tem o mesmo efeito que especificar explicitamente COLLATE 'utf8'. Para obter mais detalhes, consulte Precedência de agrupamento em operações com várias cadeias de caracteres (neste tópico).

Para ver se o agrupamento foi especificado para as colunas em uma tabela, use DESCRIBE TABLE (ou use a função COLLATION para exibir o agrupamento, se houver, para uma coluna específica).

Função COLLATE

Esta função usa o agrupamento especificado na expressão da cadeia de caracteres de entrada:

COLLATE( <expression> , '[<collation_specification>]' )
Copy

Esta função também pode ser chamada usando a notação de infixo:

<expression> COLLATE '[<collation_specification>]'
Copy

Esta função é particularmente útil para especificar explicitamente um determinado agrupamento para uma determinada operação (por exemplo, classificação), mas também pode ser usada para:

  • Permitir o agrupamento na cláusula SELECT de uma subconsulta, fazendo com que todas as operações na coluna especificada na consulta externa utilizem o agrupamento.

  • Criar uma tabela usando CTAS com um agrupamento especificado.

Por exemplo:

-- Evaluates using "English case-insensitive" collation:
SELECT * FROM t1 WHERE COLLATE(col1 , 'en-ci') = 'Tango';

-- Sorts the results using German (Deutsch) collation.
SELECT * FROM t1 ORDER BY COLLATE(col1 , 'de');

-- Creates a table with a column using French collation.
CREATE TABLE t2 AS SELECT COLLATE(col1, 'fr') AS col1 FROM t1;

-- Creates a table with a column using French collation.
CREATE TABLE t2 AS SELECT col1 COLLATE 'fr' AS col1 FROM t1;
Copy

Função COLLATION

Esta função retorna a especificação de agrupamento utilizada por uma expressão, incluindo uma coluna de tabela:

COLLATION( <expression> )
Copy

Se não tiver sido especificado um agrupamento para a expressão, a função retorna NULL.

Geralmente, se você usar isto em um nome de coluna, você deve usar DISTINCT para evitar obter uma linha de saída para cada linha na tabela. Por exemplo:

SELECT DISTINCT COLLATION(column1) FROM table1;
Copy

Nota

Esta função retorna apenas a especificação de agrupamento, não seu nível de precedência. Para obter mais detalhes sobre precedência, consulte Precedência de agrupamento em operações com várias cadeias de caracteres (neste tópico).

Especificações de agrupamento

Ao usar uma cláusula COLLATE (para uma coluna de tabela) ou a função COLLATE (para uma expressão), deve-se incluir uma especificação de agrupamento, que determina a lógica de comparação usada para a coluna/expressão.

Uma especificação de agrupamento consiste em uma cadeia de caracteres de um ou mais especificadores separados por um hífen (-) na forma:

'<specifier>[-<specifier> ...]'

Os seguintes especificadores são aceitos (para obter mais detalhes, consulte Especificadores com suporte neste tópico):

  • Local.

  • Diferenciação entre maiúsculas e minúsculas.

  • Identificação de acentos.

  • Identificação de pontuação.

  • Preferência pela primeira letra.

  • Conversão de maiúsculas/minúsculas.

  • Corte de espaço em branco.

Os especificadores não diferenciam maiúsculas e minúsculas e podem estar em qualquer ordem, exceto para locais, que devem ser sempre os primeiros, se utilizados.

As seções a seguir fornecem mais detalhes sobre as especificações de agrupamento:

Exemplos de especificações

Alguns exemplos de cadeia de caracteres de especificação de agrupamento incluem:

  • 'de': local Alemão (Deutsch).

  • 'de-ci-pi': local Alemão, com comparações sem distinção de maiúsculas/minúsculas e sem identificação de pontuação.

  • 'fr_CA-ai': local Francês canadense, com comparações sem identificação de acentos.

  • 'en_US-trim': local Inglês US, com espaços iniciais e espaços finais cortados antes da comparação.

Você também pode especificar uma cadeia de caracteres vazia para uma especificação de agrupamento (por exemplo, COLLATE '' ou COLLATE(col1, '')), o que indica que não se deve usar agrupamento.

Especificadores com suporte

Local:

Especifica as regras específicas de idioma e de país a serem aplicadas.

Aceita cadeias de caracteres locais válidas, consistindo em um código de idioma (obrigatório) e código de país (opcional) na forma language_country. Alguns exemplos de local incluem:

  • en - Inglês.

  • en_US - Inglês dos Estados Unidos.

  • fr - Francês.

  • fr_CA - Francês canadense.

Além disso, o pseudolocal utf8 especifica o uso da classificação Unicode, que é o padrão. Para obter mais detalhes, consulte Classificação usando UTF-8 vs. agrupamento de local (neste tópico).

O especificador de local é opcional, mas, se usado, deve ser o primeiro especificador na cadeia de caracteres.

Diferenciação entre maiúsculas e minúsculas:

Determina se maiúsculas/minúsculas devem ser diferenciadas ao comparar valores. Possíveis valores:

  • cs - Diferencia maiúsculas e minúsculas (padrão).

  • ci - Não diferencia maiúsculas e minúsculas.

Por exemplo:

Especificação de agrupamento

Valor

Resultado

'en-ci'

Abc = abc

True

'en-cs' / en

Abc = abc

False

Identificação de acentos:

Determina se caracteres acentuados devem ser considerados iguais ou diferentes de seus caracteres base. Possíveis valores:

  • as - Identifica acentos (padrão).

  • ai - Não identifica acentos.

Por exemplo:

Especificação de agrupamento

Valor

Resultado

Notas

'fr-ai'

E = É

True

'fr-as' / 'fr'

E = É

False

'en-ai'

a = ą

True

Em inglês, estas letras são tratadas como tendo apenas diferenças de acento, portanto, ao especificar a não diferenciação de conta, os valores são comparados como iguais.

'pl-ai'

a = ą

False

Em polonês, estas letras são tratadas como letras-base separadas, de modo que sempre se comparam como desiguais, independentemente de haver a especificação para não identificar acentos.

'pl-as' / 'pl'

a = ą

False

Observe que as regras de identificação de acentos e agrupamento variam entre os idiomas. Por exemplo, em alguns idiomas, o agrupamento sempre identifica acentos e não é possível desativá-lo, mesmo especificando o agrupamento sem identificação de acentos.

Identificação de pontuação:

Determina se caracteres que não são letras devem ser considerados. Possíveis valores:

  • ps - Identifica pontuação.

  • pi - Não identifica pontuação.

Observe que o padrão é específico do local (isto é, se a identificação de pontuação não for determinada, são usadas regras específicas do local). Na maioria dos casos, as regras são equivalentes a ps.

Por exemplo:

Especificação de agrupamento

Valor

Resultado

Notas

'en-pi'

A-B-C = ABC

True

'en-ps'

A-B-C = ABC

False

Preferência pela primeira letra:

Determina se, ao classificar, as letras maiúsculas ou minúsculas devem ser as primeiras. Possíveis valores:

  • fl - As letras minúsculas são classificadas primeiro.

  • fu - As letras maiúsculas são classificadas primeiro.

Observe que o padrão é específico do local (isto é, se nenhum valor for determinado, é usada a classificação específica do local). Na maioria dos casos, a classificação é equivalente a fl.

Além disso, este especificador não tem impacto nas comparações de igualdade.

Conversão de maiúsculas/minúsculas:

Resulta na conversão das cadeia de caracteres em minúsculas ou maiúsculas antes das comparações. Em algumas situações, isso é mais rápido do que um agrupamento completo específico de local. Possíveis valores:

  • upper - Converte a cadeia de caracteres em maiúsculas antes das comparações.

  • lower - Converte a cadeia de caracteres em minúsculas antes das comparações.

Observe que este especificador não tem um valor padrão (isto é, se nenhum valor for especificado, nenhuma das conversões ocorre).

Corte de espaço em branco:

Remove os espaços iniciais/finais das cadeia de caracteres antes das comparações. Esta funcionalidade pode ser útil para realizar comparações equivalentes (exceto em casos extremamente raros de cantos) na semântica para o tipo de dados SQL CHAR.

Possíveis valores:

  • trim - Remove os espaços iniciais e finais antes das comparações.

  • ltrim - Remove somente os espaços iniciais antes das comparações.

  • rtrim - Remover somente os espaços finais antes das comparações.

Observe que este especificador não tem um valor padrão (isto é, se nenhum valor for especificado, o corte não é realizado).

Por exemplo:

Especificação de agrupamento

Valor

Resultado

Notas

'en-trim'

__ABC_ = ABC

True

Para fins de exemplo, os caracteres sublinhados representam espaços em branco.

'en-ltrim'

__ABC_ = ABC

False

'en-rtrim'

__ABC_ = ABC

False

'en'

__ABC_ = ABC

False

Detalhes da implementação do agrupamento

As seções a seguir fornecem mais detalhes sobre o suporte ao agrupamento:

Comparação sem diferenciar maiúsculas e minúsculas

Comparação de cadeias de caracteres maiúsculos vs. comparação das cadeias de caracteres originais

Em algumas linguagens, dois caracteres em minúsculas têm o mesmo caractere correspondente em maiúsculas. Por exemplo, algumas linguagens aceitam formas com pingo e sem pingo no I minúsculo (i vs. ı). Forçar as cadeias de caracteres para maiúsculas afeta as comparações.

Veja a diferença ilustrada a seguir:

Crie a tabela:

create or replace table test_table (col1 varchar, col2 varchar);
insert into test_table values ('ı', 'i');
Copy

Consultar os dados:

select col1 = col2,
       COLLATE(col1, 'lower') = COLLATE(col2, 'lower'),
       COLLATE(col1, 'upper') = COLLATE(col2, 'upper')
    from test_table;
+-------------+-------------------------------------------------+-------------------------------------------------+
| COL1 = COL2 | COLLATE(COL1, 'LOWER') = COLLATE(COL2, 'LOWER') | COLLATE(COL1, 'UPPER') = COLLATE(COL2, 'UPPER') |
|-------------+-------------------------------------------------+-------------------------------------------------|
| False       | False                                           | True                                            |
+-------------+-------------------------------------------------+-------------------------------------------------+
Copy

Pesos dos caracteres

O Snowflake oferece suporte às seguintes especificações de agrupamento.

  • ICU (Componentes internacionais para Unicode).

  • Especificações de agrupamento específicas do Snowflake (por exemplo, upper e lower).

Para operações de comparação sem distinção de maiúsculas e minúsculas definidas pelo ICU, o Snowflake segue o Algoritmo de agrupamento Unicode (UCA) e considera apenas os pesos primário e secundário, não os pesos terciários, dos caracteres Unicode. Os caracteres que têm apenas pesos terciários diferentes são tratados como idênticos. Por exemplo, ao usar a especificação de agrupamento en-ci, um espaço e um espaço contínuo são considerados idênticos.

Classificação usando UTF-8 vs. agrupamento de local

As cadeias de caracteres são sempre armazenadas internamente no Snowflake em UTF-8 e podem representar qualquer caractere em qualquer idioma compatível com UTF-8; portanto, o agrupamento padrão é UTF-8 (ou seja, 'utf8').

O agrupamento UTF-8 é baseado na representação numérica do caractere, e não na ordem alfabética do caractere.

Isso é análogo à classificação pelo valor ordinal de cada caractere ASCII, o que é importante notar porque as letras maiúsculas têm valores ordinais inferiores às letras minúsculas:

A = 65
B = 66
...
a = 97
b = 98
...

Como resultado:

  • Se você classificar na ordem UTF-8, todas as letras maiúsculas são retornadas antes de todas as letras minúsculas:

    A , B , … , Y , Z , … , a , b , … , y , z

  • Em contraste, a especificação de agrupamento 'en' classifica alfabeticamente (em vez de usar a representação interna UTF-8), fazendo com que A e a sejam retornados antes de B e b:

    a , A , b , B , …

Além disso, as diferenças entre os especificadores de diferenciação entre maiúsculas e minúsculas cs e ci impactam a classificação:

  • cs (diferencia maiúsculas e minúsculas) sempre retorna a versão em minúsculas de uma letra antes da versão em maiúsculas da mesma letra. Por exemplo, usando 'en-cs':

    a , A , b , B , …

    Observe que o padrão é a diferenciação entre maiúsculas e minúsculas e, portanto, 'en-cs' e 'en' são equivalentes.

  • ci (case-insensitive, ou seja, sem diferenciar maiúsculas de minúsculas) retorna versões maiúsculas e minúsculas das letras aleatoriamente em relação uma à outra, mas ainda antes das versões maiúsculas e minúsculas das letras posteriores. Por exemplo, usando 'en-ci':

    A , a , b , B , …

Alguns caracteres não alfabéticos também podem ser classificados de forma diferente, dependendo da configuração de agrupamento. O exemplo de código a seguir mostra que os caracteres mais (+) e menos (-) são classificados de forma distinta para diferentes configurações de agrupamento:

Crie a tabela:

create or replace table demo (
    no_explicit_collation VARCHAR,
    en_ci VARCHAR COLLATE 'en-ci',
    en VARCHAR COLLATE 'en',
    utf_8 VARCHAR collate 'utf8');
insert into demo (no_explicit_collation) values
    ('-'),
    ('+');
update demo SET
    en_ci = no_explicit_collation,
    en = no_explicit_collation,
    utf_8 = no_explicit_collation;
Copy

Consultar os dados:

select max(no_explicit_collation), max(en_ci), max(en), max(utf_8)
    from demo;
+----------------------------+------------+---------+------------+
| MAX(NO_EXPLICIT_COLLATION) | MAX(EN_CI) | MAX(EN) | MAX(UTF_8) |
|----------------------------+------------+---------+------------|
| -                          | +          | +       | -          |
+----------------------------+------------+---------+------------+
Copy

Precedência de agrupamento em operações com várias cadeias de caracteres

Ao realizar uma operação em duas (ou mais) cadeias de caracteres, podem ser especificados diferentes agrupamentos para diferentes cadeias. A determinação do agrupamento a ser aplicado depende de como o agrupamento foi especificado para cada entrada e da precedência de cada especificador.

Existem 3 níveis de precedência (do mais alto para o mais baixo):

Função:

O agrupamento é especificado usando a função Função COLLATE em uma instrução SQL.

Coluna:

O agrupamento foi especificado na definição de coluna.

Nenhum:

Nenhum agrupamento é/foi especificado para uma determinada expressão/coluna, ou é/foi utilizado um agrupamento com uma especificação vazia (por exemplo, COLLATE(col1, '') ou col1 STRING COLLATE '').

Ao determinar o agrupamento a ser usado, é utilizada a especificação de agrupamento com a precedência mais alta. Se forem especificados vários agrupamentos e tiverem o mesmo nível de precedência, seus valores são comparados, e se não forem iguais, um erro é retornado.

Por exemplo, considere uma tabela com as seguintes especificações de agrupamento no nível da coluna:

CREATE OR REPLACE TABLE collation_precedence_example(
  col1    VARCHAR,               -- equivalent to COLLATE ''
  col2_fr VARCHAR COLLATE 'fr',  -- French locale
  col3_de VARCHAR COLLATE 'de'   -- German locale
);
Copy

Se a tabela for utilizada em uma instrução comparando duas cadeia de caracteres, o agrupamento é aplicado da seguinte forma:

-- Uses the 'fr' collation because the precedence for col2_fr is higher than
-- the precendence for col1.
... WHERE col1 = col2_fr ...

-- Uses the 'en' collation, because it is explicitly specified in the statement,
-- which takes precedence over the collation for col2_fr.
... WHERE col1 COLLATE 'en' = col2_fr ...

-- Returns an error because the expressions have different collations at the same
-- precedence level.
... WHERE col2_fr = col3_de ...

-- Uses the 'de' collation because collation for col2_fr has been removed.
... WHERE col2_fr COLLATE '' = col3_de ...

-- Returns an error because the expressions have different collations at the same
-- precedence level.
... WHERE col2_fr COLLATE 'en' = col3_de COLLATE 'de' ...
Copy

Nota

Mesmo que o agrupamento padrão do Snowflake seja 'utf8', especificar uma cadeia de caracteres vazia (ou não especificar um agrupamento) é diferente de especificar explicitamente 'utf8', porque o agrupamento explícito tem maior precedência do que o não agrupamento. As duas últimas instruções nos exemplos de código abaixo mostram a diferença:

CREATE OR REPLACE TABLE collation_precedence_example2(
  s1 STRING COLLATE '',
  s2 STRING COLLATE 'utf8',
  s3 STRING COLLATE 'fr'
);

-- Uses 'utf8' because s1 has no collation and 'utf8' is the default.
SELECT * FROM collation_precedence_example2 WHERE s1 = 'a';

-- Uses 'utf8' because s1 has no collation and s2 has explicit 'utf8' collation.
SELECT * FROM collation_precedence_example2 WHERE s1 = s2;
Copy

Este exemplo é executado sem erro porque s1 não tem agrupamento e s3 tem agrupamento fr explícito, portanto o agrupamento explícito tem precedência:

SELECT * FROM collation_precedence_example2 WHERE s1 = s3;
+----+----+----+
| S1 | S2 | S3 |
|----+----+----|
+----+----+----+
Copy

Este exemplo gera um erro porque s2 e s3 têm agrupamentos diferentes especificados no mesmo nível de precedência:

SELECT * FROM collation_precedence_example2 WHERE s2 = s3;
Copy

Saída:

002322 (42846): SQL compilation error: Incompatible collations: 'fr' and 'utf8'
Copy

Suporte limitado para agrupamento em funções internas

O agrupamento oferece suporte apenas a um subconjunto de funções de cadeia de caracteres. Funções que poderiam ser razoavelmente esperadas para implementar o agrupamento, mas que ainda não oferecem suporte a agrupamento, retornam um erro quando usadas com o agrupamento. Estas mensagens de erro são exibidas não apenas ao chamar a função COLLATE, mas também ao chamar uma função de cadeia de caracteres em uma coluna que foi definida como agrupada na instrução CREATE TABLE ou ALTER TABLE que criou aquela coluna.

Atualmente, o agrupamento influencia apenas as operações de comparação simples.

Por exemplo, POSITION('abc' in COLLATE('ABC', 'en-ci')) não encontra abc em ABC, mesmo que seja especificado um agrupamento que não diferencia maiúsculas e minúsculas.

Funções com suporte para agrupamento

Estas funções oferecem suporte para agrupamento:

Algumas dessas funções têm limitações de uso com agrupamento. Para obter mais detalhes, consulte a documentação de cada função específica.

Essa lista pode se expandir com o tempo.

Cuidado

Alguns operadores e predicados SQL, como || (concatenação) e LIKE, são implementados como funções (e estão disponíveis como funções, por exemplo LIKE() e CONCAT()). Se um predicado ou operador é implementado como uma função, e a função não aceita agrupamento, então o predicado ou operador não aceita agrupamento.

Consulte também Limitações de agrupamento.

Consequências de desempenho do uso do agrupamento

O uso do agrupamento pode afetar o desempenho de várias operações do banco de dados:

  • As operações que envolvem comparações podem ser mais lentas.

    Isso pode impactar cláusulas WHERE simples, bem como junções, classificações, operações GROUP BY etc.

  • Quando usada com algumas funções em predicados WHERE, a remoção de micropartição pode ser menos eficiente.

  • A utilização de um predicado WHERE que seja diferente do agrupamento especificado para a coluna pode resultar em uma eficiência de remoção reduzida ou a eliminação completa da remoção.

Considerações adicionais para o uso do agrupamento

  • Lembre-se de que, apesar da semelhança em seus nomes, as seguintes funções de agrupamento retornam resultados diferentes:

    • COLLATE especifica explicitamente qual agrupamento usar.

    • COLLATION mostra qual agrupamento é usado se nenhum for especificado explicitamente.

  • Uma coluna com especificação de agrupamento pode usar caracteres que não são do local para o agrupamento, o que pode causar impacto na classificação.

    Por exemplo, se uma coluna for criada com uma cláusula COLLATE 'en', os dados na coluna podem conter o caractere É não existente em inglês. Nessa situação, o caractere É é classificado próximo a E.

  • Você pode especificar operações de agrupamento que não são necessariamente significativas.

    Por exemplo, você pode especificar para que os dados poloneses sejam comparados com os dados franceses usando o agrupamento alemão:

    SELECT ... WHERE COLLATE(French_column, 'de') = Polish_column;
    
    Copy

    Entretanto, o Snowflake recomenda não utilizar o recurso dessa forma porque pode retornar resultados inesperados ou não intencionais.

  • Uma vez definida uma coluna da tabela, não é possível alterar o agrupamento da coluna. Em outras palavras, após uma coluna ter sido criada com um determinado agrupamento usando uma instrução CREATE TABLE, não é possível usar ALTER TABLE para alterar o agrupamento.

    Entretanto, é possível especificar um agrupamento diferente em uma instrução DML, como uma instrução SELECT, que faz referência à coluna.

Diferenças entre ci e upper / lower

As especificações de agrupamento upper e lower podem fornecer melhor desempenho do que a especificação de agrupamento ci durante a comparação e classificação de cadeias de caracteres. No entanto, upper e lower têm efeitos ligeiramente diferentes de ci, conforme explicado nas próximas seções:

Diferenças no tratamento de pontos de código ignoráveis

O algoritmo de agrupamento Unicode especifica que os elementos de agrupamento (pontos de código) podem ser ignoráveis, o que significa que um ponto de código não é considerado durante a comparação e classificação de cadeias de caracteres.

  • Com a especificação de agrupamento ci, esses pontos de código são ignorados. Isso pode dificultar a busca ou substituição de pontos de código ignoráveis.

  • Com as especificações de agrupamento upper e lower, esses pontos de código não são ignorados.

Por exemplo, o ponto de código U+0001 é ignorável. Se você comparar esse ponto de código a uma cadeia de caracteres vazia com a especificação de agrupamento en-ci, o resultado será TRUE porque U+0001 será ignorado:

SELECT '\u0001' = '' COLLATE 'en-ci';
Copy
+-------------------------------+
| '\U0001' = '' COLLATE 'EN-CI' |
|-------------------------------|
| True                          |
+-------------------------------+

Por outro lado, se você usar a especificação de agrupamento upper ou lower, o resultado será FALSE porque U+0001 não será ignorado:

SELECT '\u0001' = '' COLLATE 'upper';
Copy
+-------------------------------+
| '\U0001' = '' COLLATE 'UPPER' |
|-------------------------------|
| False                         |
+-------------------------------+

Da mesma forma, suponha que você chame a função REPLACE para remover esse ponto de código de uma cadeia de caracteres. Se você usar a especificação de agrupamento en-ci, a função não removerá o ponto de código porque U+0001 será ignorado.

Conforme mostrado no exemplo abaixo, a cadeia de caracteres retornada pela função REPLACE tem o mesmo comprimento que a cadeia de caracteres passada para a função porque a função não remove o caractere U+0001.

SELECT
  LEN('abc\u0001') AS original_length,
  LEN(REPLACE('abc\u0001' COLLATE 'en-ci', '\u0001')) AS length_after_replacement;
Copy
+-----------------+--------------------------+
| ORIGINAL_LENGTH | LENGTH_AFTER_REPLACEMENT |
|-----------------+--------------------------|
|               4 |                        4 |
+-----------------+--------------------------+

Por outro lado, se você usar a especificação de agrupamento upper ou lower, a função removerá o ponto de código da cadeia de caracteres, retornando uma cadeia de caracteres mais curta.

SELECT
  LEN('abc\u0001') AS original_length,
  LEN(REPLACE('abc\u0001' COLLATE 'upper', '\u0001')) AS length_after_replacement;
Copy
+-----------------+--------------------------+
| ORIGINAL_LENGTH | LENGTH_AFTER_REPLACEMENT |
|-----------------+--------------------------|
|               4 |                        3 |
+-----------------+--------------------------+

Diferenças quando os caracteres são representados por diferentes pontos de código

Em Unicode, diferentes sequências de pontos de código podem representar o mesmo caractere. Por exemplo, a letra minúscula grega Iota com Dialytika e Tonos pode ser representada pelo caractere pré-composto com o ponto de código U+0390 ou pela sequência de pontos de código U+03b9 U+0308 U+0301 para os caracteres decompostos.

Se você usar a especificação de agrupamento ci, as diferentes sequências de pontos de código de um caractere serão tratadas como o mesmo caractere. Por exemplo, o ponto de código U+0390 e a sequência de pontos de código U+03b9 U+0308 U+0301 são tratados como equivalentes:

SELECT '\u03b9\u0308\u0301' = '\u0390' COLLATE 'en-ci';
Copy
+-------------------------------------------------+
| '\U03B9\U0308\U0301' = '\U0390' COLLATE 'EN-CI' |
|-------------------------------------------------|
| True                                            |
+-------------------------------------------------+

Para melhorar o desempenho das especificações de agrupamento upper e lower, as sequências não são tratadas da mesma maneira. Duas sequências de pontos de código são consideradas equivalentes apenas se resultarem na mesma representação binária após serem convertidas para maiúsculas ou minúsculas.

Por exemplo, usar a especificação upper com o ponto de código U+0390 e a sequência de pontos de código U+03b9 U+0308 U+0301 resulta em caracteres que são tratados como iguais:

SELECT '\u03b9\u0308\u0301' = '\u0390' COLLATE 'upper';
Copy
+-------------------------------------------------+
| '\U03B9\U0308\U0301' = '\U0390' COLLATE 'UPPER' |
|-------------------------------------------------|
| True                                            |
+-------------------------------------------------+

Usar a especificação lower resulta em caracteres diferentes:

SELECT '\u03b9\u0308\u0301' = '\u0390' COLLATE 'lower';
Copy
+-------------------------------------------------+
| '\U03B9\U0308\U0301' = '\U0390' COLLATE 'LOWER' |
|-------------------------------------------------|
| False                                           |
+-------------------------------------------------+

É menos provável que essas diferenças ocorram ao usar upper (em vez de lower) porque há apenas um ponto de código composto em maiúsculas (U+0130), em comparação com mais de 100 pontos de código compostos em minúsculas.

Diferenças com sequências de pontos de código que representam um único caractere

Nos casos em que uma sequência de pontos de código representa um único caractere, a especificação de agrupamento ci reconhece que a sequência representa um único caractere e não corresponde a pontos de código individuais na sequência.

Por exemplo, a sequência de pontos de código U+03b9 U+0308 U+0301 representa um único caractere (a letra minúscula grega Iota com Dialytika e Tonos). U+0308 e U+0301 representam acentos aplicados a U+03b9.

Para a especificação de agrupamento ci, se você usar a função CONTAINS para determinar se a sequência U+03b9 U+0308 contém U+03b9 ou U+0308, a função retornará FALSE porque a sequência U+03b9 U+0308 é tratada como um único caractere:

SELECT CONTAINS('\u03b9\u0308', '\u03b9' COLLATE 'en-ci');
Copy
+----------------------------------------------------+
| CONTAINS('\U03B9\U0308', '\U03B9' COLLATE 'EN-CI') |
|----------------------------------------------------|
| False                                              |
+----------------------------------------------------+
SELECT CONTAINS('\u03b9\u0308', '\u0308' COLLATE 'en-ci');
Copy
+----------------------------------------------------+
| CONTAINS('\U03B9\U0308', '\U0308' COLLATE 'EN-CI') |
|----------------------------------------------------|
| False                                              |
+----------------------------------------------------+

Para melhorar o desempenho, as especificações upper e lower não tratam essas sequências como um único caractere. No exemplo acima, a função CONTAINS retorna TRUE porque essas especificações tratam a sequência de pontos de código como caracteres separados:

SELECT CONTAINS('\u03b9\u0308', '\u03b9' COLLATE 'upper');
Copy
+----------------------------------------------------+
| CONTAINS('\U03B9\U0308', '\U03B9' COLLATE 'UPPER') |
|----------------------------------------------------|
| True                                               |
+----------------------------------------------------+
SELECT CONTAINS('\u03b9\u0308', '\u0308' COLLATE 'upper');
Copy
+----------------------------------------------------+
| CONTAINS('\U03B9\U0308', '\U0308' COLLATE 'UPPER') |
|----------------------------------------------------|
| True                                               |
+----------------------------------------------------+

Diferenças quando alterações no caso resultam em vários pontos de código

Para alguns caracteres compostos, a versão maiúscula ou minúscula do caractere é representada por uma sequência de pontos de código. Por exemplo, o caractere maiúsculo do caractere alemão ß é uma sequência de dois caracteres S (SS).

Embora ß e SS sejam equivalentes, quando você usa a especificação de agrupamento upper, as pesquisas de ß e SS retornam resultados diferentes. As sequências produzidas pela conversão de letra maiúscula e minúscula correspondem totalmente ou não correspondem.

SELECT CONTAINS('ß' , 's' COLLATE 'upper');
Copy
+--------------------------------------+
| CONTAINS('SS' , 'S' COLLATE 'UPPER') |
|--------------------------------------|
| False                                |
+--------------------------------------+
SELECT CONTAINS('ss', 's' COLLATE 'upper');
Copy
+-------------------------------------+
| CONTAINS('SS', 'S' COLLATE 'UPPER') |
|-------------------------------------|
| True                                |
+-------------------------------------+

Diferenças na ordem de classificação

A classificação para as especificações de agrupamento upper e lower funciona de maneira diferente da classificação para a especificação ci:

  • Com a especificação ci, as cadeias de caracteres são classificadas por chave de agrupamento. Em geral, a chave de agrupamento pode levar em conta a distinção entre maiúsculas e minúsculas, sensibilidade ao acento, localidade etc.

  • Com as especificações upper e lower, as cadeias de caracteres são classificadas por ponto de código para melhorar o desempenho.

Por exemplo, alguns caracteres dentro do intervalo ASCII (como + e -) são classificados de forma diferente:

SELECT '+' < '-' COLLATE 'en-ci';
Copy
+---------------------------+
| '+' < '-' COLLATE 'EN-CI '|
|---------------------------|
| False                     |
+---------------------------+
SELECT '+' < '-' COLLATE 'upper';
Copy
+---------------------------+
| '+' < '-' COLLATE 'UPPER' |
|---------------------------|
| True                      |
+---------------------------+

Como outro exemplo, cadeias de caracteres com pontos de código ignorados são classificados em uma ordem diferente:

SELECT 'a\u0001b' < 'ab' COLLATE 'en-ci';
Copy
+-----------------------------------+
| 'A\U0001B' < 'AB' COLLATE 'EN-CI' |
|-----------------------------------|
| False                             |
+-----------------------------------+
SELECT 'a\u0001b' < 'ab' COLLATE 'upper';
Copy
+-----------------------------------+
| 'A\U0001B' < 'AB' COLLATE 'UPPER' |
|-----------------------------------|
| True                              |
+-----------------------------------+

Além disso, os emojis são classificados de maneira diferente:

SELECT 'abc' < '❄' COLLATE 'en-ci';
Copy
+-----------------------------+
| 'ABC' < '❄' COLLATE 'EN-CI' |
|-----------------------------|
| False                       |
+-----------------------------+
SELECT 'abc' < '❄' COLLATE 'upper';
Copy
+-----------------------------+
| 'ABC' < '❄' COLLATE 'UPPER' |
|-----------------------------|
| True                        |
+-----------------------------+

Limitações de agrupamento

O agrupamento somente é suportado para cadeias de caracteres de até 8MB

Embora o tipo de dados do Snowflake VARCHAR ofereça suporte a cadeias de caracteres de até 16MB, o Snowflake somente oferece suporte para o agrupamento quando a cadeia de caracteres resultante tem 8MB ou menos. (Algumas operações de agrupamento podem tornar uma cadeia de caracteres mais longa).

Limitações de agrupamento e UDFs

O Snowflake não oferece suporte para agrupamento com UDFs (funções definidas pelo usuário):

  • Você não pode retornar um valor de cadeia de caracteres agrupado a partir de uma UDF; o servidor informa que o tipo de retorno real é incompatível com o tipo de retorno declarado.

  • Se você passar um valor de cadeia de caracteres agrupado para uma UDF, a informação de agrupamento não é passada; a UDF vê a cadeia de caracteres como uma cadeia não agrupada.

Agrupamento sem suporte para cadeias de caracteres em VARIANT, ARRAY ou OBJECT

As cadeia de caracteres armazenadas dentro de um VARIANT, OBJECT ou ARRAY não incluem uma especificação de agrupamento. Portanto:

  • A comparação desses valores usa sempre o agrupamento “utf8”.

  • Quando um valor VARCHAR com uma especificação de agrupamento é usado para construir um valor de ARRAY, OBJECT ou VARIANT, a especificação de agrupamento não é preservada.

  • Os usuários podem ainda comparar um valor armazenado dentro de um ARRAY, OBJECT ou VARIANT, extraindo o valor, convertendo-o para VARCHAR e adicionando uma especificação de agrupamento. Por exemplo:

    COLLATE(VARIANT_COL:fld1::VARCHAR, 'en-ci') = VARIANT_COL:fld2::VARCHAR
    
    Copy

Exemplos

A seguinte instrução cria uma tabela que usa um agrupamento diferente para cada coluna:

CREATE TABLE collation_demo (
  uncollated_phrase VARCHAR, 
  utf8_phrase VARCHAR COLLATE 'utf8',
  english_phrase VARCHAR COLLATE 'en',
  spanish_phrase VARCHAR COLLATE 'sp'
  );

INSERT INTO collation_demo (uncollated_phrase, utf8_phrase, english_phrase, spanish_phrase) 
   VALUES ('pinata', 'pinata', 'pinata', 'piñata');
Copy

A seguinte consulta na tabela mostra os valores esperados:

SELECT * FROM collation_demo;
+-------------------+-------------+----------------+----------------+
| UNCOLLATED_PHRASE | UTF8_PHRASE | ENGLISH_PHRASE | SPANISH_PHRASE |
|-------------------+-------------+----------------+----------------|
| pinata            | pinata      | pinata         | piñata         |
+-------------------+-------------+----------------+----------------+
Copy

A seguinte consulta não encontra uma correspondência porque o caractere ñ não corresponde a n:

SELECT * FROM collation_demo WHERE spanish_phrase = uncollated_phrase;
+-------------------+-------------+----------------+----------------+
| UNCOLLATED_PHRASE | UTF8_PHRASE | ENGLISH_PHRASE | SPANISH_PHRASE |
|-------------------+-------------+----------------+----------------|
+-------------------+-------------+----------------+----------------+
Copy

Mudar o agrupamento não força caracteres relacionados, mas desiguais (por exemplo, ñ e n), a serem tratados como iguais:

SELECT * FROM collation_demo 
    WHERE spanish_phrase = uncollated_phrase COLLATE 'sp';
+-------------------+-------------+----------------+----------------+
| UNCOLLATED_PHRASE | UTF8_PHRASE | ENGLISH_PHRASE | SPANISH_PHRASE |
|-------------------+-------------+----------------+----------------|
+-------------------+-------------+----------------+----------------+
Copy

Os exemplos a seguir demonstram o efeito do agrupamento na ordem de classificação:

INSERT INTO collation_demo (spanish_phrase) VALUES
   ('piña colada'),
   ('Pinatubo (Mount)'),
   ('pint'),
   ('Pinta');
Copy
SELECT spanish_phrase FROM collation_demo 
  ORDER BY spanish_phrase;
+------------------+
| SPANISH_PHRASE   |
|------------------|
| piña colada      |
| piñata           |
| Pinatubo (Mount) |
| pint             |
| Pinta            |
+------------------+
Copy

A seguinte consulta inverte a ordem de ñ e n, alterando o agrupamento de “sp” (espanhol) para “utf8”:

SELECT spanish_phrase FROM collation_demo 
  ORDER BY COLLATE(spanish_phrase, 'utf8');
+------------------+
| SPANISH_PHRASE   |
|------------------|
| Pinatubo (Mount) |
| Pinta            |
| pint             |
| piña colada      |
| piñata           |
+------------------+
Copy

Este exemplo mostra como usar a função COLLATION para visualizar o agrupamento de uma expressão, como uma coluna:

CREATE TABLE collation_demo2 (c1 VARCHAR COLLATE 'fr', c2 VARCHAR COLLATE '');
INSERT INTO collation_demo2 (c1, c2) VALUES
    ('a', 'a'),
    ('b', 'b');
Copy
SELECT DISTINCT COLLATION(c1), COLLATION(c2) FROM collation_demo2;
+---------------+---------------+
| COLLATION(C1) | COLLATION(C2) |
|---------------+---------------|
| fr            | NULL          |
+---------------+---------------+
Copy

Você também pode usar DESCRIBE TABLE para visualizar informações de agrupamento sobre as colunas em uma tabela:

DESC TABLE collation_demo2;
+------+--------------------------------+--------+-------+---------+-------------+------------+-------+------------+---------+-------------+ 
| name | type                           | kind   | null? | default | primary key | unique key | check | expression | comment | policy name |
|------+--------------------------------+--------+-------+---------+-------------+------------+-------+------------+---------+-------------|
| C1   | VARCHAR(16777216) COLLATE 'fr' | COLUMN | Y     | NULL    | N           | N          | NULL  | NULL       | NULL    | NULL        |
| C2   | VARCHAR(16777216)              | COLUMN | Y     | NULL    | N           | N          | NULL  | NULL       | NULL    | NULL        |
+------+--------------------------------+--------+-------+---------+-------------+------------+-------+------------+---------+-------------+
Copy