CODEWITHSUNDEEP
postgresqlself-joinwindow-functions
Biju
Biju

Reputation: 11

Self join to find difference between successive rows in Postgres, tried self join but does not seem to work

I am trying to find the difference between fields(counters in this case)two successive rows using a self join. The table name is counter_table and the primary key is Country + State + City + dateandtime. The dateandtime is a timestamp field and the data gets inserted approximately every 30 minutes, but there are times when the data is not inserted at all for that interval, which means the next data set arrives after 1 hour or even worse after several hours, it can vary.

The query that I use now is as below

SELECT A.country, A.state, A.city, A.dateandtime, B.dateandtime, A.counter_1, B.counter_1, (B.counter_1 - A.counter_1), A.counter_2, B.counter_2, (B.counter_2 - A.counter_2)
FROM counter_table A, counter_table B
WHERE A.country = B.country
AND A.state = B.state
AND A.city = B.city
AND A.dateandtime > '2013-07-17 22:00:00'
AND B.dateandtime >= (A.dateandtime + interval '29 minutes')
AND B.dateandtime <= (A.dateandtime + interval '33 minutes')
ORDER BY 1,2,3,4;

The result set is as below

Country         State   City    Dateandtime A   Dateandtime B   counter_1A  Counter_1B  1B-1A   Counter_2A  Counter_2B  2B-2A
United States   Texas   Austin  7/17/2013 22:00 7/17/2013 22:30 1814166 1814291 125 1762331 1762454 123
United States   Texas   Austin  7/17/2013 22:30 7/17/2013 23:00 1814291 1814389 98  1762454 1762548 94
United States   Texas   Austin  7/17/2013 23:00 7/17/2013 23:30 1814389 1814489 100 1762548 1762640 92
United States   Texas   Austin  7/18/2013 0:30  7/18/2013 1:00  1814647 1814708 61  1762795 1762855 60
United States   Texas   Austin  7/18/2013 1:00  7/18/2013 1:30  1814708 1814758 50  1762855 1762905 50
United States   Texas   Austin  7/18/2013 1:30  7/18/2013 2:00  1814758 1814829 71  1762905 1762975 70
United States   Texas   Austin  7/18/2013 2:00  7/18/2013 2:30  1814829 1814892 63  1762975 1763037 62
United States   Texas   Austin  7/18/2013 2:30  7/18/2013 3:00  1814892 1814977 85  1763037 1763122 85
United States   Texas   Austin  7/18/2013 3:00  7/18/2013 3:30  1814977 1815056 79  1763122 1763200 78
United States   Texas   Austin  7/18/2013 3:30  7/18/2013 4:00  1815056 1815105 49  1763200 1763249 49

This obvously is not the right solution, The records for 23:30 and 00:00 are missing in this case. Can some Postgres experts help? Are window functions a good solution for this issue?

Upvotes: 1

Views: 543

Answers (1)

dcsohl
dcsohl

Reputation: 7396

Windowing functions would be an excellent idea here. Also, you should probably provide an example of the original set so as to enable people to figure out why 23:30 and 00:00 are missing.

Upvotes: 1

Related Questions