SQL How to subtract 2 row values of a same column based on same key-CodePudding

How to extract the difference of a specific column of multiple rows with same id?

Example table:

id	prev_val	new_val	date
1	0	1	2020-01-01 10:00
1	1	2	2020-01-01 11:00
2	0	1	2020-01-01 10:00
2	1	2	2020-01-02 10:00

expected result:

id	duration_in_hours
1	1
2	24

summary: with id=1, (2020-01-01 10:00 - 2020-01-01 11:00) is 1hour;

with id=2, (2020-01-01 10:00 - 2020-01-02 10:00) is 24hour

Can we achieve this with SQL?

CodePudding user response：

This solutions will be an effective way

with pd as (
select
    id,
    max(date) filter (where c.old_value = '0') as "prev",
    max(date) filter (where c.old_value = '1') as "new"
from
    table
group by
    id )
select
    id ,
    new - prev as diff
from
    pd;

CodePudding user response：

if you need the difference between successive readings something like this should work

select a.id, a.new_val, a.date - b.date
from my_table a join my_table b 
     on a.id = b.id and a.prev_val = b.new_val

CodePudding user response：

you could use min/max subqueries. For example:

SELECT mn.id, (mx.maxdate - mn.mindate) as "duration",
FROM (SELECT id, max(date) as mindate FROM table GROUP BY id) mn
JOIN (SELECT id, min(date) as maxdate FROM table GROUP BY id) mx ON
   mx.id=mn.id

Let me know if you need help in converting duration to hours.