PostgreSQL：使用聚合函数进行UPDATE

Question

我希望更新表格 paneldata，使用聚合函数设置列 ibase。

UPDATE paneldata p
SET ibase=SUM(1/i.dist)
FROM ibaselang i
WHERE p.gid=i.gid
AND i.instp<p.period

这导致 ERROR: 不能在UPDATE中使用聚合函数

表定义

CREATE TABLE public.ibaselang
(
  gid integer,
  dist double precision,
  buildid integer,
  instp smallint
)
WITH (
  OIDS=FALSE
);

解决方案

很遗憾，我不知道如何在子查询中实现我的WHERE函数。

- NewbieNeedsHelp

Answer 1

这是一个通用的示例，演示如何完成它。

UPDATE public.customer_value cv
SET total_value = sub_q.sum_val 
FROM 
    (
    SELECT SUM(order_amount) AS sum_val, o.customer_id 
    FROM public.orders AS o
    GROUP BY o.customer_id
    ) AS sub_q
WHERE sub_q.customer_id = cv.customer_id;

如果您想完整地尝试此示例，可以按如下方式创建虚拟数据:

CREATE TABLE public.customer_value
(
  customer_id int 
, total_value numeric (10,2)
);

CREATE TABLE public.orders 
(
  customer_id int
, order_amount numeric(10,2)
);

INSERT INTO public.customer_value
(customer_id)
VALUES 
  (1)
, (2);


INSERT INTO public.orders
(customer_id, order_amount)
VALUES 
 (1, 10)
,(1, 10)
,(2, 7.5)
,(2, 7.5);

- DatabaseShouter

Answer 2

我不知道这种方法的效率如何，但它应该能够起作用：

with updates as (
  select
    p.gid, sum (i.dist) as dist
  from
    paneldata p
    join ibaselang i on
      p.id = i.gid and
      i.instp < p.period
  where
    i.dist != 0
  group by
    p.gid
)
update paneldata p
set
  ibase = 1 / u.dist
from
  updates u
where
  p.gid = u.pid and
  u.dist != 0

关于添加的一些其他说明：

1. 如果您的.dist中有很多零值，在where子句中删除零值理论上可以加快查询速度。
2. 您可能存在除以零错误的潜在漏洞，这也在更新语句中处理。也许您的数据没有这种情况，但最好处理一下以确保安全。

- Hambone

Answer 3

尝试使用这样的相关查询：

UPDATE paneldata p
SET p.ibase=(select SUM(1/i.dist)
             FROM ibaselang i
             WHERE p.gid=i.gid
             AND i.instp<p.period)

- sagi

---

但那会更新所有的行！！必须遵守条件吗？ - Luciano Andress Martini