Database query generation method and system based on multi-selection optimization and storage medium

Abstract

The invention discloses a database query generation method and system based on multi-selection optimization and a storage medium. The method comprises the steps that tables and columns related to natural language query are extracted from a database mode through multiple prompts; generating a plurality of candidate SQL queries by utilizing multiple prompts based on the extracted tables and columns related to the natural language queries and a selection strategy based on question similarity and mask question similarity; and performing screening according to the confidence score of each candidate SQL query, and selecting an optimal SQL query through a multi-selection method. According to the method and the device, the accuracy and the robustness of generating the SQL query are improved.

Description

Technical Field

[0001] The present invention belongs to the technical field of database query language generation, and in particular relates to a database query generation method, system and storage medium based on multi-selection optimization. Background Art

[0002] Based on the traditional SQL generation method, CN113177123B discloses an optimization method and system for a text-to-SQL model. This method only relies on the attention network of the relationship graph in the heterogeneous graph and line graph, which has low flexibility, and the system mainly improves the coding ability in the modeling of the node and edge topology structure of the heterogeneous graph, but does not go deep into the complex SQL generation task, and does not focus on performance optimization in complex tasks. CN112287093B discloses an automatic question-answering system based on semi-supervised learning and Text-to-SQL model. The system relies on the sample training model generated by semi-supervised learning, lacks support for diversified prompts, and is limited in the search space; this method is directly based on the generated samples for training, which may lead to insufficient accuracy in complex modes. CN117931851A discloses a Text-to-SQL method based on graph attention diffusion. This method usually relies on traditional machine learning models or rule-based systems for SQL generation, which is insufficient in adaptability.

[0003] Based on the SQL generation method of the large language model, CN118503270A discloses a system and a control method thereof built on the basis of the NL2SQL data set. The method uses a preset large model and a prompting project to generate a seed data set. Although it can provide certain semantic extensions, its performance depends on the initial preset of the model. The method mainly realizes problem expansion by semantically expanding the seed data set, but the expansion process is only based on a single prompting project. The method relies on preset statement verification rules to screen the generated SQL statements, but the scope of the rules is limited, especially in complex query scenarios, it is easy to have the problem of low screening accuracy. CN117874052A discloses a SQL statement generation system based on a large model and a control method thereof. The method usually relies on a one-time input of all table identifiers, intents and field information, and the model generates SQL statements at one time, resulting in limited generation effects. The method only performs simple mapping for preset fields and intents, and is limited in the breadth and generalization ability of generated statements. CN118643050A discloses a natural language to SQL conversion system based on a large language model and a control method thereof. This method relies on automatic sample selection and matching, and provides generation references by screening out SQL samples similar to natural language queries, and cannot continuously optimize the logic and accuracy of generated SQL through feedback. In the process of SQL generation, it mainly relies on database model pruning. Although it can reduce the interference of irrelevant data, it may miss important tables and fields, thereby affecting the accuracy of generated SQL. In contrast, the present invention introduces a multi-agent collaboration mechanism to improve the generation effect through multiple prompts and feedback to ensure that more query requirements are covered. The use of simple corrections based on database models and rules for database adaptation may not be able to cope with the dialect differences between different database management systems, resulting in a decrease in the executability of generated SQL. The present invention verifies the executability of the generated SQL in the actual database through dynamic testing to improve the reliability and adaptability of generated SQL. CN117951156A discloses a method for converting large model natural language into SQL based on LlamaIndex and DB-GPT. This method is highly dependent on local knowledge base and vector index when generating SQL statements. If the database model information is incomplete or inaccurate, the generated SQL statement may be incorrect, thereby affecting the accuracy of the execution result. Since the query engine is built using a fixed LlamaIndex, this method has low flexibility when processing complex or variable queries and cannot adapt to the changes in various natural language inputs, limiting its wide application possibilities. Although rule optimization based on sql_metadata is used to improve the quality of SQL statements, this process is relatively complex and has limited ability to handle multiple situations, which may lead to performance bottlenecks in actual applications.CN118377783B discloses a method and device for generating SQL statements, which relies on historical query information and interactive information to generate SQL statements, which means that when the amount of data is small or there is a lack of historical records, the performance of the system may drop significantly, resulting in the accuracy of the generated SQL statements being affected. In the process of determining multiple intermediate data tables and candidate data tables, multiple similarity calculations are involved, which increases the complexity of the algorithm and may cause the calculation time to be extended, affecting the real-time and response speed of the system. SQL statements are generated by presetting a number of candidate data tables, which may limit the ability to explore more potential valid data tables, thereby affecting the quality and accuracy of the final generated SQL, especially in complex query scenarios. Summary of the invention

[0004] In view of the above problems, the present invention provides a database query generation method, system, electronic device and storage medium based on multi-selection optimization, aiming to improve the accuracy of SQL generation.

[0005] According to a first aspect of an embodiment of the present disclosure, a method for generating a database query based on multi-selection optimization is provided, the method comprising the following steps:

[0006] Extract tables and columns relevant to natural language queries from database schemas through multiple hints;

[0007] Based on the extracted tables and columns related to the natural language query and the selection strategy based on question similarity and masked question similarity, multiple candidate SQL queries are generated using multiple prompts;

[0008] The candidate SQL queries are screened according to their confidence scores, and the optimal SQL query is selected through a multi-selection method.

[0009] In some embodiments, extracting tables and columns related to a natural language query from a database schema using multiple prompts specifically includes the following steps:

[0010] Input a database schema and a natural language query request, wherein the database schema includes structural information of each table and its columns in the database;

[0011] Analyzing the database schema and the natural language query request using a large-scale language model, and identifying table links closely related to the natural language query request and specific column links required in the table links from the database schema;

[0012] Information on the table link and its column link are merged and duplicated to obtain a column set for SQL generation.

[0013] In some embodiments, a selection strategy based on question similarity and masked question similarity is used to generate multiple candidate SQL queries using multiple prompts, specifically including:

[0014] Different example combinations are used and the presentation order of examples is dynamically adjusted. Each time a candidate SQL query is generated, the input prompt method is changed so that the large-scale language model generates multiple candidate SQL queries when understanding natural language queries. The example selection adopts a question similarity selection strategy and a masked question similarity selection strategy. The question similarity selection strategy calculates the embedding distance of the natural language query to be generated, and selects the top k questions that are closest to the natural language query of the test sample in the embedding space of the natural language query to be generated, so as to ensure that the example and the test sample have a high semantic similarity. The masked question similarity selection strategy masks specific tags related to the database model in the natural language query to be generated, and then calculates the similarity between the natural language query to be generated and the natural language query of the test sample.

[0015] In some embodiments, screening is performed based on the confidence score of each candidate SQL query, specifically including:

[0016] Based on the large-scale language model, the generation process of each candidate SQL query is analyzed to obtain the confidence score of each candidate SQL query, wherein the generation process analysis includes the analysis of the logical rationality and grammatical accuracy of each candidate SQL query, and the screening process is to delete the candidate SQL queries whose confidence score is lower than the preset threshold.

[0017] In some embodiments, the optimal SQL query is selected by a multi-selection method, specifically including:

[0018] The candidate SQL queries that have been screened by confidence scores are input into the large-scale language model, which is required to select the best query after comprehensively considering the pros and cons of each candidate query. At this stage, the large-scale language model conducts a comprehensive evaluation based on the feature understanding, contextual information, and fit of each candidate SQL query with the natural language question input by the user, and finally selects the best SQL query.

[0019] In some embodiments, the method further includes outputting the selected optimal SQL query and the reasons for its selection in a structured format.

[0020] In some embodiments, the method further comprises returning, by the large-scale language model, a reason for selecting the selected table in a JSON format.

[0021] According to a second aspect of an embodiment of the present disclosure, a database query generation system based on multi-selection optimization is provided, the system comprising:

[0022] A schema linking module, which is used to extract tables and columns related to natural language queries from database schemas through multiple hints;

[0023] A candidate SQL generation module, for generating multiple candidate SQL queries using multiple prompts based on the extracted tables and columns related to the natural language query and a selection strategy based on question similarity and masked question similarity;

[0024] The optimal SQL query generation module is used to screen the candidate SQL queries according to their confidence scores and select the optimal SQL query through a multi-selection method.

[0025] According to a third aspect of an embodiment of the present disclosure, an electronic device is provided, comprising a memory, a processor, and a computer program stored in the memory and executable on the processor, wherein when the processor executes the program, the steps of the above-mentioned method for generating a database query based on multi-selection optimization are implemented.

[0026] According to a fourth aspect of an embodiment of the present disclosure, a non-temporary computer-readable storage medium is provided, on which computer instructions are stored. When the instructions are executed by a processor, the steps of the above-mentioned database query generation method based on multi-selection optimization are implemented.

[0027] The embodiments of the present disclosure provide a database query generation method, system, electronic device and storage medium based on multi-selection optimization. The method is based on a multiple prompt generation mechanism and explores a wider search space by using different prompts and examples, thereby improving the accuracy and diversity of SQL generation. The method of the present invention introduces a dynamic feedback mechanism to verify the executableness of the generated SQL in the actual database in real time, ensuring that the generated SQL logic is reasonable and highly matched with the user query. This mechanism significantly improves the quality of generated SQL. The method of the present invention integrates historical query information, interaction information and database mode information, provides comprehensive context information, ensures that the generated SQL statement is accurate, can better utilize historical data, and overcomes the performance deficiencies of existing methods when the amount of data is small. The method of the present invention optimizes the generation and screening of candidate SQLs and adopts a more complex candidate SQL generation and screening mechanism, which can effectively improve the quality and accuracy of generated SQLs, especially when processing queries under complex modes, significantly improving the execution accuracy.

[0028] It is to be understood that the foregoing general description and the following detailed description are exemplary and explanatory only and are not restrictive of the present disclosure. BRIEF DESCRIPTION OF THE DRAWINGS

[0029] The accompanying drawings, which are incorporated in and constitute a part of this specification, illustrate embodiments consistent with the present invention and, together with the description, serve to explain the principles of the present invention.

[0030] Figure 1 is a flow chart of a database query generation method based on multi-selection optimization in an embodiment of the present invention;

[0031] Figure 2 It is a block diagram of a database query generation model system based on multi-selection optimization in a complex motion scene in an embodiment of the present invention;

[0032] Figure 3 It is a schematic diagram of the structure of a database query generation system based on multi-selection optimization in an embodiment of the present invention;

[0033] Figure 4 It is a schematic diagram of an electronic device in an embodiment of the present invention. DETAILED DESCRIPTION

[0034] The present invention will be further described in detail below in conjunction with the accompanying drawings and embodiments. It is to be understood that the specific embodiments described herein are only used to explain the present invention, rather than to limit the present invention. It should also be noted that, for ease of description, only parts related to the present invention, rather than all structures, are shown in the accompanying drawings.

[0035] It should be mentioned before discussing the exemplary embodiments in more detail that some exemplary embodiments are described as processes or methods depicted as flow charts. Although the flow charts describe the steps as sequential processes, many of the steps therein can be implemented in parallel, concurrently or simultaneously. In addition, the order of the steps can be rearranged. The process can be terminated when its operation is completed, but can also have additional steps not included in the accompanying drawings. The process can correspond to a method, function, procedure, subroutine, subprogram, etc.

[0036] The technical terms of the present invention are described as follows:

[0037] LLMs: Large Language Models, an artificial intelligence model that uses a large number of parameters for language understanding and generation, commonly used in natural language processing tasks.

[0038] ICL: In-Context Learning, a method that uses examples as prompts to guide the model to generate outputs that meet task requirements.

[0039] SQL: Structured Query Language, used to manage and query data in relational databases.

[0040] Database schema: A collection of information describing the database structure, including tables, fields, relationships, etc., which defines how the database is organized.

[0041] Text-to-SQL: is a natural language processing (NLP) technology that aims to convert user queries expressed in natural language (such as English) into structured query language (SQL) statements. Its main goal is to enable non-technical users to interact with the database through simple language without having to master complex SQL syntax.

[0042] Schema linking: An important step in database query processing and generation that involves identifying and associating database structural information, including tables and columns, that is relevant to a particular query.

[0043] In the field of Natural Language Processing (NLP), intelligent processing of text-to-SQL tasks is driven by large-scale language models (LLMs). However, current technologies suffer from insufficient accuracy in generating SQL queries when processing complex database schemas and queries, especially when facing challenging benchmarks such as BIRD and Spider, where LLMs perform much worse than human experts. This phenomenon shows that there is still a significant performance gap in the existing technologies in bridging the gap between natural language interfaces and database systems (NLIDB).

[0044] In addition, in the research field of text-to-SQL generation, with the development of large language models (LLMs) in recent years, methods based on in-context learning (ICL) have made significant progress. These methods automatically generate SQL queries by leveraging the relationship between natural language queries and database schemas. However, existing ICL methods still face many challenges when dealing with complex database schemas and queries, especially in terms of accuracy and robustness. The SQL methods for the above scenarios mainly include: SQL generation methods based on traditional models and SQL generation methods based on large models. This type of method still has the problem of insufficient accuracy. The reason is that existing methods usually rely on a single prompt or example, which limits the diversity of generated SQL queries and makes it difficult to cover all possible query scenarios.

[0045] To address this technical bottleneck in text-to-SQL generation, the inventors proposed a SQL generation method based on multi-select optimization. This method aims to improve the accuracy and robustness of generated SQL queries, such as Figure 1 As shown, a database query generation method based on multi-selection optimization includes the following steps:

[0046] S1, extract tables and columns related to natural language queries from the database schema through multiple hints;

[0047] S2, based on the extracted tables and columns related to the natural language query, and the selection strategy based on question similarity and masked question similarity, multiple prompts are used to generate multiple candidate SQL queries;

[0048] S3. Screen the candidate SQL queries according to their confidence scores, and select the optimal SQL query through a multi-selection method.

[0049] Specific steps are as follows Figure 2 As shown, S1 is pattern linking. In this step, the system extracts tables and columns related to natural language queries from the database schema through multiple prompts. By using different prompts, the system can more comprehensively identify database elements related to the query, thereby improving the recall rate of pattern linking. S2 is candidate SQL generation: after the pattern linking is completed, the system generates multiple candidate SQL queries using multiple prompts. This process ensures extensive exploration of potential queries, increases the generation diversity, and provides rich candidates for the subsequent selection steps. S3 is the selection of the final SQL: after generating multiple candidate SQL queries, the system screens them according to the confidence score of each query, and finally selects the optimal SQL query through a multi-selection method. This process is completely transparent to data analysts, ensuring that the generated queries are optimal in both accuracy and efficiency. Through the above steps, the present invention effectively solves the problem of insufficient accuracy of LLMs in the text-to-SQL task in the prior art, and significantly improves the execution accuracy in complex query scenarios. The main features of the SQL generation method based on multi-selection optimization include: multiple prompts and candidate query generation: by adopting a variety of different prompt strategies, the system can extract relevant information from the database schema and generate a variety of candidate SQL queries. This process includes pattern linking and selection strategies based on question similarity and masked question similarity to ensure that the generated queries cover a wider range of possibilities. Confidence screening and multiple selection mechanism: After generating multiple candidate SQL queries, the confidence scores are used to screen the candidate queries, and the optimal query is selected through a multiple selection method. This mechanism improves the accuracy and reliability of the generated results while ensuring the transparency and privacy protection of the data analysis process.

[0050] Schema linking aims to extract tables and columns related to natural language queries from the database schema through multiple prompts, so as to provide an accurate basis for subsequent SQL generation. The main purpose is to accurately identify tables and columns related to natural language queries from the database schema, so as to provide a stable structural foundation for subsequent SQL generation. Schema linking includes multiple steps, including input preparation, table and column linking, result integration, etc. This step includes:

[0051] Input preparation: Input database schema and natural language query request, where the database schema includes the structural information of each table and its columns in the database. Specifically, the system receives two main inputs: one is the database schema, which contains the structural information of all tables and their columns in the database, and describes the structural relationship and content distribution of the database in detail; the other is the natural language query, which is the query request expressed in natural language by the user. By inputting this information, the system can effectively map the user's natural language requirements to specific database elements in the subsequent steps.

[0052] Table and column linking: The database schema and the natural language query request are analyzed using a large-scale language model, and table links closely related to the natural language query request and specific column links required in the table links are identified from the database schema. Specifically, in the process of table and column linking, the system's primary task is to identify tables closely related to the natural language query. To this end, the system inputs the database schema and the natural language query into a large-scale language model (LLM) together, and allows the LLM to analyze the query content to extract the relevant tables that may be required. At this time, in order to ensure that the selected tables are explainable to the user, the system requires the LLM to return the reasons for selecting the selected tables in JSON format. This formatted output not only provides a specific list of the selected tables, but also gives a detailed description of the role of each table in generating the SQL query, so that the user can more intuitively understand the necessity of each table in the query. After identifying the relevant tables, the system will further use the LLM to identify the specific columns required in these tables to ensure that the necessary column information is included when generating SQL. The system will input the identified tables and query content again, and through multiple prompts, ensure that the LLM can accurately extract the columns related to the query at a high recall rate. LLM will eventually output a list of selected columns, along with the reasons for selecting each column, so that users can more clearly understand the role of each column in the SQL query and the basis for its selection.

[0053] Result integration: The table links and their column links are merged and deduplicated to obtain a column set for SQL generation. Specifically, the system integrates the information extracted from the aforementioned table links and column links to form a complete database schema mapping. During this process, the system will further merge the selected columns to eliminate potential duplicates and ensure that all selected columns have information integrity and consistency. By merging and deduplicating multiple results, the system ultimately obtains a column set for SQL generation. This processing not only improves overall operational efficiency, but also ensures that the final generated SQL query can accurately reflect the user's query requirements, thereby effectively improving the execution accuracy and efficiency of SQL queries. When the system finally returns the optimized column set, it will attach relevant reasons for the selection to help users better understand the composition and basis of the query structure.

[0054] Through the pattern linking step, the system not only achieves efficient docking between database patterns and natural language queries, but also fully utilizes the analysis and selection capabilities of large-scale language models, enhancing the reliability and accuracy of SQL statement generation. At the same time, the provision of selection reasons further improves the interpretability of the system, ensuring that users can have a clear understanding of each selected table and column during query processing. This process lays a solid foundation for subsequent SQL generation and effectively improves the system's processing efficiency and overall accuracy in complex query scenarios.

[0055] S2 is the generation of multiple candidate SQL queries. Based on the selection strategy of question similarity and masked question similarity, multiple candidate SQL queries are generated using multiple prompts, including:

[0056] Using different example combinations and dynamically adjusting the presentation order of examples, each time a candidate SQL query is generated, by changing the input prompt mode, the large-scale language model generates multiple candidate SQL queries when understanding the natural language query. Among them, the selection of examples adopts the problem similarity selection strategy and the masked problem similarity selection strategy. The problem similarity selection strategy calculates the distance of the embedding of the natural language query to be generated, and selects the top k questions that are closest to the natural language query of the test sample in the embedding space of the natural language query to be generated, so as to ensure that the example and the test sample have a high semantic similarity; the masked problem similarity selection strategy will mask the specific tags related to the database mode in the natural language query to be generated, and calculate the similarity between the natural language query to be generated and the natural language query of the test sample. Specifically, the generation of multiple candidate SQLs aims to generate multiple candidate SQL queries through diversified prompts to fully cope with the sensitivity of the large-scale language model (LLM) to the prompt words, and improve the diversity and accuracy of the generated results. In this step, the system comprehensively utilizes different prompt strategies, a small number of example selection methods, and candidate SQL generation technologies to ensure the flexibility and accuracy of the SQL generation process. The whole step mainly includes the following aspects:

[0057] Diversified prompt generation: In order to maximize the use of LLM's sensitivity to prompt words, the system adopts a variety of different prompt strategies, including using different combinations of few-shot examples and dynamically adjusting the order in which examples are presented. At each generation, the system changes the input prompt method so that LLM can produce more diverse interpretations when understanding natural language queries. By introducing different prompt strategies, the system is able to generate more possible SQL query versions for screening in subsequent steps, thereby improving the diversity and overall accuracy of the final SQL query.

[0058] Small number of example selection: The system selects appropriate examples for each test sample, ensuring that the selection of prompt samples has a certain similarity with the test sample. Specifically, the system selects a small set of examples from the training dataset and generates multiple prompts for subsequent SQL generation. To achieve this goal, the system adopts two different selection strategies, one based on question similarity and the other based on masked question similarity. The question similarity-based strategy selects the top k questions that are closest to the natural language questions of the test sample in the sentence embedding space by calculating the distance of sentence embeddings, thereby ensuring that the prompt sample has a high semantic similarity with the test sample. In the masked question similarity strategy, the system masks specific tags related to the database pattern in the question to ignore those words related to the specific information of the pattern, so as to judge the similarity between natural language questions more purely. Through the combination of these two strategies, the system can generate different prompts in different situations, thereby further enhancing the accuracy of the generated results.

[0059] Generate candidate SQL queries: The system uses the aforementioned diverse prompt combinations to generate multiple SQL query candidates through LLM. The SQL queries generated in this process reflect the different understanding paths and reasoning methods of LLM for natural language queries, ensuring the diversity and comprehensiveness of the generated SQL results. The generated SQL candidate queries include query versions with different structures and different logical orders. These candidate SQLs can more comprehensively cover the user's possible query needs.

[0060] The multiple candidate SQL generation step generates multiple SQL queries through diversified prompts to cope with the sensitivity of large-scale language models to prompts. This step involves selecting a small number of different examples and using strategies based on question similarity and masked question similarity to generate multiple candidate SQL queries. Subsequently, the system filters the candidate queries based on the confidence score and finally selects the most accurate SQL query through a multiple selection method, thereby improving the accuracy and robustness of the generation.

[0061] S3 is to select the final SQL generation and screen according to the confidence score of each candidate SQL query, specifically including: analyzing the generation process of each candidate SQL query based on a large-scale language model to obtain the confidence score of each candidate SQL query, wherein the generation process analysis includes the analysis of the logical rationality and grammatical accuracy of each candidate SQL query, and the screening process is to delete the candidate SQL queries with a confidence score lower than the preset threshold.

[0062] Select the best SQL query through multiple selection methods, including:

[0063] The candidate SQL queries that have been screened by confidence scores are input into the large-scale language model, which is required to select the best query after comprehensively considering the pros and cons of each candidate query. At this stage, the large-scale language model conducts a comprehensive evaluation based on the feature understanding, contextual information, and fit of each candidate SQL query with the natural language question input by the user, and finally selects the best SQL query.

[0064] Specifically, the goal of selecting the final SQL generation is to select the best solution from multiple candidate SQL queries and provide users with accurate and effective query results. In the previous multiple candidate SQL generation step, the system generated multiple candidate SQL queries through a combination of diverse prompts and a small number of examples. These queries not only reflect different understandings and reasoning methods of user natural language queries, but also cover a variety of potential SQL structures and logical paths. The following is the specific content of this step:

[0065] Confidence scoring: During the confidence scoring phase, the system evaluates each candidate SQL query to calculate its confidence score. This score is obtained by analyzing the generation process of each query through a large-scale language model (LLM), and is usually based on multiple factors such as the model's depth of understanding of the query, logical rationality, and grammatical accuracy. A higher confidence score indicates that the query is more logical and correct when it is generated, and is therefore more likely to meet user needs. This step is crucial because it provides a basis for subsequent query screening, ensuring that only those candidate SQLs with higher reliability can be further considered.

[0066] Filter candidate queries: Based on the confidence score, the system will filter candidate SQL queries. The goal of this process is to exclude queries that are below a certain preset threshold, thereby ensuring that the final selected queries have high credibility and relevance. Through this filtering mechanism, the system can effectively reduce erroneous or irrelevant queries, thereby improving the accuracy and reliability of the final results. This screening method not only reduces the time cost of users' subsequent screening in the query results, but also significantly improves the response efficiency of the system.

[0067] Multiple Choice: After filtering, the system will use a multiple choice approach to determine the final SQL query. Specifically, the system will submit the remaining candidate queries to the LLM, requiring it to select the best query after comprehensively considering the pros and cons of each candidate query. At this stage, the LLM will conduct a comprehensive evaluation of each query based on its deep understanding, contextual information, and fit with the natural language question entered by the user. In this way, the system is able to leverage the LLM's powerful understanding and reasoning capabilities to ensure that the final selected SQL query best meets the user's intent and needs.

[0068] Result output: Finally, the system outputs the final SQL query selected and the reasons for its selection in a structured format. This not only provides the final SQL query, but also enhances the interpretability of the system, allowing users to understand the basis for the selection and the logic behind it. This transparent output method allows users to clearly understand the query generation process when using the system, thereby improving user trust and satisfaction.

[0069] Through the above steps, the final SQL generation step is selected to effectively select the optimal solution from many candidate SQLs through confidence scoring, candidate query filtering, multiple selection and result output. This process ensures that the generated SQL query is not only logically reasonable, but also can accurately reflect the user's query needs, ultimately improving the overall performance of the system and user experience.

[0070] The method for generating multiple candidate SQL queries in an embodiment of the present invention aims to generate multiple candidate SQL queries through diversified prompts and a small number of example selection strategies. The core of this method is to make full use of the reasoning ability of large-scale language models to ensure that the generated queries can cover a variety of possible understanding and reasoning methods, thereby improving the accuracy and flexibility of text-to-SQL conversion. In the process of generating candidate SQL queries, the present invention adopts a diversified prompt strategy. These prompts are not only based on question similarity, but also use masked question similarity to ensure that the generated queries can reflect different semantic understandings. Specifically, the design of prompts includes question similarity prompts and masked question similarity prompts. In order to further enhance the diversity of generating candidate SQL queries, the present invention introduces a small number of example selection strategies. The implementation of this strategy includes example selection and example combination. Each time a candidate SQL query is generated, the present invention dynamically selects a small number of examples that are most relevant to the current query from the training data set. These examples include not only SQL queries similar to the current query, but also queries that perform well in similar contexts. In this way, LLM can understand queries from multiple perspectives and generate more accurate SQL. After selecting a small number of examples, the system combines these examples to generate diversified prompts. This process involves adjusting the order and combination of examples to ensure that the generated prompts can cover different reasoning paths. For example, the query structure of one example can be combined with the conditions of another example to generate a new candidate SQL query. In addition, the strategy for generating diversified prompts is a key component of the present invention. The present invention adopts a sequence transformation and content transformation method. By changing the order of a small number of examples, different prompts can be generated. This sequence transformation can guide LLM to reason from different angles and generate diversified SQL queries. When generating prompts, the present invention can also transform certain content in the example, such as replacing specific column names or table names. This transformation can help LLM adapt to different database modes and ensure that the generated SQL queries are still semantically valid.

[0071] The confidence scoring mechanism of the embodiment of the present invention calculates a confidence score for each candidate SQL query. The score is based on the query execution results, execution time and other relevant factors, and is intended to filter out low-confidence queries, thereby improving the accuracy of the final selection. The calculation method and formula of the confidence score, especially how to comprehensively consider factors such as execution results and execution time for evaluation.

[0072] The multiple selection strategy of the embodiment of the present invention evaluates the filtered candidate SQL queries through LLM and selects the optimal solution. This process not only improves the accuracy of the selection, but also enhances the robustness of the system, and can effectively cope with different input and query scenarios. The specific implementation of the multiple selection strategy includes how to use LLM to select the final query and the decision-making mechanism in the selection process.

[0073] A pattern connection method with multiple prompts and multiple reasonings for union in an embodiment of the present invention. In the pattern connection process, the present invention uses multiple different prompts to guide LLM to identify database tables and columns related to natural language queries. These prompts include not only prompts for directly asking related tables, but also prompts for guiding LLM to perform reasoning, such as requiring it to explain the relevance of each table. Through this diverse prompt strategy, LLM can understand the query from different perspectives, thereby generating a more comprehensive list of candidate tables and columns. This method introduces a mechanism of multiple reasonings, allowing LLM to perform further reasoning and verification based on these candidate results after initially generating candidate tables and columns. Specifically, LLM will analyze the initially generated candidate results, identify related tables and columns that may be missed, and supplement them. After completing multiple reasonings, this method will perform a union operation on the results of all candidate tables and columns. This process ensures that the final output candidate table and column list contains all possible relevant information, thereby maximizing the recall rate. In this way, even in the face of complex queries and changing database patterns, the system can effectively identify all information related to the query, ensuring that the generated SQL query is more accurate and comprehensive.

[0074] Another embodiment is used to illustrate a database query generation system based on multi-select optimization, such as Figure 3 As shown, the system 300 includes:

[0075] A schema linking module 310 for extracting tables and columns related to a natural language query from a database schema through multiple hints;

[0076] A candidate SQL generation module 320 for generating a plurality of candidate SQL queries using multiple prompts based on the extracted tables and columns related to the natural language query and a selection strategy based on question similarity and masked question similarity;

[0077] The optimal SQL query generation module 330 is used to screen the candidate SQL queries according to the confidence scores and select the optimal SQL query through a multiple selection method.

[0078] In addition to the above modules, the system 300 may also include other components. However, since these components are irrelevant to the content of the embodiment of the present disclosure, their illustration and description are omitted here.

[0079] The other specific working processes of the database query generation system 300 based on multiple selection optimization refer to the description of the above-mentioned database query generation method embodiment based on multiple selection optimization, which will not be repeated here.

[0080] Another embodiment is used to illustrate that the system of the present invention can also be used with the help of Figure 4 The architecture of the computing device shown is implemented. Figure 4 The architecture of the computing device is shown. Figure 4 As shown, a computer system 410, a system bus 430, one or more CPUs 440, an input / output 420, a memory 450, etc. The memory 450 can store various data or files used for computer processing and / or communication and program instructions executed by the CPU including the database query generation method based on multi-selection optimization of the embodiment. Figure 4 The architecture shown is only exemplary and can be adjusted according to actual needs when implementing different devices. Figure 4 One or more components in. The memory 450, as a computer-readable storage medium, can be used to store software programs, computer executable programs and modules, such as program instructions / modules corresponding to the database query generation method based on multi-selection optimization in the embodiment of the present invention (for example, the pattern linking module 310, the candidate SQL generation module 320 and the optimal SQL query generation module 330 in the database query generation system 300 based on multi-selection optimization). One or more CPUs 440 execute various functional applications and data processing of the system of the present invention by running the software programs, instructions and modules stored in the memory 450, that is, to implement the above-mentioned database query generation method based on multi-selection optimization, and the method includes the following steps:

[0081] Extract tables and columns relevant to natural language queries from database schemas through multiple hints;

[0082] Based on the extracted tables and columns related to the natural language query and the selection strategy based on question similarity and masked question similarity, multiple candidate SQL queries are generated using multiple prompts;

[0083] The candidate SQL queries are screened according to their confidence scores, and the optimal SQL query is selected through a multi-selection method.

[0084] Of course, the processor of the server provided in the embodiment of the present invention is not limited to executing the method operations described above, but can also execute related operations in the database query generation method based on multi-selection optimization provided in any embodiment of the present invention.

[0085] The memory 450 may mainly include a program storage area and a data storage area, wherein the program storage area may store an operating system, an application required for at least one function; the data storage area may store data created according to the use of the terminal, etc. In addition, the memory 450 may include a high-speed random access memory, and may also include a non-volatile memory, such as at least one disk storage device, a flash memory device, or other non-volatile solid-state storage device. In some instances, the memory 450 may further include a memory remotely arranged relative to one or more CPUs 440, and these remote memories may be connected to the device via a network. Examples of the above-mentioned network include, but are not limited to, the Internet, an intranet, a local area network, a mobile communication network, and combinations thereof.

[0086] The input / output 420 may be used to receive input digital or character information and generate key signal input related to user settings and function control of the device. The input / output 420 may also include a display device such as a display screen.

[0087] The embodiment of the present invention also provides a non-temporary computer-readable storage medium, on which a computer program is stored, and when the computer program is executed by the processor, the database query generation method based on multi-selection optimization described in the above embodiment is implemented. The computer-readable storage medium of the embodiment of the present invention can adopt any combination of one or more computer-readable media. The computer-readable medium can be a computer-readable signal medium or a computer-readable storage medium. The computer-readable storage medium can be, for example, but not limited to, an electrical, magnetic, optical, electromagnetic, infrared, or semiconductor system, device or device, or any combination of the above. More specific examples of computer-readable storage media (non-exhaustive list) include: an electrical connection with one or more wires, a portable computer disk, a hard disk, a random access memory (RAM), a read-only memory (ROM), an erasable programmable read-only memory (EPROM or flash memory), an optical fiber, a portable compact disk read-only memory (CD-ROM), an optical storage device, a magnetic storage device, or any suitable combination of the above. In this document, a computer-readable storage medium can be any tangible medium containing or storing a program, which can be used by an instruction execution system, device or device or used in combination with it.

[0088] Computer-readable signal media may include data signals propagated in baseband or as part of a carrier wave, which carry computer-readable program code. Such propagated data signals may take a variety of forms, including but not limited to electromagnetic signals, optical signals, or any suitable combination of the above. Computer-readable signal media may also be any computer-readable medium other than a computer-readable storage medium, which may send, propagate, or transmit a program for use by or in conjunction with an instruction execution system, apparatus, or device.

[0089] The program code contained on the storage medium may be transmitted using any appropriate medium, including but not limited to wireless, wire, optical cable, RF, etc., or any suitable combination of the foregoing.

[0090] In addition, other specific working processes of a non-temporary computer-readable storage medium refer to the description of the above-mentioned database query generation method embodiment based on multi-selection optimization, which will not be repeated here.

[0091] In summary, the technical solutions provided in the above embodiments are a method, system, electronic device and storage medium for generating database queries based on multi-selection optimization. The method is based on a multiple prompt generation mechanism and explores a wider search space by using different prompts and examples, thereby improving the accuracy and diversity of SQL generation. The method of the present invention introduces a dynamic feedback mechanism to verify the executableness of the generated SQL in the actual database in real time, ensuring that the generated SQL logic is reasonable and highly matched with the user query. This mechanism significantly improves the quality of generated SQL. The method of the present invention integrates historical query information, interaction information and database mode information, provides comprehensive context information, ensures that the generated SQL statement is accurate, can better utilize historical data, and overcomes the performance deficiencies of existing methods when the amount of data is small. The method of the present invention optimizes the generation and screening of candidate SQLs and adopts a more complex candidate SQL generation and screening mechanism, which can effectively improve the quality and accuracy of generated SQLs, especially when processing queries in complex modes, and significantly improves the execution accuracy.

[0092] In this document, the terms "comprises," "comprising" or any other variations thereof are intended to cover non-exclusive inclusion, such that a step or method that includes a series of elements includes not only those elements, but also includes other elements not explicitly listed, or also includes elements inherent to such step or method.

[0093] The above contents are further detailed descriptions of the present invention in combination with specific preferred embodiments, and it cannot be determined that the specific implementation of the present invention is limited to these descriptions. For ordinary technicians in the technical field to which the present invention belongs, several simple deductions or substitutions can be made without departing from the concept of the present invention, which should be regarded as falling within the protection scope of the present invention.

Claims

1. A database query generation method based on multi-selection optimization, characterized in that: The method comprises the following steps: Extract tables and columns relevant to natural language queries from database schemas through multiple hints; Based on the extracted tables and columns related to the natural language query and the selection strategy based on question similarity and masked question similarity, multiple candidate SQL queries are generated using multiple prompts; The candidate SQL queries are screened according to their confidence scores, and the optimal SQL query is selected through a multi-selection method.

2. The method for generating a database query based on multi-selection optimization according to claim 1, characterized in that: Extracting tables and columns related to natural language queries from the database schema using multiple hints includes the following steps: Input a database schema and a natural language query request, wherein the database schema includes structural information of each table and its columns in the database; Analyzing the database schema and the natural language query request using a large-scale language model, and identifying table links closely related to the natural language query request and specific column links required in the table links from the database schema; Information on the table link and its column link are merged and duplicated to obtain a column set for SQL generation.

3. The method for generating database query based on multi-selection optimization according to claim 1, characterized in that: Based on the selection strategy of question similarity and masked question similarity, multiple candidate SQL queries are generated using multiple prompts, including: Different example combinations are used and the presentation order of examples is dynamically adjusted. Each time a candidate SQL query is generated, the input prompt method is changed so that the large-scale language model generates multiple candidate SQL queries when understanding natural language queries. The example selection adopts a question similarity selection strategy and a masked question similarity selection strategy. The question similarity selection strategy calculates the embedding distance of the natural language query to be generated, and selects the top k questions that are closest to the natural language query of the test sample in the embedding space of the natural language query to be generated, so as to ensure that the example and the test sample have a high semantic similarity. The masked question similarity selection strategy masks specific tags related to the database model in the natural language query to be generated, and then calculates the similarity between the natural language query to be generated and the natural language query of the test sample.

4. The method for generating database query based on multi-selection optimization according to claim 1, characterized in that: Filter based on the confidence score of each candidate SQL query, including: Based on the large-scale language model, the generation process of each candidate SQL query is analyzed to obtain the confidence score of each candidate SQL query, wherein the generation process analysis includes the analysis of the logical rationality and grammatical accuracy of each candidate SQL query, and the screening process is to delete the candidate SQL queries whose confidence score is lower than the preset threshold.

5. The method for generating database query based on multi-selection optimization according to claim 4, characterized in that: Select the best SQL query through multiple selection methods, including: The candidate SQL queries that have been screened by confidence scores are input into the large-scale language model, which is required to select the best query after comprehensively considering the pros and cons of each candidate query. At this stage, the large-scale language model conducts a comprehensive evaluation based on the feature understanding, contextual information, and fit of each candidate SQL query with the natural language question input by the user, and finally selects the best SQL query.

6. The method for generating database query based on multi-selection optimization according to claim 1, characterized in that: The method further includes outputting the selected optimal SQL query and the reasons for its selection in a structured format.

7. The method for generating database query based on multi-selection optimization according to claim 2, characterized in that: The method also includes returning, by the large-scale language model, a selection reason for the selected table in a JSON format.

8. A database query generation system based on multi-selection optimization, characterized in that: The system comprises: A schema linking module, which is used to extract tables and columns related to natural language queries from database schemas through multiple hints; A candidate SQL generation module, for generating multiple candidate SQL queries using multiple prompts based on the extracted tables and columns related to the natural language query and a selection strategy based on question similarity and masked question similarity; The optimal SQL query generation module is used to screen the candidate SQL queries according to their confidence scores and select the optimal SQL query through a multi-selection method.

9. An electronic device comprising a memory, a processor, and a computer program stored in the memory and executable on the processor, characterized in that: When the processor executes the program, the steps of the database query generation method based on multi-selection optimization as described in any one of claims 1 to 7 are implemented.

10. A non-transitory computer-readable storage medium having computer instructions stored thereon, characterized in that: When the instructions are executed by the processor, the steps of the database query generation method based on multi-selection optimization as described in any one of claims 1 to 7 are implemented.

Images