SQL频率分布查询以group-by计算范围并包括0个计数

Given:

table 'thing':

age
---
3.4
3.4
10.1
40
45
49

I want to count the number of things for each 10-year range, e.g.,

我想计算每个10年范围内的事物数量,例如,

age_range | count
----------+-------
        0 |     2
        10|     1
        20|     0
        30|     0
        40|     3

This query comes close:

这个查询很接近:

SELECT FLOOR(age / 10) as age_range, COUNT(*)
FROM thing
GROUP BY FLOOR(age / 10) ORDER BY FLOOR(age / 10);

Output:

 age_range | count 
-----------+-------
         0 |     1
         1 |     2
         4 |     3

However, it doesn't show me the ranges which have 0 counts. How can I modify the query so that it also shows the ranges in between with 0 counts?

但是,它没有显示具有0计数的范围。如何修改查询以便它还显示0计数之间的范围?

I found similar * questions for counting ranges, some for 0 counts, but they involve having to specify each range (either hard-coding the ranges into the query, or putting the ranges in a table). I would prefer to use a generic query like that above where I do not have to explicitly specify each range (e.g., 0-10, 10-20, 20-30, ...). I'm using PostgreSQL 9.1.3.

我发现类似的堆栈流问题用于计数范围,一些用于0计数,但它们涉及必须指定每个范围(将范围硬编码到查询中,或将范围放在表中)。我更喜欢使用如上所述的通用查询,我不必明确指定每个范围(例如,0-10,10-20,20-30,...)。我正在使用PostgreSQL 9.1.3。

Is there a way to modify the simple query above to include 0 counts?

有没有办法修改上面的简单查询以包含0计数?

类似:Oracle:如何在一个范围内“分组”?获取MySQL中小数范围的频率分布

2 个解决方案

#1

generate_series to the rescue:

generate_series救援:

select 10 * s.d, count(t.age)
from generate_series(0, 10) s(d)
left outer join thing t on s.d = floor(t.age / 10)
group by s.d
order by s.d

Figuring out the upper bound for generate_series should be trivial with a separate query, I just used 10 as a placeholder.

通过单独的查询确定generate_series的上限应该是微不足道的,我只使用10作为占位符。

This:

generate_series(0, 10) s(d)

essentially generates an inline table called s with a single column d which contains the values from 0 to 10 (inclusive).

实质上生成一个名为s的内联表,其中包含单个列d,其中包含0到10(含)的值。

You could wrap the two queries (one to figure out the range, one to compute the counts) into a function if necessary.

如果需要,您可以将两个查询(一个用于计算范围,一个用于计算计数)包装到函数中。

#2

You need some way to invent the table of age ranges. Row number usually works nicely. Do a cartesian product against a big table to get lots of numbers.

你需要一些方法来发明年龄范围表。行号通常很好用。在大桌子上做笛卡尔产品以获得大量数字。

WITH RANGES AS (
SELECT (rownum - 1) * 10 AS age_range
  FROM ( SELECT row_number() OVER() as rownum
           FROM pg_tables
       ) n
      ,( SELECT ceil( max(age) / 10 )  range_end
           FROM thing
       ) m
  WHERE  n. rownum <= range_end
)
SELECT r.age_range, COUNT(t.age) AS count
  FROM ranges r
  LEFT JOIN thing t ON r.age_range = FLOOR(t.age / 10) * 10
  GROUP BY r.age_range
  ORDER BY r.age_range;

EDIT: mu is too short has a much more elegant answer, but if you didn't have a generate_series function on the db, ... :)

编辑:mu太短有一个更优雅的答案,但如果你没有db上的generate_series函数,... :)

#1