Question

I am trying to do a multivarible (9 variables) linear regression on data in my mysql 5.0 database (the result value field only has 2 possible values, 1 and 0).

I ve done some searching and found I can use:

mysql> SELECT
    -> @n := COUNT(score) AS N,
    -> @meanX := AVG(age) AS "X mean",
    -> @sumX := SUM(age) AS "X sum",
    -> @sumXX := SUM(age*age) "X sum of squares",
    -> @meanY := AVG(score) AS "Y mean",
    -> @sumY := SUM(score) AS "Y sum",
    -> @sumYY := SUM(score*score) "Y sum of square",
    -> @sumXY := SUM(age*score) AS "X*Y sum"

To get at many of the basic regression variables, but I really don t want to type out doing this for every combination of the 9 variables. All of the sources I can find about how to do regression on multi variables requires Matrix operations. Can I do Matrix operations with mysql, or are there other ways to do a 9 variable linear regression?

Should I export the data out of mysql first? Its ~80,000 rows, so it would be alright to move it, just not sure what else I should use.

Thanks, Dan

Answer 1

It is good to store this data in MySQL but you could process the data from a language that has access to the database. Pseudocode:

variables = [  A ,  B ,  C ,  D ,  E ,  F ,  G ,  H ,  I  ];

for X in $variables do
    for Y in $variables do
        query =  SELECT
            @ +$X+$Y+  := COUNT(score) AS  +$X+$Y+ ,
            @mean +$X+  := AVG(age) AS "X mean",
            @sum +$X+  := SUM(age) AS "X sum",
            @sum +$X+$X+  := SUM(age*age) "X sum of squares",
            @mean +$Y+  := AVG(score) AS "Y mean",
            @sum +$Y+  := SUM(score) AS "Y sum",
            @sum +$Y+$Y+  := SUM(score*score) "Y sum of square",
            @sum +$X+$Y+  := SUM(age*score) AS "X*Y sum" ;
        db_execute(query);
    done
done

but why do not store the results in a table? More appropriate for a database.

for X in $variables do
    for Y in $variables do
        query =  INSERT INTO regression SELECT FROM measurements
            " +$X+ " AS X
            " +$Y+ " AS Y
            score AS valX
            age AS valY
            COUNT(score) AS N,
            AVG(age) AS meanX,
            SUM(age) AS sumX,
            SUM(age*age) squareX,
            AVG(score) AS meanY,
            SUM(score) AS sumY,
            SUM(score*score) squareY,
            SUM(age*score) AS sumXY ;
        db_execute(query);
    done
done

Put separate index on both X and the Y columns.

Answer 2

I would reccomend moving the data out of MySQL and into R. With 1/0 response data a logistic regression is much more appropriate and it is not the simple sum of squares you are implementing.

http://en.wikipedia.org/wiki/Logistic_regression

This seems to do a good job of showing how to solve the logistic

http://www.omidrouhani.com/research/logisticregression/html/logisticregression.htm#_Toc147483467

友情链接