sql - 在 pgSQL 中结合 UPDATE 和 WINDOW

Question

我正在努力实现以下目标：

从下表开始

id  town  x    y
1   A     1.0  1.0
2   A     2.0  3.5
3   A     4.0  2.0
4   B     6.0  8.0
5   B     7.5  8.5

我想用两个新列更新它，其中包含城镇 A 和 B 的 x 和 y 平均值，如下所示

id  town  x    y    avg_x  avg_y
1   A     1.0  1.0  2.3333 2.1666
2   A     2.0  3.5  2.3333 2.1666
3   A     4.0  2.0  2.3333 2.1666
4   B     6.0  8.0  6.75   8.25
5   B     7.5  8.5  6.75   8.25

我使用 ALTER 语句创建了两个新列，然后尝试了以下操作：

UPDATE table
SET (avg_x, avg_y) =
(SELECT AVG(x) OVER w, AVG(y) OVER w
FROM table
WINDOW w AS (PARTITION BY town));

返回我：错误：用作表达式的子查询返回的不止一行 SQL 状态：21000

我被困住了，非常感谢帮助。

标签： sqlpostgresql

Answer 1

只需使用聚合：

update t
    set avg_x = tt.avg_x,
        avg_y = tt.avg_y
    from (select town, avg(x) as avg_x, avg(y) as avg_y
          from t
          group by town
         ) tt
    where t.town = tt.town;

或者不要打扰，只需在需要时即时计算价值。