Row average in MySQL -如何从计数中排除空行
Row average in MySQL -如何从计数中排除空行
提问于 2013-07-04 20:46:39
我有一个简单的表格(测试)，我需要执行一些平均值(我想得到一个行平均值和一个‘总’列平均值)。
id var1 var2 var3
1 7 NULL 3
2 10 NULL 6我注意到MySQL AVG()函数从计数中排除了空值(它们不会被算作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条目的行平均值？
回答 4
Stack Overflow用户
回答已采纳
发布于 2013-07-04 21:01:05
这个怎么样？又好又简单...
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 testStack Overflow用户
发布于 2013-07-04 20:48:44
SELECT
avg1, avg2, avg3,
(
(COALESCE(avg1, 0) + COALESCE(avg2, 0) + COALESCE(avg3, 0)) -- only add non-null values
/ -- division
( IF(avg1 IS NULL, 0, 1) + IF(avg2 IS NULL, 0, 1) + IF(avg3 IS NULL, 0, 1) ) -- number of non-null averages
) AS metric_total
FROM
( SELECT
(SELECT AVG(var1) FROM test) AS avg1,
(SELECT AVG(var2) FROM test) AS avg2,
(SELECT AVG(var3) FROM test) AS avg3
) AS sub子查询只允许不重复AVG()函数。
Have fun!
Stack Overflow用户
发布于 2013-07-04 20:51:15
您需要所有列中所有var1、var2和var3的平均值。也许可以对每一列进行子选择，以获得其平均值，并将每个子选择合并在一起。缺点是，只有当每列都有相同数量的非空字段时，这才会起作用
SELECT AVG(FirstAvg)
FROM
(
SELECT AVG(var1) AS FirstAvg
FROM test
UNION
SELECT AVG(var2) AS FirstAvg
FROM test
UNION
SELECT AVG(var3) AS FirstAvg
FROM test
) Sub1要解决这个问题，也许只需在子选择上执行一次AVG
SELECT AVG(FirstAvg)
FROM
(
SELECT var1 AS FirstAvg
FROM test
UNION
SELECT var2 AS FirstAvg
FROM test
UNION
SELECT var3 AS FirstAvg
FROM test
) Sub1要获得其他列的平均值：-
SELECT AVG(var1), AVG(var2), AVG(var3), AVG(FirstAvg) AS total
FROM
(
SELECT var1 AS var1, NULL AS var2, NULL AS var3, var1 AS FirstAvg
FROM test
UNION
SELECT NULL AS var1, var2 AS var2, NULL AS var3, var2 AS FirstAvg
FROM test
UNION
SELECT NULL AS var1, NULL AS var2, var3 AS var3, var3 AS FirstAvg
FROM test
) Sub1页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/17470712
复制相关文章
相似问题
腾讯云开发者
