sql - 更新滞后

Question

我想将表的 ACTIVE 值设置如下：

• 如果FLAG=E=>ACTIVE=1并且对于任何后续FLAG值，直到FLAG=H
• 如果FLAG=H=>ACTIVE=0并且对于任何后续FLAG值，直到FLAG=E

等等等等。

例子

ID | FLAG | ACTIVE
---+------+-------
1  | E    | 1
2  | V    | 1
3  | H    | 0
4  | V    | 0
5  | E    | 1
6  | S    | 1
7  | V    | 1
8  | D    | 1
9  | H    | 0

该值按日期排序。为简单起见，我添加了一个 ID 列来获取列顺序。

问题

什么可以是 SQL 更新语句？

笔记：

业务规则也可以表示为：

如果对于给定的行，前面E的计数 - 前面的计数H为 1，则该行的 ACTIVE 为 1，否则为 0。

标签： sqloraclesql-updateoracle10g

Answer 1

您可以使用分析函数active获取值：last_value()

select id, flag,
  last_value(case when flag = 'E' then 1 when flag = 'H' then 0 end) ignore nulls
    over (order by id) as active
from your_table;

作为演示：

create table your_table (id, flag) as
          select 1, 'E' from dual
union all select 2, 'V' from dual
union all select 3, 'H' from dual
union all select 4, 'V' from dual
union all select 5, 'E' from dual
union all select 6, 'S' from dual
union all select 7, 'V' from dual
union all select 8, 'D' from dual
union all select 9, 'H' from dual;

select id, flag,
  last_value(case when flag = 'E' then 1 when flag = 'H' then 0 end) ignore nulls
    over (order by id) as active
from your_table;

        ID F     ACTIVE
---------- - ----------
         1 E          1
         2 V          1
         3 H          0
         4 V          0
         5 E          1
         6 S          1
         7 V          1
         8 D          1
         9 H          0

您可以使用相同的东西进行更新，尽管合并可能会更简单：

alter table your_table add active number;

merge into your_table
using (
  select id,
    last_value(case when flag = 'E' then 1 when flag = 'H' then 0 end) ignore nulls
      over (order by id) as active
  from your_table
) tmp
on (your_table.id = tmp.id)
when matched then update set active = tmp.active;

9 rows merged.

select * from your_table;

        ID F     ACTIVE
---------- - ----------
         1 E          1
         2 V          1
         3 H          0
         4 V          0
         5 E          1
         6 S          1
         7 V          1
         8 D          1
         9 H          0

db<>小提琴演示。

---

你说你的真实数据实际上是按日期排序的，我猜每个 ID 都有多个标志，所以这样的事情可能更现实：

create table your_table (id, flag_time, flag) as
          select 1, timestamp '2018-07-04 00:00:00', 'E' from dual
union all select 1, timestamp '2018-07-04 00:00:01', 'V' from dual
union all select 1, timestamp '2018-07-04 00:00:02', 'H' from dual
union all select 1, timestamp '2018-07-04 00:00:03', 'V' from dual
union all select 1, timestamp '2018-07-04 00:00:04', 'E' from dual
union all select 1, timestamp '2018-07-04 00:00:05', 'S' from dual
union all select 1, timestamp '2018-07-04 00:00:06', 'V' from dual
union all select 1, timestamp '2018-07-04 00:00:07', 'D' from dual
union all select 1, timestamp '2018-07-04 00:00:08', 'H' from dual;

alter table your_table add active number;

merge into your_table
using (
  select id, flag_time,
    last_value(case when flag = 'E' then 1 when flag = 'H' then 0 end) ignore nulls
      over (partition by id order by flag_time) as active
  from your_table
) tmp
on (your_table.id = tmp.id and your_table.flag_time = tmp.flag_time)
when matched then update set active = tmp.active;

select * from your_table;

        ID FLAG_TIME               F     ACTIVE
---------- ----------------------- - ----------
         1 2018-07-04 00:00:00.000 E          1
         1 2018-07-04 00:00:01.000 V          1
         1 2018-07-04 00:00:02.000 H          0
         1 2018-07-04 00:00:03.000 V          0
         1 2018-07-04 00:00:04.000 E          1
         1 2018-07-04 00:00:05.000 S          1
         1 2018-07-04 00:00:06.000 V          1
         1 2018-07-04 00:00:07.000 D          1
         1 2018-07-04 00:00:08.000 H          0

主要区别在于partition by id并更改要使用的顺序flag_time- 或任何你真正的列被称为。

db<>小提琴演示。

---

如果两个标志可以共享时间，则可能存在问题；使用时间戳列，希望它非常不可能，但是对于日期，该列的精度可能允许它。不过，您对此无能为力，除了可能会通过假设标志应该以特定顺序到达来打破平局，并据此给它们一个权重。不过比较题外话了。