Re: need help with some aggregation magic

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 iusGROUP BY 1,2,3ORDER BY 1,2,3
;

ROLLBACK ;

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