mysql 加权平均数,单个查询中的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

I need to do the following calculations:

(intermediate calculations)

hours_transit_total = 8+10+3 = 21

hours_standby_total = 4+10+9 = 23

(desired result)

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?

解决方案

Note that

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

and

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