我有简单的表(测试),我需要执行一些平均值(我想得到行平均值和’总’列平均值).
id var1 var2 var3
1 7 NULL 3
2 10 NULL 6
我注意到MySQL AVG()函数从计数中排除了NULL值(它们不算作0),这就是我想要的.
以类似的方式,我想要这个结果:
var1 var2 var3 total
8.5 NULL 4.5 6.5 (i.e. the overall average is 6.5 *not* 4.3333)
但我的查询看起来像这样:
SELECT
AVG(var1) AS var1,
AVG(var2) AS var2,
AVG(var3) AS var3,
(
AVG(var1)+
AVG(var2)+
AVG(var3)
)/3.0 AS metric_total
FROM test
返回总平均值4.333.
是否可以在单个查询中获得行计算,以与AVG()函数相同的方式从计数中排除NULL条目?
解决方法:
这个怎么样?好又简单……
SELECT
AVG(var1) AS var1,
AVG(var2) AS var2,
AVG(var3) AS var3,
(
SUM(ifnull(var1,0))+
SUM(ifnull(var2,0))+
SUM(ifnull(var3,0))
) / (COUNT(var1)+COUNT(var2)+COUNT(var3))
AS metric_total
FROM test
标签:mysql,sql
来源: https://codeday.me/bug/20190529/1177673.html