Changeset: 3ca5b1c53516 for MonetDB
URL: https://dev.monetdb.org/hg/MonetDB?cmd=changeset;node=3ca5b1c53516
Modified Files:
gdk/gdk_analytic.h
gdk/gdk_analytic_func.c
sql/backends/monet5/sql_rank.c
sql/backends/monet5/sql_rank.h
sql/backends/monet5/sql_rank.mal
sql/backends/monet5/sql_rank.mal.sh
sql/backends/monet5/sql_rank_hge.mal
sql/backends/monet5/sql_rank_hge.mal.sh
sql/backends/monet5/sql_upgrades.c
sql/scripts/39_analytics.sql
sql/scripts/39_analytics_hge.sql
sql/test/analytics/Tests/analytics15.sql
sql/test/analytics/Tests/analytics15.stable.out
Branch: statistics-analytics
Log Message:
Implemented correlation as a window function.
There's room for optimization, but first implement it.
Also added missing correlation aggregate for interval types.
diffs (truncated from 1167 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
@@ -44,5 +44,6 @@ gdk_export gdk_return GDKanalytical_vari
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_samp(BAT *r, BAT *b1, BAT *b2,
BAT *s, BAT *e, int tpe);
+gdk_export gdk_return GDKanalytical_correlation(BAT *r, BAT *b1, BAT *b2, BAT
*s, BAT *e, int tpe);
#endif //_GDK_ANALYTIC_H_
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
@@ -1882,3 +1882,86 @@ GDKanalytical_##NAME(BAT *r, BAT *b1, BA
GDK_ANALYTICAL_COVARIANCE(covariance_samp, 1, m2 / (n - 1))
GDK_ANALYTICAL_COVARIANCE(covariance_pop, 0, m2 / n)
+
+#define ANALYTICAL_CORRELATION_CALC(TPE) \
+ 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; \
+ aux = (dbl) v2 - mean2; \
+ up += delta1 * aux; \
+ down1 += delta1 * ((dbl) v1 - mean1); \
+ down2 += delta2 * aux; \
+ } \
+ if (n > 0 && up > 0 && down1 > 0 && down2 > 0) { \
+ *rb = (up / n) / (sqrt(down1 / n) * sqrt(down2
/ n)); \
+ assert(!is_dbl_nil(*rb)); \
+ } else { \
+ *rb = dbl_nil; \
+ nils++; \
+ } \
+ n = 0; \
+ mean1 = 0; \
+ mean2 = 0; \
+ up = 0; \
+ down1 = 0; \
+ down2 = 0; \
+ } \
+ } while (0)
+
+gdk_return
+GDKanalytical_correlation(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, up = 0,
down1 = 0, down2 = 0, delta1, delta2, aux;
+
+ assert(s && e && BATcount(b1) == BATcount(b2));
+ start = (lng *) Tloc(s, 0);
+ end = (lng *) Tloc(e, 0);
+
+ switch (tpe) {
+ case TYPE_bte:
+ ANALYTICAL_CORRELATION_CALC(bte);
+ break;
+ case TYPE_sht:
+ ANALYTICAL_CORRELATION_CALC(sht);
+ break;
+ case TYPE_int:
+ ANALYTICAL_CORRELATION_CALC(int);
+ break;
+ case TYPE_lng:
+ ANALYTICAL_CORRELATION_CALC(lng);
+ break;
+#ifdef HAVE_HGE
+ case TYPE_hge:
+ ANALYTICAL_CORRELATION_CALC(hge);
+ break;
+#endif
+ case TYPE_flt:
+ ANALYTICAL_CORRELATION_CALC(flt);
+ break;
+ case TYPE_dbl:
+ ANALYTICAL_CORRELATION_CALC(dbl);
+ break;
+ default:
+ GDKerror("%s: correlation 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;
+}
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), BUN minimum)
+ gdk_return
(*func)(BAT *, BAT *, BAT *, BAT *, BAT *, int), BUN minimum, dbl single_case)
{
BAT *r = NULL, *b = NULL, *c = NULL, *s = NULL, *e = NULL;
int tp1, tp2;
@@ -1711,9 +1711,9 @@ do_covariance_and_correlation(Client cnt
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;
+ dbl *restrict rb = (dbl*) Tloc(r, 0), res =
VALisnil(input2) ? dbl_nil : single_case;
lng *restrict start = (lng*) Tloc(s, 0), *restrict end
= (lng*) Tloc(e, 0);
- bool has_nils = VALisnil(input2);
+ bool has_nils = is_dbl_nil(res);
for (BUN i = 0; i < cnt; i++) {
for (lng j = start[i] ; j < end[i] ; j++) {
@@ -1771,12 +1771,19 @@ 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_samp, 1);
+
GDKanalytical_covariance_samp, 1, 0.0f);
}
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_pop, 0);
+
GDKanalytical_covariance_pop, 0, 0.0f);
}
+
+str
+SQLcorr(Client cntxt, MalBlkPtr mb, MalStkPtr stk, InstrPtr pci)
+{
+ return do_covariance_and_correlation(cntxt, mb, stk, pci, "sql.corr",
SQLSTATE(42000) "corr(:any_1,:any_1,:lng,:lng)",
+
GDKanalytical_correlation, 0, dbl_nil);
+}
diff --git a/sql/backends/monet5/sql_rank.h b/sql/backends/monet5/sql_rank.h
--- a/sql/backends/monet5/sql_rank.h
+++ b/sql/backends/monet5/sql_rank.h
@@ -42,5 +42,6 @@ sql5_export str SQLvar_samp(Client cntxt
sql5_export str SQLvar_pop(Client cntxt, MalBlkPtr mb, MalStkPtr stk, InstrPtr
pci);
sql5_export str SQLcovar_samp(Client cntxt, MalBlkPtr mb, MalStkPtr stk,
InstrPtr pci);
sql5_export str SQLcovar_pop(Client cntxt, MalBlkPtr mb, MalStkPtr stk,
InstrPtr pci);
+sql5_export str SQLcorr(Client cntxt, MalBlkPtr mb, MalStkPtr stk, InstrPtr
pci);
#endif /* _SQL_RANK_H */
diff --git a/sql/backends/monet5/sql_rank.mal b/sql/backends/monet5/sql_rank.mal
--- a/sql/backends/monet5/sql_rank.mal
+++ b/sql/backends/monet5/sql_rank.mal
@@ -610,6 +610,23 @@ address SQLcovar_pop
comment "return the covariance population value of groups";
+pattern sql.corr(b:bte, c:bte, s:lng, e:lng) :dbl
+address SQLcorr
+comment "return the correlation value of groups";
+
+pattern batsql.corr(b:bat[:bte], c:bte, s:bat[:lng], e:bat[:lng]) :bat[:dbl]
+address SQLcorr
+comment "return the correlation value of groups";
+
+pattern batsql.corr(b:bte, c:bat[:bte], s:lng, e:lng) :bat[:dbl]
+address SQLcorr
+comment "return the correlation value of groups";
+
+pattern batsql.corr(b:bat[:bte], c:bat[:bte], s:bat[:lng], e:bat[:lng])
:bat[:dbl]
+address SQLcorr
+comment "return the correlation value of groups";
+
+
pattern sql.avg(b:sht, s:lng, e:lng) :dbl
address SQLavg
comment "return the average of groups";
@@ -687,6 +704,23 @@ address SQLcovar_pop
comment "return the covariance population value of groups";
+pattern sql.corr(b:sht, c:sht, s:lng, e:lng) :dbl
+address SQLcorr
+comment "return the correlation value of groups";
+
+pattern batsql.corr(b:bat[:sht], c:sht, s:bat[:lng], e:bat[:lng]) :bat[:dbl]
+address SQLcorr
+comment "return the correlation value of groups";
+
+pattern batsql.corr(b:sht, c:bat[:sht], s:lng, e:lng) :bat[:dbl]
+address SQLcorr
+comment "return the correlation value of groups";
+
+pattern batsql.corr(b:bat[:sht], c:bat[:sht], s:bat[:lng], e:bat[:lng])
:bat[:dbl]
+address SQLcorr
+comment "return the correlation value of groups";
+
+
pattern sql.avg(b:int, s:lng, e:lng) :dbl
address SQLavg
comment "return the average of groups";
@@ -764,6 +798,23 @@ address SQLcovar_pop
comment "return the covariance population value of groups";
+pattern sql.corr(b:int, c:int, s:lng, e:lng) :dbl
+address SQLcorr
+comment "return the correlation value of groups";
+
+pattern batsql.corr(b:bat[:int], c:int, s:bat[:lng], e:bat[:lng]) :bat[:dbl]
+address SQLcorr
+comment "return the correlation value of groups";
+
+pattern batsql.corr(b:int, c:bat[:int], s:lng, e:lng) :bat[:dbl]
+address SQLcorr
+comment "return the correlation value of groups";
+
+pattern batsql.corr(b:bat[:int], c:bat[:int], s:bat[:lng], e:bat[:lng])
:bat[:dbl]
+address SQLcorr
+comment "return the correlation value of groups";
+
+
pattern sql.avg(b:lng, s:lng, e:lng) :dbl
address SQLavg
comment "return the average of groups";
@@ -841,6 +892,23 @@ address SQLcovar_pop
comment "return the covariance population value of groups";
+pattern sql.corr(b:lng, c:lng, s:lng, e:lng) :dbl
+address SQLcorr
+comment "return the correlation value of groups";
+
+pattern batsql.corr(b:bat[:lng], c:lng, s:bat[:lng], e:bat[:lng]) :bat[:dbl]
+address SQLcorr
+comment "return the correlation value of groups";
+
+pattern batsql.corr(b:lng, c:bat[:lng], s:lng, e:lng) :bat[:dbl]
+address SQLcorr
+comment "return the correlation value of groups";
+
+pattern batsql.corr(b:bat[:lng], c:bat[:lng], s:bat[:lng], e:bat[:lng])
:bat[:dbl]
+address SQLcorr
+comment "return the correlation value of groups";
+
+
pattern sql.avg(b:flt, s:lng, e:lng) :dbl
address SQLavg
comment "return the average of groups";
@@ -918,6 +986,23 @@ address SQLcovar_pop
comment "return the covariance population value of groups";
+pattern sql.corr(b:flt, c:flt, s:lng, e:lng) :dbl
+address SQLcorr
+comment "return the correlation value of groups";
+
+pattern batsql.corr(b:bat[:flt], c:flt, s:bat[:lng], e:bat[:lng]) :bat[:dbl]
+address SQLcorr
+comment "return the correlation value of groups";
+
+pattern batsql.corr(b:flt, c:bat[:flt], s:lng, e:lng) :bat[:dbl]
+address SQLcorr
+comment "return the correlation value of groups";
+
+pattern batsql.corr(b:bat[:flt], c:bat[:flt], s:bat[:lng], e:bat[:lng])
:bat[:dbl]
+address SQLcorr
+comment "return the correlation value of groups";
+
+
pattern sql.avg(b:dbl, s:lng, e:lng) :dbl
address SQLavg
comment "return the average of groups";
@@ -995,3 +1080,19 @@ address SQLcovar_pop
comment "return the covariance population value of groups";
+pattern sql.corr(b:dbl, c:dbl, s:lng, e:lng) :dbl
+address SQLcorr
+comment "return the correlation value of groups";
+
+pattern batsql.corr(b:bat[:dbl], c:dbl, s:bat[:lng], e:bat[:lng]) :bat[:dbl]
+address SQLcorr
+comment "return the correlation value of groups";
+
+pattern batsql.corr(b:dbl, c:bat[:dbl], s:lng, e:lng) :bat[:dbl]
+address SQLcorr
+comment "return the correlation value of groups";
+
+pattern batsql.corr(b:bat[:dbl], c:bat[:dbl], s:bat[:lng], e:bat[:lng])
:bat[:dbl]
+address SQLcorr
+comment "return the correlation value of groups";
+
diff --git a/sql/backends/monet5/sql_rank.mal.sh
b/sql/backends/monet5/sql_rank.mal.sh
_______________________________________________
checkin-list mailing list
[email protected]
https://www.monetdb.org/mailman/listinfo/checkin-list
