sql - 按范围分组以包括没有值的范围

Question

假设一个与此类似的场景 question .我想得到以下结果:

score range  | number of occurrences
-------------------------------------
   0-9       |        11
  10-19      |        14
  20-29      |         3
   ...       |       ...

我可以使用所选答案作为解决方案:

select t.range as [score range], count(*) as [number of occurences]
from (
  select case  
    when score between 0 and 9 then ' 0- 9'
    when score between 10 and 19 then '10-19'
    else '20-99' end as range
  from scores) t
group by t.range

即使在该范围内没有结果，我如何确保显示 30-39 的分数范围？

Answer 1

试试这个查询(也在 SQL Fiddle 上):

WITH ranges AS (
    SELECT (ten*10)::text||'-'||(ten*10+9)::text AS range,
           ten*10 AS r_min, ten*10+9 AS r_max
      FROM generate_series(0,9) AS t(ten))
SELECT r.range, count(s.*)
  FROM ranges r
  LEFT JOIN scores s ON s.score BETWEEN r.r_min AND r.r_max
 GROUP BY r.range
 ORDER BY r.range;

---

编辑:

您可以通过更改 generate_series() 的参数轻松调整范围。可以使用以下结构来确保 ranges 始终覆盖您的分数:

SELECT (ten*10)::text||'-'||(ten*10+9)::text AS range,
       ten*10 AS r_min, ten*10+9 AS r_max
  FROM generate_series(0,(SELECT max(score)/10 FROM scores)) AS t(ten))

范围 CTE。