postgresql 按价格范围分组
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1999421/
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
group by price range
提问by David
Say I have a table of real estate properties:
假设我有一张房地产表：
A 15,000
B 50,000
C 100,000
D 25,000
I'd like to group them by 0 - 49,999, 50,000 - 99,999 and 100,000 - 200,000
我想将它们按 0 - 49,999、50,000 - 99,999 和 100,000 - 200,000 分组
So the result should be:
所以结果应该是：
0 - 49k (2)
50k - 99k (1)
100k - 200k (1)
Is there a way to do that in one SQL statement? I'm using Postgres by the way.
有没有办法在一个 SQL 语句中做到这一点？顺便说一下，我正在使用 Postgres。
回答by Nicht Verstehen
You can GROUP BYan experession, something like that:
你可以表达GROUP BY，像这样：
SELECT price/50000*50000 AS minPrice,
(price/50000+1)*50000-1 AS maxPrice,
COUNT(*)
FROM table
GROUP BY price/50000;
回答by tvanfosson
Depends on whether you would accept a subselect in the statement and still call it one statement. Assuming that you want your ranges to extend, a subselect with a case statement to set the range, then an outer select grouping by range would work. If all of your ranges were the same size it would be easier as you could just divide by the range size and group by that.
取决于您是否会接受语句中的子选择并仍将其称为一个语句。假设您希望您的范围扩展，一个带有 case 语句的子选择来设置范围，然后按范围分组的外部选择将起作用。如果您的所有范围的大小都相同，那会更容易，因为您只需除以范围大小并按此分组即可。
select t.range, count(*) as num
from
(select case
when price < 50000 then '0 - 49K'
when price >= 50000 and price < 100000 then '50 - 99K'
when price >= 100000 and price < 200000 then '100 - 199K'
...
end
as range,
price
from table) as t
group by range
