count_if aggregate

Friday, December 14. 2012

Yesterday on IRC Andrew Gierth suggested it would be good to have a count_if aggregate that would count the rows where it's argument is true. This seems so obviously useful that I cooked up a version quickly in two tiny bits of SQL:

andrew=# create or replace function countif_trans(bigint, bool)
andrew-# returns bigint language sql as
andrew-# $$ select case when $2 then $1 + 1 else $1 end $$;
CREATE FUNCTION
andrew=# create aggregate count_if (bool)
andrew-# (sfunc = countif_trans,
andrew(# stype = bigint, initcond = 0);
CREATE AGGREGATE

Now you can easily do repeated counts of different conditions in one pass over a set of rows:

andrew=# select count_if(relkind in ('r','v','S'))as "r,v,S",
andrew-# count_if(relkind in ('i', 't')) as "i,t" 
andrew-# from pg_class;
 r,v,S | i,t 
-------+-----
   165 | 134
(1 row)

For speed the transition function should probably be written in C, but for many purposes this is good enough.

Posted by Andrew Dunstan in PostgreSQL at 17:02 | Comments (9) | Trackbacks (0)

Trackbacks

Trackback specific URI for this entry

No Trackbacks

Comments

Display comments as (Linear | Threaded)

How about 
 
select sum((relkind in ('r','v','S'))::int) as "r,v,S", sum((relkind in ('i', 't'))::int) as "i,t" from pg_class;

#1 Sylvain on 2012-12-14 18:07 (Reply)

Yes you can do it, but I personally hate casting a bool to an int - it's breaking the boolean abstraction (and I've been told that such a cast is contrary to the Standard). So I think this is at least much more expressive.

#1.1 Andrew Dunstan on 2012-12-14 18:11 (Reply)

I usually just do this with conditionals inside my aggregates: 
 
select sum(case when relkind in ('r','v','S') then 1 else 0 end) as "r,v,S", 
sum(case when relkind in ('i', 't') then 1 else 0 end) as "i,t" 
from pg_class

#1.1.1 Andrew Nierman on 2012-12-15 01:41 (Reply)

OK, but which would you rather write? I've seen views with dozens of these case expressions. They are all pretty ugly.

#1.1.1.1 Andrew Dunstan on 2012-12-15 01:55 (Reply)

Yes, this is also something I've blogged about before: http://thombrown.blogspot.co.uk/2010/11/countif-expression.html

#2 Thom Brown on 2012-12-14 21:57 (Reply)

Interesting, seems there is some demand for it. Maybe we should just add it to core, written in C of course. (I dislike your transition function on a couple of stylistic grounds, though.)

#2.1 Andrew Dunstan on 2012-12-14 22:07 (Reply)

If we're talking about changing the core, would it be crazy to add a WHERE clause to aggregate functions, similar to the existing ORDER BY, so for example COUNT(* WHERE relkind IN ('r','v','S'))?

#2.1.1 Dave on 2012-12-15 15:19 (Reply)

The SQL standard actually has a syntax for that: 
 
count(*) FILTER (WHERE relkind IN ('r','v','S')) 
 
I've been meaning to work on implementing that to postgres for over a year now, but my laziness always seems to get the best of me.

#2.1.1.1 Marko Tiikkaja on 2012-12-17 12:21 (Reply)

Interesting. I didn't know that. Thanks. Please do work on it.

#2.1.1.1.1 Andrew Dunstan on 2012-12-17 18:39 (Reply)

Add Comment

Name
Email
Homepage
In reply to
Comment	Enclosing asterisks marks text as bold (word), underscore are made via _word_. Standard emoticons like :-) and ;-) are converted to images. Wiki format allowed
	Remember Information? Subscribe to this entry