Geek Answers Handbook

Choose the difference between the lines (Postgres)

I am aggregating facebook as "counting URLs over time. Every hour, I check the count of" how to "and paste it in a line with a timestamp. How can I get the difference between the totals of a new line and a previous line?

eg,

  • insert total = 5 for id = 1 at 2 p.m.
  • insert total = 12, diff_since_last = 7 for id = 1 at 15:00

thanks!

+4
source share
2 answers

That 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
+6
source

When I tried the solution a_horse_with_no_name, I had to delete the section "partition by id" to get the result (I use PG 9.2)

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

0
source

More articles:


All Articles