Method and system for generating NL2SQL high-quality duplicate removal and fine adjustment corpus

Abstract

The invention discloses a method and a system for generating NL2SQL high-quality duplicate removal and fine adjustment corpus, and belongs to the crossing field of database technology and natural language processing. The method comprises the following steps: uploading project scene seed corpora, setting keywords, querying target parameters of scene classification, solving a constraint equation to calculate and generate a target, generating pre-tagged corpora by using a large language model after semantic enhancement, forming positive and negative samples and a thinking chain data set through execution feedback, AI auditing, manual tagging and rechecking, and finally obtaining a target data set. After the SQL skeleton and the SQL-TQFD index are pre-calculated, a data set is finally exported based on a deduplication algorithm and quality index sorting optimization. According to the method, a large number of manual annotations and complex rule templates are not needed, high-quality corpora conforming to actual project data distribution can be dynamically generated, the corpus generation efficiency and the model training accuracy are remarkably improved through automatic auditing, duplicate removal and quality screening mechanisms, the labor cost is reduced, and the model fine adjustment accuracy is improved.

Description

Technical Field

[0001] This invention relates to the intersection of database technology and natural language processing, and in particular to a method and system for generating high-quality deduplicated and finely tuned NL2SQL corpora. Background Technology

[0002] In recent years, the cross-fertilization of natural language processing and database technology has driven the rapid development of NL2SQL (Natural Language to SQL) technology, which is increasingly widely used in intelligent data analysis, enterprise-level question-answering systems, and other fields. As the core foundation for fine-tuning large-scale NL2SQL language models, the generation technology of high-quality corpora directly affects the accuracy and generalization ability of the generated SQL. In practical applications, NL2SQL models facing complex business scenarios need to handle complex queries such as multi-table joins and aggregation statistics. However, the shortcomings of existing corpus generation technologies in terms of data integrity, reasonable distribution, and quality screening mechanisms severely restrict the performance of models in real-world scenarios. Therefore, improving the automation level, data quality, and scenario coverage of NL2SQL corpus generation is of great significance for promoting the practical application of intelligent data interaction technology and ensuring the stability and efficiency of enterprise-level data services.

[0003] Existing NL2SQL corpus generation technologies suffer from the following main shortcomings: First, insufficient utilization of database metadata, lacking systematic integration of table structure DDL, field semantics, and sample data, leading to a disconnect between the generated corpus and real-world database scenarios, making it difficult for large language models to understand business logic. Second, a lack of data distribution planning mechanisms; existing methods largely rely on random expansion, failing to target data allocation based on keyword coverage, scenario classification, and difficulty level, resulting in an excessively high proportion of simple queries and insufficient coverage of complex scenarios in the corpus. Third, an incomplete quality assurance system, lacking automated review and iteration mechanisms, resulting in high SQL execution error rates and poor formatting, impacting model learning efficiency. Furthermore, existing technologies generally lack the ability to generate negative samples and construct thought chains, containing only correct SQL samples, making it difficult to help models learn error pattern recognition, and the lack of reasoning processes weakens model interpretability. Simultaneously, corpus deduplication and quality screening technologies are outdated, relying heavily on simple methods such as string matching, failing to capture semantic similarity and difficulty differences, increasing training computational costs with redundant data, and the fragmented and low-automation manual annotation process leads to high costs for producing high-quality corpora.

[0004] Therefore, existing methods still need improvement in terms of data authenticity, distribution rationality, and quality screening efficiency to meet the actual needs of fine-tuning NL2SQL models. Summary of the Invention

[0005] The purpose of this invention is to overcome the above-mentioned defects in the background technology and to provide a method and system for generating high-quality deduplicated and fine-tuned NL2SQL corpora. The generation process is simple, the processing efficiency is high, the deduplication and fine-tuning capabilities are strong, and it can quickly and accurately generate high-quality fine-tuned corpora containing positive and negative samples, thought chain data, and coverage of multiple scenarios and difficulty levels.

[0006] To solve the above-mentioned technical problems, the present invention adopts the following technical solution:

[0007] A method for generating high-quality, deduplicated, and finely tuned NL2SQL corpora includes the following steps:

[0008] Step S1: Upload the project scenario seed corpus. The project scenario seed corpus includes the project industry, project name, dataset name, database, data table set, data table meaning set, data table DDL creation statement set, question number, user question, related semantics, question contributor, question reviewer, SQL statement, SQL statement execution status, SQL statement execution result or error message, SQL scenario classification, SQL difficulty level, SQL annotator, and SQL reviewer. The SQL represents Structured Query Language.

[0009] Step S2: Set target parameters based on the project scenario seed corpus. The NL2SQL data synthesis system calculates the target number of each item in the corpus to be generated by solving the constraint equations according to the input target parameters. The target parameters include: keywords and query scenario classification.

[0010] Step S3: Perform semantic enhancement by integrating the database table structure information, sample data, and related semantics with the question in XML format as input to the NL2SQL large language model.

[0011] Step S4: AI expansion and generation. The semantically enhanced seed corpus is expanded and generated using the NL2SQL large language model to obtain pre-annotated corpus. The pre-annotated corpus includes the generated questions and the corresponding SQL pre-annotated answers.

[0012] Step S5: Execution feedback. The pre-annotated SQL answer generated by AI expansion is executed on the corresponding database table to obtain the execution status, execution result, and error information.

[0013] Step S6: AI review. AI reviews the generated questions and pre-annotated SQL answers from Step S4, as well as the execution status, execution results, and error messages from Step S5, to determine if the pre-annotation target has been achieved. If the pre-annotation target has not been achieved, the iteration is repeated N times until a pre-annotated result that meets the pre-annotation target is generated or the iteration limit is reached. When the pre-annotation target is achieved or the iteration limit is reached, the NL2SQL data synthesis system outputs the AI ​​review results and AI review comments, proceeding to Step S7.

[0014] Step S7: Manual annotation. The annotator manually annotates the data of the pre-annotated corpus described in step S4. The AI-assisted information provided by the NL2SQL data synthesis system in step S6 is used by the annotator to make judgments and obtain the manually annotated data. The manually annotated data includes manually annotated SQL statements and questions.

[0015] Step S8: Execution feedback. The NL2SQL data synthesis system automatically executes the SQL statements marked by manual annotation in step S7 to obtain the execution status, execution results, and error information.

[0016] Step S9: AI review. The AI ​​reviews the issues from Step S7, the manually annotated SQL statements, and the execution status, execution results, and error messages from Step S8 to determine whether the annotation target has been achieved. If the annotation target has not been achieved, the annotator decides whether to return to Step S7 for re-annotation. If the annotation target has been achieved or the annotator decides not to re-annotate, the NL2SQL data synthesis system outputs the AI ​​review results and AI review opinions, and proceeds to Step S10.

[0017] Step S10: Manual review. The auditor reviews and modifies the manually annotated data in step S7. In step S9, the AI-assisted information provided by the NL2SQL data synthesis system is used by the auditor to make a judgment and obtain the auditor's audit SQL statement.

[0018] Step S11: Execution feedback. The NL2SQL data synthesis system automatically executes the audit SQL statement given by the auditor and obtains the execution status, execution result, and error information.

[0019] Step S12: AI review. The AI ​​reviews the problem, the reviewer's SQL statement in step S10, the execution status and result of step S11, and the error message to determine whether the review objective has been achieved. If the review objective has not been achieved, the reviewer decides whether to return to step S10 for re-review. If the review objective has been achieved or the reviewer decides not to re-review, the final approved SQL statement is obtained, and step S13 is performed.

[0020] Step S13: Standardize the format of the SQL statements in the seed corpus in step S1 and the expanded corpus in step S12 until the final approval. Replace the SQL keywords with uppercase. The SQL follows the OpenAI SQL code block format and is embedded between the code block start mark and the code block end mark to obtain positive samples and form a positive sample set. The positive samples are SQL statements that match the natural language problem.

[0021] Step S14: AI generates error SQL statements by modifying positive sample SQL statements;

[0022] Step S15: Standardize the format of erroneous SQL statements in the same way as in step S13, that is, replace SQL keywords with uppercase, and embed the SQL in the code block format of OpenAI between the code block start mark and the code block end mark to obtain negative samples, which are then merged with the positive sample set in step S13 to form a positive and negative sample set.

[0023] Step S16: AI generates corresponding thought processes for positive and negative samples respectively, forming a thought chain dataset;

[0024] Step S17: Pre-calculate and cache the SQL skeleton using the SQL statements in all SQL data, where all SQL data refers to the SQL statements in the positive and negative sample sets in step S15.

[0025] Step S18: Based on the thought chain dataset from step S16, pre-calculate and cache the SQL-TQFD index. The SQL-TQFD index is obtained by calculating SQL-TFD and SQL-QFD, representing a comprehensive index of the difficulty of SQL thinking and query following. SQL-TFD is the ratio of the difficulty of thinking execution to the difficulty of basic thinking, and SQL-QFD is the ratio of the difficulty of SQL execution to the difficulty of basic query.

[0026] Step S19: Select the exported parameters according to the model training objective, including dataset matching, SQL skeleton repetition target, and the percentage of preferred data to be retained in the SQL-TQFD-IPF index ranking.

[0027] Step S20: Deduplicatize and optimize the corpus based on the SQL skeleton deduplication algorithm. The deduplication algorithm includes distributed clustering processing and intelligent quality optimization. The SQL corpus with the same SQL skeleton is clustered and quality filtered in stages. Each cluster retains no more than a set upper limit proportion of entries.

[0028] Step S21: Sort and optimize the deduplicated corpus based on the SQL-TQFD index, and retain a specified percentage of high SQL-TQFD-IPF data;

[0029] Step S22: Export the dataset in Excel and JSON formats.

[0030] Furthermore, in step S2, the constraint condition for the keywords in the target parameters is:

[0031] Where i represents the SQL keyword item, j represents the SQL difficulty level, min_lv(i) represents the lowest SQL difficulty level for item i, L={1,2,3} represents the set of all SQL difficulty levels, and ΔK i,j MinNewK(i) represents the number of new data rows required for difficulty level j of project i, MinNewK(i) represents the minimum number of new data rows required for all difficulty levels j of project i, and W represents the set of all SQL keyword projects.

[0032] In step S2, the constraint condition for querying the scene classification in the target parameters is:

[0033]

[0034] ΔS i,j ΔS represents the number of new data rows needed for query item i with an SQL difficulty level of j. i Q represents the total number of new data entries required for each difficulty level of project i, Q represents the set of all query scenario projects, and J(i) represents the SQL difficulty level of project i.

[0035] Furthermore, in step S2, the implicit constraint in the target parameter that simultaneously satisfies the constraints of keywords and query scenarios is as follows:

[0036] ΔL j Q(j) represents the total number of new data rows required for difficulty level j, and Q(j) represents the set of all query scenarios with SQL difficulty level j.

[0037] Furthermore, in step S18, the calculation method for SQL-TFD is as follows:

[0038]

[0039] TED stands for Thinking and Execution Difficulty, while BTD stands for Basic Thinking Difficulty.

[0040] Furthermore, the calculations of TED and BTD are as follows:

[0041] Where T represents the number of tokens in the SQL statement, t represents the t-th token, P represents the probability that the model predicts the current token, NL represents natural language, and CoT represents the thought process. t CoT represents the t-th token in the thought process. 1:t-1θ represents the first to the (t-1)th tokens in the thought process, and θ represents the parameters of the model.

[0042] Furthermore, in step S18, the SQL-QFD calculation method is as follows:

[0043]

[0044] QED stands for SQL execution difficulty, while BQD stands for basic query difficulty.

[0045] Furthermore, the calculations of QED and BQD are as follows:

[0046] Among them, SQL t This represents the t-th token in the SQL statement. 1:t-1 This represents the first to the (t-1)th tokens in the SQL statement.

[0047] Furthermore, in step S18, the SQL-TQFD calculation method is as follows:

[0048] SQL-TQFD=α(SQL-TFD)+β(SQL-QFD)

[0049] Where α and β are weights, taking values ​​in [0,1], and α+β=1.

[0050] Furthermore, in step S18, when pre-calculating the SQL skeleton, a difficulty-distribution balance factor is introduced as a data retention weight, specifically:

[0051] Here, freq(sql) represents the frequency of the SQL pattern.

[0052] This invention also proposes a system for generating high-quality NL2SQL deduplication and refined corpora, applied to the aforementioned method for generating high-quality NL2SQL deduplication and refined corpora. The system for generating high-quality NL2SQL deduplication and refined corpora includes:

[0053] The data management module is used to upload and parse seed corpora of project scenarios and store structured data.

[0054] The parameter setting module is used to set target parameters and calculate the target number of corpus outputs.

[0055] The semantic enhancement module is used to integrate the database table structure, sample data, and related semantics with the question in XML format to generate model input.

[0056] The AI-based expansion and review module is used to generate pre-annotated corpora, execute SQL, and review iterations.

[0057] The manual annotation and review module is used for manual annotation and review of pre-annotated corpora, executes SQL and performs AI review iterations to obtain the final approved SQL statement;

[0058] The sample processing module is used to generate positive and negative samples and add thought processes to form a thought chain dataset.

[0059] The skeleton and indicator calculation module is used to pre-calculate and cache the SQL skeleton and SQL-TQFD indicators;

[0060] The deduplication and export module is used for deduplication based on the SQL skeleton, sorting by SQL-TQFD-IPF, and exporting the dataset.

[0061] Compared with the prior art, the present invention, employing the above technical solution, has the following beneficial effects:

[0062] (1) The method and system for generating high-quality deduplication and fine-tuning NL2SQL corpus provided by the present invention integrates database DDL, table structure and sample data through dynamic semantic enhancement module. It can automatically generate training corpus containing complete semantic information without the need for manual additional writing of complex prompt words, which significantly improves the accuracy of large language model in understanding database business logic.

[0063] (2) The method and system for generating high-quality deduplication and fine-tuning NL2SQL corpus provided by the present invention calculates the optimal data ratio of scenario classification, difficulty level and keyword coverage based on actual project seed data through target parameter setting and constraint solving mechanism. It does not rely on random expansion or complicated adjustments and can generate corpus that conforms to the actual business distribution as needed.

[0064] (3) The method and system for generating high-quality deduplication and fine-tuning NL2SQL corpus provided by the present invention, through automated positive and negative sample generation and thought chain construction technology, can generate corresponding error SQL samples and intermediate thinking steps for each natural language problem without the need for manual writing of error cases or reasoning processes, forming a training dataset containing a complete reasoning chain, which significantly enhances the model's ability to identify error patterns and the interpretability of reasoning.

[0065] (4) The method and system for generating high-quality deduplication and fine-tuning NL2SQL corpus provided by the present invention, through SQL skeleton deduplication algorithm and SQL-TQFD-IPF quality index, can automatically remove redundant corpus through distributed clustering deduplication, quality index and difficulty-frequency balance factor, retain high-value complex scene data, and improve model training efficiency while reducing the size of the dataset.

[0066] (5) The method and system for generating high-quality deduplication and fine-tuning NL2SQL corpora provided by the present invention construct an end-to-end automated process system, which integrates seed corpus uploading and structured storage, parameter setting and optimization generation target calculation, semantic enhancement, AI expansion, execution review, manual annotation and review, synthesis of error samples and thought chain datasets, skeleton and index calculation, and deduplication optimization and export. It does not require scattered scripts or a large amount of complicated and time-consuming repetitive manual intervention, which can effectively improve the corpus production efficiency, reduce manual annotation costs, and solve the defects of the fragmented production process and high cost in the prior art. Attached Figure Description

[0067] Figure 1 This is a flowchart illustrating the method for generating high-quality deduplication and fine-tuning NL2SQL corpora according to the present invention.

[0068] Figure 2 This is a graph showing the accuracy improvement of the NL2SQL fine-tuning model of this invention; Detailed Implementation

[0069] The technical solutions in the embodiments of the present invention will be clearly and completely described below with reference to the accompanying drawings. Obviously, the described embodiments are only some embodiments of the present invention, and not all embodiments. Based on the embodiments of the present invention, all other embodiments obtained by those of ordinary skill in the art without creative effort are within the protection scope of the present invention.

[0070] Example:

[0071] Methods for generating high-quality deduplication and fine-tuning corpora for NL2SQL, such as... Figure 1 As shown, it includes the following steps:

[0072] Step S1: Upload the project scenario seed corpus. The project scenario seed corpus includes the project industry, project name, dataset name, database, data table set, data table meaning set, data table DDL creation statement set, question number, user question, related semantics, question contributor, question reviewer, SQL statement, SQL statement execution status, SQL statement execution result or error message, SQL scenario classification, SQL difficulty level, SQL annotator, and SQL reviewer. The SQL represents Structured Query Language.

[0073] Step S2: Set target parameters based on the project scenario seed corpus. The NL2SQL data synthesis system calculates the target number of each item in the corpus to be generated by solving the constraint equations according to the input target parameters. The target parameters include: keywords and query scenario classification.

[0074] Step S3: Perform semantic enhancement by integrating the database table structure information, sample data, and related semantics with the question in XML format as input to the NL2SQL large language model.

[0075] Step S4: AI expansion and generation. The semantically enhanced seed corpus is expanded and generated using the NL2SQL large language model to obtain pre-annotated corpus. The pre-annotated corpus includes the generated questions and the corresponding SQL pre-annotated answers.

[0076] Step S5: Execution feedback. The pre-annotated SQL answer generated by AI expansion is executed on the corresponding database table to obtain the execution status, execution result, and error information.

[0077] Step S6: AI review. AI reviews the generated questions and pre-annotated SQL answers from Step S4, as well as the execution status, execution results, and error messages from Step S5, to determine if the pre-annotation target has been achieved. If the pre-annotation target has not been achieved, the iteration is repeated N times until a pre-annotated result that meets the pre-annotation target is generated or the iteration limit is reached. When the pre-annotation target is achieved or the iteration limit is reached, the NL2SQL data synthesis system outputs the AI ​​review results and AI review comments, proceeding to Step S7.

[0078] Step S7: Manual annotation. The annotator manually annotates the data of the pre-annotated corpus described in step S4. The AI-assisted information provided by the NL2SQL data synthesis system in step S6 is used by the annotator to make judgments and obtain the manually annotated data. The manually annotated data includes manually annotated SQL statements and questions.

[0079] Step S8: Execution feedback. The NL2SQL data synthesis system automatically executes the SQL statements marked by manual annotation in step S7 to obtain the execution status, execution results, and error information.

[0080] Step S9: AI review. The AI ​​reviews the issues from Step S7, the manually annotated SQL statements, and the execution status, execution results, and error messages from Step S8 to determine whether the annotation target has been achieved. If the annotation target has not been achieved, the annotator decides whether to return to Step S7 for re-annotation. If the annotation target has been achieved or the annotator decides not to re-annotate, the NL2SQL data synthesis system outputs the AI ​​review results and AI review opinions, and proceeds to Step S10.

[0081] Step S10: Manual review. The auditor reviews and modifies the manually annotated data in step S7. In step S9, the AI-assisted information provided by the NL2SQL data synthesis system is used by the auditor to make a judgment and obtain the auditor's audit SQL statement.

[0082] Step S11: Execution feedback. The NL2SQL data synthesis system automatically executes the audit SQL statement given by the auditor and obtains the execution status, execution result, and error information.

[0083] Step S12: AI review. The AI ​​reviews the problem, the reviewer's SQL statement in step S10, the execution status and result of step S11, and the error message to determine whether the review objective has been achieved. If the review objective has not been achieved, the reviewer decides whether to return to step S10 for re-review. If the review objective has been achieved or the reviewer decides not to re-review, the final approved SQL statement is obtained, and step S13 is performed.

[0084] Step S13: Standardize the format of the SQL statements in the seed corpus in Step S1 and the expanded corpus in Step S12 until the final approval. Replace the SQL keywords with uppercase. The SQL follows the OpenAI SQL code block format and is embedded between ```sql and ``` to obtain positive samples and form a positive sample set. The ```sql is the code block start marker and ``` is the code block end marker. The positive samples are the SQL statements that match the natural language problem.

[0085] Step S14: AI generates error SQL statements by modifying positive sample SQL statements;

[0086] Step S15: Standardize the format of erroneous SQL statements in the same way as in step S13 to obtain negative samples, which are then merged with the positive sample set in step S13 to form a positive and negative sample set.

[0087] Step S16: AI generates corresponding thought processes for positive and negative samples respectively, forming a thought chain dataset;

[0088] Step S17: Pre-calculate and cache the SQL skeleton using the SQL statements in all SQL data, where all SQL data refers to the SQL statements in the positive and negative sample sets in step S15.

[0089] Step S18: Based on the thought chain dataset from step S16, pre-calculate and cache the SQL-TQFD index. The SQL-TQFD index is obtained by calculating SQL-TFD and SQL-QFD, representing a comprehensive index of the difficulty of SQL thinking and query following. SQL-TFD is the ratio of the difficulty of thinking execution to the difficulty of basic thinking, and SQL-QFD is the ratio of the difficulty of SQL execution to the difficulty of basic query.

[0090] Step S19: Select the exported parameters according to the model training objective, including dataset matching, SQL skeleton repetition target, and the percentage of preferred data to be retained in the SQL-TQFD-IPF index ranking.

[0091] Step S20: Deduplicatize and optimize the corpus based on the SQL skeleton deduplication algorithm. The deduplication algorithm includes distributed clustering processing and intelligent quality optimization. The SQL corpus with the same SQL skeleton is clustered and quality filtered in stages. Each cluster retains no more than a set upper limit proportion of entries.

[0092] Step S21: Sort and optimize the deduplicated corpus based on the SQL-TQFD index, and retain a specified percentage of high SQL-TQFD-IPF data;

[0093] Step S22: Export the dataset in Excel and JSON formats.

[0094] Furthermore, in step S2, the constraint condition for the keywords in the target parameters is:

[0095] Where i represents the SQL keyword item, j represents the SQL difficulty level, min_lv(i) represents the lowest SQL difficulty level for item i, L={1,2,3} represents the set of all SQL difficulty levels, and ΔK i,j MinNewK(i) represents the number of new data rows required for difficulty level j of project i, MinNewK(i) represents the minimum number of new data rows required for all difficulty levels j of project i, and W represents the set of all SQL keyword projects.

[0096] In step S2, the constraint condition for querying the scene classification in the target parameters is:

[0097] ΔS i,j ΔS represents the number of new data rows needed for query item i with an SQL difficulty level of j. i Q represents the total number of new data entries required for each difficulty level of project i, Q represents the set of all query scenario projects, and J(i) represents the SQL difficulty level of project i.

[0098] Furthermore, in step S2, the implicit constraint in the target parameter that simultaneously satisfies the constraints of keywords and query scenarios is as follows:

[0099] ΔL j Q(j) represents the total number of new data rows required for difficulty level j, and Q(j) represents the set of all query scenarios with SQL difficulty level j.

[0100] Furthermore, in step S18, the calculation method for SQL-TFD is as follows:

[0101]

[0102] TED stands for Thinking and Execution Difficulty, while BTD stands for Basic Thinking Difficulty.

[0103] Furthermore, the calculations of TED and BTD are as follows:

[0104] Where T represents the number of tokens in the SQL statement, t represents the t-th token, P represents the probability that the model predicts the current token, NL represents natural language, and CoT represents the thought process. t CoT represents the t-th token in the thought process. 1:t-1 θ represents the first to the (t-1)th tokens in the thought process, and θ represents the parameters of the model.

[0105] Furthermore, in step S18, the SQL-QFD calculation method is as follows:

[0106]

[0107] QED stands for SQL execution difficulty, while BQD stands for basic query difficulty.

[0108] Furthermore, the calculations of QED and BQD are as follows:

[0109] Among them, SQL t This represents the t-th token in the SQL statement. 1:t-1 This represents the first to the (t-1)th tokens in the SQL statement.

[0110] Furthermore, in step S18, the SQL-TQFD calculation method is as follows:

[0111] SQL-TQFD=α(SQL-TFD)+β(SQL-QFD)

[0112] Where α and β are weights, taking values ​​in [0,1], and α+β=1.

[0113] Furthermore, in step S18, when pre-calculating the SQL skeleton, a difficulty-distribution balance factor is introduced as a data retention weight, specifically:

[0114] Here, freq(sql) represents the frequency of the SQL pattern.

[0115] Furthermore, the SQL skeleton deduplication algorithm in step S20 is a text deduplication method based on a dual clustering mechanism and a multi-level quality assessment system. The dual clustering mechanism is a hybrid hierarchical clustering strategy combining DBSCAN and the K-Means partitioning clustering algorithm. The multi-level quality assessment system combines rule-based filtering, LLM ranking, and adaptive similarity thresholding for deduplication. DBSCAN is a density-based noise-applied spatial clustering algorithm, and K-Means is a partitioning clustering algorithm. This SQL skeleton deduplication algorithm includes the following steps:

[0116] Step A1, Input Segmentation Processing: Divide the original text set into multiple text segments;

[0117] Step A2, Local Clustering Mapping;

[0118] Step A2-1, Vectorization: Represent text segments as vectors using a pre-trained language model;

[0119] Step A2-2, Dynamic Parameter DBSCAN Clustering: Based on the k-nearest neighbor distance, clustering parameters are automatically calculated, adaptive density clustering is implemented, and primary clustering labels are generated; the calculation of the k-nearest neighbor distance includes an inflection point detection algorithm to automatically identify elbow changes in the distance curve;

[0120] Step A2-3, Secondary K-Means splitting: Perform K-Means recursive splitting on clusters exceeding the threshold to generate hierarchical cluster labels;

[0121] Step A2-4: Output key-value pairs: Generate intermediate results in the form of (cluster identifier, (text vector, original text));

[0122] Step A3, Clustering and Grouping: Aggregate and group intermediate results with the same clustering label;

[0123] Step A4: Clustering Optimization Process

[0124] Step A4-1, Noise Point Processing: Perform rule-based filtering and dynamic threshold deduplication on texts not classified into any cluster. The rules include filtering rules based on length and format. The dynamic threshold deduplication represents automatic threshold calculation based on similarity distribution.

[0125] Step A4-2, Secondary Clustering Optimization: Perform secondary DBSCAN and K-Means clustering on clusters that exceed the threshold;

[0126] Step A4-3, Quality Score Ranking: Call the large language model to perform multi-dimensional quality assessment; and use a nested ranking strategy to combine global ranking with independent score ranking; the evaluation dimensions of the multi-dimensional quality assessment include: information density, logical rigor, factual accuracy, and fluency of expression;

[0127] Step A4-4: Retain according to a predefined retention ratio;

[0128] Step A5 Result Merging: Aggregate all clusters and retain the text as the final output set.

[0129] In addition, this invention also proposes a system for generating high-quality deduplication and refined NL2SQL corpora, applicable to the aforementioned method for generating high-quality deduplication and refined NL2SQL corpora. The system for generating high-quality deduplication and refined NL2SQL corpora includes:

[0130] The data management module is used to upload and parse seed corpora of project scenarios and store structured data.

[0131] The parameter setting module is used to set target parameters and calculate the target number of corpus outputs.

[0132] The semantic enhancement module is used to integrate the database table structure, sample data, and related semantics with the question in XML format to generate model input.

[0133] The AI-based expansion and review module is used to generate pre-annotated corpora, execute SQL, and review iterations.

[0134] The manual annotation and review module is used for manual annotation and review of pre-annotated corpora, executes SQL and performs AI review iterations to obtain the final approved SQL statement;

[0135] The sample processing module is used to generate positive and negative samples and add thought processes to form a thought chain dataset.

[0136] The skeleton and indicator calculation module is used to pre-calculate and cache the SQL skeleton and SQL-TQFD indicators;

[0137] The deduplication and export module is used for deduplication based on the SQL skeleton, sorting by SQL-TQFD-IPF, and exporting the dataset.

[0138] Example 2:

[0139] The SQL scenarios in the seed dataset are categorized as follows: general queries, indicator queries, extreme value queries, aggregation statistics, grouped statistics, year-on-year analysis, month-on-month analysis, comparative analysis, and secondary queries, as shown in Table 1.

[0140] Table 1. Query Scenarios Classified by SQL Statement

[0141]

[0142]

[0143] The difficulty levels of the SQL statements in the seed dataset are divided into the following three categories: Lv1, Lv2, and Lv3, as shown in Table 2.

[0144] Table 2: Difficulty Level of SQL Statements

[0145]

[0146] The target parameter calculation algorithm for the target number of records includes target parameters containing SQL keywords, as shown in Table 3. For example, whether it contains time addition / subtraction keywords (DATE_ADD, DATE_SUB, DATE_DIFF), conditional expression keywords (CASE WHEN), and concatenation operators (UNION, UNION ALL).

[0147] Table 3 shows examples of target parameters containing SQL keywords.

[0148]

[0149] The target parameters for SQL query scenario classification are shown in Table 4. The system first calculates the current number of records and the current percentage for each predefined query scenario category based on the seed dataset uploaded by the user. Then, the data producer sets the target number of records and the target percentage for each predefined query scenario category according to their project needs. The system calculates the number of records to be added and checks that the total target percentage for each query scenario category must equal 100%, and that the total number of target records entered must be greater than the current number of records. Next, based on the data producer's settings, the system calculates the total number of records to be added for each scenario category = target number of records - current number of records, which serves as the target for subsequent automatic corpus generation.

[0150] Table 4 Target parameters for SQL query scenario classification

[0151]

[0152]

[0153] We transform the above SQL query scenario classification requirements into the following constraints. In the example table above, the value of query scenario item i is 1 for general query, 2 for indicator query, 3 for extreme value query, 4 for aggregation statistics, 5 for group statistics, 6 for year-on-year analysis, 7 for month-on-month analysis, 8 for comparative analysis, and 9 for secondary query. The set of query scenario items Q is {1,2,3,4,5,6,7,8,9}, and L is {1,2,3} representing the three difficulty levels in Table 2. When i = 1,2,3,4, J(i) = 1; when i = 5, J(i) = 2; and when i = 6,7,8,9, J(i) = 3.

[0154] After the above settings and calculations are completed, the system will use this information to calculate the summary information of Lv1, Lv2, and Lv3, as shown in Table 5. Once the data producer is determined, the system will proceed to the next step.

[0155] Table 5 Target parameters for SQL difficulty levels

[0156]

Claims

1. A method for generating NL2SQL high-quality deduplication and fine-tuning corpus, characterized in that, Comprise the following steps: Step S1, upload project scene seed corpus, the project scene seed corpus contains project industry, project name, data set name, database, data table set, data table meaning set, data table DDL table statement set, question serial number, user asking, related semantics, question contributor, question reviewer, SQL statement, SQL statement execution state, SQL statement execution result or error information, SQL scene classification, SQL difficulty level, SQL annotator, SQL reviewer, the SQL represents structured query language; Step S2, set target parameters based on project scene seed corpus, NL2SQL data synthesis system calculates the number of each item target of the generated corpus according to the input set target parameters by solving constraint condition equation, the target parameters include: keyword, query scene classification; Step S3, semantic enhancement, the database table structure information, sample data and related semantics are fused with the problem in XML format as the input of NL2SQL large language model; Step S4, AI expansion generation, NL2SQL large language model is used for AI expansion generation of the seed corpus after semantic enhancement, and pre-annotated corpus is obtained, the pre-annotated corpus includes generated questions and corresponding SQL pre-annotated answers; Step S5, execution feedback, the SQL pre-annotated answers generated by AI expansion are executed on the corresponding database table to obtain execution state and execution result, error information; Step S6, AI review, the questions generated in step S4, SQL pre-annotated answers and execution state, execution result, error information in step S5 are reviewed by AI to determine whether the pre-annotation target is reached; When the pre-annotation target is not reached, repeat iteration N times until the pre-annotated results reaching the pre-annotation target or the iteration number limit is reached; When the pre-annotation target is reached or the iteration number limit is reached, NL2SQL data synthesis system outputs AI review results and AI review opinions, and proceeds to step S7; Step S7, manual annotation, the annotators manually annotate the pre-annotated corpus data in step S4, and the AI auxiliary information provided by NL2SQL data synthesis system in step S6 is used for the annotators to judge, and the manually annotated annotation data is obtained, the manually annotated annotation data includes manually annotated SQL statements and questions; Step S8, execution feedback, NL2SQL data synthesis system automatically executes according to the manually annotated SQL statements in step S7 to obtain execution state and execution result, error information; Step S9, AI review, AI reviews the questions in step S7, manually annotated SQL statements and execution state and execution result, error information in step S8 to determine whether the annotation target is reached; When the annotation target is not reached, the annotator decides whether to return to step S7 for reannotation, when the annotation target is reached or the annotator decides not to reannotate, NL2SQL data synthesis system outputs AI review results and AI review opinions, and proceeds to step S10; Step S10, manual review, the reviewer reviews and modifies the manually labeled data in step S7, and the AI assistant information provided by the NL2SQL data synthesis system in step S9 is used for the reviewer to judge, and the reviewer's review SQL statement is obtained; Step S11, execution feedback, the NL2SQL data synthesis system automatically executes according to the reviewer's review SQL statement, and the execution state, execution result and error information are obtained; Step S12, AI review, AI reviews according to the problem, the reviewer's review SQL statement in step S10, the execution state and execution result in step S11, and the error information, and judges whether the review target is reached; when the review target is not reached, the reviewer decides whether to return to step S10 for re-review; when the review target is reached or the reviewer decides not to re-review, the final review passed SQL statement is obtained, and step S13 is performed; Step S13, format uniformity is performed on the SQL statements in the seed corpus in step S1 and the extended writing corpus in step S12 to pass the final review, the SQL keywords are uniformly replaced with uppercase letters, the SQL follows the OpenAI SQL code block format and is embedded between the code block start marker and the code block end marker, and the positive sample is obtained, forming a positive sample set, wherein the positive sample is a SQL statement matched with a natural language question; Step S14, AI generates error SQL statements by modifying the positive sample SQL statements; Step S15, the error SQL statements are uniformly formatted in the same way as step S13, that is, the SQL keywords are uniformly replaced with uppercase letters, and the SQL follows the OpenAI SQL code block format and is embedded between the code block start marker and the code block end marker, to obtain negative samples, which are combined with the positive sample set in step S13 to form a positive and negative sample set; Step S16, AI generates corresponding thinking processes for positive samples and negative samples respectively to form a thinking chain data set; Step S17, precompute SQL skeletons from all SQL data and cache, wherein the all SQL data are the SQL statements in the positive and negative sample set in step S15; Step S18, based on the thinking chain data set in step S16, precompute SQL-TQFD indicators and cache, wherein the SQL-TQFD indicators are obtained by calculating SQL-TFD and SQL-QFD, and represent the comprehensive index of SQL thinking and query following difficulty, wherein SQL-TFD is the ratio of thinking execution difficulty to basic thinking difficulty, and SQL-QFD is the ratio of SQL execution difficulty to basic query difficulty; Step S19, select and export parameters according to model training targets, including data set matching, SQL skeleton repetition degree target, and SQL-TQFD-IPF indicator sorting and reserved preferred data percentage; Step S20, de-duplication optimization of corpus based on SQL skeleton de-duplication algorithm, wherein the de-duplication algorithm includes distributed clustering processing and intelligent quality optimization, and the SQL corpus set with the same SQL skeleton is step-by-step clustered and quality-screened, and each cluster retains no more than a set upper limit percentage of entries. Step S21, based on SQL-TQFD index, the de-duplicated corpus is sorted and optimized, and a specified percentage of high SQL-TQFD-IPF data is retained; Step S22, export the data set in Excel and JSON formats.

2. The method for generating NL2SQL high-quality de-duplication and fine-tuning corpus according to claim 1, wherein, In the step S2, the constraint condition of the keywords in the target parameter is: AK i,j ≥ 0, i e W, j e L wherein i represents an SQL keyword item, j represents an SQL difficulty level, min lv(i) represents the minimum SQL difficulty level of item i, L = {1, 2, 3} represents a set of all SQL difficulty levels, ΔK i,j represents the number of data items that need to be added for item i at j difficulty level, MinNewK(i) represents the minimum number of data items that need to be added for item i at each difficulty level j, and W represents a set of all SQL keyword items. In the step S2, the constraint condition of the query scene classification in the target parameter is: ΔS i ≥ 0, i e Q ΔS i,j the number of data items needed to be added for SQL difficulty level j of query condition item i, ΔS i the total number of data items needed to be added for each difficulty level of item i, Q represents the set of all query scenario items, and J(i) represents the SQL difficulty level of item i.

3. The method for generating NL2SQL high-quality de-duplication and fine-tuning corpus according to claim 1, wherein, In the step S2, the constraint condition implied in the target parameter that satisfies the constraint conditions of the keywords and the query scene at the same time is: ΔL j represents the total number of data items that need to be added for difficulty level j, and Q(j) represents the set of all query scenarios with SQL difficulty level j.

4. The method for generating NL2SQL high-quality de-duplication and fine-tuning corpus according to claim 1, wherein, In the step S18, the calculation method of SQL-TFD is: Where TED is the thinking execution difficulty, and BTD is the basic thinking difficulty.

5. The method for generating NL2SQL high-quality de-duplication and fine-tuning corpus according to claim 1, wherein, The calculation of TED and BTD is respectively: where T represents the number of tokens of the SQL statement, t represents the tth token, P represents the probability that the model predicts the current token, NL represents the natural language, CoT represents the thinking process, CoT t represents the tth token of the thinking process, CoT 1:t-1 represents the 1th to t-1th tokens of the thinking process, and θ represents the parameters of the model.

6. The method for generating NL2SQL high-quality de-duplication and fine-tuning corpus according to claim 1, wherein, In the step S18, the calculation method of SQL-QFD is: Where QED is the SQL execution difficulty, and BQD is the basic query difficulty.

7. The method for generating NL2SQL high-quality de-duplication and fine-tuning corpus according to claim 6, wherein, The calculation of QED and BQD is respectively: wherein SQL t represents the tth token of the SQL statement, SQL 1:t-1 represents the 1st to (t-1)th tokens of the SQL statement.

8. The method for generating NL2SQL high-quality de-duplication and fine-tuning corpus according to claim 1, wherein, In the step S18, the calculation method of SQL-TQFD is: SQL-TQFD = α(SQL-TFD) + β(SQL-QFD) Where α and β are weights, the values are in [0, 1], and α + β = 1.

9. The method for generating NL2SQL high-quality de-duplication and curated fine-tuning corpus of claim 1, wherein, In the step S18, when precomputing the SQL skeleton, a difficulty-distribution balance factor is also introduced as the retention weight of the data, which is specifically: Where freq(sql) represents the frequency of the SQL mode.

10. A system for generating NL2SQL high-quality de-duplication and curated fine-tuning corpus, characterized in that, The system for generating NL2SQL high-quality de-duplicated and selected fine-tuning corpus is applied to the method for generating NL2SQL high-quality de-duplicated and selected fine-tuning corpus in any one of claims 1-9, and the system for generating NL2SQL high-quality de-duplicated and selected fine-tuning corpus comprises: A data management module for uploading and parsing project scene seed corpus and storing structured data; A parameter setting module for setting target parameters and calculating the target number of corpus generation; A semantic enhancement module for fusing database table structure, sample data and related semantics in XML format with questions to generate model input; An AI expansion and review module for generating pre-labeled corpus, executing SQL and reviewing iterations; A manual labeling and review module for manually labeling and reviewing pre-labeled corpus, executing SQL and AI reviewing iterations to obtain finally reviewed SQL statements; A sample processing module for generating positive and negative samples and adding thinking processes to form a thinking chain data set; A skeleton and index calculation module for precomputing SQL skeleton and SQL-TQFD index and caching; A de-duplication optimization and export module for de-duplication based on SQL skeleton, sorting according to SQL-TQFD-IPF, and exporting data set.

Images