SQLite Forum

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);
}
```