How Do I Transform A Data Table Column From Cumulative To Difference When Reading Csv Into Spring Boot Application?

I have data in a table like date | city | Cumulative total --------------------------------- 1/1/2020 | NYC | 10 1/2/2020 | NYC | 15 1/3/2020 | NYC | 31 1/4/2020 |

Solution 1:

If your database supports window functions, this is an easy task for lag(), which lets you access any column on the previous row, given a partition and order by specification:

select 
    t.*,
    cumulative 
        -lag(cumulative, 1, 0) over(partitionby city orderbydate) as difference
from mytable t

Not all databases support the 3-argument form of lag(), in which case you can do:

select
    t.*,
    coalesce(
        cumulative -lag(cumulative) over(partitionby city orderbydate),
        cumulative
    ) difference
from mytable t