How to do a range aggregation when ranges overlap?

Question

Suppose I have table with a numeric column called score.

 id  | score 
-----+-------
   1 |   817
   2 |    57
   3 |   571
   4 |   906
   5 |   496
   6 |   129
   7 |    93
 ... |  ...

In standard SQL or Postgresql, how to produce a table of counts by range with these ranges overlapping ?

score_range | count 
-------------+-------
 0-20        |     9
 15-50       |    25
 25-*        |   597

The case whensolution is inapplicable here as it only returns the first bucket the score belongs to.

score 1 · Accepted Answer · answered Apr 04 '20 at 13:00

Use a derived table to define the ranges. Then left join and group by:

select v.range, count(t.score)
from (values  ('0-20', 0, 20),
              ('15-50', 15, 50),
              ('25-*', 25, null)
     ) v(range, lo, hi) left join
     t 
     on t.score >= v.lo and (t.score <= v.hi or v.hi is null)
group by v.range
order by v.lo;

score 1 · Answer 2 · answered Apr 04 '20 at 13:07

You could use Postgres' range types for this, along with the containment operator (@>):

select r.score_range, count(*)
from (values 
    ('0-20', '[0, 20]'::int4range), 
    ('15-50', '[15, 50]'::int4range), 
    ('25-*', '[25,]'::int4range)
) r(score_range, rg)
left join mytable t on r.rg @> t.score
group by r.score_range

'[0, 20]'::int4range represents a range of integers for 0 (inclusive) to 20 (inclusive). '[25,]'::int4range represents a range from 25 (inclusive) to infinity. If you want exclusive ranges, you can use parentheses (()) instead of square brackets ([]).

How to do a range aggregation when ranges overlap?

2 Answers2