Received: from localhost (unknown [200.46.208.211]) by mail.postgresql.org (Postfix) with ESMTP id AF8EB6323A9 for ; Fri, 18 Dec 2009 08:20:15 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by localhost (mx1.hub.org [200.46.208.211]) (amavisd-maia, port 10024) with ESMTP id 03978-07 for ; Fri, 18 Dec 2009 12:19:57 +0000 (UTC) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from mail-bw0-f213.google.com (mail-bw0-f213.google.com [209.85.218.213]) by mail.postgresql.org (Postfix) with ESMTP id A6E6163238C for ; Fri, 18 Dec 2009 08:20:03 -0400 (AST) Received: by bwz5 with SMTP id 5so2079739bwz.3 for ; Fri, 18 Dec 2009 04:20:01 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:mime-version:received:in-reply-to:references :date:message-id:subject:from:to:cc:content-type; bh=aQjFQMeikq0Tq4FTKLWfuNuSD/J1MnSM1PSR7xIawuI=; b=QQ27j2FxP3nFJBB96/VKg2rG/zhqwEOOi1NbTha5P14pOsc0MOfNMs6omJHcuZvtEl BWnAAbLIe3dvb5ONzuLV1AZ205Y1/6yVAK8UndldYi1uIjbPCpalO+krPkgTHGqXjlP7 IrY+5JHoFdbedbLHlxrzQKsbqITJnk6dfOeGo= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=mime-version:in-reply-to:references:date:message-id:subject:from:to :cc:content-type; b=w2PuBVidlRLk+UJOIlG04t/7ywf29LhqZn42jfz+Ikl/kMf+up7pfgoXpDEeeejOYl 3J2db6AI0fC7gtDXh4JiUys49AjOXAIeGjCT0xaJHmlTPiEMlWxGA9za+joRFV8kUlPM FZNKoH9y7s0N95Zm956zoXS2aqS4PVJC6g8zE= MIME-Version: 1.0 Received: by 10.204.153.217 with SMTP id l25mr2313328bkw.108.1261138800988; Fri, 18 Dec 2009 04:20:00 -0800 (PST) In-Reply-To: <20091217162809.07f871e3@dawn.webthatworks.it> References: <20091217162809.07f871e3@dawn.webthatworks.it> Date: Fri, 18 Dec 2009 13:20:00 +0100 Message-ID: <92869e660912180420h548ec7dak2f85f0780a8d0318@mail.gmail.com> Subject: Re: get distinct + group by then filter From: =?UTF-8?Q?Filip_Rembia=C5=82kowski?= To: Ivan Sergio Borgonovo Cc: pgsql-sql@postgresql.org Content-Type: multipart/alternative; boundary=0015175d094e3ed1e0047affc2d8 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-2.436 tagged_above=-10 required=5 tests=AWL=0.162, BAYES_00=-2.599, HTML_MESSAGE=0.001 X-Spam-Level: X-Archive-Number: 200912/46 X-Sequence-Number: 33484 --0015175d094e3ed1e0047affc2d8 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: quoted-printable 2009/12/17 Ivan Sergio Borgonovo > 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) > 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=3D'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=3Doe.eventid > join tracky_hit th on th.esid=3De.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=3D'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 > > > -- > Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-sql > --=20 Filip Rembia=C5=82kowski JID,mailto:filip.rembialkowski@gmail.com http://filip.rembialkowski.net/ --0015175d094e3ed1e0047affc2d8 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable

2009/12/17 Ivan Sergio Borgonovo <mail@webthatworks.it= >
);

create table tracky_ordergroup_event (
=C2=A0ordergroupid int references ...,
=C2=A0eventid 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=C2=A0 trac= ky_hit.esid is unique,=C2=A0 then why same esid can have many aids?

= Can you specify more complete schema (at least PKeys would be nice)?
=C2=A0


Getting the first hit for each esid can be done:

select min(hitid) as h
=C2=A0from tracky_hit
=C2=A0 =C2=A0group by esid;

or

select distinct on (esid) hitid
=C2=A0from tracky_hit
=C2=A0 =C2=A0order by esid, track_time;

DISTINCT = ON seems a good aproach tu such queries.

=C2=A0

If I put a where aid=3D'somestuff' right in the above query... I= 9;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
=C2=A0tracky_ordergroup_event oe
=C2=A0join tracky_event e on e.eventid=3Doe.eventid
=C2=A0join tracky_hit th on th.esid=3De.esid
=C2=A0where th.hitid in
=C2=A0 =C2=A0(select distinct on (esid) hitid
=C2=A0 =C2=A0 =C2=A0from tracky_hit
=C2=A0 =C2=A0 =C2=A0where track_time between
=C2=A0 =C2=A0 =C2=A0 =C2=A0('2009-12-01'::timestamp - interval = 9;1 days')
=C2=A0 =C2=A0 =C2=A0 =C2=A0and
=C2=A0 =C2=A0 =C2=A0 =C2=A0('2009-12-01'::timestamp + interval = 9;1 months' + interval '1
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0days')
=C2=A0 =C2=A0 =C2=A0order by esid, track_time
=C2=A0 =C2=A0)
=C2=A0and th.aid=3D'someaid'
=C2=A0and th.track_time between
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0('2009-12-01'::timestamp)
=C2=A0 =C2=A0 =C2=A0 =C2=A0and
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0('2009-12-01'::timestamp + inter= val '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.webthat= works.it


--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
= http://www.postgresql.org/mailpref/pgsql-sql



--
Filip Rembia=C5= =82kowski
JID,mailto:fi= lip.rembialkowski@gmail.com
http://filip.rembialkowski.net/
--0015175d094e3ed1e0047affc2d8--