php或mysql提供两列之间的相关系数 [英] php or mysql to provide correlation coefficient between two columns

问题描述

有人能指出我的方向或给我一个PHP或mysql中的查询函数的示例，该函数将返回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 others

mysql_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.'<br>';
$string_version = implode($array2);
echo $string_version.'<br>';

$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<$num; $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<$num; $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<$num; $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);
}

?>

这篇关于php或mysql提供两列之间的相关系数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！