Query Generation Assist Apparatus for Non-Zero Data Extraction
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Conventional methods for data analysis in business systems face challenges in generating optimal SQL queries for joining business and time-series data, often resulting in zero data extraction or difficulty in selecting suitable join paths due to lack of reference to actual relation data and inadequate user evaluation feedback.
Innovation Solution
A query generation assist method that accepts data extraction conditions, extracts joinable data, derives subgraphs as query candidates, and calculates rank values to output ranked query candidates, ensuring non-zero data extraction by referencing schema definitions and relation data at a record level.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Measurement precision
If analysts manually generate SQL queries by referring to entity relation maps and attempting table joins, then they can extract data from business databases, but the process becomes time-consuming and error-prone with difficulty in selecting optimal join paths
Solution Approach 1:
The patent introduces an automated query generation system that acts as an intermediary between the analyst's data extraction needs and the complex SQL query formulation process. The system automatically generates candidate SQL queries based on the entity relation map and user-specified conditions, eliminating the need for analysts to manually construct complex join paths while maintaining extraction accuracy.
Solution Approach 2:
The system implements feedback by evaluating multiple candidate SQL queries and ranking them based on execution results. The evaluation mechanism provides feedback on which queries successfully extract data and which return zero results, allowing the system to iteratively improve query selection and present the most effective queries to users.
2Adaptability or versatility
If multiple join path candidates are generated for data extraction, then comprehensive query options are provided, but analysts face difficulty in selecting the optimal join path from numerous candidates
Solution Approach 1:
The system evaluates each candidate query by executing it and measuring its effectiveness in extracting data. This feedback mechanism ranks the candidate queries based on their performance, allowing analysts to easily select the optimal join path without being overwhelmed by the number of candidates. The ranked results provide clear guidance on which queries are most likely to succeed.
Solution Approach 2:
The system changes the parameter of query presentation by transforming multiple unranked candidate queries into a ranked list based on evaluation metrics. This parameter transformation makes it easier for analysts to compare and select queries, as the ranking provides an objective basis for selection rather than requiring manual assessment of multiple complex join paths.
3Ease of manufacture
If conventional query generation methods are used without referencing actual relation data, then query structure can be formed based on schema definitions, but the queries may return zero data extraction results
Solution Approach 1:
The system performs preliminary evaluation of candidate SQL queries by executing them before final selection. This preliminary action checks whether the generated queries actually return data results, filtering out queries that would return zero results. This ensures that only reliable queries with actual data extraction capability are presented to analysts, maintaining both simplicity and reliability.
4Adaptability or versatility
If analysts attempt to join business data tables without familiarity with data models, then data analysis can proceed, but the join paths found are not necessarily optimal and may not meet requirements
Solution Approach 1:
The automated query generation system serves as an intermediary that bridges the gap between analysts unfamiliar with complex data models and the requirement for optimal join paths. The system automatically explores the entity relation map to generate multiple candidate join paths, evaluates them, and presents the optimal ones, allowing analysts to perform data analysis without needing deep expertise in the underlying data model structures.
Data Source
AI summary
To derive a query which can be joined and which gives non-zero pieces of data even if joined. A query generation assist method to assist generation of a query to extract data from a database includes: a first step of accepting, at the computer, a condition of extracting data to be acquired from the database as a data extraction condition; a second step of extracting, as graph data and at the computer, data which can be joined in data of the database; a third step of extracting subgraphs at the computer based on the data extraction condition from the graph data, and acquiring the subgraphs as query candidates; and a fourth step of calculating, at the computer, rank values of the query candidates and outputting the query candidates ranked according to the rank values.