sql-按范围分组以包括没有值的范围 [英] sql - group by in ranges to include ranges without values
本文介绍了sql-按范围分组以包括没有值的范围的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
假设与这种问题类似的情况 。我想得到以下结果:
Suppose a scenario similar to this question. I want to get the following results:
score range | number of occurrences
-------------------------------------
0-9 | 11
10-19 | 14
20-29 | 3
... | ...
然后我可以使用选择的答案作为解决方案:
And I can use the selected answer as a solution:
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?
How can I assure that the score range of 30-39 will be display even when there are no results on that range?
推荐答案
尝试此查询(也在 SQL小提琴):
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()
来轻松调整范围。可以使用以下结构来确保范围
始终覆盖您的分数:
You can easily adjust the range by changing parameters to generate_series()
. It is possible to use the following construct to make sure ranges
will always cover your scores:
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。
这篇关于sql-按范围分组以包括没有值的范围的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文