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@xxxxxxx> To: pgsql-performance@xxxxxxxxxxxxxx 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 -------
