Database Query Generation for Hierarchical Data Aggregation
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Users face difficulties in constructing complex database queries to dynamically aggregate rows of a data set, particularly due to the need for determining hierarchical relationships between columns and applying calculations across multiple levels in cloud-based data warehouses.
Innovation Solution
A system and method for generating database queries that determine hierarchical relationships between columns, receive calculations for aggregation, and generate queries to aggregate data sets based on these relationships, using a graphical user interface that allows users to select and arrange columns to define the hierarchy and apply calculations across the data set.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Ease of operation
If users manually construct complex database queries to dynamically aggregate rows, then data aggregation functionality is achieved, but the complexity and difficulty of query construction increases
Solution Approach 1:
The patent introduces an intermediary system that translates user-friendly spreadsheet operations into complex database queries. The spreadsheet structure acts as a mediator between the user and the database, allowing users to define hierarchical relationships and calculations in a familiar format while the system automatically generates the corresponding SQL queries with GROUP BY clauses and aggregation functions.
Solution Approach 2:
The system enables self-service by automatically generating complex aggregation queries based on user-defined spreadsheet structures. Users don't need to manually write SQL aggregation logic; instead, they configure the desired output in a spreadsheet interface, and the system autonomously translates this into the appropriate database query with hierarchical grouping and calculation operations.
2Productivity
If complex queries are constructed to handle hierarchical relationships and calculations, then data aggregation capability is improved, but the time required to construct and execute queries increases
Solution Approach 1:
The system performs preliminary action by pre-defining hierarchical relationships between columns and storing calculation logic in the spreadsheet structure before query execution. Users configure the desired aggregation hierarchy and calculations in advance using familiar spreadsheet operations, and the system caches this structure for rapid query generation, eliminating the need to reconstruct complex aggregation logic each time.
3Manufacturing precision
If users need to determine hierarchical relationships between columns, then accurate data aggregation is achieved, but the difficulty of operation increases
Solution Approach 1:
The system changes the parameter representation from abstract database schema to familiar spreadsheet terminology. Hierarchical relationships are defined using spreadsheet column positions and row structures that users already understand from common Excel or Google Sheets usage. The system maps these familiar spreadsheet parameters to database hierarchical relationships, maintaining aggregation accuracy while dramatically improving ease of operation.
Data Source
AI summary
Generating a database query to dynamically aggregate rows of a data set including determining a hierarchical relationship between a first column and a second column within a spreadsheet structure, wherein the first column and the second column comprise rows of the data set; receiving a calculation for a third column within the spreadsheet structure; determining that the third column is on a same hierarchical level as the first column; and in response to determining that the third column is on the same hierarchical level as the first column, generating the database query aggregating a plurality of rows of the data set based on the calculation for the third column and the hierarchical relationship between the first column and the second column.