sql - postgresql - 按值范围获取计数

Question

我的数据是这样的:

name | value
------------
a    | 3.5
a    | 13.5
a    | 4.9
a    | 11
a    | 14
b    | 2.5
b    | 13.6
b    | 5.1
b    | 12
b    | 13.5

我需要按值范围分组的计数:

name | 0-5 | 5-10 | 10-15
-------------------------
a    | 2   | 0    | 2
b    | 1   | 1    | 3

感谢任何帮助。

谢谢，草

Answer 1

select name, 
       count(case when value <= 5 then 1 end) as "0-5",
       count(case when value > 5 and value <= 10 then 1 end) as "5-10",
       count(case when value > 10 and value <= 15 then 1 end) as "10-15"
from the_table
group by name;

在即将到来的 9.4 版本中，这可以写得更具可读性:

select name, 
       count(*) filter (where amount <= 5) as "0-5",
       count(*) filter (where value > 5 and value <= 10) as "5-10",
       count(*) filter (where value > 10 and value <= 15) as "10-15"
from the_table
group by name;