Changeset: 21b3a22794ba for MonetDB
URL: http://dev.monetdb.org/hg/MonetDB?cmd=changeset;node=21b3a22794ba
Modified Files:
gdk/gdk_bbp.c
sql/backends/monet5/sql_rank.c
sql/backends/monet5/sql_rank.h
sql/backends/monet5/sql_rank.mal
sql/common/sql_types.c
sql/include/sql_catalog.h
sql/server/rel_select.c
sql/server/sql_parser.y
sql/server/sql_scan.c
Branch: analytics
Log Message:
initial work on window based aggregation (ie analytical functions)
Sofar only a basic implementation for 'min' (for integer values only) is done.
We pass the information for window framing, but its not used.
diffs (truncated from 427 to 300 lines):
diff --git a/gdk/gdk_bbp.c b/gdk/gdk_bbp.c
--- a/gdk/gdk_bbp.c
+++ b/gdk/gdk_bbp.c
@@ -2426,6 +2426,8 @@ incref(bat i, int logical, int lock)
BAT *pb;
incref(tp, 0, lock);
pb = getBBPdescriptor(tp, lock);
+ if (!pb)
+ return 0;
b->theap.base = pb->theap.base + (size_t) b->theap.base;
/* if we shared the hash before, share it
* again note that if the parent's hash is
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
@@ -222,7 +222,7 @@ SQLdense_rank(Client cntxt, MalBlkPtr mb
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.rank", "rank(:any_1,:bit,:bit)");
+ throw(SQL, "sql.dense_rank", "dense_rank(:any_1,:bit,:bit)");
}
(void)cntxt;
if (isaBatType(getArgType(mb, pci, 1))) {
@@ -232,7 +232,7 @@ SQLdense_rank(Client cntxt, MalBlkPtr mb
bit *np, *no;
if (!b)
- throw(SQL, "sql.rank", "Cannot access descriptor");
+ throw(SQL, "sql.dense_rank", "Cannot access
descriptor");
cnt = (int)BATcount(b);
voidresultBAT(r, TYPE_int, cnt, b, "Cannot create bat");
rp = (int*)Tloc(r, 0);
@@ -244,7 +244,7 @@ SQLdense_rank(Client cntxt, MalBlkPtr mb
BBPunfix(b->batCacheid);
if (p) BBPunfix(p->batCacheid);
if (o) BBPunfix(o->batCacheid);
- throw(SQL, "sql.rank", "Cannot access
descriptor");
+ throw(SQL, "sql.dense_rank", "Cannot
access descriptor");
}
np = (bit*)Tloc(p, 0);
no = (bit*)Tloc(o, 0);
@@ -261,7 +261,7 @@ SQLdense_rank(Client cntxt, MalBlkPtr mb
p = BATdescriptor(*getArgReference_bat(stk,
pci, 2));
if (!p) {
BBPunfix(b->batCacheid);
- throw(SQL, "sql.rank", "Cannot access
descriptor");
+ throw(SQL, "sql.dense_rank", "Cannot
access descriptor");
}
np = (bit*)Tloc(p, 0);
for(i=1,j=1; i<=cnt; i++, np++, rp++) {
@@ -276,7 +276,7 @@ SQLdense_rank(Client cntxt, MalBlkPtr mb
o = BATdescriptor(*getArgReference_bat(stk,
pci, 3));
if (!o) {
BBPunfix(b->batCacheid);
- throw(SQL, "sql.rank", "Cannot access
descriptor");
+ throw(SQL, "sql.dense_rank", "Cannot
access descriptor");
}
no = (bit*)Tloc(o, 0);
for(i=1,j=1; i<=cnt; i++, no++, rp++) {
@@ -300,3 +300,149 @@ SQLdense_rank(Client cntxt, MalBlkPtr mb
}
return MAL_SUCCEED;
}
+
+static str
+SQLanalytics_args(BAT **r, BAT **b, BAT **p, BAT **o, Client cntxt, MalBlkPtr
mb, MalStkPtr stk, InstrPtr pci, const str mod, const str err)
+{
+ *r = *b = *p = *o = NULL;
+
+ (void)cntxt;
+ if (pci->argc != 7 ||
+ (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, mod, "%s", err);
+ }
+ if (isaBatType(getArgType(mb, pci, 1))) {
+ *b = BATdescriptor(*getArgReference_bat(stk, pci, 1));
+ if (!*b)
+ throw(SQL, mod, "Cannot access descriptor");
+ }
+ if (b) {
+ size_t cnt = BATcount(*b);
+ voidresultBAT((*r), (*b)->ttype, cnt, (*b), "Cannot create
bat");
+ if (!*r)
+ if (*b) BBPunfix((*b)->batCacheid);
+ }
+ if (isaBatType(getArgType(mb, pci, 2))) {
+ *p = BATdescriptor(*getArgReference_bat(stk, pci, 2));
+ if (!*p) {
+ if (*b) BBPunfix((*b)->batCacheid);
+ if (*r) BBPunfix((*r)->batCacheid);
+ throw(SQL, mod, "Cannot access descriptor");
+ }
+ }
+ if (isaBatType(getArgType(mb, pci, 3))) {
+ *o = BATdescriptor(*getArgReference_bat(stk, pci, 3));
+ if (!*o) {
+ if (*b) BBPunfix((*b)->batCacheid);
+ if (*r) BBPunfix((*r)->batCacheid);
+ if (*p) BBPunfix((*p)->batCacheid);
+ throw(SQL, mod, "Cannot access descriptor");
+ }
+ }
+ return NULL;
+}
+
+str
+SQLmin(Client cntxt, MalBlkPtr mb, MalStkPtr stk, InstrPtr pci)
+{
+ BAT *r, *b, *p, *o;
+ str res = SQLanalytics_args( &r, &b, &p, &o, cntxt, mb, stk, pci,
"sql.min", "min(:any_1,:bit,:bit)");
+ int tpe = getArgType(mb, pci, 1);
+ int start = *getArgReference_int(stk, pci, 4);
+ int end = *getArgReference_int(stk, pci, 5);
+ int excl = *getArgReference_int(stk, pci, 6);
+
+ if (excl != 0)
+ throw(SQL, "sql.min", "OVER currently only supports frame
extends with unit ROWS");
+ (void)start;
+ (void)end;
+
+ if (isaBatType(tpe))
+ tpe = getBatType(tpe);
+ if (res)
+ return res;
+
+ /*
+ switch(ATOMstorage(tpe)) {
+ case TYPE_bte:
+ case TYPE_sht:
+ case TYPE_int:
+ case TYPE_lng:
+#ifdef HAVE_HUGE
+ case TYPE_hge:
+#endif
+ case TYPE_flt:
+ case TYPE_dbl:
+ default:
+ throw(SQL, "sql.min", "min(:any_1,:bit,:bit)");
+ }
+ */
+
+ /* FOR NOW only int input type !! */
+ if (b) {
+ bat *res = getArgReference_bat(stk, pci, 0);
+ int i, j, cnt, *rp, *rb, *bp, curval;
+ bit *np, *no;
+
+ cnt = (int)BATcount(b);
+ rb = rp = (int*)Tloc(r, 0);
+ bp = (int*)Tloc(b, 0);
+ curval = *bp;
+ if (p) {
+ if (o) {
+ np = (bit*)Tloc(p, 0);
+ no = (bit*)Tloc(o, 0);
+ for(i=1,j=1; i<=cnt; i++, np++, no++, rp++,
bp++) {
+ if (*np) {
+ j=1;
+ for (;rb < rp; rb++)
+ *rb = curval;
+ curval = *bp;
+ } else if (*no)
+ j++;
+ curval = MIN(*bp,curval);
+ }
+ for (;rb < rp; rb++)
+ *rb = curval;
+ } else { /* single value, ie no ordering */
+ np = (bit*)Tloc(p, 0);
+ for(i=1,j=1; i<=cnt; i++, np++, rp++, bp++) {
+ if (*np) {
+ j=1;
+ for (;rb < rp; rb++)
+ *rb = curval;
+ curval = *bp;
+ }
+ curval = MIN(*bp,curval);
+ }
+ for (;rb < rp; rb++)
+ *rb = curval;
+ }
+ } else if (o) { /* single value, ie no partitions */
+ no = (bit*)Tloc(o, 0);
+ for(i=1,j=1; i<=cnt; i++, no++, rp++, bp++) {
+ if (*no)
+ j++;
+ *rp = j;
+ curval = MIN(*bp,curval);
+ }
+ for (;rb < rp; rb++)
+ *rb = curval;
+ } else { /* single value, ie no ordering */
+ for(i=1; i<=cnt; i++, rp++, bp++)
+ *rp = *bp;
+ }
+ BATsetcount(r, cnt);
+ BBPunfix(b->batCacheid);
+ if (p) BBPunfix(p->batCacheid);
+ if (o) BBPunfix(o->batCacheid);
+ BBPkeepref(*res = r->batCacheid);
+ } else {
+ ptr *res = getArgReference(stk, pci, 0);
+ ptr *in = getArgReference(stk, pci, 1);
+
+ *res = *in;
+ }
+ return MAL_SUCCEED;
+}
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,5 +15,6 @@ 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 SQLmin(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
@@ -47,6 +47,14 @@ pattern batsql.dense_rank(b:bat[:any_1],
address SQLdense_rank
comment "return the densely ranked groups";
+pattern sql.min(b:any_1, p:bit, o:bit, s:int, e:int, excl:int) :any_1
+address SQLmin
+comment "return the minimum of groups";
+
+pattern batsql.min(b:bat[:any_1], p:any_2, o:any_3, s:int, e:int, exl:int)
:bat[:any_1]
+address SQLmin
+comment "return the minimum of groups";
+
command aggr.exist(b:bat[:any_2], h:any_1):bit
address ALGexist;
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
@@ -1513,6 +1513,7 @@ 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", "precent_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, "lag", "sql", "lag", ANY, BIT, BIT, ANY,
SCALE_NONE);
@@ -1520,7 +1521,7 @@ sqltypeinit( sql_allocator *sa)
//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, "sum", "sql", "sum", ANY, BIT, BIT, ANY,
SCALE_NONE);
- //sql_create_analytic(sa, "min", "sql", "min", ANY, BIT, BIT, ANY,
SCALE_NONE);
+ sql_create_analytic(sa, "min", "sql", "min", ANY, BIT, BIT, ANY,
SCALE_NONE);
//sql_create_analytic(sa, "max", "sql", "max", ANY, BIT, BIT, ANY,
SCALE_NONE);
//sql_create_analytic(sa, "avg", "sql", "avg", ANY, BIT, BIT, ANY,
SCALE_NONE);
//sql_create_analytic(sa, "count", "sql", "count", ANY, BIT, BIT, ANY,
SCALE_NONE);
diff --git a/sql/include/sql_catalog.h b/sql/include/sql_catalog.h
--- a/sql/include/sql_catalog.h
+++ b/sql/include/sql_catalog.h
@@ -91,14 +91,17 @@
#define RD_UPD_VAL 3
#define QUICK 4
-#define FRAME_ROWS 0
-#define FRAME_RANGE 1
+#define FRAME_ROWS 0 /* number of rows (preceding/following) */
+#define FRAME_RANGE 1 /* logical range (based on the ordering column).
+ Example:
+ RANGE BETWEEN INTERVAL '1' MONTH PRECEDING
+ AND INTERVAL '1' MONTH FOLLOWING */
+#define FRAME_GROUPS 2
-#define EXCLUDE_NONE 0
-#define EXCLUDE_CURRENT_ROW 1
-#define EXCLUDE_GROUP 2
-#define EXCLUDE_TIES 3
-#define EXCLUDE_NO_OTHERS 4
+#define EXCLUDE_NONE 0 /* nothing excluded (also the default) */
+#define EXCLUDE_CURRENT_ROW 1 /* exclude the current row */
+#define EXCLUDE_GROUP 2 /* exclude group */
+#define EXCLUDE_TIES 3 /* exclude group but not the current row */
#define cur_user 1
#define cur_role 2
diff --git a/sql/server/rel_select.c b/sql/server/rel_select.c
--- a/sql/server/rel_select.c
+++ b/sql/server/rel_select.c
@@ -4123,10 +4123,12 @@ rel_frame(mvc *sql, symbol *frame, list
/* units, extent, exclusion */
dnode *d = frame->data.lval->h;
- /* RANGE vs UNITS */
+ /* ROWS, RANGE, GROUPS */
sql_exp *units = exp_atom_int(sql->sa, d->next->next->data.i_val);
- sql_exp *start = exp_atom_int(sql->sa, d->data.i_val);
- sql_exp *end = exp_atom_int(sql->sa, d->next->data.i_val);
+ symbol *start_sym = d->data.sym;
+ symbol *end_sym = d->next->data.sym;
_______________________________________________
checkin-list mailing list
[email protected]
https://www.monetdb.org/mailman/listinfo/checkin-list
