Patterns in static

Apophenia

Database moments (plus pow()!)

SQLite lets users define new functions for use in queries, and Apophenia uses this facility to define a few common functions.

select count(x), stddev(x), avg(x), var(x), variance(x), skew(x), kurt(x), kurtosis(x),
std(x), stddev_samp(x), stddev_pop(x), var_samp(x), var_pop(x)
from table
group by whatever

var and variance; kurt and kurtosis do the same thing. Choose the one that sounds better to you. var, var_samp, stddev and stddev_samp give sample variance/standard deviation; variance, var_pop std and stddev_pop give population standard deviation. The plethora of variants are for mySQL compatibility.

select sqrt(x), pow(x,0.5), exp(x), log(x),
sin(x), cos(x), tan(x), asin(x), acos(x), atan(x)
from table

Here is a test script using many of the above.

#include <apop.h>
#define Diff(L, R) assert(fabs((L)-(R)<1e-4));
#define getrow(rowname) apop_data_get(row, .colname=#rowname)
double test_all(apop_data *row){
Diff(gsl_pow_2(getrow(root)), getrow(rr))
Diff(getrow(ln), getrow(l10)*log(10))
Diff(getrow(rr), getrow(rragain))
Diff(getrow(one), 1)
return 0;
}
int main(){
//create a table with two rows.
//We didn't explicitly open a db with apop_db_open,
//so this will be an in-memory SQLite db.
apop_query("create table a(b); "
"insert into a values(1); "
"insert into a values(1); "
"create table randoms as "
"select ran() as rr "
/* join to create 2^13=8192 rows*/
"from a,a,a,a,a,a,a,a,a,a,a,a,a;");
"select rr, sqrt(rr) as root, "
"log(rr) as ln, log10(rr) as l10, "
"exp(log(rr)) as rragain, "
"pow(sin(rr),2)+pow(cos(rr),2) as one "
"from randoms");
apop_map(d, .fn_r=test_all);
//the pop variance of a Uniform[0,1]=1/12; kurtosis=1/80.
Apop_col_tv(d, "rr", rrow);
Diff(apop_var(rrow)*8191./8192., 1/12. );
Diff(apop_vector_kurtosis(rrow)*8191./8192., 1/80.);//approx.
Diff(apop_query_to_float("select stddev(rr) from randoms"),
sqrt(1/12.)*8192./8191);
}

Here is some more realistic sample code:

#include <apop.h>
int main(){
apop_opts.db_engine='s'; //SQLite only.
apop_query("create table atab (a numeric)");
for (int i=0; i< 1e5; i++)
apop_query("insert into atab values(ran())");
apop_query("create table powa as "
"select a, pow(a, 2) as sq, pow(a, 0.5) as sqrt "
"from atab");
//compare the std dev of a uniform as reported by the
//database routine, the matrix routine, and math.
double db_pop_stddev = apop_query_to_float("select stddev_pop(a) from powa");
apop_data *d = apop_query_to_data("select * from powa");
double matrix_pop_stddev = sqrt(apop_data_get(cov)*(d->matrix->size1/(d->matrix->size1-1.)));
assert(fabs(db_pop_stddev - matrix_pop_stddev) < 1e-4);
double actual_stddev = sqrt(2*gsl_pow_3(.5)/3);
assert(fabs(db_pop_stddev - actual_stddev) < 1e-3);
float sq_mean = apop_query_to_float("select avg(sq) from powa");
float actual_sq_mean = 1./3;
assert(fabs(sq_mean - actual_sq_mean) < 1e-3);
float sqrt_mean = apop_query_to_float("select avg(sqrt) from powa");
float actual_sqrt_mean = 2./3;
assert(fabs(sqrt_mean - actual_sqrt_mean) < 1e-3);
}

Autogenerated by doxygen on Wed Oct 15 2014 (Debian 0.999b+ds3-2).