How to choose a standard deviation in a line? (in SQL - or R :)

Question

I wonder if there is a way to choose the standard deviation from multiple integer fields in MySQL on the same line. Obviously if i use

SELECT STDDEV(col1) FROM mytable 

I just get the standard deviation of this particular column. Suppose I have a table like: id,somefield1,somefield2, integerfield1,integerfield2,integerfield3, ... ,integerfield30 . Now I would like to select the standard deviation of the integer field 1-30 in the row and save it as AS sdfield. Of course, I could use statistical software for this, but I'm just wondering if there is a way to do this directly in MySQL.

Answer 1

I found two solutions myself:

1) Normalize the database. I get two tables:

table first uid | information1 | metainformation2

table second uid | col | result_of_col

Then I can easily use the standard STDDEV function.

2) Use R. Data is a de-standardized format, as it should be used in statistical analysis. Thus, it is easy to get into R and use the following code.

sd (t (data set [1: 4,3: 8]))

Note that I just take the numerical part of this data.frame, leaving the selection of columns 3-8. And don't get too much data (so this time I use only the first couple of rows). t () transfers the data that is needed because sd () only works with columns.

There is a rowSds function in the vsn package that should work similarly to rowMean and rowSum, but for some reason this might be deprecated. At least these packages were not available in the Swiss CRAN mirror;).

Hth someone else.

Answer 2

With R:

 df <- your.pull sd(t(df[sapply(df, is.numeric)])) 

Pull data using RMySQL or RODBC, delete non-numeric columns, transpose and use sd.

Answer 3

for simplicity, suppose you have n columns named A , B , C ....:

 SELECT SQRT( (A*A + B*B + C*C + ...)/n - (A+B+C+...)*(A+B+C+...)/n/n) AS sd FROM table; 

Answer 4

Have you tried to use UNION to efficiently put all column values ​​in separate rows? Something like this might be:

 SELECT STDDEV(allcols) FROM ( SELECT col1 FROM table WHERE id=requiredID UNION SELECT col2 FROM table WHERE id=requiredID UNION SELECT col3 FROM table WHERE id=requiredID UNION SELECT col4 FROM table WHERE id=requiredID UNION SELECT col5 FROM table WHERE id=requiredID )