mysql - 使用聚合函数生成的列

Question

起点

假设我有一个如下所示的表 devTest:

+----+------+  
| id | j    |  
+----+------+  
|  1 |    5 |  
|  2 |    9 |  
|  3 |    4 |  
|  4 |    7 |  
+----+------+

目标

我想要一列指定行与第 j 列中平均值的偏差(以标准偏差表示)。也就是说，该表将如下所示:

+----+------+------------+
| id | j    | jDev       |
+----+------+------------+
|  1 |    5 | -0.5637345 |
|  2 |    9 | 1.2402159  |
|  3 |    4 | -1.0147221 |
|  4 |    7 | 0.3382407  |
+----+------+------------+

我尝试过的

>alter table devTest add column jDev decimal as ((j - avg(j)) / std(j));

我收到一条错误消息，指示不能在生成列的定义中使用聚合函数:

ERROR 1901 (HY000): Function or expression 'avg()' cannot be used in the 
GENERATED ALWAYS AS clause of `jDev`

制作这种专栏应该很常见，所以我很想知道最好的解决方案!

Answer 1

在标准 SQL 中你会这样做:

select id, j, (j - avg(j) over ()) / std(j) over () as jdev
from devtest;

但是MySQL不支持AVG OVER等解析式窗口函数。所以在MySQL中，必须单独选择聚合值:

select d.id, d.j, (d.j - agg.javg) / agg.jstd as jdev
from devtest d
cross join (select avg(j) as javg, std(j) as jstd from devtest) agg;

然后按照 Benjamin Crouzier 在他的回答中建议的那样创建一个 View :

CREATE VIEW v_devtest AS
  select d.id, d.j, (d.j - agg.javg) / agg.jstd as jdev
  from devtest d
  cross join (select avg(j) as javg, std(j) as jstd from devtest) agg;

计算列只能根据同一记录中的其他值计算其值。因此，您尝试执行的操作无法通过计算列完成。