25 Exemplos avançados de consulta SQL

Uma das melhores maneiras de aprender SQL avançado é através do estudo de exemplos de consultas. Neste artigo, mostraremos 25 exemplos de consultas avançadas a SQL de média a alta complexidade. Você pode usá-los para atualizar seus conhecimentos de SQL avançado ou para revisar antes de uma entrevista SQL.

Muitos dos exemplos neste artigo serão baseados no seguinte employee mesa. Apenas alguns exemplos serão baseados em outras tabelas; nestes casos, as tabelas serão explicadas junto com o exemplo.

employee_id	first_name	last_name	dept_id	manager_id	salary	expertise
100	John	White	IT	103	120000	Senior
101	Mary	Danner	Account	109	80000	junior
102	Ann	Lynn	Sales	107	140000	Semisenior
103	Peter	O'connor	IT	110	130000	Senior
106	Sue	Sanchez	Sales	107	110000	Junior
107	Marta	Doe	Sales	110	180000	Senior
109	Ann	Danner	Account	110	90000	Senior
110	Simon	Yang	CEO	null	250000	Senior
111	Juan	Graue	Sales	102	37000	Junior

Mesmo para pessoas com experiência em SQL, um bom curso de SQL interativo online pode ser uma verdadeira ajuda. Você pode encontrar o conjunto mais completo de cursos de SQL interativo em nossa trilha SQL de A a Z . Ele contém 7 cursos de SQL inter ativo com mais de 850(!) exercícios organizados logicamente para levá-lo de um iniciante completo a um usuário SQL avançado. Os cursos para iniciantes cobrem os fundamentos da SQL e são uma forma perfeita de rever e atualizar seus conhecimentos básicos de SQL. Os cursos avançados de SQL lhe ensinarão conceitos como funções de janela, consultas recursivas e relatórios SQL complexos. Crie uma conta LearnSQL.com.br gratuita e experimente nossos cursos interativos sem ter que gastar nenhum dinheiro. Então, se você gosta do que está aprendendo, você pode comprar acesso completo.

Muito bem, vamos investigar nossas consultas avançadas a SQL!

25 SQL Avançado Consultar exemplos com explicações

Exemplo #1 - Linhas de classificação baseadas em um critério de ordenação específico

Às vezes precisamos criar uma consulta SQL para mostrar um ranking de linhas com base em um critério de ordem específico. Neste exemplo de consulta, mostraremos uma lista de todos os funcionários ordenados por salário (primeiro o salário mais alto). O relatório incluirá a posição de cada funcionário no ranking.

Aqui está o código:

SELECT 	
  employee_id, 
  last_name, 
  first_name, 
  salary, 
  RANK() OVER (ORDER BY salary DESC) as ranking
FROM employee
ORDER BY ranking

Na consulta acima, utilizamos a função RANK(). É uma função de janela que retorna a posição de cada linha no conjunto de resultados, com base na ordem definida na cláusula OVER (1 para o salário mais alto, 2 para o segundo salário mais alto, e assim por diante). Precisamos usar uma cláusula de classificação ORDER BY no final da consulta para indicar a ordem em que o resultado será mostrado.

Se você quiser saber mais sobre as funções de ranking em SQL, recomendo nosso artigo Qual é a função do RANK() em SQL, e como você a utiliza?

Exemplo #2 - Liste as 5 primeiras linhas de um conjunto de resultados

A próxima consulta SQL cria um relatório com os dados dos funcionários para os 5 salários mais altos da empresa. Este tipo de relatório deve ser ordenado com base em um determinado critério; em nosso exemplo, o critério de ordenação será novamente salary DESC:

WITH employee_ranking AS (
  SELECT 
    employee_id, 
    last_name, 
    first_name, 
    salary,
    RANK() OVER (ORDER BY salary DESC) as ranking
  FROM employee
)
SELECT
  employee_id, 
  last_name, 
  first_name, 
  salary
FROM employee_ranking
WHERE ranking <= 5
ORDER BY ranking

A cláusula WITH da consulta anterior cria um CTE chamado employee_ranking, que é uma espécie de tabela virtual que é consumida na consulta principal. A subconsulta no CTE utiliza a função RANK() para obter a posição de cada linha no ranking. A cláusula OVER (ORDER BY salary DESC) indica como o valor RANK() deve ser calculado. A função RANK() para a linha com o maior salário retornará 1, e assim por diante.

Finalmente, no WHERE da consulta principal pedimos aquelas linhas com um valor de classificação menor ou igual a 5. Isto nos permite obter apenas as 5 primeiras filas por valor de ranking. Mais uma vez, usamos uma cláusula ORDER BY para mostrar o conjunto de resultados, que é ordenado por ordem crescente de classificação.

Exemplo #3 - Liste as últimas 5 linhas de um conjunto de resultados

Esta consulta é semelhante à consulta dos 5 primeiros, mas queremos as últimas 5 filas. Só precisamos introduzir uma mudança no tipo de ordem, ou seja, usando ASC em vez de DESC. No CTE, criaremos uma coluna de classificação com base em uma ordem ascendente de salário (primeiro o salário mais baixo):

WITH employee_ranking AS (
  SELECT 
    employee_id, 
    last_name, 
    first_name, 
    salary,
    RANK() OVER (ORDER BY salary ASC) as ranking
  FROM employee
)
SELECT
  employee_id, 
  last_name, 
  first_name, 
  salary
FROM employee_ranking
WHERE ranking <= 5
ORDER BY ranking

Na consulta principal, usamos WHERE ranking <= 5 para filtrar as filas com os 5 salários mais baixos. Depois disso, utilizamos ORDER BY ranking para ordenar as linhas do relatório por valor de classificação.

Exemplo #4 - Listar a segunda linha mais alta de um conjunto de resultados

Vamos supor que gostaríamos de obter os dados do funcionário com o segundo salário mais alto da empresa. Podemos aplicar uma abordagem semelhante à nossa consulta anterior:

WITH employee_ranking AS (
  SELECT 
    employee_id, 
    last_name, 
    first_name, 
    salary,
    RANK() OVER (ORDER BY salary DESC) as ranking
  FROM employee
)
SELECT 
  employee_id, 
  last_name, 
  first_name, 
  salary
FROM employee_ranking
WHERE ranking = 2

A condição WHERE ranking = 2 é utilizada para filtrar as filas com o salário na posição 2. Observe que podemos ter mais de um funcionário na posição 2 se ele tiver o mesmo salário.

Neste ponto, é importante entender o comportamento da função RANK(), bem como outras funções disponíveis como ROW_NUMBER() e DENSE_RANK(). Este tópico é abordado em detalhes em nossa Visão Geral das Funções de Ranking em SQL. Recomendo vivamente a leitura deste artigo se você precisar trabalhar com diferentes tipos de rankings.

Exemplo #5 - Liste o Segundo Salário Mais Alto por Departamento

Vamos adicionar uma variação à consulta SQL anterior. Como cada um de nossos funcionários pertence a um departamento, queremos agora um relatório mostrando o ID do departamento e o nome do funcionário com o segundo salário mais alto neste departamento. Queremos um registro para cada departamento da empresa. Aqui está a consulta:

WITH employee_ranking AS (
  SELECT 
    employee_id, 
    last_name, 
    first_name, 
    salary, 
    dept_id
    RANK() OVER (PARTITION BY dept_id ORDER BY salary DESC) as ranking
  FROM employee
)
SELECT
  dept_id, 
  employee_id, 
  last_name, 
  first_name, 
  salary
FROM employee_ranking
WHERE ranking = 2
ORDER BY dept_id, last_name

A principal mudança introduzida nesta consulta é a cláusula PARTITION BY dept_id em OVER. Esta cláusula agrupa as linhas com o mesmo dept_id, ordenando as linhas em cada grupo por salário DESC. Em seguida, a função RANK() é calculada para cada departamento.

Na consulta principal, retornamos o dept_id e os dados dos funcionários da posição 2 do ranking de seus departamentos.

Para aqueles leitores que querem saber mais sobre como encontrar a enésimafileira mais alta de um grupo, recomendo o artigo Como Encontrar o Nth-Highest Salary por departamento com SQL.

Exemplo #6 - Liste as primeiras fileiras de 50% em um conjunto de resultados

Em alguns casos, poderíamos estar interessados em obter os primeiros 50% do resultado fixado, (ou qualquer outro percentual). Para este tipo de relatório, existe uma função SQL chamada NTILE() que recebe um parâmetro inteiro indicando o número de subconjuntos nos quais queremos dividir todo o conjunto de resultados. Por exemplo NTILE(2) divide o conjunto de resultados em 2 subconjuntos com a mesma quantidade de elementos; para cada linha, retorna um 1 ou um 2, dependendo do subconjunto onde a linha está localizada.

Aqui está a consulta:

WITH employee_ranking AS (
  SELECT 
    employee_id, 
    last_name, 
    first_name, 
    salary,
    NTILE(2) OVER (ORDER BY salary ) as ntile
  FROM employee
)
SELECT 
  employee_id, 
  last_name, 
  first_name, 
  salary
FROM employee_ranking
WHERE ntile = 1
ORDER BY salary 

A consulta acima retorna apenas as linhas na primeira metade de um relatório de empregados ordenados por salário em ordem ascendente. Usamos a condição ntile = 1 para filtrar somente as linhas da primeira metade do relatório. Se você estiver interessado na função da janela NTILE(), consulte o artigo Common SQL Funções de Janela (Window Functions) em SQL: Usando Partições com Funções de Ranking.

Exemplo #7 - Lista as últimas linhas de 25% em um conjunto de resultados

Como na consulta anterior, neste exemplo usaremos NTILE(4) para dividir o conjunto de resultados em 4 subconjuntos; cada subconjunto terá 25% do total do conjunto de resultados. Usando a função NTILE(), vamos gerar uma coluna chamada ntile com os valores 1, 2, 3, e 4:

WITH employee_ranking AS (
  SELECT 
    employee_id, 
    last_name, 
    first_name, 
    salary,
    NTILE(4) OVER (ORDER BY salary) as ntile
  FROM employee
)
SELECT 
  employee_id, 
  last_name, 
  first_name, 
  salary
FROM employee_ranking
WHERE ntile = 4
ORDER BY salary 

O WHERE ntile = 4 filtra apenas as linhas do último trimestre do relatório. A última cláusula ORDER BY salary ordena que o resultado definido seja devolvido pela consulta, enquanto OVER (ORDER BY salary) ordena as linhas antes de dividi-las em 4 subconjuntos usando NTILE(4).

Exemplo #8 - Numerar as linhas em um conjunto de resultados

Às vezes queremos criar um ranking que atribua a cada linha um número indicando a posição daquela linha no ranking: 1 para a primeira linha, 2 para a segunda, e assim por diante. SQL oferece algumas maneiras de fazer isso. Se quisermos uma seqüência simples de números de 1 a N, podemos usar a função ROW_NUMBER(). Entretanto, se quisermos um ranking que permita duas linhas na mesma posição (ou seja, porque compartilham o mesmo valor), podemos usar a função RANK() ou DENSE_RANK(). A consulta seguinte cria um relatório onde cada linha tem um valor de posição:

SELECT
  employee_id, 
  last_name, 
  first_name, 
  salary,
  ROW_NUMBER() OVER (ORDER BY employee_id) as ranking_position
FROM employee

Se você quiser aprender sobre diferentes funções avançadas de ranking, recomendo o artigo Visão geral das funções de classificação em SQL.

Exemplo #9 - Lista todas as combinações de linhas a partir de duas tabelas

Em alguns casos, poderíamos precisar de uma união que inclua todas as combinações possíveis de linhas de duas mesas. Suponha que tenhamos uma empresa de alimentos onde vendemos 3 tipos de cereais: flocos de milho, flocos de milho com açúcar e flocos de arroz. Todos esses cereais são vendidos em 3 tamanhos diferentes de embalagens: 1 libra, 3 libras, e 5 libras. Como oferecemos 3 produtos em 3 tamanhos diferentes de embalagens, então oferecemos 9 combinações diferentes.

Temos um product mesa com 3 registros (flocos de milho, flocos de milho com açúcar e flocos de arroz) e outra mesa chamada box_size com 3 registros um para 1 libra e dois registros para 3 e 5 libras, respectivamente. Se quisermos criar um relatório com a lista de preços para nossas nove combinações, podemos usar a seguinte consulta:

SELECT
  grain.product_name,
  box_size.description, 
  grain.price_per_pound * box_size.box_weight
FROM product
CROSS JOIN	box_sizes

O resultado da consulta será:

product	package_size	price
Corn flake	1 pound box	2.43
Corn flake	3 pound box	7.29
Corn flake	5 pound box	12.15
Sugared corn flake	1 pound box	2.85
Sugared corn flake	3 pound box	8.55
Sugared corn flake	5 pound box	14.25
Rice flake	1 pound box	1.98
Rice flake	3 pound box	5.94
Rice flake	5 pound box	9.90

A cláusula CROSS JOIN sem qualquer condição produz uma tabela com todas as combinações de linhas de ambas as tabelas. Note que calculamos o preço com base no preço por libra armazenado na tabela product tabela e o peso de box_sizes com a expressão:

    grain.price_per_pound * box_size.box_weight

Um mergulho profundo no CROSS JOIN pode ser encontrado em Um Guia Ilustrado para o SQL CROSS JOIN.

Exemplo #10 - Junte-se a uma mesa

Em alguns casos, precisamos unir uma mesa a si mesma. Pense sobre a employee mesa. Cada linha tem uma coluna chamada manager_id com a identificação do gerente que supervisiona este funcionário. Usando uma auto-adesão, podemos obter um relatório com as colunas employee_name e manager_name; isto nos mostrará quem gerencia cada funcionário. Aqui está a consulta:

SELECT 	
  e1.first_name ||’ ‘|| e1.last_name AS manager_name,
  e2.first_name ||’ ‘|| e2.last_name AS employee_name
FROM employee e1
JOIN employee e2 
ON e1.employee_id = e2.manager_id

Na consulta acima, podemos ver a tabela employee é referenciada duas vezes como e1 e e2, e a condição de adesão é e1.employee_id = e2.manager_id. Esta condição liga cada fila de funcionários com a fila do gerente. O artigo O que é um Self Join em SQL? Uma Explicação Com Sete Exemplos lhe dará mais idéias sobre quando você pode aplicar a auto-inscrição em suas consultas SQL.

Exemplo #11 - Mostrar todas as linhas com um valor acima da média

Precisamos de um relatório mostrando todos os funcionários com um salário mais alto do que a média da empresa. Podemos primeiro criar uma subqueria para obter o salário médio da empresa, e depois comparar o salário de cada funcionário com o resultado da subqueria. Isto é mostrado no exemplo a seguir:

SELECT 
  first_name, 
  last_name, 
  salary
FROM employee  
WHERE salary > ( SELECT AVG(salary) FROM employee )

Você pode ver a subconsulta que obtém o salário médio na cláusula WHERE. Na consulta principal, selecionamos o nome e o salário do funcionário. Você pode ler mais sobre as subconsultas no artigo Como praticar as subconsultas SQL.

Exemplo #12 - Empregados com salários mais altos que a média departamental

Vamos supor que queremos obter registros para funcionários com salários mais altos que a média salarial em seus departamentos. Esta consulta é diferente da anterior porque agora precisamos de uma subconsulta para obter o salário médio para o departamento do funcionário atual e não para toda a empresa.

Aqui está o código:

SELECT
  first_name, 
  last_name, 
  salary
FROM employee e1 
WHERE salary > 
    (SELECT AVG(salary) 
     FROM employee e2 
     WHERE e1.departmet_id = e2.department_id)

Na subconsulta, podemos ver uma referência à coluna e1.department_id, que é uma coluna referenciada na consulta principal. A condição e1.departmet_id = e2.department_id é a chave na subconsulta porque nos permite obter a média de todos os funcionários do departamento da linha atual. Uma vez obtido o salário médio do departamento, comparamo-lo com o salário do funcionário e filtramos de acordo.

Exemplo #13 - Obter todas as linhas em que um valor está em um resultado da subconsulta

Suponha que John Smith administre vários departamentos e que queremos obter uma lista de todos os funcionários desses departamentos. Usaremos uma subconsulta para obter as identificações dos departamentos administrados por John Smith. Depois usaremos o operador IN para encontrar os funcionários que trabalham nesses departamentos:

SELECT 	
  first_name, 
  last_name
FROM employee e1 
WHERE department_id IN (
   SELECT department_id 
   FROM department
   WHERE manager_name=‘John Smith’)

A subconsulta anterior é uma subconsulta de várias linhas: ela retorna mais de uma linha. Na verdade, ela retornará várias fileiras porque John Smith administra muitos departamentos. Ao trabalhar com subconsultas de várias fileiras, é necessário usar operadores específicos (como IN) na condição WHERE envolvendo a subconsulta.

Exemplo #14 - Encontrar Linhas Duplicadas em SQL

Se uma tabela tiver linhas duplicadas, você pode encontrá-las com SQL. Use uma consulta com uma cláusula GROUP BY incluindo todas as colunas da tabela e uma cláusula HAVING para filtrar as linhas que aparecem mais de uma vez. Aqui está um exemplo:

SELECT 	
  employee_id, 
  last_name, 
  first_name, 
  dept_id, 
  manager_id, 
  salary
FROM employee
GROUP BY 	
  employee_id, 
  last_name, 
  first_name, 
  dept_id, 
  manager_id, 
  salary
HAVING COUNT(*) > 1

As linhas que não estão duplicadas terão um COUNT(*) igual a 1, mas aquelas linhas que existem muitas vezes terão um COUNT(*) retornando o número de vezes que a linha existe. Sugiro o artigo Como Encontrar Valores Duplicados em SQL, se você quiser encontrar mais detalhes sobre esta técnica.

Exemplo #15 - Contagem de Linhas Duplicadas

Se você quiser contar linhas duplicadas, você pode usar a seguinte consulta. É semelhante à anterior, mas adicionamos um COUNT(*) na lista SELECT para mostrar quantas vezes cada linha duplicada aparece na tabela:

SELECT 	
  employee_id, 
  last_name, 
  first_name, 
  dept_id, 
  manager_id, 
  salary, 
  COUNT(*) AS number_of_rows
FROM employee
GROUP BY
  employee_id, 
  last_name, 
  first_name, 
  dept_id, 
  manager_id, 
  salary
HAVING COUNT(*) > 1

Novamente, você pode encontrar informações valiosas sobre como gerenciar registros duplicados no artigo Como Encontrar Valores Duplicados em SQL.

Exemplo #16 - Como Encontrar Registros Comuns entre Tabelas

Se você tiver duas tabelas com o mesmo esquema ou se duas tabelas tiverem um subconjunto de colunas em comum, você pode obter as linhas que aparecem em ambas as tabelas com o operador do conjunto INTERSECT. Vamos supor que temos um instantâneo da tabela employee tomadas em janeiro de 2020 chamadas employee_2020_jan e queremos obter a lista de funcionários que existem em ambas as tabelas. Podemos fazer isso com esta consulta:

SELECT 
  last_name, 
  first_name 
FROM employee
INTERSECT
SELECT 
  last_name, 
  first_name 
FROM employee_2020_jan

Como resultado, obteremos uma lista de funcionários que aparecem em ambas as tabelas. Talvez eles tenham valores diferentes nas colunas como salary ou dept_id. Em outras palavras, estamos obtendo os funcionários que trabalharam para a empresa em janeiro de 2020 e que ainda estão trabalhando para a empresa.

Se você estiver interessado em encontrar mais sobre os operadores de conjuntos, sugiro o artigo Apresentando os operadores do SQL Set: Union, Union All, Minus, e Intersect.

Exemplo #17 - Agrupando Dados com o ROLLUP

A cláusula GROUP BY em SQL é usada para agregar linhas em grupos e aplicar funções a todas as linhas do grupo, retornando um único valor de resultado. Por exemplo, se quisermos obter um relatório com o valor total do salário por departamento e nível de especialização, podemos fazer a seguinte consulta:

SELECT 	
  dept_id, 
  expertise, 
  SUM(salary) total_salary
FROM	employee
GROUP BY dept_id, expertise

O GROUP BY tem a cláusula opcional ROLLUP, que lhe permite incluir agrupamentos adicionais em uma consulta. Adicionar a cláusula ROLLUP ao nosso exemplo poderia nos dar a soma total de salários para cada departamento (não importando o nível de especialização do funcionário) e a soma total de salários para toda a tabela (não importando o departamento do funcionário e o nível de especialização). A consulta modificada é:

SELECT 
  dept_id, 
  expertise, 
  SUM(salary) total_salary
FROM employee
GROUP BY ROLLUP (dept_id, expertise)

E o resultado será:

dept_id	expertise	total_salary
Account	Senior	90000
Account	Junior	80000
Account	NULL	170000
CEO	Senior	250000
CEO	NULL	250000
IT	Senior	250000
IT	NULL	250000
Sales	Junior	110000
Sales	Semisenior	140000
Sales	Senior	180000
Sales	NULL	430000
NULL	NULL	1100000

As linhas no conjunto de resultados com um NULL são as linhas extras adicionadas pela cláusula ROLLUP. Um valor NULL na coluna expertise significa um grupo de linhas para um valor específico de dept_id, mas sem um valor expertise específico. Em outras palavras, é o valor total de salários para cada dept_id. Da mesma forma, a última linha do resultado tendo um NULL para as colunas dept_id e expertise significa o total geral para todos os departamentos da empresa.

Se você quiser saber mais sobre a cláusula ROLLUP e outras cláusulas similares como CUBE, o artigo Agrupamento, Rolling, e Cubing Data tem muitos exemplos.

Exemplo #18 - Soma Condicional

Em alguns casos, precisamos resumir ou contar valores com base em alguma(s) condição(ões). Por exemplo, se quisermos obter o total de salários nos departamentos de Vendas e Recursos Humanos combinados e nos departamentos de TI e Suporte combinados, podemos executar a seguinte consulta:

SELECT 
  SUM (CASE
    WHEN dept_id IN (‘SALES’,’HUMAN RESOURCES’) 
    THEN salary
    ELSE 0 END) AS total_salary_sales_and_hr,
  SUM (CASE
    WHEN dept_id IN (‘IT’,’SUPPORT’) 
    THEN salary
    ELSE 0 END) AS total_salary_it_and_support
FROM employee

A consulta retorna uma única linha com duas colunas. A primeira coluna mostra o salário total para os departamentos de Vendas e Recursos Humanos. Este valor é calculado usando a função SUM() na coluna salary - mas somente quando o funcionário pertence ao departamento de Vendas ou de Recursos Humanos. Um zero é adicionado à soma quando o funcionário pertence a qualquer outro departamento. A mesma idéia é aplicada para a coluna total_salary_it_and_support.

Os artigos Padrões SQL Úteis: Sumarização Condicional com CASE e Como Usar CASE quando com SUM() em SQL fornecem mais detalhes sobre esta técnica.

Exemplo #19 - Linhas de Grupo por uma Faixa

Na próxima consulta de exemplo, criaremos as faixas salariais low, medium, e high. Em seguida, contaremos quantos funcionários estão em cada faixa salarial:

SELECT 
  CASE
    WHEN salary <= 750000 THEN ‘low’
    WHEN salary > 750000 AND salary <= 100000 THEN ‘medium’
    WHEN salary > 100000 THEN ‘high’
  END AS salary_category, 
  COUNT(*) AS number_of_employees
FROM	employee
GROUP BY 
  CASE
    WHEN salary <= 750000 THEN ‘low’
    WHEN salary > 750000 AND salary <= 100000 THEN ‘medium’
    WHEN salary > 100000 THEN ‘high’
END

Nesta consulta, utilizamos CASE para definir a faixa salarial de cada funcionário. Você pode ver a mesma declaração CASE duas vezes. A primeira define as faixas, como acabamos de dizer; a segunda no GROUP BY agregados registra e aplica a função COUNT(*) a cada grupo de registros. Você pode usar a declaração CASE da mesma forma para computar contagens ou somas para outros níveis personalizados.

Como usar CASE em SQL explica outros exemplos de instruções CASE como a usada nesta consulta.

Exemplo #20 - Cálculo de um total em SQL

Um total em execução é um padrão SQL muito comum, que é usado com freqüência nas finanças e na análise de tendências.

Quando você tem uma tabela que armazena qualquer métrica diária, como uma tabela sales com as colunas day e daily_amount, você pode calcular o total em execução como a soma cumulativa de todos os valores daily_amount anteriores. SQL fornece uma função de janela chamada SUM() para fazer exatamente isso.

Na consulta seguinte, vamos calcular as vendas acumuladas para cada dia:

SELECT 
  day,
  daily_amount,
  SUM (daily_amount) OVER (ORDER BY day) AS running_total
FROM sales

A função SUM() usa a cláusula OVER() para definir a ordem das linhas; todas as linhas anteriores ao dia atual estão incluídas no SUM(). Aqui está um resultado parcial:

day	daily_amount	running_total
Jan 30, 2023	1000.00	1000.00
Jan 31, 2023	800.00	1800.00
Feb 1, 2023	700.00	2500.00

As duas primeiras colunas do dia e daily_amount são valores extraídos diretamente da tabela sales. A coluna running_total é calculada pela expressão:

SUM (daily_amount) OVER (order by day)

Você pode ver claramente como o running_total é a soma acumulada do anterior daily_amounts.

Se você deseja aprofundar este tópico, sugiro o artigo O que é um total de execução de SQL e como calculá-lo?, que inclui muitos exemplos esclarecedores.

Exemplo #21 - Calcule uma Média Móvel em SQL

Uma média móvel é uma técnica de série temporal para analisar as tendências dos dados. Ela é calculada como a média do valor atual e um número especificado de valores imediatamente anteriores para cada ponto no tempo. A idéia principal é examinar como essas médias se comportam ao longo do tempo em vez de examinar o comportamento dos pontos de dados originais ou brutos.

Vamos calcular a média móvel para os últimos 7 dias usando o sales tabela do exemplo anterior:

SELECT 
  day,
  daily_amount,
  AVG (daily_amount) OVER (ORDER BY day ROWS 6 PRECEDING)
    AS moving_average
FROM sales

Na consulta acima, usamos a função da janela AVG() para calcular a média usando a linha atual (hoje) e as 6 linhas anteriores. Como as linhas são ordenadas por dia, a linha atual e as 6 linhas anteriores definem um período de 1 semana.

O artigo O que é uma média móvel e como calculá-la em SQL entra em detalhes sobre este assunto; verifique se você quer saber mais.

Exemplo #22 - Calcular uma diferença (Delta) entre duas colunas em linhas diferentes

Há mais de uma maneira de calcular a diferença entre duas filas em SQL. Uma maneira de fazê-lo é usando as funções de janela LEAD() e LAG(), como faremos neste exemplo.

Vamos supor que queremos obter um relatório com a quantidade total vendida em cada dia, mas também queremos obter a diferença (ou delta) relacionada com o dia anterior. Podemos utilizar uma consulta como esta:

SELECT 
  day,
  daily_amount,
  daily_amount - LAG(daily_amount) OVER (ORDER BY day)
    AS delta_yesterday_today
FROM sales

A expressão chave nesta consulta é:

daily_amount - LAG(daily_amount) OVER (ORDER BY day)

Ambos os elementos da diferença aritmética vêm de filas diferentes. O primeiro elemento vem da linha atual e LAG(daily_amount) vem da linha do dia anterior. LAG() retorna o valor de qualquer coluna da linha anterior (com base no ORDER BY especificado na cláusula OVER ).

Se você quiser ler mais sobre LAG() e LEAD(), sugiro o artigo Como calcular a diferença entre duas fileiras em SQL.

Exemplo #23 - Calcular a diferença entre duas linhas em SQL

Comparações ano a ano (YOY) ou mês a mês são uma forma popular e eficaz de avaliar o desempenho de vários tipos de organizações. Você pode calcular a comparação como um valor ou como uma porcentagem.

Neste exemplo, usaremos o sales tabela, que possui dados em uma granularidade diária. Primeiro precisamos agregar os dados ao ano ou mês, o que faremos criando um CTE com valores agregados por ano. Aqui está a consulta:

WITH year_metrics AS (
  SELECT 
    extract(year from day) as year,
    SUM(daily_amount) as year_amount
  FROM sales 
  GROUP BY year)
SELECT 
  year, 
  year_amount,
  LAG(year_amount) OVER (ORDER BY year) AS revenue_previous_year,
  year_amount - LAG(year_amount) OVER (ORDER BY year) as yoy_diff_value,
  ((year_amount - LAG(year_amount) OVER (ORDER BY year) ) /
     LAG(year_amount) OVER (ORDER BY year)) as yoy_diff_perc
FROM year_metrics
ORDER BY 1

A primeira expressão a ser analisada é a utilizada para calcular yoy_diff_value:

year_amount - LAG(year_amount ) OVER (ORDER BY year)

É usada para calcular a diferença (como um valor) entre o valor do ano atual e o ano anterior usando a função da janela LAG() e ordenando os dados por ano.

Na expressão seguinte, calculamos a mesma diferença como uma porcentagem. Este cálculo é um pouco mais complexo porque precisamos dividir pelo valor do ano anterior. (Nota: Usamos o ano anterior como base para o cálculo do percentual, portanto, o ano anterior é 100%).

((year_amount-LAG(year_amount ) OVER(ORDER BY year))/LAG(year_amount ) OVER(ORDER BY year))

No artigo Como calcular as diferenças ano a ano em SQL, você pode encontrar vários exemplos de cálculo de diferenças ano a ano e mês a mês.

Exemplo #24 - Use Consultas Recursivas para Gerenciar Hierarquias de Dados

Algumas tabelas em SQL podem ter um tipo implícito de hierarquia de dados. A título de exemplo, nossa employee A tabela tem um manager_id para cada funcionário. Temos um gerente que está a cargo de outros gerentes, que por sua vez têm outros funcionários sob sua responsabilidade, e assim por diante.

Quando temos este tipo de organização, podemos ter uma hierarquia de vários níveis. Em cada linha, a coluna manager_id se refere à linha do nível imediatamente superior da hierarquia. Nestes casos, um pedido freqüente é obter uma lista de todos os funcionários reportando-se (direta ou indiretamente) ao CEO da empresa (que, neste caso, tem o employee_id de 110). A consulta a ser utilizada é:

WITH RECURSIVE subordinate AS (
 SELECT  
   employee_id,
   first_name,
   last_name,
   manager_id
  FROM employee
  WHERE employee_id = 110 -- id of the top hierarchy employee (CEO)
 
  UNION ALL
 
  SELECT  
    e.employee_id,
    e.first_name,
    e.last_name,
    e.manager_id
  FROM employee e 
  JOIN subordinate s 
  ON e.manager_id = s.employee_id
)
SELECT 	
  employee_id,
  first_name,
  last_name,
  manager_id
FROM subordinate ;

Nesta consulta, criamos um CTE recursivo chamado subordinate. É a parte chave desta consulta porque ela atravessa a hierarquia de dados indo de uma linha até as linhas da hierarquia imediatamente abaixo dela.

Há duas subconsultas ligadas por um UNION ALL; a primeira subconsulta retorna a linha superior da hierarquia e a segunda retorna o nível seguinte, adicionando essas linhas ao resultado intermediário da consulta. Então a segunda subconsulta é executada novamente para retornar o próximo nível, que novamente será adicionado ao conjunto de resultados intermediários. Este processo é repetido até que nenhuma nova linha seja adicionada ao resultado intermediário.

Finalmente, a consulta principal consome os dados no CTE subordinate e retorna os dados da maneira que esperamos. Se você quiser saber mais sobre consultas recursivas em SQL, sugiro o artigo Como Encontrar Todos os Funcionários de Cada Gerente em SQL.

Exemplo #25 - Encontrar o comprimento de uma série usando Funções de Janela (Window Functions) em SQL

Suponha que tenhamos uma tabela com os dados de registro do usuário. Armazenamos informações sobre quantos usuários cadastrados em cada data. Definimos uma série de dados como a seqüência de dias consecutivos em que os usuários se registraram. Um dia em que nenhum usuário se registra quebra a série de dados. Para cada série de dados, queremos encontrar sua extensão.

A tabela abaixo mostra as séries de dados:

id	day	Registered users
1	Jan 25 2023	51
2	Jan 26 2023	46
3	Jan 27 2023	41
4	Jan 30 2023	59
5	Jan 31 2023	73
6	Feb 1 2023	34
7	Feb 2 2023	56
8	Feb 4 2023	34

Há 3 séries de dados diferentes mostradas em cores diferentes. Estamos procurando uma consulta para obter o comprimento de cada série de dados. A primeira série de dados começa em 25 de janeiro e tem um comprimento de 3 elementos, a segunda começa em 30 de janeiro e seu comprimento é de 4, e assim por diante.

A consulta é a seguinte:

WITH data_series AS (
  SELECT  	
    RANK() OVER (ORDER BY day) AS row_number,
    day, 
    day - RANK() OVER (ORDER BY day) AS series_id
 FROM	user_registration )
SELECT	
  MIN(day) AS series_start_day,
  MAX(day) AS series_end_day,
  MAX(day) - MIN (day) + 1 AS series_length
FROM	data_series
GROUP BY series_id
ORDER BY series_start_date

Na consulta anterior, o CTE tem a coluna series_id, que é um valor destinado a ser usado como um ID para as linhas da mesma série de dados. Na consulta principal, a cláusula GROUP BY series_id é usada para agregar as linhas da mesma série de dados. Então, podemos obter o início da série com MIN(day) e seu fim com MAX(day). O comprimento da série é calculado com a expressão:

      MAX(day) - MIN (day) + 1

Se você quiser ir mais fundo neste tópico, o artigo Como calcular o comprimento de uma série com SQL fornece uma explicação detalhada desta técnica.

Pratique SQL Avançado com os cursos LearnSQL.com

SQL é uma linguagem fácil de aprender e poderosa. Neste artigo, mostramos 25 exemplos de consultas avançadas a SQL. Todos eles podem ser explicados em cerca de 5 minutos, mostrando que SQL é uma linguagem acessível, mesmo quando é necessário fazer relatórios ou consultas complexas.

Se você quiser continuar aprendendo SQL, sugiro nossos cursos avançados de SQL: Funções de Janela (Window Functions) em SQL, Consultas Recursivas e Comandos GROUP BY em SQL. Todos eles cobrem áreas complexas da linguagem SQL em palavras simples e com muitos exemplos. Aumente sua habilidade e invista em si mesmo com SQL!