Prática de SQL: 11 exercícios práticos de funções de janela SQL com soluções detalhadas

Neste artigo, apresentamos 11 exercícios práticos envolvendo funções de janela do SQL, juntamente com soluções e explicações detalhadas.

As funções de janela do SQL são um recurso poderoso que nos permite extrair facilmente insights significativos dos nossos dados, mas poucos cursos de SQL oferecem exercícios de funções de janela do SQL. Isso dificulta bastante a prática das funções de janela. Neste artigo, apresentaremos 11 exercícios práticos com funções de janela.

Todos os exercícios mostrados neste artigo são provenientes de nossos cursos interativos Funções de Janela (Window Functions) em SQL e do Funções de Janela (Window Functions) em SQL Practice Set. O curso Funções de Janela (Window Functions) em SQL é um tutorial detalhado com mais de 200 exercícios. Recomendamos esse curso para aprender ou revisar o conhecimento das funções de janela do SQL. Funções de Janela (Window Functions) em SQL O Practice Set é um curso prático para quem já conhece as funções de janela do SQL e está buscando mais prática.

Funções de Janela (Window Functions) em SQL Em poucas palavras

As funções de janela do SQL são ferramentas que ajudam a analisar dados de diferentes maneiras. Desde o cálculo de totais em execução e médias móveis, comparando dados dentro de subconjuntos específicos e identificando os melhores desempenhos, até a obtenção de classificações e particionamentos complexos, essas funções nos permitem extrair insights mais profundos de nossos dados e realizar tarefas complexas com facilidade.

As funções de janela do SQL oferecem um kit de ferramentas versátil para análise aprimorada de dados. Esse conjunto de ferramentas inclui:

• ROW_NUMBER(), RANK(), DENSE_RANK() e NTILE() para classificar dados.
• SUM() AVG(), , e para agregações. COUNT() MAX() MIN()
• LEAD() e LAG() para comparação de dados entre linhas.
• FIRST_VALUE() e LAST_VALUE() para extrair valores de limite.

Além disso, a cláusulaOVER() possibilita o particionamento e a ordenação precisos dos dados dentro dessas funções, permitindo que os usuários realizem cálculos complexos em subconjuntos definidos de dados.

O domínio das funções de janela do SQL está se tornando cada vez mais crucial para profissionais de dados, analistas e engenheiros. Isso não só os capacita a resolver com eficiência desafios analíticos complexos, mas também proporciona uma compreensão mais profunda dos dados. A prática das funções de janela do SQL melhorará sua capacidade de criar consultas avançadas e o ajudará a descobrir novos insights a partir dos dados. Essa é uma habilidade vital no mundo atual, focado em dados.

Antes de fazer os exercícios, dê uma olhada na nossa folha de dicas sobre funções de janela, que o lembrará da lista de funções de janela e de sua sintaxe.

Exercícios práticos de SQL Funções de Janela (Window Functions) em SQL: Loja de filmes on-line

Antes de começarmos os exercícios, vamos dar uma olhada no conjunto de dados que usaremos.

Conjunto de dados

Os exercícios a seguir usam o banco de dados da loja de filmes on-line, que contém seis tabelas.

• A tabela customer armazena informações sobre todos os clientes registrados. As colunas são id, first_name, last_name, join_date, e country.
• A tabela movie contém registros de todos os filmes disponíveis na loja. As colunas são id, title, release_year, genre, e editor_ranking.
• A tabela review armazena as classificações dos filmes pelos clientes. As colunas são id, rating, customer_id (faz referência à tabela) e (faz referência à tabela). customer tabela) e movie _id (faz referência à movie tabela).
• A tabela single_rental armazena informações sobre filmes que foram alugados por um determinado período de tempo pelos clientes. As colunas são id, rental_date, rental_period, platform, customer_id (faz referência à tabela), (faz referência à tabela) e (faz referência à tabela). customer tabela), movie _id (faz referência à tabela), movie tabela), payment_date, e payment_amount.
• A tabela subscription armazena registros de todos os clientes que se inscreveram na loja. As colunas são id, length (em dias), start_date, platform, payment_date, payment_amount, e customer_id (faz referência à tabela). customer tabela).
• A tabela giftcard contém informações sobre os cartões-presente comprados. As colunas são id, amount_worth, customer_id (faz referência à tabela), e (faz referência à tabela). customer tabela), payment_date e payment_amount.

Agora que estamos familiarizados com o conjunto de dados, vamos prosseguir com os exercícios práticos de SQL.

Exercício 1: Classificar os aluguéis por preço

Exercício:

Para cada aluguel, mostre o endereço rental_date, o título do filme alugado, seu gênero, o valor do pagamento e a classificação do aluguel em termos do preço pago (o aluguel mais caro deve ter classificação = 1). A classificação deve ser criada separadamente para cada gênero de filme. Permita a mesma classificação para várias linhas e permita intervalos na numeração.

Solução:

SELECT
  rental_date,
  title,
  genre,
  payment_amount,
  RANK() OVER(PARTITION BY genre ORDER BY payment_amount DESC)
FROM movie
JOIN single_rental
  ON single_rental.movie_id = movie.id;

Explicação da solução:

A instrução nos diz para mostrar determinadas informações sobre aluguéis e filmes individuais. Assim, juntamos a tabela single_rental com a tabela movie em sua coluna comum (ou seja, a coluna movie_id ).

Em seguida, precisamos classificar todas as locações em termos do preço pago por locação. Para isso, usamos RANK(). Em seguida, na cláusula OVER(), ordenamos os dados pela coluna payment_amount em ordem decrescente, de modo que o aluguel mais caro tenha a classificação 1.

Como a classificação deve ser criada separadamente para cada gênero de filme, na cláusula OVER(), particionamos os dados pela coluna de gênero.

Por que escolhemos RANK() em vez de DENSE_RANK() ou ROW_NUMBER()? A instrução diz que a mesma classificação para várias linhas é permitida; portanto, reduzimos as opções para RANK() e DENSE_RANK(). A função ROW_NUMBER() atribui números consecutivos como classificações a linhas sucessivas; ela não permite várias linhas com a mesma classificação.

São permitidas lacunas na numeração das linhas, portanto, precisamos da função RANK(). DENSE_RANK() não pula nenhum número em uma sequência, mesmo que várias linhas tenham a mesma classificação. A tabela a seguir apresenta essas funções de classificação e como elas funcionam com uma lista de valores de dados:

VALUE	ROW_NUMBER()	RANK()	DENSE_RANK()
Apple	1	1	1
Apple	2	1	1
Apple	3	1	1
Carrot	4	4	2
Banana	5	5	3
Banana	6	5	3
Peach	7	7	4
Tomato	8	8	5

Consulte este artigo para saber mais sobre as diferentes funções de classificação.

Exercício 2: Encontrar^{o segundo} cliente que compra um cartão-presente

Exercício:

Mostre o nome e o sobrenome do cliente que comprou o segundo cartão-presente mais recente, juntamente com a data em que o pagamento foi efetuado. Suponha que seja atribuída uma classificação exclusiva para cada compra de cartão-presente.

Solução:

WITH ranking AS (
  SELECT
    first_name,
    last_name,
    payment_date,
    ROW_NUMBER() OVER(ORDER BY payment_date DESC) AS rank
  FROM customer
  JOIN giftcard
    ON customer.id = giftcard.customer_id
)

SELECT
  first_name,
  last_name,
  payment_date
FROM ranking
WHERE rank = 2;

Explicação da solução:

Vamos mostrar informações sobre os clientes e suas compras com cartão-presente, portanto, precisamos unir a tabela customer com a tabela giftcard em sua coluna comum, (customer_id).

A instrução diz para encontrar o cliente que comprou o segundo cartão-presente mais recente. Para fazer isso, vamos primeiro classificar as compras com cartão-presente usando a função ROW_NUMBER(); supomos que uma classificação exclusiva seja atribuída a cada compra com cartão-presente.

A instrução interna SELECT seleciona as informações do cliente e as datas de suas compras com cartão-presente. Em seguida, classificamos as linhas usando a função ROW_NUMBER() para marcar a segunda compra de cartão-presente mais recente (ou seja, o valor de classificação 2).

Essa instrução interna SELECT é uma expressão de tabela comum (CTE). Ela está contida na cláusula WITH e é denominada ranking. Selecionamos os dados relevantes desse CTE e fornecemos uma condição na cláusula WHERE para gerar apenas a linha com classificação igual a 2.

Por que precisamos definir um CTE e depois consultá-lo? Porque não podemos usar a coluna de classificação na cláusula WHERE do SELECT interno. O motivo é a ordem de execução, que é: FROM, JOINs, WHERE, GROUP BY, HAVING, SELECT, DISTINCT, ORDER BY, e LIMIT. Portanto, a coluna de classificação ainda não está definida no momento em que a cláusula WHERE do SELECT interno seria executada.

Exercício 3: Calcular o total em execução para pagamentos

Exercício:

Para cada aluguel individual, mostre id, rental_date, payment_amount e o total em execução de payment_amounts de todos os aluguéis, desde o mais antigo (em termos de rental_date) até a linha atual.

Solução:

SELECT
  id,
  rental_date,
  payment_amount,
  SUM(payment_amount) OVER(
    ORDER BY rental_date
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
FROM single_rental;

Explicação da solução:

Primeiro, selecionamos as informações sobre cada aluguel individual da tabela single_rental tabela.

Em seguida, encontraremos o total acumulado dos valores de pagamento de todos os aluguéis usando a função SUM() (que usa a coluna payment_amount como argumento) com a cláusula OVER(). Aqui está um artigo que explica os detalhes sobre o total em execução e como calculá-lo no SQL.

A instrução diz para encontrar o total em execução desde a data de aluguel mais antiga até a data da linha atual. Portanto, na cláusula OVER(), precisamos ordenar os dados pela coluna rental_date e, em seguida, definir ROWS para ser contado no total em execução, a partir da data mais antiga (BETWEEN UNBOUNDED PRECEDING) até a data atual (AND CURRENT ROW).

Exercícios práticos de SQL Funções de Janela (Window Functions) em SQL: Clínica de saúde

Conjunto de dados

Os exercícios a seguir usam um banco de dados de uma clínica de saúde que contém duas tabelas.

• A tabela doctor armazena informações sobre os médicos. As colunas são id, first_name, last_name e age.
• A tabela procedure A tabela contém informações sobre os procedimentos realizados pelos médicos nos pacientes. As colunas são id, procedure_date, doctor_id (faz referência à doctor tabela), patient_id, category, name, price, e score.

Agora que já estamos familiarizados com o conjunto de dados, vamos prosseguir com os exercícios práticos de SQL.

Exercício 4: Calcular média móvel para pontuações

Exercício:

Para cada procedimento, mostre as seguintes informações: procedure_date, doctor_id, category, name, score e a pontuação média dos procedimentos da mesma categoria que estão incluídos no quadro da janela a seguir: as duas linhas anteriores, a linha atual e as três linhas seguintes em termos da data do procedimento.

Solução:

SELECT
  procedure_date,
  doctor_id,
  category,
  name,
  score,
  AVG(score) OVER(
    PARTITION BY category
    ORDER BY procedure_date
    ROWS BETWEEN 2 PRECEDING AND 3 FOLLOWING)
FROM procedure;

Explicação da solução:

Vamos mostrar as informações de cada procedimento selecionando na tabela procedure tabela.

Em seguida, encontraremos a pontuação média dos procedimentos na mesma categoria. Para fazer isso, usamos a função AVG() com a coluna de pontuação como argumento. Isso é seguido pela cláusula OVER(), na qual particionamos o conjunto de dados em categorias.

Além disso, devemos considerar apenas as linhas incluídas no quadro da janela a seguir: as duas linhas anteriores, a linha atual e as três linhas seguintes em termos da data do procedimento. Definimos esse quadro de dados na cláusula OVER(). Primeiro, ordenamos o conjunto de dados pela coluna procedure_date para que os procedimentos sejam listados cronologicamente. Em seguida, definimos as linhas a serem consideradas para calcular o valor da pontuação média: duas linhas anteriores (BETWEEN 2 PRECEDING) e três linhas seguintes (AND 3 FOLLOWING), incluindo a linha atual.

Isso é conhecido como média móvel. Você pode saber mais em O que é uma média móvel e como calculá-la no SQL.

Exercício 5: Encontrar a diferença entre os preços dos procedimentos

Exercício:

Para cada procedimento, mostre as seguintes informações: id procedure_date , name, price, price do procedimento anterior (em termos de id) e a diferença entre esses dois valores. Nomeie as duas últimas colunas como previous_price e difference.

Solução:

SELECT
  id,
  procedure_date,
  name,
  price,
  LAG(price) OVER(ORDER BY id) AS previous_price,
  price - LAG(price) OVER(ORDER BY id) AS difference
FROM procedure;

Explicação da solução:

Novamente, começamos selecionando informações sobre cada procedimento da tabela procedure tabela.

A instrução diz para mostrar o preço do procedimento anterior. Para fazer isso, usamos a função LAG(), que retorna o valor da linha anterior para seu argumento (aqui, para a coluna price ). Para garantir que escolhemos o preço do procedimento anterior em termos de id, ordenamos o conjunto de dados pela coluna id na cláusula OVER(). O pseudônimo é previous_price.

Agora que temos o valor do preço e o valor do preço anterior, podemos selecionar a diferença entre esses dois valores. Simplesmente subtraímos a função LAG() da coluna price e colocamos o alias como difference.

Consulte Como calcular a diferença entre duas linhas no SQL para saber mais.

Exercício 6: Encontrar a diferença entre o preço atual e o melhor preço

Exercício:

Para cada procedimento, mostre o:

• procedure_date
• name
• price
• category
• score
• Preço do melhor procedimento (em termos de pontuação) da mesma categoria (coluna best_procedure).
• Diferença entre esse price e o best_procedure (coluna difference).

Solução:

SELECT 
  procedure_date, 
  name, 
  price,
  category,
  score, 
  FIRST_VALUE(price) OVER(PARTITION BY category ORDER BY score DESC)
     AS best_procedure,
  price - FIRST_VALUE(price) OVER(PARTITION BY category 
     ORDER BY score DESC) AS difference
FROM procedure;

Explicação da solução:

Começamos selecionando informações sobre cada procedimento da tabela de procedimentos.

A próxima etapa é encontrar o preço do melhor procedimento. Usamos a função FIRST_VALUE(), que retorna o primeiro valor em uma partição ordenada de um conjunto de resultados. Para obter o preço do melhor procedimento da mesma categoria, devemos particionar o conjunto de dados pela coluna category. E para obter o preço do melhor procedimento em termos de pontuação, devemos ordenar o conjunto de dados pela coluna de pontuação em ordem decrescente. O nome dessa expressão é best_procedure.

Por fim, encontramos a diferença entre price e best_procedure subtraindo a função FIRST_VALUE() da coluna de preço.

Exercício 7: Encontrar o melhor médico por procedimento

Exercício:

Descubra qual médico é o melhor em cada procedimento. Para cada procedimento, selecione o nome do procedimento e o nome e sobrenome de todos os médicos que obtiveram pontuações altas (maiores ou iguais à pontuação média para esse procedimento). Classifique os médicos por procedimento em termos do número de vezes que realizaram esse procedimento. Em seguida, mostre os melhores médicos para cada procedimento, ou seja, aqueles com classificação 1.

Solução:

WITH cte AS (
  SELECT
    name,
    first_name,
    last_name,
    COUNT(*) c,
    RANK() OVER(PARTITION BY name ORDER BY count(*) DESC) AS rank
  FROM procedure p 
  JOIN doctor d
    ON p.doctor_id = d.id
  WHERE score >= (SELECT avg(score) 
                  FROM procedure pl 
                  WHERE pl.name = p.name)
  GROUP BY name, first_name, last_name
)

SELECT 
  name,
  first_name,
  last_name
FROM cte
WHERE rank = 1;

Explicação da solução:

Primeiro, selecionamos o nome do procedimento e as informações sobre os médicos, então juntamos a tabela procedure com a tabela doctor em sua coluna comum (doctor_id).

Queremos selecionar todos os médicos que obtiveram pontuações altas (maiores ou iguais à pontuação média para esse procedimento). Para fazer isso, definimos a condição da cláusula WHERE para a coluna de pontuação. A coluna score deve armazenar um valor igual ou maior que a pontuação média do procedimento da linha atual.

Vamos classificar os médicos por procedimento. Usaremos a função RANK() com a cláusula OVER(), na qual particionamos o conjunto de dados pelo nome do procedimento. Além disso, devemos classificar em termos do número de vezes que o médico realizou esse procedimento. Para obter o número de vezes que o médico realizou esse procedimento, devemos COUNT(*) enquanto agrupamos pelo nome do procedimento e pelo primeiro e último nome do médico (ou seja, estamos agrupando por todas as colunas listadas na instrução SELECT ).

Tudo o que fizemos até agora foi definir uma expressão de tabela comum (Common Table Expression, CTE), que é a instrução interna SELECT delimitada pela cláusula WITH e denominada cte.

Agora, selecionamos as colunas relevantes desse CTE. Para obter os melhores médicos para cada procedimento (aqueles com classificação 1), definimos a cláusula WHERE com a condição para a coluna rank.

Por que precisamos definir um CTE e depois consultá-lo? Porque não podemos usar a coluna rank na cláusula WHERE do SELECT interno. O motivo é a ordem de execução, que é: FROM, JOINs, WHERE, GROUP BY, HAVING, SELECT, DISTINCT, ORDER BY, e LIMIT. A coluna rank não foi definida quando a cláusula WHERE foi executada.

SQL Funções de Janela (Window Functions) em SQL Exercícios práticos: Campeonatos esportivos

Conjunto de dados

Os exercícios a seguir usam o banco de dados de campeonatos esportivos que contém oito tabelas.

• A tabela competition armazena informações sobre competições. As colunas são id, name, start_date, end_date, year, e location.
• A tabela de disciplinas armazena informações de todas as disciplinas de corrida (desde corridas de curta distância (por exemplo, 100 metros) até corridas de longa distância (por exemplo, maratona)). As colunas são id, name, is_men e distance.
• A tabela event armazena informações sobre a competição e a disciplina de cada evento. As colunas são id, competition_id (faz referência à competition table) e discipline_id (faz referência à discipline tabela).
• A tabela round tabela armazena as rodadas de cada evento. As colunas são id, event_id (faz referência à tabela), , e . event tabela), round_name, round_number, e is_final.
• A tabela race tabela armazena os dados de cada corrida de cada rodada. As colunas são id, round_id (faz referência à round tabela), round_name (o mesmo que na round tabela), race_number, race_date, is_final (o mesmo que na tabela) e . round tabela) e wind.
• A tabela athlete armazena informações sobre os atletas que participam da competição. As colunas são id, first_name, last_name, nationality_id (faz referência à tabela) e . nationality tabela) e birth_date.
• A tabela nationality tabela armazena informações sobre os países de origem dos atletas. As colunas são id, country_name e country_abbr.
• A tabela result armazena informações de todos os participantes de um determinado evento. As colunas são race_id (faz referência à race tabela), athlete_id (faz referência à athlete tabela), result, place, is_dsq, is_dns, e is_dnf.

Agora que estamos familiarizados com o conjunto de dados, vamos prosseguir para os exercícios práticos de SQL.

Exercício 8: Calcular a diferença entre as médias diárias de velocidade do vento

Exercício:

Para cada data em que houve uma corrida, exiba o endereço race_date, a média do vento nessa data arredondada para três casas decimais e a diferença entre a velocidade média do vento nessa data e a velocidade média do vento na data anterior, também arredondada para três casas decimais. As colunas devem ser nomeadas race_date, avg_wind e avg_wind_delta.

Solução:

SELECT
  race_date,
  ROUND(AVG(wind), 3) AS avg_wind,
  ROUND(AVG(wind) - LAG(AVG(wind)) OVER(ORDER BY race_date), 3) 
     AS avg_wind_delta
FROM race
GROUP BY race_date;

Explicação da solução:

Vamos exibir as informações da corrida para cada data de corrida, portanto, selecionamos os dados da tabela race tabela.

Para encontrar a velocidade média do vento nessa data, arredondada para três casas decimais, usamos a função AVG() com a coluna wind como argumento. Em seguida, incluímos o valor na função ROUND() e o arredondamos para três casas decimais. Observe que devemos agrupar pela coluna race_date, pois usamos a função de agregação AVG().

Podemos obter o vento médio na data anterior usando a função LAG() com o valor AVG(wind) como argumento. A cláusula OVER() define que ordenamos todo o conjunto de dados pela coluna race_date para que as linhas de dados sejam listadas cronologicamente.

Como queremos ver a diferença entre a velocidade média do vento nessa data e a velocidade média do vento na data anterior, subtraímos LAG(AVG(wind)) de AVG(wind). E para arredondar para três casas decimais, usamos a função ROUND() novamente.

Exercício 9: Comparar os melhores resultados com os anteriores

Exercício:

Para cada mulher que correu na rodada final da maratona feminina no Rio, exiba as seguintes informações:

• O lugar que elas alcançaram na corrida.
• Seu primeiro nome.
• O sobrenome.
• comparison_to_best - A diferença entre o tempo dela e o melhor tempo nessa final.
• comparison_to_previous - A diferença entre o tempo delas e o resultado da atleta que obteve o próximo melhor tempo

Classifique as linhas pela coluna place.

Solução:

SELECT
  place,
  first_name,
  last_name,
  result - FIRST_VALUE(result) OVER (ORDER BY result) 
     AS comparison_to_best,
  result - LAG(result) OVER(ORDER BY result) 
     AS comparison_to_previous
FROM competition
JOIN event
  ON competition.id = event.competition_id
JOIN discipline
  ON discipline.id = event.discipline_id
JOIN round
  ON event.id = round.event_id
JOIN race
  ON round.id = race.round_id
JOIN result
  ON result.race_id = race.id 
JOIN athlete
  ON athlete.id = result.athlete_id
WHERE competition.name = 'Rio de Janeiro Olympic Games'
  AND discipline.name = 'Women''s Marathon'
  AND round.is_final IS TRUE
ORDER BY place;

Explicação da solução:

Vamos usar informações sobre competições, disciplinas, rodadas, atletas e resultados. Portanto, devemos unir todas essas tabelas em suas colunas comuns, conforme mencionado na introdução do conjunto de dados.

A instrução diz para exibir informações de cada mulher que correu na rodada final da maratona feminina no Rio. Cobrimos isso na cláusula WHERE que contém as seguintes condições:

• O nome da competição deve ser Rio de Janeiro Olympic Games.
• O nome da disciplina deve ser Women's Marathon.
• A rodada deve ser a rodada final.

Em seguida, selecionamos a coluna place da tabela result e as colunas first_name e last_name da tabela athlete tabela.

Para encontrar a diferença entre o tempo deles e o melhor tempo nessa final, usamos a função FIRST_VALUE() com a coluna result como argumento. Isso é seguido pela cláusula OVER(), que ordena o conjunto de dados pela coluna result. Em seguida, subtraímos essa função FIRST_VALUE() da linha atual result. O pseudônimo é comparison_to_best.

Para encontrar a diferença entre o tempo deles e o resultado do atleta que obteve a próxima melhor colocação, usamos a função LAG() com a coluna result como argumento para obter o resultado anterior. Mais uma vez, isso é seguido pela cláusula OVER() para ordenar o conjunto de dados pela coluna result (para garantir que obtenhamos o próximo melhor resultado). Em seguida, subtraímos essa função LAG() da linha atual result. O pseudônimo é comparison_to_previous.

Por fim, classificamos as linhas pela coluna place usando a cláusula ORDER BY.

Exercícios práticos de SQL Funções de Janela (Window Functions) em SQL: Estatísticas de sites

Conjunto de dados

Os exercícios a seguir usam o banco de dados de estatísticas do site que contém duas tabelas.

• A tabela website armazena informações sobre sites. As colunas são id, name, budget e opened.
• A tabela statistics armazena estatísticas para cada site. As colunas são website_id (faz referência à tabela), , , , . website tabela), day, users, impressions, clicks, e revenue.

Agora que estamos familiarizados com o conjunto de dados, vamos prosseguir para os exercícios práticos de SQL.

Exercício 10: Olhar para frente com a função LEAD()

Exercício:

Pegue as estatísticas do site com id = 2 entre 1 e 14 de maio de 2016 e mostre o dia, o número de usuários e o número de usuários 7 dias depois.

Observe que as últimas 7 linhas não têm um valor na última coluna. Isso ocorre porque nenhuma linha "7 days from now" pode ser encontrada para elas. Para esses casos, mostre -1 em vez de NULL se não for encontrado nenhum valor em LEAD().

Solução:

SELECT
  day,
  users,
  LEAD(users, 7, -1) OVER(ORDER BY day)
FROM statistics
WHERE website_id = 2
  AND day BETWEEN '2016-05-01' AND '2016-05-14';

Explicação da solução:

Vamos mostrar o dia, o número de usuários e o número de usuários 7 dias depois. Os dois primeiros valores vêm da tabela statistics essas são as colunas day e users. O último valor deve ser calculado usando a função LEAD().

Queremos ver o valor da coluna users após sete dias; portanto, passamos a coluna users como o primeiro argumento e o valor 7 como o segundo argumento para a função LEAD(). E para garantir que mostraremos -1 em vez de NULL se nenhum valor LEAD() for encontrado, passamos o terceiro argumento como -1.

A função LEAD() é seguida pela cláusula OVER(). Essa cláusula contém a condição para ordenar o conjunto de dados pela coluna do dia, pois as estatísticas devem ser ordenadas cronologicamente.

Para mostrar as estatísticas do site com id = 2 entre 1 e 14 de maio de 2016, precisamos definir as condições relevantes na cláusula WHERE.

Exercício 11: Retrospectiva com a função LAG()

Exercício:

Mostre as estatísticas do site com id = 3 que incluem dia, receita e a receita de 3 dias antes. Mostre -1,00 para as linhas sem valor de receita 3 dias antes.

Solução:

SELECT
  day,
  revenue,
  LAG(revenue, 3, -1.00) OVER(ORDER BY day)
FROM statistics
WHERE website_id = 3;

Explicação da solução:

Vamos mostrar o dia, a receita e a receita de 3 dias antes. Os dois primeiros valores vêm da tabela statistics - essas são as colunas de dia e receita. O último valor deve ser calculado usando a função LAG().

Queremos ver o valor da coluna de receita de três dias antes da linha atual; portanto, passamos a coluna de receita como o primeiro argumento e o valor de 3 como o segundo argumento para a função LAG(). E para garantir que mostraremos -1,00 para as linhas sem valor de receita 3 dias antes, passamos o terceiro argumento como -1,00.

A função LAG() é seguida pela cláusula OVER(). Ela contém a condição para ordenar o conjunto de dados pela coluna day, pois as estatísticas devem ser ordenadas cronologicamente.

Para mostrar as estatísticas do site com id = 3, precisamos definir uma condição na cláusula WHERE.

Mais prática de SQL Funções de Janela (Window Functions) em SQL

Os exercícios práticos de funções de janela SQL apresentados neste artigo fornecem uma plataforma abrangente para aprimorar suas habilidades em SQL e análise de dados, uma consulta de cada vez. Esses exercícios são provenientes de nossos cursos; para encontrar exercícios práticos adicionais, visite os cursos relacionados abaixo.

1. Funções de Janela (Window Functions) em SQL
2. Funções de Janela (Window Functions) em SQL Conjunto de práticas

Se você deseja aprender ou atualizar seus conhecimentos sobre funções de janela, sugerimos que comece com o curso Funções de Janela (Window Functions) em SQL que oferece uma exploração completa desse tópico. Para aqueles que desejam aprimorar suas habilidades em funções de janela, explore nosso conjunto de práticasFunções de Janela (Window Functions) em SQL . Ele tem 100 exercícios estruturados em três partes distintas, cada uma utilizando um conjunto de dados diferente.

Inscreva-seagora e comece gratuitamente. Boa sorte!