使用递归的 SQL-Oracle 更新表

Question

假设我们有一张这样的表格:

+--------+------------+---------------+----------------+
| Name   | Position   | Initial Date  | Final Date     |
+--------+------------+---------------+----------------+
| XXX    | 1          | 2016/06/07    | 2016/06/08     |
| XXX    | 2          | 2016/06/08    | 2016/06/09     |
| XXX    | 3          | 2016/06/09    | 2016/06/10     |
| XXX    | 4          | 2016/06/13    | 2016/06/14     |
| XXX    | 6          | 2016/06/14    | 2016/06/15     |
| YYY    | 1          | 2016/06/02    | 2016/06/03     |
+--------+------------+---------------+----------------+

我想更新它，添加一个指示组的第一个位置的新字段。成为团体的一部分意味着遵循以下规则:

1. 同名
2. 位置编号必须是相关的(例如:位置 4 和 6 需要编号 5 才能创建组)。
3. 第一行的最后日期必须与第二行的初始日期一致，以此类推。

考虑到所有这些，结果应该是这样的:

+--------+------------+---------------+----------------+------------+
| Name   | Position   | Initial Date  | Final Date     | New field  |
+--------+------------+---------------+----------------+------------+
| XXX    | 1          | 2016/06/07    | 2016/06/08     | 1          |
| XXX    | 2          | 2016/06/08    | 2016/06/09     | 1          |
| XXX    | 3          | 2016/06/09    | 2016/06/10     | 1          |
| XXX    | 4          | 2016/06/13    | 2016/06/14     | 4          |
| XXX    | 6          | 2016/06/14    | 2016/06/15     | 6          |
| YYY    | 1          | 2016/06/02    | 2016/06/03     | 1          |
+--------+------------+---------------+----------------+------------+

我可以让它只在 2 人一组的情况下工作，但我不知道如何在超过 2 人的情况下处理它。

这是我使用的示例代码，显然不适用于大群体。

update table1 f1
set f1.new_field = NVL((select f2.position
                    from table1 f2
                    where f1.name = f2.name and
                    f2.position = f1.position+1 and
                    f1.final_date = f2.initial_date),f1.position);

我应该使用递归查询来解决这个问题吗？在这种情况下，我不知道如何在 SQL 中实现它。

非常感谢任何帮助!

Answer 1

您可以使用一系列分析函数来做到这一点，如下所示:

with sample_data as (select 'XXX' name, 1 position, to_date('07/06/2016', 'dd/mm/yyyy') initial_date, to_date('08/06/2016', 'dd/mm/yyyy') final_date from dual union all
                     select 'XXX' name, 2 position, to_date('08/06/2016', 'dd/mm/yyyy') initial_date, to_date('09/06/2016', 'dd/mm/yyyy') final_date from dual union all
                     select 'XXX' name, 3 position, to_date('09/06/2016', 'dd/mm/yyyy') initial_date, to_date('10/06/2016', 'dd/mm/yyyy') final_date from dual union all
                     select 'XXX' name, 4 position, to_date('13/06/2016', 'dd/mm/yyyy') initial_date, to_date('14/06/2016', 'dd/mm/yyyy') final_date from dual union all
                     select 'XXX' name, 6 position, to_date('14/06/2016', 'dd/mm/yyyy') initial_date, to_date('15/06/2016', 'dd/mm/yyyy') final_date from dual union all
                     select 'YYY' name, 1 position, to_date('02/06/2016', 'dd/mm/yyyy') initial_date, to_date('03/06/2016', 'dd/mm/yyyy') final_date from dual)
-- end of mimicking a table called "sample_data" containing your data
select name,
       position,
       initial_date,
       final_date,
       min(position) over (partition by name, grp_sum) new_field
from   (select name,
               position,
               initial_date,
               final_date,
               sum(change_grp_required) over (partition by name order by position) grp_sum
        from   (select name,
                       position,
                       initial_date,
                       final_date,
                       case when position - lag(position, 1, position) over (partition by name order by position) != 1
                                 or initial_date != lag(final_date, 1, initial_date - 1) over (partition by name order by position) then 1
                            else 0
                       end change_grp_required
                from   sample_data));

NAME   POSITION INITIAL_DATE FINAL_DATE  NEW_FIELD
---- ---------- ------------ ---------- ----------
XXX           1 2016/06/07   2016/06/08          1
XXX           2 2016/06/08   2016/06/09          1
XXX           3 2016/06/09   2016/06/10          1
XXX           4 2016/06/13   2016/06/14          4
XXX           6 2016/06/14   2016/06/15          6
YYY           1 2016/06/02   2016/06/03          1

最里面的子查询确定当前行和上一行的位置和日期是否相关。如果不是，则为 1，否则为 0。

下一个子查询然后计算这些数字的运行总和 - 这具有为相关行创建相同数字的效果(例如，位置 1 到 3 为 1，位置 4 为 2，位置 6 为 3)，我们可以然后用于分组反对。

然后，外部查询只需找到每个名称的最小位置数和新创建的分组列。

然后您可以在 update 语句中使用此查询来执行实际更新(显然，您不需要初始的 sample_data 子查询，因为您只是直接在查询的其余部分使用您的 table_name)。