# 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?

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.

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