我有大量的数据,我试图根据24小时内发生的事件计算一列的总和。
表如下:
ID Customer LOCATION FROM_DATETIME TO_DATETIME Amount 24Revenue
23100470 Customer01 Idaho 2014-03-23 23:40 2014-03-24 00:00 8.20 26.65
23111106 Customer01 Idaho 2014-03-24 07:06 2014-03-24 07:26 0.00 22.14
23111865 Customer01 Idaho 2014-03-24 07:27 2014-03-24 07:30 0.00 22.14
23112909 Customer01 Idaho 2014-03-24 07:56 2014-03-24 08:06 3.69 22.14
23122768 Customer01 Idaho 2014-03-24 11:38 2014-03-24 11:46 3.28 18.45
23125519 Customer01 Idaho 2014-03-24 12:35 2014-03-24 12:38 1.23 15.17
23126385 Customer01 Idaho 2014-03-24 12:53 2014-03-24 13:00 2.87 13.94
23141213 Customer01 Idaho 2014-03-24 19:58 2014-03-24 20:16 7.38 11.07
23155172 Customer01 Idaho 2014-03-25 06:52 2014-03-25 07:01 3.69 3.69
23165719 Customer02 Idaho 2014-03-25 10:52 2014-03-25 13:34 44.97 70.21
23173857 Customer02 Idaho 2014-03-25 13:36 2014-03-25 13:43 2.87 25.24
23174971 Customer02 Idaho 2014-03-25 14:00 2014-03-25 14:04 1.64 22.37
23176607 Customer02 Idaho 2014-03-25 14:40 2014-03-25 14:52 4.51 20.73
23178488 Customer02 Idaho 2014-03-25 15:24 2014-03-25 16:04 0.00 16.22
23181743 Customer02 Idaho 2014-03-25 16:42 2014-03-25 17:45 16.22 16.22
此时,我选择一个周期为1周的数据集:
SELECT ID, Customer, from_datetime, to_datetime, location
WHERE to_datetime BETWEEN '2014-03-24 00:00:00' AND '2014-03-31'
AND location='Idaho';
这个查询返回$recordset。
然后在PHP中使用该数据集,我循环遍历每条记录,然后再次查询数据库,以在该时间的24小时内对具有相同车牌的所有记录的收入列求和:
foreach ($recordset as $key => $value) {
$fromdate=date('Y-m-d H:i:s',strtotime($rsRentals[$key]['to_datetime']));
$todate=strtotime(date('Y-m-d H:i:s', strtotime($fromdate)) . " +24 hours");
SELECT SUM(amount) as revenue WHERE plate='$recordset[$key]['plate']'
AND location = 'Idaho' AND to_datetime >= '$fromdate' AND from_datetime < '$todate'
}
我的问题是,根据我的计算(50个总和在2分钟内),它将需要大约13-14小时来求和~20000条记录。这是太长了,我认为必须有一个更简单的方法来运行这个查询与一个JOIN或其他东西,但我的尝试失败了。以上是我唯一能够成功完成的设置,尽管数据集有限。
有没有人对我能做些什么来获得相同的结果,但更快,更有效的查询的建议?
使用第一个查询获取车牌列表,
然后将其作为子查询插入到一个更大的查询中,其中plate是group by,而where是TO_DATETIME和TO_DATETIME-24之间的时间