SQL - 存储一个整数列并计算每个存储桶 [英] SQL - bucket an integer column and count each bucket

问题描述

我有一个如下所示的整数列.

I have an integer column like below.

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

我想把它分成 10 行.

I want to bucket this by 10 rows.

100 rows/10 buckets = 10 different buckets 

我想计算每个桶中的行数.

I want to count the no of rows from each bucket.

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

我正在尝试使用 Postgresql 和 MySQL 来实现这一点.

Im trying to implement this with Postgresql and MySQL.

推荐答案

在 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 中:

In 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

这篇关于SQL - 存储一个整数列并计算每个存储桶的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！