Suppose that I have the following sql data
species date observations
Bird1 08-09-19 40
Bird1 06-10-19 50
Bird1 11-11-19 60
Bird2 08-09-19 50
Bird2 06-10-19 90
Bird3 06-10-19 10
Bird3 11-11-19 20
and suppose that I want to show for a month and a bird species, what the incremental change in observations was (with respect to the previous month), as a fraction of the total increment of observations of bird species for that month. Given the example data, I would want the following result.
species date observations increment_fraction
Bird1 08-09-19 40 0
Bird1 06-10-19 50 0.2
Bird1 11-11-19 60 0.5
Bird2 08-09-19 50 0
Bird2 06-10-19 90 0.8
Bird3 06-10-19 10 0
Bird3 11-11-19 20 0.5
Let me explain these results. The increment fractions corresponding to the date 08-09-19 is 0 because there are no earlier records available. The second row has an increment fraction of 0.2, because the total increment in observations between the date 08-09-19 and 06-10-19 is 50, and the increment change for Bird1 between 08-09-19 and 06-10-19 is 10. Increment fraction is 10/50 = 0.2.
The same goes for the third row: the total increment between dates 06-10-19 and 11-11-19 is 20, and the increment for Bird1 between dates 06-10-19 and 11-11-19 is 10. Increment fraction is 10/20 = 0.5.
The following query will give me the desired result:
with increments_table as (
select species, date, observations,
observations - lag(observations, 1, observations) over (partition by species
order by date) as increment
from species_table),
increment_sums as (
select date, sum(increment) as increment_sum
from increments_table
group by date)
select species, date, observations, increment/increment_sum
from increments_table
join increment_sums
on increments_table.date = increment_sums.date
But I was wondering whether this could be a bit more compact. I think it can be more compact as it is quite a basic operation, but I'm not sure how.
Question: Is there a way to do this shorter?