mysql - 如何优化SQL an/average(a)？

Question

在我的 SQL 脚本中，我想标准化来自这样的子查询的值

select y/avg(y) from (
   select x*z as y from test_table
)T

我知道这个解决方案会起作用

select y/avg_y from (
       select id, x*z as y from test_table
    )T
join(
  select avg(y) as avg_y
  from(
    select x*z as y from test_table
  )a
)T2

但是我不想计算 y 两次，有什么好主意吗？

Answer 1

ANSI标准机制是:

select y / (avg(y) over ())
from (select x*z as y from test_table) T

或者:

select x * z / (avg(x * z) over ())
from test_table;

这使用大多数数据库都支持的窗口函数。

编辑:

MySQL 不支持窗口函数。如果你不想计算两次，你可以使用变量技巧:

select y / (@sy / @rn)
from (select x*z as y,
             @rn := if(x*z is not null, @rn + 1, @rn),
             @s := if(x*z is not null, @s + x*z, @s)
      from test_table t cross join
           (select @rn := 0, @sy := 0) params
     ) t;

在外部查询中使用变量之前，应先为子查询创建变量。

而且，编写不带变量的查询的更简单方法不会使用那么多子查询:

select (t.x * t.z)/ a.avg_y
from test_table t cross join
     (select avg(x*z) as avg_y
      from test_table
     ) a;