mysql - 如何: MYSQL average from multiple columns excluding Null Values and Changing Number Divided By

Question

使用以下 MySQL 5.6 查询SELECT sum(col1 + col2 + col3)/3 AS 表 1 的结果

我怎样才能做同样的事情只忽略 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}"

Answer 1

SQL 中经常使用的一种简单方法是 case声明。您可以使用 case 语句来计算 col1、col2 和 col3 以查看它们是否为 null，然后输出 col1/2/3 值(对于分子)或对于分母而言 1 或 0。

MySQL 可能有特定的函数来帮助解决此问题(例如，可以使用 case 函数重写 if 语句)，但是 case大多数 RDBMS 都能理解。

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

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 为空的任何行。这是为了防止分母为 0，从而导致除以 0 错误。如果您仅返回sum，则不需要这样做列(除非表中的所有行都只有空值)，但是一旦您将另一列添加到选择列的列表中，它将评估此 Result每行都有一个列，因此您需要确保不会拉出任何可能导致除以零错误的内容。考虑“我的查询能否除以零”问题，并将其与查询的执行成本进行平衡(如果 col1/col2/col3 上没有索引，那么您可能会看到选择性能下降)

• 如果您发现您的Result返回一个整数而不是浮点值(例如 5/3 结果为 2，而不是 1.6667)，您将需要 convert分子或分母要 float 。基本思想是，如果sum值被确定为 int值，那么MySQL将“用int除以int”，结果的数据类型也将是int。但是，如果将 int 除以 float(或 float 除以 int)，则结果将是 float 数据类型。