Limpeza de dados em SQL

A limpeza de dados é uma parte importante de qualquer análise de dados. Aqui discutiremos técnicas que você pode usar para fazer a limpeza de dados em SQL.

Acho quase impossível focar no trabalho quando minha mesa está uma bagunça. Se ela estiver cheia de papel, canecas de café ou brinquedos aleatórios que minha filha de alguma forma entrou no meu escritório, não há nenhuma chance de conseguir fazer nada até que minha mesa esteja de volta em ordem. Por alguma razão, é como se a bagunça em minha mesa tivesse de alguma forma entrado em minha mente.

Esta mesma idéia é pertinente à limpeza de dados. Muitas vezes, passei horas em uma análise e tirei minhas conclusões apenas para saber de uma inconsistência nos dados que nega todo o meu relatório. Como analistas, tipicamente, nós nos lançamos diretamente na análise de dados sem primeiro tomar o tempo necessário para garantir que nossos dados estejam limpos. Isto pode levar a muitas horas de desperdício de tempo - ou pior ainda, relatórios imprecisos.

O que é limpeza de dados?

O processo de limpeza de dados (também chamado de limpeza de dados) envolve a identificação de quaisquer imprecisões em um conjunto de dados e, em seguida, a sua correção. É o primeiro passo em qualquer análise e inclui apagar dados, atualizar dados e encontrar inconsistências ou coisas que simplesmente não fazem sentido.

Você pode aprender todas as características SQL necessárias para limpar dados em SQL em nosso SQL de A a Z pista. A pista contém 7 cursos SQL interativos que lhe ensinarão SQL completo, desde o básico até tópicos intermediários e até conceitos avançados de SQL como funções de janela e consultas recursivas. É o conjunto mais completo de cursos de SQL disponível na Internet.

Técnicas de Limpeza de Dados

Agora que você tem a idéia, vamos em frente e dar uma olhada nas técnicas SQL que você pode usar para limpar dados. Para cada exemplo, estaremos utilizando a tabela de empresas mostrada abaixo. Ela mostra informações sobre várias empresas:

Como e quando apagar dados

s vezes você encontrará cenários nos quais você precisará remover dados de seu conjunto de dados. Isto pode ser porque os dados não são pertinentes ao que você está analisando ou são duplicados ou imprecisos. Nos próximos exemplos, exploraremos estes diferentes cenários e como abordá-los.

Exemplo 1: Eliminação de dados duplicados

A primeira coisa que faremos é procurar por quaisquer dados que precisem ser apagados. Isto pode ser devido a duplicatas ou porque os dados não são relevantes. Nesta tabela, podemos ver rapidamente que a fila para a empresa Toughtam está duplicada. Isto não será tão fácil de identificar em um grande conjunto de dados. Antes de irmos e apagarmos essa linha, vamos discutir como a encontrar.

Neste conjunto de dados, cada empresa deve ter apenas uma fila, então vamos usar as cláusulas GROUP BY e HAVING para identificar nomes duplicados. Esta consulta vai contar o número de vezes que cada nome existe no banco de dados usando GROUP BY. Em seguida, utiliza a cláusula HAVING para filtrar os resultados apenas para aqueles nomes que existem mais de uma vez.

SELECT name, 
	 COUNT(name) as count
FROM companies
GROUP BY name
HAVING(count > 1)

Esta consulta retornará o seguinte resultado:

Ótimo! Agora sabemos que a empresa chamada Toughtam está duplicada, mas como eliminar uma das linhas? Vamos usar uma combinação de ROW_NUMBER() e DELETE. Primeiro, vamos adicionar um número de linha para cada linha com base na coluna do nome:

SELECT name, 
	 ROW_NUMBER() OVER(PARTITION BY name) AS rn
FROM companies

O que fizemos foi adicionar uma nova coluna que mostra o número da linha para cada nome. Como você pode ver, agora há um 1 e um 2 para as linhas do Toughtam. Agora vamos executar uma declaração DELETE para remover qualquer linha em que a coluna rn seja maior que 1.

DELETE 
FROM (
SELECT name, 
	 	ROW_NUMBER() OVER(PARTITION BY name) AS rn
FROM companies
) 
WHERE rn > 1

Agora nosso conjunto de dados se parece com isto:

Exemplo 2: Encomenda de dados antes da exclusão

Neste exemplo, as linhas para a Toughtam são as mesmas (com exceção de id), portanto, não estamos ordenando as linhas por nada além de como elas aparecem no banco de dados. Muitas vezes, você verá que as linhas estão duplicadas, mas talvez os campos não sejam idênticos. Se este for o caso, você pode adicionar uma cláusula ORDER BY após o PARTITION BY.

Por exemplo, suponha que os dados tenham este aspecto:

Parece que este registro foi atualizado para esta empresa em 1/20/2023 e a contagem dos funcionários aumentou. Se quiséssemos manter o registro mais recente, correríamos:

DELETE 
FROM (
SELECT name, 
	 	ROW_NUMBER() OVER(PARTITION BY name ORDER BY created DESC) AS rn
FROM companies
) 
WHERE rn > 1

Como você pode ver, estamos agora ordenando a declaração ROW_NUMBER() pelo campo created em ordem decrescente, puxando primeiro o registro criado mais recentemente. Em seguida, apagamos os registros que vêm após o primeiro registro.

Para mais informações sobre como encontrar valores duplicados em SQL, consulte nosso artigo Como encontrar valores duplicados em SQL.

Exemplo 3: Removendo valores NULL

Agora vamos dar uma olhada no NULLs. NULL indica um valor em falta; você pode ler mais sobre eles aqui. Dependendo das implicações dos valores NULL em seus dados, você pode remover essas linhas ou atualizá-las. Em nosso exemplo, vemos dois valores NULL. Uma linha tem um valor NULL para a indústria e a outra para o year_founded. Vamos tratar cada uma de forma diferente.

Uma empresa deve ter um ano de fundação. Vamos usar DELETE para remover essa linha onde ela está faltando, uma vez que parece ser um dado ruim.

SELECT
FROM companies 
WHERE year_founded IS NULL 

Na consulta acima, estamos usando a cláusula IS NULL. Esta cláusula está olhando para a coluna year_founded e retornando quaisquer linhas onde IS NULL. Uma vez verificado que esta é a linha que queremos remover, podemos excluí-la executando-a:

DELETE
FROM companies 
WHERE year_founded IS NULL 

Neste ponto, estamos prontos para apagar os dados ruins e estamos prontos para passar para a declaração UPDATE. Vamos usar isto para corrigir nosso outro valor NULL.

Como atualizar os dados

A declaração UPDATE é utilizada para modificar os dados existentes. Você usaria esta técnica de limpeza de dados ao corrigir dados inexatos ou para formatar seus dados (tornando-os mais legíveis). Ao longo dos próximos exemplos, vamos caminhar por estes tipos de cenários para entender como manipular os dados usando UPDATE.

Exemplo 1: Colocar um Rótulo Significativo para Valores NULL

Como já vimos, há uma empresa que tem um valor NULL para a indústria. Estamos bem com isso porque, em nossa situação hipotética, sabemos que nem todas as indústrias estão disponíveis em nosso banco de dados. O que precisamos fazer nesta situação é atualizar o NULL para ser "Outro". Outra opção seria substituir NULLs por "NA" ou "Não Aplicável".

Primeiro, vamos usar SELECT para puxar a fila com a indústria NULL:

SELECT *
FROM companies 
WHERE industry IS NULL 

Agora que sabemos que puxamos a fileira correta, podemos UPDATE a coluna da indústria. Vamos fazer isso correndo:

UPDATE companies 
SET industry = ‘Other’
WHERE industry IS NULL 

Ao usar UPDATE, a primeira coisa que precisamos fazer é identificar a tabela que queremos modificar. Em nosso exemplo, esta tabela é companies. Em seguida, precisamos dizer para qual coluna estamos atualizando e para o que estamos atualizando. Identificamos a coluna usando SET [column name]. Em seguida, definimos o que queremos mudar a coluna para usar = [ valor ]. A cláusula WHERE é a mesma como se tivéssemos que escrever uma declaração em SELECT. Só queremos mudar o setor para 'Outro' se o setor IS NULL.

Exemplo 2: Fixar a Capitalização de Valores

Neste ponto nossos dados estão com melhor aparência, mas a coluna state poderia usar um pouco de limpeza. Alguns dos valores estão em maiúsculas e outros em minúsculas. Normalmente, a abreviatura de estado é maiúscula, portanto vamos atualizar todos os valores em minúsculas para maiúsculas.

Como queremos garantir que todos os valores de estado estejam em letras maiúsculas, podemos correr:

UPDATE companies 
SET state = UPPER(state)

Quando você embrulha a declaração UPPER() em torno de um nome de coluna, você está mudando todas as letras para maiúsculas. (Com a declaração LOWER(), você faz o contrário - mude todas as letras para minúsculas). Agora nossa tabela é parecida com esta:

Exemplo 3: Corrigir erros lógicos

Uma última coisa que você vai querer procurar em seu conjunto de dados são os erros lógicos. Em nossos dados, vemos que uma empresa tem um valor year_founded de 2032. Bem, isso simplesmente não é possível, já que uma empresa não pode ser fundada no futuro. Podemos identificar registros datados do futuro, correndo:

SELECT *
FROM companies 
WHERE year_founded > CURRENT_TIMESTAMP()

CURRENT_TIMESTAMP() retorna a data e a hora atuais. Na declaração acima, estamos puxando todos os registros onde year_founded está após o tempo em que a consulta é feita. Também é possível codificar uma data, tal como a data de hoje.

Depois de uma pequena pesquisa, vemos que isto é uma gralha e que a empresa foi fundada em 2012, portanto, vamos em frente e modificaremos esse registro:

UPDATE companies
SET year_founded = 2012
FROM companies 
WHERE id = 3

Neste cenário, especificamos uma identificação específica na cláusula WHERE. Isto porque é muito provável que você não queira atualizar todos os valores year_founded para ser o mesmo ano para qualquer empresa que teve um ano de fundação no futuro. Conhecemos o ano de fundação para esta empresa específica, portanto, só atualizaremos esse registro.

Lembre-se sempre de limpar seus dados

Nossos dados agora parecem muito mais claros do que o conjunto de dados original. A limpeza dos dados, embora entediante, é uma parte imperativa do processo de análise de dados. Nunca suponha que os dados com os quais você está trabalhando estejam limpos. Explore os dados procurando por duplicatas, NULLs, e quaisquer falácias lógicas.

Agora você entende várias técnicas SQL que pode usar para modificar seus dados, incluindo DELETE e UPDATE. Um grande próximo passo é dar o SQL de A a Z pista. Ela contém 7 cursos SQL interativos, incluindo um curso inteiro nas cláusulas DELETE, UPDATE, e INSERT. É o conjunto mais completo de cursos SQL disponíveis na Internet. Esta faixa ajudará a reforçar tudo o que você aprendeu neste artigo e lhe dará oportunidades para uma prática adicional. Feliz limpeza de dados!