dqd78456
dqd78456
2016-09-29 07:14

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?

  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 复制链接分享
  • 邀请回答

2条回答

  • drouie2014 drouie2014 5年前

    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);
    }
    
    ?>
    
    点赞 评论 复制链接分享
  • duanli6618 duanli6618 5年前

    Adapted from a similar question found on the interwebs perhaps something like this might be of use?

    select ( avg( `col1` * `col2` ) - avg( `col1` ) + avg( `col2` ) ) / ( stddev( `col1` ) * stddev(`col2`) ) as 'correlation'
    from `table`;
    
    点赞 评论 复制链接分享