时间戳

首页 > 解决方案 > 为一系列重叠事件对创建 episodeID

sql - 为一系列重叠事件对创建 episodeID

问题描述

我有两个可能的事件，每个事件都可能发生多次，分别称为 eventA 和 eventB。eventA 和 eventB 以及有关它们的详细信息存在于 EventA 和 EventB 表中，它们有一个主键，即分别称为 eventAID 和 eventBID 的自动增量。

我想确定剧集，每个剧集至少包含一个 eventA 和一个 eventB，但每个剧集可以有多个。我想给每个剧集一个唯一的 episodeID，episodeID 是一个任意的标识号，并且每个剧集必须是唯一的。

我有一些标准（和 SQL 语句）可以告诉我给定的 eventA 和给定的 eventB 是否存在于同一集中。它连续输出一个带有 eventAID 和 eventBID 的表。我将此表称为情节表。

该查询的格式为：

SELECT EventA.eventAID, EventB.eventBID 
INTO Episode
FROM EventA 
INNER JOIN EventB ON *some criteria* 
WHERE *some criteria*;

我有权访问并可以修改此查询。此配对中涉及许多不相关的标准。

剧集表数据示例：

eventAID  eventBID
123       456
124       789
125       457
125       458
126       459
127       459
128       790
128       791
129       791

一些 eventA 和 eventB 没有合作伙伴，我想忽略它们。（未显示，它们不会出现在情节表中）

大多数 eventA 将与一个 eventB 配对，反之亦然，每个 eventA 都应该有一个唯一的 episodeID。 

episodeID  eventAID  eventBID
1          123       456
2          124       789

一些 eventA 将与多个 eventB 配对，反之亦然，每个 eventA 也应该获得相同的 episodeID。 

episodeID  eventAID  eventBID
3          125       457
3          125       458
4          126       459
4          127       459

一些 eventA 将与多个 eventB 配对，但其中一个 eventB 将与多个不同的 eventA 配对。所有这些也收到相同的 episodeID。 

episodeID  eventAID  eventBID
5          128       790
5          128       791
5          129       791

请注意，上面提供的表格是基于我的 SQL 语句为条件生成的所有合格 eventA-eventB 对的表格的示例。它们是为了说明我所看到的数据中的某些情况。

对此的最终演示将在原始 EventA 和 EventB 表中，其中未配对的事件已被删除。如果您的解决方案不需要该 Episode 表，请随意取消它。 

episodeID  eventAID  eventAdata
1          123       *
2          124       *
3          125       *
4          126       *
4          127       *
5          128       *
5          129       *

episodeID  eventBID  eventBdata
1          456       *
2          789       *
3          457       *
3          458       *
4          459       *
5          790       *
5          791       *

如果给定带有标签的 episodeID 表（如上例），我可以轻松地做到这一点。

单对不是问题。

我可以检测到与多个 eventB 配对的那些 eventA（通过 HAVING COUNT(*) > 1）并将它们过滤掉。对于具有多个 eventA 的 eventB，反之亦然。

从所有对的列表中删除它们（通过 EXCEPT）将只留下一对 eventA 只有一个 eventB 和 eventB 只有一个 eventA。（示例中的第 1 集和第 2 集）

使用 ROW_NUMBER 将生成一系列递增的 episodeID。

但是，问题在于第 3、4 和 5 集的情况。

图 3 和图 4 是对称的，如果可以识别其对没有更多对的 eventAID 或 eventBID（第 5 集的情况），则可以给它们提供 episodeID。

第五集就更难了。eventAID 128 链接到 eventBID 790 和 791，因此附加到它们的 episodeID 会传播到 eventAID 129。如果 eventAID 129 链接到不同的 eventBID，那么它们也会获得相同的 episodeID，等等。

这可能会无限期地持续下去，但较长的链比较短的链更罕见。

过去的解决方案是使用一个过程，将Episode表中的一行移到临时表中（从Episode中删除它们），然后将临时表中出现的具有事件ID的行移到临时表中，并重复直到临时表表停止增长。

然后所有这些行都获得相同的 EpisodeID 并写入输出表。清空临时表并重复，直到情节表为空。

这是非常不理想的，并且需要很长时间才能运行（在 15k 行的 Episode 表上需要 1 小时以上）。当必须定期重新生成 episodeID 时，这非常糟糕。

更不用说极其复杂的错误了。

有一个更好的方法吗？我正在寻找一般算法描述而不是代码，但代码也很好。

编辑：一个简单的例子。

事件表

eventAID  pid  eventAdata  eventAdata2
123       1    1           
124       1    2           
125       2    3           
126       3    4           
127       3    4           
128       3    5           1
129       3    6           
130       4    1

事件B表

eventBID  pid  eventBdata  eventBdata2
456       1    1           
789       1    2           
457       2    3           
458       2    3           
459       3    4           
790       3    5           
791       3    6           1

当前查询

SELECT EventA.eventAID, EventB.eventBID 
INTO Episode
FROM EventA 
INNER JOIN EventB ON EventA.pid = EventB.pid
WHERE EventA.eventAdata = EventB.eventBdata
OR EventA.eventAdata2 = EventB.eventBdata2;

这将创建一个Episode 表，其中填充了作为episode 一部分的eventA 和eventB 对。 

eventAID  eventBID
123       456
124       789
125       457
125       458
126       459
127       459
128       790
128       791
129       791

查询及其标准在功能上只是对“这些 EventA 和 EventB 是否属于同一集”的测试。

我需要标记 EventA 和 EventB 表，以便：

episodeID  eventAID  pid  eventAdata  eventAdata2
1          123       1    1           
2          124       1    2           
3          125       2    3           
4          126       3    4           
4          127       3    4           
5          128       3    5           1
5          129       3    6           

episodeID  eventBID  pid  eventBdata  eventBdata2
1          456       1    1           
2          789       1    2           
3          457       2    3           
3          458       2    3           
4          459       3    4           
5          790       3    5           
5          791       3    6           1

在这个简单的玩具示例中，事件数据和事件数据 2 是从 1 到 6 的数字或 null。pid 是来自另一个表的外键，用于标识属于一个人的记录组。

进一步要求：

一个情节中的所有 eventA 和 eventB 必须属于同一个人。

一集必须至少有一个 eventA 和一个 eventB。

奖励积分：

扩展解决方案以包含具有类似数据样式的 EventC 表...

标签： sqlsql-server

解决方案

检查下一个查询。

with
  ee as (
    select *
    from(values
      (123, 456), (124, 789), (125, 457),
      (125, 458), (126, 459), (127, 459),
      (128, 790), (128, 791), (129, 791)
    ) t(eva, evb)
  ),
  a as (
    select *,
      row_number() over(order by eva, evb) rn,
      row_number() over(partition by eva order by evb) rna,
      row_number() over(partition by evb order by eva) rnb
    from ee
  )
select
  sum(iif(rna = 1 and rnb = 1, 1, 0)) over(order by rn) evid,
  *
from a;


evid    eva     evb
1       123     456
2       124     789
3       125     457
3       125     458
4       126     459
4       127     459
5       128     790
5       128     791
5       129     791

在以下查询中C添加的事件的数据..

with
  eee as (
    select *
    from(values
      (123, 456, 42), (124, 789, 42), (125, 457, 42), (125, 458, 170), (126, 459, 170),
      (127, 459, 170), (128, 790, 171), (128, 791, 171), (129, 791, 172), (130, 792, 173)
    ) t(eva, evb, evc)
  ),
  a as (
    select *,
      row_number() over(order by eva, evb, evc) rn,
      row_number() over(partition by eva order by evb, evc) rna,
      row_number() over(partition by evb order by eva, evc) rnb,
      row_number() over(partition by evc order by eva, evb) rnc
    from eee
  )
select
  sum(iif(rna = 1 and rnb = 1 and rnc = 1, 1, 0)) over(order by rn) evid,
  *
from a;


evid    eva     evb     evc
1       123     456     42
1       124     789     42
1       125     457     42
1       125     458     170
1       126     459     170
1       127     459     170
2       128     790     171
2       128     791     171
2       129     791     172
3       130     792     173

可以在此处检查这两个查询。

推荐阅读