MySQL:如何仅获取正值的平均值? [英] MySQL: how to get average of positive values only?

问题描述

假设我有INT列，并且我使用-1表示在插入时没有可用数据.我想获取此列的所有值为0或更大的AVG.

Suppose I have INT column, and I am using -1 to signify that no data was available at the time of the INSERT. I'd like to get an AVG of all values of this column that are 0 or larger.

这可能吗?

谢谢.

我忘了说我是和其他AVG一起做的，因此它是从选项卡中选择avg(a)，avg(b)，avg(d)； ...所以我不能使用b> 0 ...，因为其他任何人都需要使用行数据，而不管这一列的数据为-1.

I forgot to mention that I'm doing this alongside other AVG's, so it's select avg(a), avg(b), avg(d) from tab; ... so I can't use b>0... because the others need to use the row data regardless of this one column's data being -1.

尽管我可以提高AVG结果，例如，通常是(4 + 5 + -1 + -1 + 6)/5.但是，如果我知道有-1个，我可以修复"结果以排除它们.

It occurs to me though that I could augment the AVG result e.g. if it would normally be (4 + 5 + -1 + -1 + 6) / 5. But if I know how many -1's there are I could "fix" the result to exclude them.

推荐答案

这可能有帮助:

如果您要忽略平均值中的-1值:

If you want to ignore the -1 values from the average:

SELECT AVG(`a`), AVG(IF(`b` > -1, `b`, NULL)), AVG(`c`) FROM `t`;

如果要考虑平均值中的-1值:

If you want to consider the -1 values in the average:

SELECT AVG(`a`), AVG(IF(`b` > -1, `b`, 0)), AVG(`c`) FROM `t`;

我假设虚拟列名和表名以及列b作为您只想考虑值> = 0的列.请随时根据您的模式输入名称.

I've assumed dummy column- and table- names and assumed column b as the one for which you want to consider only values >= 0. Please feel free to put in names as per your schema.

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