Resumo : neste tutorial, você aprenderá como usar a LAG()
função MySQL para acessar dados de uma linha anterior da linha atual no mesmo conjunto de resultados.
Introdução à função MySQL LAG()
A LAG()
função é uma função de janela que permite acessar dados de uma linha anterior em um conjunto de resultados da linha atual sem usar self-join .
Aqui está a sintaxe básica da LAG()
função:
LAG(expression,offset, default_value)
OVER (
PARTITION BY partition_expression
ORDER BY order_expresion ASC|DESC
)
Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )
expressão
É expression
uma coluna ou expressão da qual você deseja recuperar o valor anterior.
desvio
O offset
é o número de linhas para voltar da linha atual. O offset
deve ser zero ou um número inteiro positivo.
Se offset
for zero, a LAG()
função retorna a linha atual. Se você não fornecer o offset
argumento, o padrão será 1
.
valor padrão
Se não houver linha anterior, a LAG()
função retornará o default_value
. Se você omitir default_value
, a LAG()
função retornará NULL
.
Cláusula PARTITION BY
A PARTITION BY
cláusula divide o conjunto de resultados em partições às quais a LAG()
função é aplicada de forma independente.
Se você omitir a PARTITION BY
cláusula, a LAG()
função considerará todo o conjunto de resultados como uma única partição.
Cláusula ORDER BY
A ORDER BY
cláusula especifica a ordem das linhas em cada partição antes da LAG()
aplicação da função.
A LAG()
função pode ser útil para calcular a diferença entre as linhas atuais e anteriores.
Exemplos de funções MySQL LAG()
Vejamos alguns exemplos de uso da LAG()
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: SQL (linguagem de consulta estruturada) ( sql )
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 |
+----------------+-------------+--------+
9 rows in set (0.00 sec)
Linguagem de código: JavaScript ( javascript )
1) Exemplo básico de função MySQL LAG()
A consulta a seguir usa a função LAG para comparar as vendas de um ano com o anterior:
SELECT
sales_employee,
fiscal_year,
sale,
LAG(sale, 1 , 0) OVER (
PARTITION BY sales_employee
ORDER BY fiscal_year
) 'previous year sale'
FROM
sales;
Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )
Saída:
+----------------+-------------+--------+--------------------+
| sales_employee | fiscal_year | sale | previous year sale |
+----------------+-------------+--------+--------------------+
| Alice | 2016 | 150.00 | 0.00 |
| Alice | 2017 | 100.00 | 150.00 |
| Alice | 2018 | 200.00 | 100.00 |
| Bob | 2016 | 100.00 | 0.00 |
| Bob | 2017 | 150.00 | 100.00 |
| Bob | 2018 | 200.00 | 150.00 |
| John | 2016 | 200.00 | 0.00 |
| John | 2017 | 150.00 | 200.00 |
| John | 2018 | 250.00 | 150.00 |
+----------------+-------------+--------+--------------------+
9 rows in set (0.00 sec)
Linguagem de código: JavaScript ( javascript )
Como funciona.
A função LAG() divide as linhas da tabela de vendas por vendedores em partições. Como temos três vendedores, são criadas três partições:
PARTITION BY sales_employee
Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )
Em cada partição, a função LAG() classifica as linhas por anos fiscais. Portanto, as linhas em cada partição são classificadas por coluna do ano fiscal:
ORDER BY fiscal_year
Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )
Para cada linha de uma partição, a função LAG() retorna o valor na coluna de venda da linha anterior. Se não houver linha anterior, ele retornará 0 conforme especificamos no argumento default_value da função LAG():
LAG(sale, 1 , 0)
Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )
Como resultado, a função LAG() retorna as vendas do ano anterior (ou zero) da linha atual.
2) Usando múltiplas funções LAG
Para comparar as vendas do ano “atual” com o ano anterior, você pode usar uma função adicional LAG() como segue:
SELECT
sales_employee,
fiscal_year,
sale,
LAG(sale, 1, 0) OVER (
PARTITION BY sales_employee
ORDER BY fiscal_year
) AS previous_year_sale,
sale - LAG(sale, 1, 0) OVER (
PARTITION BY sales_employee
ORDER BY fiscal_year
) AS vs_previous_year
FROM
sales;
Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )
Saída:
+----------------+-------------+--------+--------------------+------------------+
| sales_employee | fiscal_year | sale | previous_year_sale | vs_previous_year |
+----------------+-------------+--------+--------------------+------------------+
| Alice | 2016 | 150.00 | 0.00 | 150.00 |
| Alice | 2017 | 100.00 | 150.00 | -50.00 |
| Alice | 2018 | 200.00 | 100.00 | 100.00 |
| Bob | 2016 | 100.00 | 0.00 | 100.00 |
| Bob | 2017 | 150.00 | 100.00 | 50.00 |
| Bob | 2018 | 200.00 | 150.00 | 50.00 |
| John | 2016 | 200.00 | 0.00 | 200.00 |
| John | 2017 | 150.00 | 200.00 | -50.00 |
| John | 2018 | 250.00 | 150.00 | 100.00 |
+----------------+-------------+--------+--------------------+------------------+
9 rows in set (0.00 sec)
Linguagem de código: JavaScript ( javascript )
Resumo
- Use a
LAG()
função MySQL para acessar os dados da linha anterior da linha atual.