A Method and System for Generating Structured Query Language Based on Multidimensional Complexity Evaluation

Abstract

This invention relates to the field of natural language and database interaction technology, specifically to a method and system for generating structured query language based on multidimensional complexity assessment, comprising the following steps executed sequentially: S1: Obtain or receive a user question and a target database schema; S2: Obtain the multi-table path span score, logical nesting depth score, and aggregation level score of the user question; obtain the weight parameters corresponding to these three dimensions; calculate the comprehensive complexity score of the user question based on the scores of each dimension and their corresponding weight parameters; S3: If the comprehensive complexity score is less than or equal to a preset threshold, directly drive the LLM to convert the user question into a corresponding SQL statement; otherwise, initiate dual-path generation of candidate SQL statements; S4: Perform consistency assessment and correction on the candidate SQL statements; thereby improving the stability and engineering applicability of the system in complex operation and maintenance analysis scenarios.

Description

Technical Field

[0001] This invention relates to the field of natural language and database interaction technology, specifically to a method and system for generating structured query language based on multidimensional complexity evaluation. Background Technology

[0002] Automatic conversion technology from Natural Language (Text) to Structured Query Language (SQL) serves as a crucial bridge connecting natural language and database systems. In industrial operations and maintenance, quickly obtaining equipment information using natural language amidst massive amounts of equipment records and fault logs presents a significant challenge. In recent years, with the development of Large Language Models (LLMs), Text-to-SQL methods based on prompting or a limited number of examples have made significant progress. However, existing LLM-based Text-to-SQL systems typically rely solely on implicit reasoning within the LLM to perceive query difficulty, failing to explicitly and interpretably model the complexity of user queries at the SQL reasoning level. This makes it difficult for relevant complexity information to participate in and constrain the SQL generation process in a structured and controllable manner.

[0003] This systemic deficiency is particularly prominent in the analysis of industrial equipment operation and maintenance and fault logs. User questions in this scenario vary significantly in structure, ranging from simple single-table queries, such as "query the current temperature of device A," to queries involving cross-table joins, time-series window aggregation, and complex nested logic, such as "find servers whose average CPU load exceeds a threshold within 24 hours after a firmware upgrade but has not triggered a shutdown." Because complex information cannot be generated in a structured and controllable manner, existing systems typically employ a uniform generation strategy to handle query requirements with different inference structures. In this situation, simple queries can easily introduce unnecessary structural redundancy, while complex queries may suffer from missing inference chains or incomplete logical constraints, leading to instability in query results and even risks such as missed or false alarms. Summary of the Invention

[0004] The purpose of this invention is to provide a structured query language generation method and system based on multidimensional complexity evaluation to improve query accuracy.

[0005] To achieve the above objectives, the present invention adopts the following technical solution: The structured query language generation method based on multidimensional complexity evaluation includes the following steps performed sequentially: S1: Obtain or receive user questions and target database schema; S2: By calculating the number of associated edges in the minimum Steiner tree and using a piecewise function mapping, the multi-table path span score of the user problem is obtained. By constructing a logical operator tree to extract the nesting level and using a Logistic function mapping, the logical nesting depth score of the user problem is obtained. By identifying aggregation and grouping operations and using a truncation function to calculate, the aggregation level score of the user problem is obtained. The weight parameters of the three scores are obtained. The scores of each dimension are weighted and summed with the corresponding weight parameters to obtain the comprehensive complexity score of the user problem. S3: If the overall complexity score is less than or equal to the preset threshold, the LLM is directly driven to convert the user's question into the corresponding SQL statement; otherwise, the dual-path parallel generation mechanism is started. The specific components of the dual-path parallel generation mechanism include: The first path is a multi-dimensional feature-guided decomposition path: the scores of the complexity of the three dimensions calculated in step S2 are compared to determine the decomposition strategy. When the multi-table path span score is the highest, the user problem is preferentially decomposed into two sequential sub-steps: conditional filtering and inter-table association. When the logical nesting depth score is the highest, the complex nesting intent is broken down into multiple query subtasks that are progressively based on dependencies. When the aggregation level score is the highest, the decomposition process is limited to three stages: data pre-filtering, group calculation, and aggregation result constraint. Based on the determined decomposition strategy, prompt instructions are constructed to drive the LLM to complete the logical decomposition of the user's question and the generation of SQL fragments corresponding to each sub-question. Finally, the generated SQL fragments are logically integrated according to the dependencies between sub-questions, and converged into a first candidate SQL statement whose structure adapts to the original intent. ; The second path is the opposition-heuristic example path: It receives the user question, the target database schema, the complexity scores of the three dimensions, and the logical operator tree constructed in step S2. Using the complexity scores of the three dimensions and the logical operator tree as generation conditions, it constructs a generation instruction oriented towards a preset complexity pattern, driving the LLM to generate a pair of contrasting examples containing positive guidance and negative traps. The contrasting example pairs, along with the user question and the target database schema, are input into the LLM as part of the prompt words to constrain its generation and output of a second candidate SQL statement. ; S4: Regarding the first candidate SQL statement and the second candidate SQL statement Conduct consistency assessment and correction.

[0006] Preferably, the specific calculation steps for the comprehensive complexity score in step S2 are as follows: S2-1: Use a semantic embedding model to retrieve the fields involved in the user question and map them to the corresponding tables in the target database schema. Mark the corresponding tables as target nodes in the architecture relationship graph of the target database. Calculate the minimum Steiner tree required to connect all target nodes to obtain the minimum number of associated edges. The multi-table path span score is represented by the following piecewise function. : ; S2-2: Constructing a logical operator tree using LLM, defining... The logical nesting depth score represents the logical nesting level of this logical operator tree from the root node to the deepest leaf node. The following Logistic function is used to represent it: ; Where e is a constant; S2-3: Aggregate level scores include basic aggregate scores. Grouping Dimension Gain Score and nested compensation scores The specific calculation steps are as follows: Extract logical operators from the logical operator tree, identify the types of aggregate operators included, and determine the basic aggregate score of the corresponding operator according to the system's preset aggregate operator scoring rules. ; Introducing a grouping dimension gain score, based on the number of grouping fields involved in the logical structure. The grouping dimension gain score is expressed by linear accumulation using the following formula. : ; in, This is the preset grouping complexity coefficient; If recursive calls to aggregation operators are involved, nested compensation scores should be considered. A fixed compensation score of 0.2 is assigned; otherwise, no compensation score is assigned. The aggregate level score is calculated using a truncation function. ; S2-5: Obtain the weight parameters of the complexity in the three dimensions; S2-6: For user questions, calculate their feature scores across three dimensions. This weighting parameter is used to weight and map the scores of each item to calculate the overall complexity score. : ; in, This indicates the weight of the score for the corresponding dimension.

[0007] Preferably, the specific process for obtaining the weight parameters of the complexity in the three dimensions in step S2-5 is as follows: S2-5-1: Using the public dataset, calculate its score on the complexity of the above three dimensions respectively, map the score to the logarithmic space, and construct the feature difference vector. , of which Feature difference in each dimension The calculation formula is: ; in, Representing the original question Dimensional scores, Representative subproblem set The mean score of each dimension; S2-5-2: Based on the original problem The overall complexity score and its set of subproblems The difference between the average comprehensive complexity scores is used as the target variable. Construct the feature difference vector The objective function is optimized for the input parameters. Regularization constraints are introduced during the parameter fitting process to minimize the error between the prediction result and the target variable, thereby learning the weight parameters of the complexity of each dimension.

[0008] Preferably, in step S4, each of the candidate SQL statements is executed in the target database schema, and the corresponding execution status and result set are obtained. A consistency determination is performed on the execution status and the result set. If the determination is successful, any of the candidate SQL statements is output. Otherwise, the two candidate SQL statements are corrected. The two candidate SQL statements, the user question, the target database schema, the complexity scores of the three dimensions, and the logical operator tree are used to construct a structured prompt for correction. The LLM is then driven to rewrite the SQL. The corrected statement will re-enter the execution consistency determination process until the execution result is consistent or the iteration limit is reached.

[0009] Preferably, the method further includes exception feedback guidance: if the candidate SQL statement fails to execute, the error information returned by the database is extracted, and the error information, along with the user problem and the target database schema, is used as a feedback signal and input to the corresponding LLM to trigger targeted correction. The corrected statement will be re-executed for consistency determination.

[0010] A structured query language generation system based on multidimensional complexity assessment includes a problem complexity assessment module, a multidimensional feature-guided decomposition module, an opposition heuristic example module, and an execution consistency assessment and correction module. The problem complexity assessment module is used to obtain the multi-table path span score of the user problem by calculating the number of associated edges of the minimum Steiner tree and using a piecewise function mapping; to obtain the logical nesting depth score of the user problem by constructing a logical operator tree to extract the nesting level and using a Logistic function mapping; to obtain the aggregation level score of the user problem by identifying aggregation and grouping operations and using a truncation function calculation; to obtain the weight parameters of the three scores; and to obtain the comprehensive complexity score of the user problem by weighted summing of the scores of each dimension and the corresponding weight parameters. The multi-dimensional feature-guided decomposition module is used to make a judgment based on the comprehensive complexity score. If the comprehensive complexity score is less than or equal to a preset threshold, the LLM is directly driven to convert the user's question into the corresponding SQL statement. Otherwise, the dual-path parallel generation mechanism is started. The specific components of the dual-path parallel generation mechanism include: The first path is a multi-dimensional feature-guided decomposition path: the scores of the complexity of the three dimensions calculated in step S2 are compared to determine the decomposition strategy. When the multi-table path span score is the highest, the user problem is preferentially decomposed into two sequential sub-steps: conditional filtering and inter-table association. When the logical nesting depth score is the highest, the complex nesting intent is broken down into multiple query subtasks that are progressively based on dependencies. When the aggregation level score is the highest, the decomposition process is limited to three stages: data pre-filtering, group calculation, and aggregation result constraint. Based on the determined decomposition strategy, prompt instructions are constructed to drive the LLM to complete the logical decomposition of the user's question and the generation of SQL fragments corresponding to each sub-question. Finally, the generated SQL fragments are logically integrated according to the dependencies between sub-questions, and converged into a first candidate SQL statement whose structure adapts to the original intent. ; The second path is the opposition-heuristic example path: It receives the user question, the target database schema, the complexity scores of the three dimensions, and the logical operator tree constructed in step S2. Using the complexity scores of the three dimensions and the logical operator tree as generation conditions, it constructs a generation instruction oriented towards a preset complexity pattern, driving the LLM to generate a pair of contrasting examples containing positive guidance and negative traps. The contrasting example pairs, along with the user question and the target database schema, are input into the LLM as part of the prompt words to constrain its generation and output of a second candidate SQL statement. ; The consistency evaluation and correction module is used to evaluate the first candidate SQL statement. and the second candidate SQL statement Perform consistency assessment and correction, and output the execution results.

[0011] Preferably, the specific calculation steps for the comprehensive complexity score are as follows: A semantic embedding model is used to retrieve the fields involved in the user's question and map them to the corresponding tables in the target database schema. These corresponding tables are then marked as target nodes in the target database's architecture graph. The minimum Steiner tree required to connect all target nodes is calculated to obtain the minimum number of foreign key association edges. The multi-table path span score is represented by the following piecewise function. : ; Using LLM to construct logical operator trees, define The logical nesting depth score represents the logical nesting level of this logical operator tree from the root node to the deepest leaf node. The following Logistic function is used to represent it: ; Where e is a constant; Aggregate level scores include basic aggregate scores. Grouping Dimension Gain Score and nested compensation scores The specific calculation steps are as follows: Extract logical operators from the logical operator tree, identify the types of aggregate operators included, and determine the basic aggregate score of the corresponding operator according to the system's preset aggregate operator scoring rules. ; Introducing a grouping dimension gain score, based on the number of grouping fields involved in the logical structure. The grouping dimension gain score is expressed by linear accumulation using the following formula. : ; in, This is the preset grouping complexity coefficient; If recursive calls to aggregation operators are involved, nested compensation scores should be considered. A fixed compensation score of 0.2 is assigned; otherwise, no compensation score is assigned. The aggregate level score is calculated using a truncation function. ; Obtain the weight parameters for the complexity scores across the three dimensions; For this user's question, calculate its feature scores across three dimensions. This weighting parameter is used to weight and map the scores of each item to calculate the overall complexity score. : ; in, This indicates the weight of the score for the corresponding dimension.

[0012] Preferably, the specific process for obtaining the weight parameters of the complexity in the three dimensions is as follows: Using a publicly available dataset, we calculate the scores for complexity in the three dimensions mentioned above, map the scores to logarithmic space, and construct feature difference vectors. , of which Feature difference in each dimension The calculation formula is: ; in, Representing the original question Dimensional scores, Representative subproblem set The mean score of each dimension; Based on the original question The overall complexity score and its set of subproblems The difference between the average comprehensive complexity scores is used as the target variable. Construct the feature difference vector The objective function is optimized for the input parameters. Regularization constraints are introduced during the parameter fitting process to minimize the error between the prediction result and the target variable, thereby learning the weight parameters of the complexity of each dimension.

[0013] Preferably, the execution consistency assessment and correction module executes each of the candidate SQL statements in the target database schema and obtains the corresponding execution status and result set. It performs a consistency judgment on the execution status and the result set. If the judgment is successful, it outputs any of the candidate SQL statements. Otherwise, it corrects the two candidate SQL statements. It constructs a structured prompt for correction using the two candidate SQL statements, the user question, the target database schema, the complexity scores of the three dimensions, and the logical operator tree. It then drives the LLM to rewrite the SQL. The corrected statement will re-enter the execution consistency judgment process until the execution result is consistent or the iteration limit is reached. If the candidate SQL statement fails to execute, the error information returned by the database is extracted and used as a feedback signal along with the user problem and the target database schema. This signal is then input into the corresponding LLM to trigger targeted correction. The corrected statement will be re-entered into the execution consistency assessment and correction module for execution consistency determination.

[0014] By adopting the aforementioned design scheme, the beneficial effects of this invention are as follows: This invention addresses the problem of large differences in query structure in the analysis scenario of industrial equipment operation and maintenance and fault logs. It provides an adaptively adjustable query language generation mechanism. By introducing a multi-dimensional complexity scoring mechanism to quantify the complexity of the problem, the complexity evaluation results are introduced into the SQL generation and verification process in the form of continuous control signals. This enables the generation process to adaptively adjust according to the differences in query reasoning structure, thereby improving the stability and engineering applicability of the system in complex operation and maintenance analysis scenarios. Attached Figure Description

[0015] Figure 1 This is a schematic diagram of the system flow of the present invention; Figure 2 This is a schematic diagram illustrating the multidimensional feature calculation principle of the problem complexity assessment module. Detailed Implementation

[0016] To make the objectives, technical solutions, and advantages of this invention clearer, the invention will be further described in detail below with reference to the accompanying drawings. Obviously, the described embodiments are merely some embodiments of this invention, and not all embodiments. Based on the embodiments of this invention, all other embodiments obtained by those skilled in the art without creative effort are within the scope of protection of this invention.

[0017] The terms "first," "second," "third," etc., used in the specification, claims, and accompanying drawings of this invention are used to distinguish different objects, not to describe a specific order. Furthermore, the terms "comprising" and "having," and any variations thereof, are intended to cover non-exclusive inclusion. For example, a process, method, system, product, or apparatus that includes a series of steps or units is not limited to the listed steps or units, but may optionally include steps or units not listed, or may optionally include other steps or units inherent to these processes, methods, products, or apparatuses.

[0018] The structured query language generation method based on multidimensional complexity evaluation includes the following steps performed sequentially: S1: Obtain or receive user questions and target database schema; S2: By calculating the number of associated edges in the minimum Steiner tree and using a piecewise function mapping, the multi-table path span score of the user problem is obtained. By constructing a logical operator tree to extract the nesting level and using a Logistic function mapping, the logical nesting depth score of the user problem is obtained. By identifying aggregation and grouping operations and using a truncation function to calculate, the aggregation level score of the user problem is obtained. The weight parameters of the three scores are obtained. The scores of each dimension are weighted and summed with the corresponding weight parameters to obtain the comprehensive complexity score of the user problem. In this embodiment, the specific steps for calculating the overall complexity score are as follows: S2-1: Use a semantic embedding model to retrieve the fields involved in the user's question and map them to the corresponding tables in the target database schema. Mark the corresponding tables as target nodes in the architecture graph of the target database. Calculate the minimum Steiner tree required to connect all target nodes to obtain the minimum number of foreign key association edges. The multi-table path span score is represented by the following piecewise function. : ; Multi-table path span score The aim is to quantify the structural complexity of inter-table joins.

[0019] S2-2: Constructing a Logic Operator Tree (LOT) using LLM, defining... The logical nesting depth score represents the logical nesting level of the logical operator tree from the root node to the deepest leaf node. The following Logistic function is used to represent it: ; Where e is a constant; Logical nesting depth score The aim is to quantify the logical complexity of user intent.

[0020] S2-3: Aggregate level scores include basic aggregate scores. Grouping Dimension Gain Score and nested compensation scores The specific calculation steps are as follows: Logical operators are extracted from the logical operator tree (LOT), and the types of aggregate operators included are identified, such as COUNT, SUM, AVG, MAX / MIN, etc. Based on the system's preset aggregate operator scoring rules, the basic aggregate score of the corresponding operator is determined. In this embodiment, the basic aggregation score is obtained based on the mapping table values ​​of different operators in SQL inference. ,like ; Introducing a grouping dimension gain score, based on the number of grouping fields involved in the logical structure. The grouping dimension gain score is expressed by linear accumulation using the following formula. : ; in, In this embodiment, the preset grouping complexity coefficient is set as follows: ; If recursive calls to aggregation operators are involved, nested compensation scores should be considered. A fixed compensation score of 0.3 is assigned; otherwise, no compensation score is assigned. The aggregate level score is calculated using a truncation function. .

[0021] Aggregate level score The aim is to quantify the complexity of data aggregation and grouping operations in user questions.

[0022] S2-4: Obtain the weight parameters of the complexity in the three dimensions; In this embodiment, the weight parameters for the complexity of each dimension are not preset manually, but are automatically obtained through a parameter learning mechanism based on sample statistical distribution. The specific process is as follows: S2-4-1: Utilizing public datasets, such as complex problems in Spider-Dec and its corresponding set of subproblems Calculate the complexity scores for each of the four dimensions mentioned above. Map the scores to logarithmic space to construct the feature difference vector. , of which Feature difference in each dimension The calculation formula is: ,in, Representing the original question Dimensional scores, Representative subproblem set The mean score of each dimension.

[0023] S2-4-2: Using the overall complexity difference as the target variable Construct the feature difference vector To optimize the objective function for the input parameters, regularization constraints are introduced during the parameter fitting process to minimize the error between the predicted result and the target variable, thereby learning the weight parameters for complexity in each dimension. These regularization constraints suppress the correlation between complexity features of different dimensions, improve the stability and generalization ability of the weight estimation process, and ensure that the learned weight parameters accurately reflect the relative contribution of each dimension's complexity to the overall complexity change.

[0024] The weight parameters for the complexity of each dimension can be obtained through the above parameter learning process as follows: , , .

[0025] S2-5: For user questions, calculate their feature scores across three dimensions. Subsequently, the learned weights are used to perform a weighted mapping on the scores of each item, and the overall complexity score is calculated. : .

[0026] For example, in an industrial equipment operation and maintenance and fault log analysis scenario, a user might query: "Find the workshop with the longest average fault repair time among all workshops that triggered 'sensor anomaly' alarms last month." To address this user's question, a multi-dimensional quantitative analysis is performed. In terms of multi-table path span, the system identifies the entities involved: "workshop" (workshop information table), the event "sensor anomaly" (alarm log table), and the attribute "fault repair time" (maintenance work order table). By calculating the minimum Steiner tree connecting these three entity tables, the minimum number of associated edges is obtained. The score was 0.7. In terms of logical nesting depth: This problem not only involves time filtering and event status query, but also requires multi-level aggregation operations through nested subqueries. The system constructs a logical operator tree to measure the logical nesting level from the root node to the deepest leaf node. The score was 0.73. At the aggregation level: The problem requires "calculating the average repair time by workshop and then finding the maximum value among them". The system identifies two aggregation operators: AVG and MAX (based on a preset scoring rule, the highest base score of these two operators is taken as the base aggregation score). ) and one grouping field "Workshop" (grouping dimension gain score) Simultaneously, this problem requires maximizing the average repair time again, involving recursive calls to aggregation operators and assigning nested compensation scores. The final score was 0.6.

[0027] The scores of the above dimensions are weighted and summed to obtain a score of 0.677 for the query, which exceeds the preset threshold, set to 0.6 in this embodiment.

[0028] When the overall complexity score If the score is below a set threshold, the LLM will be directly driven to convert the user's question into a corresponding SQL statement; when the overall complexity score is... If the threshold is exceeded, a multi-path generation mechanism will be gradually introduced to enhance the generation stability in complex reasoning scenarios.

[0029] S3: If the overall complexity score is less than or equal to the preset threshold, the LLM is directly driven to convert the user's question into the corresponding SQL statement; otherwise, the dual-path parallel generation mechanism is started. Enabling the dual-path parallel generation mechanism specifically includes: The first path is a multi-dimensional feature-guided decomposition path: the scores of the complexity of the three dimensions calculated in step S2 are compared to determine the decomposition strategy. When the multi-table path span score is the highest, the user problem is preferentially decomposed into two sequential sub-steps: conditional filtering and inter-table association. When the logical nesting depth score is the highest, the complex nesting intent is broken down into multiple query subtasks that are progressively based on dependencies. When the aggregation level score is the highest, the decomposition process is limited to three stages: data pre-filtering, group calculation, and aggregation result constraint. Based on the determined decomposition strategy, prompt instructions are constructed to drive the LLM to complete the logical decomposition of the user's question and the generation of SQL fragments corresponding to each sub-question. Finally, the generated SQL fragments are logically integrated according to the dependencies between sub-questions, and converged into a first candidate SQL statement whose structure adapts to the original intent. ; The second path is the opposition-heuristic example path: It receives the user question, the target database schema, the complexity scores of the three dimensions, and the logical operator tree constructed in step S2. Using the complexity scores of the three dimensions and the logical operator tree as generation conditions, it constructs a generation instruction oriented towards a preset complexity pattern, driving the LLM to generate a pair of contrasting examples containing positive guidance and negative traps. The contrasting example pairs, along with the user question and the target database schema, are input into the LLM as part of the prompt words to constrain its generation and output of a second candidate SQL statement. ; For example, for the user question "Find the workshop with the longest average fault repair time among all workshops that triggered the 'sensor abnormality' alarm last month", this question has the highest score in the dimension of logical nesting depth. The system decomposes it into the following sub-problem sequence based on the nesting condition expansion strategy: (1) Find all machine tools and their respective workshops that triggered the 'sensor abnormality' alarm last month, and obtain the corresponding maintenance work orders; (2) Based on the results of sub-problem 1, group by workshop and calculate the average fault repair time of each workshop; (3) Based on the statistical results of sub-problem 2, select the workshop with the longest average repair time.

[0030] The multi-dimensional feature-guided decomposition described above enables the problem to have a clear logical progression relationship and significantly reduces its complexity.

[0031] Based on the multi-dimensional quantitative scores generated from the problem complexity assessment and the logical operator tree constructed in the aforementioned steps, a logically targeted heuristic hint context is built for the target query. Unlike traditional methods that rely on static examples, this method dynamically generates "positive guidance-reverse trap" example pairs by analyzing the complexity scores and specific structural features of each dimension.

[0032] S4: Regarding the first candidate SQL statement and the second candidate SQL statement The following steps are involved in conducting a consistency assessment and correction: Each candidate SQL statement is executed in the target database schema, and the corresponding execution status and result set are obtained. The consistency between the execution status and the result set is determined: if each candidate SQL statement is executed successfully and the returned result set is completely consistent, the current query task is determined to have achieved logical closure, any one of the candidate SQL statements is output, and the process ends.

[0033] Logical conflict resolution and adaptive correction: If the execution result sets of candidate SQL statements differ, they are identified as logical conflicts; The two candidate SQL statements, the user question, the target database schema, the complexity scores of the three dimensions, and the logical operator tree built in the previous steps are used to construct structured prompt words for correction, which drive the LLM to rewrite the SQL. The corrected statement will re-enter the execution consistency determination process until the execution result is consistent or the iteration limit is reached.

[0034] Error feedback guidance: If the candidate SQL statement fails to execute, the error information returned by the database is extracted, and it, along with the user's problem and the target database schema, is used as a feedback signal and input to the corresponding LLM to trigger targeted correction. The corrected statement will then be re-executed for consistency determination.

[0035] This embodiment also provides a structured query language generation system based on multidimensional complexity evaluation for implementing the above method.

[0036] like Figures 1-2 As shown, the structured query language generation system based on multidimensional complexity evaluation includes a problem complexity evaluation module, a multidimensional feature-guided decomposition module, an opposition heuristic example module, and an execution consistency evaluation and correction module. The problem complexity assessment module is used to obtain the multi-table path span score of the user problem by calculating the number of associated edges of the minimum Steiner tree and using a piecewise function mapping; to obtain the logical nesting depth score of the user problem by constructing a logical operator tree to extract the nesting level and using a Logistic function mapping; to obtain the aggregation level score of the user problem by identifying aggregation and grouping operations and using a truncation function calculation; to obtain the weight parameters of the three scores; and to obtain the comprehensive complexity score of the user problem by weighted summing of the scores of each dimension and the corresponding weight parameters. The determination is made based on the comprehensive complexity score. If the comprehensive complexity score is less than or equal to a preset threshold, the LLM is directly driven to convert the user's question into the corresponding SQL statement. Otherwise, the dual-path parallel generation mechanism is started. The specific components of the dual-path parallel generation mechanism include: The first path is a multi-dimensional feature-guided decomposition path: the scores of the complexity of the three dimensions calculated in step S2 are compared to determine the decomposition strategy. When the multi-table path span score is the highest, the user problem is preferentially decomposed into two sequential sub-steps: conditional filtering and inter-table association. When the logical nesting depth score is the highest, the complex nesting intent is broken down into multiple query subtasks that are progressively based on dependencies. When the aggregation level score is the highest, the decomposition process is limited to three stages: data pre-filtering, group calculation, and aggregation result constraint. Based on the determined decomposition strategy, prompt instructions are constructed to drive the LLM to complete the logical decomposition of the user's question and the generation of SQL fragments corresponding to each sub-question. Finally, the generated SQL fragments are logically integrated according to the dependencies between sub-questions, and converged into a first candidate SQL statement whose structure adapts to the original intent. ; The second path is the opposition-heuristic example path: It receives the user question, the target database schema, the complexity scores of the three dimensions, and the logical operator tree constructed in step S2. Using the complexity scores of the three dimensions and the logical operator tree as generation conditions, it constructs a generation instruction oriented towards a preset complexity pattern, driving the LLM to generate a pair of contrasting examples containing positive guidance and negative traps. The contrasting example pairs, along with the user question and the target database schema, are input into the LLM as part of the prompt words to constrain its generation and output of a second candidate SQL statement. ; The consistency evaluation and correction module is used to evaluate the first candidate SQL statement. and the second candidate SQL statement Perform consistency assessment and correction, and output the execution results.

[0037] The specific calculation steps for the comprehensive complexity score are as follows: A semantic embedding model is used to retrieve the fields involved in the user's question and map them to the corresponding tables in the target database schema. The corresponding tables are marked as target nodes in the architecture graph of the target database. The minimum Steiner tree required to connect all target nodes is calculated to obtain the minimum number of foreign key association edges. The multi-table path span score is represented by the following piecewise function. : ; Using LLM to construct logical operator trees, define The logical nesting depth score represents the logical nesting level of this logical operator tree from the root node to the deepest leaf node. The following Logistic function is used to represent it: ; Where e is a constant; Aggregate level scores include basic aggregate scores. Grouping Dimension Gain Score and nested compensation scores The specific calculation steps are as follows: Logical operators are extracted from the logical operator tree, and the types of aggregate operators included are identified. Based on the system's preset aggregate operator scoring rules, the basic aggregate score of the corresponding operator is determined. In this embodiment, the basic aggregation score is obtained based on the mapping table values ​​of different operators in SQL inference. .

[0038] Introducing a grouping dimension gain score, based on the number of grouping fields involved in the logical structure. The grouping dimension gain score is expressed by linear accumulation using the following formula. : ; in, This is the preset grouping complexity coefficient; If recursive calls to aggregation operators are involved, nested compensation scores should be considered. A fixed compensation score of 0.2 is assigned; otherwise, no compensation score is assigned. The aggregate level score is calculated using a truncation function. ; Obtain the weight parameters for the complexity scores across the three dimensions; For each user problem, calculate its complexity score across its three dimensions. This weighting parameter is used to weight and map the scores of each item to calculate the overall complexity score. : ; in, This indicates the weight of the score for the corresponding dimension.

[0039] The specific process for obtaining the weight parameters of the three dimensions of complexity is as follows: Using publicly available datasets, we calculate the complexity scores for each of the three dimensions mentioned above, map the complexity scores to logarithmic space, and construct feature difference vectors. , of which Feature difference in each dimension The calculation formula is: ; in, Representing the original question Dimensional scores, Representative subproblem set The mean score of each dimension; Based on the original question The overall complexity score and its set of subproblems The difference between the average comprehensive complexity scores is used as the target variable. Construct the feature difference vector To optimize the objective function for the input parameters, regularization constraints are introduced during parameter fitting to minimize the error between the predicted result and the target variable, thereby learning the weight parameters for complexity in each dimension. The weight parameters for the multi-table path span score are: The weighting parameters for the logical nesting depth score are: The aggregation level weight parameter is .

[0040] The execution consistency assessment and correction module executes each of the candidate SQL statements in the target database schema and obtains the corresponding execution status and result set. It performs a consistency judgment on the execution status and the result set. If the judgment is successful, it outputs any of the candidate SQL statements. Otherwise, it corrects the two candidate SQL statements. It constructs a structured prompt for correction using the two candidate SQL statements, the user question, the target database schema, the complexity scores of the three dimensions, and the logical operator tree. It then drives the LLM to rewrite the SQL. The corrected statement will re-enter the execution consistency judgment process until the execution result is consistent or the iteration limit is reached. If the candidate SQL statement fails to execute, the error information returned by the database is extracted, and it, along with the user's problem and the target database schema, is used as a feedback signal and input to the corresponding LLM to trigger targeted correction. The corrected statement will then be re-executed for consistency determination.

[0041] To verify the effectiveness of the "Structured Query Language Generation Method and System Based on Multidimensional Complexity Evaluation" proposed in this invention in actual industrial fault diagnosis scenarios, we conducted a comparative experiment based on real operation and maintenance data from a large manufacturing enterprise.

[0042] The experimental data came from the company's historical troubleshooting logs and operations database. A test set containing 500 "natural language problem - SQL statement" pairs was constructed from these data, and the test set was divided into three difficulty levels: Simple query (160 records): Involves single-table queries or basic status filtering, such as "query the current operating status of machine tool with number CNC-001".

[0043] Medium queries (220 records): These involve joins between two tables (such as an equipment table and a maintenance table) and aggregations within a time window. For example, "to count all equipment that experienced a 'spindle overheating' alarm last week and the number of times it was repaired."

[0044] Complex queries (120 records): These involve multi-table joins (3 or more tables), nested subqueries, complex time-series logic, and grouped statistics. For example, "Count the number of machine tools that replaced 'imported bearings' parts this quarter but still experienced 'spindle vibration' alarms under high load conditions, and rank them by equipment model."

[0045] To simulate the computing power constraints in a private enterprise deployment environment, the experiment uniformly used 4 Qwen bit quantization The 32B model is used as a generator. In the baseline experiment, the quantized model was used directly, and SQL was generated through prompting engineering; while the method of this invention, based on the same model, introduces the aforementioned adaptive complexity control mechanism for generation.

[0046] The experiment uses execution accuracy (EX) as the core evaluation metric. If the result set returned by the generated SQL after execution in the database is consistent with the execution result of the standard SQL, it is considered correct.

[0047] Table 1 Comparison of Results

[0048] The experimental results are shown in Table 1. For simple problems, the proposed method performs on par with the baseline. However, for medium and complex problems, the proposed method outperforms the baseline, achieving accuracies of 0.827 and 0.733 respectively, both showing a stable improvement over the baseline. This indicates that the present invention, through a complexity-aware adaptive mechanism, can effectively handle complex structures such as multi-table joins and nested logic, thereby improving the overall robustness and practicality of Text-to-SQL systems in industrial operation and maintenance scenarios.

[0049] In summary, this invention addresses the issue of significant differences in query structures in industrial equipment operation and maintenance and fault log analysis scenarios by providing an adaptively adjustable query language generation mechanism. It quantifies the complexity of the problem by introducing a multi-dimensional complexity scoring mechanism and incorporates the complexity assessment results into the SQL generation and verification process as continuous control signals. This allows the generation process to adaptively adjust based on differences in query reasoning structures, thereby improving the system's stability and engineering applicability in complex operation and maintenance analysis scenarios.

[0050] The specific embodiments described above further illustrate the purpose, technical solution, and beneficial effects of the present invention. It should be understood that the above description is only a specific embodiment of the present invention and is not intended to limit the scope of protection of the present invention. Any modifications, equivalent substitutions, improvements, etc., made within the spirit and principles of the present invention should be included within the scope of protection of the present invention.

Claims

1. A method for structured query language generation based on multi-dimensional complexity assessment, characterized in that: The steps are as follows, performed sequentially: S1: Obtain or receive user questions and target database schema; S2: By calculating the number of associated edges in the minimum Steiner tree and using a piecewise function mapping, the multi-table path span score of the user problem is obtained. By constructing a logical operator tree to extract the nesting level and using a Logistic function mapping, the logical nesting depth score of the user problem is obtained. By identifying aggregation and grouping operations and using a truncation function to calculate, the aggregation level score of the user problem is obtained. The weight parameters corresponding to the three scores are obtained. The scores of each dimension are weighted and summed with the corresponding weight parameters to obtain the comprehensive complexity score of the user problem. S3: If the overall complexity score is less than or equal to the preset threshold, the LLM is directly driven to convert the user's question into the corresponding SQL statement; otherwise, the dual-path parallel generation mechanism is started. The specific components of the dual-path parallel generation mechanism include: The first path is a multi-dimensional feature-guided decomposition path: the scores of the complexity of the three dimensions calculated in step S2 are compared to determine the decomposition strategy. When the multi-table path span score is the highest, the user problem is preferentially decomposed into two sequential sub-steps: conditional filtering and inter-table association. When the logical nesting depth score is the highest, the complex nesting intent is broken down into multiple query subtasks that are progressively based on dependencies. When the aggregation level score is the highest, the decomposition process is limited to three stages: data pre-filtering, group calculation, and aggregation result constraint. Based on the determined decomposition strategy, prompt instructions are constructed to drive the LLM to complete the logical decomposition of the user's question and the generation of SQL fragments corresponding to each sub-question. Finally, the generated SQL fragments are logically integrated according to the dependencies between sub-questions, and converged into a first candidate SQL statement whose structure adapts to the original intent. ; The second path is the opposition-heuristic example path: It receives the user question, the target database schema, the complexity scores of the three dimensions, and the logical operator tree constructed in step S2. Using the complexity scores of the three dimensions and the logical operator tree as generation conditions, it constructs a generation instruction oriented towards a preset complexity pattern, driving the LLM to generate a pair of contrasting examples containing positive guidance and negative traps. The contrasting example pairs, along with the user question and the target database schema, are input into the LLM as part of the prompt words to constrain its generation and output of a second candidate SQL statement. ; S4: Regarding the first candidate SQL statement and the second candidate SQL statement Conduct consistency assessment and correction.

2. The structured query language generation method based on multidimensional complexity evaluation as described in claim 1, characterized in that: The specific calculation steps for the comprehensive complexity score in step S2 are as follows: S2-1: Use a semantic embedding model to retrieve the fields involved in the user question and map them to the corresponding tables in the target database schema. Mark the corresponding tables as target nodes in the architecture relationship graph of the target database. Calculate the minimum Steiner tree required to connect all target nodes to obtain the minimum number of associated edges. The multi-table path span score is represented by the following piecewise function. : ； S2-2: Constructing a logical operator tree using LLM, defining... The logical nesting depth score represents the logical nesting level of this logical operator tree from the root node to the deepest leaf node. The following Logistic function is used to represent it: ； Where e is a constant; S2-3: Aggregate level scores include basic aggregate scores. Grouping Dimension Gain Score and nested compensation scores The specific calculation steps are as follows: Extract logical operators from the logical operator tree, identify the types of aggregate operators included, and determine the basic aggregate score of the corresponding operator according to the system's preset aggregate operator scoring rules. ; Introducing a grouping dimension gain score, based on the number of grouping fields involved in the logical structure. The grouping dimension gain score is expressed by linear accumulation using the following formula. : ； in, This is the preset grouping complexity coefficient; If recursive calls to aggregation operators are involved, nested compensation scores should be considered. A fixed compensation score of 0.2 is assigned; otherwise, no compensation score is assigned. The aggregate level score is calculated using a truncation function. ; S2-4: Obtain the weight parameters for the three dimensions; S2-5: For user questions, calculate their feature scores across three dimensions. The scores of each dimension are weighted and summed with their corresponding weight parameters to calculate the overall complexity score. : ； in, This indicates the weight of the corresponding dimension.

3. The structured query language generation method based on multidimensional complexity evaluation as described in claim 2, characterized in that: The specific process for obtaining the weight parameters of the three dimensions in step S2-4 is as follows: S2-4-1: Using the public dataset, calculate the scores for each of the three dimensions mentioned above, map the scores to the logarithmic space, and construct the feature difference vector. , of which Feature difference in each dimension The calculation formula is: ; in, Representing the original question Dimensional scores, Representative subproblem set The mean score of each dimension; S2-4-2: Based on the original problem The overall complexity score and its set of subproblems The difference between the average comprehensive complexity scores is used as the target variable. Construct the feature difference vector The objective function is optimized for the input parameters. Regularization constraints are introduced during the parameter fitting process to minimize the error between the prediction result and the target variable, thereby learning the weight parameters of the complexity of each dimension.

4. The structured query language generation method based on multidimensional complexity evaluation as described in claim 3, characterized in that: In step S4, each of the candidate SQL statements is executed in the target database schema, and the corresponding execution status and result set are obtained. A consistency determination is performed on the execution status and the result set. If the determination is successful, any of the candidate SQL statements is output. Otherwise, the two candidate SQL statements are corrected. The two candidate SQL statements, the user question, the target database schema, the complexity scores of the three dimensions, and the logical operator tree are used to construct a structured prompt for correction. The LLM is then driven to rewrite the SQL. The corrected statement will re-enter the execution consistency determination process until the execution result is consistent or the iteration limit is reached.

5. The structured query language generation method based on multidimensional complexity evaluation as described in claim 4, characterized in that: The method also includes exception feedback guidance: if the candidate SQL statement fails to execute, the error information returned by the database is extracted, and it, along with the user problem and the target database schema, is used as a feedback signal and input to the corresponding LLM to trigger targeted correction. The corrected statement will be re-executed for consistency determination.

6. A structured query language generation system based on multidimensional complexity evaluation, characterized in that: It includes a problem complexity assessment module, a multi-dimensional feature-guided decomposition module, an oppositional heuristic example module, and an execution consistency assessment and correction module; The problem complexity assessment module is used to obtain the multi-table path span score of the user problem by calculating the number of associated edges of the minimum Steiner tree and using a piecewise function mapping; to obtain the logical nesting depth score of the user problem by constructing a logical operator tree to extract the nesting level and using a Logistic function mapping; to obtain the aggregation level score of the user problem by identifying aggregation and grouping operations and using a truncation function calculation; to obtain the weight parameters of the three scores; and to obtain the comprehensive complexity score of the user problem by weighted summing of the scores of each dimension and the corresponding weight parameters. The multi-dimensional feature-guided decomposition module is used to make a judgment based on the comprehensive complexity score. If the comprehensive complexity score is less than or equal to a preset threshold, the LLM is directly driven to convert the user's question into the corresponding SQL statement. Otherwise, the dual-path parallel generation mechanism is started. The specific components of the dual-path parallel generation mechanism include: The first path is a multi-dimensional feature-guided decomposition path: the scores of the complexity of the three dimensions calculated in step S2 are compared to determine the decomposition strategy. When the multi-table path span score is the highest, the user problem is preferentially decomposed into two sequential sub-steps: conditional filtering and inter-table association. When the logical nesting depth score is the highest, the complex nesting intent is broken down into multiple query subtasks that are progressively based on dependencies. When the aggregation level score is the highest, the decomposition process is limited to three stages: data pre-filtering, group calculation, and aggregation result constraint. Based on the determined decomposition strategy, prompt instructions are constructed to drive the LLM to complete the logical decomposition of the user's question and the generation of SQL fragments corresponding to each sub-question. Finally, the generated SQL fragments are logically integrated according to the dependencies between sub-questions, and converged into a first candidate SQL statement whose structure adapts to the original intent. ; The second path is the opposition-heuristic example path: It receives the user question, the target database schema, the complexity scores of the three dimensions, and the logical operator tree constructed in step S2. Using the complexity scores of the three dimensions and the logical operator tree as generation conditions, it constructs a generation instruction oriented towards a preset complexity pattern, driving the LLM to generate a pair of contrasting examples containing positive guidance and negative traps. The contrasting example pairs, along with the user question and the target database schema, are input into the LLM as part of the prompt words to constrain its generation and output of a second candidate SQL statement. ; The consistency evaluation and correction module is used to evaluate the first candidate SQL statement. and the second candidate SQL statement Perform consistency assessment and correction, and output the execution results.

7. The structured query language generation system based on multidimensional complexity evaluation as described in claim 6, characterized in that: The specific calculation steps for the comprehensive complexity score are as follows: A semantic embedding model is used to retrieve the fields involved in the user's question and map them to the corresponding tables in the target database schema. These corresponding tables are then marked as target nodes in the target database's architecture graph. The minimum Steiner tree required to connect all target nodes is calculated to obtain the minimum number of foreign key association edges. The multi-table path span score is represented by the following piecewise function. : ； Using LLM to construct logical operator trees, define The logical nesting depth score represents the logical nesting level of this logical operator tree from the root node to the deepest leaf node. The following Logistic function is used to represent it: ； Where e is a constant; Aggregate level scores include basic aggregate scores. Grouping Dimension Gain Score and nested compensation scores The specific calculation steps are as follows: Extract logical operators from the logical operator tree, identify the types of aggregate operators included, and determine the basic aggregate score of the corresponding operator according to the system's preset aggregate operator scoring rules. ; Introducing a grouping dimension gain score, based on the number of grouping fields involved in the logical structure. The grouping dimension gain score is expressed by linear accumulation using the following formula. : ； in, This is the preset grouping complexity coefficient; If recursive calls to aggregation operators are involved, nested compensation scores should be considered. A fixed compensation score of 0.2 is assigned; otherwise, no compensation score is assigned. The aggregate level score is calculated using a truncation function. ; Obtain the weight parameters for the complexity scores across the three dimensions; For this user's question, calculate its feature scores across three dimensions. The scores of each dimension are weighted and summed with their corresponding weight parameters to calculate the overall complexity score. : ； in, This indicates the weight of the score for the corresponding dimension.

8. The structured query language generation system based on multidimensional complexity evaluation as described in claim 7, characterized in that: The specific process for obtaining the weight parameters of the three dimensions of complexity is as follows: Using a publicly available dataset, we calculate the scores for complexity in the three dimensions mentioned above, map the scores to logarithmic space, and construct feature difference vectors. , of which Feature difference in each dimension The calculation formula is: ； in, Representing the original question Dimensional scores, Representative subproblem set The mean score of each dimension; Based on the original question The overall complexity score and its set of subproblems The difference between the average comprehensive complexity scores is used as the target variable. Construct the feature difference vector The objective function is optimized for the input parameters. Regularization constraints are introduced during the parameter fitting process to minimize the error between the prediction result and the target variable, thereby learning the weight parameters of the complexity of each dimension.

9. The structured query language generation system based on multidimensional complexity evaluation as described in claim 8, characterized in that: The execution consistency assessment and correction module executes each of the candidate SQL statements in the target database schema and obtains the corresponding execution status and result set. It performs a consistency judgment on the execution status and the result set. If the judgment is successful, it outputs any of the candidate SQL statements. Otherwise, it corrects the two candidate SQL statements. It constructs a structured prompt for correction using the two candidate SQL statements, the user question, the target database schema, the complexity scores of the three dimensions, and the logical operator tree. It then drives the LLM to rewrite the SQL. The corrected statement will re-enter the execution consistency judgment process until the execution result is consistent or the iteration limit is reached. If the candidate SQL statement fails to execute, the error information returned by the database is extracted and used as a feedback signal along with the user problem and the target database schema. This signal is then input into the corresponding LLM to trigger targeted correction. The corrected statement will be re-entered into the execution consistency assessment and correction module for execution consistency determination.

Images