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 3x^{2}+2x because that is something using my decades old high school math I can integrate from first principles to x^{3}+x^{2}, so I know the exact answer is 148, so we got reasonably close without too much effort.

Happy SQL-ing!

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.