Range consolidation: Difference between revisions
Content added Content deleted
Line 2,244: | Line 2,244: | ||
test result: ok. 5 passed; 0 failed; 0 ignored; 0 measured; 0 filtered out |
test result: ok. 5 passed; 0 failed; 0 ignored; 0 measured; 0 filtered out |
||
</pre> |
|||
=={{header|SQL}}== |
|||
{{works with|ORACLE 19c}} |
|||
This is not a particularly efficient solution, but it gets the job done. |
|||
<lang SQL> |
|||
/* |
|||
This code is an implementation of "Range consolidation" in SQL ORACLE 19c |
|||
p_list_of_sets -- input string |
|||
delimeter by default "|" |
|||
*/ |
|||
with |
|||
function range_consolidation(p_list_of_sets in varchar2) |
|||
return varchar2 is |
|||
-- |
|||
v_list_of_sets varchar2(32767) := p_list_of_sets; |
|||
v_output varchar2(32767); |
|||
v_set_1 varchar2(2000); |
|||
v_set_2 varchar2(2000); |
|||
v_pos_set_1 pls_integer; |
|||
v_pos_set_2 pls_integer; |
|||
v_set_1_min number; |
|||
v_set_1_max number; |
|||
v_set_2_min number; |
|||
v_set_2_max number; |
|||
-- |
|||
function sort_set(p_in_str varchar2) |
|||
return varchar2 is |
|||
v_out varchar2(32767) := p_in_str; |
|||
begin |
|||
-- |
|||
with out_tab as |
|||
(select to_number(regexp_substr(str, '[^,]+', 1, rownum, 'c', 0)) elem |
|||
from |
|||
(select p_in_str as str |
|||
from dual |
|||
) |
|||
connect by level <= regexp_count(str, '[^,]+') |
|||
) |
|||
select min(elem)||','||max(elem) end |
|||
into v_out |
|||
from out_tab; |
|||
-- |
|||
return v_out; |
|||
end; |
|||
-- |
|||
function sort_output(p_in_str varchar2) |
|||
return varchar2 is |
|||
v_out varchar2(32767) := p_in_str; |
|||
begin |
|||
-- |
|||
with out_tab as |
|||
(select to_number(regexp_substr(regexp_substr(str, '[^|]+', 1, rownum, 'c', 0), '[^,]+', 1, 1)) low_range |
|||
, regexp_substr(str, '[^|]+', 1, rownum, 'c', 0) range_def |
|||
from |
|||
(select p_in_str as str |
|||
from dual |
|||
) |
|||
connect by level <= regexp_count(str, '[^|]+') |
|||
) |
|||
select listagg(range_def, '|') within group(order by low_range) |
|||
into v_out |
|||
from out_tab; |
|||
-- |
|||
return v_out; |
|||
end; |
|||
-- |
|||
begin |
|||
-- |
|||
execute immediate ('alter session set NLS_NUMERIC_CHARACTERS = ''.,'''); |
|||
-- |
|||
--cleaning |
|||
v_list_of_sets := ltrim(v_list_of_sets, '['); |
|||
v_list_of_sets := rtrim(v_list_of_sets, ']'); |
|||
v_list_of_sets := replace(v_list_of_sets, ' ', ''); |
|||
--set delimeter "|" |
|||
v_list_of_sets := regexp_replace(v_list_of_sets, '\]\,\[', '|', 1, 0); |
|||
-- |
|||
<<loop_through_sets>> |
|||
while regexp_count(v_list_of_sets, '[^|]+') > 0 |
|||
loop |
|||
v_set_1 := regexp_substr(v_list_of_sets, '[^|]+', 1, 1); |
|||
v_list_of_sets := regexp_replace(v_list_of_sets, v_set_1, sort_set(v_set_1), 1, 1); |
|||
v_set_1 := sort_set(v_set_1); |
|||
v_pos_set_1 := regexp_instr(v_list_of_sets, '[^|]+', 1, 1); |
|||
-- |
|||
v_set_1_min := least(to_number(regexp_substr(v_set_1, '[^,]+', 1, 1)),to_number(regexp_substr(v_set_1, '[^,]+', 1, 2))); |
|||
v_set_1_max := greatest(to_number(regexp_substr(v_set_1, '[^,]+', 1, 1)),to_number(regexp_substr(v_set_1, '[^,]+', 1, 2))); |
|||
-- |
|||
<<loop_for>> |
|||
for i in 1..regexp_count(v_list_of_sets, '[^|]+')-1 |
|||
loop |
|||
-- |
|||
v_set_2 := regexp_substr(v_list_of_sets, '[^|]+', 1, i+1); |
|||
v_list_of_sets := regexp_replace(v_list_of_sets, v_set_2, sort_set(v_set_2), 1, 1); |
|||
v_set_2 := sort_set(v_set_2); |
|||
v_pos_set_2 := regexp_instr(v_list_of_sets, '[^|]+', 1, i+1); |
|||
v_set_2_min := least(to_number(regexp_substr(v_set_2, '[^,]+', 1, 1)),to_number(regexp_substr(v_set_2, '[^,]+', 1, 2))); |
|||
v_set_2_max := greatest(to_number(regexp_substr(v_set_2, '[^,]+', 1, 1)),to_number(regexp_substr(v_set_2, '[^,]+', 1, 2))); |
|||
-- |
|||
if greatest(v_set_1_min,v_set_2_min)-least(v_set_1_max,v_set_2_max) <= 0 then --overlapping |
|||
v_list_of_sets := regexp_replace(v_list_of_sets, v_set_1, ''||least(v_set_1_min,v_set_2_min)||','||greatest(v_set_1_max,v_set_2_max),v_pos_set_1,1); |
|||
v_list_of_sets := regexp_replace(v_list_of_sets, v_set_2, '', v_pos_set_2, 1); |
|||
continue loop_through_sets; |
|||
end if; |
|||
-- |
|||
end loop loop_for; |
|||
-- |
|||
v_output := ltrim(v_output||'|'||least(v_set_1_min,v_set_1_max)||', '||greatest(v_set_1_min,v_set_1_max),'|'); |
|||
-- |
|||
v_output := sort_output(v_output); |
|||
v_list_of_sets := regexp_replace(v_list_of_sets,v_set_1,'',1,1); |
|||
-- |
|||
end loop loop_through_sets; |
|||
-- |
|||
return '['||replace(v_output,'|','], [')||']'; |
|||
end; |
|||
--Test |
|||
select lpad('[]',50) || ' ==> ' || range_consolidation('[]') as output from dual |
|||
union all |
|||
select lpad('[],[]',50) || ' ==> ' || range_consolidation('[],[]') as output from dual |
|||
union all |
|||
select lpad('[],[1,1]',50) || ' ==> ' || range_consolidation('[],[1,1]') as output from dual |
|||
union all |
|||
select lpad('[1.3]',50) || ' ==> ' || range_consolidation('[1.3]') as output from dual |
|||
union all |
|||
select lpad('[2,2],[1]',50) || ' ==> ' || range_consolidation('[2,2],[1]') as output from dual |
|||
union all |
|||
select lpad('[4,-1,0,1,5,7,7,7],[9,6,9,6,9]',50) || ' ==> ' || range_consolidation('[4,-1,0,1,5,7,7,7],[9,6,9,6,9]') as output from dual |
|||
union all |
|||
--Test RosettaCode |
|||
select '-- Test RosettaCode' as output from dual |
|||
union all |
|||
select lpad('[1.1, 2.2]',50) || ' ==> ' || range_consolidation('[1.1, 2.2]') as output from dual |
|||
union all |
|||
select lpad('[6.1, 7.2], [7.2, 8.3]',50) || ' ==> ' || range_consolidation('[6.1, 7.2], [7.2, 8.3]') as output from dual |
|||
union all |
|||
select lpad('[4, 3], [2, 1]',50) || ' ==> ' || range_consolidation('[4, 3], [2, 1]') as output from dual |
|||
union all |
|||
select lpad('[4, 3], [2, 1], [-1, -2], [3.9, 10]',50) || ' ==> ' || range_consolidation('[4, 3], [2, 1], [-1, -2], [3.9, 10]') as output from dual |
|||
union all |
|||
select lpad('[1, 3], [-6, -1], [-4, -5], [8, 2], [-6, -6]',50) || ' ==> ' || range_consolidation('[1, 3], [-6, -1], [-4, -5], [8, 2], [-6, -6]') as output from dual |
|||
union all |
|||
select lpad('1,3|-6,-1|-4,-5|8,2|-6,-6',50) || ' ==> ' || range_consolidation('1,3|-6,-1|-4,-5|8,2|-6,-6') as output from dual |
|||
/ |
|||
; |
|||
/ |
|||
</lang> |
|||
{{out}} |
|||
<pre> |
|||
[] ==> [] |
|||
[],[] ==> [] |
|||
[],[1,1] ==> [1, 1] |
|||
[1.3] ==> [1.3, 1.3] |
|||
[2,2],[1] ==> [1, 1], [2, 2] |
|||
[4,-1,0,1,5,7,7,7],[9,6,9,6,9] ==> [-1, 9] |
|||
-- Test RosettaCode |
|||
[1.1, 2.2] ==> [1.1, 2.2] |
|||
[6.1, 7.2], [7.2, 8.3] ==> [6.1, 8.3] |
|||
[4, 3], [2, 1] ==> [1, 2], [3, 4] |
|||
[4, 3], [2, 1], [-1, -2], [3.9, 10] ==> [-2, -1], [1, 2], [3, 10] |
|||
[1, 3], [-6, -1], [-4, -5], [8, 2], [-6, -6] ==> [-6, -1], [1, 8] |
|||
1,3|-6,-1|-4,-5|8,2|-6,-6 ==> [-6, -1], [1, 8] |
|||
</pre> |
</pre> |
||