Visão geral
- Conheça as funções da janela SQL
- Entenda o que faltam funções agregadas e por que precisamos de funções de janela no SQL
Introdução
Os dados estão proliferando a uma taxa surpreendente, crescendo para 44 zetabytes e 2020! E nem é preciso dizer que a tecnologia para lidar com quantidades tão gigantescas de dados também está mudando em uma taxa proporcional..
Hoje temos uma grande variedade de ferramentas como Hive e Spark para lidar com Big Data. Mas, mesmo diferindo em alguns aspectos, ainda empregam os fundamentos do SQL, o que torna muito fácil para pessoas de todas as esferas da vida manipular Big Data com uma brisa. Embora ainda falhemos em certos aspectos do SQL. Então, neste artigo, Vou falar de um desses aspectos em particular: funções da janela.
Está certo! Existem funções de janela no SQL, não é brincadeira! E dada a expressão chocada em seu rosto, este item parece ser a necessidade do momento. Mesmo eu não estava muito ciente dessas funções até recentemente, o que mostra como essas funções são subestimadas.
Mas espere até o final deste artigo porque o Window Functions realmente vai te surpreender com a simplicidade com que eles resolvem problemas tão complexos.. E sim, engenheiros de dados, cientistas de dados, analistas de dados e todos os outros que flertam com dados devem dar a essas funções o devido crédito.
Antes de seguir em frente, Sugiro que você se familiarize com as funções básicas do SQL revisando este artigo: 24 funções SQL comumente usadas. E se você estiver interessado em aprender SQL em um formato de curso, confira nosso curso: Linguagem de consulta estruturada (SQL) para ciência de dados.
Tabela de conteúdo
- Apresentamos o conjunto de dados
- Onde as funções adicionadas são atrasadas?
- O que são funções de janela no SQL?
- Entendendo as funções da janela SQL – Cláusula Over
- Janelas com PARTITION BY
- Organizar linhas dentro de partições
- funções da janela
- número da linha
- Rango vs Dense_Rank
- Nth_Value
- Ntil
- liderar e atrasar
- arquivo de código SQL
Apresentamos o conjunto de dados
Antes de continuar, deixe-me apresentar o conjunto de dados fictício em que trabalharemos neste artigo. Suponha que haja uma empresa que mantém registros de nomes, cargo e salário do funcionário da seguinte forma:
Usaremos este conjunto de dados de exemplo para entender os conceitos deste artigo. Muito bem, Vamos começar!
Onde as funções adicionadas são atrasadas?
Suponha que você queira determinar o salário total de todos os funcionários da empresa. Como você faria? Você pode simplesmente usar a função agregada SUM () na coluna SALÁRIO.
Fácil.
Que tal determinar o salário total dos funcionários por categoria de trabalho? Use a última consulta e adicione uma cláusula GROUP BY na coluna JOB.
Excelente!
Agora deixe-me fazer mais duas perguntas:
- Mostre o salário total e o salário total por categoria de trabalho junto com o valor de cada linha.
- Organize o salário em ordem decrescente dentro de cada categoria de trabalho.
você entendeu? Não? Provavelmente?
Estes definitivamente não foram tão fáceis quanto os primeiros que você conseguiu obter instantaneamente. Mas porque?
Nós vamos, Se você pensar sobre isso, as consultas anteriores exigiam funções agregadas simples para resolver o problema. As funções de agregação SQL nos dão apenas um único valor para o grupo de linhas agregadas (pense na primeira consulta que escrevemos).
Mas as últimas consultas não podem ser resolvidas simplesmente usando essas funções. Essas consultas querem que mantenhamos a identidade original das linhas individuais, algo que as funções adicionadas não abordam. Portanto, para resolver esses tipos de consultas, precisamos de diferentes tipos de funções: funções da janela.
O que são funções de janela no SQL?
funções da janela realizar cálculos em um conjunto de linhas relacionadas entre si. Mas, ao contrário das funções agregadas, funções de janela não recolhem linhas de resultado em um único valor. Em vez de, todas as linhas mantêm sua identidade original e o resultado calculado é retornado para cada linha.
Entendendo as funções da janela SQL – Cláusula Over
Por exemplo, se você exibir o salário total do funcionário junto com o valor de cada linha, ficaria assim:
a SOBRE cláusula significa uma janela de linhas na qual uma função de janela é aplicada. Pode ser usado com funções adicionais, como usamos aqui com a função SUM, tornando-se assim uma função de janela. Ou também pode ser usado com funções não agregadas que são usadas apenas como funções de janela (aprenderemos mais sobre eles em seções posteriores).
Então, a sintaxe para definir uma função de janela simples que produz o mesmo valor para todas as linhas é a seguinte:
window_function_name () SOBRE ()
Mas, que tal aplicarmos a função de janela a linhas específicas em vez de toda a tabela?
Janelas com PARTITION BY
a PARTIÇÃO POR A cláusula é usada em conjunto com a cláusula OVER. Divida as linhas em diferentes partições. A seguir, a função de janela atua nessas partições.
Por exemplo, para exibir o salário total por categoria de trabalho para todas as linhas, teríamos que modificar nossa consulta SQL original da seguinte forma:
Como você pode ver, a total_trabalho_salário A coluna representa a soma das vendas para essa categoria de trabalho específica e não para a tabela inteira.
Então, a sintaxe para definir a função de janela para particionamento de linha é a seguinte:
window_function_name () SOBRE ()
Agora, que tal organizar as linhas dentro de cada partição?
Organizar linhas dentro de partições
Sabemos que para organizar linhas em uma tabela, podemos usar la cláusula ORDER BY. Então, para organizar linhas dentro de cada partição, temos que modificar a cláusula OVER com a cláusula ORDER BY.
Aqui, as linhas foram divididas de acordo com sua categoria de trabalho, conforme indicado na coluna TRABALHO. À medida que rola para baixo, Você notará que a coluna SALARY foi classificada em ordem decrescente e a ordenado_trabalho_salário A coluna representa o total acumulado da categoria de trabalho (comece de novo após cada partição).
Então, a sintaxe para definir a função de janela para particionar linhas e organizá-las em ordem é a seguinte:
window_function_name () SOBRE ( )
funções da janela
Agora que sabemos como definir funções de janela usando a cláusula OVER e algumas de suas versões modificadas, Podemos finalmente passar a trabalhar com funções de janela!!
1. Número_linha
As vezes, seu conjunto de dados pode não ter uma coluna que descreva a ordem sequencial das linhas, como é o caso do nosso conjunto de dados. Nesse caso, podemos fazer uso NÚMERO DA LINHA() função de janela. Atribuir um número sequencial exclusivo a cada linha na tabela.
Observe que a numeração começa de 1. O que mais, para evitar qualquer conflito com a palavra-chave MySQL para a função, Coloquei o nome da coluna entre aspas.
Mas, uma vez que é uma função de janela, também podemos limitá-lo a partições e depois ordenar essas partições.
Aqui, dividimos as linhas na coluna JOB e as ordenamos de acordo com o SALÁRIO do funcionário. Observe como a numeração é reiniciada sempre que uma nova partição é iniciada.
Mas suponha que queremos classificar os funcionários com base em seus salários.
2. Rank vs Dense_Rank
a ALCANCE() função de janela, como o nome sugere, classificar as linhas dentro de sua partição com base na condição dada.
Observe a parte destacada. No caso de ROW_NUMBER (), temos um número sequencial. Por outro lado, no caso de RANK (), temos o mesmo intervalo para linhas com o mesmo valor.
Mas aqui está um problema. Mesmo que as linhas com o mesmo valor sejam atribuídas à mesma classificação, intervalo subsequente pula o intervalo ausente. Isso não nos daria os resultados desejados se devolvêssemos valores "top N distintos" de uma tabela. Portanto, temos uma função diferente para resolver este problema.
a DENSE_RANK () A função é semelhante a RANK () exceto por uma diferença, não pule nenhum intervalo ao classificar linhas.
Aqui, todos os intervalos são distintos e aumentam sequencialmente dentro de cada partição. Comparado com a função RANK (), nenhum intervalo dentro de uma partição foi ignorado.
3. Nth_Value
Se você deseja recuperar o enésimo valor de um quadro de janela para uma expressão, você pode usar a função de janela NTH_VALUE (expressão, N).
Por exemplo, para recuperar o terceiro maior salário em cada categoria de TRABALHO, podemos dividir as linhas de acordo com a coluna WORK, em seguida, ordene as linhas dentro das partições com base no salário decrescente e, Finalmente, use a função NTH_VALUE para recuperar o valor. O comando será o seguinte:
Você deve ter notado algo diferente após a cláusula Order By. Esse é o Cláusula marco. Determina o subconjunto da partição (o marco) que será usado pela função de janela para calcular o valor da linha atual.
Aqui, eu mencionei que todas as linhas antes e depois de uma linha atual serão consideradas dentro do quadro ao aplicar a função de janela. Mas, por que eu usei a cláusula framework aqui e não com outras funções? Isso ocorre porque as outras funções da janela funcionam em toda a partição, mesmo que seja fornecida uma cláusula-quadro. Pero solo NTH_VALUE () pode trabalhar em quadros dentro de uma partição.
Agora suponha que você queira gerar o primeiro valor de cada partição. Embora haja uma FIRST_VALUE () função também, Vou usar o NTH_VALUE para o mesmo.
Do mesmo modo, também temos um LAST_VALUE () Função. Mas vou determinar o último valor dentro de cada partição como acima, embora usando ordem decrescente de linhas.
4. Ntil
As vezes, você pode querer ordenar as linhas dentro da partição em um certo número de grupos. Isso é útil quando você deseja determinar o percentil, quartil, etc. em que uma determinada linha é encontrada. a NTIL () A função é usada para tais fins. Retorna o número do grupo para cada uma das linhas na partição.
Por exemplo, Vamos encontrar o quartil de cada linha de acordo com o SALÁRIO do funcionário:
de forma similar, você pode dividir as linhas em diferentes números de grupos e calcular o NTILE para diferentes partições.
5. liderar e atrasar
Frequentemente, você pode querer comparar o valor da linha atual com o da linha anterior ou seguinte. Ajuda na fácil análise de dados. a DIRIGIR() e ATRASO() As funções da janela estão lá apenas para esse propósito.
Aqui, criamos uma nova coluna contendo SALARY da próxima linha dentro de cada partição classificada por salário usando a função LEAD. Observe que a última linha de cada partição contém um valor nulo porque não há nenhuma linha subsequente da qual extrair dados..
Agora, vamos fazer o mesmo com a função LAG.
Aqui, criamos duas novas colunas. A primeira coluna contém SALÁRIO da linha anterior em cada partição classificada por salário. Enquanto a segunda coluna contém a diferença entre o SALÁRIO da linha anterior e a linha atual. Como você pode ver, isso é muito útil para uma análise rápida da diferença entre os salários dentro da mesma partição.
arquivo de código SQL
Todo o código SQL relacionado às funções da janela para este artigo pode ser encontrado neste link.
Notas finais
Já vimos funções de janela suficientes e espero que agora você possa apreciar a beleza das funções de janela no SQL. Mas o aprendizado não para por aqui. Depois de dominar o básico, é hora de dominar as ferramentas para lidar com Big Data.
Se você deseja migrar para o domínio de Engenharia de Dados, Sugiro os seguintes itens para uma transição fácil:
