pg.ddx.io  pgsql-php@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: David Busby <busby@pnts.com>
To: Dave [Hawk-Systems] <dave@hawk-systems.com>
To: pgsql-php@postgresql.org
Subject: Re: Selecting distinct records
Date: Thu, 21 Nov 2002 17:37:25 -0800
Message-ID: <009c01c291c7$b617aec0$4000000a@busbydev> (raw)
References: <DBEIKNMKGOBGNDHAAKGNEEINDPAB.dave@hawk-systems.com>

Dave,
    How about a job that runs once that cleans the whole thing?
Or:
    [Warning, pseudo PHPish code]

    $rs_sid = select distinct (sessionid) from "logs";
    while ($s = pg_fetch_object($rs_sid)
    {
        $rs_stuff = select * from "logs" where .... and "sessionid" = $s->id
        for (loop of $rs_stuff)
        {
            $total+=$rs_stuff->sessiontime
        }
        echo "Session $s->id has $total seconds used";
    }

This method involves a select for each session (might be slow on millions of
records :) ).  This might work once or twice but I don't see it as a
suitable solution if this has to happen on a daily basis.

    I know that the latest PostgreSQL does nested selects just fine (I've
got one paticular statement that has three nested selects ) and it's
performance isn't as bad as you'd think, well at least after the query
analyzer has seen it once and it gets cached.

/B

----- Original Message -----
From: "Dave [Hawk-Systems]" <dave@hawk-systems.com>
To: "David Busby" <busby@pnts.com>; <pgsql-php@postgresql.org>
Sent: Thursday, November 21, 2002 13:26
Subject: RE: Selecting distinct records


> >    Try:
> >select distinct on (sessionid) sum(sessiontime) from logs
> >where name='joeblowuser' and datetime > 1036040400
> >group by sessionid.
>
> sorry, I wasn't clear...  the problem arises when we need to sort the data
for
> output.  postgres demands that the distinct items be first in the ordering
> process, which would not allow display by session, name etc...
>
> the obvious result would be to nest the select statements but can't seem
to get
> that to work either (working with version 7.0.3)
>
> eg/  select * from (select distinct on (sessionid) from logs where....) as
> tempname order by sessiondate desc
>
> gives me an error on the second select, so not sure that is a workable
solution
> either.
>
> >sum() is an aggerate (sp?) function, that means it munges a field
> >(sessiontime) from multiple records into one field in one record.  Since
you
> >are also selecting sessionid (from mulitple records) you need to munge it
> >some how, that munge is accomplished via 'group by'.  From your previous
> >e-mail it seems that (IMHO) the real problem is that duplicates are
getting
> >inserted via external hardware interaction, this select might be a
bandage
> >on a wound whose true size isn't known...
>
> agreed, and that problem has been corrected, but we are dealing with close
to a
> million records which have these duplicates strewn about within...  rather
> annoying, looking for a bandaid in teh select to avoid intensive
post-select
> processing of the output.
>
> Dave
>




view thread (5+ messages)

Message-ID: <009c01c291c7$b617aec0$4000000a@busbydev>
Permalink:  ../009c01c291c7$b617aec0$4000000a@busbydev/
Also on:    postgresql.org/message-id/009c01c291c7$b617aec0$4000000a@busbydev

 · 

reply

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 from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-php@postgresql.org
  Cc: busby@pnts.com, dave@hawk-systems.com
  Subject: Re: Selecting distinct records
  In-Reply-To: <009c01c291c7$b617aec0$4000000a@busbydev>
  References: <DBEIKNMKGOBGNDHAAKGNEEINDPAB.dave@hawk-systems.com> <009c01c291c7$b617aec0$4000000a@busbydev>

* 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