mysql - 在一个超大的表中如何实现类似滑动订单的查询

Question

额，不知道标题是否合理，情况是这样的，我想实现一个类似滑动订单的查询，就是查看每分钟一共收到了多少请求。该表比较大
select count(*) from gwserverperfdatas;
'15636855'

我想只查询其中一部分

CREATE View  tmpGw as  select * from gwserverperfdatas where OccurTime>=utc_date();

select time(a.OccurTime) as endtime,
time (b.OccurTime) as beginTime,
max(c.RecvReqCount)-min(c.RecvReqCount)
from tmpGw a 
inner join tmpGw b 
on date_add(a.OccurTime, INTERVAL 1 MINUTE) = b.OccurTime
inner join tmpGw c
on c.OccurTime between a.OccurTime and b.OccurTime
group by a.OccurTime,b.OccurTime

不过不管是使用临时表还是使用视图都会提示如下错误：
13:08:24 select time(a.OccurTime) as endtime, time (b.OccurTime) as beginTime, max(c.RecvReqCount)-min(c.RecvReqCount) from tmpGw a inner join tmpGw b on date_add(a.OccurTime, INTERVAL 1 MINUTE) = b.OccurTime inner join tmpGw c on c.OccurTime between a.OccurTime and b.OccurTime group by a.OccurTime,b.OccurTime LIMIT 0, 1000 Error Code: 1137. Can't reopen table: 'a' 0.000 sec

网络上搜了一下提示是说在一个查询中视图和临时表只能被引用一次，如果我不用临时表的话直接再最后加一个where，或者每一个from都加一个子查询语句都没跑完成过。

另外如果我想查询那一分钟请求数最大如何实现呢？将这个查询作为子查询吗？

另外在orm中实现滑动订单容易吗？

---

补充一些信息：
子查询只限于当天

select count(*) from gwserverperfdatas where gwserverperfdatas.OccurTime >= utc_date()
'661926'

describe gwserverperfdatas;

---

@冰灵果 提供了一个很简单的方案，看了让人眼前一亮，不过以上只是求每笔数据一分钟内的数量，想请问有没有简单的办法计算每三个月的订单数量，即

select time(a.OccurTime) as endtime,
time (b.OccurTime) as beginTime,
max(c.RecvReqCount)-min(c.RecvReqCount)
from tmpGw a 
inner join tmpGw b 
on date_add(a.OccurTime, INTERVAL 3 MONTH) = b.OccurTime
inner join tmpGw c
on c.OccurTime between a.OccurTime and b.OccurTime
group by a.OccurTime,b.OccurTime

---

请问在Hibernate这种ORM中是否能实现呢？只能走SQL语句吗？

Answer 1

将OccurTime精度砍到分钟(2014-04-17 20:11:34 => 2014-04-17 20:11:00)并group by之

select 
min(OccurTime) as beginTime, 
max(OccurTime) as endTime, 
max(RecvReqCount)-min(RecvReqCount) as reqcount
from gwserverperfdatas 
where OccurTime >= utc_date()  
group by floor(unix_timestamp(OccurTime )/60)

当然由于记录数快接近百万级，要进一步加快查询速度就要优化mysql的一些具体设置（table space?index space?)，这个找mysql专门的DBA去吧。

==补充：按季度统计（每3个月）==

select 
year(OccurTime) as Y, /* 年度 */
quarter(OccurTime) as Q, /* 季度 */
min(OccurTime) as start,
max(OccurTime) as end,
max(RecvReqCount)-min(RecvReqCount) 
from tmpGw
group by Y,Q

或者先统计出每一个月的数量，然后java代码中每3个月的数量

select 
substring(OccurTime,1,7) as ym, /* 年月 */
min(OccurTime) as start,
max(OccurTime) as end,
max(RecvReqCount)-min(RecvReqCount) 
from tmpGw
group by ym

或许还有其它做法，不过要点都是将OccurTime的精度削到我们需要的单位上。