postgresql sql - 按范围分组以包含没有值的范围
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10605762/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
sql - group by in ranges to include ranges without values
提问by dcarneiro
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
How can I assure that the score range of 30-39 will be display even when there are no results on that range?
即使在该范围内没有结果，我如何确保显示 30-39 的分数范围？
回答by vyegorov
Try this query (also on SQL Fiddle):
试试这个查询（也在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;
EDIT:
编辑：
You can easily adjust the range by changing parameters to generate_series(). It is possible to use the following construct to make sure rangeswill always cover your scores:
您可以通过将参数更改为 来轻松调整范围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))
for the rangesCTE.
对于rangesCTE。
回答by Nikola Markovinovi?
You cannot like that, but if you add derived table with ranges things become possible:
你不能喜欢那样，但如果你添加带有范围的派生表，事情就变得可能了：
select ranges.range, count(scores.score) as [number of occurences]
from
(
select 0 minRange, 9 maxRange, '0-9' range
union all
select 10, 19, '10-19'
union all
select 20, 29, '20-29'
) ranges
left join scores
on scores.score between ranges.minRange and ranges.maxRange
group by ranges.range
Not sure about syntax of postgresql though.
虽然不确定 postgresql 的语法。
EDIT:Got the syntax right:
编辑：语法正确：
select ranges."range", count(scores.score) as "number of occurences"
from
(
select 0 minRange, 9 maxRange, '0-9' "range"
union all
select 10, 19, '10-19'
union all
select 20, 29, '20-29'
) as ranges
left join scores
on scores.score between ranges.minRange and ranges.maxRange
group by ranges.range
回答by Luis Siquot
select r.range as [score range], count(*) as [number of occurences]
from
(
select ' 0- 9' as range, 9 as endrange
union all select '10-19',19
union all select '20-29',29
union all select '30-39',39
union all select '40-49',49
union all select '50-59',59
union all select '60-69',69
union all select '70-79',79
union all select '80-89',89
union all select '90-99',99
) as r
left join scores s on
r.endrange = case
when s.score > 90 then 99
when s.score > 80 then 89
when s.score > 70 then 79
when s.score > 60 then 69
when s.score > 50 then 59
when s.score > 40 then 49
when s.score > 30 then 39
when s.score > 20 then 29
when s.score > 10 then 19
when s.score > 0 then 9
end
group by r.range
回答by Adrian
Actually, the simplest solution is this (for 2 digit numbers):
实际上，最简单的解决方案是这样的（对于 2 位数字）：
select substr(rssi::text, 0, 2) || '0-' || substr(rssi::text, 0, 2) || '9' as range, count(*)
from sensor_stations
group by substr(rssi::text, 0, 2)
order by count(*) desc;
The output will be something like this:
输出将是这样的：
range | count
-------+-------
10-19 | 3414
30-39 | 1363
20-29 | 1269
40-49 | 769
50-59 | 294
60-69 | 106
70-79 | 5
(7 rows)
