单个查询中的MySQL加权平均值 [英] MySQL weighted average in a single query

问题描述

我有一个如下所示的MySQL表:

I have a MySQL table which looks like this:

id  load_transit    load_standby    hours_transit   hours_standby
1   40              20              8               4
2   30              15              10              10
3   50              10              3               9

我需要进行以下计算:

(中间计算)

hours_transit_total = 8+10+3 = 21
hours_standby_total = 4+10+9 = 23

(期望的结果)

load_transit_weighted_mean = 40*(8/21) + 30*(10/21) + 50*(3/21) = 36.667
load_standby_weighted_mean = 20*(4/23) + 15*(10/23) + 10*(9/23) = 13.913

是否可以在单个查询中执行此操作?最好的设计是什么?

Is it possible to do this in a single query? What would the best design be?

推荐答案

请注意

40*(8/21) + 30*(10/21) + 50*(3/21) =
(40*8)/21 + (30*10)/21 + (50*3)/21 =
(40*8 + 30*10 + 50*3)/21

和

20*(4/23) + 15*(10/23) + 10*(9/23) =
(20*4)/23 + (15*10)/23 + (10*9)/23 =
(20*4 + 15*10 + 10*9)/23

哪个可以让您获得想要的结果

Which allows you to get the results you want using

SELECT sum(hours_transit * load_transit) / sum(hours_transit),
       sum(hours_standby * load_standby) / sum(hours_standby)
FROM your_table

这篇关于单个查询中的MySQL加权平均值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！