Como comparar linhas sucessivas na mesma tabela no MySQL

Resumo : neste tutorial, você aprenderá como comparar linhas sucessivas na mesma tabela no MySQL.

Configurando dados de amostra

Primeiro, crie uma nova tabela chamada inventory:

CREATE TABLE inventory(
  id INT AUTO_INCREMENT PRIMARY KEY,
  counted_date date NOT NULL,
  item_no VARCHAR(20) NOT NULL,
  qty INT NOT NULL
);Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

Na inventorymesa:

• A  idé uma coluna de incremento automático .
• A é a datacounted_date contada  .
• O  item_noé o código do item lançado no estoque.
• A  qtyé a quantidade disponível acumulada no estoque.

Segundo, insira algumas linhas na inventory tabela:

INSERT INTO inventory(counted_date,item_no,qty)
VALUES ('2014-10-01','A',20),
	   ('2014-10-02','A',30),
	   ('2014-10-03','A',45),
	   ('2014-10-04','A',80),
	   ('2014-10-05','A',100);Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

Terceiro, recupere os dados da inventorytabela:

SELECT * FROM inventory;

Saída:

+----+--------------+---------+-----+
| id | counted_date | item_no | qty |
+----+--------------+---------+-----+
|  1 | 2014-10-01   | A       |  20 |
|  2 | 2014-10-01   | A       |  30 |
|  3 | 2014-10-01   | A       |  45 |
|  4 | 2014-10-01   | A       |  80 |
|  5 | 2014-10-01   | A       | 100 |
+----+--------------+---------+-----+
5 rows in set (0.00 sec)Linguagem de código:  JavaScript  ( javascript )

Se quiser determinar a quantidade recebida do item A de um determinado dia, será necessário comparar a quantidade disponível desse dia com o dia anterior.

Em outras palavras, você precisa comparar cada linha com sua linha consecutiva na inventorytabela para calcular a diferença.

Comparando a linha atual com a próxima linha da mesma tabela

A consulta a seguir usa uma Common Table Expression (CTE) e a função de janela LAG para calcular a quantidade recebida de itens por dia comparando a quantidade disponível de um determinado dia com seus dias sucessivos:

WITH cte AS (
  SELECT 
    item_no, 
    counted_date from_date, 
    LEAD(counted_date, 1) OVER ( ORDER BY counted_date) to_date, 
    qty, 
    LEAD(qty, 1) OVER (ORDER BY counted_date) new_qty 
  FROM 
    inventory
) 
SELECT 
  item_no, 
  from_date, 
  to_date, 
  (new_qty - qty) AS received_qty 
FROM 
  cte 
WHERE 
  to_date IS NOT NULL;Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

Saída:

+---------+------------+------------+--------------+
| item_no | from_date  | to_date    | received_qty |
+---------+------------+------------+--------------+
| A       | 2014-10-01 | 2014-10-02 |           10 |
| A       | 2014-10-02 | 2014-10-03 |           15 |
| A       | 2014-10-03 | 2014-10-04 |           35 |
| A       | 2014-10-04 | 2014-10-05 |           20 |
+---------+------------+------------+--------------+
4 rows in set (0.00 sec)Linguagem de código:  JavaScript  ( javascript )

Como funciona.

Primeiro, defina a cteexpressão de tabela comum para simplificar a consulta subsequente:

WITH cte AS (
  SELECT 
    item_no, 
    counted_date from_date, 
    LEAD(counted_date, 1) OVER ( ORDER BY counted_date) to_date, 
    qty, 
    LEAD(qty, 1) OVER (ORDER BY counted_date) new_qty 
  FROM 
    inventory
) Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

O CTE seleciona as seguintes colunas da tabela de inventário:

• item_no: O número do item.
• counted_date: A data da contagem.
• LEAD(counted_date, 1) OVER (ORDER BY counted_date) AS TO_DATE: Ele usa a LEADfunção de janela para obter o próximo counted_datepedido counted_date, que retorna o to_date.
• qty: O número de itens no dia atual.
• LEAD(qty, 1) OVER (ORDER BY counted_date) AS new_qty: usa a LEADfunção de janela para obter o número de itens no dia seguinte, que retorna o new_qty.

Segundo, recupere os dados para cteformar o conjunto de resultados desejado:

SELECT 
  item_no, 
  from_date, 
  to_date, 
  (new_qty - qty) AS received_qty 
FROM 
  cte 
WHERE 
  to_date IS NOT NULL;Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

A consulta seleciona colunas de cte:

• item_no: O número do item.
• from_date: A data contada, que é considerada a data de início.
• to_date: A data contada do dia seguinte, obtida através da LEADfunção no CTE.
• new_qty - qty AS received_qty: Calcula a quantidade recebida subtraindo a quantidade do dia atual ( qty) da quantidade do dia seguinte ( new_qty).
• WHERE to_date IS NOT NULL: Filtra os resultados para excluir o último dia onde não há dia seguinte.

Resumo

• Use uma função de janela CTEe LAGpara comparar linhas sucessivas na mesma tabela.