Search code examples
sqlpostgresqlwindow-functions

SQL Calculate the rate of growth of the indicator over time

I have a postgres table like this:

dates
date		 name
text		 values
real
2017-05-01 A 1
2017-05-02 A 3
2017-05-02 B 10
2017-05-03 A 6
2017-05-04 A 12
2017-05-03 B 10
2017-05-04 B 10
2017-05-05 B 11

how can I calculate the rate of growth of the indicator over time with SQL, and get the following table

dates
date		 name
text		 values
real		 growth
real
2017-05-01 A 1 NULL
2017-05-02 A 3 2
2017-05-02 B 10 NULL
2017-05-03 A 6 3
2017-05-04 A 12 6
2017-05-03 B 10 0
2017-05-04 B 10 0
2017-05-05 B 11 1

Examle for A:

2017-05-01 (1)
2017-05-02 (3 )
2017-05-03 (6)
2017-05-04 (12)
I am calculating the difference in metric between adjacent dates And got the follow
2017-05-01 (NULL)
2017-05-02 (3-1 =2)
2017-05-03 (6-3 = 3)
2017-05-04 (12-6=6)

Solution

  • You can use LAG() window function here if your table is having previous dates without gap:

    Try this:

    select 
*, 
values-(lag(values) over(partition by name order by dates)) "growth" 
from test

    DEMO