Skip site navigation (1) Skip section navigation (2)

Re: optimizing query with multiple aggregates

From: David Wilson <david(dot)t(dot)wilson(at)gmail(dot)com>
To: Doug Cole <dougcole(at)gmail(dot)com>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: optimizing query with multiple aggregates
Date: 2009-10-22 02:47:31
Message-ID: e7f9235d0910211947h5eedf6behc586156e79709160@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-performance
On Wed, Oct 21, 2009 at 6:51 PM, Doug Cole <dougcole(at)gmail(dot)com> wrote:

>
> repeated across many different x,y values and fields to build out several
> histograms of the data.  The main culprit appears to be the CASE statement,
> but I'm not sure what to use instead.  I'm sure other people have had
> similar queries and I was wondering what methods they used to build out data
> like this?
>

Use group by with an appropriate division/rounding to create the appropriate
buckets, if they're all the same size.

select round(field/100) as bucket, count(*) as cnt from foo group by
round(field/100);

-- 
- David T. Wilson
david(dot)t(dot)wilson(at)gmail(dot)com

In response to

pgsql-performance by date

Next:From: Nikolas EverettDate: 2009-10-22 02:47:45
Subject: Re: optimizing query with multiple aggregates
Previous:From: Doug ColeDate: 2009-10-22 02:21:36
Subject: Re: optimizing query with multiple aggregates

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group