SQL query generation method and system for complex language question based on LLM

Abstract

The invention relates to the technical field of natural language processing, and provides an LLM-based complex language question SQL query generation method and system, and the method comprises the steps: responding to a target language question request; splitting a requested target language question sentence into one or more core clauses through a language model LLM, converting the core clauses into semantic vectors, calculating a matching score of each core clause and a question normal vector of each table in the schema knowledge document, performing sorting and screening according to the score size and a score threshold, and returning a recall result of the core clauses; combining and sorting the recall results of all the core clauses, and screening to generate a recall result of the target language question; and generating a Prompt through a Prompt script, and calling an LLM (Language Model) to automatically generate an SQL (Structured Query Language) query statement. The method does not depend on field fine tuning, does not need to reconstruct a wide table, and effectively solves the problem that a target table is difficult to position when a large language model processes coded value mapping, multiple tables and complex business logic by constructing a virtual table and splitting a question sentence into clauses containing complete semantics and matching the clauses.

Description

Technical Field

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

[0002] With the rapid development of artificial intelligence technology, especially the rise of large language models (LLM), the technology of converting natural language into structured query language (NL2SQL) has made significant progress and has been widely used in various application fields, mainly to enable non-technical personnel to interact with databases through daily language, simplifying the process of data query and analysis.

[0003] In the existing technology, the core of the LLM-based NL2SQL technology is to convert the user's natural language questions into SQL query statements that can be understood by computers. The main approach is to rely on fixed database schema indexes and data enhancement technology to assist the model in logical reasoning, and to generate SQL queries by combining natural language questions with database DDL information by forming prompts. The implementation principle of this method seems simple and performs well in the scenario of processing single-table queries. However, in the actual enterprise environment, when faced with the diversity of database structures and the complexity of query requirements, it often fails to achieve the expected accuracy and flexibility. The problems are as follows: ① Complexity of multi-condition cross-table query. In business data analysis, queries usually involve multiple conditions and need to retrieve information from multiple related data tables. However, this not only increases the complexity of the query, but also requires the database query system to have the ability to efficiently coordinate and process cross-table data.

[0004] ② 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 must be associated with a special coding table to be correctly interpreted and converted.

[0005] ③ Complex business logic. The complex or customized business logic commonly seen in enterprise applications requires the database query system to be highly flexible and adaptable. For example, the balance table only records users whose balances are greater than zero. If you need to query users with zero balances, you need to perform more complex operations, that is, exclude those users with balances greater than zero from all users.

[0006] Therefore, in real enterprise application scenarios, in order to ensure that natural language questions can accurately recall the corresponding database tables and then generate correct SQL queries, how to improve the accuracy of this process has become a key challenge. Especially in the face of 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

[0007] In view of the above problems, the present invention proposes a complex language question parsing and SQL query method and system based on LLM, which does not rely on domain fine-tuning and does not need to reconstruct wide tables. Instead, it constructs virtual tables and splits complex questions into clauses containing complete semantics and matches them, effectively solving the problem that large language models are difficult to locate target tables when processing code value mapping, multiple tables and complex business logic, and the generated SQL does not meet the table storage logic, thereby improving the accuracy of NL2SQL. The above invention objectives of the present invention are achieved through the following technical solutions: The present invention provides a method for generating SQL queries for complex language questions based on LLM, comprising the following steps: 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 through the language model LLM combined 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 schema knowledge document generated offline 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 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 value; 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 according to the recall results of the target language question and the table structure information in the schema knowledge document.

[0008] Furthermore, it also includes offline preprocessing of the database table to generate corresponding schema knowledge documents, including: Use SQL scripts to query the list of database tables involved in the export 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; Perform table description and / or field coding description according to 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; 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 sentence; The table structure information, question list and question vector table are organized into a schema knowledge document for online response to the query of the target language question request.

[0009] Further, a table description and / or field coding description is performed according to 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: 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. 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, which is used to generate an SQL query statement according to the target language question to replace the original data table in the actual query.

[0010] Furthermore, based on the language model LLM, a question list for each table is generated for the database table list, 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: 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; 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.

[0011] Furthermore, according to 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 intention and / or independent conditions, including: Performing 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; The target language question 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 independent conditions.

[0012] Furthermore, 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 schema knowledge document generated offline is calculated as the matching score, including: The pre-trained bidirectional encoder representation model BERT is used to convert each core clause in the output result into a semantic vector of the core clause. ; 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 of each table is obtained , ask the normal vector table The data format is: ;in, represents the jth normal vector of the i-th table; 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. The matching similarity is, ; 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, ; 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 a preset score threshold as the recall result of the core clause, and the matching score of the question vector table does not exceed the preset score threshold.

[0013] Further, the recall results of all core clauses are merged and sorted, and the recall results of the target language question are screened and generated, and the number of tables in the recall results of the target language question does not exceed a predetermined limit value, including: 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. Traverse the recall result list of each core clause, filter the table name with the highest matching score to remove duplicates, and merge 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.

[0014] Furthermore, 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 SQL query statements 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, 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 in combination with the intention and / or independent conditions of the target language question.

[0015] Based on the same inventive concept, the present invention also provides a SQL query generation system for complex language questions based on LLM, which adopts the above-mentioned SQL query generation method for complex language questions based on LLM, including: A response module is used to respond to the target language question request and split the requested target language question into one or more core clauses that fully express the intention and / or independent conditions through the language model LLM combined with the prompt script according to the received target language question request; The data processing module is used to convert the split core clauses into semantic vectors through embedding technology, and calculate the highest cosine similarity between each core clause and the question vector in each table in the schema knowledge document generated offline as the matching score, and 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 the number of tables in the recall results of the target language question sentences does not exceed the predetermined limit value; The result output module is used to generate 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 according to the recall results of the target language question and the table structure information in the schema knowledge document.

[0016] 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, 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 according to 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.

[0017] Compared with the prior art, the present invention has at least one of the following beneficial effects: The present invention provides a method and system for generating SQL queries for complex language questions based on LLM. It does not require domain fine-tuning or reconstruction of 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 difficulty in locating target tables and failure of generated SQL to meet table storage logic when large language models process code value mapping, multiple tables and complex business logic, and ultimately improve the accuracy of NL2SQL.

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

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

[0020] Figure 1 A flowchart of the steps of the SQL query generation method for complex language questions based on LLM of the present invention; 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

[0021] In order to make the purpose, technical solution and advantages of the embodiments of the present application clearer, the technical solution in the embodiments of the present application will be clearly and completely described below in conjunction with the drawings in the embodiments of the present application. Obviously, the described embodiments are part of the embodiments of the present application, not all of the embodiments. Based on the embodiments in the present application, all other embodiments obtained by ordinary technicians in this field without creative work are within the scope of protection of this application.

[0022] 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 features, integers, steps, operations, elements and / or components, but does not exclude the presence or addition of one or more other features, integers, steps, operations, elements, components and / or groups thereof.

[0023] First embodiment The core of the NL2SQL technology, which converts natural language queries to SQL based on large language models (LLMs), is to convert users' natural language queries into SQL statements that computers can understand and execute. Although this process seems relatively simple on the surface, the main challenge lies in how to accurately parse the query intent in natural language and effectively convert it into efficient and correct SQL queries. Currently, the existing LLM-based NL2SQL methods mainly rely on data augmentation RAG technology, which improves the model's reasoning ability by inputting query-related domain knowledge as external information into the large model. This process usually includes the following steps: (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.

[0024] (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.

[0025] (3) Assemble prompts and generate SQL: Combine natural language questions with the retrieved 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.

[0026] Although this technology has shown high accuracy when processing single-table queries, in actual enterprise operation data analysis scenarios, users' natural language queries are usually 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, which combines in-depth analysis of the database table list, field coding description, and virtual wide table structure with advanced large language model LLM for reasoning and optimization, thereby achieving more accurate and efficient SQL generation. The specific implementation method is as follows: 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: 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 through the language model LLM combined 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 schema knowledge document generated offline 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; Merge and sort the recall results of all core clauses, filter and generate the recall results of the target language question, and the number of tables in the recall results does not exceed the predetermined limit value; 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 according to the recall results of the target language question and the table structure information in the schema knowledge document.

[0027] It should be specifically noted that for complex questions with multiple conditions, the original natural language questions often contain multiple conditions and involve multiple database tables, which makes it very difficult to directly recall tables, 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.

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

[0029] When recalling tables for each clause, the final target table list can be obtained by merging the table recall results corresponding to each clause. This target table list can not only accurately include all tables related to the query, but also effectively filter out irrelevant tables to improve query efficiency. In this way, unnecessary calculations and query complexity caused by recalling a large number of irrelevant tables at one time can be avoided.

[0030] As enterprise data continues to develop and business scenarios change, the question list for each table can be continuously expanded and optimized. By adding more question examples, the system can adapt to different query requirements and scenarios, so that the table location capability is continuously improved. Especially in the enterprise production environment, this iterative optimization capability is very important, because the database structure and business requirements often change in actual applications. Therefore, the question list can be dynamically updated to ensure that the table recall process is always accurate and effective. By continuously enriching the question list for each table, the ability boundary of table location can be gradually expanded. As time goes by 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 enables the system to not only have the ability to handle current business, but also to cope with future changes in business needs and database architecture adjustments, improving the long-term availability and stability of the system.

[0031] Furthermore, it also includes preprocessing the database table offline to generate corresponding schema knowledge documents; Preprocess the database table offline to generate the corresponding schema knowledge document including: Use SQL scripts to query the list of database tables involved in the export 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; Perform table description and / or field coding description according to 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; 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 sentence; 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.

[0032] Further, a table description and / or field coding description is performed according to 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: 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. 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, which is used to replace the original data table when actually querying the SQL query statement generated according to the target language question.

[0033] It should be specifically noted that, when processing complex business logic and coded value mapping, the original table in the database may contain a large number of coded values, and these coded values ​​often need to be correctly interpreted and applied through additional mapping relationships. In this case, it is more complicated to directly query and operate 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 coded value mapping are added at the same time, so that the execution of the query becomes more concise and efficient.

[0034] A virtual table is not just a static data view, its structure is dynamically maintained. Whenever the structure of the enterprise's database table changes, the structure of the virtual table will be adjusted accordingly to maintain its accuracy and timeliness. In this way, the virtual table can reflect business needs and database changes in real time without affecting the original table structure or production environment. In this way, the enterprise does not need to make any modifications to the original database table in daily operations, thus ensuring the stability of the system and the continuity of the production environment.

[0035] The construction of virtual tables enables the large language model (LLM) to generate SQL queries more efficiently without adjusting its original reasoning capabilities. Through the structured business information and encoding mapping provided by the virtual table, the LLM can better understand the complex logic and conditions in natural language questions, and then generate more accurate and efficient SQL query statements. This approach avoids the need for fine-tuning the large model, which not only ensures the stability of the model, but also significantly improves its reasoning capabilities in complex business scenarios.

[0036] By constructing a virtual table and using it in conjunction with a large model, enterprises do not need to make any changes in the real production environment, especially in terms of database table structure. This not only ensures the integrity and stability of the existing database, but also avoids the technical risks caused by changes in the database architecture. In this non-invasive mode, enterprises can flexibly respond to complex business needs while improving the accuracy and efficiency of large language models when generating SQL queries.

[0037] Furthermore, based on the language model LLM, a question list for each table is generated for the database table list, 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: Build a prompt script based on the original data table and virtual wide table to call the language model LLM and generate the corresponding question list; 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.

[0038] Furthermore, according to 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 intention and / or independent conditions, including: Performing 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; The target language question 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 independent conditions.

[0039] Furthermore, 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 schema knowledge document generated offline is calculated as the matching score, including: The pre-trained bidirectional encoder representation model BERT is used to convert each core clause in the output result into a semantic vector of the core clause. ; 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 of each table is obtained , ask the normal vector table The data format is: ;in, represents the jth normal vector of the i-th table; 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. The matching similarity is, ; 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, ; 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 a preset score threshold as the recall result of the core clause, and the matching score of the question vector table does not exceed the preset score threshold.

[0040] Furthermore, the recall results of all core clauses are merged and sorted, and the recall results of the target language question sentences are screened and generated, and the number of tables in the recall results does not exceed a predetermined limit value, including: 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. Traverse the recall result list of each core clause, filter the table name with the highest matching score to remove duplicates, and merge 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 a prompt word Prompt of the target language question through the Prompt script, and calls the language model LLM to automatically generate SQL query statements 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, a Prompt script is constructed to call the language model LLM to generate a corresponding prompt word Prompt and generate a corresponding SQL query statement in combination with the intention and / or the independent condition of the target language question.

[0042] Second embodiment Based on the same inventive concept, the present invention provides a SQL query generation system for complex language questions based on LLM, which adopts the above-mentioned SQL query generation method for complex language questions based on LLM, including: A response module is used to respond to the target language question request and split the requested target language question into one or more core clauses that fully express the intention and / or independent conditions through the language model LLM combined with the prompt script according to the received target language question request; The data processing module is used to convert the split core clauses into semantic vectors through embedding technology, and calculate the highest cosine similarity between each core clause and the question vector in each table in the schema knowledge document generated offline as the matching score, and 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 the number of tables in the recall results does not exceed the predetermined limit value; The result output module is used to generate 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 according to the recall results of the target language question and the table structure information in the schema knowledge document.

[0043] 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, 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 according to 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; 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.

[0044] 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 part, so it will not be repeated here.

[0045] Third embodiment 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 reference to its 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 in terms of improving the accuracy of the natural language to SQL conversion (NL2SQL) technology, processing complex query capabilities, and virtual wide table support through a specific operation process.

[0046] like Figure 2As shown, in this embodiment, the SQL query generation method for complex language questions based on LLM includes online and offline parts. The online part is mainly responsible for receiving complex natural language questions input by users in real time, combining the database table structure information in the schema knowledge document, and calling LLM to generate corresponding SQL query statements. The offline part includes tasks such as data preprocessing, schema knowledge document organization, virtual wide table generation and model training, providing necessary support and optimization for the online part, ensuring the efficiency and accuracy of the system when facing multi-table association and complex conditional queries.

[0047] 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 meaning of the table, column name, column type, Chinese meaning of the column, and primary and foreign key information. (2) Based on the prior knowledge of data experts, pre-build 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 a 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.

[0048] like Figure 2 As shown in the figure, the system generates and obtains the table structure information of the database, and pre-constructs sub-query statements for the coded values ​​and complex business queries in each table to form a virtual wide table based on business needs and the prior knowledge of experts. The original table structure description is updated to support flexible queries, and the large language model LLM is called to generate sample questions and prompt words for the sample question list for each table to guide users on how to ask query questions. The generated question list will be vectorized and stored as the basis for subsequent queries. Finally, the modified table structure information and the vectorized question list together form the schema knowledge document to provide support for the SQL generation of the online part.

[0049] Specifically, the main tasks of the offline part are to preprocess the database table structure, generate a list of relevant sample questions, and build a knowledge document that supports SQL generation. The steps are as follows: Step S1: Get the database table results involved in the scenario: 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 the MySQL / Tidb database, use the following SQL script: SELECT table_schema, table_name, COLUMN_NAME, DATA_TYPE, COLUMN_TYPE, COLUMN_KEY, COLUMN_COMMENT FROM information_schema.columns WHERE table_name IN ( / *table list* / ) 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; The table description includes the Chinese meaning of the table and the description of the data content stored in the table. For example, for the user table, the Chinese meaning of the table is: user detailed data; the data content description is: contains basic user information related to registration, such as user age, gender, registration date, zodiac sign, registration app type, province and city, etc., but does not include tags.

[0050] Some field coding descriptions are supplemented for fields with few enumeration values ​​or commonly used enumeration values. For example, for gender, the data stored in the table is 0, 1, 2, 3, 4, and the field coding description needs to indicate 0: unknown, 1: male, 2: female, 3: suspected male, 4: suspected female; the order type storage data is: 1, 2, 3, 4, 5, ..., and the corresponding coding values ​​are 1: dine-in, 2: packaged, 3: take-out, 4: pay, 5: store value, ....

[0051] Step S2: The specific operations of constructing a subquery statement are: In the process of data management, we design a code mapping table to deal with the situation where there are too many code values, and construct subquery statements for some complex SQL logic.

[0052] There is a code mapping table, such as the customer_coupon_usable table, whose table structure is as follows: CREATE TABLE `customer_coupon_usable` ( `customer_id` bigint(20) NOT NULL COMMENT"Customer ID", `template_id` bigint(20) NOT NULL COMMENT "voucher template id", `usable_coupon_cnt` decimal(15, 2) NULL COMMENT "Coupon issuance history cumulative number of coupons issued in days") COMMENT"Member-User Coupon Verification Template Summary Table" 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.

[0053] 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: CREATE TABLE `customer_coupon_usable` ( `customer_id` bigint(20) NOT NULL COMMENT"Customer ID", `template_id` bigint(20) NOT NULL COMMENT "voucher template id", `template_name` varchar(120) NOT NULL COMMENT "Ticket template name", `usable_coupon_cnt` decimal(15, 2) NULL COMMENT "Coupon issuance history cumulative number of coupons issued in days") COMMENT"Member-User Coupon Verification Template Summary Table" The code for adding a subquery statement to the user's available coupon table is: SELECT a.*, b.template_name FROM customer_coupon_usable a LEFT JOIN dim_template b ON a.template_id = b.template_id When the coupon template name appears in the subsequent natural language question, it can directly match the user's available coupon table and convert the generated SQL SELECT ... FROM customer_coupon_usable ... Partially replaced by SELECT ... FROM (SELECT a.*, b.template_name FROM customer_coupon_usable a LEFT JOIN dim_template b ON a.template_id = b.template_id) ... For complex business query logic, such as the balance table (customer_account), since it only stores all users with a balance greater than 0, when a natural language question is asked about users with a balance of 0, the table can often be located, but the SQL logic that satisfies the storage situation cannot be generated. Therefore, the present invention also uses the method of constructing subqueries to support queries when the balance table is 0.

[0054] The balance table (customer_account) has the following structure: CREATE TABLE `customer_account` ( `customer_id` bigint(20) NULL COMMENT "User", `usable_account` decimal(12, 2) NULL COMMENT "Current available balance") COMMENT"User balance details" When locating the balance table, the SQL SELECT ... FROM customer_account ... Partially replaced by SELECT ... FROM (SELECT a.customer_id, COALESCE(b.usable_account, 0) AS usable_account FROM customer_detail a LEFT JOIN customer_account b ON a.customer_id = b.customer_id) ... Analogously, the balance sheet is 0, and there are a series of questions such as "not consumed" and "coupon not received".

[0055] Step 3: The specific operations for constructing a question list based on LLM are: A question list is constructed for each table, that is, which questions can be supported by this table to obtain corresponding data. The present invention adopts a method based on table structure combined with LLM to obtain a large number of question lists. The method writes a general script, constructs Prompt in the script, calls LLM, and generates a question list for different tables by only changing the table creation statement in Prompt. Preferably, some question examples are provided by data experts.

[0056] The following is an example prompt: 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 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: Take-out, 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 take-out, 4: Ele.me take-out", `consume_type` int(11) NOT NULL COMMENT"Consumption type, 1: Commodity order, 2: Top-up order, 3: Live broadcast order, 4: Pay-as-you-go", `total_cnt` int(11) NULL COMMENT"Total number of consumption orders", `total_amt` decimal(15, 2) NULL COMMENT"Total consumption amount", `lastest_consume_time` date NULL COMMENT"Latest consumption date, format: YYYY-MM-DD")COMMENT"User consumption channel type summary table, according to order type, order source, consumption type statistics user cumulative consumption order number, cumulative consumption amount and the latest consumption date, excluding stores"Here is a question example, such as: / * Help me circle the users with a cumulative consumption amount between 30 yuan and 60 yuan; * / / * Users with more than 2 historical cumulative consumption times; * / / * Users who made their first consumption this month* / Requirements: 1. The format and grammar of the question should be as rich as possible, aiming to obtain user customer data from different dimensions; 2. The question simulates the natural language question input by the user. 3. Just output the question list directly, without the need to output the introduction, the format is List; After the question list is generated, it can be handed over to business experts for manual screening to retain questions that meet actual query needs or common scenarios, or to make certain modifications to questions that do not conform to user language habits to form a final question list.

[0057] Step 4: The specific operations of question list vectorization are: By using embedding technology, specifically using 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. In this way, the present invention can construct and store a detailed table-question vector correspondence list, providing a basis for subsequent semantic matching and table library positioning.

[0058] Step 5: The specific operations for organizing the knowledge formed in the above steps 1-4 into a knowledge document are as follows: The scenario-related database table structure information, including but not limited to the table name and its Chinese interpretation, column name and column type, column semantic interpretation, primary and foreign key associations and other metadata, together with the natural language question examples generated based on each table and their corresponding vectorized representations, are systematically organized and constructed into a detailed schema knowledge document. This knowledge document is intended to provide the necessary background information and reference basis for converting natural language queries raised by users into accurate SQL query statements in the subsequent stages. By integrating these structured and unstructured data assets, the semantic consistency and accuracy of the process from natural language processing to SQL query generation is ensured, thereby enhancing the reliability and efficiency of query conversion.

[0059] The following is an example of a schema knowledge document: { "customer_coupon_usable": { "table_cn":"Member-User Available Coupons", "comment":"Count the cumulative number of coupons issued by users on each coupon template", "templates": [ "Please circle users who have not received more than one coupon", "Users who have only received one coupon", "Users with no available vouchers", "Users without available coupon assets", "Count the cumulative number of coupons issued by users on each coupon template" ], "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[coupon issuance history cumulative number of coupons issued per day, decimal]", "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 in Days", "comment_vec": [ [0.0054153562523424625, 0.002859436674043536, ...], [-0.026922982186079025, 0.01595386303961277, ...], ... ] }, "customer_detail": { "table_cn":"User Basic Detail Table", ... } } The main process of the online part includes the following steps: extract the core clauses of natural language questions based on the open source large language model LLM; match the core clauses one by one in the vector library stored in the question list vector to obtain the recall Top5 results of each core clause; sort the multi-way recall results of multiple core clauses according to the sorting algorithm, and select the optimal Top5 table positioning results; obtain the knowledge information of the TOP5 tables from the knowledge documents formed in the offline part, and combine it with the original natural language question to form a prompt and deliver it to LLM to generate the final SQL; for tables with corresponding virtual tables in SQL, use virtual tables to replace them.

[0060] 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 the natural language question, a prompt word for extracting the clause is generated, and the large language model LLM is used to automatically extract the core clause from the question. The extracted core clause will be matched with the vector in the question list vector library to obtain the table recall result. The top 5 related tables are selected by sorting the recall results of multiple core clauses. Based on the generated schema knowledge document, the system further forms the prompt word for reasoning SQL generation, and generates the final query SQL query through the reasoning ability 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.

[0061] The specific steps include: Step 6: The specific operation of extracting the user's natural question into multiple core clauses is as follows: The 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 format differences. The core clauses of the natural language questions are then extracted based on LLM. The core clauses refer to the sentence components that contain the main query intent and key entity information, which are crucial for the subsequent correct determination of the target table.

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

[0063] Here is a generic prompt example for reference: You are an advanced assistant who focuses on interpreting user information. You have the ability to accurately understand the intent of a question, determine whether a question contains multiple independent key conditions, and support breaking down complex questions into multiple core clauses with complete expressions.

[0064] ## Target: - 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.

[0065] - Accurately extract the core information in the question and ensure that each core clause conveys a clear query intent.

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

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

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

[0069] ## Workflow: Analyze the question to determine whether it contains multiple independent key conditions.

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

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

[0072] ## Notes: - The core clause should avoid using pronouns or other words that require context to be understood to ensure semantic clarity.

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

[0074] - The output is presented directly in a list format, such as `[core clause 1, core clause 2, ...]`, even for single conditional questions, such as `[original question]`.

[0075] - Each element in the list does not need to have a sequence number, keeping it simple and clear.

[0076] ## Example: User question: Find VIP members who have made at least two purchases in the past month and each purchase amount exceeds 500 yuan Output result: ["VIP members who have purchased at least twice in the past month", "VIP members whose purchase amount exceeds 500 yuan each time"] Now the user's question is as follows: {question}, please output the core clause: Step 7: The specific operations for recalling the table for each clause in turn are: 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.

[0077] 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 two-layer list, where each table corresponds to multiple questions, and each question forms a vector. The data format is as follows: in, Represents the j-th normal vector of the i-th table.

[0078] 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 question vector list of the table. The present invention uses cosine similarity as the score calculation standard, and the calculation formula is as follows: in, Represents the dot product of two vectors; and are the Euclidean norms of the vectors.

[0079] For each table, select the score with the highest similarity to the core clause vector in its question vector list as the matching score of the table, that is: Finally, sort the matching scores of all tables from large to small, 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 rearrange them to get the final top 5 results. The specific operation is: For each result obtained in the previous step, the final Top 5 results are obtained through the sorting algorithm. Among them, the multi-way recall result refers to the recall result of the core clause.

[0080] The algorithm goal is to select at most 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 as follows: Initialization and preprocessing: For each recall result, first sort them in descending order according to the matching score to form a sorted recall list ; Initialize an empty list Used to store the highest scoring item in each recall.

[0081] Get the top 1 result for each route: Traverse the recall list of each sort number , for each ; If the table name of the current item is in If it does not exist, add it to middle; If there is a duplicate, skip that item and continue checking the next highest scoring item until uniqueness is found or the traversal ends.

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

[0083] Remove duplicates Using Collections Record the selected table name and its corresponding items; for For each item in Otherwise, only the item with the highest score is retained and other duplicates are marked as replaceable.

[0084] Resolving duplicate issues For each repeated table name, keep the one with the highest score ; Check whether there are unselected items with higher scores in the same path, and replace them if there are, to ensure that each path has representative items selected.

[0085] Merge remaining results if If the number of items has reached N, the first N results are returned directly, otherwise, all remaining unselected items are merged. , sorted again by score; from Select the additional number required to ensure that the final number of results does not exceed N.

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

[0087] Step 9: The specific operation of generating SQL by combining the table recall results and the natural language question is as follows: After obtaining the table recall result 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 meaning of the column and its data type, and description of the data content stored in the table.

[0088] Design and generate SQL prompt templates, and construct prompts for each natural question through programming scripts. Specifically, the natural language question entered by the user, the table structure description information obtained by the search, and the independent condition description are organized into prompt words. Call LLM. LLM uses its powerful natural language understanding ability 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 suits the question intent.

[0089] Here is an example of a generic prompt template for reference: You are a data analyst and SQL expert. Your task is to generate SQL query statements based on user questions from multiple tables provided. The requirements are as follows: 1. Only return SQL query statements. If the intention is to add, delete or modify data, then give feedback to the user. You only have query functions and cannot perform data editing operations; 2. The table names and field names of SQL query statements must all be in English; 3. There is no need to output the analysis process, and the SQL results are given directly. The output SQL must start with # 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 table]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[Rights 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 newly issued, cate], col52[Heavy holding, 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 are in beijing. Answer: #select count(distinct id) from students where age>23 and namelike'%andy% and location ='beijing'#*** / , The following are the table names and table information: ```{db_info}```User question: {question} answer: Step 10: The specific operations for post-processing of generated SQL are as follows: For the SQL query statement generated in the above steps, first parse its FROM clause to extract all the table names involved. This step is implemented through standard SQL parsing technology to ensure that all table references in SQL can be accurately identified.

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

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

[0092] 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 made thereto without departing from the principles of the present invention: The method described in the present invention is not limited to the use of open source large language models (LLMs). In practical applications, if the LLM can be fine-tuned using a high-quality NL2SQL dataset in a specific enterprise scenario, this will greatly improve its reasoning effect and accuracy in complex questions. Therefore, the implementation of the present invention for fine-tuning in different datasets and environments also falls within the scope of protection of the present invention.

[0093] The method of generating a list of questions for each database table is not limited to using LLM. In practical applications, the list of questions for a table can also be constructed in other ways. For example, a list of questions related to the table structure can be generated by using real business examples or by 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 regarded as part of the scope of protection of the present invention.

[0094] The Prompt example described in the present invention is only a preferred implementation mode and is not limited to a specific form. Without departing from the principle of the present invention, the Prompt can be adapted, adjusted or polished according to different application scenarios. These improvements or adjustments are all within the scope of protection of the present invention and should be regarded as equivalent changes to the technical solution of the present invention.

[0095] Although the present invention has been disclosed as above in the form of a preferred embodiment, it is not intended to limit the present invention. Any person skilled in the art may make possible changes and modifications to the technical solution 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 solution of the present invention shall fall within the protection scope of the technical solution 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, split the requested target language question into one or more core clauses that fully express the intention and / or independent conditions through the language model LLM combined 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 schema knowledge document generated offline is calculated as the matching score, and sorting and screening are performed according to the matching score size and score threshold, and the recall result of the core clause is returned; 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 value; 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.

2. The method for generating SQL queries for complex language questions based on LLM according to claim 1, characterized in that: It also includes offline preprocessing of the database table to generate the corresponding schema knowledge document, including: Query the list of database tables involved in the export 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; Performing table description and / or field coding description according to 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; Generate a question list for each table of 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 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.

3. The method for generating SQL queries for complex language questions based on LLM according to claim 2, characterized in that: A table description and / or field coding description is performed according to 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: Designing a coding mapping table for the enumeration value field in the field coding description, wherein the coding mapping table is used to correspond the coding value of the enumeration value field to the actual semantics; Based on the table structure information of the original data table in the database table list and the encoding mapping table, the sub-query statement is constructed. The sub-query 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 original data table and the encoding mapping table information, which is used to generate the SQL query statement according to the target language question to replace the original data table in actual query.

4. The method for generating SQL queries for complex language questions based on LLM according to claim 3, characterized in that: Generating a question list for each table of 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 sentence, including: Construct the Prompt script based on the original data table and the virtual wide table and call the language model LLM to generate the corresponding question list; The pre-trained bidirectional encoder representation model BERT is used to perform semantic vectorization on the original data table and the question list associated with the virtual wide table to generate the corresponding question vector table.

5. The method for generating SQL queries for complex language questions based on LLM according to claim 4, characterized in that: 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 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 is split by combining the language model LLM with the Prompt script, and the result is output in a list format, where the output result is one or more core clauses that fully express the intention and / or the independent conditions.

6. The method for generating SQL queries for complex language questions based on LLM according to claim 5, 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 schema knowledge document generated offline 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 method 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 of the i-th table; The semantic vector of each core clause The method 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 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, ； The matching scores of the original data table and the virtual wide table are sorted from large to small, and a limited number of the question vector tables are screened out according to the preset score threshold as the recall result of the core clause, and the matching score of the question vector table does not exceed the preset score threshold.

7. The method for generating SQL queries for complex language questions based on LLM according to claim 6, 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 value, including: Initialize 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; The recall result list of each core clause is traversed, the table name with the highest matching score is screened for duplication, and the recall result list of the target language question is generated by merging, and the number of labels in the recall result list of the target language question does not exceed a preset upper limit.

8. The method for generating SQL queries for complex language questions based on LLM according to claim 7, 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.

9. A system for generating SQL queries for complex language sentences based on LLM, using any one of the methods for generating SQL queries for complex language sentences based on LLM as claimed in claims 1 to 8, characterized in that: include: A response module, used to respond to the target language question request and split the requested target language question into one or more core clauses that fully express the intention and / or independent conditions through the language model LLM combined with the prompt script according to the received target language question request; A data processing module is used to convert the split core clauses into semantic vectors through embedding technology, and calculate the highest cosine similarity between each core clause and the question vector in each table in the schema knowledge document generated offline as a matching score, and sort and filter according to the matching score size and the score threshold, and return the recall result of the core clause; merge and sort the recall results of all the core clauses, filter and generate the recall result of the target language question, and the number of tables in the recall result of the target language question does not exceed a predetermined limit value; The result output module is used for generating a prompt word Prompt of the target language question through the Prompt script, and calling 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.

10. The SQL query generation system for complex language questions based on LLM according to claim 9, characterized in that: 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, and 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; perform table description and / or field coding description according to 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; 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, the question list and the question vector table into the schema knowledge document for online response to queries requested by the target language question.

Images