滚动平均效率，PHP MySQL [英] Rolling Average Efficiency, php mysql

问题描述

此脚本使用php和mysql计算一分钟的滚动平均值，以减少离群值对my数据的影响(一分钟= 6个10秒行).它可以正确计算所有内容，但效率不足以一次执行超过150行.我想一次完成尽可能多的行，因为表超过150,000，可能要在5-10,000之间进行，而我每天要输入约8,000行.

This script uses php and mysql to compute a one minute rolling average to reduce the impact of outliers on the my data (one minute = 6 10-second rows). It computes everything correctly, but is not efficient enough to do more than 150 rows at a time. I'd like to do as many rows as I can at a time, possibly between 5-10,000 as my table is over 150,000 and I input approximately 8,000 rows per day.

有人对我如何使此脚本更有效地运行有任何建议吗?

Does anyone have any suggestions as to how I can make this script run more efficiently?

谢谢！

<?php  

//connect to database  

mysql_connect("localhost","user","password");//database connection  
mysql_select_db("database");  

$result = mysql_query("SELECT Timestamp FROM table");  
if (!$result) {  
        die('Could not query:' . mysql_error());  
}  

//get number of rows in table  

$resultA = mysql_query("SELECT * FROM table");  
$num_rows = mysql_num_rows($result);  
echo "There are $num_rows rows.</br>";  

//select column to be averaged  

$resultB = mysql_query("SELECT PortRPMSignal FROM table");  
if (!$resultB) {  
        die('Could not query:' . mysql_error());  
}  

//set start equal to the first row you want to calculate the averages from, likely the first null row    

$start = 5;  

//calculate 1 minute average, the average is correct  

for($i = $start; $i<$num_rows; $i++){    
$output = mysql_result($result,$i);  
$test = mysql_result($resultB,$i)+mysql_result($resultB,$i-1)+mysql_result($resultB,$i-2)+mysql_result($resultB,$i-3)+mysql_result($resultB,$i-4)+mysql_result($resultB,$i-5);
$test2 = $test/6;  
$round = round($test2,4);  
$temp = mysql_query("SELECT Timestamp FROM table");  
if(!$temp){  
    die('Could not query:' . mysql_error());  
}  

//gets timestamp at row $i, and inserts new average value into that row in RPMAve column  

$time = mysql_result($result,$i);  
mysql_query("UPDATE table SET PortMinuteAveRPM = $round WHERE Timestamp =     '$time'");  
}  

推荐答案

对于初学者，可以通过添加

For starters, the initial "count" block here can be cleaned up by adding the COUNT() aggregate:

$resultA = mysql_query("SELECT * FROM table");  
$num_rows = mysql_num_rows($result);  
echo "There are $num_rows rows.</br>"; 

更改为:

$resultA = mysql_query("SELECT COUNT(*) FROM table");  
$row = mysql_fetch_array($result);  
$num_rows = $row[0];
echo "There are $num_rows rows.</br>"; 

这应该可以大大加快速度.如果没有它，您将从表中选择所有数据-一个查询，只要您将其放入表中的数量越多，它的增长就会越慢.

That should speed things up considerably on its own. Without it, you're selecting all of the data from the table - a query that will only grow slower the more you put into the table.

对于您正在计算的平均值，是否存在任何无法直接在MySQL查询中完成的逻辑?诸如此类的东西:

For the averages you're computing, is there any logic required that can't be accomplished directly in a MySQL query? Something such as:

UPDATE table SET PortMinuteAveRPM=(SELECT AVG(PortRPMSignal) FROM table WHERE Timestamp BETWEEN '$startTime' AND '$endTime') WHERE TimeStamp='$endTime'

如果可能的话，这可以使您免于循环浏览结果.

This may save you from looping through results, if it's plausible.

这篇关于滚动平均效率，PHP MySQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！