如何:MYSQL从多个列中求平均值，不包括Null值和更改数字除以

使用以下MySQL 5.6查询SELECT sum(col1 + col2 + col3) / 3 AS Result FROM table1

我怎么能只忽略NULL值来做同样的事情呢？另外，调整除以数值减去NULL值？

我们的实验室使用一个感官小组来评估我们产品的某些质量点。每个小组成员对每个样本的得分为-1、0或1。对于任何给定的样本，我们都可以得到1到15分，他们希望将其平均为"结果"。

参考问题24398431

下面是我刚刚测试的查询。我没有收到任何错误，但没有得到任何数据返回。我会尽我所能的帮助。

SELECT ndx, t_stamp, fv, name, lot, Pnl1, Pnl2, Pnl3, Pnl4, Pnl5, Pnl6, Pnl7, 
Pnl8, Pnl9, Pnl10, 
((sum((CASE WHEN (Pnl1 IS NOT NULL) THEN Pnl1 ELSE 0 END)
+(CASE WHEN (Pnl2 IS NOT NULL) THEN Pnl2 ELSE 0 END)
+(CASE WHEN (Pnl3 IS NOT NULL) THEN Pnl3 ELSE 0 END)
+(CASE WHEN (Pnl4 IS NOT NULL) THEN Pnl4 ELSE 0 END)
+(CASE WHEN (Pnl5 IS NOT NULL) THEN Pnl5 ELSE 0 END)
+(CASE WHEN (Pnl6 IS NOT NULL) THEN Pnl6 ELSE 0 END)
+(CASE WHEN (Pnl7 IS NOT NULL) THEN Pnl7 ELSE 0 END)
+(CASE WHEN (Pnl8 IS NOT NULL) THEN Pnl8 ELSE 0 END)
+(CASE WHEN (Pnl9 IS NOT NULL) THEN Pnl9 ELSE 0 END)
+(CASE WHEN (Pnl10 IS NOT NULL) THEN Pnl10 ELSE 0 END)))
/
(sum((CASE WHEN (Pnl1 IS NOT NULL) THEN 1 ELSE 0 END)
+(CASE WHEN (Pnl2 IS NOT NULL) THEN 1 ELSE 0 END)
+(CASE WHEN (Pnl3 IS NOT NULL) THEN 1 ELSE 0 END)
+(CASE WHEN (Pnl4 IS NOT NULL) THEN 1 ELSE 0 END)
+(CASE WHEN (Pnl5 IS NOT NULL) THEN 1 ELSE 0 END)
+(CASE WHEN (Pnl6 IS NOT NULL) THEN 1 ELSE 0 END)
+(CASE WHEN (Pnl7 IS NOT NULL) THEN 1 ELSE 0 END)
+(CASE WHEN (Pnl8 IS NOT NULL) THEN 1 ELSE 0 END)
+(CASE WHEN (Pnl9 IS NOT NULL) THEN 1 ELSE 0 END)
+(CASE WHEN (Pnl10 IS NOT NULL) THEN 1 ELSE 0 END)))) AS Result
FROM vdk
WHERE Pnl1 IS NOT NULL AND Pnl2 IS NOT NULL AND Pnl3 IS NOT NULL AND Pnl4 IS 
NOT NULL AND Pnl5 IS NOT NULL
AND Pnl6 IS NOT NULL AND Pnl7 IS NOT NULL AND Pnl8 IS NOT NULL AND Pnl9 IS 
NOT NULL AND Pnl10 IS NOT NULL
AND t_stamp Between "{Root Container.Brew Harvest Table.brewTable.StartTime}" 
AND "{Root Container.Brew Harvest Table.brewTable.EndTime}"

好的，把所有的AND都改为OR，所以我得到了一行数据，根据输入的数据应该有7行返回。如果我运行相同的查询减去sum case部分，我会返回所有7行。以下是当前查询。我缺少什么，为什么只有一行数据？

SELECT ndx, t_stamp, fv, name, lot, Pnl1, Pnl2, Pnl3, Pnl4, Pnl5, Pnl6, Pnl7, 
Pnl8, Pnl9, Pnl10, 
((sum((CASE WHEN (Pnl1 IS NOT NULL) THEN Pnl1 ELSE 0 END)
+(CASE WHEN (Pnl2 IS NOT NULL) THEN Pnl2 ELSE 0 END)
+(CASE WHEN (Pnl3 IS NOT NULL) THEN Pnl3 ELSE 0 END)
+(CASE WHEN (Pnl4 IS NOT NULL) THEN Pnl4 ELSE 0 END)
+(CASE WHEN (Pnl5 IS NOT NULL) THEN Pnl5 ELSE 0 END)
+(CASE WHEN (Pnl6 IS NOT NULL) THEN Pnl6 ELSE 0 END)
+(CASE WHEN (Pnl7 IS NOT NULL) THEN Pnl7 ELSE 0 END)
+(CASE WHEN (Pnl8 IS NOT NULL) THEN Pnl8 ELSE 0 END)
+(CASE WHEN (Pnl9 IS NOT NULL) THEN Pnl9 ELSE 0 END)
+(CASE WHEN (Pnl10 IS NOT NULL) THEN Pnl10 ELSE 0 END)))
/
(sum((CASE WHEN (Pnl1 IS NOT NULL) THEN 1 ELSE 0 END)
+(CASE WHEN (Pnl2 IS NOT NULL) THEN 1 ELSE 0 END)
+(CASE WHEN (Pnl3 IS NOT NULL) THEN 1 ELSE 0 END)
+(CASE WHEN (Pnl4 IS NOT NULL) THEN 1 ELSE 0 END)
+(CASE WHEN (Pnl5 IS NOT NULL) THEN 1 ELSE 0 END)
+(CASE WHEN (Pnl6 IS NOT NULL) THEN 1 ELSE 0 END)
+(CASE WHEN (Pnl7 IS NOT NULL) THEN 1 ELSE 0 END)
+(CASE WHEN (Pnl8 IS NOT NULL) THEN 1 ELSE 0 END)
+(CASE WHEN (Pnl9 IS NOT NULL) THEN 1 ELSE 0 END)
+(CASE WHEN (Pnl10 IS NOT NULL) THEN 1 ELSE 0 END)))) AS Result
FROM vdk
WHERE Pnl1 IS NOT NULL OR Pnl2 IS NOT NULL OR Pnl3 IS NOT NULL OR Pnl4 IS 
NOT NULL OR Pnl5 IS NOT NULL
OR Pnl6 IS NOT NULL OR Pnl7 IS NOT NULL OR Pnl8 IS NOT NULL OR Pnl9 IS 
NOT NULL OR Pnl10 IS NOT NULL
AND t_stamp Between "{Root Container.Brew Harvest Table.brewTable.StartTime}" 
AND "{Root Container.Brew Harvest Table.brewTable.EndTime}"

SQL中经常使用的一个简单方法是case语句。您可以使用case语句来评估col1、col2和col3是否为null，然后输出col1/2/3值(分子(或1或0作为分母。

MySQL可能有特定的函数来帮助实现这一点(例如，case语句可以用if函数重写(，但大多数RDBMS都理解case。

下面是一个如何做到这一点的例子——我扩展了分子和分母，使其更清楚地了解正在发生的事情。

select
(
-- Numerator (output col1/2/3 value when not null)
sum
(
-- col1
(case when (col1 is not null) then col1 else 0 end)
-- col2
+ (case when (col2 is not null) then col2 else 0 end)
-- col3
+ (case when (col3 is not null) then col3 else 0 end)
)
/
-- Denominator
sum
(
-- col1
(case when (col1 is not null) then 1 else 0 end)
-- col2
+ (case when (col2 is not null) then 1 else 0 end)
-- col3
+ (case when (col3 is not null) then 1 else 0 end)
)
) as Result
from
table1
where
col1 is not null
or col2 is not null
or col3 is not null

需要注意的几点：

我包含了一个where子句，用于排除col1、col2和col3为null的任何行。这是为了防止分母为0，这将导致除以0的错误。如果仅返回sum列(除非表中的所有行都只有空值(，则不需要执行此操作，但一旦将另一列添加到选择列列表中，它就会为每一行计算此Result列，因此您需要确保不会提取任何可能导致除以零错误的列。考虑"我的查询可以被零除吗"问题，并将其与查询的执行成本进行平衡(如果col1/col2/col3上没有索引，那么您可能会看到选择性能下降(
如果您发现Result返回的是整数而不是浮点值(例如5/3显示为2，而不是1.6667(，则需要convert作为分子或分母进行浮点运算。其基本思想是，如果sum的值被确定为int的值，那么MySQL将"用int除以int"，结果的数据类型也将是int。但如果用float(或float除以int(除以int，则结果将是float数据类型。

下面的查询给了我想要的结果。谢谢RToyo和Uueerdo，如果没有你的帮助，我不会让这件事发生。

SELECT ndx, t_stamp,fv, name, lot, TankTmp, gcBut, gcPent, (gcBut+gcPent) AS gcTot,
gcIntStHx, gcExtbut, gcExtpent, gcExtrsp, (gcBut*2) AS histBut, (gcPent*2) AS 
histPent, ((gcBut*2)+(gcPent*2)) AS histTot,
Initial, Pnl1, Pnl2, Pnl3, Pnl4, Pnl5, Pnl6, Pnl7, Pnl8, Pnl9, Pnl10,
(((CASE WHEN (Pnl1 IS NOT NULL) THEN Pnl1 ELSE 0 END)
+(CASE WHEN (Pnl2 IS NOT NULL) THEN Pnl2 ELSE 0 END)
+(CASE WHEN (Pnl3 IS NOT NULL) THEN Pnl3 ELSE 0 END)
+(CASE WHEN (Pnl4 IS NOT NULL) THEN Pnl4 ELSE 0 END)
+(CASE WHEN (Pnl5 IS NOT NULL) THEN Pnl5 ELSE 0 END)
+(CASE WHEN (Pnl6 IS NOT NULL) THEN Pnl6 ELSE 0 END)
+(CASE WHEN (Pnl7 IS NOT NULL) THEN Pnl7 ELSE 0 END)
+(CASE WHEN (Pnl8 IS NOT NULL) THEN Pnl8 ELSE 0 END)
+(CASE WHEN (Pnl9 IS NOT NULL) THEN Pnl9 ELSE 0 END)
+(CASE WHEN (Pnl10 IS NOT NULL) THEN Pnl10 ELSE 0 END))
/
((CASE WHEN (Pnl1 IS NOT NULL) THEN 1 ELSE 0 END)
+(CASE WHEN (Pnl2 IS NOT NULL) THEN 1 ELSE 0 END)
+(CASE WHEN (Pnl3 IS NOT NULL) THEN 1 ELSE 0 END)
+(CASE WHEN (Pnl4 IS NOT NULL) THEN 1 ELSE 0 END)
+(CASE WHEN (Pnl5 IS NOT NULL) THEN 1 ELSE 0 END)
+(CASE WHEN (Pnl6 IS NOT NULL) THEN 1 ELSE 0 END)
+(CASE WHEN (Pnl7 IS NOT NULL) THEN 1 ELSE 0 END)
+(CASE WHEN (Pnl8 IS NOT NULL) THEN 1 ELSE 0 END)
+(CASE WHEN (Pnl9 IS NOT NULL) THEN 1 ELSE 0 END)
+(CASE WHEN (Pnl10 IS NOT NULL) THEN 1 ELSE 0 END))) AS Result,
note
FROM vdk
WHERE t_stamp Between "{Root Container.Brew Harvest Table.brewTable.StartTime}" 
AND "{Root Container.Brew Harvest Table.brewTable.EndTime}"
ORDER BY t_stamp ASC

相关内容

最新更新

热门标签：