NL2SQL method and system based on LLM recursive drawing recall and self-correct

Abstract

The invention relates to the field of text generation, and particularly discloses an LLM recursive refractory recall and self-correct-based NL2SQL method and system, the method comprises the following steps: driving LLM to generate an initial recall table set according to user query and table element information of a database, performing recursive extension on a table in SEEDSET, supplementing a missing table according to VFK in a virtual database environment, constructing SQLGENPROMPT by using FINRECALLSET and SQLHINT, and performing recursive extension on the missing table in the virtual database environment. If the execution fails, recording an error, performing correction according to a prompt, repeating the process until the maximum correction frequency is reached, if an SQL query return result is null, judging whether the result is caused by a false filtering condition through NLI and vector retrieval, and if it is determined that the filtering condition has an error, correcting a filtering value in the SQL, updating a query statement and performing re-execution to ensure that the SQL result is accurate. And sorting all successfully executed SQL query statements according to quality, selecting an optimal query statement as final output, and returning an SQL result required by a user.

Description

Technical Field

[0001] The present invention relates to the field of text generation, and more specifically, to an NL2SQL method and system based on LLM recursive reasoning recall and self-correction. Background Art

[0002] The development of natural language processing technology enables non-professional users to express data query requirements through natural language without having to master complex SQL knowledge. Although existing NL2SQL tools on the market adopt methods such as rule-based, template matching, or statistical machine learning models, large language models (LLMs) have recently shown great potential in NL2SQL conversion due to their powerful semantic understanding and context-awareness capabilities. However, even the most advanced LLMs still face challenges in accurately understanding user intent and converting it into correct SQL queries, especially in accurately recalling relevant data tables in complex or multi-table environments. In addition, LLMs may generate SQL statements that seem reasonable but are actually invalid or incorrect, a phenomenon known as "hallucination", which leads to execution failure or returns unexpected results. Existing systems generally lack mechanisms to specifically automatically detect SQL and correct these potential errors, which increases user usage costs and reduces efficiency. Therefore, improving the accuracy of table recall and enhancing the quality control of generated SQL have become urgent issues to be addressed.

[0003] In order to solve the above problems, a technical solution is now provided. Summary of the invention

[0004] In order to overcome the above-mentioned defects of the prior art, the present invention provides an NL2SQL method and system based on LLM recursive reasoning recall and self-correction. The method and system use the logical reasoning ability of LLM to identify and recall related tables that are potentially associated with the seed recall table, thereby reducing the dependence on external structured information. In the process of executing the recall, valuable additional prompt information can be provided to help guide the generation of subsequent SQL statements, further improving the efficiency of data retrieval and the accuracy of table recall. By introducing NLI technology and text vector analysis, errors can be identified and corrected more accurately, the generation of invalid or erroneous SQL can be reduced, and the query accuracy and user experience can be enhanced, so as to solve the problems raised in the above-mentioned background technology.

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

[0006] An NL2SQL method based on LLM recursive reasoning recall and self-correction includes the following steps:

[0007] Step S1, construct TABLE_RECALL_PROMPT according to user query and all table metadata, drive LLM to generate SEED_SET, expand the table in SEED_SET in the virtual database environment, construct virtual query to supplement the associated table, and gradually recursively iterate to improve the table set;

[0008] Step S2, record relevant information during the recursive recall process, construct USAGE_HINT_PROMPT, drive LLM to obtain ideas and map them to SQL_HINT to help generate query statements;

[0009] Step S3, using FIN_RECALL_SET and SQL_HINT, driving LLM to generate query statement SQL_TEXT, and performing syntax analysis, executing SQL_TEXT, and if an error occurs, driving LLM to correct it until MAX_FIX_TIMES is exceeded;

[0010] Step S4: If the SQL query result is empty, correct the incorrect filter condition through NLI and vector search, re-execute the query, summarize all corrected SQL queries, and use the bubble sort algorithm to select the best SQL_TEXT as FIN_SQL for output.

[0011] As a further solution of the present invention, TABLE_RECALL_PROMPT is constructed according to user query and database table meta information, driving LLM to generate SEED_SET, expanding the table in SEED_SET in the virtual database environment, constructing a virtual query to supplement the associated table, and gradually recursively iterating to improve the table set, including the following specific contents:

[0012] According to the user query and all table meta-information, the table meta-information includes: table description, field description, field data type and field data value range, etc., which constitute TABLE_RECALL_PROMPT, driving LLM to generate SQL to solve the user's request to recall the relevant table. The initial result is used as the initial recall table seed set SEED_SET; the result of the initial recall is insufficient for generating SQL, and the key table is missing. The tables in the recall table seed set SEED_SET are processed in parallel, and each table will be associated and expanded according to the virtual V_FK in the virtual database environment. For each recalled table RECALLED_TABLE, a virtual database V_DATABASE will be constructed through DDL, and the current table will be added to the virtual database V_DATABASE. In the context of the recalled table RECALLED_TABLE being known and other tables existing in the virtual database V_DATABASE, V_SQL_GEN_PROMPT will be constructed to drive LLM to generate a virtual query statement V_SQL for the user_query; the virtual query statement V_SQL will be input into the EXTRA_QUERY module, and SQL_AST syntax parsing will be performed to check whether the recalled table RECALLED_TABLE exists and whether there is a virtual table V_TABLE associated with the V_FK. If the recalled table RECALLED_TABLE does not exist, an application will be resubmitted to V_EXTEND. The V_EXTEND is a virtual extension. If it still does not exist after repeating MAX_V_EXTEND, the current recalled table RECALLED_TABLE seed will be discarded. If the recalled table RECALLED_TABLE exists and the V_FK is associated with the virtual table V_TABLE, then obtain the information about the virtual table V_TABLE and the related field V_COL on the virtual table V_TABLE. According to the virtual query statement V_SQL, the virtual table V_TABLE, and the related field V_COL, construct QUERY_DEFI_PROMPT, drive LLM to generate a query in the context of the virtual database V_DATABASE to obtain the meta information V_TABLE_DESC, V_COL_DESC, etc. of the virtual table V_TABLE and the related field V_COL. According to the virtual table V_TABLE, the related field V_COL, the meta information V_TABLE_DESC, and the meta information V_COL_DESC, construct V2Q_PROMPT, drive LLM to convert the virtual table information into an additional optimal request V_EXTRA_USER_QUERY proposed by the user, pass it to SEMANTIC_RECALL again, and repeat the above steps.Under the continuous recursive virtual expansion of each of the recalled tables RECALLED_TABLE in the recall table seed set SEED_SET, the recall table is gradually improved and a number of FIN_RECALL_SETs are formed.

[0013] As a further solution of the present invention, relevant information is recorded in the recursive recall process, USAGE_HINT_PROMPT is constructed, and LLM is driven to obtain ideas and mapped to SQL_HINT to help generate query statements, including the following specific contents:

[0014] In the process of continuous recursive expansion, the virtual table V_TABLE, the related field V_COL in the virtual query statement V_SQL, and the real R_TABLE and R_COL obtained by V_EXTRA_USER_QUERY in SEMANTIC_RECALL will be recorded. According to the additional best request RECALLED_TABLE proposed by the current user, USAGE_HINT_PROMPT is constructed to drive LLM to obtain the usage idea of ​​the virtual table V_TABLE and the related field V_COL from the virtual query statement V_SQL, and map them to R_TABLE and R_COL as SQL_HINT to help generate better query statements.

[0015] As a further solution of the present invention, FIN_RECALL_SET and SQL_HINT are used to drive LLM to generate query statement SQL_TEXT, perform syntax analysis, and execute SQL_TEXT. If an error occurs, LLM is driven to correct it until MAX_FIX_TIMES is exceeded, including the following specific contents:

[0016] The FIN_RECALL_SET and the corresponding SQL_HINT are input into the SQL_GEN module, and SQL_GEN_PROMPT is constructed to drive LLM to generate a query statement SQL_TEXT to solve the user query. The query statement SQL_TEXT is parsed by the SQL_AST syntax to obtain all the USED_TABLEs used therein; the query statement SQL_TEXT is input into the SQL_EXE module, and the SQL_EXE module executes the query statement SQL_TEXT, interacts with the relevant real database, and obtains query data. When the query statement SQL_TEXT is executed and an interruption exception EXE_EXCEPT occurs, SQL_EXE_FIX_PROMPT is constructed according to the query statement SQL_TEXT, the EXE_EXCEPT, and the USED_TABLE, and the LLM is driven to modify the current query statement SQL_TEXT and resubmit it to the SQL_GEN module for execution. If the query statement SQL_TEXT is modified more than MAX_EXE_FIX times, the current FIN_RECALL_SET and SQL_HINT pair is abandoned.

[0017] As a further solution of the present invention, if the SQL query result is empty, the wrong filter condition is corrected through NLI and vector search, and the query is re-executed, all corrected SQL queries are summarized, and the bubble sort algorithm is used to select the best SQL_TEXT as FIN_SQL for output, including the following specific contents:

[0018] When the SQL_EXE module is executed normally and the query data is empty, the HALLUCINATION_FIX module is used. In addition to the target data being actually empty, if the LLM triggers the filter condition illusion FILTER_HALLUCINATION and causes INCORRECT_FILTER, the query data can also be made empty. The INCORRECT_FILTER mainly includes false filter condition data fake value, inappropriate condition filter data out-of-range text value, etc. After performing the syntax analysis SQL_AST on the query statement SQL_TEXT, all condition filter items CON_FILTER are obtained, and each item is judged in turn according to the above situation. For the false filter condition data fake value, an NLI task is constructed according to the current context userquery, the FIN_RECALL_SET and related meta information for judgment. The current context CUR_CONTEXT is used as the premise of the NLI task, and all the current conditional filter items CON_FILTER are used as the hypothesis of the NLI task. The task is completed by pre-training the StructBert model on the open source dataset. If the NLI result is not ENTAILMENT, the current result is used to construct FAKE_VALUE_FIX_PROMPT using the metadata of the remaining unused table UNUSED_TABLE, and the LLM is driven to repair all the conditional filter items CON_FILTER that use the wrong filter value in the query statement SQL_TEXT, and replace them with equivalent implementations from the remaining unused table UNUSED_TABLE, and output the modified query statement SQL_TEXT. The inappropriate conditional filter data out-of-range text value is generated by the LLM's unknown actual data value range. A vector recall index is constructed in ElasticSearch for all string field data values ​​in the real database, and the data value is converted into dense vector, sparse vector, etc. using the word embedding model. The FILTER_VALUE text value in the CON_FILTER is converted into a vector, and the vector similarity is calculated by hybrid retrieval. Several real data values ​​whose recall scores exceed SIMI_VEC_TH are selected, and the TOP_SIMI_VALUE with the highest score is selected as the replacement value of the FILTER_VALUE.Resubmit the corrected query statement SQL_TEXT to the SQL_EXE module for execution and judgment. When the query return data is still empty, perform HALLUCINATION_FIX for repair. When the same query statement SQL_TEXT is repaired more than MAX_FIX_TIMES times, abandon the current FIN_RECALL_SET and SQL_HINT; input all the query statements SQL_TEXT that pass the above steps into the SQL_INTEGRATION module, and the SQL_INTEGRATION module is responsible for selecting one query statement SQL_TEXT as the final result. The SQL_TEXT_SEQ composed of several query statements SQL_TEXT is sorted in ascending order using the bubble sort method, and the N-choose-1 problem is decomposed into multiple 2-choose-1 problems, and the last query statement SQL_TEXT is selected as the result. In the current bubble sort method, the size comparison of the two query statements SQL_TEXT represents the quality of the query statement. According to the user query, the union of the tables used in the query statement CO_USED_TABLE_SET and related meta information, SQL_TEXT_COMPARE_PROMPT is constructed to drive LLM to evaluate the quality of the two query statements SQL_TEXT and output the query statement SQL_TEXT with better quality. Through the bubble sort based on LLM, the query statement SQL_TEXT with the best quality is found and output as FIN_SQL.

[0019] As a further solution of the present invention, the present invention also provides an NL2SQL system based on LLM recursive reasoning recall and self-correction, the system includes a table recall component and an SQL component, the table recall component is responsible for recalling tables in a database according to a user query, the table recall component finally outputs a number of FIN_RECALL_SET, SQL_HINT pairs, the SQL component is responsible for generating corresponding SQL query statements according to the recalled table and the user query, the table recall component includes a SEMANTIC_RECALL module, a V_EXTEND module, an EXTRA_QUERY module and a RECALL_RECORD module, the SQL component includes an SQL_GEN module, an SQL_EXE module, a HALLUCINATION_FIX module and an SQL_INTEGRATION module;

[0020] The SEMANTIC_RECALL module constructs TABLE_RECALL_PROMPT according to the user query and all the table meta-information, and drives LLM to recall the tables related to solving the user request;

[0021] The V_EXTEND module processes each table in parallel for the situation that the initial recall result SEED_SET is insufficient or lacks key tables. It builds a virtual database V_DATABASE through DDL, adds the current table to it, and builds V_SQL_GEN_PROMPT under a specific context environment to drive LLM to generate a virtual query statement V_SQL;

[0022] The input of the EXTRA_QUERY module is the virtual query statement V_SQL. It first performs SQL_AST syntax parsing and decides the operation according to the situation of RECALLED_TABLE. If it does not exist, it will reapply to the V_EXTEND module. If it exists and has an associated virtual table, it will obtain relevant information, build QUERY_DEFI_PROMPT and V2Q_PROMPT, drive LLM to obtain meta information and convert it into an additional optimal request V_EXTRA_USER_QUERY, and pass it to SEMANTIC_RECALL again to continue recursive virtual extension;

[0023] The RECALL_RECORD module, in the process of continuous recursive expansion, records the real R_TABLE, R_COL obtained by V_TABLE, V_COL and V_EXTRA_USER_QUERY in the virtual query statement V_SQL in the SEMANTIC_RECALL module, and constructs USAGE_HINT_PROMPT to drive LLM to obtain the usage idea and map it to SQL_HINT;

[0024] The SQL_GEN module takes FIN_RECALL_SET and SQL_HINT as input, constructs SQL_GEN_PROMPT, drives LLM to generate query statement SQL_TEXT, and performs SQL_AST syntax analysis on it to obtain USED_TABLE;

[0025] The SQL_EXE module executes the SQL_TEXT query statement and interacts with the real database to obtain query data. When the execution is interrupted by the exception EXE_EXCEPT, SQL_EXE_FIX_PROMPT is constructed to drive the LLM to modify SQL_TEXT and resubmit the execution. If the number of corrections exceeds MAX_EXE_FIX, the current FIN_RECALL_SET and SQL_HINT pair is abandoned;

[0026] When the SQL_EXE module is executed normally but the query data is empty, the HALLUCINATION_FIX module performs syntax analysis on SQL_TEXT to obtain the conditional filter item CON_FILTER, and uses different methods to judge and repair the false filter condition data and the inappropriate conditional filter data, and resubmits the corrected SQL_TEXT for execution. If the number of repairs exceeds MAX_FIX_TIMES, the current FIN_RECALL_SET and SQL_HINT pair is abandoned;

[0027] The input of the SQL_INTEGRATION module is all SQL_TEXT that has passed the previous steps, which are sorted in ascending order using the bubblesort method, and SQL_TEXT_COMPARE_PROMPT is constructed to drive the LLM to evaluate the quality, and the SQL_TEXT with the best quality is selected as the final result FIN_SQL output.

[0028] The technical effects and advantages of the NL2SQL method and system based on LLM recursive reasoning recall and self-correction of the present invention are as follows:

[0029] The present invention provides an NL2SQL method and system based on LLM recursive reasoning recall and self-correction. The method and system utilize the logical reasoning ability of LLM to significantly improve the conversion accuracy and efficiency of natural language to SQL query, can automatically identify and recall database tables related to user queries, reduce dependence on external structured information, and correct potential errors through a self-correction mechanism to ensure that the generated SQL query is accurate and effective. With the help of NLI technology and vector retrieval technology, the method and system can effectively identify and repair erroneous filtering conditions, improve the accuracy of queries, and ultimately enhance user experience, so that non-professional users can also easily issue complex data query requests. BRIEF DESCRIPTION OF THE DRAWINGS

[0030] Figure 1 The present invention is a flowchart of an NL2SQL method based on LLM recursive reasoning recall and self-correction.

[0031] Figure 2 The figure is a schematic diagram of the structure of an NL2SQL system based on LLM recursive reasoning recall and self-correction according to the present invention. DETAILED DESCRIPTION

[0032] The following will be combined with the drawings in the embodiments of the present invention to clearly and completely describe the technical solutions in the embodiments of the present invention. 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 creative work are within the scope of protection of the present invention.

[0033] Example 1

[0034] See also Figure 1 As shown in the flowchart, the embodiment of the present invention provides an NL2SQL method based on LLM recursive reasoning recall and self-correction, which includes the following steps:

[0035] Step S1, construct TABLE_RECALL_PROMPT according to user query and database table meta information, drive LLM to generate SEED_SET, expand the table in SEED_SET in the virtual database environment, construct virtual query to supplement the associated table, and gradually recursively iterate to improve the table set;

[0036] Step S2, record relevant information during the recursive recall process, construct USAGE_HINT_PROMPT, drive LLM to obtain ideas and map them to SQL_HINT to help generate query statements;

[0037] Step S3, using FIN_RECALL_SET and SQL_HINT, driving LLM to generate query statement SQL_TEXT, and performing syntax analysis, executing SQL_TEXT, and if an error occurs, driving LLM to correct it until MAX_FIX_TIMES is exceeded;

[0038] Step S4: If the SQL query result is empty, correct the incorrect filter condition through NLI and vector search, re-execute the query, summarize all corrected SQL queries, and use the bubble sort algorithm to select the best SQL_TEXT as FIN_SQL for output.

[0039] Furthermore, TABLE_RECALL_PROMPT is constructed based on the user query and database table metadata, driving LLM to generate SEED_SET, expanding the tables in SEED_SET in the virtual database environment, constructing virtual queries to supplement the associated tables, and gradually recursively iterating to improve the table set, including the following specific contents:

[0040] According to the user query and all table meta-information, the table meta-information includes: table description, field description, field data type and field data value range, etc., which constitute TABLE_RECALL_PROMPT, driving LLM to generate SQL to solve the user's request to recall the relevant table. The initial result is used as the initial recall table seed set SEED_SET; the result of the initial recall is insufficient for generating SQL, and the key table is missing. The tables in the recall table seed set SEED_SET are processed in parallel, and each table will be associated and expanded according to the virtual V_FK in the virtual database environment. For each recalled table RECALLED_TABLE, a virtual database V_DATABASE will be constructed through DDL, and the current table will be added to the virtual database V_DATABASE. In the context of the recalled table RECALLED_TABLE being known and other tables existing in the virtual database V_DATABASE, V_SQL_GEN_PROMPT will be constructed to drive LLM to generate a virtual query statement V_SQL for the user_query; the virtual query statement V_SQL will be input into the EXTRA_QUERY module, and SQL_AST syntax parsing will be performed to check whether the recalled table RECALLED_TABLE exists and whether there is a virtual table V_TABLE associated with the V_FK. If the recalled table RECALLED_TABLE does not exist, an application will be resubmitted to V_EXTEND. The V_EXTEND is a virtual extension. If it still does not exist after repeating MAX_V_EXTEND, the current recalled table RECALLED_TABLE seed will be discarded. If the recalled table RECALLED_TABLE exists and the V_FK is associated with the virtual table V_TABLE, then obtain the information about the virtual table V_TABLE and the related field V_COL on the virtual table V_TABLE. According to the virtual query statement V_SQL, the virtual table V_TABLE, and the related field V_COL, construct QUERY_DEFI_PROMPT, drive LLM to generate a query in the context of the virtual database V_DATABASE to obtain the meta information V_TABLE_DESC, V_COL_DESC, etc. of the virtual table V_TABLE and the related field V_COL. According to the virtual table V_TABLE, the related field V_COL, the meta information V_TABLE_DESC, and the meta information V_COL_DESC, construct V2Q_PROMPT, drive LLM to convert the virtual table information into an additional optimal request V_EXTRA_USER_QUERY proposed by the user, pass it to SEMANTIC_RECALL again, and repeat the above steps.Under the continuous recursive virtual expansion of each of the recalled tables RECALLED_TABLE in the recall table seed set SEED_SET, the recall table is gradually improved and a number of FIN_RECALL_SETs are formed.

[0041] Furthermore, in the process of recursive recall, relevant information is recorded, USAGE_HINT_PROMPT is constructed, and LLM is driven to obtain ideas and mapped to SQL_HINT to help generate query statements, including the following specific contents:

[0042] In the process of continuous recursive expansion, the virtual table V_TABLE, the related field V_COL in the virtual query statement V_SQL, and the real R_TABLE and R_COL obtained by V_EXTRA_USER_QUERY in SEMANTIC_RECALL will be recorded. According to the additional best request RECALLED_TABLE proposed by the current user, USAGE_HINT_PROMPT is constructed to drive LLM to obtain the usage idea of ​​the virtual table V_TABLE and the related field V_COL from the virtual query statement V_SQL, and map them to R_TABLE and R_COL as SQL_HINT to help generate better query statements.

[0043] Furthermore, FIN_RECALL_SET and SQL_HINT are used to drive LLM to generate the query statement SQL_TEXT, perform syntax parsing, and execute SQL_TEXT. If an error occurs, LLM is driven to correct it until MAX_FIX_TIMES is exceeded, including the following specific contents:

[0044] The FIN_RECALL_SET and the corresponding SQL_HINT are input into the SQL_GEN module, and SQL_GEN_PROMPT is constructed to drive LLM to generate a query statement SQL_TEXT to solve the user query. The query statement SQL_TEXT is parsed by the SQL_AST syntax to obtain all the USED_TABLEs used therein; the query statement SQL_TEXT is input into the SQL_EXE module, and the SQL_EXE module executes the query statement SQL_TEXT, interacts with the relevant real database, and obtains query data. When the query statement SQL_TEXT is executed and an interruption exception EXE_EXCEPT occurs, SQL_EXE_FIX_PROMPT is constructed according to the query statement SQL_TEXT, the EXE_EXCEPT, and the USED_TABLE, and the LLM is driven to modify the current query statement SQL_TEXT and resubmit it to the SQL_GEN module for execution. If the query statement SQL_TEXT is modified more than MAX_EXE_FIX times, the current FIN_RECALL_SET and SQL_HINT pair is abandoned.

[0045] Furthermore, if the SQL query result is empty, the incorrect filter condition is corrected through NLI and vector search, and the query is re-executed. All corrected SQL queries are summarized, and the bubble sort algorithm is used to select the best SQL_TEXT as FIN_SQL for output, including the following specific contents:

[0046] When the SQL_EXE module is executed normally and the query data is empty, the HALLUCINATION_FIX module is used. In addition to the target data being actually empty, if the LLM triggers the filter condition illusion FILTER_HALLUCINATION and causes INCORRECT_FILTER, the query data can also be made empty. The INCORRECT_FILTER mainly includes false filter condition data fake value, inappropriate condition filter data out-of-range text value, etc. After performing the syntax analysis SQL_AST on the query statement SQL_TEXT, all condition filter items CON_FILTER are obtained, and each item is judged in turn according to the above situation. For the false filter condition data fake value, an NLI task is constructed according to the current context userquery, the FIN_RECALL_SET and related meta information for judgment. The current context CUR_CONTEXT is used as the premise of the NLI task, and all the current conditional filter items CON_FILTER are used as the hypothesis of the NLI task. The task is completed by pre-training the StructBert model on the open source dataset. If the NLI result is not ENTAILMENT, the current result is used to construct FAKE_VALUE_FIX_PROMPT using the metadata of the remaining unused table UNUSED_TABLE, and the LLM is driven to repair all the conditional filter items CON_FILTER that use the wrong filter value in the query statement SQL_TEXT, and replace them with equivalent implementations from the remaining unused table UNUSED_TABLE, and output the modified query statement SQL_TEXT. The inappropriate conditional filter data out-of-range text value is generated by the LLM's unknown actual data value range. A vector recall index is constructed in ElasticSearch for all string field data values ​​in the real database, and the data value is converted into densevector, sparse vector, etc. using the word embedding model. The FILTER_VALUE text value in the CON_FILTER is converted into a vector, and the vector similarity is calculated by hybrid retrieval. Several real data values ​​whose recall scores exceed SIMI_VEC_TH are selected, and the TOP_SIMI_VALUE with the highest score is selected as the replacement value of the FILTER_VALUE.Resubmit the corrected query statement SQL_TEXT to the SQL_EXE module for execution and judgment. When the query return data is still empty, perform HALLUCINATION_FIX for repair. When the same query statement SQL_TEXT is repaired more than MAX_FIX_TIMES times, abandon the current FIN_RECALL_SET and SQL_HINT; input all the query statements SQL_TEXT that pass the above steps into the SQL_INTEGRATION module, and the SQL_INTEGRATION module is responsible for selecting one query statement SQL_TEXT as the final result. The SQL_TEXT_SEQ composed of several query statements SQL_TEXT is sorted in ascending order using the bubble sort method, and the N-choose-1 problem is decomposed into multiple 2-choose-1 problems, and the last query statement SQL_TEXT is selected as the result. In the current bubble sort method, the size comparison of the two query statements SQL_TEXT represents the quality of the query statement. According to the user query, the union of the tables used in the query statement CO_USED_TABLE_SET and related meta information, SQL_TEXT_COMPARE_PROMPT is constructed to drive LLM to evaluate the quality of the two query statements SQL_TEXT and output the query statement SQL_TEXT with better quality. Through the bubble sort based on LLM, the query statement SQL_TEXT with the best quality is found and output as FIN_SQL.

[0047] Example 2

[0048] See also Figure 2 As shown in the structural schematic diagram, the embodiment of the present invention provides a NL2SQL system based on LLM recursive reasoning recall and self-correction, the system includes a table recall component and an SQL component, the table recall component is responsible for recalling tables in the database according to user queries, the table recall component finally outputs a number of FIN_RECALL_SET, SQL_HINT pairs, the SQL component is responsible for generating corresponding SQL query statements according to the recalled tables and user queries, the table recall component includes a SEMANTIC_RECALL module, a V_EXTEND module, an EXTRA_QUERY module and a RECALL_RECORD module, the SQL component includes an SQL_GEN module, an SQL_EXE module, a HALLUCINATION_FIX module and an SQL_INTEGRATION module;

[0049] The SEMANTIC_RECALL module constructs TABLE_RECALL_PROMPT according to the user query and all the table meta-information, and drives LLM to recall the tables related to solving the user request;

[0050] The V_EXTEND module processes each table in parallel for the situation that the initial recall result SEED_SET is insufficient or lacks key tables. It builds a virtual database V_DATABASE through DDL, adds the current table to it, and builds V_SQL_GEN_PROMPT under a specific context environment to drive LLM to generate a virtual query statement V_SQL;

[0051] The input of the EXTRA_QUERY module is the virtual query statement V_SQL. It first performs SQL_AST syntax parsing and decides the operation according to the situation of RECALLED_TABLE. If it does not exist, it will reapply to the V_EXTEND module. If it exists and has an associated virtual table, it will obtain relevant information, build QUERY_DEFI_PROMPT and V2Q_PROMPT, drive LLM to obtain meta information and convert it into an additional optimal request V_EXTRA_USER_QUERY, and pass it to SEMANTIC_RECALL again to continue recursive virtual extension;

[0052] The RECALL_RECORD module, in the process of continuous recursive expansion, records the real R_TABLE, R_COL obtained by V_TABLE, V_COL and V_EXTRA_USER_QUERY in the virtual query statement V_SQL in the SEMANTIC_RECALL module, and constructs USAGE_HINT_PROMPT to drive LLM to obtain the usage idea and map it to SQL_HINT;

[0053] The SQL_GEN module takes FIN_RECALL_SET and SQL_HINT as input, constructs SQL_GEN_PROMPT, drives LLM to generate query statement SQL_TEXT, and performs SQL_AST syntax analysis on it to obtain USED_TABLE;

[0054] The SQL_EXE module executes the SQL_TEXT query statement and interacts with the real database to obtain query data. When the execution is interrupted by the exception EXE_EXCEPT, SQL_EXE_FIX_PROMPT is constructed to drive the LLM to modify SQL_TEXT and resubmit the execution. If the number of corrections exceeds MAX_EXE_FIX, the current FIN_RECALL_SET and SQL_HINT pair is abandoned;

[0055] When the SQL_EXE module is executed normally but the query data is empty, the HALLUCINATION_FIX module performs syntax analysis on SQL_TEXT to obtain the conditional filter item CON_FILTER, and uses different methods to judge and repair the false filter condition data and the inappropriate conditional filter data, and resubmits the corrected SQL_TEXT for execution. If the number of repairs exceeds MAX_FIX_TIMES, the current FIN_RECALL_SET and SQL_HINT pair is abandoned;

[0056] The input of the SQL_INTEGRATION module is all SQL_TEXT that has passed the previous steps, which are sorted in ascending order using the bubblesort method, and SQL_TEXT_COMPARE_PROMPT is constructed to drive the LLM to evaluate the quality, and the SQL_TEXT with the best quality is selected as the final result FIN_SQL output.

[0057] The present invention provides an NL2SQL method and system based on LLM recursive reasoning recall and self-correction. The method and system utilize the logical reasoning ability of LLM to significantly improve the conversion accuracy and efficiency from natural language to SQL query. The method can automatically identify and recall database tables related to user queries, thereby reducing the dependence on external structured information, and correct potential errors through a self-correction mechanism to ensure that the generated SQL query is accurate and effective. With the help of NLI technology and vector retrieval technology, the method can effectively identify and repair erroneous filtering conditions, further improve the accuracy of queries, enhance user experience, and enable non-professional users to easily issue complex data query requests without having a deep understanding of SQL syntax and database structure.

[0058] The above is only a specific implementation of the present application, but the protection scope of the present application is not limited thereto. Any person skilled in the art who is familiar with the present technical field can easily think of changes or substitutions within the technical scope disclosed in the present application, which should be included in the protection scope of the present application. Therefore, the protection scope of the present application should be based on the protection scope of the claims.

[0059] Finally: The above description is only a preferred embodiment of the present invention and is not intended to limit the present invention. Any modifications, equivalent substitutions, improvements, etc. made within the spirit and principles of the present invention should be included in the protection scope of the present invention.

Claims

1. A NL2SQL method based on LLM recursive reasoning recall and self-correction, which enables non-professionals to raise data query requirements through daily language, characterized by: The steps include: Step S1, construct TABLE_RECALL_PROMPT according to user query and all table metadata, drive LLM to generate SEED_SET, expand the table in SEED_SET in the virtual database environment, construct virtual query to supplement the associated table, and gradually recursively iterate to improve the table set; Step S2, record relevant information during the recursive recall process, construct USAGE_HINT_PROMPT, drive LLM to obtain ideas and map them to SQL_HINT to help generate query statements; Step S3, using FIN_RECALL_SET and SQL_HINT, driving LLM to generate query statement SQL_TEXT, and performing syntax analysis, executing SQL_TEXT, and if an error occurs, driving LLM to correct it until MAX_FIX_TIMES is exceeded; Step S4: If the SQL query result is empty, correct the incorrect filter condition through NLI and vector search, re-execute the query, summarize all corrected SQL queries, and use the bubble sort algorithm to select the best SQL_TEXT as FIN_SQL for output.

2. The NL2SQL method based on LLM recursive reasoning recall and self-correction according to claim 1, characterized in that In the step S1, TABLE_RECALL_PROMPT is constructed according to the user query and all the table meta information, wherein all the table meta information includes: table description, field description, field data type and field data value range, driving LLM to generate SQL to solve the user request to recall the relevant table, the initial result is used as the initial recall table seed set SEED_SET, and each table in the recall table seed set SEED_SET is processed in parallel, and the association extension is performed according to the virtual V_FK in the virtual database environment. For each recalled table RECALLED_TABLE, a virtual database V_DATABASE is constructed through DDL, and the current table is added to the virtual database V_DATABASE. In the context environment where the recalled table RECALLED_TABLE is known in the virtual database V_DATABASE and other tables exist, V_SQL_GEN_PROMPT is constructed to drive LLM to generate a virtual query statement V_SQL for the user_query.

3. The NL2SQL method based on LLM recursive reasoning recall and self-correction according to claim 2, characterized in that , input the virtual query statement V_SQL into the EXTRA_QUERY module, perform SQL_AST syntax parsing, check the existence of the recalled table RECALLED_TABLE and the virtual table V_TABLE associated with the V_FK, if the recalled table RECALLED_TABLE does not exist, reapply to V_EXTEND, the V_EXTEND is a virtual extension, if it still does not exist after repeating MAX_V_EXTEND, discard the current recalled table RECALLED_TABLE seed, if the recalled table RECALLED_TABLE exists and the V_FK is associated with the virtual table V_TABLE, obtain the relevant information about the virtual table V_TABLE and the relevant field V_COL on the virtual table V_TABLE.

4. The NL2SQL method based on LLM recursive reasoning recall and self-correction according to claim 3, characterized in that , according to the virtual query statement V_SQL, the virtual table V_TABLE, and the related field V_COL, construct QUERY_DEFI_PROMPT, drive LLM to generate queries in the virtual database V_DATABASE context to obtain the meta information V_TABLE_DESC and meta information V_COL_DESC of the virtual table V_TABLE and the related field V_COL, and according to the virtual table V_TABLE, the related field V_COL, the meta information V_TABLE_DESC, and the meta information V_COL_DESC, construct V2Q_PROMPT, drive LLM to convert the virtual table information into an additional optimal request V_EXTRA_USER_QUERY proposed by the user, and pass it to SEMANTIC_RECALL again, and repeat the above steps, while continuously recursively expanding the recalled tables RECALLED_TABLE in the recall table seed set SEED_SET, gradually improve the recall table, and form several FIN_RECALL_SETs.

5. The NL2SQL method based on LLM recursive reasoning recall and self-correction according to claim 1, characterized in that In the step S2, during the continuous recursive expansion process, the virtual table V_TABLE, the related field V_COL in the virtual query statement V_SQL, and the real R_TABLE and R_COL obtained by V_EXTRA_USER_QUERY in SEMANTIC_RECALL will be recorded. According to the additional best request RECALLED_TABLE proposed by the current user, USAGE_HINT_PROMPT is constructed to drive LLM to obtain the usage idea of ​​the virtual table V_TABLE and the related field V_COL from the virtual query statement V_SQL, and map them to the R_TABLE and the R_COL as SQL_HINT to help generate query statements.

6. The NL2SQL method based on LLM recursive reasoning recall and self-correction according to claim 1, characterized in that In the step S3, the FIN_RECALL_SET and the corresponding SQL_HINT are input into the SQL_GEN module, and SQL_GEN_PROMPT is constructed to drive LLM to generate a query statement SQL_TEXT to solve the user query, and the query statement SQL_TEXT is parsed by the SQL_AST syntax to obtain all the USED_TABLEs used therein; the query statement SQL_TEXT is input into the SQL_EXE module, and the SQL_EXE module executes the query statement SQL_TEXT, interacts with the relevant real database, and obtains query data. When the query statement SQL_TEXT execution is interrupted by the exception EXE_EXCEPT, SQL_EXE_FIX_PROMPT is constructed according to the query statement SQL_TEXT, the EXE_EXCEPT, and the USED_TABLE, and the LLM is driven to modify the current query statement SQL_TEXT and resubmit it to the SQL_GEN module for execution. If the query statement SQL_TEXT is modified more than the MAX_EXE_FIX times, the current FIN_RECALL_SET and SQL_HINT pair are abandoned.

7. The NL2SQL method based on LLM recursive reasoning recall and self-correction according to claim 1, characterized in that In step S4, after the SQL_EXE module is executed, if the query data is empty, the HALLUCINATION_FIX module is used. In addition to the target data being truly empty, if the LLM triggers the filter condition illusion and causes the INCORRECT_FILTER, the query data can also be made empty. The INCORRECT_FILTER includes the false filter condition data fake value and the inappropriate condition filter data out-of-range text value. The SQL_TEXT is parsed to obtain all the filter condition items (CON_FILTER), and each item is checked. For the false filter condition data fake value, combined with the current context and metadata, an NLI task is constructed to judge the rationality of the filter condition. If the NLI is judged as non-ENTAILMENT, a FAKE_VALUE_FIX_PROMPT is constructed to correct the wrong filter condition and replace the data of the unused table.

8. The NL2SQL method based on LLM recursive reasoning recall and self-correction according to claim 7, characterized in that ,For the inappropriate conditional filtering data out-of-range text value, use ElasticSearch and word embedding model to build a vector index, calculate the similarity between the filtering value and the real data, replace the original filtering value with the value with the highest similarity, and resubmit the corrected SQL_TEXT for execution. If it is still empty, perform HALLUCINATION_FIX again. When it exceeds MAX_FIX_TIMES, abandon the current FIN_RECALL_SET and SQL_HINT.

9. An NL2SQL system based on LLM recursive reasoning recall and self-correction, applied to an NL2SQL method based on LLM recursive reasoning recall and self-correction according to any one of claims 1 to 8, characterized in that: The system includes a table recall component and an SQL component. The table recall component is responsible for recalling tables in a database according to a user query. The table recall component finally outputs several FIN_RECALL_SET and SQL_HINT pairs. The SQL component is responsible for generating corresponding SQL query statements according to the recalled table and the user query. The table recall component includes a SEMANTIC_RECALL module, a V_EXTEND module, an EXTRA_QUERY module and a RECALL_RECORD module. The SQL component includes an SQL_GEN module, an SQL_EXE module, a HALLUCINATION_FIX module and an SQL_INTEGRATION module.

Images