MySQL:平均空值 [英] MySQL: averaging with nulls

问题描述

有没有一种简单的方法可以排除空值影响平均值?他们似乎算作 0，这不是我想要的.我只是不想考虑它们的平均值，但这里有一个问题，我不能将它们从结果集中删除，因为该记录上有我确实需要的数据.

Is there a simple way I can exclude nulls from affecting the avg? They appear to count as 0, which is not what I want. I simply don't want to take their average into account, yet here is the catch, I can't drop them from the result set, as that record has data on it that I do need.

更新:

示例:

select avg(col1+col2), count(col3) from table1
where
group by SomeArbitraryCol
having avg(col1+col2) < 500 and count(col3) > 3
order by avgcol1+col2) asc;

这对我有用，但平均值并不准确，因为它们将空值计算为 0，这确实与整个平均值相去甚远.

This would be working for me, but the averages aren't accurate as they are counting null values as 0, which is really throwing off the whole average.

推荐答案

SQL 中的聚合函数(SUM、AVG、COUNT 等)总是自动排除 NULL.

Aggregate functions (SUM, AVG, COUNT, etc) in SQL always automatically exclude NULL.

所以 SUM(col)/COUNT(col) = AVG(col) - 这很棒且一致.

So SUM(col) / COUNT(col) = AVG(col) - this is great and consistent.

COUNT(*) 的特殊情况是对每一行进行计数.

The special case of COUNT(*) counts every row.

如果你用 NULL 组成一个表达式:A + B，其中 A 或 B 是 NULL，那么无论其他列是 NULL，A + B 都将为 NULL.

If you make up an expression with NULLs: A + B where either A or B is NULL, then A + B will be NULL regardless of the other column being NULL.

当有 NULL 时，一般情况下，AVG(A + B) <> AVG(A) + AVG(B)，它们也可能有不同的分母.您必须将列换行: AVG(COALESCE(A, 0) + COALESCE(B, 0)) 来解决该问题，但也可能排除 COALESCE(A, 0) + COALESCE(B, 0) 的情况.

When there are NULLs, in general, AVG(A + B) <> AVG(A) + AVG(B), and they will likely have different denominators, too. You would have to wrap the columns: AVG(COALESCE(A, 0) + COALESCE(B, 0)) to solve that, but perhaps also exclude the case where COALESCE(A, 0) + COALESCE(B, 0).

根据您的代码，我建议:

Based on your code, I would suggest:

select avg(coalesce(col1, 0) + coalesce(col2, 0)), count(col3) from table1
where coalesce(col1, col2) is not null -- double nulls are eliminated
group by SomeArbitraryCol
having avg(coalesce(col1, 0) + coalesce(col2, 0)) < 500 and count(col3) > 3
order by avg(coalesce(col1, 0) + coalesce(col2, 0)) asc;

这篇关于MySQL:平均空值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！