SQLite Forum

User Define Aggregate Functions with multiple columns and scaler value
Login

User Define Aggregate Functions with multiple columns and scaler value

(1) By Akhilesh Airen (airen977) on 2021-11-17 12:25:25 [link] [source]

Hi,

I have already implemented user defined aggregate functions based on one column input, however are there any examples where I could create aggrgeate function based on more than one columns and a scaler value. Say I need to calculate sumproduct of two columns based on scaler value.

Thanks

(2) By Keith Medcalf (kmedcalf) on 2021-11-17 18:44:48 in reply to 1 [link] [source]

Here is an example UDF that uses 2 arguments. It is a scalar function that takes two arguments -- the first being the number to operate on, and the second optional argument being the number of significant digits to maintain, with the default being 14. The provided number (arg 1) is then scaled, properly rounded, and scaled back so that it contains the number of significant digits specified by the second argument.

SQLITE_PRIVATE void _rounddigitsFunc(sqlite3_context *context, int argc, sqlite3_value **argv)
{
    double x, scale;
    int digits = 14;

    if ((argc==0) || (argc>2))
    {
        return;
    }
    if (sqlite3_value_type(argv[0]) == SQLITE_NULL)
    {
        return;
    }

    x = sqlite3_value_double(argv[0]);
    if (argc == 2)
    {
        digits = sqlite3_value_int(argv[1]);
    }

    digits = max(min(digits, 14), 1) - ceil(log10(fabs(x)));
    scale = pow(10.0, digits);
    x *= scale;
    x -= remainder(x, 1.0);
    x /= scale;

    sqlite3_result_double(context, x);
    return;
}

The following (windowed) aggregate calculates the weighted mean (and a bunch of other statistics) by successive approximation. Each call to the aggregate has two arguments, the value and the weight. No default is provided and two arguments are required to the window aggregate function.

typedef struct wmeanCtx wmeanCtx;
struct wmeanCtx
{
    sqlite_int64 n;
    LONGDOUBLE_TYPE m;
    LONGDOUBLE_TYPE w;
    LONGDOUBLE_TYPE s;
};

SQLITE_PRIVATE void wmeanStep(sqlite3_context *context, int argc, sqlite3_value **argv)
{
    wmeanCtx* p = sqlite3_aggregate_context(context, sizeof(wmeanCtx));
    if (p && sqlite3_value_numeric_type(argv[0]) != SQLITE_NULL && sqlite3_value_numeric_type(argv[1]) != SQLITE_NULL)
    {
        register LONGDOUBLE_TYPE x = sqlite3_value_double(argv[0]);
        register LONGDOUBLE_TYPE w = sqlite3_value_double(argv[1]);
        if (p->n)
        {
            register LONGDOUBLE_TYPE newW, delta, R;
            newW = w + p->w;
            delta = x - p->m;
            R = delta * w / newW;
            p->m += R;
            p->s += p->w * delta * R;
            p->w = newW;
        }
        else
        {
            p->m = x;
            p->w = w;
            p->s = 0.0L;
        }
        p->n++;
    }
}

SQLITE_PRIVATE void wmeanUnStep(sqlite3_context *context, int argc, sqlite3_value **argv)
{
    wmeanCtx* p = sqlite3_aggregate_context(context, sizeof(wmeanCtx));
    if (p && sqlite3_value_numeric_type(argv[0]) != SQLITE_NULL && sqlite3_value_numeric_type(argv[1]) != SQLITE_NULL)
    {
        register LONGDOUBLE_TYPE x = sqlite3_value_double(argv[0]);
        register LONGDOUBLE_TYPE w = sqlite3_value_double(argv[1]);
        register LONGDOUBLE_TYPE newW, delta, R;
        newW = p->w - w;
        delta = x - p->m;
        R = delta * w / newW;
        p->m -= R;
        p->s -= p->w * delta * R;
        p->w = newW;
        p->n--;
    }
}

SQLITE_PRIVATE void wmeanFinal(sqlite3_context *context)
{
    wmeanCtx* p = sqlite3_aggregate_context(context, 0);
    if (p && p->n > 0)
        sqlite3_result_double(context, p->m);
}

SQLITE_PRIVATE void wvarPFinal(sqlite3_context *context)
{
    wmeanCtx* p = sqlite3_aggregate_context(context, 0);
    if (p && p->n > 0)
        sqlite3_result_double(context, p->s/p->w);
}

SQLITE_PRIVATE void wvarFinal(sqlite3_context *context)
{
    wmeanCtx* p = sqlite3_aggregate_context(context, 0);
    if (p && p->n > 0)
        sqlite3_result_double(context, p->s/p->w*(LONGDOUBLE_TYPE)p->n/(LONGDOUBLE_TYPE)(p->n - 1));
}

SQLITE_PRIVATE void wstdevPFinal(sqlite3_context *context)
{
    wmeanCtx* p = sqlite3_aggregate_context(context, 0);
    if (p && p->n > 0)
        sqlite3_result_double(context, sqrtl(p->s/p->w));
}

SQLITE_PRIVATE void wstdevFinal(sqlite3_context *context)
{
    wmeanCtx* p = sqlite3_aggregate_context(context, 0);
    if (p && p->n > 0)
        sqlite3_result_double(context, sqrtl(p->s/p->w*(LONGDOUBLE_TYPE)p->n/(LONGDOUBLE_TYPE)(p->n - 1)));
}

SQLITE_PRIVATE void wsemFinal(sqlite3_context *context)
{
    wmeanCtx* p = sqlite3_aggregate_context(context, 0);
    if (p && p->n > 0)
        sqlite3_result_double(context, sqrtl(p->s/p->w*(LONGDOUBLE_TYPE)p->n/(LONGDOUBLE_TYPE)(p->n - 1))/sqrtl(p->n));
}

SQLITE_PRIVATE void wciFinal(sqlite3_context *context)
{
    wmeanCtx* p = sqlite3_aggregate_context(context, 0);
    if (p && p->n > 0)
        sqlite3_result_double(context, sqrtl(p->s/p->w*(LONGDOUBLE_TYPE)p->n/(LONGDOUBLE_TYPE)(p->n - 1))/sqrtl(p->n)*1.96L);
}

(3) By Akhilesh Airen (airen977) on 2021-11-20 09:14:13 in reply to 2 [source]

Thanks, this helped