Changeset: e7d24060cb18 for MonetDB
URL: https://dev.monetdb.org/hg/MonetDB?cmd=changeset;node=e7d24060cb18
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_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 as a window function
diffs (truncated from 494 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
@@ -42,5 +42,6 @@ gdk_export gdk_return GDKanalytical_stdd
gdk_export gdk_return GDKanalytical_stddev_pop(BAT *r, BAT *b, BAT *s, BAT *e,
int tpe);
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);
#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
@@ -1804,3 +1804,95 @@ GDK_ANALYTICAL_STDEV_VARIANCE(stddev_sam
GDK_ANALYTICAL_STDEV_VARIANCE(stddev_pop, 0, sqrt(m2 / (n - 0)))
GDK_ANALYTICAL_STDEV_VARIANCE(variance_samp, 1, m2 / (n - 1))
GDK_ANALYTICAL_STDEV_VARIANCE(variance_pop, 0, m2 / (n - 0))
+
+/* There will be always at least one value for the median, because we don't
implement the exclude clause yet */
+#define ANALYTICAL_MEDIAN(TPE) \
+ do { \
+ TPE *restrict bp = (TPE*) Tloc(b, 0), *restrict rb = (TPE*)
Tloc(r, 0), v; \
+ for (; i < cnt; i++, rb++) { \
+ ss = (BUN) start[i]; \
+ ee = (BUN) end[i]; \
+ f = (ee - ss - 1) * 0.5f; \
+ qindex = ss + ee - (BUN) (ee + 0.5f - f); \
+ assert(qindex >= ss && qindex < ee); \
+ v = bp[qindex]; \
+ *rb = v; \
+ has_nils |= (v == TPE##_nil); \
+ } \
+ } while (0)
+
+gdk_return
+GDKanalytical_median(BAT *r, BAT *b, BAT *s, BAT *e, int tpe)
+{
+ bool has_nils = false;
+ BUN i = 0, cnt = BATcount(b), qindex, ss, ee;
+ lng *restrict start, *restrict end;
+ dbl f;
+
+ assert(s && e);
+ start = (lng *) Tloc(s, 0);
+ end = (lng *) Tloc(e, 0);
+
+ if (!ATOMlinear(tpe)) {
+ GDKerror("GDKanalytical_median: cannot determine quantile on "
+ "non-linear type %s\n", ATOMname(tpe));
+ return GDK_FAIL;
+ }
+
+ switch (tpe) {
+ case TYPE_bit:
+ ANALYTICAL_MEDIAN(bit);
+ break;
+ case TYPE_bte:
+ ANALYTICAL_MEDIAN(bte);
+ break;
+ case TYPE_sht:
+ ANALYTICAL_MEDIAN(sht);
+ break;
+ case TYPE_int:
+ ANALYTICAL_MEDIAN(int);
+ break;
+ case TYPE_lng:
+ ANALYTICAL_MEDIAN(lng);
+ break;
+#ifdef HAVE_HGE
+ case TYPE_hge:
+ ANALYTICAL_MEDIAN(hge);
+ break;
+#endif
+ case TYPE_flt:
+ ANALYTICAL_MEDIAN(flt);
+ break;
+ case TYPE_dbl:
+ ANALYTICAL_MEDIAN(dbl);
+ break;
+ default: {
+ BATiter bpi = bat_iterator(b);
+ const void *nil = ATOMnilptr(tpe);
+ int (*atomcmp)(const void *, const void *) = ATOMcompare(tpe);
+ void *curval;
+ for (; i < cnt; i++) {
+ ss = (BUN) start[i];
+ ee = (BUN) end[i];
+ f = (ee - ss - 1) * 0.5f;
+ qindex = ss + ee - (BUN) (ee + 0.5f - f);
+ assert(qindex >= ss && qindex < ee);
+ curval = BUNtail(bpi, qindex);
+ if (BUNappend(r, curval, false) != GDK_SUCCEED)
+ goto allocation_error;
+ if (atomcmp(curval, nil) == 0)
+ has_nils = true;
+ }
+ }
+ }
+ 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;
+allocation_error:
+ GDKerror("GDKanalytical_median: malloc failure\n");
+ return GDK_FAIL;
+}
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
@@ -1611,3 +1611,9 @@ SQLvar_pop(Client cntxt, MalBlkPtr mb, M
return do_stddev_and_variance(cntxt, mb, stk, pci, "sql.variancep",
SQLSTATE(42000) "variancep(:any_1,:lng,:lng)",
GDKanalytical_variance_pop);
}
+
+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);
+}
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
@@ -40,5 +40,6 @@ sql5_export str SQLstddev_samp(Client cn
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);
+sql5_export str SQLmedian(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
@@ -785,6 +785,14 @@ pattern batsql.variancep(b:bat[:dbl], s:
address SQLvar_pop
comment "return the variance of groups";
+pattern sql.median(b:any_1, s:lng, e:lng) :any_1
+address SQLmedian
+comment "return the median value of groups";
+
+pattern batsql.median(b:bat[:any_1], s:bat[:lng], e:bat[:lng]) :bat[:any_1]
+address SQLmedian
+comment "return the median value 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
@@ -414,6 +414,17 @@ comment "return the variance of groups";
EOF
done
+ cat <<EOF
+pattern sql.median(b:any_1, s:lng, e:lng) :any_1
+address SQLmedian
+comment "return the median value of groups";
+
+pattern batsql.median(b:bat[:any_1], s:bat[:lng], e:bat[:lng]) :bat[:any_1]
+address SQLmedian
+comment "return the median value of groups";
+
+EOF
+
cat <<EOF
command aggr.exist(b:bat[:any_2], h:any_1):bit
address ALGexist;
diff --git a/sql/backends/monet5/sql_upgrades.c
b/sql/backends/monet5/sql_upgrades.c
--- a/sql/backends/monet5/sql_upgrades.c
+++ b/sql/backends/monet5/sql_upgrades.c
@@ -2578,7 +2578,7 @@ sql_update_default(Client c, mvc *sql, c
static str
sql_update_analytics(Client c, mvc *sql, const char *prev_schema, bool
*systabfixed)
{
- size_t bufsize = 8192, pos = 0;
+ size_t bufsize = 16384, pos = 0;
char *err = NULL, *buf = GDKmalloc(bufsize);
if (!*systabfixed &&
@@ -2686,7 +2686,43 @@ sql_update_analytics(Client c, mvc *sql,
"GRANT EXECUTE ON WINDOW var_pop(INTERVAL SECOND) TO
PUBLIC;\n"
"create window var_pop(val INTERVAL MONTH) returns
DOUBLE\n"
" external name \"sql\".\"variancep\";\n"
- "GRANT EXECUTE ON WINDOW var_pop(INTERVAL MONTH) TO
PUBLIC;\n");
+ "GRANT EXECUTE ON WINDOW var_pop(INTERVAL MONTH) TO
PUBLIC;\n"
+ "create window median(val TINYINT) returns TINYINT\n"
+ " external name \"sql\".\"median\";\n"
+ "GRANT EXECUTE ON WINDOW median(TINYINT) TO PUBLIC;\n"
+ "create window median(val SMALLINT) returns SMALLINT\n"
+ " external name \"sql\".\"median\";\n"
+ "GRANT EXECUTE ON WINDOW median(SMALLINT) TO PUBLIC;\n"
+ "create window median(val INTEGER) returns INTEGER\n"
+ " external name \"sql\".\"median\";\n"
+ "GRANT EXECUTE ON WINDOW median(INTEGER) TO PUBLIC;\n"
+ "create window median(val BIGINT) returns BIGINT\n"
+ " external name \"sql\".\"median\";\n"
+ "GRANT EXECUTE ON WINDOW median(BIGINT) TO PUBLIC;\n"
+ "create window median(val DECIMAL) returns DECIMAL\n"
+ " external name \"sql\".\"median\";\n"
+ "GRANT EXECUTE ON WINDOW median(DECIMAL) TO PUBLIC;\n"
+ "create window median(val REAL) returns REAL\n"
+ " external name \"sql\".\"median\";\n"
+ "GRANT EXECUTE ON WINDOW median(REAL) TO PUBLIC;\n"
+ "create window median(val DOUBLE) returns DOUBLE\n"
+ " external name \"sql\".\"median\";\n"
+ "GRANT EXECUTE ON WINDOW median(DOUBLE) TO PUBLIC;\n"
+ "create window median(val DATE) returns DATE\n"
+ " external name \"sql\".\"median\";\n"
+ "GRANT EXECUTE ON WINDOW median(DATE) TO PUBLIC;\n"
+ "create window median(val TIME) returns TIME\n"
+ " external name \"sql\".\"median\";\n"
+ "GRANT EXECUTE ON WINDOW median(TIME) TO PUBLIC;\n"
+ "create window median(val TIMESTAMP) returns
TIMESTAMP\n"
+ " external name \"sql\".\"median\";\n"
+ "GRANT EXECUTE ON WINDOW median(TIMESTAMP) TO PUBLIC;\n"
+ "create window median(val INTERVAL SECOND) returns
INTERVAL SECOND\n"
+ " external name \"sql\".\"median\";\n"
+ "GRANT EXECUTE ON WINDOW median(INTERVAL SECOND) TO
PUBLIC;\n"
+ "create window median(val INTERVAL MONTH) returns
INTERVAL MONTH\n"
+ " external name \"sql\".\"median\";\n"
+ "GRANT EXECUTE ON WINDOW median(INTERVAL MONTH) TO
PUBLIC;\n");
#ifdef HAVE_HGE
if (have_hge) {
@@ -2703,12 +2739,15 @@ sql_update_analytics(Client c, mvc *sql,
"GRANT EXECUTE ON WINDOW var_samp(HUGEINT) TO PUBLIC;\n"
"create window var_pop(val HUGEINT) returns DOUBLE\n"
" external name \"sql\".\"variancep\";\n"
- "GRANT EXECUTE ON WINDOW var_pop(HUGEINT) TO
PUBLIC;\n");
+ "GRANT EXECUTE ON WINDOW var_pop(HUGEINT) TO PUBLIC;\n"
+ "create window median(val HUGEINT) returns HUGEINT;\n"
+ " external name \"sql\".\"median\";\n"
+ "GRANT EXECUTE ON WINDOW median(HUGEINT) TO PUBLIC;\n");
}
#endif
pos += snprintf(buf + pos, bufsize - pos,
- "update sys.functions set system = true where name in
('stddev_samp', 'stddev_pop', 'var_samp', 'var_pop')"
+ "update sys.functions set system = true where name in
('stddev_samp', 'stddev_pop', 'var_samp', 'var_pop', 'median')"
" and schema_id = (select id from sys.schemas where
name = 'sys');\n");
pos += snprintf(buf + pos, bufsize - pos,
diff --git a/sql/scripts/39_analytics.sql b/sql/scripts/39_analytics.sql
--- a/sql/scripts/39_analytics.sql
+++ b/sql/scripts/39_analytics.sql
@@ -253,6 +253,44 @@ create aggregate median(val INTERVAL MON
external name "aggr"."median";
GRANT EXECUTE ON AGGREGATE median(INTERVAL MONTH) TO PUBLIC;
+create window median(val TINYINT) returns TINYINT
+ external name "sql"."median";
+GRANT EXECUTE ON WINDOW median(TINYINT) TO PUBLIC;
+create window median(val SMALLINT) returns SMALLINT
+ external name "sql"."median";
+GRANT EXECUTE ON WINDOW median(SMALLINT) TO PUBLIC;
+create window median(val INTEGER) returns INTEGER
+ external name "sql"."median";
+GRANT EXECUTE ON WINDOW median(INTEGER) TO PUBLIC;
+create window median(val BIGINT) returns BIGINT
+ external name "sql"."median";
+GRANT EXECUTE ON WINDOW median(BIGINT) TO PUBLIC;
+create window median(val DECIMAL) returns DECIMAL
+ external name "sql"."median";
+GRANT EXECUTE ON WINDOW median(DECIMAL) TO PUBLIC;
+create window median(val REAL) returns REAL
+ external name "sql"."median";
+GRANT EXECUTE ON WINDOW median(REAL) TO PUBLIC;
+create window median(val DOUBLE) returns DOUBLE
+ external name "sql"."median";
+GRANT EXECUTE ON WINDOW median(DOUBLE) TO PUBLIC;
+
+create window median(val DATE) returns DATE
+ external name "sql"."median";
+GRANT EXECUTE ON WINDOW median(DATE) TO PUBLIC;
+create window median(val TIME) returns TIME
+ external name "sql"."median";
+GRANT EXECUTE ON WINDOW median(TIME) TO PUBLIC;
+create window median(val TIMESTAMP) returns TIMESTAMP
+ external name "sql"."median";
+GRANT EXECUTE ON WINDOW median(TIMESTAMP) TO PUBLIC;
+create window median(val INTERVAL SECOND) returns INTERVAL SECOND
+ external name "sql"."median";
+GRANT EXECUTE ON WINDOW median(INTERVAL SECOND) TO PUBLIC;
+create window median(val INTERVAL MONTH) returns INTERVAL MONTH
+ external name "sql"."median";
+GRANT EXECUTE ON WINDOW median(INTERVAL MONTH) TO PUBLIC;
+
create aggregate quantile(val TINYINT, q DOUBLE) returns TINYINT
external name "aggr"."quantile";
GRANT EXECUTE ON AGGREGATE quantile(TINYINT, DOUBLE) TO PUBLIC;
diff --git a/sql/scripts/39_analytics_hge.sql b/sql/scripts/39_analytics_hge.sql
--- a/sql/scripts/39_analytics_hge.sql
+++ b/sql/scripts/39_analytics_hge.sql
@@ -35,6 +35,9 @@ GRANT EXECUTE ON WINDOW var_pop(HUGEINT)
create aggregate median(val HUGEINT) returns HUGEINT
external name "aggr"."median";
_______________________________________________
checkin-list mailing list
[email protected]
https://www.monetdb.org/mailman/listinfo/checkin-list
