5 Construções SQL avançadas Todo analista de dados deve aprender

Seu trabalho envolve análise de dados? Aqui estão cinco exemplos de como o SQL avançado pode ajudá-lo em seu trabalho diário.

Sou analista de dados e tenho que dizer - analistas de dados podem ser bastante estranhos. Outras pessoas geralmente têm medo de quantidades imensas de dados; nós gostamos disso. Quanto mais profundos formos nos dados, mais felizes seremos. Uma ferramenta importante para entrar em dados (e, portanto, ser um analista de dados mais feliz) é o SQL.

Lembra-se quando você descobriu pela primeira vez funções SQL simples? A alegria que você sentiu quando pôde selecionar rapidamente os dados, agrupá-los e ordená-los? Se você agora tem um domínio firme dessas funções básicas, você provavelmente está se perguntando o que é SQL avançado e como ele pode ajudá-lo a alcançar novos níveis de manipulação de dados e criar relatórios legais. Afinal, todo o trabalho e nenhuma diversão faz com que os analistas de dados sejam monótonos, meninos e meninas.

Bem, eu não vou me deter na definição. A melhor maneira de explicar o SQL avançado é mostrar-lhe algumas consultas avançadas. Então você verá como elas podem ser úteis (e divertidas).

Como vou usar muitas funções de janela SQL, é melhor se você já souber o que são e como funcionam. Este artigo o colocará em dia se você não estiver familiarizado com elas.

Consulta 1: Classificação dos dados

A gerência gosta de ver rankings para absolutamente tudo: produtos vendidos, salários, funcionários por departamento, dinheiro ganho por qualquer segmento imaginável - eles sempre pedirão para ver o ranking. Para lhe mostrar um exemplo de como classificar as coisas em SQL, vou usar o sales mesa. Ela possui as seguintes colunas:

• product - O nome do produto.
• product_price - O preço do produto.
• items_sold - O número de artigos vendidos.

A idéia é calcular a receita para cada produto e classificá-lo usando a função RANK(). O código abaixo irá resolver esta tarefa:

SELECT	product,
		product_price,
		items_sold,
		product_price * items_sold AS revenue,
		RANK() OVER (ORDER BY product_price * items_sold DESC) AS revenue_rank
FROM sales;

O código acima seleciona todas as colunas da tabela. Para obter a receita por produto, você precisa multiplicar o preço pelos itens vendidos. Isto é exatamente o que a consulta fará, e o resultado será mostrado na nova receita da coluna. Finalmente, há a função RANK(). Aqui, esta função classificará todas as linhas pela nova receita da coluna (definida por product_price * items_sold). A classificação será mostrada na nova coluna revenue_rank.

E aqui está o resultado:

RANK() é apenas uma maneira de classificar os dados em SQL. Você pode ver as outras formas neste artigo. E, se você quiser fazer um mergulho profundo no assunto, aqui está nosso guia completo de funções de ranking.

Consulta 2: Cálculo de valores Delta

Além da classificação, o cálculo dos valores delta é provavelmente uma das tarefas mais comuns dos analistas de dados. Isto é normalmente necessário ao calcular as variações diárias, mês a mês, trimestre a trimestre, ou ano a ano. Seja receita, custos, mudanças de preço, mudanças de volume ou qualquer outra coisa imaginável, você precisará calcular a diferença entre os números. Para fazer isso, uma consulta SQL avançada com a função LAG() é o que você precisa. Esta função é usada para recuperar os dados de uma linha anterior. Deixe-me mostrar como funciona usando a função revenue mesa. A tabela é composta de duas linhas:

• month - O mês do ano.
• revenue - A receita para aquele mês.

Sua tarefa é calcular a diferença entre a receita de cada mês e o mês anterior (ou seja, o delta da receita mensal). Como você faria isso? Se você conhece a função LAG(), é muito fácil. Aqui está o código:

SELECT	month,
		revenue,
		revenue - LAG(revenue, 1) OVER (ORDER BY month) AS monthly_delta
FROM revenue;

O princípio para calcular o delta é deduzir o mês anterior do mês atual. O código acima faz exatamente isso. Primeiro, ele seleciona o mês das colunas e a receita. Depois disso, ele deduz o montante da receita do mês atual do mês anterior. Isto é definido pela função LAG(). Os valores que colocamos no parêntese da função (revenue, 1) indicam que o valor da receita da coluna será deduzido do valor anterior naquela coluna. Por isso existe o número 1; ele define quantas linhas a função retorna para realizar a operação.

Em teoria, LAG(revenue) fará o mesmo, já que voltar uma linha é o padrão para a função LAG(). Entretanto, eu queria mostrar isto explicitamente. É mais fácil de entender e você saberá o que fazer quando precisar voltar mais de uma fileira.

Os deltas precisam ser calculados sequencialmente, e não por meses aleatórios; é por isso que existe o ORDER BY month. O delta será mostrado na nova coluna mensal_delta. Execute o código e você terá a tabela resultante:

Consulta 3: Cálculo dos totais de execução

Os totais de execução (também conhecidos como somas cumulativas) são amplamente utilizados na análise de dados. Eles são normalmente usados com dados de séries cronológicas para ver como certos indicadores de desempenho estão (ou estarão) se desenvolvendo ao longo do tempo. Como outros conceitos SQL avançados, os totais em execução têm um uso prático muito amplo. Eles são usados para monitorar vendas, receitas, custos, lucros e orçamentos. Aqui está um artigo que explica muito bem os totais em execução e como calculá-los em SQL.

Por enquanto, vou mostrar como funciona uma soma cumulativa para orçamentos. Vamos usar uma tabela muito imaginativamente chamada budget. É constituída por estas colunas:

• month - O mês do fluxo de caixa.
• client - O nome do cliente.
• cash_flow - O fluxo de caixa orçado.

Há três clientes. O orçamento contém projeções mensais do fluxo de caixa anual que sua empresa vai cobrar deles. Você precisa calcular o fluxo de caixa acumulado para cada cliente. Você já deve saber intuitivamente que precisa usar a função SUM(), mas com algum tipo de reviravolta. Veja aqui como calcular os totais em execução:

SELECT	month,
		client,
		cash_flow,
		SUM (cash_flow) OVER (PARTITION BY client ORDER BY month) AS running_total

FROM budget;

Esta é uma pequena e simples pergunta que faz maravilhas! Ela seleciona as colunas mês, cliente e fluxo de caixa. Para calcular o total em execução, você tem que resumir os fluxos de caixa. Isto é definido por SUM (cash_flow).

Entretanto, você não está interessado em ter os totais em um nível de tabela. Você precisa, de alguma forma, fazer um resumo dos fluxos de caixa mês a mês para o primeiro cliente, depois reiniciar e começar novamente para o segundo cliente. Para isso, você precisa OVER (PARTITION BY client ORDER BY month). Aqui a partição é definida pela coluna cliente, o que significa que cada conjunto de dados é definido pelos diferentes clientes. Além disso, a operação será realizada somente dentro da partição, não na tabela completa. Dessa forma, obtém-se um total em execução para cada cliente separadamente.

Naturalmente, os fluxos de caixa devem ser resumidos seqüencialmente; por isso é ordenado pela coluna do mês. O total em execução aparecerá na nova coluna running_total.

Aqui está a tabela resultante:

Eu usei funções de janela nos últimos três exemplos. Se você quiser saber mais sobre este tópico, uma boa maneira é o curso Funções de Janela (Window Functions) em SQL , um de nossos cursos SQL avançados.

Algo que também pode ser muito útil, especialmente se você é novo nas funções de janela ou as usa apenas ocasionalmente, é este SQL Funções de Janela (Window Functions) em SQL Cheat Sheet. Estarei usando-o na próxima vez que escrever sobre funções de janela, com certeza!

Consulta 4: Criando um Relatório Baseado em Múltiplas Condições

Uma das principais tarefas dos analistas de dados é tornar os dados mais amigáveis para outros usuários. Ao fornecer-lhes dados de uma forma que eles possam utilizar facilmente, facilitamos seu trabalho. Para criar relatórios úteis, um analista de dados tem que combinar a entrada de negócios com seu conhecimento dos dados. Uma das ferramentas que podem ajudá-lo a conseguir isso é uma declaração CASE, que é outro conceito SQL avançado.

Para dar um exemplo, vamos imaginar o seguinte cenário. Você está trabalhando para um banco e é solicitado por seus colegas a criar um relatório. Há uma tabela chamada debt que mostra os clientes do banco e detalhes sobre sua dívida. A tabela é composta pelas seguintes colunas:

• client - O nome do cliente.
• date_due - O dia em que a dívida se tornou exigível.
• amount_due - O valor da dívida que é devida.

O que você precisa fazer é criar um relatório a partir de 30.4.2020. De alguma forma é preciso calcular o número de dias de vencimento a partir da data do relatório. Além disso, é preciso alocar o cliente a um determinado período de tempo, de acordo com o número de dias de vencimento de sua conta.

A consulta está logo abaixo. Não tenha medo - vou analisá-la para você. Não é tão assustador quanto parece!

SELECT	client,
		date_due,
		amount_due,
		DATEDIFF ('2020-04-30', date_due) AS days_due,
		CASE
	WHEN  DATEDIFF ('2020-04-30', date_due) <= 30 THEN '0-30 days'
	WHEN  DATEDIFF ('2020-04-30', date_due) > 30 AND DATEDIFF ('2020-04-30', date_due) <=90 THEN '31-90 days'
	WHEN  DATEDIFF ('2020-04-30', date_due) > 90 AND DATEDIFF ('2020-04-30', date_due) <=180 THEN '91-180 days'
	WHEN  DATEDIFF ('2020-04-30', date_due) > 180 AND DATEDIFF ('2020-04-30', date_due) <=365 THEN '181-365 days'
	ELSE '> 365 days'
END AS time_bucket

FROM debt;

Primeiro, você precisa especificar a parte SELECT da consulta. Selecionei as colunas existentes cliente, data_limite, e quantidade_limite.

A seguir, você tem que calcular os dias devidos. Você faz isso subtraindo a data de vencimento da data do relatório. Isto é exatamente o que fiz com DATEDIFF ('2020-04-30', date_due) AS days_due. Usei a função DATEDIFF() para calcular a diferença necessária. Ao utilizar esta função, você primeiro tem que especificar quais datas você deseja subtrair. Em nosso caso, é a data do relatório e a data de vencimento. Em seguida, você tem que especificar como deseja que o resultado seja mostrado, ou seja, em anos, meses ou dias. Neste caso, você precisa de dias, então você coloca o dia como o último valor em DATEDIFF().

Agora vem a parte emocionante - criando as condições que usei na declaração em CASE. Esta declaração abre com CASE e termina com END. No meio, você precisa definir as condições que criarão o relatório que seus colegas desejam. Para isso, você usará WHEN e THEN.

Digamos que o primeiro balde dos dias previstos é de 0 a 30 dias. A primeira condição na declaração CASE é WHEN DATEDIFF ('2020-04-30', date_due) <= 30 THEN '0-30 days'. Como você precisa alocar clientes a um balde de tempo de acordo com os dias devidos, esta parte do código faz exatamente isso. Ela diz o seguinte: se a diferença entre a data do relatório e a data de vencimento for menor ou igual a 30 dias, então este cliente será alocado para o período de 0-30 dias.

O próximo balde de tempo é 31-90 dias, e esta é a parte do código que o define:

WHEN DATEDIFF ('2020-04-30', date_due) > 30 AND DATEDIFF ('2020-04-30', date_due) <=90 THEN '31-90 days'

Não é tão complicado assim, certo?

O mesmo princípio funciona para os dois baldes de tempo restantes: 91-180 dias e 181-365 dias. Toda dívida vencida há mais de 365 dias pertence ao balde de tempo de 365 dias. Isto é definido por ELSE '> 365 days'. Isto simplesmente define o critério para o relatório: se o valor é este, faça isto; se não é, faça isto. Essencialmente, é uma versão mais complexa da declaração IF.

Note que há uma maneira mais elegante de escrever este código: Eu poderia ter declarado uma variável contendo o valor '2020-04-30' em vez de escrever '2020-04-30' manualmente em todos os lugares do código. Entretanto, eu não queria confundi-lo se você não estiver familiarizado com variáveis.

Também, com relação à função DATEDIFF(), observe que usei a função e a sintaxe do MySQL. Dependendo do motor de banco de dados que você estiver usando, é possível que você tenha que adaptar a sintaxe de acordo.

Todos esses baldes de tempo serão mostrados na nova coluna time_bucket. Como você quer que seus dados fiquem bonitos, você ordenará sua tabela por dias_acima. Execute o código e você terá uma boa tabela. E provavelmente um café grátis de seus colegas!

Emocionante, não é? Se você quiser mais empolgação como esta, Como Criar Relatórios Básicos em SQL é para você! Lá você conhecerá mais sobre CASE WHEN e as nuances do GROUP BY.

Query 5: Adicionando Subtotais a um Relatório

Um pedido muito comum é mostrar os subtotais e totais no mesmo relatório. A cláusula ROLLUP torna isto muito mais fácil. É uma extensão de uma cláusula GROUP BY. Ela permite que você adicione subtotais e totais gerais aos seus dados.

Veja aqui como usar o ROLLUP. Você tem a tabela warehouse com as seguintes colunas:

• warehouse - O nome do armazém.
• brand - A marca do produto.
• product - O nome do produto.
• quantity - A quantidade deste produto no armazém.

Há duas marcas diferentes com cinco produtos entre eles. E há dois armazéns. Sua tarefa é calcular a quantidade total do produto para ambas as marcas nos dois armazéns. Você também precisa do total geral de todos os produtos em ambos os armazéns. E, finalmente, você precisa fazer tudo em uma única mesa com uma única consulta. Como você administraria isso? O código é:

SELECT	warehouse,
		brand,
		SUM (quantity) AS sum_product

FROM warehouse
GROUP BY ROLLUP (warehouse, brand);

Primeiro, você seleciona o armazém de colunas e a marca da tabela. Você também quer a soma da quantidade da coluna, que será mostrada na nova tabela sum_product. Qual é o próximo passo? É aqui que entra o ROLLUP! Ele é usado para obter totais para múltiplos níveis de agrupamento de dados. A parte GROUP BY ROLLUP (warehouse, brand) vai fazer exatamente isso. Ela agrupará os dados pelas colunas do armazém e da marca. Depois disso, ele somará os dados de acordo com cada agrupamento. O resultado é:

A tabela contém totais para as marcas Brando e Ostap nos armazéns de Amsterdã e Berlim e um total geral. O subtotal dos dois produtos no armazém de Amsterdã é mostrado na primeira linha com o valor da marca NULL. Ele totaliza 64 039, a soma das duas fileiras anteriores.

A seguir, você pode ver os totais das duas marcas no armazém de Berlim. Depois disso, há outra linha com um valor de marca NULL; na verdade, este é o subtotal de Berlim no valor de 80 807. A última linha mostra o total geral de todos os produtos em todos os armazéns, que é de 144 846.

Por que existem valores NULL em algumas filas? Porque SQL não sabe como nomear marcas e armazéns quando eles são agrupados e um subtotal ou total geral é mostrado. Para encontrar mais detalhes divertidos sobre outras extensões GROUP BY, confira nosso curso GROUP BY Extensions.

Esta é a última consulta SQL avançada, por enquanto.

Você encontrou estes SQL Avançado Tópicos úteis?

SQL Avançado como usado por cientistas de dados, analistas de dados e outros é um tópico realmente amplo. Há vastas possibilidades no uso de SQL para análise de dados. Esta faixaSQL Avançado lhe dará uma idéia do que você pode fazer com SQL como um analista de dados.

Neste artigo, tentei mostrar-lhe alguns dos usos mais comuns do SQL avançado. Os exemplos são práticos e extraídos de minha experiência, por isso espero que tenham sido úteis. Tentei tornar as consultas tão fáceis (e compreensíveis) quanto possível. Você pode adaptar estas consultas aos seus relatórios e conjuntos de dados particulares. Sinta-se à vontade para usá-los!

Se você tiver alguma pergunta ou comentário, informe-me na seção de comentários!