Changeset: 9aef4b4bf037 for MonetDB
URL: https://dev.monetdb.org/hg/MonetDB?cmd=changeset;node=9aef4b4bf037
Modified Files:
clients/Tests/MAL-signatures.stable.out
clients/Tests/MAL-signatures.stable.out.int128
sql/backends/monet5/sql_aggr_bte.mal
sql/backends/monet5/sql_aggr_dbl.mal
sql/backends/monet5/sql_aggr_flt.mal
sql/backends/monet5/sql_aggr_hge.mal
sql/backends/monet5/sql_aggr_int.mal
sql/backends/monet5/sql_aggr_lng.mal
sql/backends/monet5/sql_aggr_sht.mal
sql/backends/monet5/sql_upgrades.c
sql/scripts/39_analytics.sql
sql/scripts/39_analytics_hge.sql
sql/test/BugTracker-2016/Tests/subcorr-missing.Bug-3978.sql
sql/test/BugTracker-2016/Tests/subcorr-missing.Bug-3978.stable.out
sql/test/Tests/systemfunctions.stable.out
sql/test/Tests/systemfunctions.stable.out.int128
sql/test/emptydb/Tests/check.stable.out
sql/test/emptydb/Tests/check.stable.out.32bit
sql/test/emptydb/Tests/check.stable.out.int128
Branch: default
Log Message:
fixed and revised (MAL-)implementation of (sub)covar() & (sub)corr():
- always produce result of type dbl (nothing else makes sense)
- use correct types internally to avoid overflows (as much as possible)
- use avg() instead of sum()/count() to avoid overflows (as much as possible)
and to properly respect / handle NIL/NULL values
- correctly handle "mutual" NIL/NULL values, i.e., only consider
values from either column in case both column values are not NIL/NULL
- correctly return NIL/NULL as corr() in case at least one stddev() is 0
diffs (truncated from 1014 to 300 lines):
diff --git a/clients/Tests/MAL-signatures.stable.out
b/clients/Tests/MAL-signatures.stable.out
--- a/clients/Tests/MAL-signatures.stable.out
+++ b/clients/Tests/MAL-signatures.stable.out
@@ -75,24 +75,24 @@ Ready.
[ "aggr", "avg", "command aggr.avg(b:bat[:sht], g:bat[:oid],
e:bat[:any_1]) (X_0:bat[:dbl], X_1:bat[:lng]) ", "AGGRavg23_dbl;",
"Grouped tail average on sht, also returns count" ]
[ "aggr", "avg", "pattern aggr.avg(b:bat[:any_2]):dbl ", "CMDcalcavg;",
"Gives the avg of all tail values" ]
[ "aggr", "cardinality", "command aggr.cardinality(b:bat[:any_2]):lng ",
"ALGcard;", "Return the cardinality of the BAT tail values." ]
-[ "aggr", "corr", "function aggr.corr(e1:bat[:bte], e2:bat[:bte]):bte;",
"", "" ]
+[ "aggr", "corr", "function aggr.corr(e1:bat[:bte], e2:bat[:bte]):dbl;",
"", "" ]
[ "aggr", "corr", "function aggr.corr(e1:bat[:dbl], e2:bat[:dbl]):dbl;",
"", "" ]
-[ "aggr", "corr", "function aggr.corr(e1:bat[:flt], e2:bat[:flt]):flt;",
"", "" ]
-[ "aggr", "corr", "function aggr.corr(e1:bat[:int], e2:bat[:int]):int;",
"", "" ]
-[ "aggr", "corr", "function aggr.corr(e1:bat[:lng], e2:bat[:lng]):lng;",
"", "" ]
-[ "aggr", "corr", "function aggr.corr(e1:bat[:sht], e2:bat[:sht]):sht;",
"", "" ]
+[ "aggr", "corr", "function aggr.corr(e1:bat[:flt], e2:bat[:flt]):dbl;",
"", "" ]
+[ "aggr", "corr", "function aggr.corr(e1:bat[:int], e2:bat[:int]):dbl;",
"", "" ]
+[ "aggr", "corr", "function aggr.corr(e1:bat[:lng], e2:bat[:lng]):dbl;",
"", "" ]
+[ "aggr", "corr", "function aggr.corr(e1:bat[:sht], e2:bat[:sht]):dbl;",
"", "" ]
[ "aggr", "count", "command aggr.count(b:bat[:any_1], g:bat[:oid],
e:bat[:any_2], ignorenils:bit):bat[:lng] ", "AGGRcount3;", "" ]
[ "aggr", "count", "command aggr.count(b:bat[:any_1], g:bat[:oid],
e:bat[:any_2]):bat[:lng] ", "AGGRcount3nils;", "Grouped count" ]
[ "aggr", "count", "command aggr.count(b:bat[:any]):lng ",
"ALGcount_bat;", "Return the current size (in number of elements) in a
BAT." ]
[ "aggr", "count", "command aggr.count(b:bat[:any],
ignore_nils:bit):lng ", "ALGcount_nil;", "Return the number of
elements currently in a BAT ignores\n\t\tBUNs with nil-tail iff
ignore_nils==TRUE." ]
[ "aggr", "count_no_nil", "command aggr.count_no_nil(b:bat[:any_1],
g:bat[:oid], e:bat[:any_2]):bat[:lng] ", "AGGRcount3nonils;", "" ]
[ "aggr", "count_no_nil", "command aggr.count_no_nil(b:bat[:any_2]):lng
", "ALGcount_no_nil;", "Return the number of elements
currently\n\tin a BAT ignoring BUNs with nil-tail" ]
-[ "aggr", "covar", "function aggr.covar(e1:bat[:bte],
e2:bat[:bte]):bte;", "", "" ]
+[ "aggr", "covar", "function aggr.covar(e1:bat[:bte],
e2:bat[:bte]):dbl;", "", "" ]
[ "aggr", "covar", "function aggr.covar(e1:bat[:dbl],
e2:bat[:dbl]):dbl;", "", "" ]
-[ "aggr", "covar", "function aggr.covar(e1:bat[:flt],
e2:bat[:flt]):flt;", "", "" ]
-[ "aggr", "covar", "function aggr.covar(e1:bat[:int],
e2:bat[:int]):int;", "", "" ]
-[ "aggr", "covar", "function aggr.covar(e1:bat[:lng],
e2:bat[:lng]):lng;", "", "" ]
-[ "aggr", "covar", "function aggr.covar(e1:bat[:sht],
e2:bat[:sht]):sht;", "", "" ]
+[ "aggr", "covar", "function aggr.covar(e1:bat[:flt],
e2:bat[:flt]):dbl;", "", "" ]
+[ "aggr", "covar", "function aggr.covar(e1:bat[:int],
e2:bat[:int]):dbl;", "", "" ]
+[ "aggr", "covar", "function aggr.covar(e1:bat[:lng],
e2:bat[:lng]):dbl;", "", "" ]
+[ "aggr", "covar", "function aggr.covar(e1:bat[:sht],
e2:bat[:sht]):dbl;", "", "" ]
[ "aggr", "exist", "command aggr.exist(b:bat[:any_2], h:any_1):bit
", "ALGexist;", "" ]
[ "aggr", "exist", "command aggr.exist(b:bat[:any_2]):bit ",
"SQLexist;", "" ]
[ "aggr", "exist", "pattern aggr.exist(v:any_2):bit ",
"SQLexist_val;", "" ]
@@ -225,20 +225,20 @@ Ready.
[ "aggr", "subavg", "command aggr.subavg(b:bat[:int], g:bat[:oid],
e:bat[:any_1], s:bat[:oid], skip_nils:bit, abort_on_error:bit) (X_0:bat[:dbl],
X_1:bat[:lng]) ", "AGGRsubavg2cand_dbl;", "Grouped average aggregate with
candidates list, also returns count" ]
[ "aggr", "subavg", "command aggr.subavg(b:bat[:lng], g:bat[:oid],
e:bat[:any_1], s:bat[:oid], skip_nils:bit, abort_on_error:bit) (X_0:bat[:dbl],
X_1:bat[:lng]) ", "AGGRsubavg2cand_dbl;", "Grouped average aggregate with
candidates list, also returns count" ]
[ "aggr", "subavg", "command aggr.subavg(b:bat[:sht], g:bat[:oid],
e:bat[:any_1], s:bat[:oid], skip_nils:bit, abort_on_error:bit) (X_0:bat[:dbl],
X_1:bat[:lng]) ", "AGGRsubavg2cand_dbl;", "Grouped average aggregate with
candidates list, also returns count" ]
-[ "aggr", "subcorr", "function aggr.subcorr(e1:bat[:bte],
e2:bat[:bte], g:bat[:oid], e:bat[:any_2], f:bit):bat[:bte];", "", ""
]
+[ "aggr", "subcorr", "function aggr.subcorr(e1:bat[:bte],
e2:bat[:bte], g:bat[:oid], e:bat[:any_2], f:bit):bat[:dbl];", "", ""
]
[ "aggr", "subcorr", "function aggr.subcorr(e1:bat[:dbl],
e2:bat[:dbl], g:bat[:oid], e:bat[:any_2], f:bit):bat[:dbl];", "", ""
]
-[ "aggr", "subcorr", "function aggr.subcorr(e1:bat[:flt],
e2:bat[:flt], g:bat[:oid], e:bat[:any_2], f:bit):bat[:flt];", "", ""
]
-[ "aggr", "subcorr", "function aggr.subcorr(e1:bat[:int],
e2:bat[:int], g:bat[:oid], e:bat[:any_2], f:bit):bat[:int];", "", ""
]
-[ "aggr", "subcorr", "function aggr.subcorr(e1:bat[:lng],
e2:bat[:lng], g:bat[:oid], e:bat[:any_2], f:bit):bat[:lng];", "", ""
]
-[ "aggr", "subcorr", "function aggr.subcorr(e1:bat[:sht],
e2:bat[:sht], g:bat[:oid], e:bat[:any_2], f:bit):bat[:sht];", "", ""
]
+[ "aggr", "subcorr", "function aggr.subcorr(e1:bat[:flt],
e2:bat[:flt], g:bat[:oid], e:bat[:any_2], f:bit):bat[:dbl];", "", ""
]
+[ "aggr", "subcorr", "function aggr.subcorr(e1:bat[:int],
e2:bat[:int], g:bat[:oid], e:bat[:any_2], f:bit):bat[:dbl];", "", ""
]
+[ "aggr", "subcorr", "function aggr.subcorr(e1:bat[:lng],
e2:bat[:lng], g:bat[:oid], e:bat[:any_2], f:bit):bat[:dbl];", "", ""
]
+[ "aggr", "subcorr", "function aggr.subcorr(e1:bat[:sht],
e2:bat[:sht], g:bat[:oid], e:bat[:any_2], f:bit):bat[:dbl];", "", ""
]
[ "aggr", "subcount", "command aggr.subcount(b:bat[:any_1],
g:bat[:oid], e:bat[:any_2], skip_nils:bit):bat[:lng] ", "AGGRsubcount;",
"Grouped count aggregate" ]
[ "aggr", "subcount", "command aggr.subcount(b:bat[:any_1],
g:bat[:oid], e:bat[:any_2], s:bat[:oid], skip_nils:bit):bat[:lng] ",
"AGGRsubcountcand;", "Grouped count aggregate with candidates list" ]
-[ "aggr", "subcovar", "function aggr.subcovar(e1:bat[:bte],
e2:bat[:bte], g:bat[:oid], e:bat[:any_2], f:bit):bat[:bte];", "", ""
]
+[ "aggr", "subcovar", "function aggr.subcovar(e1:bat[:bte],
e2:bat[:bte], g:bat[:oid], e:bat[:any_2], f:bit):bat[:dbl];", "", ""
]
[ "aggr", "subcovar", "function aggr.subcovar(e1:bat[:dbl],
e2:bat[:dbl], g:bat[:oid], e:bat[:any_2], f:bit):bat[:dbl];", "", ""
]
-[ "aggr", "subcovar", "function aggr.subcovar(e1:bat[:flt],
e2:bat[:flt], g:bat[:oid], e:bat[:any_2], f:bit):bat[:flt];", "", ""
]
-[ "aggr", "subcovar", "function aggr.subcovar(e1:bat[:int],
e2:bat[:int], g:bat[:oid], e:bat[:any_2], f:bit):bat[:int];", "", ""
]
-[ "aggr", "subcovar", "function aggr.subcovar(e1:bat[:lng],
e2:bat[:lng], g:bat[:oid], e:bat[:any_2], f:bit):bat[:lng];", "", ""
]
-[ "aggr", "subcovar", "function aggr.subcovar(e1:bat[:sht],
e2:bat[:sht], g:bat[:oid], e:bat[:any_2], f:bit):bat[:sht];", "", ""
]
+[ "aggr", "subcovar", "function aggr.subcovar(e1:bat[:flt],
e2:bat[:flt], g:bat[:oid], e:bat[:any_2], f:bit):bat[:dbl];", "", ""
]
+[ "aggr", "subcovar", "function aggr.subcovar(e1:bat[:int],
e2:bat[:int], g:bat[:oid], e:bat[:any_2], f:bit):bat[:dbl];", "", ""
]
+[ "aggr", "subcovar", "function aggr.subcovar(e1:bat[:lng],
e2:bat[:lng], g:bat[:oid], e:bat[:any_2], f:bit):bat[:dbl];", "", ""
]
+[ "aggr", "subcovar", "function aggr.subcovar(e1:bat[:sht],
e2:bat[:sht], g:bat[:oid], e:bat[:any_2], f:bit):bat[:dbl];", "", ""
]
[ "aggr", "subjsonaggr", "command aggr.subjsonaggr(val:bat[:dbl],
g:bat[:oid], e:bat[:any_1], skip_nils:bit):bat[:str] ", "JSONsubjson;",
"Grouped aggregation of values." ]
[ "aggr", "subjsonaggr", "command aggr.subjsonaggr(val:bat[:str],
g:bat[:oid], e:bat[:any_1], skip_nils:bit):bat[:str] ", "JSONsubjson;",
"Grouped aggregation of values." ]
[ "aggr", "subjsonaggr", "command aggr.subjsonaggr(val:bat[:dbl],
g:bat[:oid], e:bat[:any_1], s:bat[:oid], skip_nils:bit):bat[:str] ",
"JSONsubjsoncand;", "Grouped aggregation of values with candidates list."
]
diff --git a/clients/Tests/MAL-signatures.stable.out.int128
b/clients/Tests/MAL-signatures.stable.out.int128
--- a/clients/Tests/MAL-signatures.stable.out.int128
+++ b/clients/Tests/MAL-signatures.stable.out.int128
@@ -81,26 +81,26 @@ Ready.
[ "aggr", "avg", "command aggr.avg(b:bat[:sht], g:bat[:oid],
e:bat[:any_1]) (X_0:bat[:dbl], X_1:bat[:lng]) ", "AGGRavg23_dbl;",
"Grouped tail average on sht, also returns count" ]
[ "aggr", "avg", "pattern aggr.avg(b:bat[:any_2]):dbl ", "CMDcalcavg;",
"Gives the avg of all tail values" ]
[ "aggr", "cardinality", "command aggr.cardinality(b:bat[:any_2]):lng ",
"ALGcard;", "Return the cardinality of the BAT tail values." ]
-[ "aggr", "corr", "function aggr.corr(e1:bat[:bte], e2:bat[:bte]):bte;",
"", "" ]
+[ "aggr", "corr", "function aggr.corr(e1:bat[:bte], e2:bat[:bte]):dbl;",
"", "" ]
[ "aggr", "corr", "function aggr.corr(e1:bat[:dbl], e2:bat[:dbl]):dbl;",
"", "" ]
-[ "aggr", "corr", "function aggr.corr(e1:bat[:flt], e2:bat[:flt]):flt;",
"", "" ]
-[ "aggr", "corr", "function aggr.corr(e1:bat[:hge], e2:bat[:hge]):hge;",
"", "" ]
-[ "aggr", "corr", "function aggr.corr(e1:bat[:int], e2:bat[:int]):int;",
"", "" ]
-[ "aggr", "corr", "function aggr.corr(e1:bat[:lng], e2:bat[:lng]):lng;",
"", "" ]
-[ "aggr", "corr", "function aggr.corr(e1:bat[:sht], e2:bat[:sht]):sht;",
"", "" ]
+[ "aggr", "corr", "function aggr.corr(e1:bat[:flt], e2:bat[:flt]):dbl;",
"", "" ]
+[ "aggr", "corr", "function aggr.corr(e1:bat[:hge], e2:bat[:hge]):dbl;",
"", "" ]
+[ "aggr", "corr", "function aggr.corr(e1:bat[:int], e2:bat[:int]):dbl;",
"", "" ]
+[ "aggr", "corr", "function aggr.corr(e1:bat[:lng], e2:bat[:lng]):dbl;",
"", "" ]
+[ "aggr", "corr", "function aggr.corr(e1:bat[:sht], e2:bat[:sht]):dbl;",
"", "" ]
[ "aggr", "count", "command aggr.count(b:bat[:any_1], g:bat[:oid],
e:bat[:any_2], ignorenils:bit):bat[:lng] ", "AGGRcount3;", "" ]
[ "aggr", "count", "command aggr.count(b:bat[:any_1], g:bat[:oid],
e:bat[:any_2]):bat[:lng] ", "AGGRcount3nils;", "Grouped count" ]
[ "aggr", "count", "command aggr.count(b:bat[:any]):lng ",
"ALGcount_bat;", "Return the current size (in number of elements) in a
BAT." ]
[ "aggr", "count", "command aggr.count(b:bat[:any],
ignore_nils:bit):lng ", "ALGcount_nil;", "Return the number of
elements currently in a BAT ignores\n\t\tBUNs with nil-tail iff
ignore_nils==TRUE." ]
[ "aggr", "count_no_nil", "command aggr.count_no_nil(b:bat[:any_1],
g:bat[:oid], e:bat[:any_2]):bat[:lng] ", "AGGRcount3nonils;", "" ]
[ "aggr", "count_no_nil", "command aggr.count_no_nil(b:bat[:any_2]):lng
", "ALGcount_no_nil;", "Return the number of elements
currently\n\tin a BAT ignoring BUNs with nil-tail" ]
-[ "aggr", "covar", "function aggr.covar(e1:bat[:bte],
e2:bat[:bte]):bte;", "", "" ]
+[ "aggr", "covar", "function aggr.covar(e1:bat[:bte],
e2:bat[:bte]):dbl;", "", "" ]
[ "aggr", "covar", "function aggr.covar(e1:bat[:dbl],
e2:bat[:dbl]):dbl;", "", "" ]
-[ "aggr", "covar", "function aggr.covar(e1:bat[:flt],
e2:bat[:flt]):flt;", "", "" ]
-[ "aggr", "covar", "function aggr.covar(e1:bat[:hge],
e2:bat[:hge]):hge;", "", "" ]
-[ "aggr", "covar", "function aggr.covar(e1:bat[:int],
e2:bat[:int]):int;", "", "" ]
-[ "aggr", "covar", "function aggr.covar(e1:bat[:lng],
e2:bat[:lng]):lng;", "", "" ]
-[ "aggr", "covar", "function aggr.covar(e1:bat[:sht],
e2:bat[:sht]):sht;", "", "" ]
+[ "aggr", "covar", "function aggr.covar(e1:bat[:flt],
e2:bat[:flt]):dbl;", "", "" ]
+[ "aggr", "covar", "function aggr.covar(e1:bat[:hge],
e2:bat[:hge]):dbl;", "", "" ]
+[ "aggr", "covar", "function aggr.covar(e1:bat[:int],
e2:bat[:int]):dbl;", "", "" ]
+[ "aggr", "covar", "function aggr.covar(e1:bat[:lng],
e2:bat[:lng]):dbl;", "", "" ]
+[ "aggr", "covar", "function aggr.covar(e1:bat[:sht],
e2:bat[:sht]):dbl;", "", "" ]
[ "aggr", "exist", "command aggr.exist(b:bat[:any_2], h:any_1):bit
", "ALGexist;", "" ]
[ "aggr", "exist", "command aggr.exist(b:bat[:any_2]):bit ",
"SQLexist;", "" ]
[ "aggr", "exist", "pattern aggr.exist(v:any_2):bit ",
"SQLexist_val;", "" ]
@@ -268,22 +268,22 @@ Ready.
[ "aggr", "subavg", "command aggr.subavg(b:bat[:int], g:bat[:oid],
e:bat[:any_1], s:bat[:oid], skip_nils:bit, abort_on_error:bit) (X_0:bat[:dbl],
X_1:bat[:lng]) ", "AGGRsubavg2cand_dbl;", "Grouped average aggregate with
candidates list, also returns count" ]
[ "aggr", "subavg", "command aggr.subavg(b:bat[:lng], g:bat[:oid],
e:bat[:any_1], s:bat[:oid], skip_nils:bit, abort_on_error:bit) (X_0:bat[:dbl],
X_1:bat[:lng]) ", "AGGRsubavg2cand_dbl;", "Grouped average aggregate with
candidates list, also returns count" ]
[ "aggr", "subavg", "command aggr.subavg(b:bat[:sht], g:bat[:oid],
e:bat[:any_1], s:bat[:oid], skip_nils:bit, abort_on_error:bit) (X_0:bat[:dbl],
X_1:bat[:lng]) ", "AGGRsubavg2cand_dbl;", "Grouped average aggregate with
candidates list, also returns count" ]
-[ "aggr", "subcorr", "function aggr.subcorr(e1:bat[:bte],
e2:bat[:bte], g:bat[:oid], e:bat[:any_2], f:bit):bat[:bte];", "", ""
]
+[ "aggr", "subcorr", "function aggr.subcorr(e1:bat[:bte],
e2:bat[:bte], g:bat[:oid], e:bat[:any_2], f:bit):bat[:dbl];", "", ""
]
[ "aggr", "subcorr", "function aggr.subcorr(e1:bat[:dbl],
e2:bat[:dbl], g:bat[:oid], e:bat[:any_2], f:bit):bat[:dbl];", "", ""
]
-[ "aggr", "subcorr", "function aggr.subcorr(e1:bat[:flt],
e2:bat[:flt], g:bat[:oid], e:bat[:any_2], f:bit):bat[:flt];", "", ""
]
-[ "aggr", "subcorr", "function aggr.subcorr(e1:bat[:hge],
e2:bat[:hge], g:bat[:oid], e:bat[:any_2], f:bit):bat[:hge];", "", ""
]
-[ "aggr", "subcorr", "function aggr.subcorr(e1:bat[:int],
e2:bat[:int], g:bat[:oid], e:bat[:any_2], f:bit):bat[:int];", "", ""
]
-[ "aggr", "subcorr", "function aggr.subcorr(e1:bat[:lng],
e2:bat[:lng], g:bat[:oid], e:bat[:any_2], f:bit):bat[:lng];", "", ""
]
-[ "aggr", "subcorr", "function aggr.subcorr(e1:bat[:sht],
e2:bat[:sht], g:bat[:oid], e:bat[:any_2], f:bit):bat[:sht];", "", ""
]
+[ "aggr", "subcorr", "function aggr.subcorr(e1:bat[:flt],
e2:bat[:flt], g:bat[:oid], e:bat[:any_2], f:bit):bat[:dbl];", "", ""
]
+[ "aggr", "subcorr", "function aggr.subcorr(e1:bat[:hge],
e2:bat[:hge], g:bat[:oid], e:bat[:any_2], f:bit):bat[:dbl];", "", ""
]
+[ "aggr", "subcorr", "function aggr.subcorr(e1:bat[:int],
e2:bat[:int], g:bat[:oid], e:bat[:any_2], f:bit):bat[:dbl];", "", ""
]
+[ "aggr", "subcorr", "function aggr.subcorr(e1:bat[:lng],
e2:bat[:lng], g:bat[:oid], e:bat[:any_2], f:bit):bat[:dbl];", "", ""
]
+[ "aggr", "subcorr", "function aggr.subcorr(e1:bat[:sht],
e2:bat[:sht], g:bat[:oid], e:bat[:any_2], f:bit):bat[:dbl];", "", ""
]
[ "aggr", "subcount", "command aggr.subcount(b:bat[:any_1],
g:bat[:oid], e:bat[:any_2], skip_nils:bit):bat[:lng] ", "AGGRsubcount;",
"Grouped count aggregate" ]
[ "aggr", "subcount", "command aggr.subcount(b:bat[:any_1],
g:bat[:oid], e:bat[:any_2], s:bat[:oid], skip_nils:bit):bat[:lng] ",
"AGGRsubcountcand;", "Grouped count aggregate with candidates list" ]
-[ "aggr", "subcovar", "function aggr.subcovar(e1:bat[:bte],
e2:bat[:bte], g:bat[:oid], e:bat[:any_2], f:bit):bat[:bte];", "", ""
]
+[ "aggr", "subcovar", "function aggr.subcovar(e1:bat[:bte],
e2:bat[:bte], g:bat[:oid], e:bat[:any_2], f:bit):bat[:dbl];", "", ""
]
[ "aggr", "subcovar", "function aggr.subcovar(e1:bat[:dbl],
e2:bat[:dbl], g:bat[:oid], e:bat[:any_2], f:bit):bat[:dbl];", "", ""
]
-[ "aggr", "subcovar", "function aggr.subcovar(e1:bat[:flt],
e2:bat[:flt], g:bat[:oid], e:bat[:any_2], f:bit):bat[:flt];", "", ""
]
-[ "aggr", "subcovar", "function aggr.subcovar(e1:bat[:hge],
e2:bat[:hge], g:bat[:oid], e:bat[:any_2], f:bit):bat[:hge];", "", ""
]
-[ "aggr", "subcovar", "function aggr.subcovar(e1:bat[:int],
e2:bat[:int], g:bat[:oid], e:bat[:any_2], f:bit):bat[:int];", "", ""
]
-[ "aggr", "subcovar", "function aggr.subcovar(e1:bat[:lng],
e2:bat[:lng], g:bat[:oid], e:bat[:any_2], f:bit):bat[:lng];", "", ""
]
-[ "aggr", "subcovar", "function aggr.subcovar(e1:bat[:sht],
e2:bat[:sht], g:bat[:oid], e:bat[:any_2], f:bit):bat[:sht];", "", ""
]
+[ "aggr", "subcovar", "function aggr.subcovar(e1:bat[:flt],
e2:bat[:flt], g:bat[:oid], e:bat[:any_2], f:bit):bat[:dbl];", "", ""
]
+[ "aggr", "subcovar", "function aggr.subcovar(e1:bat[:hge],
e2:bat[:hge], g:bat[:oid], e:bat[:any_2], f:bit):bat[:dbl];", "", ""
]
+[ "aggr", "subcovar", "function aggr.subcovar(e1:bat[:int],
e2:bat[:int], g:bat[:oid], e:bat[:any_2], f:bit):bat[:dbl];", "", ""
]
+[ "aggr", "subcovar", "function aggr.subcovar(e1:bat[:lng],
e2:bat[:lng], g:bat[:oid], e:bat[:any_2], f:bit):bat[:dbl];", "", ""
]
+[ "aggr", "subcovar", "function aggr.subcovar(e1:bat[:sht],
e2:bat[:sht], g:bat[:oid], e:bat[:any_2], f:bit):bat[:dbl];", "", ""
]
[ "aggr", "subjsonaggr", "command aggr.subjsonaggr(val:bat[:dbl],
g:bat[:oid], e:bat[:any_1], skip_nils:bit):bat[:str] ", "JSONsubjson;",
"Grouped aggregation of values." ]
[ "aggr", "subjsonaggr", "command aggr.subjsonaggr(val:bat[:str],
g:bat[:oid], e:bat[:any_1], skip_nils:bit):bat[:str] ", "JSONsubjson;",
"Grouped aggregation of values." ]
[ "aggr", "subjsonaggr", "command aggr.subjsonaggr(val:bat[:dbl],
g:bat[:oid], e:bat[:any_1], s:bat[:oid], skip_nils:bit):bat[:str] ",
"JSONsubjsoncand;", "Grouped aggregation of values with candidates list."
]
diff --git a/sql/backends/monet5/sql_aggr_bte.mal
b/sql/backends/monet5/sql_aggr_bte.mal
--- a/sql/backends/monet5/sql_aggr_bte.mal
+++ b/sql/backends/monet5/sql_aggr_bte.mal
@@ -4,52 +4,51 @@
#
# Copyright 1997 - July 2008 CWI, August 2008 - 2017 MonetDB B.V.
-function aggr.covar(e1:bat[:bte], e2:bat[:bte]):bte;
- e0:bat[:bte] := batcalc.*(e1,e2);
- s0:bte := aggr.sum(e0);
- s1:bte := aggr.sum(e1);
- s2:bte := aggr.sum(e2);
- v2:bte := calc.*(s1,s2);
- c := aggr.count(e1);
- n:bte := calc.bte(c);
- v3:bte := calc./(v2,n);
- v1:bte := calc.-(s0,v3);
- v:bte := calc./(v1,n);
+function aggr.covar(e1:bat[:bte], e2:bat[:bte]):dbl;
+ e0:bat[:sht] := batcalc.*(e1,e2);
+ s0:dbl := aggr.avg(e0);
+ # ignore "mutual" NILs:
+ e1n := algebra.select(e1, nil:bte,nil:bte,false,false,false);
+ e12n := algebra.select(e1,e1n,nil:bte,nil:bte,false,false,false);
+ s1:dbl := batcalc.avg(e1,e12n);
+ s2:dbl := batcalc.avg(e2,e12n);
+ s3:dbl := calc.*(s1,s2);
+ v:dbl := calc.-(s0,s3);
return v;
end aggr.covar;
-function aggr.corr(e1:bat[:bte], e2:bat[:bte]):bte;
- cv:bte := aggr.covar(e1,e2);
+function aggr.corr(e1:bat[:bte], e2:bat[:bte]):dbl;
+ cv := aggr.covar(e1,e2);
sd1 := aggr.stdev(e1);
sd2 := aggr.stdev(e2);
sd := calc.*(sd1,sd2);
- r := calc./(cv,sd);
- res := calc.bte(r);
+ sdn := calc.==(sd,0:dbl);
+ sds := calc.ifthenelse(sdn,nil:dbl,sd);
+ res := calc./(cv,sds);
return res;
end aggr.corr;
-function aggr.subcovar(e1:bat[:bte], e2:bat[:bte], g:bat[:oid], e:bat[:any_2],
f:bit):bat[:bte];
- e0:bat[:bte] := batcalc.*(e1,e2);
- s0:bat[:bte] := aggr.subsum(e0,g,e,f,true);
- s1:bat[:bte] := aggr.subsum(e1,g,e,f,true);
- s2:bat[:bte] := aggr.subsum(e2,g,e,f,true);
- v2:bat[:bte] := batcalc.*(s1,s2);
- c := aggr.subcount(e1,g,e,f);
- n:bat[:bte] := batcalc.bte(c);
- v3:bat[:bte] := batcalc./(v2,n);
- v1:bat[:bte] := batcalc.-(s0,v3);
- v:bat[:bte] := batcalc./(v1,n);
+function aggr.subcovar(e1:bat[:bte], e2:bat[:bte], g:bat[:oid], e:bat[:any_2],
f:bit):bat[:dbl];
+ e0:bat[:sht] := batcalc.*(e1,e2);
+ s0:bat[:dbl] := aggr.subavg(e0,g,e,f,true);
+ # ignore "mutual" NILs:
+ t := calc.not(f);
+ e1n := algebra.select(e1, nil:bte,nil:bte,t,t,false);
+ e12n := algebra.select(e1,e1n,nil:bte,nil:bte,t,t,false);
+ s1:bat[:dbl] := aggr.subavg(e1,g,e,e12n,f,true);
+ s2:bat[:dbl] := aggr.subavg(e2,g,e,e12n,f,true);
+ s3:bat[:dbl] := batcalc.*(s1,s2);
+ v:bat[:dbl] := batcalc.-(s0,s3);
return v;
end aggr.subcovar;
-function aggr.subcorr(e1:bat[:bte], e2:bat[:bte], g:bat[:oid],e:bat[:any_2],
f:bit):bat[:bte];
- cv:bat[:bte] := aggr.subcovar(e1,e2,g,e,f);
+function aggr.subcorr(e1:bat[:bte], e2:bat[:bte], g:bat[:oid],e:bat[:any_2],
f:bit):bat[:dbl];
+ cv := aggr.subcovar(e1,e2,g,e,f);
sd1 := aggr.substdevp(e1,g,e,f,true);
sd2 := aggr.substdevp(e2,g,e,f,true);
sd := batcalc.*(sd1,sd2);
- sdn := batcalc.==(sd,0:bte);
- sds := batcalc.ifthenelse(sdn,1:dbl,sd);
- r := batcalc./(cv,sds);
- res := batcalc.bte(r);
+ sdn := batcalc.==(sd,0:dbl);
+ sds := batcalc.ifthenelse(sdn,nil:dbl,sd);
+ res := batcalc./(cv,sds);
return res;
end aggr.subcorr;
diff --git a/sql/backends/monet5/sql_aggr_dbl.mal
b/sql/backends/monet5/sql_aggr_dbl.mal
--- a/sql/backends/monet5/sql_aggr_dbl.mal
+++ b/sql/backends/monet5/sql_aggr_dbl.mal
@@ -6,50 +6,49 @@
function aggr.covar(e1:bat[:dbl], e2:bat[:dbl]):dbl;
e0:bat[:dbl] := batcalc.*(e1,e2);
- s0:dbl := aggr.sum(e0);
- s1:dbl := aggr.sum(e1);
- s2:dbl := aggr.sum(e2);
- v2:dbl := calc.*(s1,s2);
- c := aggr.count(e1);
- n:dbl := calc.dbl(c);
- v3:dbl := calc./(v2,n);
- v1:dbl := calc.-(s0,v3);
- v:dbl := calc./(v1,n);
+ s0:dbl := aggr.avg(e0);
+ # ignore "mutual" NILs:
+ e1n := algebra.select(e1, nil:dbl,nil:dbl,false,false,false);
+ e12n := algebra.select(e1,e1n,nil:dbl,nil:dbl,false,false,false);
+ s1:dbl := batcalc.avg(e1,e12n);
+ s2:dbl := batcalc.avg(e2,e12n);
+ s3:dbl := calc.*(s1,s2);
+ v:dbl := calc.-(s0,s3);
return v;
end aggr.covar;
function aggr.corr(e1:bat[:dbl], e2:bat[:dbl]):dbl;
- cv:dbl := aggr.covar(e1,e2);
+ cv := aggr.covar(e1,e2);
sd1 := aggr.stdev(e1);
sd2 := aggr.stdev(e2);
sd := calc.*(sd1,sd2);
- r := calc./(cv,sd);
- res := calc.dbl(r);
+ sdn := calc.==(sd,0:dbl);
+ sds := calc.ifthenelse(sdn,nil:dbl,sd);
+ res := calc./(cv,sds);
return res;
end aggr.corr;
function aggr.subcovar(e1:bat[:dbl], e2:bat[:dbl], g:bat[:oid], e:bat[:any_2],
f:bit):bat[:dbl];
e0:bat[:dbl] := batcalc.*(e1,e2);
- s0:bat[:dbl] := aggr.subsum(e0,g,e,f,true);
- s1:bat[:dbl] := aggr.subsum(e1,g,e,f,true);
- s2:bat[:dbl] := aggr.subsum(e2,g,e,f,true);
- v2:bat[:dbl] := batcalc.*(s1,s2);
- c := aggr.subcount(e1,g,e,f);
- n:bat[:dbl] := batcalc.dbl(c);
- v3:bat[:dbl] := batcalc./(v2,n);
- v1:bat[:dbl] := batcalc.-(s0,v3);
- v:bat[:dbl] := batcalc./(v1,n);
+ s0:bat[:dbl] := aggr.subavg(e0,g,e,f,true);
+ # ignore "mutual" NILs:
+ t := calc.not(f);
+ e1n := algebra.select(e1, nil:dbl,nil:dbl,t,t,false);
+ e12n := algebra.select(e1,e1n,nil:dbl,nil:dbl,t,t,false);
+ s1:bat[:dbl] := aggr.subavg(e1,g,e,e12n,f,true);
+ s2:bat[:dbl] := aggr.subavg(e2,g,e,e12n,f,true);
+ s3:bat[:dbl] := batcalc.*(s1,s2);
+ v:bat[:dbl] := batcalc.-(s0,s3);
return v;
end aggr.subcovar;
function aggr.subcorr(e1:bat[:dbl], e2:bat[:dbl], g:bat[:oid],e:bat[:any_2],
f:bit):bat[:dbl];
- cv:bat[:dbl] := aggr.subcovar(e1,e2,g,e,f);
+ cv := aggr.subcovar(e1,e2,g,e,f);
_______________________________________________
checkin-list mailing list
[email protected]
https://www.monetdb.org/mailman/listinfo/checkin-list
