想象一個示例表
CREATE TABLE mytable (myid integer, myval double precision);
如果 myval 等于 0，我想更新myval到之前的非零值，按 排序myid。
為了使其正常作業，查詢必須從最低的開始更新，myid并以最高的結束。
這次真不知道從何說起。下面說window functions are not allowed in UPDATE：
UPDATE mytable
SET myval = LAG(myval) OVER (ORDER BY myid)
WHERE myval = 0
RETURNING *;
更復雜的 FROM 子查詢替代方案以語法錯誤或愚蠢的輸出結束，因為子查詢被評估一次而不是每行一次。最后一句話讓我想到了SELECT ... LEFT JOIN LATERAL ...結構，但我一直無法使其與更新陳述句一起作業。
uj5u.com熱心網友回復：
解決方案的第一部分：由于您不能在SETan的子句中呼叫視窗函式UPDATE，您可以使用 acte代替：
WITH list AS
(
SELECT myval, LAG(myval) OVER (ORDER BY myid) AS new_val
FROM mytable
WHERE myval = 0
)
UPDATE mytable AS t
SET myval = l.new_val
FROM list AS l
WHERE t.myval = l.myval ;
解決方案的第二部分：為了以指定的順序將零值替換為其非零先前值，我們不能將子句添加FILTER (WHERE myval <> 0)到lag()函式中，因為該子句僅特定于聚合函式（所有聚合函式都可以通過在OVER()后面添加子句用作視窗函式，但“純”視窗函式不是聚合函式）。所以在這里我們可以定義我們自己的聚合函式replaced_by_first_previous_non_zero_value()如下：
CREATE OR REPLACE FUNCTION replaced_by_first_previous_non_zero_value(x double precision, y double precision)
RETURNS double precision LANGUAGE sql AS
$$
SELECT CASE
WHEN y = 0
THEN COALESCE(x, y)
ELSE y
END ;
$$ ;
DROP AGGREGATE IF EXISTS replaced_by_first_previous_non_zero_value(double precision) ;
CREATE AGGREGATE replaced_by_first_previous_non_zero_value(double precision)
( sfunc = replaced_by_first_previous_non_zero_value
, stype = double precision
) ;
然后，對于以下查詢：
SELECT myval, replaced_by_first_previous_non_zero_value(myval) OVER (ORDER BY myid RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
FROM unnest(array[5, 0, 0 , 0 , 6] :: double precision[]) with ordinality as a(myval, myid)
我們得到以下結果：
myval | replaced_by_first_previous_non_zero_value
5 | 5
0 | 5
0 | 5
0 | 5
6 | 6
uj5u.com熱心網友回復：
update mytable t set myval=(select s.myval from mytable s where s.myid < t.myid and s.myval!=0 order by s.myid desc limit 1) where t.myid in (select myid from mytable where myval=0 order by myid for update) ;
結果
select * from mytable;
myid | myval
------ -------
1 | 1
2 | 0.123
3 | 0
4 | 5
7 | 0
update mytable t set myval=(select s.myval from mytable s where s.myid < t.myid and s.myval!=0 order by s.myid desc limit 1) where t.myid in (select myid from mytable where myval=0 order by myid for update) ;
select * from mytable order by myid;
myid | myval
------ -------
1 | 1
2 | 0.123
3 | 0.123
4 | 5
7 | 5
轉載請註明出處，本文鏈接：https://www.uj5u.com/gongcheng/346943.html
標籤：sql PostgreSQL postgresql-13
上一篇：在更新陳述句中排除空列-JOOQ