This imples that tracky_hit.esid is at least UNIQUE.
);
create table tracky_ordergroup_event ( ordergroupid int references ..., eventid int references tracky_event (eventid) );
Now I'd like to pick up the first hit for each esid in a given interval of time for a given aid and relate them with ordergroupid.
aid may change across the same esid.
If tracky_hit.esid is unique, then why same esid can have many aids?
Can you specify more complete schema (at least PKeys would be nice)?
Getting the first hit for each esid can be done:
select min(hitid) as h from tracky_hit group by esid;
or
select distinct on (esid) hitid from tracky_hit order by esid, track_time;
DISTINCT ON seems a good aproach tu such queries.
If I put a where aid='somestuff' right in the above query... I'm not picking up the first hit in an esid.
The only way that comes to my mind to solve the problem is applying the condition later in a subquery, but no conditions means a lot of data returned.
I've a similar problem with the interval: if I chop in the middle of a session I may not pick up the beginning of each session. Furthermore I've to count session just once even if they cross the boundary of an interval.
I could do something like:
select oe.ordergroupid from tracky_ordergroup_event oe join tracky_event e on e.eventid=oe.eventid join tracky_hit th on th.esid=e.esid where th.hitid in (select distinct on (esid) hitid from tracky_hit where track_time between ('2009-12-01'::timestamp - interval '1 days') and ('2009-12-01'::timestamp + interval '1 months' + interval '1 days') order by esid, track_time ) and th.aid='someaid' and th.track_time between ('2009-12-01'::timestamp) and ('2009-12-01'::timestamp + interval '1 months');