使用聚合函数生成的列 [英] Generated columns using aggregate functions

问题描述

假设我有一个表devTest看起来像这样:

Suppose I have a table devTest that looks like this:

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

目标

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

The goal

I want a column specifying the row's deviation from the mean in the j column (expressed in standard deviations). That is, the table would look like this:

+----+------+------------+
| 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));

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

To which I receive an error indicating that aggregate functions can't be used in the definition of a generated column:

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

制作此类专栏必须很常见，所以我很想知道最好的解决方案！

Making this kind of column must be pretty common, so I'd love to know the best solution!

推荐答案

在标准SQL中，您可以这样做:

In standard SQL you'd do:

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

但是MySQL不支持诸如AVG OVER之类的分析窗口函数.因此，在MySQL中，必须单独选择聚合值:

But MySQL doesn't support analytic window functions such as AVG OVER. So in MySQL, you must select the aggregation values separately:

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)在他的回答中建议的方式创建一个视图:

Then create a view as Benjamin Crouzier suggests in his answer:

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;

计算列只能根据同一记录中的其他值来计算其值.因此，您要执行的操作无法使用计算所得的列来完成.

A computed column can only calculate its value from other values in the same record. So what you are trying to do cannot be done with a calculated column.

这篇关于使用聚合函数生成的列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！