如何比较PL/SQL中的整数值范围

Question

我正在尝试比较测试表和引用表之间的整数值范围。如果测试表中的任何值范围与参考表中的可用范围重叠，则应将其删除。

如果不清楚，很抱歉，但这里有一个示例数据：

测试表：

MIN          MAX
10           121
122          648
1200         1599

REFERENCE_TABLE:

MIN          MAX
50           106
200          1400
1450         1500

MODIFIED TEST_TABLE:(运行PL/SQL后的预期结果(

MIN          MAX
10           49
107          121
122          199
1401        1449
1501        1599

在上面例子的第一行中，10-121被分为两行：10-49和107-121，因为值50、51、。。。，106被包括在reference_ table的第一行中(50-106(；等等。

以下是我迄今为止使用嵌套循环所写的内容。我创建了两个额外的临时表，用于存储引用表中的所有值。然后，它将创建要插入test_table的新范围集。

但这似乎无法正常工作和可能会导致性能问题，尤其是当我们处理数百万及以上的价值时：

CREATE TABLE new_table (num_value NUMBER);
CREATE TABLE new_table_next (num_value NUMBER, next_value NUMBER);

--PL/SQL启动

DECLARE
l_count NUMBER;
l_now_min NUMBER;
l_now_max NUMBER;
l_final_min NUMBER;
l_final_max NUMBER;
BEGIN
FOR now IN (SELECT min_num, max_num FROM test_table) LOOP
l_now_min:=now.min_num;
l_now_max:=now.max_num;
WHILE (l_now_min < l_now_max) LOOP
SELECT COUNT(*) -- to check if number is found in reference table
INTO l_count
FROM reference_table refr
WHERE l_now_min  >= refr.min_num
AND l_now_min   <= refr.max_num;
IF l_count        > 0 THEN
INSERT INTO new_table (num_value) VALUES (l_now_min);
COMMIT;
END IF;
l_now_min:=l_now_min+1;
END LOOP;
INSERT INTO new_table_next (num_value, next_value)
VALUES (SELECT num_value, (SELECT MIN (num_value) FROM new_table t2 WHERE t2.num_value > t.num_value) AS next_value FROM new_table t);
DELETE FROM test_table t
WHERE now.min_num   = t.min_num
AND now.max_num   = t.max_num;
COMMIT;
SELECT (num_value + 1) INTO l_final_min FROM new_table_next;
SELECT (next_value - num_value - 2) INTO l_final_max FROM new_table_next;
INSERT INTO test_table (min_num, max_num)
VALUES (l_final_min, l_final_max);
COMMIT;
DELETE FROM new_table;
DELETE FROM new_table_next;
COMMIT;
END LOOP;
END;
/

请帮帮我，我被卡住了

Answer 1

这种方法背后的思想是展开两个表，跟踪数字是在引用表中还是在原始表中。这真的很麻烦，因为相邻的值可能会导致问题。

然后，我们的想法是沿着两个维度执行"间隙和岛屿"类型的解决方案，然后只保留原始表中的值，而不保留第二个表中的。也许这可以被称为"排他性的缺口和岛屿"。

这是一个工作版本：

with vals as (
select min as x, 1 as inc, 0 as is_ref
from test_table
union all
select max + 1, -1 as inc, 0 as is_ref
from test_table
union all
select min as x, 0, 1 as is_ref
from reference_table
union all
select max + 1 as x, 0, -1 as is_ref
from reference_table
)
select min, max
from (select refgrp, incgrp, ref, inc2, min(x) as min, (lead(min(x), 1, max(x) + 1) over (order by min(x))  - 1) as max
from (select v.*,
row_number() over (order by x) - row_number() over (partition by ref order by x) as refgrp,
row_number() over (order by x) - row_number() over (partition by inc2 order by x) as incgrp
from (select v.*, sum(is_ref) over (order by x, inc) as ref,
sum(inc) over (order by x, inc) as inc2
from vals v
) v
) v
group by refgrp, incgrp, ref, inc2
) v
where ref = 0 and inc2 = 1 and min < max
order by min;

这是一个db<>不停摆弄

获得重叠的反问题要容易得多。"反转"引用表来处理此问题可能是可行的。

select greatest(tt.min, rt.min), least(tt.max, rt.max)
from test_table tt join
reference_table rt
on tt.min < rt.max and tt.max > rt.min  -- is there an overlap?

Answer 2

这是我在Teradata上做的一个类似任务(使用日期而不是数字(的修改，它基于与Gordon相同的基本数据(所有开始/结束值组合在一个列表中(，但使用了一个更简单的逻辑：

WITH minmax AS 
( -- create a list of all existing start/end values (possible to simplify using Unpivot or Cross Apply)
SELECT Min AS val, -1 AS prio, 1 AS flag  -- main table, range start
FROM test_table
UNION ALL
SELECT Max+1,   -1, -1                      -- main table, range end
FROM test_table
UNION ALL 
SELECT Min, 1,  1                      -- reference table, adjusted range start
FROM reference_table
UNION ALL
SELECT Max+1, 1, -1                      -- reference table, adjusted range end
FROM reference_table
)
, all_ranges AS 
( -- create all ranges from current to next row
SELECT minmax.*,
Lead(val) Over (ORDER BY val, prio desc, flag) AS next_val,  -- next value = end of range
Sum(flag) Over (ORDER BY val, prio desc, flag ROWS Unbounded Preceding) AS Cnt -- how many overlapping periods exist
FROM minmax
)
SELECT val, next_val-1
FROM all_ranges
WHERE Cnt = 1           -- 1st level only
AND prio + flag =  0  -- either (prio -1 and flag  1) = range start in base table
--     or (prio  1 and flag -1) = range end in ref table
ORDER BY 1

参见db fiddle

Answer 3

这里有一种方法。我把测试数据放在WITH子句中，而不是创建表(我发现这对于测试来说更容易(。我用了你的列名(MIN和MAX(；但是，由于MIN和MAX是Oracle关键字，因此这些选择非常糟糕。它们肯定会产生混乱，并且可能导致查询出错。

策略很简单——首先取REFERENCE_TABLE中范围的COMPLEMENT，它也是区间的并集(使用NULL作为负无穷大和正无穷大的标记(；则取TEST_ TABLE中的每个区间与REFERENCE_。下面的解决方案中的最终(外部(查询显示了如何实现这一点。

with
test_table (min, max) as (
select   10,  121 from dual union all
select  122,  648 from dual union all
select 1200, 1599 from dual
)
, reference_table (min, max) as (
select   50,  106 from dual union all
select  200, 1400 from dual union all
select 1450, 1500 from dual
)
, 
prep (min, max) as (
select lag(max) over (order by max) + 1 as min
, min - 1                          as max
from ( select min, max   from reference_table
union  all
select null, null from dual
)
)
select   greatest(t.min, nvl(p.min, t.min)) as min
,   least   (t.max, nvl(p.max, t.max)) as max
from     test_table t inner join prep p
on  t.min <= nvl(p.max, t.max)
and t.max >= nvl(p.min, t.min)
order by min
;
MIN        MAX
---------- ----------
10         49
107        121
122        199
1401       1449
1501       1599

Answer 4

解决问题的示例：

CREATE TABLE xrange_reception
(
vdeb    NUMBER,
vfin    NUMBER
);
CREATE TABLE xrange_transfert
(
vdeb    NUMBER,
vfin    NUMBER
);
CREATE TABLE xrange_resultat
(
vdeb    NUMBER,
vfin    NUMBER
);
insert into xrange_reception values (10,50);
insert into  xrange_transfert values (15,25);
insert into  xrange_transfert values (30,33);
insert into  xrange_transfert values (40,45);
DECLARE
CURSOR cr_rec IS SELECT * FROM xrange_reception;
CURSOR cr_tra IS
SELECT *
FROM xrange_transfert
ORDER BY vdeb;
i              NUMBER;
vdebSui        NUMBER;
BEGIN
FOR rc IN cr_rec
LOOP
i := 1;
vdebSui := NULL;
FOR tr IN cr_tra
LOOP
IF tr.vdeb BETWEEN rc.vdeb AND rc.vfin
THEN
IF i = 1 AND tr.vdeb > rc.vdeb
THEN
INSERT INTO xrange_resultat (vdeb, vfin)
VALUES (rc.vdeb, tr.vdeb - 1);
ELSIF i = cr_rec%ROWCOUNT AND tr.vfin < rc.vfin
THEN
INSERT INTO xrange_resultat (vdeb, vfin)
VALUES (tr.vfin, rc.vfin);
ELSIF vdebSui < tr.vdeb
THEN
INSERT INTO xrange_resultat (vdeb, vfin)
VALUES (vdebSui + 1, tr.vdeb - 1);
END IF;
vdebSui := tr.vfin;
i := i + 1;
END IF;
END LOOP;
IF vdebSui IS NOT NULL THEN
IF vdebSui < rc.vfin 
THEN
INSERT INTO xrange_resultat (vdeb, vfin)
VALUES (vdebSui + 1, rc.vfin);
END IF;
ELSE
INSERT INTO xrange_resultat (vdeb, vfin)
VALUES (rc.vdeb, rc.vfin);
END IF;
END LOOP;
END;

因此：

表xrange_reception:

vdeb  vfin
10    50

表xrange_transfer:

vdeb  vfin
15    25
30    33
40    45

表xrange_resultat:

vdeb  vfin
10    14
26    29
34    39
46    50