Hierarchical Window Functions for Irregular Database Aggregations
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Existing relational database management systems (RDBMS) lack efficient methods for expressing and processing complex hierarchical computations, especially on irregular hierarchies, leading to inefficiencies and convoluted queries when dealing with aggregations and rollups.
Innovation Solution
The introduction of hierarchical computations and corresponding SQL constructs that allow for structural grouping operations, leveraging powerful indexing schemes to efficiently evaluate queries on arbitrary hierarchies, including the use of unary structural grouping and recursive expressions.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Adaptability or versatility
If conventional SQL mechanisms (ROLLUP) are used for hierarchical computations, then simple rollup operations can be performed, but complex computations on irregular hierarchies become exceedingly difficult and inefficient
Solution Approach 1:
The patent introduces hierarchical window functions as an intermediary mechanism between conventional SQL aggregation and complex hierarchical computations. These window functions serve as a mediator that enables sophisticated hierarchical operations (weighted sums, custom aggregations on irregular hierarchies) while maintaining SQL syntax simplicity, thus resolving the contradiction between computational capability and query complexity
Solution Approach 2:
The patent extends SQL's aggregation capabilities by introducing new parameters and options for hierarchical window functions, such as custom weighting schemes, flexible hierarchy definitions, and multiple aggregation types. This allows the system to adapt to complex hierarchical computation requirements without requiring convoluted query structures, thereby improving versatility while controlling complexity
2Adaptability or versatility
If recursive common table expressions (RCTEs) are used for hierarchical computations, then arbitrary hierarchical structures can be processed, but the resulting statements become convoluted and inherently inefficient
Solution Approach 1:
The patent replaces the mechanical recursive computation approach (RCTEs) with a window function-based mechanism that leverages the database engine's optimized aggregation capabilities. This substitution maintains the ability to process arbitrary hierarchies while achieving significantly better execution efficiency by avoiding the inherent performance penalties of recursive query evaluation
Solution Approach 2:
The patent performs preliminary organization of hierarchical data using window functions that pre-compute aggregation values in an optimized manner. This preliminary action allows subsequent queries to reuse these computed values, avoiding the repeated recursive evaluation that plagues RCTE-based approaches and thereby improving productivity
3Adaptability or versatility
If stored procedures or client code are used as workarounds for hierarchical computations, then complex computations can be performed, but expressiveness is reduced and efficiency is handicapped due to ignoring hierarchy structure
Solution Approach 1:
The patent creates a universal SQL-based hierarchical computation mechanism that can handle diverse hierarchical computation scenarios (weighted sums, custom aggregations, irregular hierarchies) directly within the database engine. This eliminates the need to switch to stored procedures or client code, maintaining both expressiveness and efficiency by keeping computations within the optimized SQL execution environment
Solution Approach 2:
The patent enables the database engine to self-service complex hierarchical computations through enhanced window function capabilities. The engine leverages its own indexing and aggregation mechanisms to efficiently process hierarchical data without external intervention, thereby maintaining both SQL expressiveness and computational efficiency that would otherwise require sacrificing one for the other
4Productivity
If conventional aggregation methods are used without hierarchy awareness, then standard SQL operations can be performed, but efficiency is lost due to ignoring known hierarchy structure
Solution Approach 1:
The patent segments the hierarchical computation process into distinct phases that can leverage different indexing strategies: (1) hierarchical window function evaluation that uses pre-computed hierarchy structures, and (2) final aggregation that reuses intermediate results. This segmentation enables efficient query execution while managing indexing complexity through modular organization of computation steps
Data Source
AI summary
Addressed herein is the problem of expressing and evaluating computations on hierarchies represented as database tables. Engine support for such computations is very limited today, and so they are usually outsourced into stored procedures or client code. Structural grouping is applied to relational algebra to provide concise syntax to express a class of useful computations. Algorithms are also provided to evaluate such structural groupings efficiently by exploiting available indexing schemes. Related apparatus, systems, techniques and articles are also described.