在postgres中获取上一行的“列”值“在UPDATE中不能使用窗口函数”。 [英] Get Column value of previous row in postgres "cannot use window function in UPDATE"

问题描述

当i大于或等于3时，我试图获取该列的前一行值，并尝试在当前行计算中使用它，并且我尝试使用Lag函数这样做，但未成功，出现错误，因为无法在UPDATE中使用窗口功能。

When i is greater than or equal to 3, I am trying to fetch the previous row value of that column and trying to use it in the current row calculation and I have tried using Lag function to do so but was not successful,was getting the error as "cannot use window function in UPDATE". Could some one help me out.Thanks!

CREATE OR REPLACE FUNCTION vin_calc() RETURNS text AS
$BODY$
DECLARE
    r res%rowtype;
    i integer default 0;
    x  text;
    curs2 CURSOR FOR SELECT * FROM res;
BEGIN
open curs2;
   -- FOR r IN curs2 
  LOOP
  FETCH curs2 INTO r;
  exit when not found;

    if(x!=r.prod_grp_nm) then
    i:=0;
    end if;

     i:= i+1;

     if (i=1) then
      update res set duration =0 where 
      dur=r.dur and prod_grp_nm=r.prod_grp_nm and week_end=r.week_end;


     elsif(i=2) then
     update res set duration =1 where 
      dur=r.dur and prod_grp_nm=r.prod_grp_nm and week_end=r.week_end;


     elsif(i>=3) then
     update res set gwma_duration =0.875*lag(res.duration,1) over()+(0.125*r.dur) where 
      dur=r.dur and prod_grp_nm=r.prod_grp_nm and week_end=r.week_end;
     end if ;

    x:=r.prod_grp_nm;      

    END LOOP;
    RETURN 'yes';
END
$BODY$
LANGUAGE 'plpgsql' ;

推荐答案

假设...

• gwma_duration 和 duration

您想通过名为 order_column 。替换为您的实际列。

You want to order by a column named order_column. Replace with your actual column(s).

您的主键列为 res_id 。替换为您的实际列。

Your primary key columns is res_id. Replace with your actual column(s).

您的程序代码已得到修复和改进：

Your procedural code repared and improved:

CREATE OR REPLACE FUNCTION vin_calc()
  RETURNS void AS
$func$
DECLARE
   r res%rowtype;
   i integer := 0;
   last_grp text;
BEGIN

FOR r IN
   SELECT * FROM res
LOOP
   IF last_grp <> r.prod_grp_nm THEN
      i := 1;
   ELSE
      i := i + 1;
   END IF;

   IF i < 3 THEN
      UPDATE res
      SET    duration = i - 1
      WHERE  dur = r.dur
      AND    prod_grp_nm = r.prod_grp_nm
      AND    week_end = r.week_end;

   ELSE
      UPDATE res r1
      SET    duration = r.dur * 0.125 + 
            (SELECT 0.875 * gwma_duration FROM res
             WHERE order_column < r1.order_column
             ORDER BY order_column
             LIMIT 1
            )  -- could be replaced with last_duration, analog to last_grp
      WHERE  r1.dur = r.dur
      AND    r1.prod_grp_nm = r.prod_grp_nm
      AND    r1.week_end = r.week_end;
   END IF;

   last_grp := r.prod_grp_nm;

   END LOOP;
END
$func$
LANGUAGE plpgsql;

• 使用 FOR 循环。

  永远不要引用语言名称 plpgsql ，这是一个标识符，不是字符串。

  Never quote the language name plpgsql, which is an identifier, not a string.

  在多个地方简化了逻辑。

  Simplified your logic in several places.

  最重要的是，正如错误消息告诉您的那样，您不能在 UPDATE 的 SET 子句中使用窗口函数c $ c>。我将其替换为相关的子查询。但是可能用 last_duration 代替，类似于 last_grp ：只记得上次迭代的值。

  Most importantly, as the error message tells you, you can't use window functions in a SET clause of an UPDATE. I replaced it with a correlated subquery. But could probably be replaced with with last_duration, analog to last_grp: just remember the value from the last iteration.

  但是，以上所有内容都非常当您在单个 UPDATE 语句中执行此操作时效率低下：

  However, all of the above is very inefficient when you can do it in a single UPDATE statement:

  UPDATE res r
  SET    duration = CASE WHEN r0.rn < 3
                       THEN r0.rn - 1
                       ELSE r0.last_dur * 0.875 + r.dur * 0.125
                    END
  FROM  (
     SELECT res_id, duration
          , row_number()  OVER (PARTITION BY prod_grp_nm ORDER BY order_column) AS rn
          , lag(duration) OVER (PARTITION BY prod_grp_nm ORDER BY order_column) AS last_dur
     FROM res
     ) r0
  WHERE  r.res_id = r0.res_id
  

  
  
  

  
  

  • 明确：您可以可以在 FROM 子句中使用窗口函数-至少在现代版本的Postgres中是这样。

    • To be clear: you can use window functions in the FROM clause - at least in modern-day versions of Postgres.

      使用 row_number（） ，而不是 rank（） 等同于您的过程代码。

      Use row_number(), not rank() to be equivalent to your procedural code.

      这篇关于在postgres中获取上一行的“列”值“在UPDATE中不能使用窗口函数”。的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！