mysql - 在Mysql中跨列使用stddev

Question

我有表 MYsql 表。

name, t1 , t2 , t3. 

有很多名字，以及他们的分数。

Show 将计算 t1,t2,t3 的标准差

尝试选择名称，stddev(t1,t2,t3)，但没有成功。

这有效:

SELECT
  NAME, STDDEV_SAMP(t)   STD
FROM
( SELECT NAME, t1 AS t FROM test._copy
  UNION ALL 
  SELECT NAME, t2 AS t FROM  test._copy
  UNION ALL
  SELECT NAME, t3 AS t FROM  test._copy
) t
GROUP BY  NAME

Answer 1

STDEV() 是一个聚合函数。它从许多行中获取值并产生一个结果。

SELECT
  name,
  STDEV(t1)   standard_deviation_of_t1,
  STDEV(t2)   standard_deviation_of_t2,
  STDEV(t3)   standard_deviation_of_t3
FROM
  yourTable
GROUP BY
  name

-- This will give meaningful results if there are several records for the
-- same name.

不幸的是，它没有从多个字段中获取值。只需几行，为此，您需要重新处理数据以将所有值放在一列中......

SELECT
  name,
  STDEV(t)   standard_deviation_of_all_t_values,
FROM
(
  SELECT name, t1 AS t FROM yourTable
  UNION ALL
  SELECT name, t2 AS t FROM yourTable
  UNION ALL
  SELECT name, t3 as t FROM yourTable
)
  AS data
GROUP BY
  name

编辑

另一个选项，而不是UNION ALL可能是......

SELECT
  name,
  STDEV(CASE map.field_id WHEN 1 THEN t1 WHEN 2 THEN t2 ELSE t3 END)
FROM
  your_table
CROSS JOIN
  (SELECT 1 as field_id UNION ALL SELECT 2 UNION ALL SELECT 3) AS map
GROUP BY
  name