如何：mysql从多个列中求平均值，不包括空值和变化数除以

2ekbmq32 于 2021-06-20 发布在 Mysql

关注(0)|答案(2)|浏览(336)

使用以下mysql 5.6查询 SELECT sum(col1 + col2 + col3) / 3 AS Result FROM table1 我怎么能只忽略空值就做同样的事情呢？另外，调整除以数字减去空值？
我们的实验室使用感官面板来评估我们产品的某些质量点。每个小组成员对每个样本的得分为-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都改成ors，这样我就得到了一行数据，根据输入的数据应该有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}"

mysql Null average

来源：https://stackoverflow.com/questions/51639890/how-to-mysql-average-from-multiple-columns-excluding-null-values-and-changing-n

2条答案

按热度按时间

nwwlzxa71#

下面的查询给出了我想要的结果。谢谢你，没有你的帮助，我是不会工作的。

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

赞(0）回复(0）举报 2021-06-20

7xllpg7q2#

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 要浮动的分子或分母。基本思想是如果 sum 值被确定为 int 值，则mysql将“用int除int”，结果的数据类型也将是int。但是如果用float除int（或用int除float），则结果将是float数据类型。

赞(0）回复(0）举报 2021-06-20

我来回答

如何：mysql从多个列中求平均值，不包括空值和变化数除以

2条答案

相关问题

热门标签

最新问答