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

Canadian_Marine 发表于 Dev

加拿大海军

我想以说我对SQL还是很陌生的方式来开头，但是现在我的工作需要我参与其中。

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

作为概念验证，我试图简单地遍历表，并将每行中“ 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;

这段代码将执行并成功返回，但是当我检查表时，没有进行任何更改。我怎么了

a_horse_with_no_name

循环进行逐行更新几乎总是一个坏主意，并且将非常缓慢且无法扩展。您应该真正找到避免这种情况的方法。

说完之后：

您所做的所有功能就是更改内存中列值的值-您只是在修改变量的内容。如果要更新数据，则需要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;

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

甲稍微更有效的解决方案是使用一个光标，然后用做更新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;