Re: efficient query help

Hmmm... Is this better? Please let me know; it looks better with a small set
of sample data, but I'd be curious how it does with your real table:

select id,
flag,
sum(count)

from ( select id,
flag,
1 as count
from log
where flgtime='2002-04-16'

union all

select id,
f.flag,
0 as count
from log l,
flags f

/* don't think this is neccessary where flgtime <> '2002-04-16' */

) as t

where flag is not null

group by id, flag;

[ for others: the point of the query is to count the number of unique
id/flag combos on 4/16/2002, and union this with all possible combos of
ids/flags that aren't present on non-4/16/2002 ]

Joel BURTON | joel(at)joelburton(dot)com | joelburton.com | aim: wjoelburton
Knowledge Management & Technology Consultant

> -----Original Message-----
> From: pgsql-sql-owner(at)postgresql(dot)org
> [mailto:pgsql-sql-owner(at)postgresql(dot)org]On Behalf Of Laurette Cisneros
> Sent: Monday, April 22, 2002 2:11 PM
> To: pgsql-sql(at)postgresql(dot)org
> Subject: [SQL] efficient query help
>
>
>
> Hi all,
>
> I have crafted the following query which returns the results I want but
> runs extremely slow (I'm sure it's the "not in" part that does it) since
> the tables can contain large amounts of data.
>
> Is there a better, more efficient way to build this query?
>
> It's important that I return zero counts for id flag combinations
> that do not
> have an entry in the log table hence the union with the "not in":
>
> id is a text field and flgtime is a timestamp. flags is a table
> that contains
> a single column with a row for each unique flag (text).
>
> select id, flag, count(*) as count
> from log
> where date(flgtime) = '2002-04-16'
> and flag is not null
> group by id, flag
> union
> select distinct l.id, f.flag, 0 as count
> from log l, flags f
> where (l.id, f.flag) not in
> (select id, flag
> from log
> where date(flgtime) = '2002-04-16'
> and fag is not null)
> group by l.id, f.flag
> ;
>
> Thanks for any suggestions.
>
> --
> Laurette Cisneros
> Database Roadie
> (510) 420-3137
> NextBus Information Systems, Inc.
> www.nextbus.com
> Where's my....bus?
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>