MySQL相关系数r,php或mysql提供两列之间的相关系数

Can anybody point me in the direction of or give me an example of a function in php or query in mysql that will return the correlation coefficient between 2 columns in a mysql table?

解决方案

Final version complete - may help othersmysql_connect($host,$username,$password);

mysql_select_db($db_name);

$array1 = mysql_query("SELECT totalgoals FROM stats");

$array2 = mysql_query("SELECT totalshotsrecieved FROM stats");

$query = "SELECT * FROM stats";

$result = mysql_query($query) or die(mysql_error());

$array1 = array();

$array2 = array();

while($row = mysql_fetch_array($result)){

$array1[] = $row['totalgoals'];

$array2[] = $row['totalshots_made'];

}

$string_version = implode($array1);

echo $string_version.'
';

$string_version = implode($array2);

echo $string_version.'
';

$correlation = Correlation($array1, $array2);

//Displaying the calculated Correlation:

print $correlation;

//The functions that work behind the scene to calculate the

//correlation

function Correlation($arr1, $arr2)

{

$correlation = 0;

$k = SumProductMeanDeviation($arr1, $arr2);

$ssmd1 = SumSquareMeanDeviation($arr1);

$ssmd2 = SumSquareMeanDeviation($arr2);

$product = $ssmd1 * $ssmd2;

$res = sqrt($product);

$correlation = $k / $res;

return $correlation;

}

function SumProductMeanDeviation($arr1, $arr2)

{

$sum = 0;

$num = count($arr1);

for($i=0; $i

{

$sum = $sum + ProductMeanDeviation($arr1, $arr2, $i);

}

return $sum;

}

function ProductMeanDeviation($arr1, $arr2, $item)

{

return (MeanDeviation($arr1, $item) * MeanDeviation($arr2, $item));

}

function SumSquareMeanDeviation($arr)

{

$sum = 0;

$num = count($arr);

for($i=0; $i

{

$sum = $sum + SquareMeanDeviation($arr, $i);

}

return $sum;

}

function SquareMeanDeviation($arr, $item)

{

return MeanDeviation($arr, $item) * MeanDeviation($arr, $item);

}

function SumMeanDeviation($arr)

{

$sum = 0;

$num = count($arr);

for($i=0; $i

{

$sum = $sum + MeanDeviation($arr, $i);

}

return $sum;

}

function MeanDeviation($arr, $item)

{

$average = Average($arr);

return $arr[$item] - $average;

}

function Average($arr)

{

$sum = Sum($arr);

$num = count($arr);

return $sum/$num;

}

function Sum($arr)

{

return array_sum($arr);

}

?>