What’s all the buzz about?May 9th, 2012 — ddelmoli
During the course of recent twitter / RSS reading, I came across an interesting post related to the old FizzBuzz problem. The post intrigued me for 2 reasons: it talks about solving a basic problem in Scala, and it emphasizes the use of functional programming. And just for grins and chuckles it calls out OOP based on an old paper from 1984 by John Hughes on Why Functional Programming Matters.
I’m a big fan of functional programming, even if my language of choice, SQL, is a poor example of it. Although, I tend to think functional when I write SQL, I know that the language is limited in that respect.
However, when I think about problems that need to be solved in an Oracle database system, I always try to exhaust SQL-based solutions before looking to PL/SQL (I’ve always enjoyed Tom Kyte’s mantra on the subject). It’s one of the reasons that systems I have influence over have very little imperative PL/SQL constructs.
I also strive to write modular, relatively self-explanatory SQL to combat the reputation of SQL as a “difficult” language — all languages are difficult when you are confronted with them
Anyway, the post under discussion describes an inverse FizzBuzz problem with a description of working through various solutions using the function constructs of Scala, and many people have had a go at solving it.
One of my favorites is in F#, Microsoft’s functional language.
Anyway, here’s my attempt at it — I’m not entirely satisfied with it yet, but it was fun to write:
with fb0 as ( select 'fizz,buzz' input from dual ), fb1 as ( select level n, case when mod(level,3) = 0 and mod(level,5) <> 0 then 'fizz' when mod(level,5) = 0 and mod(level,3) <> 0 then 'buzz' when mod(level,3) = 0 and mod(level,5) = 0 then 'fizzbuzz' end fb, lead(level,1) over (order by level) next_n from dual where mod(level,3) = 0 or mod(level,5) = 0 connect by level <= 100 ), fb2 as ( select connect_by_root n n_root, level pos, fb1.n, fb1.fb from fb0, fb1 where level <= nvl(length(regexp_replace(fb0.input,'\w'))+1,1) start with fb1.fb = regexp_substr(fb0.input,'\w+') connect by prior fb1.next_n = fb1.n ), fb3 as ( select listagg(fb2.fb,',') within group (order by fb2.pos) fb_str, listagg(fb2.n,',') within group (order by fb2.pos) n_str, max(fb2.n) - min(fb2.n) n_length, min(fb2.n) n_min from fb0, fb2 group by fb2.n_root having listagg(fb2.fb,',') within group (order by fb2.pos) = fb0.input ), fb4 as ( select fb3.n_length, fb3.n_min, fb3.fb_str, fb3.n_str, min(fb3.n_length) over () min_length, min(fb3.n_min) over (partition by fb3.n_length) min_start from fb3 ), fb5 as ( select fb4.fb_str, fb4.n_str from fb4 where fb4.n_length = fb4.min_length and fb4.n_min = fb4.min_start ) select * from fb5;
I'm not entirely happy with it -- I'd like to combine fb2 with fb3, and fb4 with fb5. I'd also like to rewrite it with recursive subqueries instead of the connect by's and listagg's, but it's not too bad.
One of the things I'm starting to like with the WITH construct is how easy it is to debug the overall statement -- I can just replace the final SELECT with whichever subquery I want to test in order to see what results it's "passing" to the next subquery. Go ahead and try it to see!