A method and system for generating SQL queries for complex language questions based on LLM

Abstract

The present invention relates to the field of natural language processing technology, and provides an LLM-based SQL query generation method and system for complex language questions. The method comprises responding to a target language question request; splitting the requested target language question into one or more core clauses using the language model (LLM) and converting them into semantic vectors; calculating the matching score between each core clause and the question vector of each table in the schema knowledge document; sorting and filtering the returned recall results of the core clauses based on the score size and score threshold; merging and sorting the recall results of all core clauses to filter and generate the recall results of the target language question; and automatically generating SQL query statements using a prompt word generated by a Prompt script and calling the language model (LLM). This method, which does not rely on domain fine-tuning or require the reconstruction of wide tables, effectively solves the problem of difficulty in locating target tables when large language models process code value mapping, multiple tables, and complex business logic by constructing virtual tables and splitting the question into clauses containing complete semantics and matching them.

Description

Technical Field

[0001] The present invention relates to the field of natural language processing technology, and in particular to a method and system for generating SQL queries for complex language questions based on LLM, which is mainly used in scenarios of enterprise operation data analysis. Background Art

[0002] With the rapid development of artificial intelligence (AI), especially the rise of Large Language Models (LLMs), the technology for converting natural language into structured query language (NL2SQL) has made significant progress and has been widely used in various application fields. This technology primarily enables non-technical personnel to interact with databases using everyday language, simplifying the data query and analysis process.

[0003] The core of existing LLM-based NL2SQL technology is to convert users' natural language questions into SQL queries that computers can understand. This approach primarily relies on fixed database schema indexes and data augmentation techniques to assist the model in logical reasoning. Prompts are then generated to combine natural language questions with database DDL information. This approach appears simple and performs well for single-table queries. However, in real-world enterprise environments, faced with the diversity of database structures and the complexity of query requirements, it often fails to achieve the expected accuracy and flexibility. The following are some of the issues:

[0004] ① The complexity of multi-condition, cross-table queries. In business data analysis, queries often involve multiple conditions and require retrieving information from multiple related tables. However, this not only increases query complexity but also requires the database query system to be able to efficiently coordinate and process cross-table data.

[0005] ② The problem of associating coding values ​​with coding tables. When processing certain queries, the main data table may only contain coding values. In order to correctly parse the user's questions, they need to be correctly interpreted and converted by associating with a special coding table.

[0006] ③ Complex business logic. The complex or customized business logic commonly found in enterprise applications requires the database query system to be highly flexible and adaptable. For example, a balance table only records users with a balance greater than zero. To query users with a zero balance, a more complex operation is required, namely excluding those users with a balance greater than zero from all users.

[0007] Therefore, in real-world enterprise scenarios, ensuring that natural language questions accurately retrieve the corresponding database tables and subsequently generate correct SQL queries becomes a key challenge. Especially in the complex situations mentioned above, it is necessary to develop more intelligent and flexible methods to improve the performance and reliability of the NL2SQL system. Summary of the Invention

[0008] To address the above issues, the present invention proposes a complex language question parsing and SQL query method and system based on LLM. This method does not rely on domain fine-tuning or require the reconstruction of wide tables. Instead, it constructs virtual tables and splits complex questions into clauses containing complete semantics and performs matching. This effectively addresses the difficulties of large language models in locating target tables when processing code value mapping, multiple tables, and complex business logic, as well as the problems of generated SQL not meeting table storage logic. This improves the accuracy of NL2SQL. The above-mentioned inventive objectives of the present invention are achieved through the following technical solutions:

[0009] The present invention provides a method for generating SQL queries for complex language questions based on LLM, comprising the following steps:

[0010] Respond to target language question requests;

[0011] Based on the received target language question request, the language model (LLM) is combined with the prompt script to split the requested target language question into one or more core clauses that fully express the intent and / or independent conditions;

[0012] The split core clauses are converted into semantic vectors using embedding technology. The highest cosine similarity between each core clause and the question vector in each table in the offline generated schema knowledge document is calculated as the matching score. The matching scores are sorted and filtered based on the matching score size and score threshold, and the recall results of the core clauses are returned.

[0013] Merging and sorting the recall results of all core clauses, filtering and generating the recall results of the target language question, and ensuring that the number of tables in the recall results of the target language question does not exceed a predetermined limit;

[0014] The target language question generates the prompt word Prompt of the target language question through the Prompt script, and calls the language model LLM to automatically generate SQL query statements based on the recall results of the target language question and the table structure information in the schema knowledge document.

[0015] Furthermore, it also includes offline pre-processing of database tables to generate corresponding schema knowledge documents, including:

[0016] Use SQL scripts to query and export the database tables involved in the scenario and obtain the table structure information of each table. The table structure information includes table name, field name, data type, field type, primary and foreign key information, and field description;

[0017] Perform table description and / or field coding instructions based on the exported table structure information. The database table list also includes the table structure information of the virtual wide table. The table structure information of the virtual wide table is generated by constructing a corresponding subquery statement based on business requirements.

[0018] Generate a list of questions for each table in the database table list based on the language model LLM, and perform semantic vectorization on the question list to generate a corresponding question vector table for matching with the semantic vector of the core clause of the target language question sentence;

[0019] The table structure information, question list and question vector table are organized into a schema knowledge document for online response to query requests in the target language.

[0020] Furthermore, a table description and / or field coding description is performed based on the exported table structure information. The database table list also includes table structure information of a virtual wide table. The table structure information of the virtual wide table is generated by constructing a corresponding subquery statement according to business requirements, including:

[0021] Design a coding mapping table for the enumeration value field in the field coding description. The coding mapping table is used to correspond the coding value of the enumeration value field to the actual semantics;

[0022] Based on the table structure information of the original data table and the encoding mapping table in the database table list, a subquery statement is constructed. The subquery statement connects the original data table and the encoding mapping table through the LEFT JOIN operator to generate the table structure information of a virtual wide table containing the original data table and the encoding mapping table information. The virtual wide table is used to generate an SQL query statement according to the target language question and replace the original data table in the actual query.

[0023] Furthermore, based on the language model (LLM), a question list for each table in the database table list is generated, and the question list is semantically vectorized to generate a corresponding question vector table for matching with the semantic vector of the core clause of the target language question, including:

[0024] Build a prompt script based on the original data table and virtual wide table and call the language model LLM to generate the corresponding question list;

[0025] The pre-trained bidirectional encoder representation model BERT is used to perform semantic vectorization on the question list associated with the original data table and the virtual wide table to generate the corresponding question vector table.

[0026] Furthermore, based on the received target language question request, the language model LLM is combined with the prompt script to split the requested target language question into one or more core clauses that fully express the intent and / or independent conditions, including:

[0027] Perform text preprocessing on the target language questions in the target language question request, including removing redundant spaces, unifying character formats, removing stop words, and standardizing word forms;

[0028] The target language question is split by combining the language model LLM with the prompt script, and the results are output in a list format. The output results are one or more core clauses that fully express the intention and / or independent conditions.

[0029] Furthermore, the split core clauses are converted into semantic vectors using embedding technology, and the highest cosine similarity between each core clause and the question vector in each table in the offline generated schema knowledge document is calculated as the matching score, including:

[0030] Use the pre-trained bidirectional encoder representation model BERT to convert each core clause in the output result into the semantic vector of the core clause ;

[0031] Traverse the schema knowledge document to extract the table structure information of each table and the corresponding question vector table , where the normal vector table for each table is obtained , ask the normal vector table The data format is:

[0032] ;in, Represents the jth normal vector of the i-th table;

[0033] The semantic vector of each core clause A table of query vectors for each table in the schema knowledge document Each question vector in is calculated as the matching similarity by calculating the cosine similarity, and the matching similarity is,

[0034] ;

[0035] Based on the matching similarity between the question vector table of each table and the semantic vector of the core clause, the first matching similarity, i.e. the highest cosine similarity, is used as the matching score of the question vector table; the matching score is,

[0036] ;

[0037] The matching scores of the original data table and the virtual wide table are sorted from large to small, and a limited number of question vector tables are screened out according to the preset score threshold as the recall results of the core clause, and the matching scores of the question vector tables do not exceed the preset score threshold.

[0038] Furthermore, the recall results of all core clauses are merged and sorted, and the recall results of the target language question are generated by screening, and the number of tables in the recall results of the target language question does not exceed a predetermined limit, including:

[0039] Initialize an empty list to store the recall result list of each core clause. The recall result list of each core clause includes table names sorted in descending order of matching scores.

[0040] Traverse the recall result list of each core clause, filter the table names with the highest matching scores to remove duplicates, and merge them to generate the recall result list of the target language question. The number of labels in the recall result list of the target language question does not exceed the preset upper limit.

[0041] Furthermore, the target language question generates the prompt word Prompt of the target language question through the Prompt script, and calls the language model LLM to automatically generate SQL query statements based on the recall results of the target language question and the table structure information in the schema knowledge document, including:

[0042] Based on the target language question, the recall results of the target language question, and the table structure information in the schema knowledge document, a Prompt script is constructed to call the language model LLM to generate the corresponding prompt word Prompt, and the corresponding SQL query statement is generated based on the intent and / or independent conditions of the target language question.

[0043] Based on the same inventive concept, the present invention further provides a system for generating SQL queries for complex language sentences based on LLM, which adopts the above-mentioned method for generating SQL queries for complex language sentences based on LLM, including:

[0044] A response module is used to respond to the target language question request and, based on the received target language question request, split the requested target language question into one or more core clauses that fully express the intent and / or independent conditions using the language model (LLM) combined with the prompt script;

[0045] The data processing module is used to convert the split core clauses into semantic vectors using embedding technology, calculate the highest cosine similarity between each core clause and the question vector in each table in the offline-generated schema knowledge document as the matching score, sort and filter according to the matching score size and score threshold, and return the recall results of the core clauses; merge and sort the recall results of all core clauses, filter and generate the recall results of the target language question sentences, and ensure that the number of tables in the recall results of the target language question sentences does not exceed the predetermined limit value;

[0046] The result output module is used to generate the prompt word Prompt of the target language question through the Prompt script, and call the language model LLM to automatically generate SQL query statements based on the recall results of the target language question and the table structure information in the schema knowledge document.

[0047] Furthermore, it also includes an offline processing module, which is used to query and export the database table list involved in the scenario through SQL scripts to obtain the table structure information of each table, where the table structure information includes table name, field name, data type, field type, primary and foreign key information and field description; perform table description and / or field coding description based on the exported table structure information, wherein the database table list also includes the table structure information of the virtual wide table, and the table structure information of the virtual wide table is generated by constructing corresponding sub-query statements according to business needs; generate a question list for each table in the database table list based on the language model LLM, and semantically vectorize the question list to generate a corresponding question vector table for matching with the semantic vector of the core clause of the target language question; organize the table structure information, question list and question vector table into a schema knowledge document for online response to queries requested by the target language question.

[0048] Compared with the prior art, the present invention has at least one of the following beneficial effects:

[0049] The present invention provides an LLM-based SQL query generation method and system for complex language questions. Without domain fine-tuning or rebuilding wide tables, by constructing virtual wide tables and splitting complex questions into clauses containing complete semantics and matching them, it can effectively solve the problems of large language models in processing code value mapping, multiple tables, and complex business logic, such as difficulty in locating target tables and the generated SQL not meeting table storage logic, thereby ultimately improving the accuracy of NL2SQL.

[0050] (1) By splitting complex questions involving multiple tables into multiple core clauses containing key information, each core clause focuses more on a specific table, enabling more accurate location of the relevant database table. Merging the table recall results corresponding to multiple core clauses helps improve the accuracy of table location and avoids ambiguity and redundancy in table recall when querying multiple tables.

[0051] (2) By using the subquery method to integrate complex business logic and complex code value mapping knowledge, more accurate business background information can be input into large models without changing the production environment database tables, thereby greatly improving the ability to infer correct SQL logic, which is very suitable for real-world enterprise scenarios. BRIEF DESCRIPTION OF THE DRAWINGS

[0052] Figure 1 Flowchart of the steps of the method for generating SQL queries for complex language questions based on LLM of the present invention;

[0053] Figure 2 This is a working principle diagram of the SQL query generation method for complex language questions based on LLM of the present invention. DETAILED DESCRIPTION

[0054] To make the purpose, technical solutions, and advantages of the embodiments of this application more clear, the technical solutions in the embodiments of this application will be clearly and completely described below in conjunction with the drawings in the embodiments of this application. Obviously, the described embodiments are part of the embodiments of this application, not all of the embodiments. Based on the embodiments in this application, all other embodiments obtained by ordinary technicians in this field without making creative efforts are within the scope of protection of this application.

[0055] Those skilled in the art will appreciate that, unless otherwise stated, the singular forms "a," "an," "said," and "the" used herein may also include plural forms. It should be further understood that the term "comprising" used in the specification of the present invention refers to the presence of the stated features, integers, steps, operations, elements, and / or components, but does not preclude the presence or addition of one or more other features, integers, steps, operations, elements, components, and / or groups thereof.

[0056] First embodiment

[0057] The core of NL2SQL technology, which converts natural language queries into SQL statements based on large language models (LLMs), is to convert users' natural language queries into SQL statements that computers can understand and execute. While this process may appear simple on the surface, the key challenge lies in accurately parsing the query intent in natural language and effectively converting it into efficient and correct SQL queries. Currently, existing LLM-based NL2SQL methods primarily rely on data augmentation (RAG) technology, which improves the model's reasoning capabilities by inputting query-related domain knowledge as external information into the large model. This process typically involves the following steps:

[0058] (1) Database Schema Index Creation: First, analyze the schema of the target database and create an index to quickly identify the database tables related to the user's question.

[0059] (2) Natural language question recall related tables: Based on the natural language questions provided by the user, the system needs to accurately identify and recall the database tables corresponding to the data entities mentioned in the questions.

[0060] (3) Assemble prompts and generate SQL: Combine natural language questions with the recalled table structure information DDL into one or more prompts, and then use the reasoning ability of LLM to convert these prompts into actual SQL query statements.

[0061] Although this technology has demonstrated high accuracy when processing single-table queries, in actual enterprise operational data analysis scenarios, users' natural language queries are often more complex, involving multi-table associations, multi-condition screening, and specific data processing requirements. Therefore, when faced with these complex queries, the accuracy and efficiency of existing technologies are often limited. Based on the above problems, the inventors proposed a new method that combines in-depth analysis of database table lists, field coding descriptions, and virtual wide table structures with advanced large-scale language models (LLMs) for reasoning and optimization, thereby achieving more accurate and efficient SQL generation. The specific implementation method is as follows:

[0062] like Figure 1 As shown, the present invention provides a method for generating SQL queries for complex language questions based on LLM, comprising the following steps:

[0063] Respond to target language question requests;

[0064] Based on the received target language question request, the language model (LLM) is combined with the prompt script to split the requested target language question into one or more core clauses that fully express the intent and / or independent conditions;

[0065] The split core clauses are converted into semantic vectors using embedding technology. The highest cosine similarity between each core clause and the question vector in each table in the offline generated schema knowledge document is calculated as the matching score. The matching scores are sorted and filtered based on the matching score size and score threshold, and the recall results of the core clauses are returned.

[0066] Merge and sort the recall results of all core clauses, and filter to generate the recall results of the target language question, and the number of tables in the recall results does not exceed the predetermined limit;

[0067] The target language question generates the prompt word Prompt of the target language question through the Prompt script, and calls the language model LLM to automatically generate SQL query statements based on the recall results of the target language question and the table structure information in the schema knowledge document.

[0068] It should be noted that for complex questions with multiple conditions, the original natural language questions often contain multiple conditions and involve multiple database tables, which makes direct table recall very difficult, because a natural language question often contains information from multiple fields, which can easily cause unnecessary matching errors. Therefore, this technical solution splits complex natural language questions into multiple clauses with complete semantics, and each clause focuses on specific query conditions and information. In this way, confusion can be reduced and the accuracy of each clause can be improved. The semantics of each clause and the matching relationship between the tables also become clearer and more controllable.

[0069] Each clause after splitting corresponds to a specific database table. To further improve the accuracy of table recall, this embodiment designs a detailed query list for each table. These query lists contain various query conditions, fields, business scenarios, etc. related to the table, which can help quickly and accurately locate the relevant database table when processing each clause. When each core clause is matched with the query list of the corresponding table, it can ensure that the clause is accurately matched to the target table, thereby greatly improving the accuracy of table recall.

[0070] When recalling tables for each clause, the final target table list is obtained by merging the table recall results corresponding to each clause. This target table list not only accurately includes all tables relevant to the query, but also effectively filters out irrelevant tables, improving query efficiency. This approach avoids unnecessary computation and query complexity caused by recalling a large number of irrelevant tables at once.

[0071] As enterprise data continues to grow and business scenarios change, the query list for each table can be continuously expanded and optimized. By adding more query examples, the system can adapt to different query requirements and scenarios, continuously improving table location capabilities. This iterative optimization capability is particularly important in enterprise production environments, as database structures and business requirements often change in practice. Therefore, being able to dynamically update the query list ensures that the table recall process remains accurate and effective. By continuously enriching the query list for each table, the boundaries of table location capabilities can be gradually expanded. As time passes and data grows, the system can gradually adapt to the increasingly complex business needs and data structure changes in the enterprise environment. This continuous optimization mechanism ensures that the system not only has the ability to handle current business needs, but also can cope with future changes in business needs and database architecture adjustments, improving the system's long-term availability and stability.

[0072] Furthermore, it also includes offline pre-processing of database tables to generate corresponding schema knowledge documents;

[0073] Offline preprocessing of database tables to generate corresponding schema knowledge documents includes:

[0074] Use SQL scripts to query and export the database tables involved in the scenario and obtain the table structure information of each table. The table structure information includes table name, field name, data type, field type, primary and foreign key information, and field description;

[0075] Perform table description and / or field coding instructions based on the exported table structure information. The database table list also includes the table structure information of the virtual wide table. The table structure information of the virtual wide table is generated by constructing a corresponding subquery statement based on business requirements.

[0076] Generate a list of questions for each table in the database table list based on the language model LLM, and perform semantic vectorization on the question list to generate a corresponding question vector table for matching with the semantic vector of the core clause of the target language question sentence;

[0077] The table structure information, question list and question vector table are organized into a schema knowledge document for online response to query requests in the target language.

[0078] Furthermore, a table description and / or field coding description is performed based on the exported table structure information. The database table list also includes table structure information of a virtual wide table. The table structure information of the virtual wide table is generated by constructing a corresponding subquery statement according to business requirements, including:

[0079] Design a coding mapping table for the enumeration value field in the field coding description. The coding mapping table is used to correspond the coding value of the enumeration value field to the actual semantics;

[0080] Based on the table structure information of the original data table and the encoding mapping table in the database table list, a subquery statement is constructed. The subquery statement connects the original data table and the encoding mapping table through the LEFT JOIN operator to generate the table structure information of a virtual wide table containing the original data table and the encoding mapping table information. The virtual wide table is used to replace the original data table when generating an SQL query statement according to the target language question sentence.

[0081] It should be noted that when dealing with complex business logic and code value mapping, the original table in the database may contain a large number of code values, and these code values ​​often need to be correctly interpreted and applied through additional mapping relationships. In this case, it is more complicated to query and operate directly on the database table. In order to simplify this process, the present embodiment adopts the concept of virtual table. By constructing a virtual table, the relevant information of multiple original tables can be integrated into a virtual table, and complex business logic and code value mapping are added at the same time, making the execution of the query more concise and efficient.

[0082] A virtual table isn't just a static view of data; its structure is dynamically maintained. Whenever an enterprise's database table structure changes, the virtual table's structure is adjusted accordingly to maintain accuracy and timeliness. This allows the virtual table to reflect business needs and database changes in real time without impacting the original table structure or production environment. This eliminates the need for enterprises to modify the original database tables during daily operations, ensuring system stability and production continuity.

[0083] The construction of virtual tables enables the Large Language Model (LLM) to generate SQL queries more efficiently without adjusting its original reasoning capabilities. By leveraging the structured business information and encoding mappings provided by virtual tables, the LLM can better understand the complex logic and conditions in natural language questions, thereby generating more precise and efficient SQL queries. This approach avoids the need for fine-tuning the large model, ensuring model stability while significantly improving its reasoning capabilities in complex business scenarios.

[0084] By constructing virtual tables and integrating them with large models, enterprises do not need to make any changes to their actual production environments, particularly in terms of database table structure. This not only ensures the integrity and stability of the existing database but also avoids the technical risks associated with database architecture changes. This non-invasive model allows enterprises to flexibly address complex business needs while improving the accuracy and efficiency of SQL query generation using large language models.

[0085] Furthermore, based on the language model (LLM), a question list for each table in the database table list is generated, and the question list is semantically vectorized to generate a corresponding question vector table for matching with the semantic vector of the core clause of the target language question, including:

[0086] Based on the original data table and the virtual wide table, a prompt script is built to call the language model LLM and generate a corresponding question list;

[0087] The pre-trained bidirectional encoder representation model BERT is used to perform semantic vectorization on the question list associated with the original data table and the virtual wide table to generate the corresponding question vector table.

[0088] Furthermore, based on the received target language question request, the language model LLM is combined with the prompt script to split the requested target language question into one or more core clauses that fully express the intent and / or independent conditions, including:

[0089] Perform text preprocessing on the target language questions in the target language question request, including removing redundant spaces, unifying character formats, removing stop words, and standardizing word forms;

[0090] The target language question is split by combining the language model LLM with the prompt script, and the results are output in a list format. The output results are one or more core clauses that fully express the intention and / or independent conditions.

[0091] Furthermore, the split core clauses are converted into semantic vectors using embedding technology, and the highest cosine similarity between each core clause and the question vector in each table in the offline generated schema knowledge document is calculated as the matching score, including:

[0092] Use the pre-trained bidirectional encoder representation model BERT to convert each core clause in the output result into the semantic vector of the core clause ;

[0093] Traverse the schema knowledge document to extract the table structure information of each table and the corresponding question vector table , where the normal vector table for each table is obtained , ask the normal vector table The data format is:

[0094] ;in, Represents the jth normal vector of the i-th table;

[0095] The semantic vector of each core clause A table of query vectors for each table in the schema knowledge document Each question vector in is calculated as the matching similarity by calculating the cosine similarity, and the matching similarity is,

[0096] ;

[0097] Based on the matching similarity between the question vector table of each table and the semantic vector of the core clause, the first matching similarity, i.e. the highest cosine similarity, is used as the matching score of the question vector table; the matching score is,

[0098] ;

[0099] The matching scores of the original data table and the virtual wide table are sorted from large to small, and a limited number of question vector tables are screened out according to the preset score threshold as the recall results of the core clause, and the matching scores of the question vector tables do not exceed the preset score threshold.

[0100] Furthermore, the recall results of all core clauses are merged and sorted, and the recall results of the target language question are generated by screening, and the number of tables in the recall results does not exceed the predetermined limit, including:

[0101] Initialize an empty list to store the recall result list of each core clause. Each recall result list includes table names sorted in descending order of matching scores.

[0102] Traverse the recall result list of each core clause, filter the table names with the highest matching scores to remove duplicates, and merge them to generate the recall result list of the target language question. The number of labels in the recall result list of the target language question does not exceed the preset upper limit.

[0103] Furthermore, the target language question generates the prompt word Prompt of the target language question through the Prompt script, and calls the language model LLM to automatically generate SQL query statements based on the recall results of the target language question and the table structure information in the schema knowledge document, including:

[0104] Based on the target language question, the recall result of the target language question and the table structure information in the schema knowledge document, a Prompt script is constructed to call the language model LLM to generate the corresponding prompt word Prompt and generate the corresponding SQL query statement in combination with the intention and / or the independent condition of the target language question.

[0105] Second embodiment

[0106] Based on the same inventive concept, the present invention provides a system for generating SQL queries for complex language sentences based on LLM, which adopts the above-mentioned method for generating SQL queries for complex language sentences based on LLM, including:

[0107] A response module is used to respond to the target language question request and, based on the received target language question request, split the requested target language question into one or more core clauses that fully express the intent and / or independent conditions using the language model (LLM) combined with the prompt script;

[0108] The data processing module is used to convert the split core clauses into semantic vectors using embedding technology, calculate the highest cosine similarity between each core clause and the question vector in each table in the offline-generated schema knowledge document as the matching score, sort and filter according to the matching score size and score threshold, and return the recall results of the core clauses; merge and sort the recall results of all core clauses, filter and generate the recall results of the target language question, and ensure that the number of tables in the recall results does not exceed the predetermined limit;

[0109] The result output module is used to generate the prompt word Prompt of the target language question through the Prompt script, and call the language model LLM to automatically generate SQL query statements based on the recall results of the target language question and the table structure information in the schema knowledge document.

[0110] Furthermore, it also includes an offline processing module, which is used to query and export the database table list involved in the scenario through SQL scripts to obtain the table structure information of each table, where the table structure information includes table name, field name, data type, field type, primary and foreign key information and field description; perform table description and / or field coding description based on the exported table structure information, wherein the database table list also includes the table structure information of the virtual wide table, and the table structure information of the virtual wide table is generated by constructing corresponding sub-query statements according to business needs; generate a question list for each table in the database table list based on the language model LLM, and semantically vectorize the question list to generate a corresponding question vector table for matching with the semantic vector of the core clause of the target language question; organize the table structure information, question list and question vector table into a schema knowledge document for online response to queries requested by the target language question.

[0111] This system adopts the SQL query generation method for complex language questions based on LLM described in the first embodiment. The relevant content has been explained in detail in the previous section and will not be repeated here.

[0112] Third embodiment

[0113] In order to enable those skilled in the art to deeply understand the technical solution and practical application of the present invention, Figure 2 The third embodiment is further described with detailed operation steps. Through the detailed analysis of this embodiment, it can be clearly demonstrated how to achieve the technical goals of the present invention of improving the accuracy of natural language to SQL conversion (NL2SQL) technology, processing complex queries, and supporting virtual wide tables through specific operation processes.

[0114] like Figure 2 As shown in the figure, in this embodiment, the LLM-based SQL query generation method for complex natural language questions includes both online and offline components. The online component is responsible for receiving complex natural language questions input by users in real time, combining them with the database table structure information in the schema knowledge document, and invoking the LLM to generate the corresponding SQL query statement. The offline component includes tasks such as data preprocessing, schema knowledge document organization, virtual wide table generation, and model training. This provides necessary support and optimization for the online component, ensuring the system's efficiency and accuracy when handling multi-table joins and complex conditional queries.

[0115] In the offline part, the main process includes the following steps: (1) Obtain the database table structure involved in the scenario, including the table name, Chinese definition of the table, column name, column type, Chinese definition of the column, and primary and foreign key information. (2) Based on the prior knowledge of data experts, pre-construct sub-query statements for the coded values ​​and complex business queries in each table, and form a new virtual library table structure. The virtual library table result is the virtual wide table of the first embodiment. (3) Generate a list of example questions for each table based on the open source large language model LLM. (4) Vectorize the question lists corresponding to each table. (5) Organize the database table structure involved in the scenario into a knowledge document.

[0116] like Figure 2 As shown, the system generates and retrieves database table structure information. Based on business requirements and expert prior knowledge, it pre-constructs subquery statements for each table's coded values ​​and complex business queries, forming a virtual wide table. The system then updates the original table structure description to support flexible queries and invokes the Large Language Model (LLM) to generate example questions and prompts. This list of example questions for each table serves as a guide for users on how to pose queries. The generated list of questions is vectorized and stored, serving as the basis for subsequent queries. Finally, the modified table structure information and the vectorized list of questions together form a schema knowledge document, which supports SQL generation in the online phase.

[0117] Specifically, the offline part's main tasks are to preprocess the database table structure, generate a list of relevant example questions, and build a knowledge document that supports SQL generation. The steps are as follows:

[0118] Step S1: Get the database table results involved in the scenario:

[0119] Clarify the list of libraries and tables involved in the scenario, and obtain the table structure information (DDL information) of each table by building a script. For example, for MySQL / Tidb databases, use the following SQL script:

[0120] SELECT table_schema,

[0121] table_name,

[0122] COLUMN_NAME,

[0123] DATA_TYPE,

[0124] COLUMN_TYPE,

[0125] COLUMN_KEY,

[0126] COLUMN_COMMENT

[0127] FROM information_schema.columns

[0128] WHERE table_name IN ( / *table list* / )

[0129] Export the data obtained by the SQL script and hand it over to the data expert to add table descriptions and some field coding instructions;

[0130] A table description includes the table's meaning and the data stored in the table. For example, for a user table, the table's meaning is "user details." The data content description is "basic user information related to registration, including age, gender, registration date, zodiac sign, registered app type, province, and city," but does not include tags.

[0131] Some field coding descriptions supplement fields with few enumeration values ​​or commonly used enumeration values. For example, for gender, the table stores data as 0, 1, 2, 3, and 4. The field coding description must indicate 0: Unknown, 1: Male, 2: Female, 3: Suspected Male, 4: Suspected Female. Order type stores data as 1, 2, 3, 4, 5, ..., and the corresponding coding values ​​are 1: Dine-in, 2: Takeout, 3: Takeout, 4: Pay, 5: Stored Value, ...

[0132] Step S2: The specific operations of constructing the subquery statement are:

[0133] In the data management process, we design code mapping tables to address the situation where there are too many code values, and we construct subquery statements to address the situation where there are some complex SQL logic.

[0134] There is a code mapping table, such as the customer_coupon_usable table, whose table structure is as follows:

[0135] CREATE TABLE `customer_coupon_usable` (

[0136] `customer_id` bigint(20) NOT NULL COMMENT"Customer ID",

[0137] `template_id` bigint(20) NOT NULL COMMENT "voucher template id",

[0138] `usable_coupon_cnt` decimal(15, 2) NULL COMMENT "Coupon issuance history cumulative number of coupons issued")

[0139] COMMENT"Member-User Coupon Redemption Template Summary Table"

[0140] Since the coupon template IDs all have unique coding values ​​such as 1025035307825045501, the user's actual natural language questions usually do not directly involve these specific coding values. Therefore, the present invention associates this table with the coupon template ID coding table and constructs a virtual wide table to optimize data query.

[0141] The table structure of the user available coupons table after transformation is as follows, with a new template_name field added to contain the coupon template name:

[0142] CREATE TABLE `customer_coupon_usable` (

[0143] `customer_id` bigint(20) NOT NULL COMMENT"Customer ID",

[0144] `template_id` bigint(20) NOT NULL COMMENT "voucher template id",

[0145] `template_name` varchar(120) NOT NULL COMMENT "Ticket template name",

[0146] `usable_coupon_cnt` decimal(15, 2) NULL COMMENT "Coupon issuance history cumulative number of coupons issued")

[0147] COMMENT"Member-User Coupon Redemption Template Summary Table"

[0148] The code for adding a subquery statement to the user's available coupons table is: SELECT a.*,

[0149] b.template_name

[0150] FROM customer_coupon_usable a

[0151] LEFT JOIN dim_template b

[0152] ON a.template_id = b.template_id

[0153] When the coupon template name appears in the subsequent natural language question, it can be directly matched with the user's available coupon table and the generated SQL

[0154] SELECT ...

[0155] FROM customer_coupon_usable ...

[0156] Partially replaced with

[0157] SELECT ...

[0158] FROM

[0159] (SELECT a.*,

[0160] b.template_name

[0161] FROM customer_coupon_usable a

[0162] LEFT JOIN dim_template b

[0163] ON a.template_id = b.template_id) ...

[0164] When querying complex business logic, such as the balance table (customer_account), which only stores all users with a balance greater than 0, natural language questions about users with a balance of 0 often find the table, but fail to generate SQL logic that satisfies this storage requirement. Therefore, the present invention also utilizes the subquery construction method to support queries for users with a balance of 0 in the balance table.

[0165] The balance table (customer_account) has the following structure:

[0166] CREATE TABLE `customer_account` (

[0167] `customer_id` bigint(20) NULL COMMENT "User",

[0168] `usable_account` decimal(12, 2) NULL COMMENT "Current available balance")

[0169] COMMENT "User balance details"

[0170] When locating the balance table, the SQL

[0171] SELECT ...

[0172] FROM customer_account ...

[0173] Partially replaced with

[0174] SELECT ...

[0175] FROM

[0176] (SELECT a.customer_id,

[0177] COALESCE(b.usable_account, 0) AS usable_account

[0178] FROM customer_detail a

[0179] LEFT JOIN customer_account b

[0180] ON a.customer_id = b.customer_id) ...

[0181] Analogously, the balance sheet is 0, and there are a series of questions such as "not consumed" and "coupon not received".

[0182] Step 3: The specific operations for constructing a question list based on LLM are:

[0183] For each table, a list of questions is constructed, indicating which questions can be asked to retrieve the corresponding data. This method uses a method based on table structure combined with LLM to generate a large number of question lists. This method involves writing a general script, constructing a prompt in the script, and calling LLM. For different tables, a list of questions can be generated by simply changing the table creation statement in the prompt. Preferably, data experts provide some example questions.

[0184] Here's an example prompt:

[0185] You are an intelligent assistant that is good at generating natural language questions based on table structures. Do your best to generate questions based on the structural information of the following database table. The questions can obtain the corresponding data through this table. Here is the structure information of the database table: CREATE TABLE IF NOT EXISTS customer_consume( `customer_id` bigint(20)NOT NULL COMMENT"User ID", `order_type` int(11) NOT NULL COMMENT"Order type, 0: Dine-in, 1: Take-out, 2: Takeout, 3: Pay-as-you-go, 4: Store-based", `order_source` int(11) NOT NULL COMMENT"Order source, 1: WeChat applet, 2: Alipay applet, 3: Meituan takeout, 4: Ele.me takeout", `consume_type` int(11) NOT NULL COMMENT"Consumption type, 1: Product order, 2: Recharge order, 3: Live broadcast order, 4: Pay-as-you-go", `total_cnt` int(11) NULL COMMENT"Cumulative number of consumption orders", `total_amt` decimal(15, 2) NULL COMMENT"Cumulative consumption amount", `lastest_consume_time` date NULL COMMENT "Last purchase date, format: YYYY-MM-DD") COMMENT "User consumption channel type summary table, statistics of users' cumulative purchase orders, cumulative purchase amount, and latest purchase date by order type, order source, and consumption type, excluding stores." Here are some example questions, such as: / * Circle users with a cumulative purchase amount between 30 and 60 yuan; * / / * Users with more than two historical purchases; * / / * Users making their first purchase this month* / Requirements: 1. The format and grammar of the questions should be as diverse as possible, aiming to obtain user customer data from different dimensions; 2. The questions should simulate natural language questions input by users. 3. Simply output the question list without any introduction. The format should be List;

[0186] After the question list is generated, it can be handed over to business experts for manual screening, retaining questions that meet actual query needs or common scenarios, or making certain modifications to questions that do not conform to user language habits to form the final question list.

[0187] Step 4: The specific operations for vectorizing the question list are:

[0188] Utilizing embedding technology, specifically the pre-trained bidirectional encoder representation model BERT, deep semantic vectorization is performed on the question list associated with each database table. This process maps natural language questions into a high-dimensional continuous vector space, ensuring that semantically similar questions have similar vector representations in this space. This approach enables the construction and storage of a comprehensive list of table-question vector correspondences, providing a foundation for subsequent semantic matching and table database location.

[0189] Step 5: Organize the knowledge generated in steps 1-4 into a knowledge document.

[0190] The database table structure information relevant to the scenario, including but not limited to metadata such as table names and their Chinese meanings, column names and types, column semantics, and primary and foreign key associations, is systematically organized and constructed into a detailed schema knowledge document, along with natural language question examples generated based on each table and their corresponding vectorized representations. This knowledge document is intended to provide the necessary background information and reference for converting user-generated natural language queries into precise SQL queries in subsequent stages. By integrating these structured and unstructured data assets, semantic consistency and accuracy are ensured from natural language processing to SQL query generation, thereby enhancing the reliability and efficiency of query conversion.

[0191] The following is an example of a schema knowledge document:

[0192] {

[0193] "customer_coupon_usable": {

[0194] "table_cn":"Member-User Available Coupons",

[0195] "comment":"Count the cumulative number of coupons issued by each user's coupon template",

[0196] "templates": [

[0197] "Please circle the users who have not received more than one coupon."

[0198] "Users who have only received one coupon",

[0199] "Users without available coupons",

[0200] "Users without available coupon assets",

[0201] "Count the cumulative number of coupons issued by each user's coupon template"

[0202] ],

[0203] "desc": "Table name: customer_coupon_usable [Member-user available coupons, statistics on the cumulative number of coupons issued on each coupon template of the user]\ncustomer_id [customer ID, bigint], template_id [coupon template id, bigint], template_name [coupon template name, varchar(120)], usable_coupon_cnt [the cumulative number of coupons issued in the coupon issuance history, decimal]",

[0204] "table_desc": "Table name: Member - User Available Coupons; All fields: Customer ID, Coupon Template ID, Coupon Template Name, Coupon Issuance History Cumulative Number of Coupons Issued",

[0205] "comment_vec": [

[0206] [0.0054153562523424625, 0.002859436674043536, ...],

[0207] [-0.026922982186079025, 0.01595386303961277, ...], ... ]

[0209] },

[0210] "customer_detail": {

[0211] "table_cn":"User Basic Detail Table", ...

[0212] }

[0213] }

[0214] The main process of the online part includes the following steps: extracting the core clauses of natural language questions based on the open source large-scale language model (LLM); matching the core clauses one by one with the vector library stored in the question list vector to obtain the top 5 recall results for each core clause; sorting the multi-way recall results of multiple core clauses according to the sorting algorithm, and selecting the optimal top 5 table positioning results; obtaining the knowledge information of each top 5 table from the knowledge document generated in the offline part, and combining it with the original natural language question to form a prompt, which is delivered to the LLM to generate the final SQL; for tables with corresponding virtual tables in the SQL, using virtual tables to replace them.

[0215] like Figure 2As shown in the figure, the core task of the online part is to extract core clauses from the natural language questions input by the user and generate correct SQL query statements. After the user enters a natural language question, a prompt word is generated for extracting the clause, and the large language model (LLM) is used to automatically extract the core clause from the question. The extracted core clause is matched with the vectors in the question list vector library to obtain the table recall results. The recall results of multiple core clauses are sorted and the top 5 relevant tables are selected. Based on the generated schema knowledge document, the system further forms prompt words for inference SQL generation, and generates the final query SQL query through the reasoning capability of the large language model (LLM), and performs SQL post-processing. In addition, if the generated SQL query statement contains a virtual wide table, the system will automatically replace the original table with the virtual wide table to ensure the correctness and efficiency of the query statement.

[0216] The specific steps include:

[0217] Step 6: The specific operations for extracting the user's natural question into multiple core clauses are as follows:

[0218] Natural language questions are preprocessed, such as removing spaces, converting full-width characters to half-width characters, and converting traditional Chinese characters to simplified Chinese characters, to avoid parsing errors caused by formatting differences. LLM is then used to extract the core clauses of the natural language questions. Core clauses are sentence components that contain the main query intent and key entity information, and are crucial for subsequently correctly identifying the target table.

[0219] Design a general prompt template and build prompts for each user question by writing scripts. Call LLM to split the user's natural language questions into one or more core clauses.

[0220] Here is a generic prompt example for reference:

[0221] You are an advanced assistant focused on interpreting user information. You have the ability to accurately understand the intent of questions, determine whether a question contains multiple independent key conditions, and support breaking down complex questions into multiple core clauses with complete expressions.

[0222] ## Target:

[0223] - As part of the parsing process, your task is to parse the customer's question and generate SQL queries based on the database table structure information to obtain the target customer group that meets the question intent.

[0224] - Accurately extract the core information from the question and ensure that each core clause conveys clear query intent.

[0225] - Improve the readability and structure of information to facilitate subsequent processing and SQL generation.

[0226] ## Skill:

[0227] - Have strong semantic understanding and text organization capabilities, and be able to handle complex Chinese grammatical structures.

[0228] - Proficient in Chinese grammar and ensure the semantic coherence and completeness of core clauses.

[0229] ## Workflow:

[0230] Analyze the question to determine whether it contains multiple independent key conditions.

[0231] For single-condition questions, remove redundant text that is not related to the selected customer and retain the core query intent.

[0232] For multi-conditional questions, break them down into multiple core clauses with complete expressions, ensuring that each clause can independently reflect a query condition.

[0233] ## Notes:

[0234] - Core clauses should avoid using pronouns or other words that require context to understand, ensuring semantic clarity.

[0235] - All necessary splitting must be completed in a single process to ensure that it is in place in one go.

[0236] - Output results are presented directly in list format, such as `[core clause 1, core clause 2, ...]`. This format should be maintained even for single conditional questions, such as `[original question]`.

[0237] - Each element in the list does not need to be numbered, keeping it concise and clear.

[0238] ## Example:

[0239] User query: Find VIP members who have made at least two purchases in the past month, with each purchase amount exceeding 500 yuan

[0240] Output: ["VIP members who have purchased at least twice in the past month", "VIP members whose purchase amount exceeds 500 yuan each time"]

[0241] The user's question is as follows: {question}, please output the core clause:

[0242] Step 7: The specific operations for recalling the table for each clause are:

[0243] Get the core clause in step 6 and vectorize it using embedding technology (consistent with the embedding technology in step 4) to form a vectorized representation , ensuring that each core clause can be understood by the machine in a semantically coherent way.

[0244] At the same time, extract the structural information of each table and its corresponding question vector list from the schema knowledge document in step 5 The question vector list is a double-layer list, where each table corresponds to multiple questions, and each question forms a vector. The data format is as follows:

[0245]

[0246] in, Represents the j-th normal vector of the i-th table.

[0247] For each core clause vector , traverse each table in the schema knowledge document in turn, and perform similarity matching with each question vector in the table's question vector list. This invention uses cosine similarity as the score calculation standard, and the calculation formula is as follows:

[0248]

[0249] in,

[0250] Represents the dot product of two vectors;

[0251] and are the Euclidean norms of the vectors respectively.

[0252] For each table, the score with the highest similarity to the core clause vector in its question vector list is selected as the matching score of the table, that is:

[0253]

[0254] Finally, sort the matching scores of all tables from highest to lowest, select the top 5, and only take the items that exceed the score threshold from the top 5 to form the final matching result list. Step 8: Merge the multi-way recall results of all core clauses and re-rank them to obtain the final top 5 results. The specific operations are as follows:

[0255] For each result obtained in the previous step, a sorting algorithm is used to obtain the final top 5 results. The multi-way recall result refers to the recall result of the core clause.

[0256] The algorithm goal is to select up to N (N is set to 5 in this invention) the most relevant table matching results from the multi-way recall results. The implementation steps are:

[0257] Initialization and preprocessing:

[0258] For each recall result, first sort it in descending order according to the matching score to form a sorted recall list ;

[0259] Initialize an empty list Used to store the highest scoring item in each recall.

[0260] Get the top 1 result for each path:

[0261] Traverse the recall list of each sort number , for each ;

[0262] If the table name of the current item is in If it does not exist, add it to middle;

[0263] If there is a duplicate, skip that item and continue checking the next highest scoring item until uniqueness is found or the traversal ends.

[0264] make sure Include representative items from each recall, and retain at least one top item even if there are duplicates.

[0265] Remove duplicates

[0266] Using Collections Record the selected table name and its corresponding items;

[0267] for For each item in Otherwise, only the item with the highest score is retained and other duplicates are marked as replaceable.

[0268] Resolving duplicate issues

[0269] For each repeated table name, keep the one with the highest score ;

[0270] Check whether there are any unselected items with higher scores in the same path. If so, replace them to ensure that each path has representative items selected.

[0271] Merge remaining results

[0272] if If the number of items has reached N, then the first N results are returned directly, otherwise, all remaining unselected items are merged. , sorted again by score;

[0273] from Select the additional number required to ensure that the final number of results does not exceed N.

[0274] The end result is Add from The total number of additional items selected from does not exceed N.

[0275] Step 9: The specific operations for generating SQL statements based on the table recall results and the natural language question are as follows:

[0276] After obtaining the table recall results from step 8, extract the structural information of each table in the result table of step 8 from the schema knowledge document in step 5, including: table name, column name, Chinese definition of the column and its data type, and description of the data content stored in the table.

[0277] Design and generate SQL prompt templates, and construct prompts for each natural question through programming scripts. Specifically, the user's natural language question, the searched table structure description, and the independent condition description are organized into prompts.

[0278] Call LLM. LLM uses its powerful natural language understanding capabilities to automatically select one or more tables related to the question from the multiple tables obtained in step 8 and construct a SQL query statement that best matches the question intent.

[0279] Here is an example of a generic prompt template for reference:

[0280] You are a data analyst and SQL expert. Your task is to generate SQL queries based on user questions from multiple tables. The requirements are as follows: 1. Only SQL queries must be returned. If the user's intent is to add, delete, or modify data, you must provide feedback that you only have query capabilities and cannot perform data editing operations. 2. All table and field names in the SQL query must be in English. 3. The SQL results must be directly displayed, without outputting the analysis process. The output must begin and end with #. The following is an example: / ***The table name and table information are as follows: ```Table name: students[student table]Fields: id[, int], name[name, text], age[age, int], location[location, list]Table name: course[course schedule]Fields: id[, int], name[course name, text], teacher[teacher, text]Table name: FundTable[Fund details table, including fund name, investment manager, fund establishment time and other basic information of each fund]Fields: col1[fund code, id], col2[fund name, list], col3[establishment time, date], col4[fund type, cate], col5[fund size, num], col6[sales status, cate], col7[whether it can be sold, cate], col8[risk level, cate], col9[fund company name, list], col10[dividend method, cate], col11[redemption status, cate], col12[whether it supports fixed investment, cate], col13[Net Value Synchronization Date, date], col14[Net Value, num], col15[Rise and Fall since Establishment, num], col16[Rise and Fall yesterday, num], col17[Rise and Fall in the past week, num], col18[Rise and Fall in the past month, num], col19[Rise and Fall in the past three months, num], col20[Rise and Fall in the past six months, num], col21[Rise and Fall in the past year, num], col22[Fund Manager, list], col23[Theme / Concept, list], col24[One-month Sharpe Ratio, num], col25[One-year Sharpe Ratio, num], col26[Three-month Sharpe Ratio, num], col27[Six-month Sharpe Ratio, num], col28[Sharp Ratio since Establishment, num], col29[Investment Market, cate], col30[Sector, list], col31[Industry, list], col32[Morningstar three-year rating, cate], col33[Management fee rate, text],col34[Sales service fee rate, text], col35[Custody fee rate, text], col36[Subscription fee rate, text], col37[Subscription fee rate, text], col38[Redemption fee rate, text], col39[Dividend year, list], col40[Equity registration date, list], col41[Ex-dividend date, list], col42[Dividend payment date, list], col43[Dividend reinvestment date, list], col44[Dividend per ten units of income, list], col45[Main investment asset type, cate], col46[Fund investment style description, cate], col47[Valuation, cate], col48[Is it an actively managed fund, cate], col49[Investment, list], col50[Tracking index, list], col51[Is it a newly issued fund, cate], col52[Heavy holdings, list]```User question: Diagnose the fund holding Jiugui Liquor Answer: #select col2 from FundTable wherecol52 like'%Jiuguijiu%'#User question: Count the number of students whose name is older than 23, whose name contains andy and who live in beijing. Answer: #select count(distinct id) from students where age>23 and namelike'%andy% and location ='beijing'#*** / ,

[0281] The following are the table names and table information: ```{db_info}```User question: {question}

[0282] answer:

[0283] Step 10: The specific operations for post-processing of generated SQL are as follows:

[0284] For the SQL query generated in the previous step, first parse its FROM clause to extract all table names involved. This step is implemented using standard SQL parsing techniques to ensure that all table references in the SQL are accurately identified.

[0285] Using the schema knowledge document in step 5, check each extracted table name to determine whether there is a corresponding virtual subquery.

[0286] If a virtual subquery matching the SQL table name exists in the schema knowledge document, a replacement operation is performed; if not, the original SQL remains unchanged.

[0287] Finally, the protection scope of the present invention is not limited to the above specific embodiments, but also includes the following equivalent changes or substitutions thereto without departing from the principles of the present invention:

[0288] The method described in this invention is not limited to the use of open-source large-scale language models (LLMs). In practical applications, fine-tuning the LLM using high-quality NL2SQL datasets tailored to enterprise-specific scenarios can significantly improve its reasoning performance and accuracy for complex questions. Therefore, fine-tuning the LLM using different datasets and environments also falls within the scope of this invention.

[0289] Generating a list of questions for each database table is not limited to using LLM. In practical applications, other methods can also be used to construct a list of questions for a table. For example, a list of questions related to the table structure can be generated using real business examples or annotation by data experts or business experts. Whether it is through expert annotation or automatic generation, as long as similar functions are achieved, it should be considered part of the scope of protection of this invention.

[0290] The Prompt examples described in this invention are only preferred implementations and are not limited to specific forms. Without departing from the principles of the present invention, the Prompt can be adapted, adjusted, or polished according to different application scenarios. These improvements or adjustments fall within the scope of protection of this invention and should be regarded as equivalent modifications to the technical solution of this invention.

[0291] Although the present invention has been disclosed above in terms of preferred embodiments, it is not intended to limit the present invention. Any person skilled in the art may make possible changes and modifications to the technical solutions of the present invention by using the methods and technical contents disclosed above without departing from the spirit and scope of the present invention. Therefore, any simple modifications, equivalent changes and modifications made to the above embodiments based on the technical essence of the present invention without departing from the content of the technical solutions of the present invention shall fall within the scope of protection of the technical solutions of the present invention.

Claims

1. A method for generating SQL queries for complex language questions based on LLM, characterized in that: The following steps are involved: Respond to target language question requests; According to the received target language question request, the requested target language question is split into one or more core clauses that fully express the intention and / or independent conditions by using the language model LLM in combination with the prompt script; The split core clauses are converted into semantic vectors through embedding technology, and the highest cosine similarity between each core clause and the question vector in each table in the offline generated schema knowledge document is calculated as the matching score. The matching scores are sorted and filtered according to the matching score size and score threshold, and the recall results of the core clauses are returned; Merging and sorting the recall results of all the core clauses, filtering and generating the recall results of the target language question, wherein the number of tables in the recall results of the target language question does not exceed a predetermined limit; The target language question generates a prompt word Prompt of the target language question through the Prompt script, and calls the language model LLM to automatically generate an SQL query statement according to the recall result of the target language question and the table structure information in the schema knowledge document. This also includes offline pre-processing of the database table to generate the corresponding schema knowledge document: Query and export the database table list involved in the scenario through SQL scripts to obtain the table structure information of each table, including table name, field name, data type, field type, primary and foreign key information, and field description; Performing a table description and / or field coding description based on the derived table structure information, wherein the database table list also includes table structure information of a virtual wide table, the table structure information of the virtual wide table is generated by constructing a corresponding sub-query statement according to business requirements, designing a coding mapping table for the enumeration value field in the field coding description, the coding mapping table being used to correspond the coding value of the enumeration value field with the actual semantics, and constructing the sub-query statement together with the table structure information of the original data table in the database table list; Generating a question list for each table in the database table list based on the language model LLM, and performing semantic vectorization on the question list to generate a corresponding question vector table for matching with the semantic vector of the core clause of the target language question sentence; The table structure information, the question list and the question vector table are organized into the schema knowledge document for online response to the query of the target language question request.

2. The method for generating SQL queries for complex language questions based on LLM according to claim 1, characterized in that: A table description and / or field coding description is performed based on the exported table structure information, wherein the database table list also includes table structure information of a virtual wide table, and the table structure information of the virtual wide table is generated by constructing a corresponding subquery statement according to business requirements, including: Based on the table structure information of the original data table and the encoding mapping table in the database table list, the subquery statement is constructed. The subquery statement connects the original data table and the encoding mapping table through a LEFT JOIN operator to generate the table structure information of the virtual wide table containing the information of the original data table and the encoding mapping table, which is used to generate the SQL query statement according to the target language question to replace the original data table during actual query.

3. The method for generating SQL queries for complex language questions based on LLM according to claim 2, characterized in that: Generating a question list for each table in the database table list based on the language model LLM, and semantically vectorizing the question list to generate a corresponding question vector table for matching with the semantic vector of the core clause of the target language question, including: Building the Prompt script based on the original data table and the virtual wide table and calling the language model LLM to generate the corresponding question list; A pre-trained bidirectional encoder representation model BERT is used to perform semantic vectorization on the question list associated with the original data table and the virtual wide table to generate the corresponding question vector table.

4. The method for generating SQL queries for complex language questions based on LLM according to claim 3, characterized in that: According to the received target language question request, the target language question sentence is split into one or more core clauses that fully express the intent and / or independent conditions through the language model LLM combined with the prompt script, including: performing text preprocessing on the target language question in the target language question request, including removing redundant spaces, unifying character formats, removing stop words, and standardizing word forms; The target language question sentence is split by combining the language model LLM with the Prompt script, and the result is output in a list format. The output result is one or more core clauses that fully express the intention and / or the independent conditions.

5. The method for generating SQL queries for complex language questions based on LLM according to claim 4, characterized in that: The split core clauses are converted into semantic vectors through embedding technology, and the highest cosine similarity between each core clause and the question vector in each table in the offline generated schema knowledge document is calculated as the matching score, including: Using the pre-trained bidirectional encoder representation model BERT, each core clause in the output result is converted into a semantic vector of the core clause ; Traverse the schema knowledge document and extract the table structure information of each table and the corresponding question vector table , wherein the normal vector table of each table is obtained , the normal vector table The data format is: ;in, represents the j-th normal vector in the i-th table; The semantic vector of each core clause The question vector table of each table in the schema knowledge document Each of the question vectors in is calculated as the matching similarity by calculating the cosine similarity, and the matching similarity is, ； Based on the matching similarity between the question vector table in each table and the semantic vector of the core clause, the first matching similarity, i.e., the highest cosine similarity, is used as the matching score of the question vector table; the matching score is, ； The matching scores of the original data table and the virtual wide table are sorted from largest to smallest, and a limited number of the question vector tables are screened out according to the preset score threshold as the recall results of the core clause, and the matching scores of the question vector tables do not exceed the preset score threshold.

6. The method for generating SQL queries for complex language questions based on LLM according to claim 5, characterized in that: Merging and sorting the recall results of all the core clauses, filtering and generating the recall results of the target language question, and the number of tables in the recall results of the target language question does not exceed a predetermined limit, including: Initializing an empty list for storing a recall result list of each core clause, wherein the recall result list of each core clause includes the table names arranged in descending order according to the matching scores; Traversing the recall result list of each core clause, filtering the table name with the highest matching score to remove duplicates, and merging to generate a recall result list of the target language question, wherein the number of labels in the recall result list of the target language question does not exceed a preset upper limit.

7. The method for generating SQL queries for complex language questions based on LLM according to claim 6, characterized in that: The target language question generates a prompt word Prompt of the target language question through the Prompt script, and calls the language model LLM to automatically generate an SQL query statement according to the recall result of the target language question and the table structure information in the schema knowledge document, including: Based on the target language question, the recall result of the target language question, and the table structure information in the schema knowledge document, the Prompt script is constructed to call the language model LLM to generate the corresponding prompt word Prompt, and the corresponding SQL query statement is generated in combination with the intention and / or the independent condition of the target language question.

8. A system for generating SQL queries for complex language questions based on LLM, using the method for generating SQL queries for complex language questions based on LLM according to any one of claims 1 to 7, characterized in that: include: A response module is configured to respond to a target language question request and, based on the received target language question request, split the requested target language question into one or more core clauses that fully express the intent and / or independent conditions using the language model (LLM) in combination with a prompt script; A data processing module is configured to convert the split core clauses into semantic vectors using an embedding technique, calculate the highest cosine similarity between each core clause and the question vector in each table in the offline-generated schema knowledge document as a matching score, sort and filter the core clauses according to the matching score and a score threshold, and return the recall results of the core clauses; merge and sort the recall results of all the core clauses, filter and generate the recall results of the target language question, and ensure that the number of tables in the recall results of the target language question does not exceed a predetermined limit; A result output module is configured to generate a prompt word Prompt for the target language question using the Prompt script, and to call the language model LLM to automatically generate an SQL query statement according to the recall result of the target language question and the table structure information in the schema knowledge document; It also includes an offline processing module for querying and exporting a list of database tables involved in the scenario through SQL scripts to obtain the table structure information of each table, wherein the table structure information includes table name, field name, data type, field type, primary and foreign key information, and field description; performing table description and / or field coding description based on the exported table structure information, wherein the database table list also includes table structure information of a virtual wide table, and the table structure information of the virtual wide table is generated by constructing a corresponding sub-query statement according to business needs; designing a coding mapping table for the enumeration value field in the field coding description, and the coding mapping table The mapping table is used to correspond the encoded value of the enumeration value field with the actual semantics, and to construct the sub-query statement with the table structure information of the original data table in the database table list; based on the language model LLM, a question list for each table in the database table list is generated, and the question list is semantically vectorized to generate a corresponding question vector table for matching with the semantic vector of the core clause of the target language question; the table structure information, the question list and the question vector table are organized into the schema knowledge document for online response to the query requested by the target language question.

Images