From: Ivan Sergio Borgonovo <mail@webthatworks.it>
To: pgsql-sql@postgresql.org
Subject: get distinct + group by then filter
Date: Thu, 17 Dec 2009 16:28:09 +0100
Message-ID: <20091217162809.07f871e3@dawn.webthatworks.it> (raw)
I've a web application and I'm trying to do some reporting on
affiliate commission
create table tracky_hit (
hitid serial,
esid varchar(32), -- related to browser session
track_time timestamp,
aid varchar(32), -- affiliate code
-- some other tracking stuff
);
create table tracky_event (
eventid serial,
esid varchar(32) references tracky_hit (esid)
);
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.
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;
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');
but this looks awful. Any better way?
I'm on 8.3 and no short term plan to move to 8.4
thanks
--
Ivan Sergio Borgonovo
http://www.webthatworks.it
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: mail@webthatworks.it
Subject: Re: get distinct + group by then filter
In-Reply-To: <20091217162809.07f871e3@dawn.webthatworks.it>
* 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