在 Oracle SQL 中，当查询未返回单行时进行更新

Question

初学者问题。

前提：我知道这里的关键问题是SQL表中没有固有的顺序，因此为了将列"添加"到表中，我必须指定一些键来匹配。但是，我正在寻找一个优雅的解决方案。

问题：我想用通过窗口函数获得的V1的转换替换列V1的值(我用它来用最接近的已知值填充NULL值V1，按data排序)。

示例数据集(请注意，我没有标识行的键)：

create table Tab1(data date, V1 number, val number);
insert into Tab1 values (date '2000-01-01', 1, 100);
insert into Tab1 values (date '2000-02-01', 1, 110);
insert into Tab1 values (date '2000-03-01', 1, 100);
insert into Tab1 values (date '2000-03-01', 1, 130);
insert into Tab1 values (date '2000-05-01', NULL, 100);
insert into Tab1 values (date '2000-06-01', NULL, 100);
insert into Tab1 values (date '2000-03-01', 2, 110);
insert into Tab1 values (date '2000-03-01', 2, 105);
insert into Tab1 values (date '2000-04-01', 2, 190);
insert into Tab1 values (date '2000-05-01', NULL, 200);
insert into Tab1 values (date '2000-06-01', NULL, 150);
select * from Tab1;
DATA       V1   val
2000-01-01  1   100
2000-02-01  1   110
2000-03-01  1   100
2000-03-01  1   130
2000-04-01  1   100
2000-05-01      100
2000-06-01      100
2000-03-01  2   110
2000-03-01  2   105
2000-04-01  2   190
2000-05-01      200
2000-06-01      150

我想避免创建第二个表，例如

create table Tab2 as
select A.*, 
(case when V1 is null 
then last_value(V1) ignore nulls 
over (partition by V1 order by data 
range between unbounded preceding and 1 preceding) 
else V1 
end) V2
from Tab1 A;

实际上，窗口功能仍然没有做我想要的，但这是一个单独的问题(如果你有一个解决方案，它非常受欢迎)。最后，我想要的是以下内容，其中 V1 被其版本替换，空值替换为最接近的非缺失值：

DATA       V1   val 
2000-01-01  1   100
2000-02-01  1   110
2000-03-01  1   100
2000-03-01  1   130
2000-04-01  1   100
2000-05-01  1   100
2000-06-01  1   100
2000-03-01  2   110
2000-03-01  2   105
2000-04-01  2   190
2000-05-01  2   200
2000-06-01  2   150

我不能使用update因为不允许 windows 函数，并且带有 windows 函数的子查询将检索多行。

同样，merge into语句也不起作用，因为我无法给出一个on条件来标识要匹配的单行(data和V1是不够的)。

有没有办法只将V2"添加"到Tab1，而无需经历创建新表的麻烦？

Answer 1

首先是好消息，这是您问题的答案。

同样，合并到语句也不起作用，因为我无法给出一个 on 条件来标识要匹配的单行(数据和 V1 是不够的)。

您可以使用rowid。 所以：

merge into tab1 t using
( select t1.rowid row_id, 
(case when t1.V1 is null 
then last_value(t1.V1) ignore nulls 
over (partition by null order by data 
range between unbounded preceding and 1 preceding) 
else t1.V1 
end) new_V1
from Tab1 t1
) u
on ( t.rowid = u.row_id )
when matched then update set t.v1 = u.new_v1;

现在坏消息。

没有办法编写一个窗口函数来做你想做的事情。

请考虑输入数据中的以下两行：

DATA       V1   val
2000-01-01  1   100
2000-02-01  1   110
2000-03-01  1   100
2000-03-01  1   130
2000-04-01  1   100
2000-05-01      100   <== this one
2000-06-01      100
2000-03-01  2   110
2000-03-01  2   105
2000-04-01  2   190
2000-05-01      200   <== and this one
2000-06-01      150

我们的逻辑无法使用这些行来知道第一行应该更新为V1 = 1行，第二行应该更新为V1 = 2. 我们唯一拥有的是你列出它们的顺序，但这些信息不会存储在你的数据模型中的任何地方。

Answer 2

你只需要用不同的方式表达它。

无需创建另一个表。 在这种情况下，WITH子句将起作用。

使用以下查询：

-- your update query
UPDATE TAB1
SET
V1 = (
WITH UPDATED_VAL AS (
SELECT /*+ materialize */
A.ROWID   RID,
( LAST_VALUE(V1) IGNORE NULLS OVER(
PARTITION BY DATE1
ORDER BY
DATE1
RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
) ) V2
FROM
TAB1 A
)
SELECT
V2
FROM
UPDATED_VAL
WHERE
TAB1.ROWID = UPDATED_VAL.RID
)
WHERE
V1 IS NULL

数据库<>小提琴演示