在 db2 转换上加入 Mysql 更新

Question

我有一个更适合 mysql 的查询

 update metrics m join
   (select repnumber, material, item, color, sum(m2.velocity)/count(*) as avg_velocity
    from metrics m2
    group by repnumber, material, item, color
   ) m2
   using (repnumber, material, item, color)
set m.repvolocity = m2.avg_velocity;

有没有办法改变它以适用于 db2？

Answer 1

在 DB2(和大多数其他数据库)中，您将使用相关子查询:

update metrics m
    set repvolocity = (select avg(m2.velocity)
                       from materials m2
                       where m2.repnumber = m.repnumber and m2.material = m.material and m2.item = m.item and m2.color = m.color
                      );

我应该注意到，DB2 还可以轻松地动态计算值:

select m.*,
       avg(m.velocity) over (partition by repnumber, material, item, color) as repvelocity
from materials;

使用窗口函数，您可能会发现不需要实际存储值。我应该补充一点，MySQL 8+ 也具有此功能。