How to show incremental changes in SQL

user_9464073 0
Viewed 88

I have a sql (Presto) table that contains data about different kinds of bird species. The table contains information about how often the different species have been observed in a park over different months. Some of the species have not been observed in certain months, but measurements were only performed on 07-9-19, 08-10-19, and 05-11-19. A sample of the data would look like this

Species        Period     Total (Cumulative) Observations (TCO)

Bird1           07-9-19            33
Bird1           08-10-19           45
Bird1           05-11-19           60
Bird2           07-9-10            20
Bird3            ....            ....
Bird3            ....            ....
....             ....            ....

What I would like is to add a new column to this table that captures incremental change between two days of measurement for each species. Based on the example sample I would like to produce the following result:

Species        Period            TCO        Change

Bird1           07-9-19            33          33
Bird1           08-10-19           45          12
Bird1           05-11-19           60          15
Bird2           07-9-10            20          20
Bird3           08-10-19           5           5
Bird3           05-11-19           24          19
....             ....            ....

I think I've found a way to do this, but it is quite complicated and involves creating a new table. I was wondering if there was are simpler solution to my problem (as it is slightly simpler than the problem in the link).

presto sql database
1 Answers

You can use window function lag() to recover the previous tco for the same species, period-wise:

select 
    t.*,
    tco - lag(tco, 1, 0) over(partition by species order by period) change
from mytable

The three-argument form of lag lets you specify a default value instead of null: as per your expected result, you want 0 in that case.

user_106767160
Related