As cinco principais funções da janela SQL que você deve conhecer para entrevistas de ciência de dados

SQL é a linguagem universal no mundo dos dados e é a habilidade mais importante a ser aprendida como profissional de dados.

A razão pela qual o SQL é tão importante é que ele é a principal habilidade necessária durante a fase de preparação de dados. Muita exploração de dados, manipulação de dados, desenvolvimento de pipeline e criação de painéis são feitos por meio do SQL.

O que separa grandes cientistas de dados de bons cientistas de dados é que grandes cientistas de dados podem disputar dados tanto quanto os recursos do SQL permitem. Uma grande parte de poder usar totalmente tudo o que o SQL tem a oferecer é saber como usar as funções da janela.

Com isso dito, vamos mergulhar nisso!

1. Deltas com LEAD() e LAG()

 
LEAD() e LAG() são usados ​​principalmente ao comparar um período de tempo com o período anterior de uma determinada métrica. Para dar alguns exemplos…

• Você pode obter o delta entre as vendas de cada ano e as vendas do ano anterior
• Você pode obter o delta no número de inscrições/conversões/visitas ao site mensalmente
• Você pode comparar a perda de usuários mensalmente

Exemplo:

A consulta a seguir mostra como você pode consultar a alteração percentual mensal nos custos

with monthly_costs as ( SELECT date , monthlycosts , LEAD(monthlycosts) OVER (ORDER BY date) as previousCosts FROM costs )SELECT date , (monthlycosts - previousCosts) / previousCosts * 100 AS costPercentChange FROM monthly_costs

2. Somas cumulativas com SUM() ou COUNT()

 
O cálculo de totais em execução pode ser feito simplesmente por meio de uma função do Windows que começa com SUM() ou COUNT(). Essa é uma ferramenta poderosa quando você deseja mostrar o crescimento de uma determinada métrica ao longo do tempo. Mais especificamente, é útil nas seguintes circunstâncias:

• Obtenha um total atual de receitas e custos ao longo do tempo
• Obtenha um total de tempo gasto no aplicativo por usuário
• Obtenha um total contínuo de conversões ao longo do tempo

Exemplo:

O exemplo a seguir mostra como você pode incluir uma coluna de soma cumulativa de custos mensais:

SELECT date , monthlycosts , SUM(monthlycosts) OVER (ORDER BY date) as cumCosts FROM cost_table

3. Médias móveis com AVG()

 
AVG() é realmente poderoso em funções do Windows, pois permite calcular médias móveis ao longo do tempo.

As médias móveis são uma maneira simples, mas eficaz, de prever valores no curto prazo. Eles também são extremamente úteis para suavizar curvas voláteis em um gráfico. Geralmente, as médias móveis são usadas para avaliar a direção geral de onde as coisas estão se movendo.

Mais especificamente…

• Eles podem ser usados ​​para obter a tendência geral das vendas semanais (a média está subindo ao longo do tempo?). Isso indicaria crescimento como empresa.
• Eles também podem ser usados ​​para obter a tendência geral de conversões semanais ou visitas ao site.

Exemplo:

A consulta a seguir é um exemplo de como obter a média móvel de 10 dias para conversões.

SELECT Date , dailyConversions , AVG(dailyConversions) OVER (ORDER BY Date ROWS 10 PRECEDING) AS 10_dayMovingAverage FROM conversions

4. ROW_NUMBER()

 
ROW_NUMBER() é particularmente útil quando você deseja obter o primeiro ou o último registro. Por exemplo, se você tem uma tabela de quando os membros da academia foram à academia e deseja obter a data do primeiro dia em que eles foram à academia, você pode PARTICIONAR POR cliente (nome/id) e ORDER POR data de compra. Então, para obter a primeira linha, você pode simplesmente filtrar as linhas com rowNumber igual a um.

Exemplo:

Este exemplo mostra como você pode usar ROW_NUMBER() para obter a primeira data de quando cada membro (usuário) visitou.

with numbered_visits as ( SELECT memberId , visitDate , ROW_NUMBER() OVER (PARTITION BY customerId ORDER BY purchaseDate) as rowNumber FROM gym_visits )SELECT * FROM numbered_visits WHERE rowNumber = 1

Para recapitular, se você precisar obter o primeiro ou o último registro, ROW_NUMBER() é uma ótima maneira de conseguir isso.

5. Registre a classificação com DENSE_RANK()

 
DENSE_RANK() é semelhante a ROW_NUMBER(), exceto que retorna a mesma classificação para valores iguais. A classificação densa é bastante útil quando se trata de recuperar os principais registros, por exemplo:

• Se você quiser puxar os 10 programas mais assistidos da Netflix esta semana
• Se você deseja obter os 100 principais usuários com base em dólares gastos
• Se você quiser ver o comportamento dos 1000 usuários menos ativos

Exemplo:

Se você quiser classificar seus principais clientes pelo total de vendas, DENSE_RANK() seria uma função apropriada para usar.

SELECT customerId , totalSales , DENSE_RANK() OVER (ORDER BY totalSales DESC) as rank FROM customers

Obrigado pela leitura!

 
E isso é tudo! Espero que isso o ajude em sua preparação para a entrevista - tenho certeza que se você conhece esses 5 conceitos de dentro para fora, você se sairá muito bem quando se trata da maioria dos problemas de função de janela SQL por aí.

Como sempre, desejo-lhe o melhor em seus esforços de aprendizagem!

 
 
Terence Shin é um entusiasta de dados com mais de 3 anos de experiência em SQL e mais de 2 anos de experiência em Python e um blogueiro em Towards Data Science e KDnuggets.

Óptimo estado. Original. Republicado com permissão.

Fonte: https://www.kdnuggets.com/2022/01/top-five-sql-window-functions-know-data-science-interviews.html