DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma;
	h=mime-version:in-reply-to:references:from:date:message-id:subject:to
	:cc:content-type;
	b=NUqWpFov+ypmO8WC/5O9Lqpm+p7QnRDmjW7cMDMOEd8H4E75UkwG8+kv5fhBgdtLTI
	LmTMj2/XKx3inc80w5Z16C3bXRflSIliBC3Qk50xtMC5EiudC2w+MULmk/CEywGXKXFc
	rHHVvlUdxr+bbupxf8vkwbqGrEoDhJEFDq2Rk=
MIME-Version: 1.0
In-Reply-To: <4DF0CDFC.4070802@gmx.net>
References: <4DF0CDFC.4070802@gmx.net>
From: Kevin Crain <kevin.crain1@gmail.com>
Date: Thu, 9 Jun 2011 14:39:01 -0700
Message-ID: <BANLkTimX4Pb6OxQdVmQFm4CYuqaCgS3K=w@mail.gmail.com>
Subject: Re: need help with some aggregation magic
To: Andreas <maps.on@gmx.net>
Cc: pgsql-sql@postgresql.org
Content-Type: text/plain; charset=ISO-8859-1

Try this:

select user_id, project_id, date_trunc, sum(sum) FROM (select user_id,
project_id, date_trunc('day', ts), SUM(duration) FROM (select user_id,
project_id, a.ts, ((SELECT MIN(b.ts) FROM log b WHERE b.ts>a.ts AND
(date_trunc('day',a.ts)=date_trunc('day',b.ts)))-a.ts) AS duration
from log a order by user_id, project_id, ts) AS foo group by user_id,
project_id, ts) AS day_set group by user_id, project_id, date_trunc
order by user_id, project_id, date_trunc;


-Kevin Crain

On Thu, Jun 9, 2011 at 6:43 AM, Andreas <maps.on@gmx.net> wrote:
> hi,
> I have a log-table that stores events of users and projects like this
> ( user_id integer, project_id integer, ts timestamp, event_type integer )
>
> I need an aggregated list of worktime per user, per project, per day.
>
> The users can switch projects during the day so I can't work this out with
> min(ts) and max(ts).
>
> Is there a clever way to get this with SQL ?
>
> --
> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql
>