Colação

Possivelmente, você já se encontrou numa situação em que pediu para o R ou o PostgreSQL ordenar uma coluna de texto e ficou surpreso ao ver que ‘20’ apareceu depois de ‘100’ ou não sabe a razão porque caixa baixa vem antes de caixa alta ou porque ‘Ágora’ vem depois de ‘agora’ ou, mais grave, não sabe quem vem primeiro se ‘vovó’ ou ‘vovô’.

Além disso, por vezes, você quer realizar buscas ignorando acentuação e caixa. Por exemplo, sabe que o nome do juiz André de Paula pode estar escrito das seguintes formas: André de Paula, Andre de Paula ou ANDRE DE PAULA. Naturalmente, você pode preprocessar os termos de busca e usar expressões regulares para garantir que qualquer das três forma que a pessoa escrever, o resultado será aquele armazenado em sua tabela.

Uma outra situação que você seguramente já se encontrou foi realizar buscas com sequências de números separados por pontuação, mas a sequência na sua tabela não está pontuada ou vice-versa. Bons exemplos são o CNPJ, o CPF, o CEP ou o número do processo judicial.

A essa altura, você já deve ter perguntado se existem regras para ordenar e comparar caracteres. A esse conjunto de regras para comparar e ordenar caracteres, respeitando os diferentes idiomas, chamamos de colação.

Colação deve ser usada com cuidado para não ter surpresas como alguém pedir um bolo de coco e receber um bolo de cocô.

Neste tutorial, assumimos que tanto o R quanto o PostgreSQl estão configurados para usar unicode, mais especificamente UTF-8. Se você não adotou unicode, melhor fazê-lo para evitar infindáveis problemas com comparação ou mesmo visualização de caracteres.

As regras de colação são amplas e à primeira vista um tanto complexas, você pode consultá-las aqui. Neste tutorial trataremos das mais comumente utilizadas, que são aquelas que tratam de caixa, i.e. maiúsculo ou minúsculo, acentuação, ordem numérica e pontuação.

Unicode e ICU

Unicode é um padrão de tecnologia da informação para dar consistência ao encoding, à representação e ao manuseio de caracteres wikipedia. Encoding é basicamente um sistema, dentre muitos, adotado para representar caracteres numericamente na linguagem do computador.

Antes do Unicode ascender como padrão preferível, havia uma multiplicidade de regras definidas conforme a conveniência da lingua ou do criador do programa. Infelizmente ainda há, mas ao menos agora todos têm uma opção unificadora que cobre praticamente todos os caracteres existentes.

ICU (International Components for Unicode) é um conjunto de bibliotecas escritas para C/C++ e Java para permitir que programas de computador adotem o Unicode. No R, os pacotes stringi e stringr usam ICU não só para colação, como também para expressões regulares. No PostgreSQL, a partir da versão 12 passou a ser possível optar por colação não-determinística e, com isso, aplicar as várias opções de ICU para colação.

Dataframe

Para iniciar, vamos criar um dataframe que nos permita trabalhar tanto no R quanto no PostgreSQL e aplicar as quatro opções indicadas: caixa, acentuação, pontuação e numérico.

library(tidyverse)
df <- data.frame(nome = c("Mário","Flávia","Angélica","Vinícios"),
             cpf = c("432.097.759-39", "017.372.652-92", "612.260.255-65", "828.483.096-08"),
             codigo = c("g123","g27","g34","g257")
                )

nome	cpf	codigo
Mário	432.097.759-39	g123
Flávia	017.372.652-92	g27
Angélica	612.260.255-65	g34
Vinícios	828.483.096-08	g257

Colação no R

O pacote stringi possui funções específicas para aplicar regras de colação. Você controla o comportamento do “colator” ICU levando em conta os quatro problemas acima mencionados por meio de quatro argumentos:


| argumento          | opções | padrao | descrição                      | 
|--------------------|--------|        |--------------------------------|
| strength           | 1L     |        | ignora caixa e acentos         |
|                    | 2L     |        | ignora caixa                   |
|                    | 3L     |   x    |                                |
|                    | 4L     |        |                                |
| alternated_shifted | TRUE   |        | ignora pontuação               |
|                    | FALSE  |   x    | não ignora pontuação           |
| numeric            | TRUE   |        | considera sequência e números  |
|                    | FALSE  |   x    | considera cada número          |
| locale             | NULL   |   x    | Não define locale              |
|                    | ''     |        | Locale padão                   |
|                    | outros |        | outros locales,e.g.,pt_BR-UTF-8|

Eu particularmente considero as opções de argumento no stringi bem confusas. Se você ler a documentação oficial da ICU, verá que no primeiro nível você desconsidera acentos e caixa, mas para ignorar acentos, mas não caixa, você ajusta Case_Level para ‘On’. Este é o mesmo comportamento no PostgreSQL. Com o pacote stringi, não acontece nada se você optar por case_level = TRUE. Para que isso aconteça, você tem de ajustar o locale para não NULL, ou seja, para "" (padrão) ou para algum idioma.

Ordem numérica

Vamos ordernar o dataframe criado acima pela coluna codigo:

df %>% 
  arrange(codigo)

      nome            cpf codigo
1    Mário 432.097.759-39   g123
2 Vinícios 828.483.096-08   g257
3   Flávia 017.372.652-92    g27
4 Angélica 612.260.255-65    g34

Possivelmente não era esse o resultado que você queria, pois esperava que “g123” viesse depois de “g27” e de “g34”. Se você quiser garantir a ordenação conforme os números, use a função stri_sort com o argumento numeric = TRUE. Dessa forma, o R usará colação para garantir a ordem desejada:

library(stringi)
stri_sort(df$codigo, numeric = TRUE)

[1] "g27"  "g34"  "g123" "g257"

No entanto, para usar dentro do arrange, você deve informar qual posição deve ocupar cada um desses valores. Isto é, que o “g123” vá para a terceira posição, o “g27” vá para a primeira e assim por diante. Para tanto, a função match do pacote base vem ao socorro.

df %>% 
  arrange(match(codigo, stri_sort(codigo, numeric = TRUE)))

      nome            cpf codigo
1   Flávia 017.372.652-92    g27
2 Angélica 612.260.255-65    g34
3    Mário 432.097.759-39   g123
4 Vinícios 828.483.096-08   g257

Acentos e Caixa

Vamos olhar agora para o filtro por nome. Se você quiser filtrar por “Mario” sem acento, você poderia simplesmente remover os acentos antes. Vejamos:

df %>% 
  mutate(nome = stringi::stri_trans_general(nome,"latin-ascii")) %>% 
  filter(nome == "Mario")

   nome            cpf codigo
1 Mario 432.097.759-39   g123

E se quiser garantir que caso o filtro seja por “mario”, “MARIO” ou “Mario”, poderia recorrer a regex, com a vantagem de que não precisa transformar a variável:

df %>% 
   filter(stri_detect_regex(nome,"(?i)m[áa]rio"))

   nome            cpf codigo
1 Mário 432.097.759-39   g123

As desvantagens dessas duas abordagens são notórias, pois se sua intenção é realizar a busca conforme um termo fornecido pelo usuário, regex irá ajudar muito pouco. Para essas situações, colação vem a calhar:

df  %>% 
   filter(stri_detect_coll(nome,"mario", strength = 1L))

   nome            cpf codigo
1 Mário 432.097.759-39   g123

Para filtrar, eu usei a função stri_detect_coll. Esta função usará regras de colação para comparar os caracteres. A regra usada foi a de nível 1, argumento strength = 1L, ou seja, a mais flexível, indicando que serão ignorados os acentos e a caixa.

Se você usar o nível 2, apenas caixa será ignorada:

stri_detect_coll("mário","MÁRIO", strength = 2L)

[1] TRUE

Porém, obterá FALSE se não acentuar a palavra.

stri_detect_coll("mário","MARIO", strength = 2L)

[1] FALSE

Se quiser ignorar a acentuação, mas não ignorar a caixa, terá de manter o nível 1 e informar o locale padrão:

stri_detect_coll("mário","mario", strength = 1L, locale = '')

[1] TRUE

Agora vamos manter o mesmo nível 1 e alterar a caixa:

stri_detect_coll("mário","Mário", strength = 1L, locale = '')

[1] FALSE

Como já disse acima, esse não é o comportamento esperado e me parece confuso, mas funciona.

Ignorar pontuação

Por fim, podemos olhar para a coluna cpf. Ela foi armazenada com os números separados por ponto ou hífen. Se quisermos que o usuário eventualmente forneça números para filtrar sem dígito, ajustamos o valor do argumento alternate_shifted para TRUE, de modo a ignorar qualquer pontuação. Vejamos:

df %>% 
  filter(stri_detect_coll(cpf,"43209775939", alternate_shifted = TRUE))

   nome            cpf codigo
1 Mário 432.097.759-39   g123

Colação no PostgreSQL

No PostgreSQL, podemos usar as mesmas regras, com a diferença que a sintaxe muda e me parece mais consistente que o R. Vale notar que, por padrão, o PostgreSQL usa colação determinística, ou seja, a comparação é por identidade da sequência de bites. Por exemplo, a \(\ne\) á. Colação não determinística, adicionada na versão 12 do PostgreSQL, considera alguns caracteres como iguais, mesmo quando eles têm sequências diferentes de bites. Por exemplo, a = á = A = Á.

A vantagem no uso de colação no PostgreSQL é que você pode armazenar os dados de uma forma e flexibilizar para o usuário como ele irá realizar buscas. Por exemplo, você pode armazenar o CNPJ sem pontuação e deixar que o usuário busque com ou sem pontos e vice-versa.

Basicamente, no PostgreSQL criamos uma colação e, ao criar ou alterar a coluna, indicamos qual colação será usada. Se não indicarmos, será adotada a do locale e determinística.

Inicialmente, vamos criar a colação para a coluna codigo. Se você consultar as opções de ajuste na página oficial da ICU, verá que para ordem numérica, usa-se o parâmetro kn-on.

CREATE COLLATION colacao_num (
provider = 'icu',
locale = 'und-u-kn-on'
deterministic = false
);

Primeiramente, demos o nome colacao_num, que será usado mais adiante para alterar a colação da coluna codigo. Depois definimos o provedor, vez que o PostgreSQL não admite somente a ICU. Em seguida, definimos o locale com a inclusão das regras de colação, und-u nenhuma linguagem específica e mantêm o locale padrão. A chave kn quando ligada on considera o valor numérico da sequência de números.

Se você ordenar a tabela pela coluna codigo, verá que a ordem não é aquela desejada:

SELECT * FROM df ORDER BY codigo;

   nome   |      cpf       | codigo
----------+----------------+--------
 Mário    | 432.097.759-39 | g123
 Vinícios | 828.483.096-08 | g257
 Flávia   | 017.372.652-92 | g27
 Angélica | 612.260.255-65 | g34
(4 rows)

Vamos alterar a colação da coluna codigo para aquela que acabamos de criar.


alter table df alter column codigo set data type text collate colacao_num;

O resultado sai como esperado:

SELECT * FROM df ORDER BY codigo;


   nome   |      cpf       | codigo
----------+----------------+--------
 Flávia   | 017.372.652-92 | g27
 Angélica | 612.260.255-65 | g34
 Mário    | 432.097.759-39 | g123
 Vinícios | 828.483.096-08 | g257
(4 rows)

Ignorar acentos e caixa

Vamos agora criar uma colação para ignorar acentos e caixa na coluna nome. Para tanto, basta você optar para level1 na chave ks:

CREATE COLLATION  colacao_nome (

provider = 'icu',
locale = 'und-u-ks-level1',
deterministic = 'false'

);

Altere a colação:

ALTER TABLE df ALTER COLUMN nome  SET DATA TYPE text COLLATE colacao_nome;

Agora vamos filtrar para ‘angelica’, ou seja, tudo em minúsculo e sem acento:

SELECT * FROM df WHERE nome = 'angelica';


   nome   |      cpf       | codigo
----------+----------------+--------
 Angélica | 612.260.255-65 | g34
(1 row)

Ignorar pontuação

Por fim, vamos ajustar para ignorar pontuação na coluna cpf. Para tanto, você precisa ajustar a chave ‘ka’ para ‘shifted’:

CREATE COLLATION colacao_pontuacao (
provider = 'icu',
locale = 'und-u-ka-shifted',
deterministic = 'false';

);

Alterando a coluna cpf para aceitar a nova colação criada:

ALTER TABLE df ALTER COLUMN cpf SET DATA TYPE text collate colacao_pontuacao;

Vamos filtrar para o cpf da Flávia sem incluir pontos:

SELECT * FROM df WHERE cpf = '01737265292';

E o resultado:

d <- "
  nome  |      cpf       | codigo
--------+----------------+--------
 Flávia | 017.372.652-92 | g27
(1 row)
"
cat(d)


  nome  |      cpf       | codigo
--------+----------------+--------
 Flávia | 017.372.652-92 | g27
(1 row)

Considerações finais

Colação é um recurso fantástico para realizar buscas e ordenar tabelas com bastante flexibilidade, sem ter de promover alterações nas colunas.

Colação no R e no PostgreSQL

Colação

Unicode e ICU

Dataframe

Colação no R

Ordem numérica

Acentos e Caixa

Ignorar pontuação

Colação no PostgreSQL

Ignorar acentos e caixa

Ignorar pontuação

Considerações finais