Jump to content

Archived

This topic is now archived and is closed to further replies.

AV1611

averaging by weeks...

Recommended Posts

This query was supposed to give the average for a 7 day period, but I guess I can't use LIMIT to pick the slot of dates...

Can someone point me in the right direction??? I actually have a table with about 100 rows, and I just want to create an array that has the average of each seven day period in order so I can plot it on a graph...

the full script repeat this a dozen times and only the limit part changes... but it don't work...
[code]

$result1 = mysql_query("Select avg(pass_avg) as pass_avg from firstpassavg order by it_date DESC limit 7,7")or die(mysql_error());
while ($row = mysql_fetch_array($result1)){
$graphValues2[]=round($row['pass_avg'],0);
echo round($row['pass_avg'],0);
[/code]

Share this post


Link to post
Share on other sites
Looks like this will do it...I'll have to tweak it some...
[code]

function average($a){
  return array_sum($a)/count($a);
}

$result1 = mysql_query("Select pass_avg from firstpassavg order by it_date DESC limit 0,7 ")or die(mysql_error());
while ($row = mysql_fetch_array($result1)){
$graphValues2[]=round($row['pass_avg'],0);

}

echo average($graphValues2);
[/code]

Share this post


Link to post
Share on other sites

[!--quoteo--][div class=\'quotetop\']QUOTE[/div][div class=\'quotemain\'][!--quotec--]Can someone point me in the right direction??? [/quote]

Frankly, I doubt it. You don't give us any clues about the data you are starting with

Share this post


Link to post
Share on other sites
I'm sorry... we're in the middle of an ISO audit and this is part of a thing tomorrow for it: Here is where I am now. I've fixed the major issue, and have a working script, but the script plots from a table that has a date and a number that is an average. the graph charts each day's average, then a second line charts the previous seven days average. so the line plots every seven days... I did it because I'm not smart enough to do a true rolling seven day average (each day plots the previous seven day average daily instead of each week plotting the seven day average)

Could someone help me convert this to a true seven day rolling average? I can't get my head around how to do the loops...

the table has fields it_date and pass_avg

the script is as follows:
[code]
<?php
$user="";
$host = "localhost";
$password="";
$database = "teknetix";
mysql_connect($host,$user,$password);
mysql_select_db($database);
// creates the daily plot array
$result = mysql_query("Select distinct firstpass3.it_date,(firstpass4.pass/(firstpass3.pass+firstpass4.pass))*100 as passpercent from firstpass3,firstpass4 where firstpass3.it_date=firstpass4.it_date order by firstpass3.it_date DESC limit 90")or die(mysql_error());
while ($row = mysql_fetch_array($result)){
$graphValues[]=round($row['passpercent'],0);
}


function average($a){
  return array_sum($a)/count($a);
}

// this section is repeated 13 times (90 days/7)
$result1 = mysql_query("Select pass_avg from firstpassavg order by it_date DESC limit 0,7 ")or die(mysql_error());
while ($row = mysql_fetch_array($result1)){
$graphValuesa[]=round($row['pass_avg'],0);
// echo $row['pass_avg']."<br/>";
}
// echo "<br/>". average($graphValuesa);
$graphValues3[] = average($graphValuesa);

$result1 = mysql_query("Select pass_avg from firstpassavg order by it_date DESC limit 7,7 ")or die(mysql_error());
while ($row = mysql_fetch_array($result1)){
$graphValuesb[]=round($row['pass_avg'],0);
}
$graphValues3[] = average($graphValuesb);

$result1 = mysql_query("Select pass_avg from firstpassavg order by it_date DESC limit 14,7 ")or die(mysql_error());
while ($row = mysql_fetch_array($result1)){
$graphValuesc[]=round($row['pass_avg'],0);
}

$graphValues3[] = average($graphValuesc);

$result1 = mysql_query("Select pass_avg from firstpassavg order by it_date DESC limit 21,7 ")or die(mysql_error());
while ($row = mysql_fetch_array($result1)){
$graphValuesd[]=round($row['pass_avg'],0);
}

$graphValues3[] = average($graphValuesd);

$result1 = mysql_query("Select pass_avg from firstpassavg order by it_date DESC limit 28,7 ")or die(mysql_error());
while ($row = mysql_fetch_array($result1)){
$graphValuese[]=round($row['pass_avg'],0);

}

$graphValues3[] = average($graphValuese);


$result1 = mysql_query("Select pass_avg from firstpassavg order by it_date DESC limit 35,7 ")or die(mysql_error());
while ($row = mysql_fetch_array($result1)){
$graphValuesf[]=round($row['pass_avg'],0);

}

$graphValues3[] = average($graphValuesf);


$result1 = mysql_query("Select pass_avg from firstpassavg order by it_date DESC limit 42,7 ")or die(mysql_error());
while ($row = mysql_fetch_array($result1)){
$graphValuesg[]=round($row['pass_avg'],0);

}

$graphValues3[] = average($graphValuesg);


$result1 = mysql_query("Select pass_avg from firstpassavg order by it_date DESC limit 49,7 ")or die(mysql_error());
while ($row = mysql_fetch_array($result1)){
$graphValuesh[]=round($row['pass_avg'],0);

}

$graphValues3[] = average($graphValuesh);


$result1 = mysql_query("Select pass_avg from firstpassavg order by it_date DESC limit 56,7 ")or die(mysql_error());
while ($row = mysql_fetch_array($result1)){
$graphValuesi[]=round($row['pass_avg'],0);

}

$graphValues3[] = average($graphValuesi);


$result1 = mysql_query("Select pass_avg from firstpassavg order by it_date DESC limit 63,7 ")or die(mysql_error());
while ($row = mysql_fetch_array($result1)){
$graphValuesj[]=round($row['pass_avg'],0);

}

$graphValues3[] = average($graphValuesj);


$result1 = mysql_query("Select pass_avg from firstpassavg order by it_date DESC limit 70,7 ")or die(mysql_error());
while ($row = mysql_fetch_array($result1)){
$graphValuesk[]=round($row['pass_avg'],0);

}

$graphValues3[] = average($graphValuesk);


$result1 = mysql_query("Select pass_avg from firstpassavg order by it_date DESC limit 77,7 ")or die(mysql_error());
while ($row = mysql_fetch_array($result1)){
$graphValuesl[]=round($row['pass_avg'],0);

}

$graphValues3[] = average($graphValuesl);


$result1 = mysql_query("Select pass_avg from firstpassavg order by it_date DESC limit 84,7 ")or die(mysql_error());
while ($row = mysql_fetch_array($result1)){
$graphValuesm[]=round($row['pass_avg'],0);

}

$graphValues3[] = average($graphValuesm);
//
$graphValues3[] = average($graphValuesm);

// from here below is how I make the graph

// Define .PNG image
header("Content-type: image/png");
$imgWidth=676;
$imgHeight=501;
// Create image and define colors
$image=imagecreate($imgWidth, $imgHeight);
$colorWhite=imagecolorallocate($image, 255, 255, 255);
$colorGrey=imagecolorallocate($image, 192, 192, 192);
$colorBlue=imagecolorallocate($image, 0, 0, 255);
$colorBlack=imagecolorallocate($image, 0,0,0);
$colorRed=imagecolorallocate($image, 255,0,0);

// Create border around image
// right vert
imageline($image, 674, 674, 674, 0, $colorBlack);
// bottom horiz
imageline($image, 0, 499, 674, 499, $colorBlack);
// top horiz
imageline($image, 674, 0, 0, 0, $colorBlack);


imageline($image, 0,374,674,374,$colorBlack);
imageline($image, 0,249,674,249,$colorBlack);
imageline($image, 0,124,674,124,$colorBlack);

imageline($image, 224,0,224,499,$colorBlack);
  imageline($image, 449,0,449,499,$colorBlack);


// left vert
imageline($image, 0, 0, 0, 674, $colorBlack);

// Create grid i<52 means 52 verticals
for ($i=1; $i<90; $i++){
//H
imageline($image, $i*7.5, 0, $i*7.5, 675, $colorGrey);
//V
imageline($image, 0, $i*25, 675, $i*25, $colorGrey);
}


// Create line graph
for ($i=0; $i<90; $i++){
    if (isset($graphValues[$i+1])) {
    imageline($image, ($i*7.5)/1,
    (500-($graphValues[$i])*25 ), (($i+1)*7.5)/1,
    (500-($graphValues[$i+1])*25 ), $colorBlue);
    }
}

// Create line graph
for ($i=0; $i<13; $i++){
    if (isset($graphValues3[$i+1])) {
    imageline($image, ($i*7.5),
    (500-($graphValues3[$i])*25 ), (($i+1)*7.5),
    (500-($graphValues3[$i+1])*25 ), $colorRed);
    }
}


// Output graph and clear image from memory
imagepng($image);
imagedestroy($image);

?>
[/code]

Share this post


Link to post
Share on other sites
This is the method I employ for rolling averages

1 ) put all the values into a single array

2 ) get the size of the array

3 ) Use array_slice() to get successive groups of 7 elements

<?php

[code]$data = array(1,2,4,6,5,8,7,9,3,4,6,7,8,4,5,6,2,3,4,8,7,9,4,6,7);

$size = count($data);

$avgs = $sums = $nums = array();
for ($i = 0; $i < $size-6; $i++) {
     $tmp = array_slice($data, $i, 7);
     $s = array_sum($tmp);
     $avgs[$i+6] = $s/7;
     $nums[$i+6] = join (',',$tmp);
     $sums[$i+6] = $s;
}

// output
echo "<TABLE border='1'>\n";
echo "<TR>
       <TH>Data</TH>
       <TH>Average</TH>
       <TH>Numbers</TH>
       <TH>Sum</TH>
       </TR>\n";
foreach ($data as $k => $v) {
         echo "<TR>
                <TD>$v</TD>
                <TD>".number_format($avgs[$k],3)."</TD>
                <TD>$nums[$k]</TD>
                <TD>$sums[$k]</TD>
         </TR>\n";
}
echo "</TABLE>\n";
?>[/code]

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.