Select Difference Between Rows (Postgres)

Question

i'm aggregating facebook "like" counts for urls over time. each hour, i check the "like" count and insert it into a timestamped row. how can i get the difference in total between the new row and the previous hour's row?

eg,

insert total=5 for id=1 at 2pm
insert total=12, diff_since_last=7 for id=1 at 3pm

thanks!

user330315 · Accepted Answer · 2010-08-21 19:24:53Z

6

This should do it.

SELECT id, 
       total, 
       total - lag(total) over (partition by id order by timestamp_column) as diff_since_last
FROM the_table_with_no_name

answered Aug 21, 2010 at 19:24

user330315

Sign up to request clarification or add additional context in comments.

Comments

Jason Denney · Accepted Answer · 2013-08-02 19:57:51Z

0

When I tried a_horse_with_no_name's solution I had to remove the 'partition by id' portion to get a result (I'm using PG 9.2)

SELECT id, total, total - lag(total) over (order by timestamp_column) as diff_since_last FROM the_table_with_no_name

answered Aug 2, 2013 at 19:57

Jason Denney

3,3111 gold badge20 silver badges14 bronze badges

Collectives™ on Stack Overflow

Select Difference Between Rows (Postgres)

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related