Incremental computation of common windowed holistic aggregates

Abstract

Windowed aggregates are a SQL 2003 feature for computing aggregates in moving windows. Common examples include cumulative sums, local maxima and moving quantiles. With the advent over the last few years of easy-to-use data analytics tools, these functions are becoming widely used by more and more analysts, but some aggregates (such as local maxima) are much easier to compute than others (such as moving quantiles). Nevertheless, aggregates that are more difficult to compute, like quantile and mode (or "most frequent") provide more appropriate statistical summaries in the common situation when a distribution is not Gaussian and are an essential part of a data analysis toolkit. Recent work has described highly efficient windowed implementations of the most common aggregate function categories, including distributive 1 aggregates such as cumulative sums and algebraic aggregates such as moving averages. But little has been published on either the implementation or the performance of the more complex holistic windowed aggregates such as moving quantiles. This paper provides the first in-depth study of how to efficiently implement the three most common holistic windowed aggregates (count distinct, mode and quantile) by reusing the aggregate state between consecutive frames. Our measurements show that these incremental algorithms generally achieve improvements of about 10× over naïve implementations, and that they can effectively detect when to reset the internal state during extreme frame variation.

Building Advanced SQL Analytics From Low-Level Plan Operators

“…However, these papers omit to discuss how to implement DISTINCT aggregates, which are significantly more complicated than simple aggregates, in particular, when implemented using hashing. There is even less work on window functions: The papers of Leis et al [24] and Wesley et al [36] are the only one that describe the implementation of window functions in detail. Cao et al [9] present query optimization techniques for queries with multiple window functions (e.g., reusing existing partitioning and ordering properties), which are also applicable and indeed are directly enabled by our approach.…”

GASSER: An Auto-Tunable System for General Sliding-Window Streaming Operators on GPUs

“…For commutative and associative functions, this allows for efficient computation because the system may avoid recomputing each window from scratch by reusing partial results of shared portions between consecutive windows. Optimized algorithms for incremental queries have been the subject of an intensive research for some notable queries (e.g., aggregates and quantiles [13]- [15]). This has also been studied for providing parallel solutions on GPUs (like in Saber [6]), where incremental queries allow the window processing to be split by computing partial results of different window fragments in parallel and then merging them to obtain final window results.…”

Overlay Indexes: Efficiently Supporting Aggregate Range Queries and Authenticated Data Structures in Off-the-Shelf Databases

“…In this kind of aggregation functions, there is no constant bound on the size of the storage needed to describe a sub-aggregate [25]. For this reason, they are commonly recognized as hard to optimize (see for example [61,36,14]). Examples of this kind of aggregation functions are median, n-th percentile, and mode.…”