mysql - 2个字段的平均值，忽略零

Question

我正在寻找一个优雅的查询来获得忽略零值的平均值。

示例:我有 2 个值和一个预期结果:

SELECT X(10,20) AS result
-- expected result: 15

SELECT X(0,20) AS result
-- expected result: 20

SELECT X(10,0) AS result
-- expected result: 10

SELECT X(0,0) AS result
-- expected result: NULL

这是我的尝试:

(val1+val2)/(2-IF(val1,0,1)-IF(val2,0,1))

有什么更好的方法吗？

谢谢

Answer 1

我会这样写:

( val1 + val2 )/( ABS(SIGN(val1)) + ABS(SIGN(val2)) )

我相信这等同于 OP 表达式，它看起来已经很优雅了，无需创建用户定义的函数。

(避免从常量“2”中减去，以及额外的“1”和“0”常量，这真的只是我个人的偏好。)

如果我必须将这个表达式扩展为三个参数:

( val1 + val2 + val3)/( ABS(SIGN(val1)) + ABS(SIGN(val2)) + ABS(SIGN(val3)) )

注意事项:

MySQL SIGN 函数将返回一个表示数值“符号”的整数值:-1, 0, 1 或 NULL。

MySQL ABS 函数将返回数字的绝对值，因此将其包装在 SIGN 函数周围，即 ABS(SIGN(n)) 返回将是 0、1 或 NULL。 SIGN(ABS(n)) 会返回同样的东西。

如果其中一个参数为 NULL，则此表达式将返回 NULL。就像 OP 表达式一样。

我不确定这样是否更优雅。但这就是我要做的。

---

如果意图是使 SQL 更短，则只指定一次 val1 和 val2，就像在 OP 示例中那样，这需要一个函数。 MySQL 不为此提供 native 内置函数，因此我们必须定义(创建)用户定义的函数。

但如果这样做，我们将需要指定参数的数据类型和返回的数据类型(例如 BIGINT、DECIMAL(M,N)、 DOUBLE 等)，无论我们采用哪种方式，它都可能会发生隐式数据类型转换和舍入/不精确。

如果没有用户定义的函数，val1 和 val2 必须在表达式中包含两次。但我认为这比其他一些提议的表达方式中的四次或五次要好。