How to calculate the cumulative difference between rows in Postgres?
You can use LAG()
function in a query with GROUP BY
just like any other query. The only difference is that the columns allowed in the window (OVER
) and in the LAG
are the ones allowed in SELECT
after a GROUP BY
:
select
date,
session_id,
sum(upload_usage) as upload,
sum(download_usage) as download,
sum(total_usage_on_a_day) as total_as_cumm,
sum(total_usage_on_a_day)
- coalesce(lag(sum(total_usage_on_a_day)) over (partition by session_id order by date), 0)
as expected_difference
from jiodba.s_crc_zda_mon_conn_usage
where gpart = '1100043958'
and zzaccess_ntwk_id = 'FTTH'
group by session_id, date
order by session_id, date
limit 100 ;
- I'm not sure why you used
max(usage)
while you name the columntotal_usage
. I changed it to usesum()
instead. If you need to usemax(usage)
there, pick a more appropriate name for that column.