From: Kevin Crain <kevin.crain1@gmail.com>
To: Andreas <maps.on@gmx.net>
Cc: pgsql-sql@postgresql.org
Subject: Re: need help with some aggregation magic
Date: Thu, 9 Jun 2011 14:39:01 -0700
Message-ID: <BANLkTimX4Pb6OxQdVmQFm4CYuqaCgS3K=w@mail.gmail.com> (raw)
In-Reply-To: <4DF0CDFC.4070802@gmx.net>
References: <4DF0CDFC.4070802@gmx.net>
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
>
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: kevin.crain1@gmail.com, maps.on@gmx.net
Subject: Re: need help with some aggregation magic
In-Reply-To: <BANLkTimX4Pb6OxQdVmQFm4CYuqaCgS3K=w@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox