Anonymous user
Range consolidation: Difference between revisions
→{{header|Rust}}
Line 2,244:
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>
| |||