Changeset: 59327df64a2e for MonetDB
URL: https://dev.monetdb.org/hg/MonetDB?cmd=changeset;node=59327df64a2e
Modified Files:
gdk/gdk_analytic.c
gdk/gdk_analytic.h
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/server/sql_scan.c
sql/test/analytics/Tests/analytics01.sql
sql/test/analytics/Tests/analytics01.stable.err
sql/test/analytics/Tests/analytics01.stable.out
Branch: analytics
Log Message:
Implemented first_value and last_value on a window.
diffs (truncated from 1064 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
@@ -205,6 +205,231 @@ finish:
#undef ANALYTICAL_NTILE_IMP
#undef NTILE_CALC
+#define ANALYTICAL_FIRST_IMP(TPE) \
+ do { \
+ TPE *rp, *rb, *restrict bp, *end, curval; \
+ rb = rp = (TPE*)Tloc(r, 0); \
+ bp = (TPE*)Tloc(b, 0); \
+ curval = *bp; \
+ end = rp + cnt; \
+ if (p) { \
+ np = (bit*)Tloc(p, 0); \
+ for(; rp<end; np++, rp++, bp++) { \
+ if (*np) { \
+ if(is_##TPE##_nil(curval)) \
+ has_nils = true; \
+ for (;rb < rp; rb++) \
+ *rb = curval; \
+ curval = *bp; \
+ } \
+ } \
+ if(is_##TPE##_nil(curval)) \
+ has_nils = true; \
+ for (;rb < rp; rb++) \
+ *rb = curval; \
+ } else { \
+ if(is_##TPE##_nil(curval)) \
+ has_nils = true; \
+ for(; rp<end; rp++) \
+ *rp = curval; \
+ } \
+ } while(0);
+
+gdk_return
+GDKanalyticalfirst(BAT *r, BAT *b, BAT *p, BAT *o, int tpe)
+{
+ int (*atomcmp)(const void *, const void *);
+ const void *nil;
+ bool has_nils = false;
+ BUN i, j, cnt = BATcount(b);
+ bit *restrict np;
+ gdk_return gdk_res = GDK_SUCCEED;
+
+ (void)o;
+ switch(ATOMstorage(tpe)) {
+ case TYPE_bit:
+ ANALYTICAL_FIRST_IMP(bit)
+ break;
+ case TYPE_bte:
+ ANALYTICAL_FIRST_IMP(bte)
+ break;
+ case TYPE_sht:
+ ANALYTICAL_FIRST_IMP(sht)
+ break;
+ case TYPE_int:
+ ANALYTICAL_FIRST_IMP(int)
+ break;
+ case TYPE_lng:
+ ANALYTICAL_FIRST_IMP(lng)
+ break;
+#ifdef HAVE_HUGE
+ case TYPE_hge:
+ ANALYTICAL_FIRST_IMP(hge)
+ break;
+#endif
+ case TYPE_flt:
+ ANALYTICAL_FIRST_IMP(flt)
+ break;
+ case TYPE_dbl:
+ ANALYTICAL_FIRST_IMP(dbl)
+ break;
+ default: {
+ BATiter bpi = bat_iterator(b);
+ void *restrict curval = BUNtail(bpi, 0);
+ nil = ATOMnilptr(tpe);
+ atomcmp = ATOMcompare(tpe);
+ if (p) {
+ np = (bit*)Tloc(p, 0);
+ for(i=0,j=0; i<cnt; i++, np++) {
+ if (*np) {
+ if((*atomcmp)(curval, nil) == 0)
+ has_nils = true;
+ for (;j < i; j++) {
+ if ((gdk_res =
BUNappend(r, curval, false)) != GDK_SUCCEED)
+ goto finish;
+ }
+ curval = BUNtail(bpi, i);
+ }
+ }
+ if((*atomcmp)(curval, nil) == 0)
+ has_nils = true;
+ for (;j < i; j++) {
+ if ((gdk_res = BUNappend(r, curval,
false)) != GDK_SUCCEED)
+ goto finish;
+ }
+ } else { /* single value, ie no ordering */
+ if((*atomcmp)(curval, nil) == 0)
+ has_nils = true;
+ for(i=0; i<cnt; i++) {
+ if ((gdk_res = BUNappend(r, curval,
false)) != GDK_SUCCEED)
+ goto finish;
+ }
+ }
+ }
+ }
+finish:
+ BATsetcount(r, cnt);
+ r->tnonil = !has_nils;
+ r->tnil = has_nils;
+ return gdk_res;
+}
+
+#undef ANALYTICAL_FIRST_IMP
+
+#define ANALYTICAL_LAST_IMP(TPE) \
+ do { \
+ TPE *rp, *rb, *restrict bp, *end, curval; \
+ rb = rp = (TPE*)Tloc(r, 0); \
+ bp = (TPE*)Tloc(b, 0); \
+ end = rp + cnt; \
+ if (p) { \
+ np = (bit*)Tloc(p, 0); \
+ for(; rp<end; np++, rp++, bp++) { \
+ if (*np) { \
+ curval = *(bp - 1); \
+ if(is_##TPE##_nil(curval)) \
+ has_nils = true; \
+ for (;rb < rp; rb++) \
+ *rb = curval; \
+ } \
+ } \
+ curval = *(bp - 1); \
+ if(is_##TPE##_nil(curval)) \
+ has_nils = true; \
+ for (;rb < rp; rb++) \
+ *rb = curval; \
+ } else { \
+ curval = *(bp + cnt - 1); \
+ if(is_##TPE##_nil(curval)) \
+ has_nils = true; \
+ for(; rp<end; rp++) \
+ *rp = curval; \
+ } \
+ } while(0);
+
+gdk_return
+GDKanalyticallast(BAT *r, BAT *b, BAT *p, BAT *o, int tpe)
+{
+ int (*atomcmp)(const void *, const void *);
+ const void *nil;
+ bool has_nils = false;
+ BUN i, j, cnt = BATcount(b);
+ bit *restrict np;
+ gdk_return gdk_res = GDK_SUCCEED;
+
+ (void)o;
+ switch(ATOMstorage(tpe)) {
+ case TYPE_bit:
+ ANALYTICAL_LAST_IMP(bit)
+ break;
+ case TYPE_bte:
+ ANALYTICAL_LAST_IMP(bte)
+ break;
+ case TYPE_sht:
+ ANALYTICAL_LAST_IMP(sht)
+ break;
+ case TYPE_int:
+ ANALYTICAL_LAST_IMP(int)
+ break;
+ case TYPE_lng:
+ ANALYTICAL_LAST_IMP(lng)
+ break;
+#ifdef HAVE_HUGE
+ case TYPE_hge:
+ ANALYTICAL_LAST_IMP(hge)
+ break;
+#endif
+ case TYPE_flt:
+ ANALYTICAL_LAST_IMP(flt)
+ break;
+ case TYPE_dbl:
+ ANALYTICAL_LAST_IMP(dbl)
+ break;
+ default: {
+ BATiter bpi = bat_iterator(b);
+ void *restrict curval;
+ nil = ATOMnilptr(tpe);
+ atomcmp = ATOMcompare(tpe);
+ if (p) {
+ np = (bit*)Tloc(p, 0);
+ for(i=0,j=0; i<cnt; i++, np++) {
+ if (*np) {
+ curval = BUNtail(bpi, i - 1);
+ if((*atomcmp)(curval, nil) == 0)
+ has_nils = true;
+ for (;j < i; j++) {
+ if ((gdk_res =
BUNappend(r, curval, false)) != GDK_SUCCEED)
+ goto finish;
+ }
+ }
+ }
+ curval = BUNtail(bpi, cnt - 1);
+ if((*atomcmp)(curval, nil) == 0)
+ has_nils = true;
+ for (;j < i; j++) {
+ if ((gdk_res = BUNappend(r, curval,
false)) != GDK_SUCCEED)
+ goto finish;
+ }
+ } else { /* single value, ie no ordering */
+ curval = BUNtail(bpi, cnt - 1);
+ if((*atomcmp)(curval, nil) == 0)
+ has_nils = true;
+ for(i=0; i<cnt; i++) {
+ if ((gdk_res = BUNappend(r, curval,
false)) != GDK_SUCCEED)
+ goto finish;
+ }
+ }
+ }
+ }
+finish:
+ BATsetcount(r, cnt);
+ r->tnonil = !has_nils;
+ r->tnil = has_nils;
+ return gdk_res;
+}
+
+#undef ANALYTICAL_LAST_IMP
+
#define ANALYTICAL_LIMIT_IMP(TPE, OP) \
do { \
TPE *rp, *rb, *restrict bp, *end, curval; \
diff --git a/gdk/gdk_analytic.h b/gdk/gdk_analytic.h
--- a/gdk/gdk_analytic.h
+++ b/gdk/gdk_analytic.h
@@ -18,6 +18,8 @@
gdk_export gdk_return GDKanalyticaldiff(BAT *r, BAT *b, BAT *c, int tpe);
gdk_export gdk_return GDKanalyticalntile(BAT *r, BAT *b, BAT *p, BAT *o, int
tpe, ptr ntile);
+gdk_export gdk_return GDKanalyticalfirst(BAT *r, BAT *b, BAT *p, BAT *o, int
tpe);
+gdk_export gdk_return GDKanalyticallast(BAT *r, BAT *b, BAT *p, BAT *o, int
tpe);
gdk_export gdk_return GDKanalyticalmin(BAT *r, BAT *b, BAT *p, BAT *o, int
tpe);
gdk_export gdk_return GDKanalyticalmax(BAT *r, BAT *b, BAT *p, BAT *o, int
tpe);
gdk_export gdk_return GDKanalyticalcount(BAT *r, BAT *b, BAT *p, BAT *o, const
bit *ignore_nils, int tpe);
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
@@ -620,6 +620,18 @@ SQLanalytical_func(Client cntxt, MalBlkP
}
str
+SQLfirst_value(Client cntxt, MalBlkPtr mb, MalStkPtr stk, InstrPtr pci)
+{
+ return SQLanalytical_func(cntxt, mb, stk, pci, "sql.first_value",
SQLSTATE(42000) "first_value(:any_1,:bit,:bit)", GDKanalyticalfirst);
+}
+
+str
+SQLlast_value(Client cntxt, MalBlkPtr mb, MalStkPtr stk, InstrPtr pci)
+{
+ return SQLanalytical_func(cntxt, mb, stk, pci, "sql.last_value",
SQLSTATE(42000) "last_value(:any_1,:bit,:bit)", GDKanalyticallast);
+}
+
+str
SQLmin(Client cntxt, MalBlkPtr mb, MalStkPtr stk, InstrPtr pci)
{
return SQLanalytical_func(cntxt, mb, stk, pci, "sql.min",
SQLSTATE(42000) "min(:any_1,:bit,:bit)", GDKanalyticalmin);
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
@@ -18,6 +18,8 @@ sql5_export str SQLdense_rank(Client cnt
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 SQLntile(Client cntxt, MalBlkPtr mb, MalStkPtr stk, InstrPtr
pci);
+sql5_export str SQLfirst_value(Client cntxt, MalBlkPtr mb, MalStkPtr stk,
InstrPtr pci);
+sql5_export str SQLlast_value(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
@@ -64,6 +64,22 @@ pattern batsql.cume_dist(b:bat[:any_1],
address SQLcume_dist
comment "return the accumulated distribution of the number of rows per group
to the total number of partition rows";
+pattern sql.first_value(b:any_1, p:bit, o:bit, unit:int, s:int, e:int,
excl:int) :any_1
+address SQLfirst_value
+comment "return the first value of groups";
+
+pattern batsql.first_value(b:bat[:any_1], p:any_2, o:any_3, unit:int, s:int,
e:int, exl:int) :bat[:any_1]
+address SQLfirst_value
+comment "return the first value of groups";
+
+pattern sql.last_value(b:any_1, p:bit, o:bit, unit:int, s:int, e:int,
excl:int) :any_1
+address SQLlast_value
+comment "return the last value of groups";
+
_______________________________________________
checkin-list mailing list
[email protected]
https://www.monetdb.org/mailman/listinfo/checkin-list
