É fato: as pessoas cometem erros de digitação ou simplesmente usam grafias alternativas com frequência.

Seja qual for a causa, de um ponto de vista prático, strings diferentes, mas semelhantes, podem representar desafios para os desenvolvedores de software. Sua aplicação precisa ser capaz de lidar com esses casos extremos inevitáveis.

Veja nomes, por exemplo. Algumas pessoas me chamam de Peter em alguns lugares, de Pete em outros e assim por diante. Há outras variantes e meu nome pode ser representado Assim:

• "Pete Gleeson"
• "Peter J Gleeson"
• "Mr P Gleeson"
• "Gleeson, Peter"

Isso sem falar em grafias alternativas do meu sobrenome, como "Gleason". Todas essas são variações para apenas uma string – combiná-las entre si programaticamente pode não parecer algo óbvio.

Felizmente, existem soluções por aí.

O nome genérico para essas soluções é 'correspondência de strings por semelhança' (em inglês, fuzzy string matching). O "fuzzy" refere-se ao fato de que a solução não procura uma correspondência perfeita, posição por posição, ao comparar duas strings. Em vez disso, ela permite algum grau de diferença (ou "desvio").

Existem soluções disponíveis nas mais diversas linguagens de programação. Hoje, vamos explorar algumas opções disponíveis no Postgresql (ou 'Postgres') – uma variante do SQL, de código aberto, amplamente utilizada e com alguns recursos adicionais bastante úteis.

Configuração

Primeiro, vejamos se você tem o Postgres instalado em sua máquina.

Em seguida, criamos um banco de dados em seu próprio diretório (chame-o como quiser – para a demonstração, usei o nome 'fuzz-demo'). A partir da linha de comando, digite:

$ mkdir fuzz-demo && cd fuzz-demo
$ initdb .
$ pg_ctl -D . start
$ createdb fuzz-demo

Para a demonstração, usei uma tabela com detalhes sobre artistas no Museu de Arte Moderna de Nova York. Você pode fazer o download do arquivo artists.csv no Kaggle.

Em seguida, iniciamos o psql (um front-end com base no terminal para o Postgresql):

$ psql fuzz-demo

Agora, criamos uma tabela chamada artists:

CREATE TABLE artists (
	artist_id INT,
    name VARCHAR,
    nationality VARCHAR,
    gender VARCHAR,
    birth_year INT,
    death_year INT);

Por fim, usamos a função COPY do Postgresql para copiar o conteúdo do arquivo artists.csv para a tabela:

COPY artists FROM '~/Downloads/artists.csv' DELIMTER ',' CSV HEADER;

Se tudo estiver certo até o momento, você já pode começar a fazer consultas à tabela artists.

SELECT * FROM artists LIMIT 10;

Filtros curinga (wildcards)

Digamos que você se lembre do primeiro nome de uma artista chamada Bárbara, mas não consegue se lembrar bem do segundo nome dela. Começa com 'Hep...', mas você não tem certeza de como termina.

Aqui, você pode usar um filtro e o operador curinga do SQL, %. Esse símbolo representa qualquer número de caracteres não especificados.

SELECT
	* 
FROM artists
WHERE name LIKE 'Barbara%'
AND name LIKE '%Hep%';

A primeira parte do filtro encontra artistas cujo nome começa com 'Bárbara' e termina em qualquer combinação de caracteres.

A segunda parte do filtro encontra artistas cujo nome pode começar e terminar com qualquer combinação de caracteres, mas deve conter as letras 'Hep', nessa ordem.

O que você faria, no entanto, se não lembrasse como soletrar qualquer um dos nomes? Os filtros e os curingas têm um limite.

Usando as trigramas

Felizmente, o Postgres tem uma extensão útil com o nome e tanto: pg_trgm. Você pode habilitá-la a partir do psql usando o comando abaixo:

CREATE EXTENSION pg_trgm;

Essa extensão traz algumas funções úteis para correspondência de strings semelhantes. O princípio subjacente é o do uso de trigramas (que, sim, parecem com algo saído de Harry Potter).

As trigramas são formadas pela quebra de uma string em grupos de três letras consecutivas. Por exemplo, a string "hello" seria representada pelo seguinte conjunto de trigramas:

• " h", " he", "hel", "ell", "llo", "lo "

Ao comparar a semelhança do conjunto de trigramas entre duas strings, é possível estimar se elas são semelhantes em uma escala entre 0 e 1. Isso permite a correspondência por similaridade (em inglês, fuzzy matching), definindo um limite de similaridade acima do qual as strings são consideradas correspondentes.

SELECT
	*
FROM artists
WHERE SIMILARITY(name,'Claud Monay') > 0.4 ;

Talvez você queira ver as cinco melhores correspondências:

SELECT 
	*
FROM artists
ORDER BY SIMILARITY(name,'Lee Casner') DESC
LIMIT 5;

O limite padrão é 0.3. Você pode usar o operador % nesse caso como uma abreviação para nomes correspondentes por semelhança comparados a uma possível correspondência correta:

SELECT
	*
FROM artists
WHERE name % 'Andrey Deran';

Talvez você só tenha uma ideia de uma parte do nome. O operador % permite comparar com elementos de um array para que você possa fazer a correspondência com qualquer parte do nome. A próxima consulta usa a função STRING_TO_ARRAY do Postgres para dividir os nomes completos dos artistas em arrays de nomes separados.

SELECT
	*
FROM artists
WHERE 'Cadinsky' % ANY(STRING_TO_ARRAY(name,' '));

Algoritmos fonéticos

Outra abordagem para correspondência de strings similares vem de um grupo de algoritmos chamados algoritmos fonéticos.

Esses são algoritmos que usam conjuntos de regras para representar uma string usando um código curto. O código contém as principais informações sobre como a string deve soar se lida em voz alta. Ao comparar esses códigos curtos, é possível combinar strings que são escritas de modo diferente, mas soam iguais.

O Postgres vem com uma extensão que permite que você faça uso de alguns desses algoritmos. Você pode habilitá-la com o seguinte comando:

CREATE EXTENSION fuzzystrmatch;

Um exemplo é um algoritmo chamado Soundex. Suas origens remontam a mais de 100 anos – foi patenteado pela primeira vez em 1918 e foi usado no século 20 para analisar dados do censo dos EUA.

O Soundex funciona convertendo strings em códigos de quatro letras que descrevem como elas soam. Por exemplo, as representações do Soundex de 'flor' (em inglês, flower) e 'farinha' (em inglês, flour) são ambas F460.

A consulta abaixo encontra o registro que soa como o nome 'Damian Hurst'.

SELECT
	*
FROM artists
WHERE nationality IN ('American', 'British')
AND SOUNDEX(name) = SOUNDEX('Damian Hurst');

Outro algoritmo conhecido é o do metafone. Ele funciona de maneira semelhante ao Soundex, na medida em que converte strings em uma representação de código usando um conjunto de regras.

O algoritmo do metafone retornará códigos de diferentes comprimentos (ao contrário do Soundex, que sempre retorna quatro caracteres). Você pode passar um argumento para a função METAPHONE indicando o código de comprimento máximo que você deseja que ele retorne.

SELECT
	artist_id,
    name,
    METAPHONE(name,10)
FROM artists
WHERE nationality = 'American'
LIMIT 5;

Como o metafone e o Soundex retornam strings como resultado, você pode usá-las em outras funções de correspondência de strings similares. Essa abordagem combinada pode produzir resultados poderosos. O exemplo abaixo encontra as cinco correspondências mais próximas para o nome Si Tomlee.

SELECT
	*
FROM artists
WHERE nationality = 'American'
ORDER BY SIMILARITY(
	METAPHONE(name,10),
    METAPHONE('Si Tomlee',10)
    ) DESC
LIMIT 5;

Aqui, uma abordagem apenas de trigrama não teria ajudado muito, já que há pouca sobreposição entre 'Cy Twombly' e 'Si Tomlee'. Na verdade, eles têm apenas uma pontuação de SIMILARITY (em português, similaridade) de 0,05, embora soem semelhantes quando lidos em voz alta (em inglês).

Devido às suas origens históricas, nenhum desses algoritmos funciona bem com nomes ou palavras de origem não inglesa. No entanto, existem versões mais focadas para o mercado internacional.

Um exemplo é o algoritmo de metafone duplo. Ele usa um conjunto mais sofisticado de regras para produzir metafones, podendo fornecer codificações alternativas para strings de origem inglesa e não inglesa.

Como exemplo, veja a consulta abaixo. Ele compara os resultados de metafone duplo para diferentes grafias do artista espanhol Joan Miró:

SELECT
	'Joan Miró' AS name, 
    DMETAPHONE('Joan Miró'),
    DMETAPHONE_ALT('Joan Miró')
UNION SELECT
	'Juan Mero' AS name,
    DMETAPHONE('Juan Mero'),
    DMETAPHONE_ALT('Juan Mero');

Indo mais longe

Finalmente, outra abordagem para a correspondência de strings similares no Postgres é calcular a "distância" entre as strings. Existem várias maneiras de se fazer isso. O Postgres fornece uma funcionalidade para calcular a distância de Levenshtein.

Em um nível mais alto, a distância de Levenshtein entre duas strings é o número mínimo de edições necessárias para transformar uma string na outra. As edições são consideradas no nível dos caracteres e podem incluir:

• substituições,
• exclusões e
• inserções

Por exemplo, a distância de Levenshtein entre as palavras "bigger" e "better" (maior e melhor em português, respectivamente) é de 3, porque você pode transformar "bigger" em "better" substituindo "igg" por "ett".

Já a distância de Levenshtein entre 'biggest' e 'best' ("o maior" e "o melhor" em português, respectivamente) também é 3, porque você pode transformar 'biggest' em 'best' excluindo as letras 'igg'.

Veja abaixo uma consulta que encontra os nomes de artistas com as menores distâncias de Levenshtein para o nome 'Freda Kallo'.

SELECT
	*,
    LEVENSHTEIN(name, 'Freda Kallo')
FROM artists
ORDER BY LEVENSHTEIN(name, 'Freda Kallo') ASC
LIMIT 5

Obrigado pela leitura!

Espero que esta visão geral da correspondência de strings similares no Postgresql tenha dado a você novos sugestões e ideias para seu próximo projeto.

É claro que existem outros métodos para correspondência de strings similares não abordados aqui, incluindo em outras linguagens de programação.

Por exemplo, ao usar o Python, dê uma olhada no pacote fuzzywuzzy. Se preferir a linguagem R, você pode usar a função agrep() integrada ou testar o pacote stringdist.

O agendamento é um recurso que permite automatizar tarefas para que você não precise fazer isso em tempo real.

Neste artigo, veremos como agendar uma tarefa (ou trabalho) no PostgreSQL. Usaremos o pgAgent, que é um agente de agendamento de trabalhos para o PostgreSQL. Se você não sabe o que é um agente de banco de dados, eu já explico.

Esses agentes são processos que rodam em segundo plano. O papel deles é executar os trabalhos/comandos que você deixa registrado neles. Nesses trabalhos, você determina a periodicidade com que quer executar esses comandos.

Agora, vamos pôr a mão na massa...

Como instalar o PostgreSQL e o Stack Builder

Você pode instalar o pgAgent com o Stack Builder.

Instale o PostreSQL a partir do site oficial. Ele fará o download do Stack Builder junto com o instalador do PostgreSQL.

Caso você já tenha o PostgreSQL instalado, é só fazer o download dele e executar também a instalação do Stack Builder.

O Stack Builder é executado assim que a instalação do PostgreSQL é concluída. Estou usando aqui o PostgreSQL14 e o pgAdmin4.

Como instalar o pgAgent

Na instalação do PostgreSQL, em uma das telas, mostrará as opções de extensões que você instalará. A opção do Stack Builder já vem selecionada. Caso não esteja, no entanto, selecione-a.

Após a instalação do PostgreSQL, quando você executa o Stack Builder, ele primeiro abre um assistente de boas-vindas.

Se você tiver várias versões do PostgreSQL instaladas, escolha uma para instalar o pgAgent.

Você encontrará o pgAgent na categoria Add-ons, tools and utilities (Add-ons, ferramentas e utilitários). Marque a caixa de seleção dele para instalá-lo.

Em seguida, ele solicitará que você escolha um diretório onde deseja instalar o pgAgent.

O Stack Builder abrirá o assistente de instalação do pgAgent.

Aqui, você escolhe se deseja instalá-lo em Upgrade Mode (Modo de atualização). Caso você já tenha scripts no seu PostgreSQL, e se você não quiser alterá-los automaticamente durante a atualização, marque a caixa.

Na tela PostgreSQL Installation Details (Detalhes da instalação do PostgreSQL), forneça o usuário e a senha que você digitou quando instalou o PostgreSQL.

Se você inserir os dados incorretamente, aparecerá uma janela com erro de conexão. Portanto, guarde sempre esses dados de acesso, tanto do PostgreSQL quanto do banco de dados.

OBSERVAÇÃO: Você fará o login no PostgreSQL com o usuário e a senha que você fornece nesse estágio para poder visualizar as tarefas do pgAgent.

Depois de adicionar esses dados, a instalação começa:

Demora alguns segundos para terminar.

Clique no botão Finish (Finalizar).

Depois de instalado, o Stack Builder mostrará uma tela de instalação concluída. Ali, você verá instruções sobre como instalar e desinstalar utilitários.

Se o Stack Builder estiver instalado, basta executá-lo para instalar outros utilitários. Para desinstalá-los, use o applet Add/Remove Programs (Adicionar/Remover Programas do Painel de Controle do seu sistema).

Com tudo instalado, abra o programa pgAdmin. Ele pedirá a senha, que você inseriu quando o instalou, para abrir o programa.

Os trabalhos do pgAgent estarão visíveis para você na árvore de navegação no lado esquerdo do painel.

Acima, você pode ver uma visão ampliada da árvore do navegação.

Como criar um trabalho no pgAgent

Para criar um novo trabalho, clique com o botão direito do mouse no botão pgAgent Jobs e clique em Create (Criar).

Você verá um menu, e lá é só clicar em Create > pgAgent Job (Criar > Tarefa do pgAgent).

A caixa de diálogo criar do pgAgent possui quatro abas.

A primeira é a guia General. Aqui você insere o nome do trabalho e seleciona uma categoria (Job class).

Essa categoria é apenas para fins de categorização/organização interna sua – e não afeta a execução do trabalho. Você pode selecionar uma com base na função do trabalho. Como eu quero exportar os dados para um CSV, escolhi a categoria Data Export (Exportação de dados).

Em seguida, clicamos na aba Steps (etapas). No canto superior direito da aba, haverá um sinal de +. Clique nele para adicionar uma nova linha de etapa.

Quando você clica em Editar a linha (ícone de um lápis), há duas seções: General e Code.

Na seção General:

1. Adicione o nome da etapa.
2. Em seguida, você habilita ou desabilita a etapa. Seu trabalho será executado somente se a etapa estiver habilitada.
3. Dependendo do fato de seu trabalho ser local ou remoto, você pode escolher o tipo de conexão. Vou escolher uma conexão remota para exemplificar uma string de conexão.
4. Uma conexão remota permite que você adicione manualmente a string de conexão. A sintaxe dela deve ser como na string de conexão libq (documentação em inglês). Vou adicionar meus detalhes de conexão nesse formato: host=localhost port=5432 dbname=postgres
5. Na caixa de seleção On Error, você pode escolher o que deve acontecer caso ocorra um erro. Eu selecionei para o trabalho falhar.
6. Finalmente, você pode adicionar um comentário sobre a essa etapa. Em seguida, salve as alterações.

Ao lado, vemos a seção Code.

Como quero exportar os dados de uma visualização, chamarei a visualização e pedirei para exportar o arquivo. O código será:
COPY (select * from acc_view) TO E'C:\\test-data\\try.csv';

Vou salvar as alterações depois de adicionar o comando.

Agora, estamos prontos para agendar esse trabalho. Na aba Schedules, adicionamos a hora da data de início e a hora da data de término do trabalho para iniciar e terminar.

SQL é a última aba. Ela mostra o código gerado pela Interface Gráfica (GUI), pois você também pode criar scripts por código. Se você desejar editar esse código, mostrarei como fazer depois.

Como visualizar os trabalhos criados no pgAgent

Assim que um novo trabalho for criado, ele será exibido em Steps do pgAgent na árvore de navegação.

Seus agendamentos (schedules) e etapas (steps) serão exibidos quando você estender o trabalho agendado.

Para ver se o trabalho foi executado (se falhou ou se foi bem-sucedido), selecione o trabalho pelo nome e clique na guia Statistics (Estatísticas) no painel. Aqui, você pode ver o número de vezes que o trabalho foi executado, hora de início e término, seu status e o id. Na coluna Status, a sigla s significa sucesso e f significa falha.

Para depurar o motivo de um trabalho ter falhado, basta clicar no nome da etapa em Steps (Etapas), na árvore de navegação, e clicar na aba de estatísticas no painel. Na coluna Output (Saída), você pode ver o motivo de o trabalho ter falhado.

No meu caso, não foi possível acessar o diretório para o qual eu estava tentando copiar os dados. Depois de alterar o caminho, meu trabalho foi executado com sucesso (observe a primeira linha).

Como editar trabalhos no pgAgent

Para editar um trabalho no pgAgent, selecione o trabalho que deseja alterar e clique na guia Properties (Propriedades) no painel.

Clique no ícone de lápis no canto superior esquerdo, ele abrirá um assistente onde você poderá editar todos os detalhes.

Conclusão

Nem sempre é viável criar agendadores em seu código. Quando fazer isso é uma opção, ela pode ser muito útil.

O agendamento de trabalho, juntamente com a exportação de dados no formato CSV, é um recurso poderoso do PostgreSQL. Vou tentar explicar como criar um trabalho dinamicamente em um próximo tutorial. Ótimo aprendizado para você!