php - 滚动平均效率，php mysql

Question

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

关于如何让这个脚本更高效地运行，有人有什么建议吗？

谢谢!

<?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'");  
}  

Answer 1

对于初学者来说，可以通过添加 COUNT() 来清理这里的初始“count” block 。聚合:

$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>"; 

这应该会大大加快速度。没有它，您将选择表中的所有数据 - 一个查询只会随着您放入表中的数据越多而变得越慢。

对于您正在计算的平均值，是否需要任何无法在 MySQL 查询中直接完成的逻辑？比如:

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

如果合理的话，这可以避免循环遍历结果。