如何从php中的mysql表中找到Average？

问题描述：

I need to calculate average for r1-r25 columns in ratings table.There will be null values in the columns represented by value 99.How to find the average for 25 columns in efficient manner? The sample code I used for a single column is as follows,

Sample PHP code

$s=0;
$n=0; 
$res = mysql_query("SELECT * FROM ratings");  

while($arr=mysql_fetch_array($res)){    
    $rat=$arr['r1'];
    if($rat != 99)
    {
        $s=$rat+$s;
        $n=$n+1;
        $avg=$s/$n; 
    }       
}

我需要计算评级表中r1-r25列的平均值。在表示的列中将显示空值如何以有效的方式找到25列的平均值？我用于单列的示例代码如下， p>

示例PHP代码 p> \ ñ

  $ S = 0; 
的$ n = 0;  
 $ res = mysql_query（“SELECT * FROM ratings”）;  
 
而（$ arr = mysql_fetch_array（$ res））{
 $ rat = $ arr ['r1']; 
 if（$ rat！= 99）
 {
 $ s = $ rat + $ s  ; 
 $ n = $ n + 1; 
 $ avg = $ s / $ n;  
} 
} 
  code>  pre> 
  div>

答

$res = mysql_query("SELECT * FROM ratings WHERE `r1` NOT IN ( 99 )");

Now you don't need if check in your while loop.

答

Use the database for this work:

SELECT AVG(case when r1 <> 99 then r1 end) as r1_avg,
       AVG(case when r2 <> 99 then r2 end) as r2_avg,
       . . .
       AVG(case when r25 <> 99 then r25 end) as r25_avg     
FROM ratings;

This will be faster because you don't have to bring all the data over to the application for sequential processing.

It does require more typing (or use a spreadsheet to generate the code). This is because you are storing data in columns, when the data should really be in separate rows -- one row per whatever and the rating (and perhaps a rating number). With that data structure, the query would be both shorter and better performing.

Also, SQL has a great mechanism for storing NULL values, and you should use it. It is called NULL, not "99".

如何从php中的mysql表中找到Average？

相关推荐