Changeset: 50f3689592a5 for MonetDB
URL: https://dev.monetdb.org/hg/MonetDB?cmd=changeset;node=50f3689592a5
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/scripts/39_analytics.sql
sql/server/rel_select.c
sql/test/analytics/Tests/analytics14.sql
sql/test/analytics/Tests/analytics14.stable.out
Branch: statistics-analytics
Log Message:
Implemented stddev_pop as a window function
diffs (truncated from 526 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,6 @@ 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 GDKanalyticalstddev_samp(BAT *r, BAT *b, BAT *s, BAT *e,
int tpe);
+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
@@ -1736,8 +1736,8 @@ GDKanalyticalavg(BAT *r, BAT *b, BAT *s,
mean += delta / n; \
m2 += delta * ((dbl) v - mean); \
} \
- if (n > 1) { \
- *rb = sqrt(m2 / (n - 1)); \
+ if (n > sample) { \
+ *rb = sqrt(m2 / (n - sample)); \
} else { \
*rb = dbl_nil; \
has_nils = true; \
@@ -1749,10 +1749,10 @@ GDKanalyticalavg(BAT *r, BAT *b, BAT *s,
} while (0)
gdk_return
-GDKanalyticalstddev_samp(BAT *r, BAT *b, BAT *s, BAT *e, int tpe)
+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;
+ 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;
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
@@ -1537,12 +1537,11 @@ SQLavg(Client cntxt, MalBlkPtr mb, MalSt
return msg;
}
-str
-SQLstddev_samp(Client cntxt, MalBlkPtr mb, MalStkPtr stk, InstrPtr pci)
+static str
+do_stddev(Client cntxt, MalBlkPtr mb, MalStkPtr stk, InstrPtr pci, const char*
op, const char* err, bool issample)
{
BAT *r, *b, *s, *e;
- str msg = SQLanalytics_args(&r, &b, &s, &e, cntxt, mb, stk, pci,
TYPE_dbl, "sql.stddev",
- SQLSTATE(42000)
"stddev(:any_1,:lng,:lng)");
+ str msg = SQLanalytics_args(&r, &b, &s, &e, cntxt, mb, stk, pci,
TYPE_dbl, op, err);
int tpe = getArgType(mb, pci, 1);
gdk_return gdk_res;
@@ -1554,14 +1553,14 @@ SQLstddev_samp(Client cntxt, MalBlkPtr m
if (b) {
bat *res = getArgReference_bat(stk, pci, 0);
- gdk_res = GDKanalyticalstddev_samp(r, b, s, e, tpe);
+ gdk_res = GDKanalytical_variance(r, b, s, e, tpe, issample);
BBPunfix(b->batCacheid);
if (s) BBPunfix(s->batCacheid);
if (e) BBPunfix(e->batCacheid);
if (gdk_res == GDK_SUCCEED)
BBPkeepref(*res = r->batCacheid);
else
- throw(SQL, "sql.stddev", GDK_EXCEPTION);
+ throw(SQL, op, GDK_EXCEPTION);
} else {
dbl *res = getArgReference(stk, pci, 0);
@@ -1578,8 +1577,20 @@ SQLstddev_samp(Client cntxt, MalBlkPtr m
*res = dbl_nil;
break;
default:
- throw(SQL, "sql.stddev", SQLSTATE(42000)
"sql.stddev not available for %s", ATOMname(tpe));
+ throw(SQL, op, SQLSTATE(42000) "%s not
available for %s", op, ATOMname(tpe));
}
}
return msg;
}
+
+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);
+}
+
+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);
+}
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
@@ -37,5 +37,6 @@ 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);
#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
@@ -541,6 +541,24 @@ pattern batsql.avg(b:bat[:bte], s:bat[:l
address SQLavg
comment "return the average of groups";
+
+pattern sql.stdev(b:bte, s:lng, e:lng) :dbl
+address SQLstddev_samp
+comment "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";
+
+
+pattern sql.stdevp(b:bte, s:lng, e:lng) :dbl
+address SQLstddev_pop
+comment "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";
+
pattern sql.avg(b:sht, s:lng, e:lng) :dbl
address SQLavg
comment "return the average of groups";
@@ -549,6 +567,24 @@ pattern batsql.avg(b:bat[:sht], s:bat[:l
address SQLavg
comment "return the average of groups";
+
+pattern sql.stdev(b:sht, s:lng, e:lng) :dbl
+address SQLstddev_samp
+comment "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";
+
+
+pattern sql.stdevp(b:sht, s:lng, e:lng) :dbl
+address SQLstddev_pop
+comment "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";
+
pattern sql.avg(b:int, s:lng, e:lng) :dbl
address SQLavg
comment "return the average of groups";
@@ -557,6 +593,24 @@ pattern batsql.avg(b:bat[:int], s:bat[:l
address SQLavg
comment "return the average of groups";
+
+pattern sql.stdev(b:int, s:lng, e:lng) :dbl
+address SQLstddev_samp
+comment "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";
+
+
+pattern sql.stdevp(b:int, s:lng, e:lng) :dbl
+address SQLstddev_pop
+comment "standard deviation of groups";
+
+pattern batsql.stdevp(b:bat[:int], s:bat[:lng], e:bat[:lng]) :bat[:dbl]
+address SQLstddev_pop
+comment "standard deviation of groups";
+
pattern sql.avg(b:lng, s:lng, e:lng) :dbl
address SQLavg
comment "return the average of groups";
@@ -565,6 +619,24 @@ pattern batsql.avg(b:bat[:lng], s:bat[:l
address SQLavg
comment "return the average of groups";
+
+pattern sql.stdev(b:lng, s:lng, e:lng) :dbl
+address SQLstddev_samp
+comment "standard deviation of groups";
+
+pattern batsql.stdev(b:bat[:lng], s:bat[:lng], e:bat[:lng]) :bat[:dbl]
+address SQLstddev_samp
+comment "standard deviation of groups";
+
+
+pattern sql.stdevp(b:lng, s:lng, e:lng) :dbl
+address SQLstddev_pop
+comment "standard deviation of groups";
+
+pattern batsql.stdevp(b:bat[:lng], s:bat[:lng], e:bat[:lng]) :bat[:dbl]
+address SQLstddev_pop
+comment "standard deviation of groups";
+
pattern sql.avg(b:flt, s:lng, e:lng) :dbl
address SQLavg
comment "return the average of groups";
@@ -573,6 +645,24 @@ pattern batsql.avg(b:bat[:flt], s:bat[:l
address SQLavg
comment "return the average of groups";
+
+pattern sql.stdev(b:flt, s:lng, e:lng) :dbl
+address SQLstddev_samp
+comment "standard deviation of groups";
+
+pattern batsql.stdev(b:bat[:flt], s:bat[:lng], e:bat[:lng]) :bat[:dbl]
+address SQLstddev_samp
+comment "standard deviation of groups";
+
+
+pattern sql.stdevp(b:flt, s:lng, e:lng) :dbl
+address SQLstddev_pop
+comment "standard deviation of groups";
+
+pattern batsql.stdevp(b:bat[:flt], s:bat[:lng], e:bat[:lng]) :bat[:dbl]
+address SQLstddev_pop
+comment "standard deviation of groups";
+
pattern sql.avg(b:dbl, s:lng, e:lng) :dbl
address SQLavg
comment "return the average of groups";
@@ -581,45 +671,6 @@ pattern batsql.avg(b:bat[:dbl], s:bat[:l
address SQLavg
comment "return the average of groups";
-pattern sql.stdev(b:bte, s:lng, e:lng) :dbl
-address SQLstddev_samp
-comment "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";
-
-pattern sql.stdev(b:sht, s:lng, e:lng) :dbl
-address SQLstddev_samp
-comment "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";
-
-pattern sql.stdev(b:int, s:lng, e:lng) :dbl
-address SQLstddev_samp
-comment "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";
-
-pattern sql.stdev(b:lng, s:lng, e:lng) :dbl
-address SQLstddev_samp
-comment "standard deviation of groups";
-
-pattern batsql.stdev(b:bat[:lng], s:bat[:lng], e:bat[:lng]) :bat[:dbl]
-address SQLstddev_samp
-comment "standard deviation of groups";
-
-pattern sql.stdev(b:flt, s:lng, e:lng) :dbl
-address SQLstddev_samp
-comment "standard deviation of groups";
-
-pattern batsql.stdev(b:bat[:flt], s:bat[:lng], e:bat[:lng]) :bat[:dbl]
-address SQLstddev_samp
-comment "standard deviation of groups";
pattern sql.stdev(b:dbl, s:lng, e:lng) :dbl
address SQLstddev_samp
@@ -629,6 +680,15 @@ pattern batsql.stdev(b:bat[:dbl], s:bat[
address SQLstddev_samp
comment "standard deviation of groups";
+
+pattern sql.stdevp(b:dbl, s:lng, e:lng) :dbl
+address SQLstddev_pop
+comment "standard deviation of groups";
+
+pattern batsql.stdevp(b:bat[:dbl], s:bat[:lng], e:bat[:lng]) :bat[:dbl]
+address SQLstddev_pop
+comment "standard deviation of groups";
+
command aggr.exist(b:bat[:any_2], h:any_1):bit
address ALGexist;
diff --git a/sql/backends/monet5/sql_rank.mal.sh
b/sql/backends/monet5/sql_rank.mal.sh
--- a/sql/backends/monet5/sql_rank.mal.sh
+++ b/sql/backends/monet5/sql_rank.mal.sh
@@ -377,11 +377,7 @@ pattern batsql.avg(b:bat[:${tp1#*:}], s:
_______________________________________________
checkin-list mailing list
[email protected]
https://www.monetdb.org/mailman/listinfo/checkin-list
