我有一个简单的表值(int),创建(时间戳)
我想做一个查询,返回任意数量的带有avg(value)和avg(created)的行。分组函数是created的order by,这意味着如果我要求2行,我应该根据created排序的第一行获得一个集合。
我有下表:
create table log(value int,created timestamp);
insert into log values
(1,'2016-01-01 00:00:00'),
(2,'2016-01-01 01:00:00'),
(3,'2016-01-01 02:00:00'),
(4,'2016-01-01 03:00:00'),
(5,'2016-01-01 04:00:00'),
(6,'2016-01-01 05:00:00'),
(7,'2016-01-01 06:00:00'),
(8,'2016-01-01 07:00:00'),
(9,'2016-01-01 08:00:00');
http://sqlfiddle.com/#!9/b9a94
我想检索应该是的3行
2-2016-01-01 01:00:00
5-2016-01-01 04:00:00
8-2016-01-01 07:00:00
是否可以在不使用java或php处理的情况下在单个查询中完成?
例如,按3分组时,按创建日期排序。您可以将3更改为您想要分组的大小。
SELECT avg(sub.value), avg(sub.created)
from (
SELECT @row_number:=@row_number+1 AS row_number,value, created
FROM log, (SELECT @row_number:=0) AS t
ORDER BY created
) sub
group by floor((sub.row_number-1)/3)
更新
如果您总是希望根据行数将其分为3组,您可以执行以下操作:
SELECT avg(sub.value), avg(sub.created)
from (
SELECT @row_number:=@row_number+1 AS row_number, floor((@row_number-1)/c.num) groupid,value, created
FROM log,
(SELECT @row_number:=0) AS t,
(SELECT ceil(COUNT(*)/3) num FROM log) c
ORDER BY log.created
) sub
GROUP BY sub.groupid