PostgreSQL使用array_agg后数组求和报sum(numeric[]) does not exist错误如何解决？

报错原因 #

PostgreSQL内置的SUM()函数仅支持对行集合中的数值类型字段做聚合，不能直接接收数组类型参数，因此调用SUM(numeric[])时会触发function sum(numeric[]) does not exist报错。

解决方案 #

方案1：直接聚合（无需保留数组，性能最优） #

如果不需要输出原始的total_price数组，仅需要最终求和结果，直接将ARRAY_AGG替换为SUM即可：

SELECT SUM(cart_items.unit_price) FILTER (WHERE cart_items.type = 2) AS sum_price
FROM cart 
INNER JOIN cart_items ON cart_items.cart_id = cart.id 
WHERE cart.id = 40868884;

方案2：对已生成的数组求和（需保留数组场景） #

如果业务逻辑必须先生成total_price数组，再对数组内数值求和，可以用UNNEST()函数将数组展开为多行数值后再聚合：

SELECT array_to_json(array_agg(items)) 
FROM (
  SELECT 
    p.total_price,
    (SELECT SUM(price_item) FROM UNNEST(p.total_price) AS price_item) AS sum_total_price
  FROM (
    SELECT ARRAY_AGG(cart_items.unit_price) FILTER (WHERE cart_items.type = 2) AS total_price 
    FROM cart 
    INNER JOIN cart_items ON cart_items.cart_id = cart.id 
    WHERE cart.id = 40868884
  ) AS p
) AS items;

按照你提供的数组[1.867,1.867,1.867,1.867,1.867,1.21,1.867,1.867,1.56,1.867,1.867,1.867,44]计算，求和结果为64.232。

可选方案：自定义数组求和函数 #

如果业务中频繁需要对numeric类型数组求和，可以自定义专用聚合函数简化后续调用：

CREATE OR REPLACE FUNCTION array_sum(numeric[])
RETURNS numeric AS $$
  SELECT SUM(elem) FROM UNNEST($1) AS elem;
$$ LANGUAGE sql IMMUTABLE;

函数定义完成后，即可直接调用array_sum(total_price)完成数组求和：

SELECT array_to_json(array_agg(items)) 
FROM (
  SELECT array_sum(p.total_price) AS sum_total
  FROM (
    SELECT ARRAY_AGG(cart_items.unit_price) FILTER (WHERE cart_items.type = 2) AS total_price 
    FROM cart 
    INNER JOIN cart_items ON cart_items.cart_id = cart.id 
    WHERE cart.id = 40868884
  ) AS p
) AS items;

内容的提问来源于stack exchange，提问作者Developer