遍历表，对每一行进行计算

Question

我想先说我对 SQL 非常陌生，但我现在的工作需要我在其中工作。

我有一个包含地形点数据（x，y，z）的数据集。我正在尝试基于此数据构建 KNN 模型。对于每个点“P”，我在数据集中搜索最接近 P 的 100 个点（最近的意思是地理上最近的）。然后我平均这些点的值（这个平均值称为残差），并将这个值添加到“resid”列中的表中。

作为概念证明，我试图简单地遍历表，并将每行中“resid”列的值设置为 1.0。

我的查询是这样的：

 CREATE OR REPLACE FUNCTION LoopThroughTable() RETURNS VOID AS '
DECLARE row table%rowtype;
BEGIN
    FOR row in SELECT * FROM table LOOP
        SET row.resid = 1.0;
    END LOOP;
END

' LANGUAGE 'plpgsql';

SELECT LoopThroughTable() as output;

此代码成功执行并返回，但是当我检查表格时，没有进行任何更改。我的错误是什么？

原文由 Canadian_Marine 发布，翻译遵循 CC BY-SA 4.0 许可协议

Answer 1

在循环中逐行更新几乎总是一个坏主意，并且 会 非常慢并且不会扩展。你真的应该找到一种方法来避免这种情况。

说完之后：

您所做的所有功能就是更改内存中列值的值 - 您只是在修改变量的内容。如果要更新数据，则需要 update 语句：

您需要在循环内使用 UPDATE ：

 CREATE OR REPLACE FUNCTION LoopThroughTable()
  RETURNS VOID
AS
$$
DECLARE
   t_row the_table%rowtype;
BEGIN
    FOR t_row in SELECT * FROM the_table LOOP
        update the_table
            set resid = 1.0
        where pk_column = t_row.pk_column; --<<< !!! important !!!
    END LOOP;
END;
$$
LANGUAGE plpgsql;

请注意，您 必须 在 update 语句的主键上添加 where 条件，否则您将更新循环的 每次 迭代的 所有 行。

更 有效的解决方案是使用游标，然后使用 where current of

 CREATE OR REPLACE FUNCTION LoopThroughTable()
  RETURNS VOID
AS $$
DECLARE
   t_curs cursor for
      select * from the_table;
   t_row the_table%rowtype;
BEGIN
    FOR t_row in t_curs LOOP
        update the_table
            set resid = 1.0
        where current of t_curs;
    END LOOP;
END;
$$
LANGUAGE plpgsql;

---

因此，如果我在循环完成后执行 UPDATE 查询，是否会将更改提交到表中？

不，对函数的调用在调用事务的上下文中运行。因此，如果您在 SQL 客户端中禁用了自动提交，则在运行 SELECT LoopThroughTable() commit 。

---

请注意，语言名称是标识符，请勿在其周围使用单引号。您还应该避免使用像 row 这样的关键字作为变量名。

使用 美元引用（正如我所做的那样）也使编写函数体更容易

原文由 a_horse_with_no_name 发布，翻译遵循 CC BY-SA 3.0 许可协议

Answer 2

我不确定概念证明示例是否符合您的要求。通常，使用 SQL，您几乎 不需要 FOR 循环。虽然您可以使用函数，但如果您有 PostgreSQL 9.3 或更高版本，则可以使用 LATERAL 子 查询对每一行执行子查询。

例如，使用随机 value 列创建 10,000 个随机 3D 点：

 CREATE TABLE points(
  gid serial primary key,
  geom geometry(PointZ),
  value numeric
);
CREATE INDEX points_geom_gist ON points USING gist (geom);
INSERT INTO points(geom, value)
SELECT ST_SetSRID(ST_MakePoint(random()*1000, random()*1000, random()*100), 0), random()
FROM generate_series(1, 10000);

对于每个点，搜索最近的 100 个点（除了有问题的点），并找到点之间的残差 value 和最近的 100 个的平均值：

 SELECT p.gid, p.value - avg(l.value) residual
FROM points p,
  LATERAL (
    SELECT value
    FROM points j
    WHERE j.gid <> p.gid
    ORDER BY p.geom <-> j.geom
    LIMIT 100
) l
GROUP BY p.gid
ORDER BY p.gid;

原文由 Mike T 发布，翻译遵循 CC BY-SA 3.0 许可协议