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 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 _______________________________________________ users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list

Best regards, Richard Wesley Senior Research Scientist