Last week I brought you a challenge on how to make derivatives in SQL. Evidently, this week we are looking into how to make integrals in SQL!
The Challenge
Let’s say that we have a table such as the following one
| x | y |
|---|---|
| 0 | 1 |
| 0.01672240803 | 1.001673601 |
| 0.03344481605 | 1.003349453 |
| 0.05016722408 | 1.005026144 |
| … | … |
| … | … |
| 4.949832776 | 1.323710024 |
| 4.966555184 | 1.467952331 |
| 4.983277592 | 1.57260469 |
| 5 | 1.634217159 |
The question is easy. Find the integral of y(x) from x=0 to x=5. You should get a simple scalar for each integral that you make. For a reminder on what integrals are and how to calculate them you can watch this amazing series by 3b1b.
You can get the data here to generate the table here.
In this table there are the following three functions.
y1 = cos(x * x) * exp(0.1 * x)
y2 = 3 * x - 7
y3 = exp(- x ** 2)
And the results of their integrations from 0 to 5 are
Solution
In my case, I will use the trapezoidal rule. Basically, the area of each of the trapezoids below the known points of the function can be calculated using the following equation.
After that, it is just a matter of creating the correct query using the lead functions in SQL.
with traps_res as (
select
0.5 * (
lead(x) OVER (ORDER BY x ASC) * y1
- x * y1
+ lead(x) OVER (ORDER BY x ASC) * lead(y1) OVER (ORDER BY x ASC)
- x * lead(y1) OVER (ORDER BY x ASC)
)
from funcs
) select sum(*) as integration_result from traps_res;
Note that we first have to create a CTE that stores the area of each of the trapezoids and then we have to sum all of them. I have also created a procedure (in Snowflake) that takes the column name as an argument and returns the result of the integral.
CREATE OR REPLACE procedure integrate(colname varchar)
returns table(integration_result float)
language sql
AS
DECLARE
sql_query VARCHAR;
res RESULTSET;
BEGIN
sql_query := 'with traps_res as (
select
0.5 * (
lead(x) OVER (ORDER BY x ASC) * ' || colname || '
- x * ' || colname || '
+ lead(x) OVER (ORDER BY x ASC) * lead(' || colname || ') OVER (ORDER BY x ASC)
- x * lead(' || colname || ') OVER (ORDER BY x ASC)
)
from funcs
) select sum(*) as integration_result from traps_res';
res := (EXECUTE IMMEDIATE :sql_query);
return TABLE(res);
END
;
Calling this for our three functions, we obtain the following results
call integrate('y1'); --> 0.6016285595
call integrate('y2'); --> 2.5
call integrate('y3'); --> 0.8862269255
Do you have another solution? Send it over to eloi.sanchez@nimbusintelligence.com!
Are you into Python as well? Make sure to check another interesting blog about Python’s tricky stuff. Do you tend to like more visual stuff? Well, maybe try out this one about data visualization in Streamlit!
Bonus question
Do you know why the integral of y1 produces the approximate result but the integral of y2 produces the exact one?
