Changeset: d3397b421fa3 for MonetDB
URL: https://dev.monetdb.org/hg/MonetDB?cmd=changeset;node=d3397b421fa3
Modified Files:
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/analytics01.sql
sql/test/analytics/Tests/analytics01.stable.out
Branch: analytics
Log Message:
Implemented cume_dist over a window.
diffs (truncated from 354 to 300 lines):
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
@@ -371,6 +371,77 @@ SQLpercent_rank(Client cntxt, MalBlkPtr
return MAL_SUCCEED;
}
+str
+SQLcume_dist(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.cume_dist", SQLSTATE(42000)
"cume_dist(: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,
*r;
+ int j, cnt;
+ dbl *rb, *rp, *end, cnt_cast;
+ bit *np;
+
+ if (!b)
+ throw(SQL, "sql.cume_dist", SQLSTATE(HY005) "Cannot
access column descriptor");
+ cnt = (int)BATcount(b);
+ cnt_cast = (dbl) cnt;
+ voidresultBAT(r, TYPE_dbl, cnt, b, "sql.cume_dist");
+ rb = 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));
+ 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; j++, np++, rp++) {
+ if (*np) {
+ for(; rb<rp; rb++)
+ *rb = j / cnt_cast;
+ }
+ }
+ for(; rb<rp; rb++)
+ *rb = 1;
+ } 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; j++, np++, rp++) {
+ if (*np) {
+ for(; rb<rp; rb++)
+ *rb = j / cnt_cast;
+ }
+ }
+ for(; rb<rp; rb++)
+ *rb = 1;
+ BBPunfix(p->batCacheid);
+ }
+ } else {
+ for(; rp<end; rp++)
+ *rp = 1;
+ }
+ 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
@@ -16,6 +16,7 @@ sql5_export str SQLrow_number(Client cnt
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 SQLcume_dist(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
@@ -56,6 +56,14 @@ pattern batsql.percent_rank(b:bat[:any_1
address SQLpercent_rank
comment "return the percentage into the total number of groups for each row";
+pattern sql.cume_dist(b:any_1, p:bit, o:bit) :dbl
+address SQLcume_dist
+comment "return the accumulated distribution of the number of rows per group
to the total number of partition rows";
+
+pattern batsql.cume_dist(b:bat[:any_1], p:any_2, o:any_3) :bat[:dbl]
+address SQLcume_dist
+comment "return the accumulated distribution of the number of rows per group
to the total number of partition rows";
+
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
@@ -62,6 +62,14 @@ pattern batsql.percent_rank(b:bat[:any_1
address SQLpercent_rank
comment "return the percentage into the total number of groups for each row";
+pattern sql.cume_dist(b:any_1, p:bit, o:bit) :dbl
+address SQLcume_dist
+comment "return the accumulated distribution of the number of rows per group
to the total number of partition rows";
+
+pattern batsql.cume_dist(b:bat[:any_1], p:any_2, o:any_3) :bat[:dbl]
+address SQLcume_dist
+comment "return the accumulated distribution of the number of rows per group
to the total number of partition rows";
+
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
@@ -1567,8 +1567,8 @@ sqltypeinit( sql_allocator *sa)
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, "cume_dist", "sql", "cume_dist", ANY, BIT, BIT,
DBL, 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);
diff --git a/sql/test/analytics/Tests/analytics01.sql
b/sql/test/analytics/Tests/analytics01.sql
--- a/sql/test/analytics/Tests/analytics01.sql
+++ b/sql/test/analytics/Tests/analytics01.sql
@@ -14,4 +14,19 @@ select percent_rank() over (partition by
select percent_rank() over (order by bb) from analytics;
select percent_rank() over (order by bb desc) from analytics;
+select cume_dist() over (partition by aa) from analytics;
+select cume_dist() over (partition by aa order by aa asc) from analytics;
+select cume_dist() over (partition by aa order by aa desc) from analytics;
+select cume_dist() over (order by aa) from analytics;
+select cume_dist() over (order by aa desc) from analytics;
+
+select cume_dist() over (partition by bb) from analytics;
+select cume_dist() over (partition by bb order by bb asc) from analytics;
+select cume_dist() over (partition by bb order by bb desc) from analytics;
+select cume_dist() over (order by bb) from analytics;
+select cume_dist() over (order by bb desc) from analytics;
+
+select percent_rank() over () from analytics;
+select cume_dist() over () from analytics;
+
rollback;
diff --git a/sql/test/analytics/Tests/analytics01.stable.out
b/sql/test/analytics/Tests/analytics01.stable.out
--- a/sql/test/analytics/Tests/analytics01.stable.out
+++ b/sql/test/analytics/Tests/analytics01.stable.out
@@ -218,6 +218,186 @@ Ready.
[ 0.7777777778 ]
[ 0.7777777778 ]
[ 0.7777777778 ]
+#select cume_dist() over (partition by aa) from analytics;
+% sys.L4 # table_name
+% L4 # name
+% double # type
+% 24 # length
+[ 0.1 ]
+[ 0.2 ]
+[ 0.3 ]
+[ 0.4 ]
+[ 0.5 ]
+[ 0.7 ]
+[ 0.7 ]
+[ 0.8 ]
+[ 1 ]
+[ 1 ]
+#select cume_dist() over (partition by aa order by aa asc) from analytics;
+% sys.L4 # table_name
+% L4 # name
+% double # type
+% 24 # length
+[ 0.2 ]
+[ 0.2 ]
+[ 0.3 ]
+[ 0.5 ]
+[ 0.5 ]
+[ 0.6 ]
+[ 0.7 ]
+[ 0.8 ]
+[ 0.9 ]
+[ 1 ]
+#select cume_dist() over (partition by aa order by aa desc) from analytics;
+% sys.L4 # table_name
+% L4 # name
+% double # type
+% 24 # length
+[ 0.1 ]
+[ 0.2 ]
+[ 0.3 ]
+[ 0.4 ]
+[ 0.5 ]
+[ 0.7 ]
+[ 0.7 ]
+[ 0.8 ]
+[ 1 ]
+[ 1 ]
+#select cume_dist() over (order by aa) from analytics;
+% sys.L4 # table_name
+% L4 # name
+% double # type
+% 24 # length
+[ 1 ]
+[ 1 ]
+[ 1 ]
+[ 1 ]
+[ 1 ]
+[ 1 ]
+[ 1 ]
+[ 1 ]
+[ 1 ]
+[ 1 ]
+#select cume_dist() over (order by aa desc) from analytics;
+% sys.L4 # table_name
+% L4 # name
+% double # type
+% 24 # length
+[ 1 ]
+[ 1 ]
+[ 1 ]
+[ 1 ]
+[ 1 ]
+[ 1 ]
+[ 1 ]
+[ 1 ]
+[ 1 ]
+[ 1 ]
+#select cume_dist() over (partition by bb) from analytics;
+% sys.L4 # table_name
+% L4 # name
+% double # type
+% 24 # length
+[ 0.1 ]
+[ 0.4 ]
+[ 0.4 ]
+[ 0.4 ]
+[ 0.7 ]
+[ 0.7 ]
+[ 0.7 ]
+[ 1 ]
+[ 1 ]
+[ 1 ]
+#select cume_dist() over (partition by bb order by bb asc) from analytics;
+% sys.L4 # table_name
+% L4 # name
+% double # type
+% 24 # length
+[ 0.3 ]
+[ 0.3 ]
+[ 0.3 ]
+[ 0.6 ]
+[ 0.6 ]
+[ 0.6 ]
+[ 0.9 ]
+[ 0.9 ]
+[ 0.9 ]
+[ 1 ]
+#select cume_dist() over (partition by bb order by bb desc) from analytics;
+% sys.L4 # table_name
+% L4 # name
+% double # type
+% 24 # length
+[ 0.1 ]
+[ 0.4 ]
+[ 0.4 ]
+[ 0.4 ]
+[ 0.7 ]
+[ 0.7 ]
+[ 0.7 ]
+[ 1 ]
+[ 1 ]
+[ 1 ]
+#select cume_dist() over (order by bb) from analytics;
+% sys.L4 # table_name
+% L4 # name
+% double # type
+% 24 # length
+[ 1 ]
+[ 1 ]
+[ 1 ]
+[ 1 ]
_______________________________________________
checkin-list mailing list
[email protected]
https://www.monetdb.org/mailman/listinfo/checkin-list
