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

在postgres中：

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

您可以尝试使用case表达式

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