Recursive Subqueries for FizzBuzzMay 10th, 2012 — ddelmoli
So in yesterday’s post I mentioned that I wasn’t happy with my solution. Among several reasons were the limit on the first 100 numbers, and the lack of using what I think to be a more elegant solution with recursive sub-queries.
Also, I received a comment about problems with the MarkDown plugin — which was also affecting my code posting, so I disabled MarkDown and can now handle code posting much, much better.
I couldn’t let it rest, so I dug into the recursive sub-queries with a vengeance and wrote up something more elegant.
with fb0 (input) as ( select 'buzz,fizz,fizzbuzz,fizz,buzz,fizz,fizz' input from dual ), fb2 (n,pos,n_start,n_str) as ( select /* Anchor query of root nodes for recursion */ fb1.column_value n, 1 pos, fb1.column_value n_start, to_char(fb1.column_value) n_str from fb0, table(sys.odcinumberlist(3,5,6,9,10,12,15)) fb1 where /* Limit root nodes to ones that match the first word of input */ case regexp_substr(fb0.input,'\w+') when 'fizz' then mod(fb1.column_value,3) when 'buzz' then mod(fb1.column_value,5) when 'fizzbuzz' them mod(fb1.column_value,15) end = 0 union all select /* Build "child" nodes by walking fizzbuzz matching to input string */ case when mod(fb2.n,15) in (5,9) then n+1 when mod(fb2.n,15) in (3,10) then n+2 when mod(fb2.n,15) in (0,6,12) then n+3 end, fb2.pos + 1, fb2.n_start, fb2.n_str || ',' || case when mod(fb2.n,15) in (5,9) then n+1 when mod(fb2.n,15) in (3,10) then n+2 when mod(fb2.n,15) in (0,6,12) then n+3 end from fb2, fb0 where case when mod(fb2.n,15) in (0,5,6,10) then 'fizz' when mod(fb2.n,15) in (3,9) then 'buzz' when mod(fb2.n,15) in (12) then 'fizzbuzz' end = regexp_substr(fb0.input,'\w+',1,fb2.pos + 1) ), fb3 (input, n_str, n_length, n_start, min_length, min_start) as ( select /* Measure lengths of resulting matches, discard ones that are not long enough */ fb0.input, fb2.n_str, fb2.n - fb2.n_start n_length, fb2.n_start, min(fb2.n - fb2.n_start) over () min_length, min(fb2.n_start) over (partition by fb2.n - fb2.n_start) min_start from fb2, fb0 where fb2.pos = nvl(length(regexp_replace(fb0.input,'\w')),0)+1 ), fb4 (input, n_str) as ( select /* Retain the matches which are shortest and start at the smallest value */ fb3.input, fb3.n_str from fb3 where fb3.n_length = fb3.min_length and fb3.n_start = fb3.min_start ) select /* Display output */ input, n_str from fb4;