CODEWITHSUNDEEP
sqlpostgresqlsubtraction
user1798707
user1798707

Reputation: 361

SQL: Subtract from consecutive rows with specific value

I have a table like the following one:

+------+-----+------+-------+
|  ID  | day | time | count |
+------+-----+------+-------+
| abc1 |   1 |   12 |     1 |
| abc1 |   1 |   13 |     3 |
| abc1 |   2 |   14 |     2 |
| abc2 |   2 |   18 |     4 |
| abc2 |   2 |   19 |     8 |
| abc2 |   3 |   15 |     3 |
+------+-----+------+-------+

What I want to do is subtract the "count" from the next row if the ID is the same, the day has the same value as the current row and the time is bigger by a value (ex. +1). So the new table I want to get has this layout:

+------+-----+------+-------+------------+
|  ID  | day | time | count | difference |
+------+-----+------+-------+------------+
| abc1 |   1 |   12 |     1 | 2          |
| abc1 |   1 |   13 |     3 | null       |
| abc1 |   2 |   14 |     2 | null       |
| abc2 |   2 |   18 |     4 | 4          |
| abc2 |   2 |   19 |     8 | null       |
| abc2 |   3 |   15 |     3 | null       |
+------+-----+------+-------+------------+

As you can see only the rows that have the same ID, day and a time difference of 1 are subtracted.

Upvotes: 2

Views: 5804

Answers (2)

Giorgos Betsos
Giorgos Betsos

Reputation: 72205

You can use the following query that makes use of LEAD window function:

SELECT ID, day, time, count,
       CASE WHEN lTime - time = 1 THEN lCount - count
            ELSE NULL
       END as difference 
FROM (
  SELECT ID, day, time, count,
         LEAD(time) OVER w AS lTime,
         LEAD(count) OVER w AS lCount
  FROM mytable  
  WINDOW w AS (PARTITION BY ID, day ORDER BY time) ) t

The above query uses the same window twice, in order to get value of next record within the same partition. The outer query uses these next values in order to enforce the requirements.

Demo here

Upvotes: 3

Deep
Deep

Reputation: 3202

after seeing your example data and expected output, I would suggest to use left join like this :

SELECT a.*,
       b.count - a.count
FROM   MyTable a
       LEFT JOIN MyTable b
              ON a.ID = b.ID
                 AND a.time = b.time - 1
                 AND a.count < b.count

NOTE : if there are two or more rows which statisfies the join criteria then it will show multiple rows.

Upvotes: 0

Related Questions