Hi Moritz -
In case you are interested, there have been two VLDB papers recently on implementing efficient analytic functions:
Efficient Processing of Window Functions in Analytical SQL Queries http://www.vldb.org/pvldb/vol8/p1058-leis.pdf Incremental Computation of Common Windowed Holistic Aggregates https://research.tableau.com/sites/default/files/p1221-wesley.pdf
There was also a third one on optimisation a few years back
Optimization of Analytic Window Functions http://vldb.org/pvldb/vol5/p1244_yucao_vldb2012.pdf
On Feb 26, 2017, at 05:26, Moritz Bruder moritz.bruder@student.uni-tuebingen.de wrote:
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 <win_fun.mal><monetdb-dsh.tar.gz>_______________________________________________ users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list
Best regards, Richard Wesley Senior Research Scientist