PostgreSQL-通过值范围获取计数 [英] postgresql - get count by value ranges

问题描述

我的数据如下：

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

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

I need the count grouped by value ranges:

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

感谢您的帮助。

谢谢，
grassu

Thanks, grassu

推荐答案

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版本中，该代码的可读性更高：

With the upcoming version 9.4 this can be written a bit more readable:

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;

这篇关于PostgreSQL-通过值范围获取计数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！