From: Edgardo Portal <egportal2002@yahoo.com>
Subject: Re: need help with some aggregation magic
Date: Thu, 9 Jun 2011 21:22:57 +0000 (UTC)
Organization: A noiseless patient Spider
Lines: 89
Message-ID: <isrdjh$ugu$1@dont-email.me>
References: <4DF0CDFC.4070802@gmx.net>
	<BANLkTi=M4QNH1QCaoH6k+dCVL3j5wczEnQ@mail.gmail.com>
	<4DF0FB0A.2020801@gmx.net>
Injection-Date: Thu, 9 Jun 2011 21:22:57 +0000 (UTC)
Injection-Info: mx04.eternal-september.org;
	posting-host="BE3KXeOo8XQLoO8WlDTIjw";
	logging-data="31262"; mail-complaints-to="abuse@eternal-september.org";
	posting-account="U2FsdGVkX19oe8ty20bLHJ5L+inQV40l45pRQro+UMU="
User-Agent: slrn/pre1.0.0-16 (Linux)
Cancel-Lock: sha1:B/gRpiRldTtOzabsnAW2fBTvVrs=
To: pgsql-sql@postgresql.org

On 2011-06-09, Andreas <maps.on@gmx.net> wrote:
> Am 09.06.2011 18:20, schrieb Richard Broersma:
>> On Thu, Jun 9, 2011 at 6:43 AM, Andreas<maps.on@gmx.net>  wrote:
>>
>>> 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).
>> SELECT user_id, project_id, date_trunc(  'day', ts ) as event_day,
>>                MIN( ts ) AS event_start, MAX( ts ) AS event_end,
>>                MAX( ts ) - MIN( ts ) AS duration
>>    FROM Loggingtable
>> GROUP BY user_id, project_id, date_trunc( 'day', ts )
>> ORDER BY date_trunc( 'day', ts ), user_id, project_id;
>>
> As far as I understand you calculate the duration as the difference 
> between the first and last event of a project per day.
> There is a problem because a user can work from 08.00 to 10.00 on 
> project 1 and then from 10.00 to 12.00 on project 2 and then from 12.00 
> to 16.00 on project 1 again.
> Then I get   project 1   8 hours  plus  project 2   2 hours   though the 
> user actually was just 8 hours there.

Unclear to me what to do with the last event of the day (i.e. is there
a implicit end-of-work time to use, or does your usage pattern
guarantee a "closing event" to always be present?), but what about
something like:

BEGIN ;

CREATE TABLE upes (
  uid     integer
 ,pid     integer
 ,ts      timestamp without time zone
 ,evtype  integer
) ;

-- ...user 100 works on 3 projects Monday
INSERT INTO upes VALUES(100, 11, '2011-06-06 13:00', 1) ;
INSERT INTO upes VALUES(100, 11, '2011-06-06 13:30', 2) ;
INSERT INTO upes VALUES(100, 22, '2011-06-06 13:45', 1) ;
INSERT INTO upes VALUES(100, 33, '2011-06-06 18:00', 1) ;

-- ...user 100 works on 2 projects Tuesday
INSERT INTO upes VALUES(100, 11, '2011-06-07 13:00', 1) ;
INSERT INTO upes VALUES(100, 33, '2011-06-07 13:30', 2) ;
INSERT INTO upes VALUES(100, 33, '2011-06-07 17:45', 1) ;

-- ...user 200 works also works on Tuesday
INSERT INTO upes VALUES(200, 11, '2011-06-07 13:00', 1) ;
INSERT INTO upes VALUES(200, 33, '2011-06-07 13:30', 2) ;
INSERT INTO upes VALUES(200, 33, '2011-06-07 13:45', 4) ;
INSERT INTO upes VALUES(200, 33, '2011-06-07 19:45', 8) ;

-- ...attempt to summarize
WITH ius AS (
SELECT upes.ts AS uts, upes.uid, upes.pid, upes.evtype, upes_next.ts AS nts
  FROM upes
       LEFT JOIN upes upes_next
              ON upes_next.ts = (
                 SELECT min(utmp.ts)
                   FROM upes utmp
                  WHERE utmp.uid=upes.uid
                    AND utmp.ts > upes.ts
                    AND utmp.ts::date=upes.ts::date
                 )
)
SELECT ius.uts::date, ius.uid, ius.pid, SUM(nts-uts)
  FROM ius
 GROUP BY 1,2,3
 ORDER BY 1,2,3
;

ROLLBACK ;

with results of:
    uts     | uid | pid |   sum    
------------+-----+-----+----------
 2011-06-06 | 100 |  11 | 00:45:00
 2011-06-06 | 100 |  22 | 04:15:00
 2011-06-06 | 100 |  33 | 
 2011-06-07 | 100 |  11 | 01:00:00
 2011-06-07 | 100 |  33 | 04:15:00
 2011-06-07 | 200 |  11 | 01:00:00
 2011-06-07 | 200 |  33 | 06:15:00
(7 rows)