SQL频率分布查询来计算含有0组计数的范围 [英] SQL frequency distribution query to count ranges with group-by and include 0 counts

问题描述

鉴于：

 表'thing'：
 
年龄
 --- 
 3.4 
 3.4 
 10.1 
 40 
 45 
 49 
  

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

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

此查询即将结束：

<$ p （年龄/ 10）; $ p $ SELECT FLOOR（age / 10）作为age_range，COUNT（*）
FROM thing
GROUP BY FLOOR（age / 10）ORDER BY FLOOR


输出：

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

但是，它并没有显示出有0个计数的范围。我怎样才能修改查询，以便它也显示范围之间的0计数？我发现计数范围类似的stackoverflow问题，一些0计数，但他们涉及必须指定每个范围（将范围硬编码到查询中或将范围放入表中）。我宁愿使用上面的通用查询，我不必明确指定每个范围（例如0-10,10-20,20-30，...）。我使用的是PostgreSQL 9.1.3。

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

类似：

Oracle：如何到group by超过范围？

在MySQL中获取小数范围的频率分布

解决方案

generate_series 来拯救：

  select 10 * sd，count（t.age）
 from generate_series（0，10）s（d）
 left sd = floor（t.age / 10）
 group by sd 
 order by sd 
  

找出 generate_series 的上界应该是一个单独的查询，它只是用10作为占位符。

$ p $ generate_series（0，10）s（d）


实际上会生成一个名为 s 的内联表，其中一列为 d ，其中包含从0到10（in

如果需要，您可以将两个查询（一个计算出范围，一个计算计数）包装到函数中。

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.,

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?

I found similar stackoverflow 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.

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

Similar:
Oracle: how to "group by" over a range?
Get frequency distribution of a decimal range in MySQL

解决方案

generate_series to the rescue:

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.

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).

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

这篇关于SQL频率分布查询来计算含有0组计数的范围的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！