Hello experts,
DB Version : Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
I have a requirement to select a series of consecutive numbers of a specified length in a specified position. By position I mean the occurrences of specified length of such series of numbers. I have done something like below. Please let me know if it needs further improvements.
with
inputs(ids, nums) as
(
select 1, 1 from dual union all
select 2, 2 from dual union all
select 3, 3 from dual union all
select 4, 11 from dual union all
select 5, 4 from dual union all
select 6, 5 from dual union all
select 7, 6 from dual union all
select 8, 8 from dual union all
select 9, 9 from dual
)
select ids, nums from
(
select * from inputs
match_recognize
(
order by ids
measures match_number() as mn
all rows per match
after match skip to last s
pattern (a b{:length_of_series} ({-c-}|$)) -- 1 if length is 2
subset s = (c,b)
define b as nums = prev(nums) + 1,
c as nums <> b.nums + 1 or c.nums is null
)
where mn = :which_position
);
For eg;
In the above data, you will find that a series of numbers of length 3 is present twice. 1, 2, 3 and then 4,5,6. So, if I input the length of series as 3 and the position, I mean the occurrence, as 2 then I will get 4,5,6 as the result and if I input occurrence of such a length is 1 then I will get 1,2,3.
I am getting the result as expected. But my questions:
1. Am I doing it correctly ?
2. Are there any hidden scenarios that I am missing
3. Is there a better way of doing the same ?
Regards,
Ranagal