取多個可空列的平均值

我在 PostgreSQL 9.5 中有一個範例表（年份），如下所示：

Name | 2010 | 2011 | 2012
-------------------------
A    |   10 |      |   40
B    |   10 |   20 |   30 

現在，如果我編寫一個如下所示的簡單查詢來獲取列的平均值，(2010, 2011, 2012)我將得到 B 的正確結果，但由於2011列中的 NULL，A 的結果將為 NULL：

select (2010+2011+2012)/3 as avg from year

有什麼方法可以編寫查詢，以便我可以連續取非空值的平均值嗎？

該問題的唯一正確且可擴展的解決方案是規範化您的模型。

但是，您可以“即時”規範化數據，然後對結果使用標準 SQL 聚合。

select name, avg(t.val)
from the_table
 cross join unnest (array["2010","2011","2012"]) with ordinality as t(val)
group by name;

線上範例：https ://rextester.com/TGTC30399

但我強烈建議通過正確規範化來修復您的數據模型。

如前所述，在大多數情況下，規範化的關係設計將是正確的解決方案。

當您堅持自己的設計時——在某些情況下它是有意義的（比如最小化大表的儲存空間）——列出所有列名可能很乏味且容易出錯。這是一個替代方案：

SELECT name, avg(value::int)  -- cast to the type actually used
FROM   tbl t, jsonb_each_text(to_jsonb(t) - 'name')  -- exclude non-value columns
GROUP  BY 1;

不要列出要包含的所有列，而是刪除要**排除的一列（或幾列）。如果您稍後添加更多值列（"2013", "2014", …） ，也會繼續工作

缺點：這會將值轉換為 JSON 並返回，這增加了很小的成本。

有關的：

• 計算一行為 NULL 的屬性數
• 計算行中的非空列

另外，不要使用數字作為列名，這需要雙引號。使用類似c2010, c2011, …

引用自：https://dba.stackexchange.com/questions/245052