MENU
  1. Início
  2. SQL
  3. Como obter o primeiro e o último valor usando as funções FIRST_VALUE e LAST_VALUE em SQL

Como obter o primeiro e o último valor usando as funções FIRST_VALUE e LAST_VALUE em SQL

SQL

Na análise de dados e na elaboração de relatórios usando SQL, muitas vezes é necessário obter o primeiro ou o último valor de um conjunto de dados. Para facilitar isso, as funções FIRST_VALUE e LAST_VALUE são muito úteis. Neste artigo, vamos detalhar como usar essas funções para manipular dados de forma eficiente, incluindo exemplos práticos.

Índice

Uso básico da função FIRST_VALUE

A função FIRST_VALUE é utilizada para obter o primeiro valor dentro de uma janela ou partição especificada. Isso permite extrair facilmente o valor da primeira linha do conjunto de dados.

Sintaxe básica

SELECT 
    column1,
    FIRST_VALUE(column1) OVER (PARTITION BY column2 ORDER BY column3) AS first_value
FROM 
    table_name;

Neste exemplo, obtemos o primeiro valor de column1 após particionar os dados por column2 e ordená-los por column3.

Exemplo de uso

Por exemplo, se quisermos obter os nomes dos primeiros funcionários contratados em cada departamento em um banco de dados de funcionários, usamos a seguinte consulta:

SELECT 
    department,
    employee_name,
    FIRST_VALUE(employee_name) OVER (PARTITION BY department ORDER BY hire_date) AS first_hired
FROM 
    employees;

Esta consulta retorna o nome do funcionário que foi contratado primeiro em cada departamento.

Exemplos de aplicação da função FIRST_VALUE

A função FIRST_VALUE não é útil apenas em casos simples, mas também em consultas e análises mais complexas. Aqui, apresentamos exemplos aplicados a um conjunto de dados real.

Obter a primeira data de venda dos dados de vendas

Por exemplo, se quisermos obter a primeira data de venda de cada loja a partir dos dados de vendas de um produto, usamos a seguinte consulta:

SELECT 
    store_id,
    product_id,
    sale_date,
    FIRST_VALUE(sale_date) OVER (PARTITION BY store_id, product_id ORDER BY sale_date) AS first_sale_date
FROM 
    sales;

Esta consulta retorna a primeira data de venda para cada loja e cada produto.

Obter o primeiro resultado de exame dos dados de notas de estudantes

Quando gerenciamos dados de notas de estudantes, podemos obter o primeiro resultado de exame de cada estudante.

SELECT 
    student_id,
    exam_date,
    score,
    FIRST_VALUE(score) OVER (PARTITION BY student_id ORDER BY exam_date) AS first_exam_score
FROM 
    exam_results;

Esta consulta retorna a primeira pontuação de exame de cada estudante.

Obter o primeiro valor de compra dos dados de transações

Este exemplo mostra como obter o primeiro valor de compra a partir dos dados de transações de clientes.

SELECT 
    customer_id,
    transaction_date,
    amount,
    FIRST_VALUE(amount) OVER (PARTITION BY customer_id ORDER BY transaction_date) AS first_purchase_amount
FROM 
    transactions;

Esta consulta retorna o primeiro valor de compra de cada cliente.

Uso básico da função LAST_VALUE

A função LAST_VALUE é usada para obter o último valor dentro de uma janela ou partição especificada. Isso permite extrair facilmente o valor da última linha do conjunto de dados.

Sintaxe básica

SELECT 
    column1,
    LAST_VALUE(column1) OVER (PARTITION BY column2 ORDER BY column3 ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS last_value
FROM 
    table_name;

Neste exemplo, obtemos o último valor de column1 após particionar os dados por column2 e ordená-los por column3. É importante usar a cláusula ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING para especificar a janela inteira.

Exemplo de uso

Por exemplo, se quisermos obter os nomes dos últimos funcionários contratados em cada departamento em um banco de dados de funcionários, usamos a seguinte consulta:

SELECT 
    department,
    employee_name,
    LAST_VALUE(employee_name) OVER (PARTITION BY department ORDER BY hire_date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS last_hired
FROM 
    employees;

Esta consulta retorna o nome do funcionário que foi contratado por último em cada departamento.

Exemplos de aplicação da função LAST_VALUE

A função LAST_VALUE é tão útil quanto a função FIRST_VALUE em análises de dados e elaboração de relatórios. Aqui, apresentamos exemplos aplicados a um conjunto de dados real.

Obter a última data de venda dos dados de vendas

Por exemplo, se quisermos obter a última data de venda de cada loja a partir dos dados de vendas de um produto, usamos a seguinte consulta:

SELECT 
    store_id,
    product_id,
    sale_date,
    LAST_VALUE(sale_date) OVER (PARTITION BY store_id, product_id ORDER BY sale_date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS last_sale_date
FROM 
    sales;

Esta consulta retorna a última data de venda para cada loja e cada produto.

Obter o último resultado de exame dos dados de notas de estudantes

Quando gerenciamos dados de notas de estudantes, podemos obter o último resultado de exame de cada estudante.

SELECT 
    student_id,
    exam_date,
    score,
    LAST_VALUE(score) OVER (PARTITION BY student_id ORDER BY exam_date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS last_exam_score
FROM 
    exam_results;

Esta consulta retorna a última pontuação de exame de cada estudante.

Obter o último valor de compra dos dados de transações

Este exemplo mostra como obter o último valor de compra a partir dos dados de transações de clientes.

SELECT 
    customer_id,
    transaction_date,
    amount,
    LAST_VALUE(amount) OVER (PARTITION BY customer_id ORDER BY transaction_date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS last_purchase_amount
FROM 
    transactions;

Esta consulta retorna o último valor de compra de cada cliente.

Combinação de FIRST_VALUE e LAST_VALUE

Ao combinar as funções FIRST_VALUE e LAST_VALUE, é possível obter simultaneamente os primeiros e últimos valores de um conjunto de dados. Isso possibilita, por exemplo, comparar os valores no início e no final de um período, permitindo uma análise mais avançada.

Obter a primeira e a última data de venda dos dados de vendas

Por exemplo, para obter a primeira e a última data de venda de cada loja a partir dos dados de vendas, a consulta é a seguinte:

SELECT 
    store_id,
    product_id,
    FIRST_VALUE(sale_date) OVER (PARTITION BY store_id, product_id ORDER BY sale_date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS first_sale_date,
    LAST_VALUE(sale_date) OVER (PARTITION BY store_id, product_id ORDER BY sale_date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS last_sale_date
FROM 
    sales;

Esta consulta retorna a primeira e a última data de venda para cada loja e cada produto.

Obter a primeira e a última data de contratação de funcionários

Quando obtemos a primeira e a última data de contratação em um banco de dados de funcionários, usamos a seguinte consulta:

SELECT 
    department,
    employee_name,
    FIRST_VALUE(hire_date) OVER (PARTITION BY department ORDER BY hire_date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS first_hire_date,
    LAST_VALUE(hire_date) OVER (PARTITION BY department ORDER BY hire_date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS last_hire_date
FROM 
    employees;

Esta consulta retorna o nome do funcionário que foi contratado primeiro e o que foi contratado por último em cada departamento.

Obter o primeiro e o último valor de compra de transações de clientes

Esta consulta é usada para obter o primeiro e o último valor de compra a partir dos dados de transações de clientes.

SELECT 
    customer_id,
    FIRST_VALUE(amount) OVER (PARTITION BY customer_id ORDER BY transaction_date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS first_purchase_amount,
    LAST_VALUE(amount) OVER (PARTITION BY customer_id ORDER BY transaction_date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS last_purchase_amount
FROM 
    transactions;

Esta consulta retorna o primeiro e o último valor de compra de cada cliente.

Uso como funções de janela

As funções FIRST_VALUE e LAST_VALUE podem ser usadas como funções de janela, permitindo uma análise e manipulação de dados mais flexíveis. Usadas como funções de janela, podem calcular os primeiros e últimos valores para cada linha e retornar isso como resultado.

Conceito básico de funções de janela

As funções de janela realizam cálculos sobre o conjunto de resultados de uma consulta e se aplicam a linhas dentro de uma janela específica. A janela é definida usando as cláusulas PARTITION BY e ORDER BY.

Sintaxe básica

SELECT 
    column1,
    FIRST_VALUE(column1) OVER (PARTITION BY column2 ORDER BY column3 ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS first_value,
    LAST_VALUE(column1) OVER (PARTITION BY column2 ORDER BY column3 ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS last_value
FROM 
    table_name;

Nesta sintaxe, particionamos os dados por column2 e obtemos os primeiros e últimos valores de column1 após ordená-los por column3.

Exemplo de uso: funções de janela em dados de vendas

Por exemplo, para obter a primeira e a última data de venda usando funções de janela nos dados de vendas de cada loja, usamos a seguinte consulta:

SELECT 
    store_id,
    product_id,
    sale_date,
    FIRST_VALUE(sale_date) OVER (PARTITION BY store_id ORDER BY sale_date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS first_sale_date,
    LAST_VALUE(sale_date) OVER (PARTITION BY store_id ORDER BY sale_date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS last_sale_date
FROM 
    sales;

Esta consulta retorna a primeira e a última data de venda para cada loja.

Exemplo de uso: dados de notas de estudantes

Para obter o primeiro e o último resultado de exame para cada estudante em dados de notas, usamos a seguinte consulta:

SELECT 
    student_id,
    exam_date,
    score,
    FIRST_VALUE(score) OVER (PARTITION BY student_id ORDER BY exam_date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS first_exam_score,
    LAST_VALUE(score) OVER (PARTITION BY student_id ORDER BY exam_date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS last_exam_score
FROM 
    exam_results;

Esta consulta retorna o primeiro e o último resultado de exame para cada estudante.

Uso de PARTITION BY e ORDER BY

Uma das características poderosas das funções FIRST_VALUE e LAST_VALUE é a capacidade de particionar e ordenar os dados. Isso permite obter os primeiros e últimos valores dentro de grupos específicos.

Conceito básico de particionamento

O particionamento consiste em agrupar os dados de acordo com critérios específicos. Isso permite realizar cálculos independentes dentro de cada grupo. O particionamento é realizado usando a cláusula PARTITION BY.

Conceito básico de ORDER BY

A cláusula ORDER BY é utilizada para ordenar as linhas dentro de cada partição em uma ordem específica. Isso permite identificar corretamente os primeiros e últimos valores.

Exemplo de uso de particionamento e ORDER BY

A seguir está uma consulta para obter a primeira e a última data de venda para cada loja a partir dos dados de vendas.

SELECT 
    store_id,
    product_id,
    sale_date,
    FIRST_VALUE(sale_date) OVER (PARTITION BY store_id, product_id ORDER BY sale_date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS first_sale_date,
    LAST_VALUE(sale_date) OVER (PARTITION BY store_id, product_id ORDER BY sale_date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS last_sale_date
FROM 
    sales;

Esta consulta retorna a primeira e a última data de venda para cada loja e cada produto.

Exemplo de aplicação em dados de estudantes

Para obter o primeiro e o último resultado de exame para cada estudante em dados de notas, usamos a seguinte consulta:

SELECT 
    student_id,
    exam_date,
    score,
    FIRST_VALUE(score) OVER (PARTITION BY student_id ORDER BY exam_date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS first_exam_score,
    LAST_VALUE(score) OVER (PARTITION BY student_id ORDER BY exam_date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS last_exam_score
FROM 
    exam_results;

Esta consulta retorna o primeiro e o último resultado de exame para cada estudante.

Exercícios e respostas

Para aprofundar sua compreensão das funções FIRST_VALUE e LAST_VALUE, preparamos alguns exercícios. Para cada exercício, também apresentamos uma solução, então sinta-se à vontade para experimentá-los.

Exercício 1: Obter as primeiras e últimas datas de contratação em cada departamento

Crie uma consulta para obter as primeiras e últimas datas de contratação a partir da tabela employees.

-- tabela employees
+---------+------------+------------+
| emp_id  | department | hire_date  |
+---------+------------+------------+
| 1       | Sales      | 2020-01-01 |
| 2       | Sales      | 2021-05-10 |
| 3       | HR         | 2019-03-15 |
| 4       | HR         | 2020-07-23 |
| 5       | IT         | 2021-01-05 |
+---------+------------+------------+

Exemplo de resposta

SELECT 
    department,
    FIRST_VALUE(hire_date) OVER (PARTITION BY department ORDER BY hire_date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS first_hire_date,
    LAST_VALUE(hire_date) OVER (PARTITION BY department ORDER BY hire_date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS last_hire_date
FROM 
    employees;

Exercício 2: Obter as primeiras e últimas datas de venda de cada produto

Crie uma consulta para obter as primeiras e últimas datas de venda a partir da tabela sales.

-- tabela sales
+---------+------------+------------+
| sale_id | product_id | sale_date  |
+---------+------------+------------+
| 1       | 101        | 2022-01-01 |
| 2       | 102        | 2022-01-05 |
| 3       | 101        | 2022-02-01 |
| 4       | 103        | 2022-01-10 |
| 5       | 102        | 2022-03-01 |
+---------+------------+------------+

Exemplo de resposta

SELECT 
    product_id,
    FIRST_VALUE(sale_date) OVER (PARTITION BY product_id ORDER BY sale_date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS first_sale_date,
    LAST_VALUE(sale_date) OVER (PARTITION BY product_id ORDER BY sale_date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS last_sale_date
FROM 
    sales;

Exercício 3: Obter os primeiros e últimos valores de compra de cada cliente

Crie uma consulta para obter os primeiros e últimos valores de compra a partir da tabela transactions.

-- tabela transactions
+-------------+----------+--------+--------------+
| transaction_id | customer_id | amount | transaction_date |
+-------------+----------+--------+--------------+
| 1           | 1001     | 200    | 2023-01-01   |
| 2           | 1002     | 150    | 2023-01-05   |
| 3           | 1001     | 300    | 2023-02-01   |
| 4           | 1003     | 250    | 2023-01-10   |
| 5           | 1002     | 400    | 2023-03-01   |
+-------------+----------+--------+--------------+

Exemplo de resposta

SELECT 
    customer_id,
    FIRST_VALUE(amount) OVER (PARTITION BY customer_id ORDER BY transaction_date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS first_purchase_amount,
    LAST_VALUE(amount) OVER (PARTITION BY customer_id ORDER BY transaction_date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS last_purchase_amount
FROM 
    transactions;

Conclusão

Neste artigo, aprendemos como usar as funções FIRST_VALUE e LAST_VALUE em SQL para obter os primeiros e últimos valores de um conjunto de dados. Abordamos o uso básico, exemplos práticos, uso como funções de janela e também como particionar e ordenar dados. Aproveitando essas funções, a análise de dados e a elaboração de relatórios se tornam mais eficientes. Experimente aplicar essas técnicas em seus próprios conjuntos de dados.

SQL
Análise de dados Dados de Transações Data de Venda FIRST_VALUE FUNÇÕES DE JANELA LAST_VALUE Notas de Exame ORDER BY Particionamento SQL
Índice