SQL-bucket是一个整数列，并对每个bucket进行计数

Question

我有一个整数列，如下所示。

ID
---
1
2
3
..
..
99
100

我想把它排成10排。

100 rows/10 buckets = 10 different buckets 

我想数一下每个桶的行数。

id    | total rows
----------------
1-10  | 10
11-20 | 10
21-30 | 10
...   | ..
...   | ..
91-100| 10

我试图用Postgresql和MySQL来实现这一点。

Answer 1

在mysql:中

select
concat(min((id-1) div 10) * 10 + 1, '-', min((id-1) div 10) * 10 + 10) 'id',
count(id) 'total rows'
from yourtable
group by (id-1) div 10

进展中：

select
(min((id-1) / 10) * 10 + 1) || '-' || (min((id-1) / 10) * 10 + 10) as id,
count(id) as total_rows
from yourtable
group by (id-1) / 10
order by (id-1) / 10

Answer 2

您可以使用truncate函数，如下所示：

Select concat(min(id),'-',max(id)) as ids, count(1) as total_rows
From your_table t
Group by truncate(id-1, -1)   

Answer 3

您可以尝试使用事例表达式

select case 
when id between 1 and 10 then '1-10'
when id between 11 and 20 then '11-20'
when id between 21 and 30 then '21-30' end as id, count(*) as total_rows
from tablename
group by case 
when id between 1 and 10 then '1-10'
when id between 11 and 20 then '11-20'
when id between 21 and 30 then '21-30' end

Answer 4

在Postgres和MySQL中，以下内容都应该有效：

select concat_ws('-', ceiling(id / 10.0) * 10 - 9, ceiling(id / 10.0) * 10) as id_range,
count(*)
from t
group by id_range
order by min(id);

除了在两个数据库中都是相同的之外，我还认为这可能是任何一个数据库中最简单的构造。

这里有两个db<gt；fiddles：适用于Postgres和MySQL。