在 PostgreSQL 中选择数组列的总和

Select sum of an array column in PostgreSQL

提问by Nicolas

If I have the following table:

如果我有下表：

Table "users"
Column          |       Type       | Modifiers 
---------------+------------------+-----------
  id            | integer          | not null default nextval('users_id_seq'::regclass)
  monthly_usage | real[]           | 

Where monthly_usageis an array of 12 numbers, i.e. {1.2, 1.3, 6.2, 0.9,...}

monthly_usage12个数字的数组在哪里，即{1.2, 1.3, 6.2, 0.9,...}

How can I select the sum of that column?

如何选择该列的总和？

Something along the lines of:

类似的东西：

SELECT id, sum(monthly_usage) as total_usage from users;

SELECT id, sum(monthly_usage) as total_usage from users;

Which obviously doesn't work.

这显然不起作用。

回答by Dmitry Seleznev

SELECT id, (SELECT SUM(s) FROM UNNEST(monthly_usage) s) as total_usage from users;

回答by jbryanscott

This generalization and reformatting Dmitry's answer helps me understand how it works:

这种概括和重新格式化Dmitry的答案有助于我理解它是如何工作的：

SELECT
  sum(a) AS total
FROM
  (
    SELECT
      unnest(array [1,2,3]) AS a
  ) AS b

Result:

结果：

total
 6