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 inventory
mesa:
- A
id
é uma coluna de incremento automático . - A é a data
counted_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 inventory
tabela:
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 inventory
tabela 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 cte
expressã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 aLEAD
função de janela para obter o próximocounted_date
pedidocounted_date
, que retorna oto_date
.qty
: O número de itens no dia atual.LEAD(qty, 1) OVER (ORDER BY counted_date) AS new_qty
: usa aLEAD
função de janela para obter o número de itens no dia seguinte, que retorna onew_qty
.
Segundo, recupere os dados para cte
formar 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 daLEAD
funçã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
CTE
eLAG
para comparar linhas sucessivas na mesma tabela.