Jump to content

averaging by weeks...


AV1611

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]
Link to comment
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]

Link to comment
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]
Link to comment
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]
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

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.