A friend threw me a puzzle yesterday which I thought would be challenging, but turned out to be easy to solve with SQL. Such is the wonder of the SQL language!

The challenge is simple: Given a function, can we use SQL to integrate that function over a range?

image

Not every function has an integral, but for many functions, a reasonable approximation of the value can be performed with Riemann Sums or you can often get a more accurate approximation with the same amount computation using the Trapezoidal Rule.

image

With a little use of common table expressions (aka the WITH clause) and the fact that user defined functions can be embedded directly within them, here's an simple integration SQL

 SQL> with   2    function f(x number) return number is   3    begin   4      return 3*x*x + 2*x;   5    end;   6  boundaries as ( select 1 x_start, 5 x_end, 0.000001 delta from dual )   7  select   8    sum(   9     case when level in (1, ( x_end - x_start ) / delta) then 1 else 2 end *  10     f(x_start + (level-1)*delta)  11    )*delta/2 integ  12  from boundaries  13  connect by level <= ( x_end - x_start ) / delta;  14  /       INTEG ---------- 147.999915

or if you're an ANSI fan and prefer recursive WITH over CONNECT BY then you can do the same with this:

 SQL> with   2    function f(x number) return number is   3    begin   4      return 3*x*x + 2*x;   5    end;   6  boundaries as ( select 1 x_start, 5 x_end, 0.00001 delta from dual ),   7  integral(seq,fnc,inp) as   8  (   9    select 1 seq, f(x_start) fnc, x_start inp  from boundaries  10    union all  11    select seq+1, f(inp+delta), inp+delta  12    from integral, boundaries  13    where inp+delta <= x_end  14  )  15  select  16    sum(decode(seq,1,1,x_end,1,2)*fnc)*delta/2  17  from integral, boundaries  18  /  SUM(DECODE(SEQ,1,1,X_END,1,2)*FNC)*DELTA/2 ------------------------------------------                                   148.0004

I picked 3x2+2x because that is something using my decades past high school math Smile I can integrate from first principles to x3+x2, so I know the exact answer is 148, so we got reasonably close without too much effort.

Happy SQL-ing!