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

Re: Better way to write aggregates?

From: "Jim Buttafuoco" <jim(at)contactbda(dot)com>
To: Jan Dittmer <jdi(at)l4x(dot)org>, pgsql-performance(at)postgresql(dot)org
Subject: Re: Better way to write aggregates?
Date: 2006-04-21 12:27:32
Message-ID: 20060421122617.M81850@contactbda.com (view raw or flat)
Thread:
Lists: pgsql-performance
Jan,

I write queries like this

CREATE VIEW parent_childs AS
SELECT
 	c.parent,
 	count(c.state) as childtotal,
 	sum(case when c.state = 1 then 1 else 0 end) as childstate1,
 	sum(case when c.state = 2 then 1 else 0 end) as childstate2,
 	sum(case when c.state = 3 then 1 else 0 end) as childstate3
 FROM child c
 GROUP BY parent;

---------- Original Message -----------
From: Jan Dittmer <jdi(at)l4x(dot)org>
To: pgsql-performance(at)postgresql(dot)org
Sent: Fri, 21 Apr 2006 10:37:10 +0200
Subject: [PERFORM] Better way to write aggregates?

> Hi,
> 
> I more or less often come about the problem of aggregating a
> child table counting it's different states. The cleanest solution
> I've come up with so far is:
> 
> BEGIN;
> CREATE TABLE parent (
> 	id int not null,
>   	name text not null,
> 	UNIQUE(id)
> );
> 
> CREATE TABLE child (
> 	name text not null,
> 	state int not null,
> 	parent int not null references parent(id)
> );
> 
> CREATE VIEW parent_childs AS
> SELECT
> 	c.parent,
> 	count(c.state) as childtotal,
> 	count(c.state) - count(nullif(c.state,1)) as childstate1,
> 	count(c.state) - count(nullif(c.state,2)) as childstate2,
> 	count(c.state) - count(nullif(c.state,3)) as childstate3
> FROM child c
> GROUP BY parent;
> 
> CREATE VIEW parent_view AS
> SELECT p.*,
> pc.*
> FROM parent p
> LEFT JOIN parent_childs pc ON (p.id = pc.parent);
> COMMIT;
> 
> Is this the fastest way to build these aggregates (not considering
> tricks with triggers, etc)? The count(state) - count(nullif(...)) looks
> a bit clumsy.
> I also experimented with a pgsql function to sum these up, but considered
> it as not-so-nice and it also always forces a sequential scan on the
> data.
> 
> Thanks for any advice,
> 
> Jan
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 6: explain analyze is your friend
------- End of Original Message -------


In response to

Responses

pgsql-performance by date

Next:From: Jan DittmerDate: 2006-04-21 12:35:33
Subject: Re: Better way to write aggregates?
Previous:From: Wu FengguangDate: 2006-04-21 12:20:28
Subject: Re: Introducing a new linux readahead framework

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