mysql - 4 列的平均值，具有随机空值

Question

我有一张包含 4 种奖金的员工表。我想把每个员工的四笔奖金加起来，求平均值。有些员工没有资格获得奖金，因此这种类型的奖金不应计入他们的平均水平。除了取笑数据库设计(这不是我的错)之外，有没有人有创造性的方法在计算平均值时忽略 NULL 值？

下表中唯一具有正确平均值的员工是 Employee1。

SELECT title, (IFNULL(level1,0)+IFNULL(level2,0)+IFNULL(level3,0)+IFNULL(level4,0))/4 as avglevel, level1, level2, level3, level4
FROM test;
Title       avglevel    level1  level2  level3  level4
Employee1   2500.0000   1000    2000    3000    4000
Employee2   2250.0000   NULL    2000    3000    4000
Employee3   2000.0000   1000    NULL    3000    4000
Employee4   1750.0000   1000    2000    NULL    4000
Employee5   1500.0000   1000    2000    3000    NULL

我找到了这个项目，但它实际上只涵盖了 AVG 函数，而不是除以列数，除非其中一列为 NULL。 Strange MySQL AVG() anomaly NULL values

Answer 1

由于您当前的表设计，您需要对列而不是行执行聚合操作。 MySQL 通常对跨行/记录的聚合有很强的支持，但对列的支持较少。如果您打算更改表格设计，那么 AVG 函数已经可以解决这个问题，因为它开箱即用。考虑将您的表存储为:

Title     | level | amount
Employee2 | 1     | NULL
Employee2 | 2     | 2000
Employee2 | 3     | 3000
Employee2 | 4     | 4000

也就是说，将每个员工的每个级别值存储在单独的记录中。然后您可以使用以下方法轻松计算出正确的平均值:

SELECT
    title,
    AVG(amount) AS avglevel
FROM test
GROUP BY
    title;

Demo

默认情况下，AVG 函数已经忽略了 NULL 值，因此总和将通过不包括那些具有 NULL 的记录的记录计数进行归一化。

编辑:

设置演示后，您似乎确实想要使用所有级别对平均值进行归一化，即使金额为NULL。在这种情况下，我们可以将总和除以计数:

SELECT
    title,
    SUM(amount) / COUNT(*) AS avglevel
FROM test
GROUP BY
    title;

Demo