mysql - 根据相等的列值集来查询分段结果

Question

我想构造一个查询(或尽可能少的查询)来对数据集进行分组。因此，给定多个存储桶，我想根据特定列返回结果。

因此，给定一个名为 Score 的列，它是一个 double 值，其中包含:

 90.00
 91.00
 94.00
 96.00
 98.00
 99.00

我希望能够将 GROUP BY 子句与如下函数一起使用:

从表 GROUP BY BUCKETS(score, 3) 中选择 MIN(score)、MAX(score)、SUM(score)

理想情况下，这将返回 3 行(将结果分组到 3 个桶中，每组中的计数尽可能接近相等):

 90.00, 91.00, 181.00
 94.00, 96.00, 190.00
 98.00, 99.00, 197.00

有什么函数可以做到这一点吗？我想避免返回所有行并自己计算存储桶段。

戴夫

Answer 1

create table test (
id int not null auto_increment primary key,
val decimal(4,2)
) engine = myisam;

insert into test (val) values 
 (90.00),
 (91.00),
 (94.00),
 (96.00),
 (98.00),
 (99.00);

select min(val) as lower,max(val) as higher,sum(val) as total from (
select id,val,@row:=@row+1 as row
from test,(select @row:=0) as r order by id
) as t
group by ceil(row/2)

+-------+--------+--------+
| lower | higher | total  |
+-------+--------+--------+
| 90.00 |  91.00 | 181.00 |
| 94.00 |  96.00 | 190.00 |
| 98.00 |  99.00 | 197.00 |
+-------+--------+--------+
3 rows in set (0.00 sec)

不幸的是mysql没有像rownum()这样的分析函数，所以你必须使用一些变量来模拟它。一旦完成，您可以简单地使用 ceil() 函数来根据需要对每个 tot 行进行分组。尽管我的英语不好，但希望它能有所帮助。

set @r = (select count(*) from test);
select min(val) as lower,max(val) as higher,sum(val) as total from (
select id,val,@row:=@row+1 as row
from test,(select @row:=0) as r order by id
) as t
group by ceil(row/ceil(@r/3))

或者，使用单个查询

select min(val) as lower,max(val) as higher,sum(val) as total from (
select id,val,@row:=@row+1 as row,tot
from test,(select count(*) as tot from test) as t2,(select @row:=0) as r order by id
) as t
group by ceil(row/ceil(tot/3))