Hierarchical Window Function via Visiting Sequence
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Conventional database management systems are not optimized for processing hierarchical data, particularly with SQL SELECT queries on self-referencing tables, which can be indeterminate and inflexible, and existing solutions for hierarchical data processing are slow and inefficient.
Innovation Solution
A system and method for executing a hierarchical window function by generating a visiting sequence data structure that specifies the order for traversing nodes in a hierarchy, allowing for the determination of summary values such as averages, counts, and sums across nodes and their descendants or ancestors, using indexing schemes like Pre, Post, Parent, and Level (PPPL) to optimize node identification and traversal.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Ease of operation
If SQL SELECT queries are used on self-referencing tables to retrieve hierarchical data, then data retrieval functionality is provided, but the query execution time becomes indeterminate and performance deteriorates due to multiple table joins
Solution Approach 1:
The patent applies preliminary action by pre-computing and storing the visiting sequence (traversal order) of hierarchical nodes in the database table structure. This allows the query execution to directly follow the pre-determined sequence without performing multiple self-joins during query time, thus making execution time deterministic and improving performance while maintaining ease of data retrieval
Solution Approach 2:
The patent changes the structural parameters of the hierarchical data representation by introducing specific columns (e.g., visiting_sequence, level, path) that encode the traversal order and hierarchy structure directly in the table. This transformation enables efficient querying without multiple joins, resolving the contradiction between operational ease and execution time
2Adaptability or versatility
If conventional database functionalities are used to operate on parent-child hierarchies, then hierarchical data processing is enabled, but the processing speed becomes slow and efficiency deteriorates
Solution Approach 1:
The patent segments the hierarchical processing task by pre-computing the visiting sequence and dividing the hierarchy into manageable traversal steps. Each row in the table contains pre-calculated navigation information (visiting_sequence, parent-child relationships) that enables efficient segment-by-segment processing without requiring slow conventional recursive queries, thus improving processing speed while maintaining versatility
Solution Approach 2:
The patent introduces intermediary data structures (visiting_sequence column, pre-computed path information) that act as mediators between the hierarchical data and query operations. These intermediaries enable fast traversal and processing by providing direct navigation paths, eliminating the need for slow conventional hierarchical processing methods
3Loss of information
If multiple self-joins are performed to execute SQL queries on hierarchical data, then complete hierarchical information is retrieved, but the device complexity increases and computational overhead grows
Solution Approach 1:
The patent applies preliminary action by pre-computing and storing complete traversal sequences and path information in dedicated columns during data insertion or maintenance operations. This allows single-query retrieval of complete hierarchical information without requiring multiple self-joins, thus reducing query structure complexity while maintaining information completeness
Solution Approach 2:
The patent extracts the complex join logic and replaces it with pre-stored visiting sequence data. By taking out the computational complexity from the query execution phase and embedding it in the data structure phase, the patent enables simple queries to retrieve complete hierarchical information without multiple self-joins, reducing device complexity
Data Source
Figure 1A
Figure 1B
Figure 2A~2B
AI summary
A method may include generating, based on a representation of a hierarchy stored in a database, a visiting sequence data structure. The hierarchy may be stored in a table in the database. Each of a plurality of rows comprising the table may correspond to one of a plurality of nodes comprising the hierarchy. The visiting sequence data structure may include a row vector specifying an order for traversing the plurality of nodes in the hierarchy. A hierarchical window function may be executed by iterating through the plurality of rows in the table in accordance with the order specified by the row vector. The execution of the hierarchical window function may further include determining, for a first node in the hierarchy, a summary value corresponding to a first value of the first node and a second value of a second node descendent from the first node.