Integration with SQL

Posted by

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 a 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 old 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!

2 comments

  1. you rock man 😊 SQL rocks man more of these please, i found interesting the idea of algorithmic/math quizzes vs implementation in SQL.

    Cheers (man) 🙂
    Emanuel O.

Got some thoughts? Leave a comment

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.