A dynamic multi-mode SQL generation method and system based on a four-quadrant strategy

Abstract

The present application relates to the technical field of data processing and business intelligence, and more particularly to a dynamic multi-mode SQL generation method and system based on a four-quadrant strategy, the method comprising the following steps: obtaining and quantifying the complexity and flexibility of user queries; dynamically switching SQL generation modes based on a four-quadrant strategy and a decision gateway; under the selected SQL generation mode, performing data processing and analysis, and returning query results. Through an intelligent routing engine, the present application performs accurate qualitative and quantitative analysis on user queries before SQL generation, automatically shunts to the optimal solution according to the analysis results, and takes into account the reliability and flexibility of queries.

Description

TECHNICAL FIELD

[0001] The present application relates to the technical field of data processing and business intelligence, and more particularly, to a dynamic multi-mode SQL generation method based on a four-quadrant strategy. BACKGROUND

[0002] With the deepening of enterprise digital transformation, ChatBI (Chat Business Intelligence) systems that allow data interaction through natural language have become a key direction for industry development. ChatBI refers to a new type of data interaction system that allows users to ask questions to the system using natural language (just like daily conversation), and the system will automatically query data and return answers or charts without the user having to write complex code or SQL (Structured Query Language).

[0003] However, existing technical paths generally face a dilemma: one is a question and answer system based on fixed reports or APIs (Application Programming Interface), whose technical core is a "query-template" mapping mechanism that requires a pre-built semantic layer to match multiple SQL query templates pre-installed in the background by recognizing user intent and key entities. Since SQL queries are pre-designed and verified, their calculation logic, associated tables, and fields are determined, so their query results are accurate and reliable, and they are suitable for core business reports. However, the system intelligently answers questions pre-defined in the semantic layer and template library, and once the user's question exceeds the pre-set range or performs cross-model association, it will not be understood or will return an error, and its flexibility is severely insufficient, unable to meet the user's exploratory analysis needs. The second is to directly use a large language model for end-to-end Text-to-SQL (natural language to SQL) generation. After the user inputs a question, the system inputs the database schema information and the user's question together, submits it to the LLM (Large Language Model), and the LLM directly generates an executable SQL query based on its comprehensive understanding of natural language and SQL syntax. In theory, as long as the model's ability is strong enough, it can understand and generate SQL queries to address various complex, variable, and even previously unseen problems, perfectly meeting the needs of exploratory analysis and having high flexibility. However, the LLM may generate SQL with correct syntax but incorrect logic, or fabricate non-existent table names and field names, making it difficult to guarantee the accuracy of SQL generation in complex enterprise environments, especially for core business indicators, where small calculation deviations can lead to major decision-making errors.

[0004] Enterprises, especially in the financial, e-commerce, supply chain and other fields with high requirements for data accuracy, are therefore caught in a dilemma: choosing reliability, business analysis is rigid and difficult to innovate; choosing flexibility may lead to strategic misjudgment due to incorrect data, causing huge losses. Therefore, how to design a system that can balance the absolute reliability and high flexibility of query is a technical problem to be solved in the field. SUMMARY

[0005] The purpose of the present application is to overcome the shortcomings of the prior art, provide a dynamic multi-mode SQL generation method and generation system based on a four-quadrant strategy, which performs accurate qualitative and quantitative analysis on user queries before SQL generation through an intelligent routing engine, and automatically shunts to the optimal solution according to the analysis results, balancing query reliability and flexibility.

[0006] To solve the above technical problems, the technical scheme adopted by the present application is: a dynamic multi-mode SQL generation method based on a four-quadrant strategy, comprising the following steps:

[0007] S1: obtaining and quantitatively evaluating the complexity and flexibility of user queries;

[0008] S2: dynamically switching SQL generation modes based on a four-quadrant strategy and a decision gateway;

[0009] S3: executing data processing and analysis under the selected SQL generation mode and returning the query result.

[0010] The present application integrates the originally opposing "highly reliable template-based queries" and "highly flexible generated queries" through a unified framework, fundamentally solving the technical difficulties of existing ChatBI systems, changing the single and fixed processing mode of traditional systems, introducing a "preliminary evaluation" mechanism, enabling the system to dynamically select the optimal execution path according to the characteristics of the query request, like an expert; by shunting different queries to the most suitable processing channel, it not only guarantees the stability and accuracy of core business queries, but also provides the possibility for exploratory queries, thereby achieving higher query success rate, better resource utilization and better user experience as a whole.

[0011] Further, in step S1, obtaining and quantitatively evaluating user queries includes:

[0012] Receiving user text input through an interactive interface and using natural language processing technology for intent recognition and entity extraction;

[0013] According to the user query and the pre-built enterprise-level semantic layer, atomic features including complexity atomic features and flexibility atomic features are extracted in parallel, and scores are calculated according to preset rules;

[0014] The weights are configured for each atomic feature, and the complexity total score C is calculated by weighted summation Score and the flexibility total score F Score .

[0015] The complexity total score C and the flexibility total score F are obtained through NLP intent recognition, atomic feature extraction and weighted calculation Score and the flexibility total score F Score ; subjective and fuzzy natural language queries are converted into objective and quantifiable mathematical scores, providing a unique and reliable basis for subsequent automated decision-making, avoiding the inconsistency of subjective judgments. By extracting multi-dimensional atomic features in parallel and performing weighted fusion, the characterization of query complexity and flexibility is more precise and accurate, far superior to simple judgments based on single rules.

[0016] Further, the complexity atomic features include structural complexity, index complexity and constraint complexity; the flexibility atomic features include intent certainty, template matching degree and entity openness.

[0017] By limiting the six atomic features contained in complexity and flexibility, and closely combining the actual scene of enterprise data query, it has high practicality; the clear feature definition provides a clear structure and direction for subsequent adjustment of feature weights and improvement of evaluation accuracy through machine learning.

[0018] Further, in step S2, the SQL generation mode is dynamically switched through a double-layer threshold decision gateway, including:

[0019] whether the flexibility total score F Score is lower than the preset flexibility threshold F threshold ;

[0020] If yes, it is determined as a low-flexibility query, and the template SQL generation mode is switched to, and the path corresponds to the first quadrant and the second quadrant in the four quadrants;

[0021] If no, it is determined as a high-flexibility query, and it is further judged whether the complexity total score C Score is lower than the preset complexity threshold C threshold ;

[0022] If yes, it is determined as a low-complexity query, and the generated SQL generation mode is switched to, and the path corresponds to the third quadrant in the four quadrants;

[0023] If no, it is determined as a high-complexity query, and the proxy SQL generation mode is switched to, and the path corresponds to the fourth quadrant in the four quadrants.

[0024] The logic is clear, the calculation is simple, and the routing decision can be completed with extremely low delay, meeting the real-time requirements of interactive systems. The deterministic algorithm also guarantees the predictability of the results; strictly following the principle of "flexibility first, complexity second", it ensures that high-determinacy queries must take the most reliable templated path, thereby guaranteeing the absolute accuracy of core business at the source.

[0025] Further, when switching to the templated SQL generation mode:

[0026] Template matching is performed through semantic vector retrieval and entity verification to determine the target SQL template from the preset template library;

[0027] The entities in the user query are identified by a custom named entity recognition model, and the enterprise-level semantic layer is queried to standardize the entities into SQL condition expressions;

[0028] The standardized SQL condition expressions are safely filled into the target SQL template through parameterized query to generate an executable SQL statement.

[0029] Semantic vector retrieval ensures that relevant templates can be quickly found (high recall rate), while entity verification ensures that the finally selected template is logically complete and executable (high accuracy rate); using pre-audited SQL templates and parameterized filling, SQL syntax errors and performance problems are fundamentally eliminated, while effectively preventing SQL injection attacks, achieving the high reliability required by enterprise-level applications.

[0030] Further, when switching to the generated SQL generation mode:

[0031] According to the entities in the user query, the minimum and relevant context is dynamically extracted from the enterprise-level semantic layer and provided as instant prompts to the custom named entity recognition model;

[0032] The entities and instant prompts in the user query are identified by a custom named entity recognition model, and the enterprise-level semantic layer is queried to standardize the instant prompts and fill them into the target SQL template, providing condition values for the generated executable SQL statement.

[0033] By dynamically supplying "minimum and highly relevant" context from the enterprise semantic layer, the "hallucination" problem of LLM (Large Language Model) is greatly reduced, making the generated SQL more in line with the current status of enterprise data in terms of syntax and semantics. Even if the LLM is used to generate the SQL body, the query condition values are still filled by standardized parsing, ensuring the accuracy of the key filtering conditions, combining the flexibility of the generated mode with the precision of the templated mode.

[0034] Further, when switching to the proxy SQL generation mode:

[0035] Activating an intelligent agent based on the reasoning and action framework;

[0036] Autonomously decomposing a complex natural language query into multiple logically coherent sub-queries by the intelligent agent;

[0037] Dynamically planning the execution order for each sub-query and calling the template-based SQL generation mode or the generative SQL generation mode to execute the sub-query;

[0038] Aggregating the execution results of each sub-query to generate a comprehensive analysis report.

[0039] Enabling the system to handle complex analysis tasks (such as attribution analysis and root cause positioning) that cannot be completed with a single SQL, greatly expanding the depth and capability boundaries of the ChatBI system; automating the thinking process of analysts who manually decompose problems, write multiple SQLs, and integrate conclusions, empowering business users to directly obtain deep analysis insights, greatly improving analysis efficiency.

[0040] Further, collecting user feedback data on query results; based on the feedback data, continuously optimizing the weight configuration of atomic features, expanding the template library of the template-based SQL generation mode, and / or fine-tuning the large language model used by the generative SQL generation mode.

[0041] Enabling the system to evolve from a static tool into an intelligent agent that can continuously evolve with use, becoming more accurate and powerful with use; positive feedback from users reinforces successful paths, while failure cases are used for targeted improvement, continuously improving the quality of quantitative models, template libraries, and generation models.

[0042] The application also provides a dynamic multi-mode SQL generation system based on a four-quadrant strategy, for executing the dynamic multi-mode SQL generation method based on the four-quadrant strategy described above, comprising:

[0043] A semantic connection and quantitative evaluation module for connecting an enterprise-level semantic layer and performing feature extraction and quantitative scoring on user natural language queries, outputting a complexity total score C Score and a flexibility total score F Score ;

[0044] A four-quadrant strategy routing engine for receiving C Score and F Score , and dynamically routing the query request to one of the three execution modules through a double-layer decision gateway;

[0045] A template-based SQL generation module for generating SQL statements through template matching and parameter filling in the template-based SQL generation mode;

[0046] The generative SQL generation module is used to generate SQL statements in generative SQL generation mode by combining a custom named entity recognition model with dynamic context.

[0047] The proxy SQL generation module is used to plan and execute multi-step analysis tasks through intelligent agents in proxy SQL generation mode;

[0048] The data query and execution module is used to execute the generated SQL or subqueries and retrieve the results from the database.

[0049] Compared with the prior art, the beneficial effects of this invention are as follows:

[0050] (1) By integrating the originally contradictory “highly reliable templated query” and “highly flexible generative query” through a unified framework, the technical dilemma of the existing ChatBI system is fundamentally solved. The single and fixed processing mode of the traditional system is changed. The “preliminary evaluation” mechanism is introduced, which enables the system to dynamically select the optimal execution path according to the characteristics of the query request, just like an expert. By diverting different queries to the most suitable processing channel, the stability and accuracy of core business queries are guaranteed, and exploratory queries are also provided. As a result, the overall query success rate, resource utilization and user experience are improved.

[0051] (2) The total complexity score C is obtained through NLP intent recognition, atomic feature extraction and weighted calculation. Score And flexibility total score F Score It transforms subjective and vague natural language queries into objective and quantifiable mathematical scores, providing a unique and reliable basis for subsequent automated decision-making and avoiding inconsistencies in subjective judgments. By extracting multi-dimensional atomic features in parallel and performing weighted fusion, it achieves a more refined and accurate characterization of query "complexity" and "flexibility," far surpassing the simple judgment of a single rule.

[0052] (3) The logic is clear and the calculation is simple. It can complete the routing decision with extremely low latency, which meets the real-time requirements of interactive systems. Its deterministic algorithm also ensures the predictability of the results; it strictly follows the principle of "flexibility first, complexity second", ensuring that high deterministic queries must take the most reliable templated path, thereby ensuring the absolute accuracy of core business at the source;

[0053] (4) Semantic vector retrieval ensures that relevant templates can be found quickly (high recall), while entity verification ensures that the final selected template is logically complete and executable (high accuracy). Using pre-approved SQL templates and parameterized filling fundamentally eliminates SQL syntax errors and performance issues, while effectively preventing SQL injection attacks and achieving the high reliability required for enterprise-level applications. Attached Figure Description

[0054] Figure 1 This is a flowchart of the dynamic multi-model SQL generation method based on the four-quadrant strategy in an embodiment of the present invention;

[0055] Figure 2 This is a flowchart illustrating the acquisition and quantification of user queries in an embodiment of the present invention;

[0056] Figure 3 This is a flowchart illustrating the dynamic switching of SQL generation modes in an embodiment of the present invention. Detailed Implementation

[0057] The present invention will be further described below with reference to specific embodiments. The accompanying drawings are for illustrative purposes only, representing schematic diagrams rather than actual physical objects, and should not be construed as limiting the scope of this patent. To better illustrate the embodiments of the present invention, some components in the drawings may be omitted, enlarged, or reduced, and do not represent the actual dimensions of the product. It is understandable to those skilled in the art that some well-known structures and their descriptions may be omitted in the drawings.

[0058] In the accompanying drawings of the embodiments of the present invention, the same or similar reference numerals correspond to the same or similar components. In the description of the present invention, it should be understood that if terms such as "upper," "lower," "left," "right," etc., indicate the orientation or positional relationship based on the orientation or positional relationship shown in the drawings, they are only for the convenience of describing the present invention and simplifying the description, and do not indicate or imply that the device or element referred to must have a specific orientation, or be constructed and operated in a specific orientation. Therefore, the terms used to describe positional relationships in the drawings are only for illustrative purposes and should not be construed as limiting the present patent. For those skilled in the art, the specific meaning of the above terms can be understood according to the specific circumstances. Example

[0059] like Figure 1 As shown, a dynamic multi-model SQL generation method based on a four-quadrant strategy includes the following steps:

[0060] S1: Acquire and quantify the complexity and flexibility of user queries;

[0061] S2: Based on a four-quadrant strategy and decision gateway, dynamically switch SQL generation modes;

[0062] S3: In the selected SQL generation mode, perform data processing and analysis, and return the query results.

[0063] The embodiment integrates the originally opposite "high reliability template query" and "high flexibility generated query" through a unified framework, fundamentally solves the technical difficulties of the existing ChatBI system, changes the single and fixed processing mode of the traditional system, introduces a "pilot evaluation" mechanism, enables the system to dynamically select the optimal execution path according to the characteristics of the query request like an expert, and through the diversion of different queries to the most suitable processing channel, not only guarantees the stability and accuracy of the core business query, but also provides the possibility for exploratory query, thereby realizing higher query success rate, better resource utilization and better user experience as a whole.

[0064] In step S1, as shown in Figure 2 , obtaining and quantitatively evaluating the user query includes:

[0065] Receiving the user's text input through the interactive interface, and using natural language processing technology for intent recognition and entity extraction;

[0066] According to the user query and the pre-constructed enterprise-level semantic layer, atomic features including complexity atomic features and flexibility atomic features are extracted in parallel, and scores are calculated according to preset rules;

[0067] The weights of each atomic feature are configured, and the complexity total score C Score and the flexibility total score F Score are calculated by weighted summation respectively.

[0068] In the embodiment, the system is connected to a pre-constructed enterprise-level semantic layer, which contains the calculation logic of indicators (simple indicators, composite indicators), the association relationship between entities (table JOIN path), the synonym library of business terms, etc. By matching the entities of the user query with the semantic layer, the system can quantitatively evaluate the complexity (for example, the number of data tables to be associated, the nesting depth of calculation indicators) and flexibility (for example, whether the query mode is highly matched with the preset business scenario) of the query.

[0069] The complexity total score C Score and the flexibility total score F Score are obtained by NLP (Natural Language Processing) intent recognition, atomic feature extraction and weighted calculation; the subjective and fuzzy natural language query is converted into objective and quantifiable mathematical scores, providing a unique and reliable basis for subsequent automated decision-making, avoiding the inconsistency of subjective judgment. By extracting multi-dimensional atomic features in parallel and performing weighted fusion, the characterization of query "complexity" and "flexibility" is more fine and accurate, far superior to the simple judgment of a single rule.

[0070] Intention recognition is a key task in NLP, aiming to identify the user's true purpose or intention from the user's text or voice input. Through NLP intention recognition, the system can determine whether the user query is deterministic (e.g., "query total sales" - clear intention) or exploratory (e.g., "analyze the reasons for the decline in sales" - open intention), which is a key atomic feature for evaluating query "flexibility".

[0071] Complexity atomic features include structural complexity, index complexity, and constraint complexity; flexibility atomic features include intention certainty, template matching degree, and entity openness.

[0072] Structural complexity is scored based on the number of data tables required for association and path. Single-table queries are low, while cross-multi-fact table or complex snowflake model connections are high. For example: 1-2 tables get 1 point; 3-4 tables get 3 points, and more than 5 tables get 5 points.

[0073] Index complexity is scored based on the level of index calculation, measuring the difficulty of core index calculation. In the semantic layer, simple aggregation is low, multi-index ratio is medium, and analysis-type indicators involving window functions or complex CASE WHEN logic are high.

[0074] Constraint complexity is scored based on the logical level of filter conditions, measuring the complexity of WHERE conditions. Single equality filtering is low, multiple AND / OR conditions are medium, and subqueries or dynamic date calculations are high.

[0075] Intention certainty is scored by analyzing keywords and sentence patterns. "How much", "query", and other fact-type keywords indicate low flexibility, while "why", "analyze the reasons", and other exploratory keywords indicate high flexibility.

[0076] Template matching degree is scored by calculating the vector similarity between the query and the preset question template library, measuring the semantic matching degree between the query and the preset, fixed-structure "high-frequency question template library". High matching degree indicates low flexibility. For example: cosine similarity greater than 0.95 gets 0 points; between 0.7 and 0.95 gets 2 points; less than 0.7 gets 5 points.

[0077] Entity openness is scored based on the breadth of entities in the query, measuring the specificity of business entities in the query. For example, "iPhone 17 Pro Max sales" is a precise entity, indicating low flexibility, while "core product performance" is a broad entity, requiring the system to explore multiple dimensions, indicating high flexibility.

[0078] Through weighted summation, an accurate two-dimensional coordinate point (C Score , F ScoreThis completes the transformation from fuzzy language to precise mathematical expression, serving as the basis for all subsequent automated operations.

[0079] By limiting the complexity and flexibility to six atomic features, and closely integrating them with the actual scenarios of enterprise data querying, it is highly practical; the clear feature definition provides a clear structure and direction for subsequent adjustment of feature weights and improvement of evaluation accuracy through machine learning.

[0080] In step S2, the SQL generation mode is dynamically switched through a two-layer threshold decision gateway, such as... Figure 3 As shown, it includes:

[0081] Flexibility score: F Score Is it below the preset flexibility threshold F? threshold ;

[0082] If so, it is determined to be a low-flexibility query, and the query is switched to the templated SQL generation mode, with the path corresponding to the first and second quadrants in the four quadrants;

[0083] If not, it is determined to be a high-flexibility query, and the total complexity score is further evaluated. Score Is it below the preset complexity threshold C? threshold ;

[0084] If so, it is determined to be a low-complexity query, and the query is switched to generative SQL generation mode, with the path corresponding to the third quadrant in the four quadrants;

[0085] If not, it is determined to be a high-complexity query, and the system switches to proxy SQL generation mode, with the path corresponding to the fourth quadrant in the four-quadrant system.

[0086] This automatic switching process simplifies complex decision-making logic into a comparison of two values, thereby achieving accurate and efficient routing of different query requests. With clear logic and simple calculations, it can complete routing decisions with extremely low latency, meeting the real-time requirements of interactive systems. Its deterministic algorithm also ensures the predictability of the results; strictly adhering to the principle of "flexibility first, complexity second," it ensures that high-deterministic queries always take the most reliable templated path, thus guaranteeing the absolute accuracy of core business operations from the source.

[0087] When switching to templated SQL generation mode:

[0088] Template matching is performed through semantic vector retrieval and entity verification to determine the target SQL template from a preset template library; all standard questions in the "high-frequency question template library" are converted into high-dimensional vectors by a sentence vector model and stored in an efficient vector index database. When a user query is routed to this mode, the system converts it into a query vector using the same model, and then performs a similarity search in the index library to quickly find the most similar candidate template in terms of semantics;

[0089] Entities in the user query are identified by a custom named entity recognition model, and the entities are standardized to SQL condition expressions by querying the enterprise-level semantic layer; the custom named entity recognition model can identify entities and their types in the query text that have business meaning. The system queries the dictionary and business rules in the enterprise-level semantic layer for each identified entity to "translate" its text value into a standard value or expression executable by the backend database.

[0090] The standardized SQL condition expressions are safely filled into the target SQL template through parameterized query to generate an executable SQL statement.

[0091] In this embodiment, the template-based SQL generation method for the first quadrant and the second quadrant (low flexibility, arbitrary complexity): It is suitable for business scenarios such as core dashboards and fixed reports. When the query is determined to be fixed in structure, the system will invoke this method. This method matches in a pre-set, strictly audited parameterized SQL template library, and only extracts entities (such as "yesterday" and "East China") from user requests as parameters to fill in the WHERE condition of the SQL template, thereby generating a 100% accurate and high-performance SQL.

[0092] Semantic vector retrieval quickly retrieves the most similar candidate template in terms of semantics using a sentence vector model, ensuring that relevant templates can be quickly found (high recall rate). Entity verification checks whether the entities in the user query can completely cover the parameter slots of the candidate template through named entity recognition, ensuring that the finally selected template is logically complete and executable (high accuracy). Using the preset SQL template and parameterized filling, the system calculates a final confidence score that combines semantic similarity and entity coverage. Only when the score of the template is the highest and its confidence exceeds the preset threshold, the template is confirmed as a successful exact match, fundamentally eliminating SQL syntax errors and performance problems, effectively preventing SQL injection attacks, and achieving the high reliability required by enterprise-level applications.

[0093] When switching to the generated SQL generation mode:

[0094] According to the entities in the user query, the minimum relevant context is dynamically extracted from the enterprise-level semantic layer and provided as instant prompts to the custom named entity recognition model.

[0095] Identify entities and instant prompts in user queries through a custom named entity recognition model, and query the enterprise-level semantic layer to standardize instant prompts and fill in target SQL templates to provide conditional values for the generated executable SQL statements.

[0096] In this embodiment, the third quadrant (high flexibility, low complexity) generative SQL generation method: suitable for regular ad hoc queries by business personnel. When the query is determined to be structurally flexible and logically simple, the system invokes this method. This method uses a large language model, combined with the relevant context provided by the semantic layer (such as table structure, field annotations), to translate the user's natural language into SQL code in real time.

[0097] By dynamically supplying "minimized, high-relevance" context from the enterprise semantic layer, the "hallucination" problem of LLM (Large Language Model) is greatly reduced, making the generated SQL more closely aligned with the current state of enterprise data in terms of syntax and semantics. Even if the LLM is used to generate the SQL body, the WHERE condition values are still filled in by standardized parsing, ensuring the accuracy of key filtering conditions, combining the flexibility of generative and the precision of templated.

[0098] When switching to the proxy SQL generation mode:

[0099] Activate the intelligent agent based on the reasoning and action framework;

[0100] Decompose complex natural language queries into multiple logically coherent subqueries autonomously by the intelligent agent;

[0101] Dynamically plan the execution order for each subquery and recursively call the templated SQL generation mode or the generative SQL generation mode to execute the subqueries;

[0102] Aggregate the execution results of each subquery to generate a comprehensive analysis report.

[0103] In this embodiment, the fourth quadrant (high flexibility, high complexity) agent-based SQL generation method: suitable for open attribution, prediction and other deep analysis tasks. When the query intention is complex analysis, the system calls this method. This method activates an intelligent agent with thinking and action ability, which will decompose complex tasks into multiple sub-steps, and call the template SQL generation mode or the generated SQL generation mode to collect data, and finally integrate information to generate a comprehensive analysis report containing insight conclusions. Make the system able to handle complex analysis tasks (such as attribution analysis, root cause positioning) that cannot be completed with one SQL, greatly expanding the analysis depth and ability boundary of ChatBI system; Automate the thinking process of analysts manually "decompose problems-write multiple SQLs-integrate conclusions", empower business users to directly obtain deep analysis insights, and greatly improve analysis efficiency. Embodiment

[0104] A dynamic multi-mode SQL generation system based on a four-quadrant strategy for executing the dynamic multi-mode SQL generation method based on a four-quadrant strategy of embodiment one, comprising:

[0105] A semantic connection and quantitative evaluation module for connecting an enterprise-level semantic layer and performing feature extraction and quantitative scoring on user natural language queries, outputting complexity total score C Score and flexibility total score F Score ;

[0106] A four-quadrant strategy routing engine for receiving C Score and F Score , and dynamically routing query requests to one of the three execution modules through a double-layer decision gateway;

[0107] A template SQL generation module for generating SQL statements through template matching and parameter filling in the template SQL generation mode;

[0108] A generated SQL generation module for generating SQL statements using a custom named entity recognition model combined with dynamic context in the generated SQL generation mode;

[0109] An agent-based SQL generation module for planning and executing multi-step analysis tasks through an intelligent agent in the agent-based SQL generation mode;

[0110] A data query and execution module for executing generated SQL or subqueries and obtaining results from a database.

[0111] Wherein, collect feedback data of users on query results; based on the feedback data, continuously optimize the weight configuration of atomic features, expand the template library of the template SQL generation mode, and / or fine-tune the large language model used by the generated SQL generation mode.

[0112] This embodiment constructs a learning and evolution closed loop. The system records user feedback and successful query cases, continuously expands and optimizes the SQL template library, and uses failure cases to fine-tune the generative model. This continuously enhances the capabilities of the entire system, achieving a comprehensive effect of ensuring the stability of core data queries, improving the flexibility of exploratory analysis, lowering the threshold for data use, and saving manpower costs for the data team. This allows the system to evolve from a static tool into an intelligent agent that can continuously evolve with use, becoming more accurate and stronger with each use. Positive user feedback reinforces the successful path, while failure cases are used for targeted improvements, continuously enhancing the quality of the quantitative model, template library, and generative model.

[0113] This invention integrates the previously opposing concepts of "highly reliable templated queries" and "highly flexible generative queries" through a unified framework, fundamentally solving the technical dilemmas of existing ChatBI systems. It changes the traditional system's single, fixed processing mode by introducing a "leader evaluation" mechanism, enabling the system to dynamically select the optimal execution path based on the characteristics of the query request, much like an expert. By diverting different queries to the most suitable processing channels, it ensures the stability and accuracy of core business queries while also providing possibilities for exploratory queries, thereby achieving a higher overall query success rate, better resource utilization, and a better user experience. The total complexity score C is obtained through NLP intent recognition, atomic feature extraction, and weighted calculation. Score And flexibility total score F Score This system transforms subjective and ambiguous natural language queries into objective and quantifiable mathematical scores, providing a unique and reliable basis for subsequent automated decision-making and avoiding inconsistencies arising from subjective judgments. By extracting multi-dimensional atomic features in parallel and performing weighted fusion, it achieves a more refined and accurate characterization of query "complexity" and "flexibility," far surpassing simple judgments based on single rules. Its clear logic and simple computation enable routing decisions with extremely low latency, meeting the real-time requirements of interactive systems. Its deterministic algorithm also ensures predictable results; strictly adhering to the principle of "flexibility first, complexity second," it ensures that high-deterministic queries always follow the most reliable templated path, thus guaranteeing the absolute accuracy of core business processes at the source. Semantic vector retrieval ensures rapid retrieval of relevant templates (high recall), while entity verification ensures that the final selected template is logically complete and executable (high accuracy). Using pre-approved SQL templates and parameterized filling fundamentally eliminates SQL syntax errors and performance issues, while effectively preventing SQL injection attacks, achieving the high reliability required for enterprise-level applications.

[0114] Obviously, the above embodiments of the present application are merely exemplary but not intended to limit the embodiments of the present application. Based on the above description, any other variations or changes can be made by those skilled in the art without departing from the spirit and principles of the present application. It is not necessary to list all the embodiments here. Any modifications, equivalent replacements, and improvements made within the spirit and principles of the present application shall fall within the scope of the claims of the present application.

Claims

1. A dynamic multi-model SQL generation method based on a four-quadrant strategy, characterized in that, Includes the following steps: S1: Acquire and quantify the complexity and flexibility of user queries; S2: Based on a four-quadrant strategy and decision gateway, dynamically switch SQL generation modes; S3: In the selected SQL generation mode, perform data processing and analysis, and return the query results; In step S1, obtaining and quantifying user queries includes: The system receives text input from users through an interactive interface and uses natural language processing technology to perform intent recognition and entity extraction. Based on user queries and a pre-built enterprise-level semantic layer, atomic features are extracted in parallel. These atomic features include complexity atomic features and flexibility atomic features, and are scored according to preset rules. Weights are assigned to each atomic feature, and the total complexity score C is calculated by weighted summation. Score And flexibility total score F Score ; The atomic features of complexity include structural complexity, index complexity, and constraint complexity; the atomic features of flexibility include intent determinism, template matching degree, and entity openness. In step S2, the SQL generation mode is dynamically switched through a two-layer threshold decision gateway, including: Determine the total flexibility score F Score Is it below the preset flexibility threshold F? threshold ; If so, it is determined to be a low-flexibility query, and the query is switched to the templated SQL generation mode, with the path corresponding to the first and second quadrants in the four quadrants; If not, it is determined to be a high-flexibility query, and the total complexity score C is further evaluated. Score Is it below the preset complexity threshold C? threshold ; If so, it is determined to be a low-complexity query, and the query is switched to generative SQL generation mode, with the path corresponding to the third quadrant in the four quadrants; If not, it is determined to be a high-complexity query, and the system switches to proxy SQL generation mode, with the path corresponding to the fourth quadrant in the four-quadrant system.

2. The dynamic multi-model SQL generation method based on the four-quadrant strategy according to claim 1, characterized in that, When switching to the templated SQL generation mode: Template matching is performed using semantic vector retrieval and entity verification to determine the target SQL template from a pre-set template library; The system identifies entities in user queries using a custom named entity recognition model and then queries the enterprise-level semantic layer to standardize the entities into SQL conditional expressions. The standardized SQL conditional expression is safely populated into the target SQL template through parameterized query to generate an executable SQL statement.

3. The dynamic multi-model SQL generation method based on the four-quadrant strategy according to claim 2, characterized in that, When switching to the generative SQL generation mode: Based on the entities in the user query, the minimal and relevant context is dynamically extracted from the enterprise-level semantic layer and provided as an instant prompt to the custom named entity recognition model; The system identifies entities and instant prompts in user queries using a custom named entity recognition model, and then queries the enterprise-level semantic layer to standardize the instant prompts and populate them into the target SQL template, providing condition values ​​for the generated executable SQL statement.

4. The dynamic multi-model SQL generation method based on the four-quadrant strategy according to claim 3, characterized in that, When switching to the agent-based SQL generation mode: Activate intelligent agents based on reasoning and action frameworks; The intelligent agent autonomously decomposes complex natural language queries into multiple logically coherent subqueries; The execution order of each subquery is dynamically planned, and the templated SQL generation mode or the generative SQL generation mode is called repeatedly to execute the subquery; Summarize the execution results of each subquery and generate a comprehensive analysis report.

5. The dynamic multi-model SQL generation method based on the four-quadrant strategy according to claim 4, characterized in that, Collect user feedback data on query results; based on the feedback data, continuously optimize the weight configuration of the atomic features, expand the template library of the templated SQL generation pattern, and / or fine-tune the large language model used by the generative SQL generation pattern.

6. A dynamic multi-model SQL generation system based on a four-quadrant strategy, used to execute the dynamic multi-model SQL generation method based on a four-quadrant strategy as described in any one of claims 1 to 5, characterized in that, include: The semantic connectivity and quantitative evaluation module connects to the enterprise-level semantic layer and performs feature extraction and quantitative scoring on user natural language queries, outputting a total complexity score C. Score And flexibility total score F Score ; The four-quadrant policy routing engine is used to receive the C Score and F Score And through a two-layer decision gateway, query requests are dynamically routed to the templated SQL generation module, the generative SQL generation module, or the proxy SQL generation module; The templated SQL generation module is used to generate SQL statements in templated SQL generation mode through template matching and parameter filling. The generative SQL generation module is used to generate SQL statements in generative SQL generation mode by combining a custom named entity recognition model with dynamic context. The proxy SQL generation module is used to plan and execute multi-step analysis tasks through intelligent agents in proxy SQL generation mode; The data query and execution module is used to execute the generated SQL or subqueries and retrieve the results from the database.

Images