I was thinking of adding an index, but thought it would be pointless since I would only be using the index once before dropping the table (after its loaded into hourly_activity). I assumed it would take longer to create the index and then use it than to just seq scan once or twice. Am I wrong in that assumption?
On Thu, Mar 1, 2012 at 10:40 AM, Peter van Hardenberg <[email protected]> wrote: > On Thu, Mar 1, 2012 at 10:27 AM, Alessandro Gagliardi > <[email protected]> wrote: > > Now, I want to reduce that data to get the last activity that was > performed > > by each user in any given hour. It should fit into a table like this: > > > > How about: > > 1) Create an expression based index on date_trunc('hour', hr_timestamp) > 2) Create a view on that showing the last value > 3) If you want to throw away the data use CREATE TABLE AS on the > results of the view. > > You may also want to investigate window functions. > > -p > > -- > Peter van Hardenberg > San Francisco, California > "Everything was beautiful, and nothing hurt." -- Kurt Vonnegut >
