Resumo : neste tutorial, você aprenderá como usar a LEAD()
função MySQL para acessar dados da próxima linha no conjunto de resultados
Introdução à função MySQL LEAD()
A LEAD()
função é uma função de janela que permite acessar dados da próxima linha em um conjunto de resultados
Aqui está a sintaxe básica da LEAD()
função:
LEAD(expression, offset, default_value)
OVER (
PARTITION BY partition_expression
ORDER BY sort_expression
)
Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )
Nesta sintaxe:
expressão
Esta é a coluna ou expressão da qual você deseja recuperar o próximo valor.
desvio
Especifica offset
o número de linhas a serem visualizadas. Se você ignorá-lo, o padrão será 1, que é a linha imediata.
valor padrão
Este é o valor padrão se não houver próxima linha. Por exemplo, a última linha no conjunto de resultados (ou em uma partição) não terá a próxima linha.
Se você não especificar o default_value, o padrão será NULL.
PARTIÇÃO POR partição_expressão
A PARTITION BY
é uma cláusula opcional que divide o conjunto de resultados em partições às quais a LEAD()
função é aplicada de forma independente.
ORDER BY expressão_pedido
A ORDER BY
cláusula especifica a ordem em que as linhas são processadas em cada partição.
A LEAD()
função pode ser útil para consultas como encontrar o próximo valor em uma sequência ou calcular as diferenças entre o valor atual e o próximo valor em uma coluna.
Exemplos de funções LEAD() do MySQL
Vejamos alguns exemplos de uso da LEAD()
função. Usaremos a seguinte sales
tabela para demonstração:
CREATE TABLE sales(
sales_employee VARCHAR(50) NOT NULL,
fiscal_year INT NOT NULL,
sale DECIMAL(14,2) NOT NULL,
PRIMARY KEY(sales_employee,fiscal_year)
);
INSERT INTO sales(sales_employee,fiscal_year,sale)
VALUES('Bob',2016,100),
('Bob',2017,150),
('Bob',2018,200),
('Alice',2016,150),
('Alice',2017,100),
('Alice',2018,200),
('John',2016,200),
('John',2017,150),
('John',2018,250);
SELECT * FROM sales;
Linguagem de código: PHP ( php )
Saída:
+----------------+-------------+--------+
| sales_employee | fiscal_year | sale |
+----------------+-------------+--------+
| Alice | 2016 | 150.00 |
| Alice | 2017 | 100.00 |
| Alice | 2018 | 200.00 |
| Bob | 2016 | 100.00 |
| Bob | 2017 | 150.00 |
| Bob | 2018 | 200.00 |
| John | 2016 | 200.00 |
| John | 2017 | 150.00 |
| John | 2018 | 250.00 |
+----------------+-------------+--------+
1) Exemplo básico da função MySQL LEAD()
O exemplo a seguir usa a LEAD()
função para puxar as vendas da próxima linha para a linha atual:
SELECT
sales_employee,
fiscal_year,
sale,
LEAD(sale) OVER (
PARTITION BY sales_employee
ORDER BY fiscal_year
) AS next_year_sale
FROM
sales;
Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )
Saída:
+----------------+-------------+--------+----------------+
| sales_employee | fiscal_year | sale | next_year_sale |
+----------------+-------------+--------+----------------+
| Alice | 2016 | 150.00 | 100.00 |
| Alice | 2017 | 100.00 | 200.00 |
| Alice | 2018 | 200.00 | NULL |
| Bob | 2016 | 100.00 | 150.00 |
| Bob | 2017 | 150.00 | 200.00 |
| Bob | 2018 | 200.00 | NULL |
| John | 2016 | 200.00 | 150.00 |
| John | 2017 | 150.00 | 250.00 |
| John | 2018 | 250.00 | NULL |
+----------------+-------------+--------+----------------+
9 rows in set (0.00 sec)
Linguagem de código: texto simples ( texto simples )
Como funciona.
Primeiro, PARTITION BY sales_employee
divide as linhas da tabela em três partições, cada uma representando as vendas de um vendedor.
Segundo, ORDER BY fiscal_year
classifica as linhas em cada partição por ano fiscal em ordem crescente.
Portanto, a LEAD()
função retorna as vendas do ano seguinte ao ano atual para cada vendedor. Se não houver próximo ano, como para o ano de 2018, a LEAD()
função retornará NULL.
2) Usando múltiplas funções LEAD() em uma consulta
A consulta a seguir usa várias LEAD()
funções para calcular as vendas em relação ao próximo ano em porcentagem:
SELECT
sales_employee,
fiscal_year,
sale,
LEAD(sale) OVER (
PARTITION BY sales_employee
ORDER BY fiscal_year
) AS next_year_sale,
ROUND(sale - (LEAD(sale) OVER (
PARTITION BY sales_employee
ORDER BY fiscal_year)) / sale * 100, 2) 'vs_next_year (%)'
FROM
sales;
Linguagem de código: PHP ( php )
Saída:
+----------------+-------------+--------+----------------+------------------+
| sales_employee | fiscal_year | sale | next_year_sale | vs_next_year (%) |
+----------------+-------------+--------+----------------+------------------+
| Alice | 2016 | 150.00 | 100.00 | 83.33 |
| Alice | 2017 | 100.00 | 200.00 | -100.00 |
| Alice | 2018 | 200.00 | NULL | NULL |
| Bob | 2016 | 100.00 | 150.00 | -50.00 |
| Bob | 2017 | 150.00 | 200.00 | 16.67 |
| Bob | 2018 | 200.00 | NULL | NULL |
| John | 2016 | 200.00 | 150.00 | 125.00 |
| John | 2017 | 150.00 | 250.00 | -16.67 |
| John | 2018 | 250.00 | NULL | NULL |
+----------------+-------------+--------+----------------+------------------+
9 rows in set (0.00 sec)
Linguagem de código: PHP ( php )
Neste exemplo, adicionamos uma nova coluna chamada vs_next_year (%)
. Esta coluna calcula a variação percentual das vendas do ano atual para as vendas do ano seguinte usando a fórmula:
( current year's sale - next year's sale ) x 100 / next year's sale
Linguagem de código: JavaScript ( javascript )
Esta expressão calcula a variação percentual e a atribui à
coluna.vs_next_year (%)
Resumo
- Use a função MySQL
LEAD()
para recuperar o valor da próxima linha em uma sequência especificada dentro de um conjunto de resultados particionado.