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 offseto 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 BYclá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 salestabela 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_employeedivide as linhas da tabela em três partições, cada uma representando as vendas de um vendedor.
Segundo, ORDER BY fiscal_yearclassifica 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 saleLinguagem de código: JavaScript ( javascript )Esta expressão calcula a variação percentual e a atribui à 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.
