On Thu, Mar 1, 2012 at 3:27 PM, Alessandro Gagliardi
<alessandro@path.com> wrote:
> INSERT INTO hourly_activity
> SELECT DISTINCT date_trunc('hour', hr_timestamp) AS activity_hour,
> activity_unlogged.user_id,
> client_ip, hr_timestamp, locale, log_id, method,
> server_ip, uri, user_agent
> FROM activity_unlogged,
> (SELECT user_id, MAX(hr_timestamp) AS last_timestamp
> FROM activity_unlogged GROUP BY user_id, date_trunc('hour',
> hr_timestamp)) AS last_activity
> WHERE activity_unlogged.user_id = last_activity.user_id AND
> activity_unlogged.hr_timestamp = last_activity.last_timestamp;
Try
INSERT INTO hourly_activity
SELECT ... everything from au1 ...
FROM activity_unlogged au1
LEFT JOIN activity_unlogged au2 ON au2.user_id = au1.user_id
AND
date_trunc('hour', au2.hr_timestamp) = date_trunc('hour',
au1.hr_timestamp)
AND
au2.hr_timestamp < au1.hr_timestamp
WHERE au2.user_id is null;