Changeset: 2abd89a2f10d for MonetDB
URL: https://dev.monetdb.org/hg/MonetDB?cmd=changeset;node=2abd89a2f10d
Added Files:
sql/test/analytics/Tests/analytics01.sql
sql/test/analytics/Tests/analytics01.stable.err
sql/test/analytics/Tests/analytics01.stable.out
Modified Files:
gdk/gdk_analytic.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/common/sql_types.c
sql/test/analytics/Tests/All
Branch: analytics
Log Message:
Implemented percent_rank analytic over a window.
diffs (truncated from 477 to 300 lines):
diff --git a/gdk/gdk_analytic.c b/gdk/gdk_analytic.c
--- a/gdk/gdk_analytic.c
+++ b/gdk/gdk_analytic.c
@@ -73,7 +73,7 @@ GDKanalyticaldiff(BAT *r, BAT *b, BAT *c
ptr v = BUNtail(it, 0), next;
atomcmp = ATOMcompare(tpe);
if(rp) {
- for (i = 0; i < cnt; i++, rp++) {
+ for (i=0; i<cnt; i++, rp++) {
*rb = *rp;
next = BUNtail(it, i);
if (atomcmp(v, next) != 0) {
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
@@ -280,6 +280,94 @@ SQLdense_rank(Client cntxt, MalBlkPtr mb
return MAL_SUCCEED;
}
+str
+SQLpercent_rank(Client cntxt, MalBlkPtr mb, MalStkPtr stk, InstrPtr pci)
+{
+ if (pci->argc != 4 ||
+ (getArgType(mb, pci, 2) != TYPE_bit &&
getBatType(getArgType(mb, pci, 2)) != TYPE_bit) ||
+ (getArgType(mb, pci, 3) != TYPE_bit &&
getBatType(getArgType(mb, pci, 3)) != TYPE_bit)){
+ throw(SQL, "sql.percent_rank", SQLSTATE(42000)
"percent_rank(:any_1,:bit,:bit)");
+ }
+ (void)cntxt;
+ if (isaBatType(getArgType(mb, pci, 1))) {
+ bat *res = getArgReference_bat(stk, pci, 0);
+ BAT *b = BATdescriptor(*getArgReference_bat(stk, pci, 1)), *p,
*o, *r;
+ int j, k, cnt;
+ dbl *rp, *end, cnt_cast;
+ bit *np, *no;
+
+ if (!b)
+ throw(SQL, "sql.percent_rank", SQLSTATE(HY005) "Cannot
access column descriptor");
+ cnt = (int)BATcount(b);
+ cnt_cast = (dbl) (cnt - 1);
+ voidresultBAT(r, TYPE_dbl, cnt, b, "sql.percent_rank");
+ rp = (dbl*)Tloc(r, 0);
+ end = rp + cnt;
+ if (isaBatType(getArgType(mb, pci, 2))) {
+ if (isaBatType(getArgType(mb, pci, 3))) {
+ p = BATdescriptor(*getArgReference_bat(stk,
pci, 2));
+ o = BATdescriptor(*getArgReference_bat(stk,
pci, 3));
+ if (!p || !o) {
+ BBPunfix(b->batCacheid);
+ if (p) BBPunfix(p->batCacheid);
+ if (o) BBPunfix(o->batCacheid);
+ throw(SQL, "sql.percent_rank",
SQLSTATE(HY005) "Cannot access column descriptor");
+ }
+ np = (bit*)Tloc(p, 0);
+ no = (bit*)Tloc(o, 0);
+ for(j=0,k=0; rp<end; k++, np++, no++, rp++) {
+ if (*np)
+ j=k=0;
+ if (*no)
+ j=k;
+ *rp = j / cnt_cast;
+ }
+ BBPunfix(p->batCacheid);
+ BBPunfix(o->batCacheid);
+ } else { /* single value, ie no ordering */
+ p = BATdescriptor(*getArgReference_bat(stk,
pci, 2));
+ if (!p) {
+ BBPunfix(b->batCacheid);
+ throw(SQL, "sql.percent_rank",
SQLSTATE(HY005) "Cannot access column descriptor");
+ }
+ np = (bit*)Tloc(p, 0);
+ for(j=0; rp<end; np++, rp++) {
+ if (*np)
+ j=0;
+ *rp = j / cnt_cast;
+ }
+ BBPunfix(p->batCacheid);
+ }
+ } else { /* single value, ie no partitions */
+ if (isaBatType(getArgType(mb, pci, 3))) {
+ o = BATdescriptor(*getArgReference_bat(stk,
pci, 3));
+ if (!o) {
+ BBPunfix(b->batCacheid);
+ throw(SQL, "sql.percent_rank",
SQLSTATE(HY005) "Cannot access column descriptor");
+ }
+ no = (bit*)Tloc(o, 0);
+ for(j=0,k=0; rp<end; k++, no++, rp++) {
+ if (*no)
+ j=k;
+ *rp = j / cnt_cast;
+ }
+ BBPunfix(o->batCacheid);
+ } else { /* single value, ie no ordering - the outcome
will always be 0 */
+ for(; rp<end; rp++)
+ *rp = 0;
+ }
+ }
+ BATsetcount(r, cnt);
+ BBPunfix(b->batCacheid);
+ BBPkeepref(*res = r->batCacheid);
+ } else {
+ int *res = getArgReference_int(stk, pci, 0);
+
+ *res = 1;
+ }
+ return MAL_SUCCEED;
+}
+
static str
SQLanalytics_args(BAT **r, BAT **b, BAT **p, BAT **o, Client cntxt, MalBlkPtr
mb, MalStkPtr stk, InstrPtr pci,
int rtype, const str mod, const str err)
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
@@ -15,6 +15,7 @@ sql5_export str SQLdiff(Client cntxt, Ma
sql5_export str SQLrow_number(Client cntxt, MalBlkPtr mb, MalStkPtr stk,
InstrPtr pci);
sql5_export str SQLrank(Client cntxt, MalBlkPtr mb, MalStkPtr stk, InstrPtr
pci);
sql5_export str SQLdense_rank(Client cntxt, MalBlkPtr mb, MalStkPtr stk,
InstrPtr pci);
+sql5_export str SQLpercent_rank(Client cntxt, MalBlkPtr mb, MalStkPtr stk,
InstrPtr pci);
sql5_export str SQLmin(Client cntxt, MalBlkPtr mb, MalStkPtr stk, InstrPtr
pci);
sql5_export str SQLmax(Client cntxt, MalBlkPtr mb, MalStkPtr stk, InstrPtr
pci);
sql5_export str SQLcount(Client cntxt, MalBlkPtr mb, MalStkPtr stk, InstrPtr
pci);
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
@@ -48,6 +48,14 @@ pattern batsql.dense_rank(b:bat[:any_1],
address SQLdense_rank
comment "return the densely ranked groups";
+pattern sql.percent_rank(b:any_1, p:bit, o:bit) :dbl
+address SQLpercent_rank
+comment "return the percentage into the total number of groups for each row";
+
+pattern batsql.percent_rank(b:bat[:any_1], p:any_2, o:any_3) :bat[:dbl]
+address SQLpercent_rank
+comment "return the percentage into the total number of groups for each row";
+
pattern sql.min(b:any_1, p:bit, o:bit, unit:int, s:int, e:int, excl:int) :any_1
address SQLmin
comment "return the minimum 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
@@ -54,6 +54,14 @@ pattern batsql.dense_rank(b:bat[:any_1],
address SQLdense_rank
comment "return the densely ranked groups";
+pattern sql.percent_rank(b:any_1, p:bit, o:bit) :dbl
+address SQLpercent_rank
+comment "return the percentage into the total number of groups for each row";
+
+pattern batsql.percent_rank(b:bat[:any_1], p:any_2, o:any_3) :bat[:dbl]
+address SQLpercent_rank
+comment "return the percentage into the total number of groups for each row";
+
pattern sql.min(b:any_1, p:bit, o:bit, unit:int, s:int, e:int, excl:int) :any_1
address SQLmin
comment "return the minimum of groups";
diff --git a/sql/common/sql_types.c b/sql/common/sql_types.c
--- a/sql/common/sql_types.c
+++ b/sql/common/sql_types.c
@@ -1566,13 +1566,16 @@ sqltypeinit( sql_allocator *sa)
sql_create_analytic(sa, "rank", "sql", "rank", ANY, BIT, BIT, INT,
SCALE_NONE);
sql_create_analytic(sa, "dense_rank", "sql", "dense_rank", ANY, BIT,
BIT, INT, SCALE_NONE);
sql_create_analytic(sa, "row_number", "sql", "row_number", ANY, BIT,
BIT, INT, SCALE_NONE);
+ sql_create_analytic(sa, "percent_rank", "sql", "percent_rank", ANY,
BIT, BIT, DBL, SCALE_NONE);
- //sql_create_analytic(sa, "percent_rank", "sql", "percent_rank", ANY,
BIT, BIT, INT, SCALE_NONE);
//sql_create_analytic(sa, "cume_dist", "sql", "cume_dist", ANY, BIT,
BIT, ANY, SCALE_NONE);
+ //sql_create_analytic(sa, "ntile", "sql", "ntile", ANY, BIT, BIT, ANY,
SCALE_NONE);
//sql_create_analytic(sa, "lag", "sql", "lag", ANY, BIT, BIT, ANY,
SCALE_NONE);
//sql_create_analytic(sa, "lead", "sql", "lead", ANY, BIT, BIT, ANY,
SCALE_NONE);
//sql_create_analytic(sa, "first_value", "sql", "first_value", ANY,
BIT, BIT, ANY, SCALE_NONE);
//sql_create_analytic(sa, "last_value", "sql", "last_value", ANY, BIT,
BIT, ANY, SCALE_NONE);
+ //sql_create_analytic(sa, "nth_value", "sql", "nth_value", ANY, BIT,
BIT, ANY, SCALE_NONE);
+
sql_create_analytic(sa, "count", "sql", "count", BIT, BIT, NULL, LNG,
SCALE_NONE);
sql_create_analytic(sa, "count", "sql", "count", ANY, BIT, BIT, LNG,
SCALE_NONE);
sql_create_analytic(sa, "min", "sql", "min", ANY, BIT, BIT, ANY,
SCALE_NONE);
diff --git a/sql/test/analytics/Tests/All b/sql/test/analytics/Tests/All
--- a/sql/test/analytics/Tests/All
+++ b/sql/test/analytics/Tests/All
@@ -1,1 +1,2 @@
analytics00
+analytics01
diff --git a/sql/test/analytics/Tests/analytics01.sql
b/sql/test/analytics/Tests/analytics01.sql
new file mode 100644
--- /dev/null
+++ b/sql/test/analytics/Tests/analytics01.sql
@@ -0,0 +1,17 @@
+start transaction;
+create table analytics (aa int, bb int, cc bigint);
+insert into analytics values (15, 3, 15), (3, 1, 3), (2, 1, 2), (5, 3, 5),
(NULL, 2, NULL), (3, 2, 3), (4, 1, 4), (6, 3, 6), (8, 2, 8), (NULL, 4, NULL);
+
+select percent_rank() over (partition by aa) from analytics;
+select percent_rank() over (partition by aa order by aa asc) from analytics;
+select percent_rank() over (partition by aa order by aa desc) from analytics;
+select percent_rank() over (order by aa) from analytics;
+select percent_rank() over (order by aa desc) from analytics;
+
+select percent_rank() over (partition by bb) from analytics;
+select percent_rank() over (partition by bb order by bb asc) from analytics;
+select percent_rank() over (partition by bb order by bb desc) from analytics;
+select percent_rank() over (order by bb) from analytics;
+select percent_rank() over (order by bb desc) from analytics;
+
+rollback;
diff --git a/sql/test/analytics/Tests/analytics01.stable.err
b/sql/test/analytics/Tests/analytics01.stable.err
new file mode 100644
--- /dev/null
+++ b/sql/test/analytics/Tests/analytics01.stable.err
@@ -0,0 +1,35 @@
+stderr of test 'analytics01` in directory 'sql/test/analytics` itself:
+
+
+# 17:00:05 >
+# 17:00:05 > "mserver5" "--debug=10" "--set" "gdk_nr_threads=0" "--set"
"mapi_open=true" "--set" "mapi_port=39763" "--set"
"mapi_usock=/var/tmp/mtest-22291/.s.monetdb.39763" "--set" "monet_prompt="
"--forcemito"
"--dbpath=/home/ferreira/repositories/MonetDB-analytics/BUILD/var/MonetDB/mTests_sql_test_analytics"
"--set" "embedded_c=true"
+# 17:00:05 >
+
+# builtin opt gdk_dbpath =
/home/ferreira/repositories/MonetDB-analytics/BUILD/var/monetdb5/dbfarm/demo
+# builtin opt gdk_debug = 0
+# builtin opt gdk_vmtrim = no
+# builtin opt monet_prompt = >
+# builtin opt monet_daemon = no
+# builtin opt mapi_port = 50000
+# builtin opt mapi_open = false
+# builtin opt mapi_autosense = false
+# builtin opt sql_optimizer = default_pipe
+# builtin opt sql_debug = 0
+# cmdline opt gdk_nr_threads = 0
+# cmdline opt mapi_open = true
+# cmdline opt mapi_port = 39763
+# cmdline opt mapi_usock = /var/tmp/mtest-22291/.s.monetdb.39763
+# cmdline opt monet_prompt =
+# cmdline opt gdk_dbpath =
/home/ferreira/repositories/MonetDB-analytics/BUILD/var/MonetDB/mTests_sql_test_analytics
+# cmdline opt embedded_c = true
+# cmdline opt gdk_debug = 553648138
+
+# 17:00:05 >
+# 17:00:05 > "mclient" "-lsql" "-ftest" "-tnone" "-Eutf-8" "-i" "-e"
"--host=/var/tmp/mtest-22291" "--port=39763"
+# 17:00:05 >
+
+
+# 17:00:05 >
+# 17:00:05 > "Done."
+# 17:00:05 >
+
diff --git a/sql/test/analytics/Tests/analytics01.stable.out
b/sql/test/analytics/Tests/analytics01.stable.out
new file mode 100644
--- /dev/null
+++ b/sql/test/analytics/Tests/analytics01.stable.out
@@ -0,0 +1,226 @@
+stdout of test 'analytics01` in directory 'sql/test/analytics` itself:
+
+
+# 17:00:05 >
+# 17:00:05 > "mserver5" "--debug=10" "--set" "gdk_nr_threads=0" "--set"
"mapi_open=true" "--set" "mapi_port=39763" "--set"
"mapi_usock=/var/tmp/mtest-22291/.s.monetdb.39763" "--set" "monet_prompt="
"--forcemito"
"--dbpath=/home/ferreira/repositories/MonetDB-analytics/BUILD/var/MonetDB/mTests_sql_test_analytics"
"--set" "embedded_c=true"
+# 17:00:05 >
+
+# MonetDB 5 server v11.32.0
+# This is an unreleased version
+# Serving database 'mTests_sql_test_analytics', using 8 threads
+# Compiled for x86_64-pc-linux-gnu/64bit with 128bit integers
+# Found 15.492 GiB available main-memory.
+# Copyright (c) 1993 - July 2008 CWI.
+# Copyright (c) August 2008 - 2018 MonetDB B.V., all rights reserved
+# Visit https://www.monetdb.org/ for further information
+# Listening for connection requests on mapi:monetdb://wired-142.cwi.nl:39763/
+# Listening for UNIX domain connection requests on
mapi:monetdb:///var/tmp/mtest-22291/.s.monetdb.39763
+# MonetDB/GIS module loaded
+# SQL catalog created, loading sql scripts once
+# loading sql script: 09_like.sql
+# loading sql script: 10_math.sql
+# loading sql script: 11_times.sql
+# loading sql script: 12_url.sql
+# loading sql script: 13_date.sql
+# loading sql script: 14_inet.sql
+# loading sql script: 15_querylog.sql
+# loading sql script: 16_tracelog.sql
+# loading sql script: 17_temporal.sql
+# loading sql script: 18_index.sql
+# loading sql script: 20_vacuum.sql
+# loading sql script: 21_dependency_views.sql
+# loading sql script: 22_clients.sql
+# loading sql script: 23_skyserver.sql
+# loading sql script: 25_debug.sql
+# loading sql script: 26_sysmon.sql
+# loading sql script: 27_rejects.sql
+# loading sql script: 39_analytics.sql
+# loading sql script: 39_analytics_hge.sql
+# loading sql script: 40_geom.sql
+# loading sql script: 40_json.sql
+# loading sql script: 40_json_hge.sql
+# loading sql script: 41_md5sum.sql
+# loading sql script: 45_uuid.sql
+# loading sql script: 46_profiler.sql
+# loading sql script: 51_sys_schema_extension.sql
+# loading sql script: 60_wlcr.sql
+# loading sql script: 72_fits.sql
+# loading sql script: 74_netcdf.sql
+# loading sql script: 75_lidar.sql
_______________________________________________
checkin-list mailing list
[email protected]
https://www.monetdb.org/mailman/listinfo/checkin-list
