MySQL - 单个查询中的两个相关加权平均值 [英] MySQL - two dependent weighted averages in a single query
本文介绍了MySQL - 单个查询中的两个相关加权平均值的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
这个问题扩展了上一个问题.除了一些单独的加权平均值之外,我还想添加一个加权平均值.
This question extends a previous question. I want to add a weighted average total in addition to some individual weighted averages.
我有一个 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
hours_total = 21+23 = 44
(新的额外期望结果)
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
load_total_weighted_mean = 36.667*(21/44)+13.913*(23/44) = 24.772
是否可以在单个查询中执行此操作?最好的设计是什么?
Is it possible to do this in a single query? What would the best design be?
推荐答案
通过扩展我对您上一个问题的回答,这同样简单:
By extension of my answer to your previous question, this is just as simple:
SELECT sum(hours_transit * load_transit) / sum(hours_transit),
sum(hours_standby * load_standby) / sum(hours_standby),
(sum(hours_transit * load_transit + hours_standby * load_standby)) / sum(hours_transit + hours_standby)
FROM table1
这是对应的sqlfiddle.
这篇关于MySQL - 单个查询中的两个相关加权平均值的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文