LAG function with sequential calculus

Question

I come to you today because I'm struggling with a query that involve the LAG function (FYI, I am using PostgreSQL). I have a table that contains the quantities of a product sold by country to another one on a monthly basis. The table is defined like this:

create table market_research.test_tonnage(
    origin text, -- Origin country
    desti text, -- Destination country
    yr int, -- Year
    mt int, -- Month
    q numeric -- quantity sold (always > 0)
)

Here is the content:

origin	desti	yr	mt	q
toto	coucou	2019	1	1.4
toto	coucou	2019	2	2.5
toto	coucou	2019	3	1.2
tata	yoyo	2018	11	5.4
tata	yoyo	2018	12	5.5
tata	yoyo	2019	1	5.2

I am trying to create a view that will add 2 calculated fields as following:

beginning_stock : Initial value of 0, then beginning_stock = ending_stock of the previous month
ending_stock : ending_stock = beginning_stock - q

origin	desti	yr	mt	q	beginning_stock	ending_stock
toto	coucou	2019	1	1.4	0	-1.4
toto	coucou	2019	2	2.5	-1.4	-3.9
toto	coucou	2019	3	1.2	-3.9	-5.1
tata	yoyo	2018	11	5.4	0	-5.4
tata	yoyo	2018	12	5.5	-5.4	-10.9
tata	yoyo	2019	1	5.2	-10.9	-16.1

I have tried many queries using the LAG function but I think that the problem comes from the sequentiality of the calculus over time. Here is an example of my attempt:

select origin,
       desti,
       yr,
       mt,
       q,
       COALESCE(lag(ending_stock, 1) over (partition by origin order by yr, mt), 0) beginning_stock,
       beginning_stock - q ending_stock    
 from market_research.test_tonnage

Thank you for your help! Max

Show us some sample table data and the expected result - as formatted text (no images.) Also show us your current query attempt. I.e. [mcve]. — jarlh, Mar 30 '21 at 09:04

score 2 · Accepted Answer · answered Mar 30 '21 at 10:05

You need a cumulative SUM() function instead of LAG():

demo:db<>fiddle

SELECT
    *,
    SUM(-q) OVER (PARTITION BY origin ORDER BY yr, mt) + q as beginning, -- 2
    SUM(-q) OVER (PARTITION BY origin ORDER BY yr, mt) as ending         -- 1
FROM my_table

Sum all quantities (because you want negative values, you can make the values negative before, of course) until the current gives you current total (ending)
Same operation without the current value (add q again, because the SUM() subtracted it already) gives the beginning.

Thank you very much sir, that' s a smart solution! :-) – Max Mar 30 '21 at 10:22 — Max, Mar 30 '21 at 10:22

LAG function with sequential calculus

1 Answers1