I have a A table to store the product name, the quantity and input day of goods in stock. In adverse conditions, invoices for the above products will be sent later. I will put the product name and invoice quantity in B table. The problem here is that I want to check the quantity of goods with invoice and without invoice. Updating goods with invoices will follow FIFO.
Example:
Table A
id | good_id | num | created_at |
---|---|---|---|
1 | 1 | 10 | 2021-09-24 |
2 | 1 | 5 | 2021-09-25 |
Table B
id | good_id | num_invoice |
---|---|---|
1 | 1 | 12 |
I solved it by creating a new C table with the same data as the A table
Table C
id | good_id | current_number | created_at | invoice_number |
---|---|---|---|---|
1 | 1 | 10 | 2021-09-24 | null |
2 | 1 | 5 | 2021-09-25 | null |
Then I get the data in table B group by good_id and store it in $data. Using php to foreach $data and check condition:
I updated C table ORDER BY created_at
DESC limit 1 as follows:
if (
tableC
.current_num
–$data['num']
< 0) then updatecurrent_number
= 0,invoice_number
=$data['num']
–tableC
.current_num
. Update value$data['num']
=$data['num']
–tableC
.current_num
if (
tableC
.current_num
–$data['num']
> 0) or (tableC
.current_num
– $data[‘num’] = 0) then updatecurrent_number
=tableC
.current_num
–$data['num']
,invoice_number
=$data['num']
.
table C after update
id | good_id | current_number | created_at | invoice_number |
---|---|---|---|---|
1 | 1 | 0 | 2021-09-24 | 10 |
2 | 1 | 3 | 2021-09-25 | 2 |
I solved the problem with php
like that. However, with a dataset of about 100,000 rows, I think the backend processing will take a long time. Can someone give me a smarter way to handle this?
Advertisement
Answer
Updated solution for MySQL 5.7:
Test case to support MySQL 5.7+, PG, MariaDB prior to 10.2.2, etc:
For MySQL 5.7:
This replaces the window function (for running SUM) and uses derived tables instead of WITH clause
.
SELECT id, good_id
, num - GREATEST(num - GREATEST(balance, 0), 0) AS num
, created_at
, GREATEST(num - GREATEST(balance, 0), 0) AS invoice_num
FROM (
SELECT MIN(t2.id) AS id, MIN(t2.num) AS num
, t2.good_id, t2.created_at
, MIN(o.num_invoice) AS num_invoice
, SUM(t1.num) - MIN(o.num_invoice) AS balance
FROM tableA AS t1
JOIN tableA AS t2
ON t1.good_id = t2.good_id
AND t1.created_at <= t2.created_at
JOIN (
SELECT good_id, SUM(num_invoice) AS num_invoice
FROM tableB
GROUP BY good_id
) AS o
ON o.good_id = t1.good_id
GROUP BY t2.good_id, t2.created_at
) AS cte2
ORDER BY created_at
;
For databases that handle functional dependence in GROUP BY
properly, we could just GROUP BY t2.id
(the primary key
of tableA
) and remove the MIN(t2.id)
and the MIN(t2.num)
.
Like this:
-- For MySQL 5.7
SELECT id, good_id
, num - GREATEST(num - GREATEST(balance, 0), 0) AS num
, created_at
, GREATEST(num - GREATEST(balance, 0), 0) AS invoice_num
FROM (
SELECT t2.id, t2.num
, t2.good_id, t2.created_at
, MIN(o.num_invoice) AS num_invoice
, SUM(t1.num) - MIN(o.num_invoice) AS balance
FROM tableA AS t1
JOIN tableA AS t2
ON t1.good_id = t2.good_id
AND t1.created_at <= t2.created_at
JOIN (
SELECT good_id, SUM(num_invoice) AS num_invoice
FROM tableB
GROUP BY good_id
) AS o
ON o.good_id = t1.good_id
GROUP BY t2.id
) AS cte2
ORDER BY created_at
;
Original Answer using window functions and WITH clause
:
Here’s a test case with PG 13, but works fine with MySQL 8 or MariaDB 10.2.2+.
Note: I left this as just a query that generates the requested detail. It’s not clear the 3rd table is necessary. This can be used to update (or create) that table, if needed.
Test case:
CTE terms:
- cte1 – calculate the total requested goods
- cte2 – Calculate the running balance based on running inventory by date
Finally, we use cte2 to determine the goods allocated and remaining, requested by the question.
WITH cte1 (good_id, num_invoice) AS (
SELECT good_id, SUM(num_invoice) AS num_invoice
FROM tableB
GROUP BY good_id
)
, cte2 AS (
SELECT a.*, o.num_invoice
, SUM(num) OVER (PARTITION BY a.good_id ORDER BY created_at) - o.num_invoice AS balance
FROM tableA AS a
JOIN cte1 AS o
ON o.good_id = a.good_id
)
SELECT id, good_id
, num - GREATEST(num - GREATEST(balance, 0), 0) AS num
, created_at
, GREATEST(num - GREATEST(balance, 0), 0) AS invoice_num
FROM cte2
ORDER BY created_at
;
The result:
+----+---------+------+------------+-------------+
| id | good_id | num | created_at | invoice_num |
+----+---------+------+------------+-------------+
| 1 | 1 | 0 | 2021-09-24 | 10 |
| 2 | 1 | 3 | 2021-09-25 | 2 |
| 3 | 1 | 7 | 2021-09-26 | 0 |
+----+---------+------+------------+-------------+
Note: I added one additional onhand entry for 7 goods (id = 3) to test an edge case.
Setup of the test case:
CREATE TABLE tableA (
id int primary key
, good_id int
, num int
, created_at date
);
CREATE TABLE tableB (
id int primary key
, good_id int
, num_invoice int
);
INSERT INTO tableA VALUES
(1,1,10,'2021-09-24')
, (2,1, 5,'2021-09-25')
, (3,1, 7,'2021-09-26')
;
INSERT INTO tableB VALUES
(1,1,12)
;