Challenge: Integrals in SQL?

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

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@ddbm.com!

Bonus question

Do you know why the integral of y1 produces the approximate result but the integral of y2 produces the exact one?