From: Joel Burton <joel@joelburton.com>
To: Laurette Cisneros <laurette@nextbus.com>
To: Pgsql-Sql@Postgresql. Org <pgsql-sql@postgresql.org>
Subject: Re: efficient query help
Date: Wed, 24 Apr 2002 10:06:16 -0400
Message-ID: <JGEPJNMCKODMDHGOBKDNKEAGCLAA.joel@joelburton.com> (raw)
In-Reply-To: <Pine.LNX.4.44.0204221110140.22730-100000@visor.corp.nextbus.com>
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@joelburton.com | joelburton.com | aim: wjoelburton
Knowledge Management & Technology Consultant
> -----Original Message-----
> From: pgsql-sql-owner@postgresql.org
> [mailto:pgsql-sql-owner@postgresql.org]On Behalf Of Laurette Cisneros
> Sent: Monday, April 22, 2002 2:11 PM
> To: pgsql-sql@postgresql.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
>
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: joel@joelburton.com, laurette@nextbus.com
Subject: Re: efficient query help
In-Reply-To: <JGEPJNMCKODMDHGOBKDNKEAGCLAA.joel@joelburton.com>
* 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