Text2SQL data set generation method based on dynamic difficulty adjustment

Abstract

The invention discloses a Text2SQL (Structured Query Language) data set generation method based on dynamic difficulty adjustment. According to the method, a complexity quantification method is provided, and the complexity of SQL statements and Text texts is evaluated. A Text2SQL data set is generated based on a cue word generation template by using a large language model, and a dynamic constraint part is introduced into the cue word generation template. And performing complexity evaluation on data generated by the large language model through a complexity quantification method, comparing the data with preset difficulty target distribution, modifying dynamic constraints, and guiding the model to generate data conforming to expected target distribution. Meanwhile, a verification and correction template is also set, so that the execution correctness and semantic correctness of the generated data are ensured, the efficiency and quality of data set construction are improved, the diversity and pertinence of the data set are ensured, more comprehensive training data better fitting practical application is provided for the model, and the training efficiency is improved. And the generalization ability and the performance of the Text2SQL model in different scenes can be improved.

Description

Technical Field

[0001] The present invention belongs to the technical field of electronic digital data processing, relates to the generation of a Text2SQL dataset, and particularly relates to a method for generating a Text2SQL dataset based on dynamic difficulty adjustment. Background Art

[0002] As a core tool for data storage and management, databases are widely used in many fields such as scientific research, finance, healthcare, and e-commerce. The Text2SQL task aims to convert natural language questions into SQL query statements, enabling users to conveniently obtain the required information from databases and playing an important role in data analysis and decision support.

[0003] With the rapid development of artificial intelligence and machine learning technologies, the demand for high-quality, large-scale, and reasonably distributed Text2SQL datasets is becoming increasingly urgent. However, there are significant bottlenecks in the construction of traditional Text2SQL datasets: manual annotation is time-consuming and laborious, and the datasets generated by simple automation often have problems of unbalanced difficulty and cannot be dynamically adjusted according to the needs of actual application scenarios, resulting in poor controllability of the generated datasets in terms of diversity and difficulty. Specifically, the following problems are particularly prominent:

[0004] (1) Insufficient diversity: The SQL queries generated by the fixed-template method usually focus on certain specific patterns and lack sufficient variation and diversity. For example, if a template can only generate simple queries involving a single table, it is difficult for the generated dataset to contain complex queries involving multi-table joins. This singularity not only limits the richness of the dataset but may also cause the model to perform poorly when faced with diverse queries. In fact, database queries in the real world often involve complex relationships between multiple tables, and the fixed-template method is difficult to simulate these complex scenarios, thus reducing the effectiveness of the dataset.

[0005] (2) Insufficient difficulty control: Existing technologies are difficult to flexibly adjust the complexity of SQL queries when generating datasets. For example, in some application scenarios, it may be necessary to generate some relatively complex queries to test the model's ability to handle complex logic; while in other scenarios, it may be necessary to generate some relatively simple queries for preliminary evaluation or debugging. However, the fixed-template method cannot dynamically adjust the complexity of the query according to the requirements, resulting in the generated dataset being either too simple to fully test the model's performance or too complex to exceed the scope of the current stage's requirements.

[0006] (3) Lack of dynamic optimization mechanism: When generating datasets in the prior art, a one-time template or rule setting is usually adopted, lacking a subsequent dynamic adjustment mechanism. This means that even if problems are found in the generated dataset, they cannot be corrected without redesigning the template. For example, if it is found that the proportion of subqueries in the generated dataset is too high, or certain types of queries are missing, it can only be solved by manually modifying the template or rules. This static method not only increases the workload but also may make the generation process cumbersome and inefficient.

[0007] In this context, the implementation of an automatic construction system for Text2SQL datasets based on dynamic difficulty adjustment is of great significance. Summary of the Invention

[0008] Aiming at the deficiencies of the prior art, the present invention proposes a method for generating Text2SQL datasets based on dynamic difficulty adjustment, introducing a dynamic constraint part into the prompt generation template, evaluating the complexity of the data generated by the large language model, comparing it with the preset difficulty target distribution, modifying the dynamic constraint, and guiding the model to generate data that meets the expected target distribution to meet the training requirements.

[0009] The method for generating Text2SQL datasets based on dynamic difficulty adjustment specifically includes the following steps:

[0010] Step 1: Initialize parameters

[0011] Map the complexity metrics of SQL statements and Text texts to three difficulty intervals of low, medium, and high, and define the target distribution. The complexity metrics of the SQL statements include structural complexity, semantic complexity, and syntactic complexity. The complexity metrics of the Text texts include text perplexity and text complexity.

[0012] Step 2: Data generation

[0013] Define the default prompt template. Use the large language model to generate SQL statements and Text texts according to the default prompt template.

[0014] The default prompt template includes role description, Schema, problem description, SQL generation requirements, and output format example. The SQL generation requirements include basic rules and dynamic constraints. The dynamic constraint part is used to adjust the complexity of the generated SQL statements.

[0015] Step 3: SQL statement verification

[0016] For the SQL statements generated in Step 2, perform verification of execution correctness and semantic correctness.

[0017] Verification of execution correctness: Create real data through the Schema in the default prompt template and execute the generated SQL statement. If it cannot be executed, go to step 4. Otherwise, perform verification of semantic correctness.

[0018] Verification of semantic correctness: Compare the execution result of the generated SQL statement with the problem description to determine whether the execution result is the answer required by the problem. If so, return to step 2 to generate the next piece of data until the number of generated data reaches the preset batch quantity and then enter step 5. Otherwise, enter step 4.

[0019] Step 4, Data correction

[0020] Define a correction template. Use the large language model to correct the unqualified generated data according to the correction template, generate corrected data and return to step 3 for verification. If the verification passes, replace the original data with the corrected data; if it still fails to pass the verification after 3 corrections, retain the original data, mark it as an error sample, and return to step 2.

[0021] The correction template replaces the SQL generation requirements in the default prompt template with the incorrect SQL statement and the error feedback from the verification of execution correctness.

[0022] Step 5, Complexity index evaluation

[0023] Evaluate the complexity indexes of the SQL statements and Text texts in the generated data:

[0024] s5.1, Structural complexity

[0025] The structural complexity index SCI is used to measure the structural complexity of the SQL statement:

[0026] SCI = α × Nsubquery + β × Njoin + γ × Dnest

[0027] Among them, Nsubquery represents the number of subqueries in the SQL statement. Njoin represents the number of join operations in the SQL statement. Dnest represents the maximum depth of nesting in the SQL statement. α, β, and γ are the corresponding weight coefficients.

[0028] s5.2, Semantic complexity

[0029] The semantic complexity index SEI is used to measure the semantic complexity of the SQL statement:

[0030] SEI = δ × Crange + ∈ × Nagg + ζ × Ccondition

[0031] Among them, Crange is the sum of the number of tables and the number of fields in the tables in the query data, reflecting the complexity of the query data range. Nagg represents the number of aggregate functions in the SQL statement. Ccondition is the number of conditional expressions, reflecting the complexity of the conditional expressions. δ, ∈, and ζ are the corresponding weight coefficients.

[0032] s5.3. Syntactic Complexity

[0033] The syntactic complexity index SGI is used to measure the syntactic complexity of the SQL statement:

[0034] SGI = η × Lsql + θ × Dsyntax

[0035] Among them, Lsql represents the length of the SQL statement. Dsyntax represents the number of different syntactic structures of the SQL statement, reflecting the diversity of syntactic structures. η and θ represent the corresponding weight coefficients.

[0036] s5.4. Text Perplexity

[0037] Tokenize the generated Text text T, T = (t1, t2,..., t n ), where n represents the number of tokens in text T. Predict the probability of the next token based on the previous tokens and take the logarithm:

[0038]

[0039] Among them, P(t i |t ＜i ) represents the probability that the i-th word is t i-1 when the previous i - 1 words are t1, t2,..., t i respectively.

[0040] Then calculate the average log probability AvgLogProb, and map it to the scale of the original probability through exponentiation to obtain the text perplexity P:

[0041]

[0042] P = exp(-AvgLogProb)

[0043] s5.5. Text Complexity

[0044] Perform syntactic dependency analysis on the Text text T, construct a syntactic dependency tree, obtain the maximum depth MaxDepth(T), average dependency distance AvgDepDist(T), and average branching factor AvgBranchingFactor(T) of the syntactic dependency tree, and calculate the text complexity Complexity:

[0045] Complexity = μ·MaxDepth(T) + ν·AvgDepDist(T) + ω·AvgBranchingFactor(T)

[0046] Among them, μ, ν, and ω are preset weight parameters, which can be adjusted according to the data distribution or expected effect of the specific task.

[0047] Step 6, Dynamic Constraint Feedback

[0048] According to the index evaluation result of Step 5, generate a feedback template. Use the large language model to generate dynamic constraints based on the feedback template, and use the generated dynamic constraints to replace the dynamic constraint part in the default prompt template described in Step 2.

[0049] Return to Step 2, and use the large language model to generate SQL statements and Text texts according to the modified prompt template until the number of generated data reaches the preset generation quantity, and then enter Step 7.

[0050] The feedback template includes role description, task requirements, task examples, task background, and output format examples.

[0051] Among them, the task requirements define the analysis focus and conflict handling methods of the task. The task example gives an example of the steps to complete the task requirements. The task background includes the index evaluation result and the defined target distribution.

[0052] Step 7, Text Difficulty Control

[0053] According to the index evaluation result of Step 5, determine the modification direction of the Text text difficulty, and generate a difficulty control template. Use the large language model to modify the Text text in the generated data according to the difficulty control template, and verify its semantic correctness to make the complexity index distribution of the generated data consistent with the target distribution defined in Step 1.

[0054] The difficulty control template includes role setting, task requirements, and task examples. The task requirements define dynamic adjustment instructions, adjustment constraints, and cooperation strategies. Among them, the cooperation strategy specifies the task priority order and conflict resolution method, the adjustment constraint stipulates the modification requirements of the Text text difficulty, and the dynamic adjustment instruction calls the instruction according to the modification direction of the Text text difficulty.

[0055] The present invention has the following beneficial effects:

[0056] This method designs a series of quantitative indicators to measure the difficulty of Text texts and SQL statements in the generated data, and dynamically adjusts the difficulty of the generated data set according to the measurement results to make it conform to the preset difficulty distribution. It not only improves the efficiency and quality of data set construction, but also ensures the diversity and pertinence of the data set, provides more comprehensive and practical training data for the model, helps to improve the generalization ability and performance of the model in different scenarios, and promotes the development and application of Text2SQL technology. Brief Description of the Drawings

[0057] Figure 1 It is a flow chart of a Text2SQL data set generation method based on dynamic difficulty adjustment;

[0058] Figure 2 It is a schematic diagram of a syntactic dependency tree. Detailed Implementation Modes

[0059] The present invention will be further explained below with reference to the accompanying drawings;

[0060] As Figure 1 shown, the Text2SQL data set generation method based on dynamic difficulty adjustment specifically includes the following steps:

[0061] Step 1, Initialize parameters

[0062] Map the complexity indicators of SQL statements and Text texts to three difficulty intervals of low, medium, and high, and define a preset target distribution, as shown in Table 1:

[0063] Index Low-difficulty range Medium-difficulty range High-difficulty range Target distribution Structural complexity [0,2) [2,3) [3,+∞) 20％:50％:30％ Semantic complexity [0,0.5) [0.5,1.5) [1.5,+∞) 25％:50％:25％ Syntactic complexity [0,2) [2,3) [3,+∞) 30％:40％:30％ Text perplexity [0,5.5) [5.5,9.5) [9.5,+∞) 30％:50％:20％ Text complexity [0,4) [4,5) [5,+∞) 40％:50％:10％

[0064] Step 2, Data generation

[0065] Define a default prompt template. Use a large language model to generate SQL statements and Text texts according to the default prompt template.

[0066] The default prompt template includes a role description, a Schema, a question description, SQL generation requirements, and an output format example. The following is an example of a default prompt template:

[0067] ① Role description

[0068] You are a rigorous SQL engineer who needs to generate a SQL query question and the corresponding SQL statement that meet the requirements according to the given schema and user needs.

[0069] ② Schema

[0070] CREATE TABLE `stadium`(

[0071] Stadium_ID INT PRIMARY KEY,

[0072] Location TEXT,

[0073] Name TEXT,

[0074] Capacity INT,

[0075] Highest INT,

[0076] Lowest INT,

[0077] Average INT );

[0079] Sample rows from `stadium`:

[0080] 1,Raith Rovers,Stark's Park,10104,4812,1294,2106

[0081] 2,Ayr United,Somerset Park,11998,2363,1057,1477

[0082] 3,East Fife,Bayview Stadium,2000,1980,533,864

[0083] CREATE TABLE `singer`(

[0084] Singer_ID INT PRIMARY KEY,

[0085] Name TEXT,

[0086] Country TEXT,

[0087] Song_Name TEXT,

[0088] Song_release_year TEXT,

[0089] Age INT,

[0090] Is_male bool );

[0092] Sample rows from `singer`:

[0093] (1, Joe Sharp, Netherlands, You, 1992, 52, F),

[0094] (2, Timbaland, United States, Dangerous, 2008, 32, T),

[0095] (3, Justin Brown, France, Hey Oh, 2013, 29, T)

[0096] ③ Problem description

[0097] How many female singers from France released songs before 2000?

[0098] ④ SQL generation requirements

[0099] ## Basic rules

[0100] 1. Use standard SQL-92 syntax

[0101] 2. The result should be executable on MySQL 8.0

[0102] ## Dynamic constraints

[0103] Must comply with:

[0104] {must_items}

[0105] It is recommended to comply with:

[0106] {suggest_items}

[0107] Strictly prohibited:

[0108] {forbid_items}

[0109] ⑥ Output format example

[0110] Please output strictly in the following JSON format:

[0111] {

[0112] "Question": "How many singers are there in total",

[0113] "SQL": "SELECT count(*) FROM singer",

[0114] }

[0115] In the default prompt template, the Schema describes the data table structure in the database to be generated and gives some example data.

[0116] The dynamic constraint part is used to adjust the complexity of the generated SQL statements. When initially generating data, the dynamic constraint is empty. When the generated data volume reaches the preset batch quantity, the complexity of the generated data will be evaluated, and targeted dynamic generation constraint conditions will be generated according to the difficulty distribution of the current data, so as to fit the parameters set in the initialization.

[0117] The following is a data example generated by the large language model qwen-omni-turbo according to the default prompt template during the code debugging process:

[0118] {

[0119] "schema":"CREATE TABLE `singer`(\n Singer_ID INT PRIMARY KEY,\n Name TEXT,\n Country TEXT,\n Song_Name TEXT,\n Song_release_year TEXT,\n Age INT,\n Is_male bool\n);\n Sample rows from `singer`:\n 1,Joe Sharp,Netherlands,You,1992,52,F\n 2,Timbaland,United States,Dangerous,2008,32,T\n 3,Justin Brown,France,Hey Oh,2013,29,T",

[0120] "question":"How many singers are there in total",

[0121] "sql":"SELECT count(*) FROM singer",

[0122] "count":"1",

[0123] "turn":"1"

[0124] }

[0125] Among them, schema is the representation of the data table structure, question represents the generated Text text, and sql represents the generated SQL statement. count and turn are used to count the generated data, representing the quantity of the generated data and the corresponding generation batch respectively. In actual data generation, the model will output the generated Text text and SQL statement according to the output format example defined in the default prompt template.

[0126] Step 3, SQL statement verification

[0127] Verify the execution correctness and semantic correctness of the SQL statements generated in step 2.

[0128] Verification of execution correctness: Create real data through the Schema in the default prompt template, and execute the generated SQL statements. If they cannot be executed, go to step 4. Otherwise, verify the semantic correctness.

[0129] Verification of semantic correctness: Compare the execution results of the generated SQL statements with the problem description to determine whether the execution results are the answers required by the problem. If so, return to step 2 to generate the next piece of data until the number of generated data reaches the preset batch quantity and then enter step 5. Otherwise, enter step 4.

[0130] The following is a data example output by the large language model qwen-omni-turbo according to the SQL statement verification results during the code debugging process:

[0131]

[0132]

[0133] Among them, sqlValid is used to identify the verification result of the execution correctness of the current SQL statement, sqlMatch is used to identify the verification result of the semantic correctness, and error is used to output the error reason. In actual data generation, the model will output the corrected SQL statements according to the output format example defined in the correction template.

[0134] Step 4, Data correction

[0135] Define a correction template. Use the large language model to correct the unpassed generated data according to the correction template, generate corrected data and return to step 3 for verification. If the verification passes, replace the original data with the corrected data; if it still fails to pass the verification after 3 corrections, keep the original data and mark it as an error sample, and return to step 2.

[0136] The correction template replaces the SQL generation requirements in the default prompt template with the incorrect SQL statements and error feedback from the execution correctness verification.

[0137] The following is an example of a correction template:

[0138] ① Role description

[0139] You are a rigorous SQL engineer and need to modify the current incorrect sql statement according to the given Schema, problem, and error feedback

[0140]

[0141] Sample rows from `Employees`:

[0142] (1,'Alice','Engineering',75000.00,'2020-06-15'),

[0143] (2,'Bob','HR',55000.00,'2018-03-21'),

[0144] (3,'Charlie','Engineering',82000.00,'2019-11-10'),

[0145] (4,'David','Marketing',62000.00,'2021-09-05');

[0146] ③ Problem description

[0147] Query the names and salaries of all employees in the Engineering department and sort them in descending order of salary.

[0148] ④ Incorrect SQL statement

[0149] SELECT Name,Salary FROM Employee WHERE Department = 'Engineering' ORDER BY Salary DESC;

[0150] ⑤ Error feedback

[0151] ERROR: relation "Employee" does not exist

[0152] LINE 1: SELECT Name,Salary FROM Employee WHERE Department = 'Engineering'...

[0153] HINT: Did you mean "Employees"?

[0154] ⑥ Output format example

[0155] Please output strictly in the following JSON format:

[0156] {

[0157] "SQL": "SELECT Name, Salary FROM Employees WHERE Department = 'Engineering' ORDER BY Salary DESC;"

[0158] }

[0159] Step 5, Complexity Index Evaluation

[0160] After generating a batch of data, evaluate the complexity indices of the SQL statements and Text texts in the generated data:

[0161] s5.1, Structural Complexity

[0162] The structural complexity index SCI is used to measure the structural complexity of SQL statements:

[0163] SCI = α × Nsubquery + β × Njoin + γ × Dnest

[0164] Among them, Nsubquery represents the number of subqueries in the SQL statement. The more subqueries there are, the more nested logic and data dependency relationships need to be processed. Njoin represents the number of join operations in the SQL statement. The more tables are joined, the more relationships between tables and join conditions need to be processed. Dnest represents the maximum depth of nesting in the SQL statement. The greater the depth, the more levels and nested logic need to be processed. α, β, and γ represent the weights of the number of subqueries, the number of join operations, and the maximum nesting depth, respectively.

[0165] s5.2, Semantic Complexity

[0166] The semantic complexity index SEI is used to measure the semantic complexity of SQL statements:

[0167] SEI = δ × Crange + ∈ × Nagg + ζ × Ccondition

[0168] Among them, Crange is the sum of the number of tables in the query data and the number of fields in the tables, reflecting the complexity of the query data range. Nagg represents the number of aggregate functions in the SQL statement. Ccondition is the number of conditional expressions, reflecting the complexity of the conditional expressions. δ, ∈, and ζ are the corresponding weight coefficients.

[0169] s5.3, Syntactic Complexity

[0170] The syntactic complexity index SGI is used to measure the syntactic complexity of SQL statements:

[0171] SGI = η × Lsql + θ × Dsyntax

[0172] Among them, Lsql represents the length of the SQL statement. Dsyntax represents the number of different syntactic structures of the SQL statement, reflecting the diversity of syntactic structures. η and θ represent the corresponding weight coefficients.

[0173] S5.4, Text Perplexity

[0174] Different expressions with the same meaning vary in terms of comprehension difficulty and clarity. To measure this clarity, text perplexity is introduced to measure the prediction ability of the large language model for each word in the Text text. If the model has a high prediction probability for a certain word, it indicates that this word is very reasonable in the given context, that is, the semantics of the text is clear. If the model has a low prediction probability for a certain word, it means that this word is not very reasonable in the given context, that is, the text may be ambiguous or poorly expressed. The lower the text perplexity, the more accurate the model's prediction of the text, that is, the text structure and word usage are more in line with the expectations of the language model, usually indicating a clearer expression; conversely, the higher the perplexity, there may be problems such as ambiguity, ungrammaticality, or chaotic expression.

[0175] Tokenize the generated Text text T, T = (t1, t2,..., t n ), where n represents the number of tokens in the text T. Predict the probability of the next token based on the previous tokens and take the logarithm:

[0176]

[0177] Among them, P(t i |t ＜i ) represents the probability that the i-th word is t i-1 when the previous i - 1 words are t1, t2,..., t i respectively.

[0178] Then calculate the average log probability AvgLogProb and map it to the scale of the original probability through exponentiation to obtain the text perplexity P:

[0179]

[0180] P = exp(-AvgLogProb)

[0181] S5.5, Text Complexity

[0182] Syntactic dependency analysis is a method of revealing the sentence structure by analyzing the dependency relationships between words in a sentence. Dependency relationships refer to the grammatical and semantic associations between words, such as subject-predicate relationships, verb-object relationships, attributive-center relationships, etc. Syntactic dependency analysis can parse a sentence into a grammatical dependency tree, where each node represents a word and the edges represent the dependency relationships between words.

[0183] Perform syntactic dependency analysis on the Text text T, construct a grammatical dependency tree as shown in Figure 2 to obtain the maximum depth MaxDepth(T), average dependency distance AvgDepDist(T), and average branching factor AvgBranchingFactor(T) of the grammatical dependency tree, and calculate the text complexity Complexity:

[0184] Complexity = μ·MaxDepth(T) + ν·AvgDepDist(T) + ω·AvgBranchingFactor(T)

[0185] where μ, ν, and ω are preset weight parameters that can be adjusted according to the data distribution or experimental results of specific tasks. [[ID=!2]]

[0186] Step 6, Dynamic Constraint Feedback

[0187] Generate a feedback template based on the index evaluation results in Step 5. Use the large language model to generate dynamic constraints according to the feedback template, and use the generated dynamic constraints to replace the dynamic constraint part in the default prompt template described in Step 2.

[0188] Return to Step 2, and use the large language model to generate SQL statements and Text text according to the modified prompt template until the number of generated data reaches the preset generation quantity, and then enter Step 7.

[0189] The feedback template includes role description, task requirements, task examples, task background, and output format examples. Among them, the task requirements define the analysis focus and conflict handling methods of the task. The task example gives an example of the steps to complete the task requirements. The task background includes the index evaluation results and the defined target distribution, which are used to indicate the generation of targeted dynamic constraint conditions.

[0190] The following is an example of a default prompt template:

[0191] ① Role description

[0192] You are an intelligent Text2SQL generation controller, and you need to dynamically adjust the generation strategy according to the complexity index. You possess the dual capabilities of an SQL grammar expert and a data analyst.

[0193] ② Task requirements

[0194] 1. Analysis focus: Prioritize resolving the metrics with the largest deviation from the target. It is necessary to identify the parts with the largest absolute difference between the current and target values at each level in SEI, SCI, and SGI. The larger the absolute value, the higher the priority. Then process them in the order of priority.

[0195] 2. Conflict handling: If there are conflicting suggestions, handle them according to the priority.

[0196] 3. Feasibility check: Ensure that the suggestions do not cause syntax errors or execution failures.

[0197] ③ Task example

[0198] 1. Gap location: It is necessary to identify the parts with the largest absolute difference between the current and target values at each level in SEI, SCI, and SGI.

[0199] Example: "sci": {"Current distribution": {"Low": 40%, "Medium": 55%, "High": 5%}, "Target distribution": {"Low": 10%, "Medium": 40%, "High": 50%},

[0200] "sei": {"Current distribution": {"Low": 25%, "Medium": 50%, "High": 25%}, "Target distribution": {"Low": 10%, "Medium": 40%, "High": 50%},

[0201] "sgi": {"Current distribution": {"Low": 60%, "Medium": 20%, "High": 20%}, "Target distribution": {"Low": 10%, "Medium": 40%, "High": 50%}

[0202] The high - difficulty gap of Sci is 20%, the high - difficulty gap of sei is 15%, and the high - difficulty over - standard of sgi is 50%.

[0203] 2. Weighted priority: If multiple metrics deviate from the target, prioritize adjusting the metric with the largest deviation.

[0204] Example: Such as the high - difficulty over - standard of sgi is 50% > the high - difficulty gap of Sci is 20% > the high - difficulty gap of sei is 15%

[0205] 3. Attribution analysis: Deduce the reasons based on the metric formula.

[0206] Example: The low value of SEI may be due to the small number of tables involved.

[0207] 4. Suggestion generation: Propose specific measures based on the attribution results.

[0208] Correct example: "It is required that the query must involve more than 3 tables", "It is required that the query involves four or more fields"

[0209] Example of error: "Increasing complexity" (too vague)

[0210] 5. Instruction conversion: Convert the suggestions into constraints that can be added to the prompt

[0211] ④ Task background

[0212] {userinput}

[0213] ⑤ Output format example

[0214] Please output strictly in the following JSON format:

[0215] {

[0216] "gap_analysis": ["List no more than 3 key gaps, sorted by priority"],

[0217] "action_plan": ["Specific adjustment actions (each item needs to be associated with the impact indicators)"],

[0218] "prompt_modifications": {

[0219] "must": ["New mandatory constraints (up to 3)"],

[0220] "suggest": ["Suggestive guidelines (up to 2)"],

[0221] "forbid": ["Prohibited items (up to 2)"]

[0222] }

[0223] }

[0224] Among them, userinput is a placeholder for filling in the index evaluation results of step 5. The following gives an example of the structured input design of the task background, which structures and transmits information such as index definitions, current status, and historical adjustment records to the large model to ensure that it can accurately understand the task background:

[0225] {

[0226] "Task type": "Text2SQL dataset complexity control",

[0227] "Index definition": {

[0228] "SCI": {

[0229] "Formula": "SCI = 0.4 * number of subqueries + 0.3 * number of join operations + 0.3 * nesting depth",

[0230] "Threshold": {"Low": [0, 5), "Medium": [5, 15), "High": [15, 100]},

[0231] "Explanation": "The number of subqueries has the greatest impact on SCI (weight 0.4)"

[0232] },

[0233] "SEI": {

[0234] "Formula": "SEI = 0.5 * Number of tables involved + 0.3 * Number of aggregate functions + 0.2 * Complexity of conditional expressions",

[0235] "Threshold": {"Low": [0, 10), "Medium": [10, 25), "High": [25, 100]},

[0236] "Explanation": "Pay close attention to the number of tables and the complexity of WHERE conditions"

[0237] },

[0238] "SGI": {

[0239] "Formula": "SGI = 0.6 * SQL length + 0.4 * Diversity of syntax structures",

[0240] "Threshold": {"Low": [0, 20), "Medium": [20, 50), "High": [50, 100]},

[0241] "Explanation": "Long statements and complex syntax (such as window functions) will significantly increase SGI"

[0242] }

[0243] },

[0244] "Current Status": {

[0245] "SCI": {"Average value": 14.2, "Current distribution": {"Low ": 15%, "Medium": 60%, "High": 25%}, "Target distribution": {"Low": 20%, "Medium": 50%, "High": 30%}},

[0246] "SEI": {"Average value": 8.7, "Distribution": {"Low": 30%, "Medium": 55%, "High": 15%}, "Target distribution": {"Low": 20%, "Medium": 50%, "High": 30%}},

[0247] "SGI": {"Average value": 35.2, "Distribution": {"Low": 10%, "Medium": 40%, "High": 50%}, "Target distribution": {"Low": 20%, "Medium": 50%, "High": 30%}},

[0248] },

[0249] "Historical adjustment records":

[0250] {"Round": 1, "Action": "Increase the number of JOINs", "Result": "SCI increased by 5%, but SGI exceeded the standard"},

[0251] {"Round": 2, "Action": "Limit GROUP BY", "Result": "SGI decreased by 10%"},

[0252] ,

[0253] "Task instruction": "Please generate suggestions for the generation strategy of the next batch of data",

[0254] },

[0255] The following is an example of a dynamic constraint generated by the large language model qwen-omni-turbo according to the feedback template:

[0256] {

[0257] "gap_analysis":

[0258] "There is a serious shortage of high-difficulty SEI samples (15% vs 30%), mainly because the average number of tables involved is only 2.1",

[0259] "There is an excess of high-difficulty SGI samples (50% vs 30%) due to the average SQL length reaching 120 characters",

[0260] "The high-difficulty SCI is close to the target (25% vs 30%), and a slight improvement is needed",

[0261] ,

[0262] "action_plan":

[0263] "Improve SEI: Force queries across 3 tables (affects SEI + SCI)",

[0264] "Reduce SGI: Limit SQL length < 100 characters (affects SGI)",

[0265] "Fine-tune SCI: It is recommended but not mandatory to use 1 subquery (affects SCI)"

[0266] ,

[0267] "prompt_modifications": {

[0268] "must":

[0269] "At least 3 tables must be joined using JOIN",

[0270] "The WHERE clause must contain at least 2 AND / OR conditions"

[0271] ,

[0272] "suggest":

[0273] "It can contain 1 subquery to increase the nesting depth"

[0274] ,

[0275] "forbid":

[0276] "The SQL statement is forbidden to exceed 100 characters",

[0277] "The use of window functions is forbidden"

[0279] }

[0280] }

[0281] Among them, must, suggest, and forbid in prompt_modifications are used to fill in the placeholder must_items, suggest_items, and forbid_items corresponding to the dynamic constraint part of the default prompt template.

[0282] Step 7, Text text difficulty control

[0283] When the quantity of the generated data reaches the preset generation quantity, according to the evaluation results of the text perplexity and text complexity metrics of the Text text in Step 5, determine the modification direction of the Text text difficulty, and generate a difficulty control template. Use the large language model to modify the Text text in the generated data according to the difficulty control template and verify its semantic correctness, so that the complexity metric distribution of the generated data is consistent with the target distribution defined in Step 1.

[0284] The difficulty control template includes role setting, task requirements, and task examples. The task requirements define dynamic adjustment instructions, adjustment constraints, and collaboration strategies. Among them, the collaboration strategy specifies the task priority order and conflict resolution methods, the adjustment constraints stipulate the requirements for modifying the Text text difficulty, and the dynamic adjustment instructions call instructions by matching predefined rules according to the modification direction of the Text text difficulty. The following is an example of a difficulty control template:

[0285] ① Role Description

[0286] Multidimensional Text Complexity Control Expert

[0287] ② Task Requirements

[0288] Precisely rewrite while strictly adhering to the adjustment constraint reference collaboration strategy, without changing the text semantics:

[0289] Original sentence: {text}

[0290] Adjustment direction: {direction}

[0291] ## Dynamic Adjustment Instructions

[0292] Execute according to priority:

[0293] 1. [Comprehension Difficulty] {perplexity_instruction}

[0294] 2. [Structural Complexity] {max_depth_instruction}

[0295] 3. [Modification Distance] {dep_distance_instruction}

[0296] 4. [Logical Density] {branching_instruction}

[0297] ## Adjustment Constraints

[0298] - Keep the semantic elements of the text complete (preserve all entities, attributes, conditions), but the expression of semantic elements can be changed

[0299] - Must strictly follow the original sentence's constraint conditions without introducing additional information

[0300] - Do not change numbers, but the expression can be changed, e.g., half can be changed to fifty percent

[0301] ## Collaboration Strategy

[0302] 1. Priority order: Perplexity > Depth > Distance > Branching factor

[0303] 2. Conflict resolution:

[0304] - When there is a conflict between structural simplification and increased perplexity, give priority to meeting the perplexity goal

[0305] - Use compensatory adjustments (e.g., shorten the distance to compensate for the increased depth)

[0306] ③ Task Examples

[0307] Original sentence: "List the stores in Hangzhou with monthly sales exceeding the average."

[0308] Adjustment direction: Complicate

[0309] Dynamic adjustment instructions:

[0310] [Comprehension difficulty] Deliberately create local syntactic anomalies (such as omitting prepositions)

[0311] [Structural complexity] Increase the nesting level by 2 (insert conditional clauses)

[0312] [Modification distance] Extend the word distance by 1.5 (postpone geographical restrictions)

[0313] [Logical density] Add one parallel condition

[0314] The following explains the dynamic adjustment instructions given in the task example:

[0315] For [Comprehension difficulty], it can be achieved by changing the text perplexity. For example, make the sentence pattern more convoluted, use "Find those stores in Hangzhou, and these stores'..." instead of "List the stores in Hangzhou...", or introduce local syntactic anomalies by omitting the preposition "de" to create slight confusion.

[0316] For [Structural complexity], the maximum depth in the syntactic dependency tree can be increased. For example, introduce "and these stores'..." as a conditional clause to increase the nesting.

[0317] For [Modification distance], geographical restrictions can be postponed to make the modifying elements more backward, thus extending the word distance.

[0318] For [Logical density], a parallel structure can be inserted to increase the logical level.

[0319] After executing the dynamic adjustment instructions, "List the stores in Hangzhou with monthly sales exceeding the average" can be made more complex and rewritten as "Find those stores in Hangzhou, and these stores' monthly sales are higher than the average monthly sales of all stores."

Claims

1. A method for generating a Text2SQL dataset based on dynamic difficulty adjustment, characterized in that: Specifically, it includes the following steps: Step 1: Map the complexity metrics of SQL statements and Text texts to different difficulty intervals, and define the target difficulty interval distribution for generating the dataset; the complexity metrics of the SQL statements include structural complexity, semantic complexity, and syntactic complexity; the complexity metrics of the Text texts include text perplexity and text complexity; Step 2: Define the default prompt template; use the large language model to generate SQL statements and Text texts according to the default prompt template; Introduce dynamic constraints in the SQL generation requirements part of the default prompt template to adjust the complexity of the generated SQL statements; Step 3: For the SQL statements generated in Step 2, perform execution correctness and semantic correctness verification in sequence; if there is incorrect execution, go to Step 4; if there is incorrect semantics, go to Step 4, otherwise return to Step 2 to generate the next piece of data until the number of generated data reaches the preset batch number and then enter Step 5; Step 4: Define the correction template; use the large language model to correct the generated data that fails the verification according to the correction template, generate the corrected data and return to Step 3 for verification; if the verification passes, use the corrected data to replace the original data; if it still fails the verification after several corrections, keep the original data and mark it as an error sample, and return to Step 2; Step 5: Evaluate the complexity metrics of the SQL statements and Text texts in the generated data Step 6: According to the index evaluation results in Step 5, generate a feedback template; use the large language model to generate dynamic constraints according to the feedback template, and use the generated dynamic constraints to replace the dynamic constraint part in the default prompt template described in Step 2; return to Step 2, and use the large language model to generate SQL statements and Text texts according to the modified prompt template until the number of generated data reaches the preset generation number, and then enter Step 7; Step 7: According to the index evaluation results in Step 5, determine the modification direction of the Text text difficulty, generate a difficulty control template; use the large language model to modify the Text text in the generated data according to the difficulty control template, and verify its semantic correctness to make the complexity metric distribution of the generated data consistent with the target distribution defined in Step 1.

2. The method for generating a Text2SQL dataset based on dynamic difficulty adjustment according to claim 1, wherein: The structural complexity metric SCI is used to measure the structural complexity of SQL statements: SCI = α × Nsubquery + β × Njoin + γ × Dnest Where, Nsubquery represents the number of subqueries in the SQL statement; Njoin represents the number of join operations in the SQL statement; Dnest represents the maximum depth of nesting in the SQL statement; α, β, and γ respectively represent the weights of the number of subqueries, the number of join operations, and the maximum nesting depth.

3. The method for generating a Text2SQL dataset based on dynamic difficulty adjustment according to claim 1, wherein: The semantic complexity metric SEI is used to measure the semantic complexity of SQL statements: SEI = δ × Crange + ∈ × Nagg + ζ × Ccondition Among them, Crange is the sum of the number of tables and the number of fields in the tables in the query data, reflecting the complexity of the query data range; Nagg represents the number of aggregate functions in the SQL statement; Ccondition is the number of conditional expressions, reflecting the complexity of the conditional expressions; δ, ∈, ζ are the corresponding weight coefficients.

4. The method for generating a Text2SQL dataset based on dynamic difficulty adjustment according to claim 1, characterized in that: The syntax complexity index SGI is used to measure the syntactic complexity of the SQL statement: SGI = η × Lsql + θ × Dsyntax Among them, Lsql represents the length of the SQL statement; Dsyntax represents the number of different syntactic structures of the SQL statement, reflecting the diversity of the syntactic structures; η, θ are the corresponding weight coefficients.

5. The method for generating a Text2SQL dataset based on dynamic difficulty adjustment according to claim 1, characterized in that: Tokenize the generated Text text T, where T = (t1, t2,..., t n ), where n represents the number of tokens in text T; predict the probability of the next token based on the previous tokens and take the logarithm: Among them, P(t i |t ＜i ) represents the probability that when the first i - 1 words are t1, t2, … t i-1 respectively, the i-th word is t i ; Then calculate the average log probability AvgLogProb, and map it to the scale of the original probability through the exponential operation to obtain the text perplexity P: P = exp(-AvgLogProb).

6. The method for generating a Text2SQL dataset based on dynamic difficulty adjustment according to claim 1, wherein: Perform syntactic dependency analysis on the Text text T, construct a syntactic dependency tree, obtain the maximum depth MaxDepth(T), average dependency distance AvgDepDist(T), and average branching factor AvgBranchingFactor(T) of the syntactic dependency tree, and calculate the text complexity Complexity: Complexity = μ·MaxDepth(T) + ν·AvgDepDist(T) + ω·AvgBranchingFactor(T) Among them, μ, ν, ω are preset weight parameters.

7. The method for generating a Text2SQL dataset based on dynamic difficulty adjustment according to claim 1, wherein: The default prompt word template includes role description, Schema, question description, SQL generation requirements, and output format example; the SQL generation requirements include basic rules and dynamic constraints; The correction template replaces the SQL generation requirements in the default prompt word template with the incorrect SQL statement and error feedback from the execution correctness verification.

8. The method for generating a Text2SQL dataset based on dynamic difficulty adjustment according to claim 1, wherein: The method for verifying execution correctness is: create real data through the Schema in the default prompt word template and execute the generated SQL statement; the method for verifying semantic correctness is: compare the execution result of the generated SQL statement with the question description to determine whether the execution result is the answer required by the question.

9. The method for generating a Text2SQL dataset based on dynamic difficulty adjustment according to claim 1, wherein: The feedback template includes role description, task requirements, task examples, task background, and output format example; Among them, the task requirements define the analysis focus and conflict handling method of the task; the task example gives an example of the steps to complete the task requirements; the task background includes the index evaluation result and the defined target distribution.

10. The method for generating a Text2SQL dataset based on dynamic difficulty adjustment according to claim 1, characterized in that: The difficulty control template includes role setting, task requirements, task examples; the task requirements define dynamic adjustment instructions, adjustment constraints, and cooperation strategies; Among them, the cooperation strategy specifies the task priority order and conflict resolution method, the adjustment constraint stipulates the requirements for modifying the Text text difficulty, and the dynamic adjustment instruction calls the instruction according to the Text text difficulty modification direction.

Images