Changeset: ac4ff6297281 for MonetDB
URL: https://dev.monetdb.org/hg/MonetDB?cmd=changeset;node=ac4ff6297281
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/analytics14.sql
sql/test/analytics/Tests/analytics14.stable.out
Branch: statistics-analytics
Log Message:
Implemented median_avg as a window function
diffs (truncated from 692 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,5 +43,6 @@ 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_median(BAT *r, BAT *b, BAT *s, BAT *e, int
tpe);
+gdk_export gdk_return GDKanalytical_median_avg(BAT *r, BAT *b, 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
@@ -1896,3 +1896,78 @@ allocation_error:
GDKerror("GDKanalytical_median: malloc failure\n");
return GDK_FAIL;
}
+
+#define ANALYTICAL_MEDIAN_AVG(TPE) \
+ do { \
+ TPE *restrict bp = (TPE*) Tloc(b, 0), ns, ne; \
+ for (; i < cnt; i++, rb++) { \
+ ss = (BUN) start[i]; \
+ ee = (BUN) end[i]; \
+ f = (ee - ss - 1) * 0.5f; \
+ lo = floor(f); \
+ hi = ceil(f); \
+ ns = bp[ss + (BUN) hi]; \
+ ne = bp[ss + (BUN) lo]; \
+ if (is_##TPE##_nil(ns) || is_##TPE##_nil(ne)) { \
+ v = dbl_nil; \
+ has_nils = true; \
+ } else \
+ v = (f - lo) * ns + (lo + 1 - f) * ne; \
+ *rb = v; \
+ } \
+ } while (0)
+
+gdk_return
+GDKanalytical_median_avg(BAT *r, BAT *b, BAT *s, BAT *e, int tpe)
+{
+ bool has_nils = false;
+ BUN i = 0, cnt = BATcount(b), ss, ee;
+ lng *restrict start, *restrict end;
+ dbl f, lo, hi, v, *restrict rb = (dbl*) Tloc(r, 0);
+
+ assert(s && e);
+ start = (lng *) Tloc(s, 0);
+ end = (lng *) Tloc(e, 0);
+
+ if (!ATOMlinear(tpe)) {
+ GDKerror("GDKanalytical_median_avg: cannot determine quantile
on "
+ "non-linear type %s\n", ATOMname(tpe));
+ return GDK_FAIL;
+ }
+
+ switch (tpe) {
+ case TYPE_bte:
+ ANALYTICAL_MEDIAN_AVG(bte);
+ break;
+ case TYPE_sht:
+ ANALYTICAL_MEDIAN_AVG(sht);
+ break;
+ case TYPE_int:
+ ANALYTICAL_MEDIAN_AVG(int);
+ break;
+ case TYPE_lng:
+ ANALYTICAL_MEDIAN_AVG(lng);
+ break;
+#ifdef HAVE_HGE
+ case TYPE_hge:
+ ANALYTICAL_MEDIAN_AVG(hge);
+ break;
+#endif
+ case TYPE_flt:
+ ANALYTICAL_MEDIAN_AVG(flt);
+ break;
+ case TYPE_dbl:
+ ANALYTICAL_MEDIAN_AVG(dbl);
+ break;
+ default:
+ GDKerror("GDKanalytical_median_avg: average of type %s
unsupported.\n", ATOMname(tpe));
+ return GDK_FAIL;
+ }
+ BATsetcount(r, cnt);
+ r->tkey = BATcount(r) <= 1;
+ r->tsorted = BATcount(r) <= 1;
+ r->trevsorted = BATcount(r) <= 1;
+ r->tnonil = !has_nils;
+ r->tnil = has_nils;
+ 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
@@ -1471,12 +1471,12 @@ SQLprod(Client cntxt, MalBlkPtr mb, MalS
GDKanalyticalprod);
}
-str
-SQLavg(Client cntxt, MalBlkPtr mb, MalStkPtr stk, InstrPtr pci)
+static str
+do_fp_window(Client cntxt, MalBlkPtr mb, MalStkPtr stk, InstrPtr pci, const
char *op, const char* err,
+ gdk_return (*func)(BAT *, BAT *, BAT *, BAT *, int))
{
BAT *r, *b, *s, *e;
- str msg = SQLanalytics_args(&r, &b, &s, &e, cntxt, mb, stk, pci,
TYPE_dbl, "sql.avg",
- SQLSTATE(42000)
"avg(: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;
@@ -1488,14 +1488,14 @@ SQLavg(Client cntxt, MalBlkPtr mb, MalSt
if (b) {
bat *res = getArgReference_bat(stk, pci, 0);
- gdk_res = GDKanalyticalavg(r, b, s, e, tpe);
+ gdk_res = func(r, b, s, e, tpe);
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.avg", GDK_EXCEPTION);
+ throw(SQL, op, GDK_EXCEPTION);
} else {
ptr *res = getArgReference(stk, pci, 0);
ptr *in = getArgReference(stk, pci, 1);
@@ -1531,12 +1531,25 @@ SQLavg(Client cntxt, MalBlkPtr mb, MalSt
*res = *in;
break;
default:
- throw(SQL, "sql.avg", SQLSTATE(42000) "sql.avg
not available for %s", ATOMname(tpe));
+ throw(SQL, op, SQLSTATE(42000) "%s not
available for %s", op, ATOMname(tpe));
}
}
return msg;
}
+str
+SQLavg(Client cntxt, MalBlkPtr mb, MalStkPtr stk, InstrPtr pci)
+{
+ return do_fp_window(cntxt, mb, stk, pci, "sql.avg", SQLSTATE(42000)
"avg(:any_1,:lng,:lng)", GDKanalyticalavg);
+}
+
+str
+SQLmedian_avg(Client cntxt, MalBlkPtr mb, MalStkPtr stk, InstrPtr pci)
+{
+ return do_fp_window(cntxt, mb, stk, pci, "sql.median_avg",
SQLSTATE(42000) "median_avg(:any_1,:lng,:lng)",
+ GDKanalytical_median_avg);
+}
+
static str
do_stddev_and_variance(Client cntxt, MalBlkPtr mb, MalStkPtr stk, InstrPtr
pci, const char* op, const char* err,
gdk_return (*func)(BAT *, BAT *, BAT
*, BAT *, int))
@@ -1615,5 +1628,6 @@ SQLvar_pop(Client cntxt, MalBlkPtr mb, M
str
SQLmedian(Client cntxt, MalBlkPtr mb, MalStkPtr stk, InstrPtr pci)
{
- return SQLanalytical_func(cntxt, mb, stk, pci, "sql.median",
SQLSTATE(42000) "median(:any_1,:lng,:lng)", GDKanalytical_median);
+ return SQLanalytical_func(cntxt, mb, stk, pci, "sql.median",
SQLSTATE(42000) "median(:any_1,:lng,:lng)",
+ GDKanalytical_median);
}
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
@@ -41,5 +41,6 @@ sql5_export str SQLstddev_pop(Client cnt
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);
sql5_export str SQLmedian(Client cntxt, MalBlkPtr mb, MalStkPtr stk, InstrPtr
pci);
+sql5_export str SQLmedian_avg(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
@@ -575,6 +575,15 @@ pattern batsql.variancep(b:bat[:bte], s:
address SQLvar_pop
comment "return the variance of groups";
+
+pattern sql.median_avg(b:bte, s:lng, e:lng) :dbl
+address SQLmedian_avg
+comment "return the median value of groups with average in case of 2 values in
the median";
+
+pattern batsql.median_avg(b:bat[:bte], s:bat[:lng], e:bat[:lng]) :bat[:dbl]
+address SQLmedian_avg
+comment "return the median value of groups with average in case of 2 values in
the median";
+
pattern sql.avg(b:sht, s:lng, e:lng) :dbl
address SQLavg
comment "return the average of groups";
@@ -617,6 +626,15 @@ pattern batsql.variancep(b:bat[:sht], s:
address SQLvar_pop
comment "return the variance of groups";
+
+pattern sql.median_avg(b:sht, s:lng, e:lng) :dbl
+address SQLmedian_avg
+comment "return the median value of groups with average in case of 2 values in
the median";
+
+pattern batsql.median_avg(b:bat[:sht], s:bat[:lng], e:bat[:lng]) :bat[:dbl]
+address SQLmedian_avg
+comment "return the median value of groups with average in case of 2 values in
the median";
+
pattern sql.avg(b:int, s:lng, e:lng) :dbl
address SQLavg
comment "return the average of groups";
@@ -659,6 +677,15 @@ pattern batsql.variancep(b:bat[:int], s:
address SQLvar_pop
comment "return the variance of groups";
+
+pattern sql.median_avg(b:int, s:lng, e:lng) :dbl
+address SQLmedian_avg
+comment "return the median value of groups with average in case of 2 values in
the median";
+
+pattern batsql.median_avg(b:bat[:int], s:bat[:lng], e:bat[:lng]) :bat[:dbl]
+address SQLmedian_avg
+comment "return the median value of groups with average in case of 2 values in
the median";
+
pattern sql.avg(b:lng, s:lng, e:lng) :dbl
address SQLavg
comment "return the average of groups";
@@ -701,6 +728,15 @@ pattern batsql.variancep(b:bat[:lng], s:
address SQLvar_pop
comment "return the variance of groups";
+
+pattern sql.median_avg(b:lng, s:lng, e:lng) :dbl
+address SQLmedian_avg
+comment "return the median value of groups with average in case of 2 values in
the median";
+
+pattern batsql.median_avg(b:bat[:lng], s:bat[:lng], e:bat[:lng]) :bat[:dbl]
+address SQLmedian_avg
+comment "return the median value of groups with average in case of 2 values in
the median";
+
pattern sql.avg(b:flt, s:lng, e:lng) :dbl
address SQLavg
comment "return the average of groups";
@@ -743,6 +779,15 @@ pattern batsql.variancep(b:bat[:flt], s:
address SQLvar_pop
comment "return the variance of groups";
+
+pattern sql.median_avg(b:flt, s:lng, e:lng) :dbl
+address SQLmedian_avg
+comment "return the median value of groups with average in case of 2 values in
the median";
+
+pattern batsql.median_avg(b:bat[:flt], s:bat[:lng], e:bat[:lng]) :bat[:dbl]
+address SQLmedian_avg
+comment "return the median value of groups with average in case of 2 values in
the median";
+
pattern sql.avg(b:dbl, s:lng, e:lng) :dbl
address SQLavg
comment "return the average of groups";
@@ -785,6 +830,15 @@ pattern batsql.variancep(b:bat[:dbl], s:
address SQLvar_pop
comment "return the variance of groups";
+
+pattern sql.median_avg(b:dbl, s:lng, e:lng) :dbl
+address SQLmedian_avg
+comment "return the median value of groups with average in case of 2 values in
the median";
+
+pattern batsql.median_avg(b:bat[:dbl], s:bat[:lng], e:bat[:lng]) :bat[:dbl]
+address SQLmedian_avg
+comment "return the median value of groups with average in case of 2 values in
the median";
+
pattern sql.median(b:any_1, s:lng, e:lng) :any_1
address SQLmedian
comment "return the median value of groups";
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
@@ -367,49 +367,58 @@ EOF
done
done
-for tp1 in 1:bte 2:sht 4:int 8:lng 4:flt 8:dbl; do
+for tp in bte sht int lng flt dbl; do
cat <<EOF
-pattern sql.avg(b:${tp1#*:}, s:lng, e:lng) :dbl
+pattern sql.avg(b:${tp}, s:lng, e:lng) :dbl
address SQLavg
comment "return the average of groups";
-pattern batsql.avg(b:bat[:${tp1#*:}], s:bat[:lng], e:bat[:lng]) :bat[:dbl]
+pattern batsql.avg(b:bat[:${tp}], s:bat[:lng], e:bat[:lng]) :bat[:dbl]
address SQLavg
comment "return the average of groups";
-pattern sql.stdev(b:${tp1#*:}, s:lng, e:lng) :dbl
+pattern sql.stdev(b:${tp}, s:lng, e:lng) :dbl
address SQLstddev_samp
comment "return the standard deviation of groups";
-pattern batsql.stdev(b:bat[:${tp1#*:}], s:bat[:lng], e:bat[:lng]) :bat[:dbl]
_______________________________________________
checkin-list mailing list
[email protected]
https://www.monetdb.org/mailman/listinfo/checkin-list
