与不同运算符协调的 SQL 语句

Question

这与问题非常相关：SQL 协调语句，但有更多的变化。

给定以下架构：

create table TBL1 (ID varchar2(100) primary key not null, MATCH_CRITERIA timestamp);
create table TBL2 (ID varchar2(100) primary key not null, MATCH_CRITERIA timestamp);
create table TBL_RESULT (ID varchar2(100) primary key not null, TBL1_ID varchar2(100), TBL2_ID varchar2(100));

create unique index UK_TBL_RESULT_TBL1_ID on TBL_RESULT(TBL1_ID);
create unique index UK_TBL_RESULT_TBL2_ID on TBL_RESULT(TBL2_ID);

insert into TBL1 VALUES('1', to_date('01/26/2012 20:00:00', 'mm/dd/yyyy hh24:mi:ss'));
insert into TBL1 VALUES('2', to_date('01/26/2012 20:05:00', 'mm/dd/yyyy hh24:mi:ss'));

insert into TBL2 VALUES('3', to_date('01/26/2012 19:59:00', 'mm/dd/yyyy hh24:mi:ss'));
insert into TBL2 VALUES('4', to_date('01/26/2012 20:04:00', 'mm/dd/yyyy hh24:mi:ss'));

我们当前的查询：

INSERT INTO TBL_RESULT (ID, TBL1_ID, TBL2_ID) 
SELECT rawtohex(sys_guid()),t1.id,t2.id 
FROM
(SELECT t1.match_criteria,t1.id, row_number() OVER (PARTITION BY t1.match_criteria ORDER BY t1.id) rn 
FROM tbl1 t1) t1,  
(SELECT t2.match_criteria,t2.id, row_number() OVER (PARTITION BY t2.match_criteria ORDER BY t2.id) rn 
FROM tbl2 t2) t2
WHERE t1.match_criteria between t2.match_criteria - (10/1440) AND t2.match_criteria + (10/1440)
AND t1.rn=t2.rn

它的输出：

| ID  |  TBL1_ID | TBL2_ID |
| '1' |  '1'     |    '3'  |
| '2' |  '1'     |    '4'  |
| '3' |  '2'     |    '3'  |
| '4' |  '2'     |    '4'  |

如您所见，结果不符合唯一约束（重复的 TBL1_ID /重复的 TBL2_ID）。这是因为：

1. 每条记录的 RN 始终为 1（因此始终相等）
2. 两条记录之间的日期为 10 分钟。

我们期望输出如下表所示：

| ID  |  TBL1_ID | TBL2_ID |
| '1' |  '1'     |    '4'  |
| '2' |  '2'     |    '3'  |

注意 1：“1”是否与“3”匹配并不重要，但“2”应该与“4”匹配遵守约束条件，并且只要 T1.MATCH_CRITERIA 与 T2.MATCH_CRITERIA 的时间间隔在 10 分钟以内。

注 2：我们从 TBL1 插入一百万条记录，从 TBL2 插入另外一百万条记录。因此，使用 PL/SQL 的顺序插入是不可接受的，除非它运行得非常快（少于 15 分钟）。

注3：不匹配的数据应剔除。预计数据也会出现不平衡。

注 4：我们不限于只执行 1 个查询。一系列有限查询就可以了。

Answer 1

目前，您的查询会产生交叉联接，因为您的业务规则无法提供一种机制来将 T1 中的一条记录与 T2 中的一条记录链接起来。鉴于这显然是一个玩具示例，我们很难提出任何其他非常简单的建议：

(SELECT t1.match_criteria,t1.id, row_number() OVER (ORDER BY t1.match_criteria,t1.id) rn 
.... 
(SELECT t2.match_criteria,t2.id, row_number() OVER (ORDER BY t2.match_criteria,t2.id) rn 

这将简单地将 T1 结果集中的第一行与 T2 结果集中的第一行相匹配，将 T1 结果集中的第二行与T2 结果集中的第二行，依此类推。

SQL> INSERT INTO TBL_RESULT (ID, TBL1_ID, TBL2_ID) 
SELECT seq_tbl_result.nextval,t1.id,t2.id 
FROM
(SELECT t1.match_criteria,t1.id, row_number() OVER (ORDER BY t1.match_criteria, t1.id) rn 
FROM tbl1 t1) t1,  
(SELECT t2.match_criteria,t2.id, row_number() OVER (ORDER BY t2.match_criteria, t2.id) rn 
FROM tbl2 t2) t2
WHERE t1.match_criteria between t2.match_criteria - (10/1440) AND t2.match_criteria + (10/1440)
AND t1.rn=t2.rn
SQL> SQL> SQL>   2    3    4    5    6    7    8    9  
 10  /

2 rows created.


SQL> select * from tbl_result
  2  /

ID     TBL1_I TBL2_I
------ ------ ------
9      1      3
10     2      4

SQL> 

这可能不是你想要的。在这种情况下，您需要解释您的数据以及决定什么与什么链接的规则。例如，任一组时间是否存在某种模式可以让您导出锚点？

---

顺便说一句，当我统治世界时，使用 VARCHAR2(100) 列来保存数字 ID 的人将被枪杀。

Answer 2

我认为这可以工作：

INSERT INTO TBL_RESULT (ID, TBL1_ID, TBL2_ID)
select seq_tbl_result.nextval,
tt1.id, tt2.id
from (select id, v, row_number() over(partition by v order by id) rn
from (select distinct t1.id,
case
when (t1.match_criteria between
t2.match_criteria - (10 / 1440) and
t2.match_criteria + (10 / 1440)) then
1
else
2
end v
from tbl1 t1, tbl2 t2
where t1.match_criteria between
t2.match_criteria - (10 / 1440) and
t2.match_criteria + (10 / 1440))) tt1,
(select id, v, row_number() over(partition by v order by id) rn
from (select distinct t2.id,
case
when (t1.match_criteria between
t2.match_criteria - (10 / 1440) and
t2.match_criteria + (10 / 1440)) then
1
else
2
end v
from tbl1 t1, tbl2 t2
where t1.match_criteria between
t2.match_criteria - (10 / 1440) and
t2.match_criteria + (10 / 1440))) tt2
where tt1.v = tt2.v
and tt1.rn = tt2.rn