Running aggregates?

Moritz Bruder moritz.bruder at student.uni-tuebingen.de
Sun Feb 26 14:26:03 CET 2017


Hi Stefan,

Am 26.02.2017 um 13:40 schrieb Stefan Manegold:
> Hi,
>
> does MonetDB support any efficient/optimized functionality to compute running aggregates?
As far as I know only for row numbering.

> A quick check/test suggest that aggregation functions with windows functions like below
> does not seem to be supported, is it?
>
> ========
>   SELECT somedate, somevalue,
>    SUM(somevalue) OVER(ORDER BY somedate
>       ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
>            AS RunningTotal
>    FROM Table
> ========
>
> Is their anything else that would provide running aggregates?
As a tiny part of my master thesis I wrote a few primitives for MAL to 
support running aggregates (sum, min, max, avg, first_value). I don't 
know how to embed them in SQL as I did not use it. Note that the window 
functions are neither comprehensively tested nor used (and most likely 
don't have the quality MonetDB provides). At the moment semantics for 
unbounded and bounded windows exist (the latter is not very efficient at 
the moment, i.e. O(n * window_len), as it does not use group properties, 
i.e. subtraction).

I know that it is probably not too helpful, but who knows.


Best wishes,

Moritz
-------------- next part --------------
s:bat[:oid] := bat.new(nil:oid);
bat.append(s, 0:oid);
bat.append(s, 0:oid);
bat.append(s, 0:oid);
bat.append(s, 0:oid);
bat.append(s, 0:oid);
bat.append(s, 0:oid);
bat.append(s, 1:oid);
bat.append(s, 1:oid);
bat.append(s, 1:oid);
bat.append(s, 1:oid);
bat.append(s, 2:oid);
bat.append(s, 3:oid);
bat.append(s, 3:oid);

i:bat[:int] := bat.new(nil:int);
bat.append(i, 0);
bat.append(i, 1);
bat.append(i, 2);
bat.append(i, 3);
bat.append(i, 4);
bat.append(i, 5);
bat.append(i, 6);
bat.append(i, 7);
bat.append(i, 8);
bat.append(i, 9);
bat.append(i, 10);
bat.append(i, 11);
bat.append(i, 12);



d:bat[:bit] := batsql.diff(s);

io.print(s, d, i);

rsum:bat[:int] := dsh.win_fun_sum(d, i, 3:lng);

rmin:bat[:int] := dsh.win_fun_min(d, i, 3:lng);
rmax:bat[:int] := dsh.win_fun_max(d, i, 3:lng);

# int avg with div
ravg:bat[:int] := dsh.win_fun_avg(d, i, 3:lng);
idbl := batcalc.dbl(i);
ravgdbl:bat[:dbl] := dsh.win_fun_avg(d, idbl, 3:lng);

rnum:bat[:int] := batsql.row_number(d, d, nil:bit);

# simulate count with row number
rcntsim:bat[:int] := batcalc.min(3, rnum);
rcnt:bat[:int] := dsh.win_fun_count(d, 3:lng);

rfst:bat[:int] := dsh.win_fun_first_value(d, i, 3:lng);

io.print(s, d, i, rsum, rmin, rmax, ravg, ravgdbl, rnum, rcnt, rcntsim, rfst);
-------------- next part --------------
A non-text attachment was scrubbed...
Name: monetdb-dsh.tar.gz
Type: application/x-gzip
Size: 59172 bytes
Desc: not available
URL: <http://www.monetdb.org/pipermail/users-list/attachments/20170226/98872ca7/attachment-0001.bin>


More information about the users-list mailing list