如何连续选择标准差? (在SQL-或R中:) [英] How to select standard deviation within a row? (in SQL - or R :)

问题描述

我想知道是否有一种方法可以从同一行中的MySQL的几个整数字段中选择标准差.显然，如果我使用

I wonder whether there is a way to select the standard deviation from several integer fields in MySQL within the same row. Obviously, if I use

SELECT STDDEV(col1) FROM mytable

我只得到该特定列的标准偏差.假设我有一个像这样的表: id,somefield1,somefield2, integerfield1,integerfield2,integerfield3, ... ,integerfield30. 现在，我想在一行中选择integerfield 1-30的标准偏差，并将其保存为sdfield.我当然可以为此使用统计软件，但是我只是想知道是否有一种方法可以直接在MySQL中进行.

I just get the standard deviation of that particular column. Let´s assume I have a table like: id,somefield1,somefield2, integerfield1,integerfield2,integerfield3, ... ,integerfield30 . Now I´d like to select the standard deviation of integerfield 1-30 within a row and save it AS sdfield . Of course I could use statistical software for this, but I just wonder if there is a way to do it directly in MySQL.

推荐答案

我自己找到了两个解决方案:

I found two solutions on my own:

1)规范化数据库.我最终得到两个表:

1) Normalize the database. I end up with two tables:

表一 uid |信息1 |元信息2

table one uid | information1 | metainformation2

表二 uid | col | result_of_col

table two uid | col | result_of_col

然后，我可以轻松使用标准STDDEV函数.

Then I can easily use the standard STDDEV function.

2)使用R.由于应在统计分析中使用该数据，因此该数据为非规范化格式.因此，很容易进入R并使用以下代码.

2) Use R. The data is a de-normalized format because it should be used in statistical analysis. Thus it´s easy to get into R and use the following code.

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

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

请注意，我只是通过保留选择3-8列来获取此data.frame的数字部分.而且不要被太多的数据所打击(这就是为什么我这次只使用前几行). t()转置了必要的数据，因为sd()仅适用于列.

Note that, I just take the numeric part of this data.frame by leaving selecting the columns 3-8. And dont get hit by too much data (that´s why I only use the first couple of rows this time). t() transposes the data which is necessary because sd() only works with columns.

vsn包中有一个rowSds函数，该函数应该类似于rowMean和rowSum一起工作，但是不建议使用.至少该软件包在Swiss CRAN镜像上不可用;).

There´s a function rowSds around in the vsn package, that is supposed to work analogously to rowMean and rowSum, but somehow this might be deprecated. At least this packages was not available on the Swiss CRAN mirror ;) .

还有其他人.

这篇关于如何连续选择标准差? (在SQL-或R中:)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！