一行中平均数的平均数

Question

我不确定我想要实现的是否可能。我想获取平均列的平均值。

SELECT avg(col1), avg(col2), avg(col3) FROM tbl

我的结果应该是三个平均列的平均值，这可能吗？类似这样。

SELECT avg( col1, col2, col3) FROM tbl

在MySQL 5.1版本中无法正常工作。

- onigunn

---

请注意，平均列的平均值可能与所有列值的平均值完全不同。 - Dour High Arch

Answer 1

你试过了吗：

SELECT avg( col1 + col2 + col3)/3.0 FROM tbl

您必须检查该列中是否存在空值。

- zendar

---

有时候，与其强行解决问题，不如休息一下好好思考。感谢大家帮我理清了思路。 - onigunn

Answer 2

SELECT (AVG(col1) * COUNT(col1) +
        AVG(col2) * COUNT(col2) +
        AVG(col3) * COUNT(col3)) /
       (COUNT(col1) + COUNT(col2) + COUNT(col3))
FROM tbl

- Anthony Faull

---

+1，这也适用于空值，但仅在总行数相对较大的情况下才准确，与具有空值的行相比（否则 count(col1)+count(col2)+count(col3) 可能明显小于 3 * count(*)）； 这里是进一步的改进 https://dev59.com/803Sa4cB1Zd3GeqPrQCA#2586543 - Unreason

不理智，你说得很对。我已经更新了我的答案，给出了加权平均值。 - Anthony Faull

Answer 3

我试图在Anthony和zendar的基础上改进

SELECT (SUM(col1)+SUM(col2)+SUM(col3))/(COUNT(col1)+COUNT(col2)+COUNT(col3))     
FROM tbl

假设：

• 所有值都具有相同的重要性（权重）
• 存在空值
• 您始终希望得到正确的结果

可能存在的问题：

• 对于整数输入，AVG不会溢出，而SUM会溢出，因此可能需要显式转换

编辑（感谢redcayuga）：如果所有行的任何一列都为NULL，则上述查询返回NULL，因此应将COALESCE应用于SUM。

SELECT (COALESCE(SUM(col1),0)+
        COALESCE(SUM(col2),0)+
        COALESCE(SUM(col3),0))/(COUNT(col1)+COUNT(col2)+COUNT(col3))     
FROM tbl

- Unreason

---

我不确定所有的值都应该具有相同的权重。原始问题似乎暗示了另外一种情况。 - redcayuga

如果一列中每行都有空值，表达式将被评估为 null。如果所有行都是 null，则会尝试除以零。 - redcayuga

所有的列中都是 NULL 是个好主意，至于零除 - 在这种情况下 AVG 是未定义的，如果在这种情况下返回 NULL 非常重要，那么可以使用 IF 条件来帮助实现，但我将把它留给潜在用户作为练习。 :) - Unreason

Answer 4

基本数学：

SELECT AVG(col1 + col2 + col3) / 3 FROM tbl

- Matti Virkkunen

---

只有当所有值都不为空时，它才是“基本数学”。例如，如果一行是（null，3，5），那么“null + 3 + 5”将计算为null，并且该行实际上被忽略。 - redcayuga

Answer 5

仅仅是为了好玩，这里提供一个略有不同的解决方案，将 NULL 处理交给 avg() 函数处理：

SELECT avg(colValue) from 
  ( SELECT col1 as colValue from tbl
    UNION ALL
    SELECT col2 as colValue from tbl
    UNION ALL
    SELECT col3 as colValue from tbl
  )

将来自 co1l、col2 和 col3 的值放入一个虚拟列中，然后数据库计算平均值。

您无需担心 NULL 值 - 数据库和 avg() 函数会为您处理。

注意：这可能比其他解决方案慢，因为它可能会导致 3 次完整的表扫描以创建虚拟表。请检查执行计划。

- zendar

Answer 6

SELECT (ISNULL(AVG(col1),0) + ISNULL(AVG(col2),0) + .....)
       /
       (CASE WHEN AVG(col1) IS NULL THEN 0 ELSE 1 END + CASE WHEN AVG(col2) IS NULL THEN 0 ELSE 1 END +......)
  FROM tbl

这将丢弃空值

- Novartz