Changeset: f85ae0bfc50d for MonetDB
URL: https://dev.monetdb.org/hg/MonetDB?cmd=changeset;node=f85ae0bfc50d
Added Files:
sql/test/analytics/Tests/analytics15.sql
sql/test/analytics/Tests/analytics15.stable.err
sql/test/analytics/Tests/analytics15.stable.out
Modified Files:
gdk/gdk_analytic.h
gdk/gdk_analytic_func.c
sql/backends/monet5/sql_rank.c
sql/test/analytics/Tests/All
Branch: statistics-analytics
Log Message:
Implemented covar_pop and covar_samp as window functions
diffs (truncated from 1528 to 300 lines):
diff --git a/gdk/gdk_analytic.h b/gdk/gdk_analytic.h
--- a/gdk/gdk_analytic.h
+++ b/gdk/gdk_analytic.h
@@ -43,7 +43,7 @@ gdk_export gdk_return GDKanalytical_stdd
gdk_export gdk_return GDKanalytical_variance_samp(BAT *r, BAT *b, BAT *s, BAT
*e, int tpe);
gdk_export gdk_return GDKanalytical_variance_pop(BAT *r, BAT *b, BAT *s, BAT
*e, int tpe);
gdk_export gdk_return GDKanalytical_covariance_pop(BAT *r, BAT *b1, BAT *b2,
BAT *s, BAT *e, int tpe);
-gdk_export gdk_return GDKanalytical_covariance_sample(BAT *r, BAT *b1, BAT
*b2, BAT *s, BAT *e, int tpe);
+gdk_export gdk_return GDKanalytical_covariance_samp(BAT *r, BAT *b1, BAT *b2,
BAT *s, BAT *e, int tpe);
gdk_export gdk_return GDKanalytical_quantile(BAT *r, BAT *b, BAT *s, BAT *e,
BAT *q, const void *restrict quantile, int tp1, int tp2);
gdk_export gdk_return GDKanalytical_quantile_avg(BAT *r, BAT *b, BAT *s, BAT
*e, BAT *q, const void *restrict quantile, int tp1, int tp2);
diff --git a/gdk/gdk_analytic_func.c b/gdk/gdk_analytic_func.c
--- a/gdk/gdk_analytic_func.c
+++ b/gdk/gdk_analytic_func.c
@@ -1797,6 +1797,92 @@ GDK_ANALYTICAL_STDEV_VARIANCE(stddev_pop
GDK_ANALYTICAL_STDEV_VARIANCE(variance_samp, 1, m2 / (n - 1), "variance")
GDK_ANALYTICAL_STDEV_VARIANCE(variance_pop, 0, m2 / n, "variance")
+#define ANALYTICAL_COVARIANCE_CALC(TPE, SAMPLE, OP) \
+ do { \
+ TPE *bp1 = (TPE*)Tloc(b1, 0), *bp2 = (TPE*)Tloc(b2, 0), *bs1,
*be1, *bs2, v1, v2; \
+ for (; i < cnt; i++, rb++) { \
+ bs1 = bp1 + start[i]; \
+ be1 = bp1 + end[i]; \
+ bs2 = bp2 + start[i]; \
+ for (; bs1 < be1; bs1++, bs2++) { \
+ v1 = *bs1; \
+ v2 = *bs2; \
+ if (is_##TPE##_nil(v1) || is_##TPE##_nil(v2))
\
+ continue; \
+ n++; \
+ delta1 = (dbl) v1 - mean1; \
+ mean1 += delta1 / n; \
+ delta2 = (dbl) v2 - mean2; \
+ mean2 += delta2 / n; \
+ m2 += delta1 * ((dbl) v2 - mean2); \
+ } \
+ if (n > SAMPLE) { \
+ *rb = OP; \
+ } else { \
+ *rb = dbl_nil; \
+ nils++; \
+ } \
+ n = 0; \
+ mean1 = 0; \
+ mean2 = 0; \
+ m2 = 0; \
+ } \
+ } while (0)
+
+#ifdef HAVE_HGE
+#define ANALYTICAL_COVARIANCE_LIMIT(SAMPLE, OP) \
+ case TYPE_hge: \
+ ANALYTICAL_COVARIANCE_CALC(hge, SAMPLE, OP); \
+ break;
+#else
+#define ANALYTICAL_COVARIANCE_LIMIT(SAMPLE, OP)
+#endif
+
+#define GDK_ANALYTICAL_COVARIANCE(NAME, SAMPLE, OP) \
+gdk_return \
+GDKanalytical_##NAME(BAT *r, BAT *b1, BAT *b2, BAT *s, BAT *e, int tpe) \
+{ \
+ BUN i = 0, cnt = BATcount(b1), n = 0, nils = 0; \
+ lng *restrict start, *restrict end; \
+ dbl *restrict rb = (dbl *) Tloc(r, 0), mean1 = 0, mean2 = 0, m2 = 0,
delta1, delta2; \
+ \
+ assert(s && e && BATcount(b1) == BATcount(b2)); \
+ start = (lng *) Tloc(s, 0); \
+ end = (lng *) Tloc(e, 0); \
+ \
+ switch (tpe) { \
+ case TYPE_bte: \
+ ANALYTICAL_COVARIANCE_CALC(bte, SAMPLE, OP); \
+ break; \
+ case TYPE_sht: \
+ ANALYTICAL_COVARIANCE_CALC(sht, SAMPLE, OP); \
+ break; \
+ case TYPE_int: \
+ ANALYTICAL_COVARIANCE_CALC(int, SAMPLE, OP); \
+ break; \
+ case TYPE_lng: \
+ ANALYTICAL_COVARIANCE_CALC(lng, SAMPLE, OP); \
+ break; \
+ ANALYTICAL_COVARIANCE_LIMIT(SAMPLE, OP) \
+ case TYPE_flt:\
+ ANALYTICAL_COVARIANCE_CALC(flt, SAMPLE, OP); \
+ break; \
+ case TYPE_dbl: \
+ ANALYTICAL_COVARIANCE_CALC(dbl, SAMPLE, OP); \
+ break; \
+ default: \
+ GDKerror("%s: covariance of type %s unsupported.\n", __func__,
ATOMname(tpe)); \
+ return GDK_FAIL; \
+ } \
+ BATsetcount(r, cnt); \
+ r->tnonil = nils == 0; \
+ r->tnil = nils > 0; \
+ return GDK_SUCCEED; \
+}
+
+GDK_ANALYTICAL_COVARIANCE(covariance_samp, 1, m2 / (n - 1))
+GDK_ANALYTICAL_COVARIANCE(covariance_pop, 0, m2 / n)
+
/* There will be always at least one value for the quantile, because we don't
implement the exclude clause yet */
#define ANALYTICAL_QUANTILE_IMP_FIXED(TPE1, TPE2, NEXT_QUA)
\
@@ -2104,27 +2190,3 @@ quantile_nosupport:
GDKerror("%s: type %s not supported for the quantile.\n", __func__,
ATOMname(tp2));
return GDK_FAIL;
}
-
-gdk_return
-GDKanalytical_covariance_pop(BAT *r, BAT *b1, BAT *b2, BAT *s, BAT *e, int tpe)
-{
- (void) r;
- (void) b1;
- (void) b2;
- (void) s;
- (void) e;
- (void) tpe;
- return GDK_SUCCEED;
-}
-
-gdk_return
-GDKanalytical_covariance_sample(BAT *r, BAT *b1, BAT *b2, BAT *s, BAT *e, int
tpe)
-{
- (void) r;
- (void) b1;
- (void) b2;
- (void) s;
- (void) e;
- (void) tpe;
- return GDK_SUCCEED;
-}
diff --git a/sql/backends/monet5/sql_rank.c b/sql/backends/monet5/sql_rank.c
--- a/sql/backends/monet5/sql_rank.c
+++ b/sql/backends/monet5/sql_rank.c
@@ -1647,7 +1647,7 @@ SQLvar_pop(Client cntxt, MalBlkPtr mb, M
static str
do_covariance_and_correlation(Client cntxt, MalBlkPtr mb, MalStkPtr stk,
InstrPtr pci, const char* op, const char* err,
- gdk_return
(*func)(BAT *, BAT *, BAT *, BAT *, BAT *, int))
+ gdk_return
(*func)(BAT *, BAT *, BAT *, BAT *, BAT *, int), BUN minimum)
{
BAT *r = NULL, *b = NULL, *c = NULL, *s = NULL, *e = NULL;
int tp1, tp2;
@@ -1681,46 +1681,63 @@ do_covariance_and_correlation(Client cnt
throw(SQL, op, SQLSTATE(HY005) "Cannot access column
descriptor");
voidresultBAT(r, TYPE_dbl, BATcount(b), b, op);
- if (isaBatType(getArgType(mb, pci, 3))) {
- s = BATdescriptor(*getArgReference_bat(stk, pci, 3));
- if (!s) {
- BBPunfix(b->batCacheid);
- BBPunfix(r->batCacheid);
- throw(SQL, op, SQLSTATE(HY005) "Cannot access
column descriptor");
- }
+ s = BATdescriptor(*getArgReference_bat(stk, pci, 3));
+ if (!s) {
+ BBPunfix(b->batCacheid);
+ BBPunfix(r->batCacheid);
+ throw(SQL, op, SQLSTATE(HY005) "Cannot access column
descriptor");
}
- if (isaBatType(getArgType(mb, pci, 4))) {
- e = BATdescriptor(*getArgReference_bat(stk, pci, 4));
- if (!e) {
- BBPunfix(b->batCacheid);
- BBPunfix(r->batCacheid);
- if (s) BBPunfix(s->batCacheid);
- throw(SQL, op, SQLSTATE(HY005) "Cannot access
column descriptor");
- }
+ e = BATdescriptor(*getArgReference_bat(stk, pci, 4));
+ if (!e) {
+ BBPunfix(b->batCacheid);
+ BBPunfix(r->batCacheid);
+ BBPunfix(s->batCacheid);
+ throw(SQL, op, SQLSTATE(HY005) "Cannot access column
descriptor");
}
if (is_a_bat2) {
c = BATdescriptor(*getArgReference_bat(stk, pci, 2));
if (!e) {
BBPunfix(b->batCacheid);
BBPunfix(r->batCacheid);
- if (s) BBPunfix(s->batCacheid);
- if (e) BBPunfix(e->batCacheid);
+ BBPunfix(s->batCacheid);
+ BBPunfix(e->batCacheid);
throw(SQL, op, SQLSTATE(HY005) "Cannot access
column descriptor");
}
gdk_res = func(r, b, c, s, e, tp1);
} else {
- BUN cnt = BATcount(b);
+ BUN cnt = BATcount(b), n = 0;
+ BATiter bi = bat_iterator(b); /* corner case, second
column is a constant, calculate it this way... */
+ void *curval;
+ const void *restrict nil = ATOMnilptr(tp1);
+ int (*cmp) (const void *, const void *) =
ATOMcompare(tp1);
ValRecord *input2 = &(stk)->stk[(pci)->argv[2]];
dbl *restrict rb = (dbl*) Tloc(r, 0), res =
VALisnil(input2) ? dbl_nil : 0;
+ lng *restrict start = (lng*) Tloc(s, 0), *restrict end
= (lng*) Tloc(e, 0);
+ bool has_nils = VALisnil(input2);
- for (BUN i = 0 ; i < cnt; i++)
- rb[i] = res;
+ for (BUN i = 0; i < cnt; i++) {
+ for (lng j = start[i] ; j < end[i] ; j++) {
+ curval = BUNtail(bi, j);
+ if (!cmp(curval, nil))
+ continue;
+ n++;
+ }
+ if (n > minimum) { /* covariance_samp requires
at least one value */
+ rb[i] = res;
+ } else {
+ rb[i] = dbl_nil;
+ has_nils = true;
+ }
+ n = 0;
+ }
BATsetcount(r, cnt);
+ r->tnonil = !has_nils;
+ r->tnil = has_nils;
}
BBPunfix(b->batCacheid);
- if (s) BBPunfix(s->batCacheid);
- if (e) BBPunfix(e->batCacheid);
+ BBPunfix(s->batCacheid);
+ BBPunfix(e->batCacheid);
if (c) BBPunfix(c->batCacheid);
if (gdk_res == GDK_SUCCEED)
BBPkeepref(*res = r->batCacheid);
@@ -1754,14 +1771,14 @@ str
SQLcovar_samp(Client cntxt, MalBlkPtr mb, MalStkPtr stk, InstrPtr pci)
{
return do_covariance_and_correlation(cntxt, mb, stk, pci,
"sql.covariance", SQLSTATE(42000) "covariance(:any_1,:any_1,:lng,:lng)",
-
GDKanalytical_covariance_pop);
+
GDKanalytical_covariance_samp, 1);
}
str
SQLcovar_pop(Client cntxt, MalBlkPtr mb, MalStkPtr stk, InstrPtr pci)
{
return do_covariance_and_correlation(cntxt, mb, stk, pci,
"sql.covariancep", SQLSTATE(42000) "covariancep(:any_1,:any_1,:lng,:lng)",
-
GDKanalytical_covariance_sample);
+
GDKanalytical_covariance_pop, 0);
}
#define QUANTILE_SINGLE_IMP(TPE) \
diff --git a/sql/test/analytics/Tests/All b/sql/test/analytics/Tests/All
--- a/sql/test/analytics/Tests/All
+++ b/sql/test/analytics/Tests/All
@@ -18,3 +18,4 @@ analytics13
# Statistical window function tests
analytics14
+analytics15
diff --git a/sql/test/analytics/Tests/analytics15.sql
b/sql/test/analytics/Tests/analytics15.sql
new file mode 100644
--- /dev/null
+++ b/sql/test/analytics/Tests/analytics15.sql
@@ -0,0 +1,98 @@
+start transaction;
+create table analytics (aa int, bb int);
+insert into analytics values (15, 3), (3, 1), (2, 1), (5, 3), (NULL, 2), (3,
2), (4, 1), (6, 3), (8, 2), (NULL, 4);
+
+select covar_samp(aa, aa) over (partition by bb) from analytics;
+select covar_samp(aa, aa) over (partition by bb order by bb asc) from
analytics;
+select covar_samp(aa, aa) over (partition by bb order by bb desc) from
analytics;
+select covar_samp(aa, aa) over (order by bb desc) from analytics;
+
+select covar_samp(bb, bb) over (partition by bb) from analytics;
+select covar_samp(bb, bb) over (partition by bb order by bb asc) from
analytics;
+select covar_samp(bb, bb) over (partition by bb order by bb desc) from
analytics;
+select covar_samp(bb, bb) over (order by bb desc) from analytics;
+
+
+select covar_pop(aa, aa) over (partition by bb) from analytics;
+select covar_pop(aa, aa) over (partition by bb order by bb asc) from analytics;
+select covar_pop(aa, aa) over (partition by bb order by bb desc) from
analytics;
+select covar_pop(aa, aa) over (order by bb desc) from analytics;
+
+select covar_pop(bb, bb) over (partition by bb) from analytics;
+select covar_pop(bb, bb) over (partition by bb order by bb asc) from analytics;
+select covar_pop(bb, bb) over (partition by bb order by bb desc) from
analytics;
+select covar_pop(bb, bb) over (order by bb desc) from analytics;
+
+
+select covar_samp(aa, bb) over (partition by bb) from analytics;
+select covar_samp(aa, bb) over (partition by bb order by bb asc) from
analytics;
+select covar_samp(aa, bb) over (partition by bb order by bb desc) from
analytics;
+select covar_samp(aa, bb) over (order by bb desc) from analytics;
+
+select covar_samp(bb, aa) over (partition by bb) from analytics;
+select covar_samp(bb, aa) over (partition by bb order by bb asc) from
analytics;
+select covar_samp(bb, aa) over (partition by bb order by bb desc) from
analytics;
+select covar_samp(bb, aa) over (order by bb desc) from analytics;
+
_______________________________________________
checkin-list mailing list
[email protected]
https://www.monetdb.org/mailman/listinfo/checkin-list
