Changeset: 619cab065c15 for MonetDB
URL: https://dev.monetdb.org/hg/MonetDB?cmd=changeset;node=619cab065c15
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/scripts/39_analytics.sql
sql/test/analytics/Tests/analytics14.sql
sql/test/analytics/Tests/analytics14.stable.out
Branch: statistics-analytics
Log Message:
Implemented var_samp and var_pop as window functions
diffs (truncated from 928 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
@@ -38,6 +38,7 @@ gdk_export gdk_return GDKanalyticalsum(B
gdk_export gdk_return GDKanalyticalprod(BAT *r, BAT *b, BAT *s, BAT *e, int
tp1, int tp2);
gdk_export gdk_return GDKanalyticalavg(BAT *r, BAT *b, BAT *s, BAT *e, int
tpe);
+gdk_export gdk_return GDKanalytical_stddev(BAT *r, BAT *b, BAT *s, BAT *e, int
tpe, bool issample);
gdk_export gdk_return GDKanalytical_variance(BAT *r, BAT *b, BAT *s, BAT *e,
int tpe, bool issample);
#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
@@ -1721,7 +1721,7 @@ GDKanalyticalavg(BAT *r, BAT *b, BAT *s,
return GDK_SUCCEED;
}
-#define ANALYTICAL_STDEV_CALC(TPE) \
+#define ANALYTICAL_STDEV_VARIANCE_CALC(TPE, OP) \
do { \
TPE *bp = (TPE*)Tloc(b, 0), *bs, *be, v; \
for (; i < cnt; i++, rb++) { \
@@ -1737,7 +1737,7 @@ GDKanalyticalavg(BAT *r, BAT *b, BAT *s,
m2 += delta * ((dbl) v - mean); \
} \
if (n > sample) { \
- *rb = sqrt(m2 / (n - sample)); \
+ *rb = OP; \
} else { \
*rb = dbl_nil; \
has_nils = true; \
@@ -1748,48 +1748,57 @@ GDKanalyticalavg(BAT *r, BAT *b, BAT *s,
} \
} while (0)
-gdk_return
-GDKanalytical_variance(BAT *r, BAT *b, BAT *s, BAT *e, int tpe, bool issample)
-{
- bool has_nils = false;
- BUN i = 0, cnt = BATcount(b), n = 0, sample = (BUN) issample;
- lng *restrict start, *restrict end;
- dbl *restrict rb = (dbl *) Tloc(r, 0), mean = 0, m2 = 0, delta;
-
- assert(s && e);
- start = (lng *) Tloc(s, 0);
- end = (lng *) Tloc(e, 0);
+#ifdef HAVE_HGE
+#define ANALYTICAL_STDEV_VARIANCE_LIMIT(OP) \
+ case TYPE_hge: \
+ ANALYTICAL_STDEV_VARIANCE_CALC(hge, OP); \
+ break;
+#else
+#define ANALYTICAL_STDEV_VARIANCE_LIMIT(OP)
+#endif
- switch (tpe) {
- case TYPE_bte:
- ANALYTICAL_STDEV_CALC(bte);
- break;
- case TYPE_sht:
- ANALYTICAL_STDEV_CALC(sht);
- break;
- case TYPE_int:
- ANALYTICAL_STDEV_CALC(int);
- break;
- case TYPE_lng:
- ANALYTICAL_STDEV_CALC(lng);
- break;
-#ifdef HAVE_HGE
- case TYPE_hge:
- ANALYTICAL_STDEV_CALC(hge);
- break;
-#endif
- case TYPE_flt:
- ANALYTICAL_STDEV_CALC(flt);
- break;
- case TYPE_dbl:
- ANALYTICAL_STDEV_CALC(dbl);
- break;
- default:
- GDKerror("%s: average of type %s unsupported.\n", __func__,
ATOMname(tpe));
- return GDK_FAIL;
- }
- BATsetcount(r, cnt);
- r->tnonil = !has_nils;
- r->tnil = has_nils;
- return GDK_SUCCEED;
+#define GDK_ANALYTICAL_STDEV_VARIANCE(NAME, OP) \
+gdk_return \
+GDKanalytical_##NAME(BAT *r, BAT *b, BAT *s, BAT *e, int tpe, bool issample) \
+{ \
+ bool has_nils = false; \
+ BUN i = 0, cnt = BATcount(b), n = 0, sample = (BUN) issample; \
+ lng *restrict start, *restrict end; \
+ dbl *restrict rb = (dbl *) Tloc(r, 0), mean = 0, m2 = 0, delta; \
+ \
+ assert(s && e); \
+ start = (lng *) Tloc(s, 0); \
+ end = (lng *) Tloc(e, 0); \
+ \
+ switch (tpe) { \
+ case TYPE_bte: \
+ ANALYTICAL_STDEV_VARIANCE_CALC(bte, OP); \
+ break; \
+ case TYPE_sht: \
+ ANALYTICAL_STDEV_VARIANCE_CALC(sht, OP); \
+ break; \
+ case TYPE_int: \
+ ANALYTICAL_STDEV_VARIANCE_CALC(int, OP); \
+ break; \
+ case TYPE_lng: \
+ ANALYTICAL_STDEV_VARIANCE_CALC(lng, OP); \
+ break; \
+ ANALYTICAL_STDEV_VARIANCE_LIMIT(OP) \
+ case TYPE_flt:\
+ ANALYTICAL_STDEV_VARIANCE_CALC(flt, OP); \
+ break; \
+ case TYPE_dbl: \
+ ANALYTICAL_STDEV_VARIANCE_CALC(dbl, OP); \
+ break; \
+ default: \
+ GDKerror("%s: average of type %s unsupported.\n", __func__,
ATOMname(tpe)); \
+ return GDK_FAIL; \
+ } \
+ BATsetcount(r, cnt); \
+ r->tnonil = !has_nils; \
+ r->tnil = has_nils; \
+ return GDK_SUCCEED; \
}
+
+GDK_ANALYTICAL_STDEV_VARIANCE(stddev, sqrt(m2 / (n - sample)))
+GDK_ANALYTICAL_STDEV_VARIANCE(variance, m2 / (n - sample))
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
@@ -1538,7 +1538,8 @@ SQLavg(Client cntxt, MalBlkPtr mb, MalSt
}
static str
-do_stddev(Client cntxt, MalBlkPtr mb, MalStkPtr stk, InstrPtr pci, const char*
op, const char* err, bool issample)
+do_stddev_and_variance(Client cntxt, MalBlkPtr mb, MalStkPtr stk, InstrPtr
pci, const char* op, const char* err,
+ bool issample, gdk_return
(*func)(BAT *, BAT *, BAT *, BAT *, int, bool))
{
BAT *r, *b, *s, *e;
str msg = SQLanalytics_args(&r, &b, &s, &e, cntxt, mb, stk, pci,
TYPE_dbl, op, err);
@@ -1553,7 +1554,7 @@ do_stddev(Client cntxt, MalBlkPtr mb, Ma
if (b) {
bat *res = getArgReference_bat(stk, pci, 0);
- gdk_res = GDKanalytical_variance(r, b, s, e, tpe, issample);
+ gdk_res = func(r, b, s, e, tpe, issample);
BBPunfix(b->batCacheid);
if (s) BBPunfix(s->batCacheid);
if (e) BBPunfix(e->batCacheid);
@@ -1586,11 +1587,27 @@ do_stddev(Client cntxt, MalBlkPtr mb, Ma
str
SQLstddev_samp(Client cntxt, MalBlkPtr mb, MalStkPtr stk, InstrPtr pci)
{
- return do_stddev(cntxt, mb, stk, pci, "sql.stdev", SQLSTATE(42000)
"stddev(:any_1,:lng,:lng)", true);
+ return do_stddev_and_variance(cntxt, mb, stk, pci, "sql.stdev",
SQLSTATE(42000) "stddev(:any_1,:lng,:lng)",
+ true,
GDKanalytical_stddev);
}
str
SQLstddev_pop(Client cntxt, MalBlkPtr mb, MalStkPtr stk, InstrPtr pci)
{
- return do_stddev(cntxt, mb, stk, pci, "sql.stdevp", SQLSTATE(42000)
"stdevp(:any_1,:lng,:lng)", false);
+ return do_stddev_and_variance(cntxt, mb, stk, pci, "sql.stdevp",
SQLSTATE(42000) "stdevp(:any_1,:lng,:lng)",
+ false,
GDKanalytical_stddev);
}
+
+str
+SQLvar_samp(Client cntxt, MalBlkPtr mb, MalStkPtr stk, InstrPtr pci)
+{
+ return do_stddev_and_variance(cntxt, mb, stk, pci, "sql.variance",
SQLSTATE(42000) "variance(:any_1,:lng,:lng)",
+ true,
GDKanalytical_variance);
+}
+
+str
+SQLvar_pop(Client cntxt, MalBlkPtr mb, MalStkPtr stk, InstrPtr pci)
+{
+ return do_stddev_and_variance(cntxt, mb, stk, pci, "sql.variancep",
SQLSTATE(42000) "variancep(:any_1,:lng,:lng)",
+ false,
GDKanalytical_variance);
+}
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
@@ -38,5 +38,7 @@ sql5_export str SQLavg(Client cntxt, Mal
/* statistical functions */
sql5_export str SQLstddev_samp(Client cntxt, MalBlkPtr mb, MalStkPtr stk,
InstrPtr pci);
sql5_export str SQLstddev_pop(Client cntxt, MalBlkPtr mb, MalStkPtr stk,
InstrPtr pci);
+sql5_export str SQLvar_samp(Client cntxt, MalBlkPtr mb, MalStkPtr stk,
InstrPtr pci);
+sql5_export str SQLvar_pop(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
@@ -544,20 +544,36 @@ comment "return the average of groups";
pattern sql.stdev(b:bte, s:lng, e:lng) :dbl
address SQLstddev_samp
-comment "standard deviation of groups";
+comment "return the standard deviation of groups";
pattern batsql.stdev(b:bat[:bte], s:bat[:lng], e:bat[:lng]) :bat[:dbl]
address SQLstddev_samp
-comment "standard deviation of groups";
-
+comment "return the standard deviation of groups";
pattern sql.stdevp(b:bte, s:lng, e:lng) :dbl
address SQLstddev_pop
-comment "standard deviation of groups";
+comment "return the standard deviation of groups";
pattern batsql.stdevp(b:bat[:bte], s:bat[:lng], e:bat[:lng]) :bat[:dbl]
address SQLstddev_pop
-comment "standard deviation of groups";
+comment "return the standard deviation of groups";
+
+
+pattern sql.variance(b:bte, s:lng, e:lng) :dbl
+address SQLvar_samp
+comment "return the variance of groups";
+
+pattern batsql.variance(b:bat[:bte], s:bat[:lng], e:bat[:lng]) :bat[:dbl]
+address SQLvar_samp
+comment "return the variance of groups";
+
+pattern sql.variancep(b:bte, s:lng, e:lng) :dbl
+address SQLvar_pop
+comment "return the variance of groups";
+
+pattern batsql.variancep(b:bat[:bte], s:bat[:lng], e:bat[:lng]) :bat[:dbl]
+address SQLvar_pop
+comment "return the variance of groups";
pattern sql.avg(b:sht, s:lng, e:lng) :dbl
address SQLavg
@@ -570,20 +586,36 @@ comment "return the average of groups";
pattern sql.stdev(b:sht, s:lng, e:lng) :dbl
address SQLstddev_samp
-comment "standard deviation of groups";
+comment "return the standard deviation of groups";
pattern batsql.stdev(b:bat[:sht], s:bat[:lng], e:bat[:lng]) :bat[:dbl]
address SQLstddev_samp
-comment "standard deviation of groups";
-
+comment "return the standard deviation of groups";
pattern sql.stdevp(b:sht, s:lng, e:lng) :dbl
address SQLstddev_pop
-comment "standard deviation of groups";
+comment "return the standard deviation of groups";
pattern batsql.stdevp(b:bat[:sht], s:bat[:lng], e:bat[:lng]) :bat[:dbl]
address SQLstddev_pop
-comment "standard deviation of groups";
+comment "return the standard deviation of groups";
+
+
+pattern sql.variance(b:sht, s:lng, e:lng) :dbl
+address SQLvar_samp
+comment "return the variance of groups";
+
+pattern batsql.variance(b:bat[:sht], s:bat[:lng], e:bat[:lng]) :bat[:dbl]
+address SQLvar_samp
+comment "return the variance of groups";
+
+pattern sql.variancep(b:sht, s:lng, e:lng) :dbl
+address SQLvar_pop
+comment "return the variance of groups";
+
+pattern batsql.variancep(b:bat[:sht], s:bat[:lng], e:bat[:lng]) :bat[:dbl]
+address SQLvar_pop
+comment "return the variance of groups";
pattern sql.avg(b:int, s:lng, e:lng) :dbl
address SQLavg
@@ -596,20 +628,36 @@ comment "return the average of groups";
pattern sql.stdev(b:int, s:lng, e:lng) :dbl
address SQLstddev_samp
-comment "standard deviation of groups";
+comment "return the standard deviation of groups";
pattern batsql.stdev(b:bat[:int], s:bat[:lng], e:bat[:lng]) :bat[:dbl]
address SQLstddev_samp
-comment "standard deviation of groups";
-
+comment "return the standard deviation of groups";
pattern sql.stdevp(b:int, s:lng, e:lng) :dbl
address SQLstddev_pop
-comment "standard deviation of groups";
+comment "return the standard deviation of groups";
_______________________________________________
checkin-list mailing list
[email protected]
https://www.monetdb.org/mailman/listinfo/checkin-list
