Como encontrar o próximo valor não-NULL em SQL

Você precisa encontrar o próximo nãoNULL valor em uma série cronológica, mas você não sabe como. Você pode fazer isso mesmo em SQL? Sim, você pode! Este artigo vai lhe mostrar o que fazer.

Se você trabalha com SQL, mais cedo ou mais tarde você confrontará os valores NULL. Ter NULLs em um banco de dados é quase inevitável. Entretanto, às vezes você quer evitá-los em seus relatórios. Isto é muitas vezes verdade quando você está analisando dados de séries temporais; os valores NULL significam que não há dados disponíveis. Nenhum dado disponível na série de dados geralmente significa que algum evento ocorreu ou não.

Então, como você encontra o próximo valor nãoNULL em uma série cronológica? Antes de responder como, vamos nos concentrar em quando - como em "Quando eu precisaria encontrar o próximo valor que não sejaNULL?". Aqui está um cenário que fornecerá uma resposta.

Cenário

Suponha que você esteja analisando dados para uma plataforma freelance chamada NoBoss que conecta empresas com freelancers. As empresas estão procurando bons freelancers; os freelancers estão procurando bons empregos (ou shows, como eles dizem na indústria). Você está trabalhando com a log tabela, que contém os dados dos freelancers, seus dados de login, e algumas atividades. Aqui estão as colunas da tabela:

• id - A identificação do registro de registro e a chave primária da tabela (PK).
• user_id - O ID do usuário.
• first_name - O primeiro nome do usuário.
• last_name - O sobrenome do usuário.
• login_start - A hora de início de sessão do usuário.
• login_end - A hora de término do login do usuário.
• job_id - O ID do trabalho ao qual o usuário se candidatou.
• job_name - O nome do emprego ao qual o usuário se candidatou.
• category_id - O ID da categoria do cargo.
• category_name - O nome da categoria do emprego.

Sua tarefa aqui é encontrar os IDs e nomes dos usuários. Além disso, você precisa obter os horários de início e fim de login dos usuários, juntamente com o emprego ao qual o usuário se candidatou. Finalmente, você também precisa de uma nova coluna chamada profile_category. A categoria do perfil do usuário é determinada pela categoria do primeiro emprego ao qual o usuário se candidata (ou seja, se o freelancer se candidata a um emprego de "Assistente Virtual", seu valor category_name é "Assistente Virtual"). Dessa forma, a plataforma NoBoss pode fornecer a seus usuários relatórios e estatísticas que os comparem com outros usuários dentro da mesma categoria.

Isto parece complicado? Talvez não, no início. Mas dê uma olhada nos dados da tabela log tabela e você verá porque sua tarefa não é simples. Preste atenção especial à coluna destacada.

Você vê o que torna isto tão difícil? Esta tabela registra cada login. Em outras palavras, esta tabela contém uma série cronológica, o que significa que pode haver registros de log onde o usuário não se candidatou a nenhum trabalho. Você precisa excluir todos os registros com valores NULL na coluna job_id. Esta é a parte mais fácil.

Você também precisa mostrar apenas a categoria do primeiro emprego. O usuário pode fazer o login e não se candidatar ao primeiro emprego durante meses. Isto significa que você precisará pular quem sabe - como - muitos registros com valores NULL antes de alcançar o registro nãoNULL que é igual ao primeiro emprego.

Lembre-se, para completar esta tarefa, você terá que encontrar todos os valores nãoNULL na coluna job_id e somente o primeiro valor nãoNULL da mesma coluna. Para resolver este problema, você precisará das funções da janela SQL; se você não estiver familiarizado com elas, verifique nosso curso Funções de Janela (Window Functions) em SQL .

Exemplo de solução: SQL Funções de Janela (Window Functions) em SQL

A utilização de funções de janela não é a única maneira de resolver este problema em SQL. É por isso que o seguinte código é apenas um exemplo. Entretanto, o uso das funções de janela é a maneira mais elegante de encontrar valores nãoNULL. Portanto, aqui está o código:

SELECT	user_id,
		first_name,
		last_name,
		login_start,
		login_end,
		job_name,
		FIRST_VALUE(category_name) OVER (PARTITION BY user_id ORDER BY job_id ASC) AS profile_category
FROM log
WHERE job_name IS NOT NULL;

O que este código faz? Primeiro ele seleciona várias colunas da tabela logNada de extravagante. A parte excitante vem do uso da função de janela FIRST_VALUE(). Esta função retorna o primeiro valor em um conjunto definido de dados. Neste caso, ela retornará o primeiro valor da coluna category_name.

Entretanto, não quero que a primeira categoria de trabalho já escolhida seja a categoria para cada usuário; o primeiro trabalho dos usuários é usado somente para aquele usuário em particular. Para conseguir isso, usei o PARTITION BY. Isto define a janela sobre a qual a função FIRST_VALUE() irá funcionar. Isto significa que ela vai encontrar o primeiro emprego do primeiro usuário; então ela vai se mudar para o usuário seguinte e encontrar seu primeiro emprego, e assim por diante.

Observe que esta operação é realizada sobre a coluna job_id em ordem ascendente. Por que isso acontece? Os IDs dos empregos são alocados sequencialmente aos empregos. Se o usuário encomendar os IDs do trabalho em ordem crescente, significa que o ID mais baixo estará no topo e os valores NULL estarão na parte inferior. Dessa forma, você evita receber um NULL como primeiro valor no resultado.

Finalmente, a cláusula WHERE exclui todos os logins onde o usuário não se candidatou a nenhum emprego.

Vamos verificar o código para ver se ele retorna o resultado desejado:

O código parece estar funcionando perfeitamente! Este é apenas um aspecto da análise de séries temporais em SQL. Você pode aprender mais lendo este artigo sobre análise de séries cron ológicas. Você não precisa conhecer todas as funções de janela e sua sintaxe de cor, especialmente se você não as usa com muita freqüência. Em tais situações, nosso SQL Funções de Janela (Window Functions) em SQL Cheat Sheet pode ser útil. Lá você encontrará todas as funções de janela, sua sintaxe e exemplos mostrando como elas funcionam.

Preste atenção ao adaptar esta consulta

A consulta acima fornece a estrutura geral para a solução e consegue encontrar o próximo valor nãoNULL. Você pode facilmente adaptar este código à série cronológica que você está analisando. Entretanto, tenha cuidado e preste atenção quando o fizer!

O mais importante é conhecer seus dados. Entenda se seu conjunto de dados contém os valores NULL e o que eles significam. No exemplo acima, foi necessário saber que o usuário pode estar logado e nunca se candidatar a qualquer trabalho. Para estes dados, ter os valores NULL não é um erro; são informações que o levam a certas conclusões.

Há outro exemplo da importância de conhecer seus dados. Eu sabia que os valores job_id são alocados sequencialmente, não aleatoriamente. Usei este conhecimento para ordenar os dados e assim eliminar NULLs do resultado. Também é útil saber como os valores NULL são tratados ao encomendar os dados. Dependendo da ordem, eles aparecerão ou como primeiro ou último valor na tabela.

É fácil pensar que você pode usar a função LAST_VALUE() ao invés de FIRST_VALUE() e obter os mesmos resultados simplesmente encomendando os dados de forma diferente. Vamos ver se funciona!

A lógica comum diz que eu só preciso pegar nossa consulta anterior e escrevê-la desta forma:

SELECT	user_id,
		first_name,
		last_name,
		login_start,
		login_end,
		job_name,
		LAST_VALUE(category_name) OVER (PARTITION BY user_id ORDER BY job_id DESC ) AS profile_category
FROM log
WHERE job_name IS NOT NULL;

A única diferença está nesta linha: LAST_VALUE(category_name) OVER (PARTITION BY user_id ORDER BY job_id DESC ) AS profile_category. Em vez do FIRST_VALUE(), eu usei a função LAST_VALUE(). A outra diferença é que a função será executada sobre job_id em ordem decrescente.

Vamos executar o código e analisar o resultado para o usuário Suzy Cinque:

Só deve haver "Análise de Dados" no profile_category. Por que isso acontece? É porque Suzy Cinque aplicou pela primeira vez no trabalho "Analista de dados para esfregar os dados". No entanto, esta coluna agora mostra também a categoria "Escrita". Dê uma olhada mais de perto. Os outros dois trabalhos aos quais Suzy Cinque se candidatou são "Revisor" e "Escritor Fantasma para biografia". Ambos pertencem à categoria de trabalho "Escrita".

O que este código lhe dá é apenas a categoria do emprego atual. Um revisor é um trabalho de redação. Um escritor fantasma também é um trabalho de redação. Analista de dados é, bem, um trabalho de análise de dados. Por que isto aconteceu? Porque o quadro de janela padrão é RANGE UNBOUNDED PRECEDING quando ORDER BY é usado. Isso significa que o LAST_VALUE() considerará apenas valores entre a primeira linha e a linha atual.

Não se desespere - há uma maneira de fazer isto funcionar! O truque é definir corretamente a moldura da janela:

SELECT	user_id,
		first_name,
		last_name,
		login_start,
		login_end,
		job_name,
		LAST_VALUE(category_name) OVER (PARTITION BY user_id ORDER BY job_id DESC ROWS BETWEEN UNBOUNDED PRECEDING
      AND UNBOUNDED FOLLOWING ) AS profile_category
FROM log
WHERE job_name IS NOT NULL;

Este código alterado agora considerará todos os valores entre a primeira e a última fila. Ele consegue isso através da seguinte cláusula: ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING. Confira o novo resultado para Suzy Cinque:

Agora o resultado está correto novamente. O primeiro trabalho ao qual Suzy Cinque se candidatou foi "Analista de dados para esfregar os dados"; é por isso que sua categoria de perfil será sempre "Análise de dados".

Encontrar o próximo valor nãoNULL é apenas um aspecto da análise de uma série temporal. Para se familiarizar mais com ambas as séries cronológicas e funções de janela, tente praticar com dados reais da COVID-19 como neste artigo.

Falando dos dados de tempo, talvez seja necessário calcular a duração de uma série cronológica. Não se preocupe, aqui está um artigo que ensina como usar as funções de janela para calcular a duração de uma série cronológica.

Quer mais ajuda para trabalhar com valores não-NULL?

NULL Este cenário com a plataforma NoBoss é apenas um exemplo de como encontrar os próximos valores não-NULL. Use este cenário e o código que expliquei como base para o aprendizado e a prática posterior. Nosso curso Funções de Janela (Window Functions) em SQL lhe dará mais estrutura e o ajudará a aprender sobre todas as outras funções da janela. Se você estiver interessado, aqui estão todas as informações sobre o curso Funções de Janela (Window Functions) em SQL que você precisará, fornecidas pelo nosso Chief Content Officer.

Pratique o que você aprendeu aqui, e boa sorte em encontrar seu caminho através dos valores nãoNULL em dados de séries temporais!