An NL2SQL method based on LLM multi-table adaptive selection

Abstract

The present invention relates to the field of natural language processing technology, and in particular to an NL2SQL method based on LLM multi-table adaptive selection. The present invention comprises the following operating steps: extracting representative field information from an original database involved in a query, and constructing a data knowledge base based on the content of the representative field information; extracting entity information and time information from a natural language sentence input into a large model; performing a fuzzy matching query on the entity information and the representative field information, and obtaining library table information corresponding to the entity information in the data knowledge base through the matching query results; inputting the entity information and time information extracted from the natural language sentence together with the library table information into the large model to obtain SQL statements with time information and SQL statements without time information; and correcting the generated SQL statements based on the time information and field information. The present invention realizes multi-table adaptive selection, improves the accuracy of SQL generation, and thus meets the practical requirements of general business applications.

Description

Technical Field

[0001] The present invention relates to the technical field of natural language processing, and in particular to an NL2SQL method based on LLM multi-table adaptive selection. Background Art

[0002] NL2SQL converts natural language statements into SQL statements that the database can understand, allowing users to interact directly with the database. Thanks to the unstructured processing capabilities of deep learning, NL2SQL technology has developed rapidly, and its core question parsing module has achieved promising results. However, its construction process is overly complex and its domain generalization capabilities are weak, leading to the following problems:

[0003] 1. During the NL2SQL process, the large model had a low accuracy rate when generating SQL statements from natural language containing time information. This was mainly reflected in the high error rate in parsing complex time information, which ultimately led to incorrect SQL statements.

[0004] 2. Due to the complexity of the Chinese language, field values ​​extracted during the NL2SQL process cannot be directly used to populate SQL statements. To avoid this, external knowledge graphs based on named entities in natural language statements are often introduced to enhance the NL2SQL model's understanding of natural language statements and improve parsing performance. However, building a knowledge graph is complex, and the domain-specific nature of the knowledge is not practical for different data queries.

[0005] 3. When generating SQL statements using a large model, you need to submit the data table structure information to the large model through a prompt. In real-world business scenarios, multiple tables and multiple fields are very common, requiring very long input bytes. However, the large model has a limited byte length and cannot handle long inputs effectively, severely restricting the effectiveness of generating SQL statements from the large model.

[0006] This shows that the NL2SQL model needs further optimization in the natural language processing process. Summary of the Invention

[0007] To avoid and overcome the technical problems of the prior art, the present invention provides an NL2SQL method based on LLM multi-table adaptive selection. By integrating multiple aspects, including multi-table selection, temporal rule processing, and knowledge enhancement, the present invention achieves multi-table adaptive selection, improving the accuracy of SQL statement generation and thus meeting the practical requirements of general business applications.

[0008] To achieve the above object, the present invention provides the following technical solutions:

[0009] An NL2SQL method based on LLM multi-table adaptive selection includes the following steps:

[0010] S1. Build a data knowledge base: Extract representative field information from the original database involved in the query and build a data knowledge base based on the content of the representative field information;

[0011] S2, semantic analysis and processing: extract entity information and time information from the natural language sentences input into the large model, and convert the time information into SQL statements through regularization;

[0012] S3. Database table selection: Perform fuzzy matching query on entity information and representative field information, and obtain the corresponding database table information of the entity information in the data knowledge base through the matching query results;

[0013] S4. Generate SQL statements from the big model: Input the entity information and time information extracted from the natural language statements together with the database table information into the big model to obtain SQL statements with time information and SQL statements without time information;

[0014] S5. SQL statement correction: Correct the generated SQL statement using time information and field information.

[0015] As a further solution of the present invention, the specific steps of constructing a data knowledge base are as follows:

[0016] S11. Collect all database tables involved in the query and save them in the original database;

[0017] S12. Filter the fields in the original database by data type and data content category to obtain representative field information in the original database;

[0018] S13, establishing a mapping relationship between the database table and the representative field information in the original database;

[0019] S14. Establish a field search service through the representative field message, and establish a database table mapping service through the mapping relationship.

[0020] As a further solution of the present invention: the specific process of semantic analysis is as follows: inputting a natural language sentence into the large model, and extracting entity information and time information from the input natural language sentence;

[0021] The extracted entity information acts on the adaptive selection module of the database table, and the entity information is matched with the data knowledge base to perform adaptive selection of the database table;

[0022] The extracted time information acts on the time rule information processing module, and the time information processing is realized through template matching.

[0023] As a further solution of the present invention, the specific steps of database table selection are as follows:

[0024] S31. Input the entity information identified during the semantic analysis process into the field search service for fuzzy matching query to match the corresponding representative field message in the data knowledge base;

[0025] S32. The representative field message obtained in step S31 is input into the corresponding library-table mapping service through the corresponding library-table mapping relationship for matching query, so as to obtain the database table information corresponding to the entity information of the natural language statement in step S31, and establish an association relationship between the natural language statement and the database table.

[0026] As a further solution of the present invention, the specific steps of generating SQL statements from a large model are as follows:

[0027] S41. First, the natural language sentence is processed into a natural language sentence with a time message and a natural language sentence without a time message according to a time rule;

[0028] S42: Then, the natural language sentence with the time message, the natural language sentence without the time message, and the database table corresponding to the natural language sentence are input into the large model;

[0029] S43. The large model builds a prompt to generate SQL, and calls the large model interface to generate corresponding SQL statements with time messages and SQL statements without time messages.

[0030] As a further solution of the present invention, the specific steps of SQL statement correction are as follows:

[0031] S51. Compare the SQL statement with time information and the SQL statement without time information, and locate the time field in the SQL statement with time information;

[0032] S52. Verify whether the execution results of the SQL statement with time information generated by the large model are the same as the time SQL statement parsed by the rules; if the execution results are the same, use the SQL statement with time information as the time-corrected SQL statement generated by the corresponding natural language; if the execution results are different, use the SQL statement without time information plus the time SQL statement converted by the rules as the time-corrected SQL statement generated by the corresponding natural language;

[0033] S53. Then, the field information in the time-corrected SQL statement generated by the large model is compared with the corresponding field message in the data knowledge base; if the two field information are consistent, the time-corrected SQL statement is used as the final SQL statement; if the two field information cannot be consistent, the field information matched by similarity retrieval is used to replace the field message in the time-corrected SQL statement, and the time-corrected SQL statement after the field information is replaced is used as the final SQL statement.

[0034] As a further solution of the present invention: the large model can adopt ChatGPT or ChatGLM.

[0035] Compared with the prior art, the present invention has the following beneficial effects:

[0036] 1. From a natural language perspective, this invention analyzes the structure of natural language statements, emphasizing that they primarily consist of time and business information. Business information is flexible and adaptable to logical reasoning within large models, while time information is relatively fixed and suitable for rule-based template processing. Therefore, this invention constructs a method for generating SQL by processing time and business information separately, improving the ability to convert time information into SQL and increasing the accuracy of SQL generated from natural language.

[0037] 2. This invention breaks through the word limit of SQL generated by large models, builds prompt projects with the most accurate information, and quickly implements data analysis tasks in multi-table scenarios.

[0038] 3. The present invention eliminates the dependence of NL2SQL knowledge enhancement on the knowledge graph, quickly builds a knowledge database based on database entity fields, and realizes NL2SQL knowledge enhancement in a convenient and low-cost manner.

[0039] 4. The present invention expands the application of natural language parsing capabilities, divides language categories for processing, improves the accuracy of time rule information parsing, eliminates the impact of time information on LLM generated SQL, and enhances the robustness of natural language generated SQL.

[0040] 5. The present invention utilizes the original database table field content and constructs a natural language input and original data information knowledge base through non-entity field filtering, establishes data retrieval services and database table mapping services, realizes the process of adaptive table selection in multi-table scenarios, and performs post-processing verification on the field information of the generated SQL statement to ensure the accuracy of the value in the SQL statement, thereby improving the efficiency and accuracy of SQL generation by large models.

[0041] 6. This invention divides natural language into two categories: time information and business information, and adopts different processing methods for different language components. Regular time information is processed using a rule template approach to ensure the accuracy of time information processing. Entity information is processed using a large model approach, fully leveraging the large model's reasoning process for natural language and comprehensively improving the accuracy of time information generation. BRIEF DESCRIPTION OF THE DRAWINGS

[0042] Figure 1 This is the NL2SQL workflow diagram based on LLM multi-table union in the present invention.

[0043] Figure 2 A flow chart is constructed for the data knowledge base in the present invention.

[0044] Figure 3 This is a flowchart of the semantic analysis process in the present invention.

[0045] Figure 4 This is a flowchart of the database table selection process in the present invention.

[0046] Figure 5 Generate SQL statement processing flow chart for the large model in the present invention.

[0047] Figure 6 This is a flowchart of the SQL statement correction process in the present invention. DETAILED DESCRIPTION

[0048] The following will clearly and completely describe the technical solutions in the embodiments of the present invention in conjunction with the accompanying drawings. Obviously, the described embodiments are only part of the embodiments of the present invention, not all of the embodiments. Based on the embodiments of the present invention, all other embodiments obtained by ordinary technicians in this field without making creative efforts are within the scope of protection of the present invention.

[0049] See also Figures 1 to 6 , a NL2SQL method based on LLM multi-table adaptive selection, based on the large model processing capability, from multiple aspects such as multi-table selection, time rule processing and knowledge enhancement, to achieve a multi-table adaptive selection NL2SQL method, improve the accuracy of generated SQL statements, and meet the practical requirements of general business applications. The present invention mainly includes the functions of data knowledge base construction, semantic analysis processing, database table selection, time rule processing, large model generation processing and correction processing of generated SQL statements, and finally outputs highly available query statements. Its workflow is as follows Figure 1 shown.

[0050] 1. Data knowledge base construction

[0051] The construction of a data knowledge base is to pre-process the database involved in the query and form a knowledge base based on the original data information. First, the database fields are filtered by data type and data content category to obtain the representative field information in the database table and establish a mapping relationship between the representative field in the database table and the database table; then, the data knowledge base is constructed based on the content of the representative field information; finally, data retrieval services and database table mapping services are established. This provides a knowledge query basis for database table selection and SQL statement correction. Its construction process is as follows: Figure 2 shown.

[0052] 2. Semantic analysis and processing

[0053] Semantic analysis processing uses the power of large models to extract entity and time information from input natural language sentences. The extracted entity information acts on the adaptive selection module of the database table, and matches the entity information with the data knowledge base to achieve adaptive selection of the database table; the extracted time information acts on the time rule information processing module, and realizes the processing of time information through template matching. The processing flow is as follows: Figure 3 shown.

[0054] 3. Database table selection

[0055] Database table selection is to use the entity information identified by the semantic analysis module to perform fuzzy matching query with the representative field information content in the data knowledge base, obtain the association relationship between the natural language sentence and the representative field in the data knowledge base, and then obtain the association relationship between the natural language sentence and the database table, providing accurate database table information for the SQL prompt generated by the large model. Its processing flow is as follows: Figure 4 shown.

[0056] 4. Time rule processing

[0057] Time rule processing parses time information in natural language statements using rule template matching. Rule templates consist of slots, common characters, and regular syntax. The templates define the time query syntax of natural language statements and convert them into precise time SQL statements.

[0058] 5. Generate SQL from large models

[0059] The big model generates SQL statements based on the input database table information and natural language statements, automatically constructs the SQL generation prompt, and then calls the big model interface to generate the corresponding SQL statements. In order to reduce the impact of time information on SQL statement generation and ensure the accuracy of time parsing, the present invention adds a natural language statement that filters out time information to locate the time field. Therefore, in addition to the database table information, the input of the big model also includes two natural language statements with and without time, and finally generates two different SQL statements. Its processing flow is as follows Figure 5 shown.

[0060] 6. SQL statement correction

[0061] SQL statement correction is to use the SQL statements generated by the data knowledge base information and time rules to accurately correct the two SQL statements generated by the large model. The precise correction of SQL statements is mainly reflected in two aspects. One is to correct the time information in the generated SQL statements. First, compare the SQL statements with time information and the SQL statements without time information to locate the time field, and then verify whether the execution results of the time SQL statements generated by the large model are the same as the time SQL statements parsed by the rules. If the execution results are the same, the SQL statement generated by the natural language with time information is output; if the execution results are different, the SQL statement without the time field plus the time statement converted by the rules is output; the other is to correct the value in the SQL statement, that is, the field information. In principle, the value must exist in the database table, so it is necessary to ensure that the value extracted by the large model is consistent with the value in the data knowledge base. First, verify whether the field information in the SQL statement can be accurately matched. If so, the SQL statement is output directly; if not, the matching field content is retrieved by similarity to replace the value in the SQL statement for output. The processing process is as follows Figure 6 shown. Specific embodiment:

[0063] 1. Build a data knowledge base

[0064] In healthcare scenarios, there are multiple tables containing patient basic information, diagnosis information, and test information. Select the corresponding representative fields for each database table involved in the business. The processing steps are as follows:

[0065] (1) Select the representative field

[0066] The patient basic information table includes fields such as ID, name, gender (male, female), age, date of birth, and ethnicity. Based on the requirements, select name and gender as representative fields.

[0067] The diagnosis information table includes fields such as diagnosis ID, diagnosis source (outpatient diagnosis, admission diagnosis, discharge diagnosis, and pathology diagnosis), disease name, diagnosis time, and treatment department. Based on your needs, select diagnosis source and disease name as representative fields.

[0068] Test field information includes: test ID, test item, test specimen, test unit, test abnormality indicator, test reference value range, test result, test report time, etc. Test item and test result can be selected as representative fields based on business needs.

[0069] Depending on your business needs, you can adjust the representative fields of your database tables. For example, if you're targeting clinical medicine for precise retrieval of individual data, or basic research for general statistics of group data, you can adjust the fields based on the specific scenarios. It's recommended to select unique fields as representative fields, rather than general fields like time information.

[0070] (2) Build field query and database table mapping services

[0071] Build an index based on the selected representative fields and provide field query services. Provide database table services that map the corresponding field query results and support multi-table selection.

[0072] 2. After building the knowledge base, you can perform multi-table query tasks on the database based on the query statement, for example, querying women aged 30 and over who were diagnosed with gestational diabetes in 2022 and have undergone iron metabolism testing. The steps are as follows:

[0073] (1) Semantic analysis and processing

[0074] Construct a prompt to extract entity information and time information from the sentence "Search for women aged 30 and over who were diagnosed with gestational diabetes in 2022 and have undergone iron metabolism testing." The details are as follows:

[0075] Entity information: gestational diabetes, iron metabolism test, female;

[0076] Time information: within 2022.

[0077] (2) Selection of database tables

[0078] Using the entity information of "gestational diabetes," "iron metabolism test," and "female" obtained through semantic analysis as input, we use field fuzzy query and database mapping services to obtain the database tables associated with the entities. The associations are as follows:

[0079] Entity "gestational diabetes" associated diagnosis information table;

[0080] Entity "Iron Metabolism Test" associated test information table;

[0081] The entity "Female" is associated with the basic information table.

[0082] (3) Time information rule processing

[0083] Regular expressions can easily convert rule-based time information into SQL. Natural language time information generally appears as conditional information, so simply parsing the rules allows for accurate SQL conversion of time information. The results of rule parsing of time information are shown below:

[0084] The time information “within 2022” is converted to YEAR(diagnosis_time)=2022.

[0085] (4) Generate SQL statements from large models

[0086] First, the time information is filtered out of the statement "Query women aged 30 and over who were diagnosed with gestational diabetes in 2022 and have undergone iron metabolism testing" to obtain the statement "Query women aged 30 and over who were diagnosed with gestational diabetes and have undergone iron metabolism testing" without time information.

[0087] Bring the two statements into the big model separately to obtain the corresponding SQL. The details are as follows:

[0088] SQL statement with time information: SELECT * FROM patients WHERE disease_name = 'gestational diabetes' AND diagnosis_time > = 2022-01-01 AND patient_gender = 'female' AND test_item = 'iron metabolism test' AND patient_age > 30;

[0089] SQL statement without time information: SELECT * FROM patients WHERE disease_name = 'gestational diabetes' AND patient_gender = 'female' AND test_item = 'iron metabolism test' AND patient_age>30.

[0090] (5) Generating sentence correction

[0091] <1> Time Correction

[0092] Compare the time query conditions parsed by the rule with those generated by the large model to see if they are the same. There are two situations:

[0093] A. Different time query conditions

[0094] This indicates that there is an error in the parsing of the time information. The SQL generated by the time information parsed by the rule plus the statement without time information is the output of the time correction.

[0095] B. Time query conditions are the same

[0096] This indicates that the time information is parsed correctly, and the SQL generated by the statement with time information is the output of time correction.

[0097] For example, the time field information of "diagnosis_time>=2022-01-01" and "YEAR(diagnosis_time)=2022" is different, so the output of time correction is: SELECT *FROM patients WHERE disease_name='gestational diabetes' AND YEAR(diagnosis_time)=2022 AND patient_gender='female' AND test_item='iron metabolism test' AND patient_age>30.

[0098] <2> Field Correction

[0099] Get entity information in SQL statements, such as the "Iron Metabolism Test" field. Use the entity information in the SQL as input and accurately match the entity field. The following are available:

[0100] A. Entity Retrieval

[0101] This indicates that the entity resolution is correct and no correction is required.

[0102] B. Entity Retrieval

[0103] This indicates that the field is parsed incorrectly or the relevant information does not exist in the database; it will be replaced with the most similar information in the field knowledge base.

[0104] For example, when a user enters an inspection item, they enter "iron metabolism" because it's clearly a test item, omitting the word "inspection" by default. When expressing a natural language query, the word "inspection" is usually added due to grammatical conventions, resulting in entity recognition as "iron metabolism inspection." However, the "iron metabolism inspection" field does not exist in the database, so the correct query result cannot be obtained. A similarity query is then used to retrieve the field information with the highest similarity, and the entity field information in the SQL is replaced to obtain the correct result.

[0105] Replace "iron metabolism test" with "iron metabolism", and the final SQL input is: SELECT * FROM patients WHERE disease_name = 'gestational diabetes' AND YEAR (diagnosis_time) = 2022 AND patient_gender = 'female' AND test_item = 'iron metabolism' AND patient_age>30.

[0106] The above description is only a preferred specific embodiment of the present invention, but the scope of protection of the present invention is not limited thereto. Any technician familiar with the technical field, within the technical scope disclosed by the present invention, who makes equivalent replacements or changes based on the technical solution and inventive concept of the present invention, should be covered by the scope of protection of the present invention.

Claims

1. An NL2SQL method based on LLM multi-table adaptive selection, characterized by: The following steps are included: S1. Build a data knowledge base: Extract representative field information from the original database involved in the query and build a data knowledge base based on the content of the representative field information; S2, semantic analysis and processing: extract entity information and time information from the natural language sentences input into the large model, and convert the time information into SQL statements through regularization; S3. Database table selection: Perform fuzzy matching query on entity information and representative field information, and obtain the corresponding database table information of the entity information in the data knowledge base through the matching query results; S4. Generate SQL statements from the big model: Input the entity information and time information extracted from the natural language statements together with the database table information into the big model to obtain SQL statements with time information and SQL statements without time information; S5. SQL statement correction: Correct the generated SQL statement using time information and field information; The specific steps for SQL statement correction are as follows: S51. Compare the SQL statement with time information and the SQL statement without time information, and locate the time field in the SQL statement with time information; S52. Verify whether the execution results of the SQL statement with time information generated by the large model are the same as the time SQL statement parsed by the rules; if the execution results are the same, use the SQL statement with time information as the time-corrected SQL statement generated by the corresponding natural language; if the execution results are different, use the SQL statement without time information plus the time SQL statement converted by the rules as the time-corrected SQL statement generated by the corresponding natural language; S53. Then, the field information in the time-corrected SQL statement generated by the large model is compared with the corresponding field message in the data knowledge base; if the two field information are consistent, the time-corrected SQL statement is used as the final SQL statement; if the two field information cannot be consistent, the field information matched by similarity retrieval is used to replace the field message in the time-corrected SQL statement, and the time-corrected SQL statement after the field information is replaced is used as the final SQL statement.

2. The NL2SQL method based on LLM multi-table adaptive selection according to claim 1, characterized in that: The specific steps to build a data knowledge base are as follows: S11. Collect all database tables involved in the query and save them in the original database; S12. Filter the fields in the original database by data type and data content category to obtain representative field information in the original database; S13, establishing a mapping relationship between the database table and the representative field information in the original database; S14. Establish a field search service through the representative field message, and establish a database table mapping service through the mapping relationship.

3. The NL2SQL method based on LLM multi-table adaptive selection according to claim 2, characterized in that: The specific process of semantic analysis is as follows: input natural language sentences into the large model, and extract entity information and time information from the input natural language sentences; The extracted entity information acts on the adaptive selection module of the database table, and the entity information is matched with the data knowledge base to perform adaptive selection of the database table; The extracted time information acts on the time rule information processing module, and the time information processing is realized through template matching.

4. The NL2SQL method based on LLM multi-table adaptive selection according to claim 3, characterized in that: The specific steps for selecting a database table are as follows: S31. Input the entity information identified during the semantic analysis process into the field search service for fuzzy matching query to match the corresponding representative field message in the data knowledge base; S32. The representative field message obtained in step S31 is input into the corresponding library-table mapping service through the corresponding library-table mapping relationship for matching query, so as to obtain the database table information corresponding to the entity information of the natural language statement in step S31, and establish an association relationship between the natural language statement and the database table.

5. The NL2SQL method based on LLM multi-table adaptive selection according to claim 4, characterized in that: The specific steps for generating SQL statements for a large model are as follows: S41. First, the natural language sentence is processed into a natural language sentence with a time message and a natural language sentence without a time message according to a time rule; S42: Then, the natural language sentence with the time message, the natural language sentence without the time message, and the database table corresponding to the natural language sentence are input into the large model; S43. The large model builds a prompt to generate SQL, and calls the large model interface to generate corresponding SQL statements with time messages and SQL statements without time messages.

6. The NL2SQL method based on LLM multi-table adaptive selection according to claim 5, characterized in that: Large models can use ChatGPT or ChatGLM.

Images