How to calculate the moving average cost?

Question

MySQL

Hello. Tell me how to recalculate the cost of each sale when deleting an invoice for the receipt of goods.
It was:
(purchase) +10kg at 10r each (in stock now 10kg at 10r each)
(purchase) +10kg at 15r each (in stock now 20kg at 12.5r each)
(sale) -5kg (cost price 62, 5R, in stock 15kg at 12.5r each)
(sales) -3kg (cost price 37.5R, in stock 12 kg at 12.5 each)
(purchase) +8kg at 20r each (in stock 20kg at 15.5 rubles each )
(sale) -5kg (cost price 77.5R, in stock 15 kg at 15.5 rubles each)
We remove the supply of 10 kg at 10r. How to recalculate all deliveries and the entire cost of all subsequent sales. And what is the best way to have a mysql table schema for this

Answer 1

Why count all deliveries? Record incoming and outgoing transactions (with the cost of the goods). Return of goods is a type of expense transaction at cost. It makes no sense to save and record the average price, calculate it in real time based on what product you have left in stock and at the price of the last transactions. You have 15 kg of goods left after the last sale (15 units). If the warehouse works according to FIFO, then we have already sold the first units of goods, which means that the remaining units arrived in transactions +8kg for 20 rubles and +10kg for 15 rubles. We take 8 from the last transaction and 7 from the penultimate transaction and calculate the average cost.

Answer 2

Everything has already been thought of before us.
As in 1C.
www.script-coding.com/v77tables.html
What you need to store cost is called "registers"