主页 / dba / 问题 / 199846
Accepted
Roberto Ribeiro
Asked: 2018-03-10 11:17:19 +0800 CST

按最小间隔值合并两个表

  • 772

我有两个表，其中两列保留分类值的选项卡，例如：

Table 1
+----+-------+-----+-----------+
| ID | Begin | End | Condition |
+----+-------+-----+-----------+
|  1 |     1 |   8 |    Normal |
|  2 |     8 |  23 |  Critical |
|  3 |    23 |  30 |    Normal |
+----+-------+-----+-----------+

Table 2
+----+-------+-----+------------+
| ID | Begin | End | Supervisor |
+----+-------+-----+------------+
|  1 |     1 |  14 |       John |
|  2 |    14 |  30 |     Janice |
+----+-------+-----+------------+

这些 Begin 和 End 列表示值有效的连续间隔。在上面的示例中，时间间隔是一个月中的几天，因此根据表 1，我从第 1 天到第 8 天处于正常状态，从第 8 天到第 23 天处于严重状态，从第 23 天到第 30 天再次正常. 我从表 2 中得知，John 从 1 号到 14 号负责监督，而 Janice 从 14 号到 30 号接任。

我想要的是合并这两个表，在同一个表中同时具有 Condition 和 Supervisor 值，每对值的间隔最小。所以这：

Merged Table
+----+-------+-----+-----------+------------+
| ID | Begin | End | Condition | Supervisor |
+----+-------+-----+-----------+------------+
|  1 |     1 |   8 |    Normal |       John |
|  2 |     8 |  14 |  Critical |       John |
|  3 |    14 |  23 |  Critical |     Janice |
|  4 |    23 |  30 |    Normal |     Janice |
+----+-------+-----+-----------+------------+

这些表可以保证的是：

  • 每个都有其各自的“开始”和“结束”字段。
  • 这些将具有相同的跨度（第一行的“开始”值和最后一行的“结束”值对于每个表都是相同的）。
  • 每个间隔都是连续的（第 n 行的“结束”值始终等于第 n+1 行的“开始”值）。
  • 对于给定行，“结束”的值将始终高于“开始”。

我在 python 中以编程方式解决了这个问题，但我试图从我的工作流程中删除该脚本并直接在数据库中执行所有操作。最终我可以在 plpgsql 中复制我的 python 函数，但我想知道是否有更多类似 SQL 的方法来实现这一点？

postgresql gaps-and-islands

1 个回答

  1. Best Answer

    我会分三个逻辑阶段来处理这个问题：

    1. 展开两个表中该月所有日期的行并将它们连接在一起。
    2. 为每个系列的行分配一个“组”，其中condition和supervisor不更改一段时间（“差距和岛屿”问题）。
    3. 对结果进行分组。

    所以解决方案看起来像这样：

    create table t1(
  id serial primary key
, begin_on integer
, end_on integer
, condition text
);

insert into t1(begin_on,end_on,condition)
values (1,8,'Normal')
     , (8,23,'Critical')
     , (23,30,'Normal');

create table t2(
  id serial primary key
, begin_on integer
, end_on integer
, supervisor text
);

insert into t2(begin_on,end_on,supervisor)
values (1,14,'John')
     , (14,30,'Janice');

    select min(g) begin_on, max(g)+1 end_on, condition, supervisor
from( select g
           , condition
           , supervisor
           , row_number() over (order by g) 
             - row_number() over (partition by t1.id, t2.id order by g) grp
      from generate_series(1,30) g
           join t1 on g>=t1.begin_on and g<t1.end_on
           join t2 on g>=t2.begin_on and g<t2.end_on ) z
group by condition, supervisor, grp
order by begin_on;

    开始| 结束| 条件 | 导师
------: | -----: | :-------- | :----------
       1 | 8 | 正常 | 约翰      
       8 | 14 | 关键 | 约翰      
      14 | 23 | 关键 | 珍妮丝    
      23 | 30 | 正常 | 珍妮丝

    dbfiddle在这里

    我假设这是您现实世界问题的缩减示例，或者我还建议首先更改您存储数据的方式，也许使用日期范围而不是整数。

    • 3

相关问题