Efficient Evaluation of Arbitrarily-Framed Holistic SQL Aggregates and Window Functions
Vogelsgesang A, Neumann T, Leis V, Kemper A (2022)
Publication Type: Conference contribution
Publication year: 2022
Publisher: Association for Computing Machinery
Pages Range: 1243-1256
Conference Proceedings Title: Proceedings of the ACM SIGMOD International Conference on Management of Data
Event location: Virtual
ISBN: 9781450392495
Abstract
Window functions became part of the SQL standard in SQL:2003 and are widely used for data analytics: Percentiles, rankings, moving averages, running sums and local maxima are all expressed as window functions in SQL. Yet, the features offered by SQL's window functions lack composability: Framing is only available for distributive and algebraic aggregate functions, but not for holistic aggregates like percentiles and window functions like ranks. The SQL standard explicitly disallows holistic aggregates from being framed and thereby severely limits data analysts. This paper proposes to remove this restriction, thereby making window functions fully composable. The newly gained composability allows for more complex aggregates which are tricky to evaluate. The lack of subquadratic, parallel algorithms to evaluate framed holistic aggregates is probably the main objection against adding truly composable window functionality to the SQL standard. As such, this paper shows how to efficiently evaluate all window and aggregate functions from SQL:2011, except for DENSE_RANK, in combination with arbitrary window frames. This includes framed distinct aggregates, framed value functions, framed percentiles and framed ranks.
Authors with CRIS profile
Involved external institutions
Germany (DE)How to cite
APA:
Vogelsgesang, A., Neumann, T., Leis, V., & Kemper, A. (2022). Efficient Evaluation of Arbitrarily-Framed Holistic SQL Aggregates and Window Functions. In Proceedings of the ACM SIGMOD International Conference on Management of Data (pp. 1243-1256). Virtual, US: Association for Computing Machinery.
MLA:
Vogelsgesang, Adrian, et al. "Efficient Evaluation of Arbitrarily-Framed Holistic SQL Aggregates and Window Functions." Proceedings of the 2022 ACM SIGMOD International Conference on the Management of Data, SIGMOD 2022, Virtual Association for Computing Machinery, 2022. 1243-1256.
BibTeX: Download