Generating Running Sum of Ratings in SQL

Question

I have a rating table. It boils down to:

rating_value  created
+2            april 3rd
-5            april 20th

So, every time someone gets rated, I track that rating event in the database.

I want to generate a rating history/time graph where the rating is the sum of all ratings up to that point in time on a graph.

I.E. A person's rating on April 5th might be select sum(rating_value) from ratings where created <= april 5th

The only problem with this approach is I have to run this day by day across the interval I'm interested in. Is there some trick to generating a running total using this sort of data?

Otherwise, I'm thinking the best approach is to create a denormalized "rating history" table alongside the individual ratings.

Answer 1

try to add a group by statement. that gives you the rating value for each day (in e.g. an array). as you output the rating value over time, you can just add the previous array elements together.

Answer 2

If you have postgresql 8.4, you can use a window-aggregate function to calculate a running sum:

steve@steve@[local] =# select rating_value, created,
                              sum(rating_value) over(order by created)
                       from rating;
 rating_value |  created   | sum 
--------------+------------+-----
            2 | 2010-04-03 |   2
           -5 | 2010-04-20 |  -3
(2 rows)

See http://www.postgresql.org/docs/current/static/sql-expressions.html#SYNTAX-WINDOW-FUNCTIONS