CODEWITHSUNDEEP
sqlpostgresqlgroup-by
Forece85
Forece85

Reputation: 518

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

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?

Upvotes: 0

Views: 753

Answers (4)

Belayer
Belayer

Reputation: 14934

You can use the lead()/lag() window functions to access data from the next/ previous row. You can further subtract timestamps to give an interval and extract the parts needed.

select id, floor( extract('day' from diff)*24 + extract('hour' from diff) ) "Time Difference: Hours" 
  from (select id, date_ts - lag(date_ts) over (partition by id order by date_ts) diff
          from example
       ) hd
  where diff is not null
  order by id;

NOTE:
Your expected results, as presented, are incorrect. The results would be -1 and -24 respectively.
DATE is a very poor choice for a column name. It is both a Postgres data type (at best leads to confusion) and a SQL Standard reserved word.

Upvotes: 0

GnanaSagar reddy
GnanaSagar reddy

Reputation: 56

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;

Upvotes: 1

bracko
bracko

Reputation: 372

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.

Upvotes: 0

SR3142
SR3142

Reputation: 610

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

Upvotes: 0

Related Questions