A method and device for generating SQL instructions in a conversational natural language

Abstract

The application provides a method and device for generating SQL instructions in a conversational natural language, and relates to the technical field of language processing. The method comprises: receiving a natural language query request input by a user; calling a neural symbolic reasoning module to extract a query intent and a key entity list, generating a plurality of candidate SQL instructions through symbolic reasoning, and evaluating a confidence level; if the highest confidence level is lower than a set threshold, initiating a multi-round conversation clarification and feeding back to the neural symbolic reasoning module; calling an adaptive SQL reconstruction engine to perform filtering, reconstruction or optimization based on a dynamic data environment on a target candidate SQL instruction; and executing a final SQL instruction, obtaining a query result and returning it to the user. The application solves the technical problem that the accuracy of SQL instruction generation is low due to the fuzziness of natural language query and the dynamic complexity of the data environment in the prior art, thereby affecting the query execution efficiency. The application improves the query execution efficiency while ensuring the accuracy of the SQL semantics.

Description

Technical Field

[0001] This application relates to the field of language processing technology, specifically to a method and apparatus for generating SQL commands using conversational natural language. Background Technology

[0002] With the development of information systems across various industries, a large amount of data has accumulated. To utilize this data, it is necessary to query and analyze it to extract valuable insights. Typically, custom query and analysis software is developed based on specific data query and analysis needs. However, if these needs change, the software needs to be redeveloped, resulting in high costs. Furthermore, many needs are temporary and one-off, making custom software development impractical. In such cases, specialized data engineers are needed to write SQL queries and analyses, then export and deliver the data.

[0003] Because data querying methods heavily rely on skilled technical personnel to write SQL commands, communication gaps between business and technical staff often lead to misunderstandings of query requirements, resulting in high development costs. While existing natural language-based SQL generation technologies can lower the barrier to entry, the inherent ambiguity of natural language and the dynamic complexity of database environments often result in insufficient command generation accuracy and low execution efficiency. Especially when dealing with complex data models, the generated SQL commands may be syntactically correct, but semantic misunderstandings can lead to incorrect query results or performance bottlenecks, failing to meet the actual reliability and efficiency requirements of production environments.

[0004] In summary, existing technologies suffer from low accuracy in generating SQL commands due to the ambiguity of natural language queries and the dynamic complexity of the data environment, which in turn affects query execution efficiency. Summary of the Invention

[0005] The purpose of this application is to provide a method and apparatus for generating SQL commands using conversational natural language, in order to solve the technical problem in the prior art where the accuracy of SQL command generation is low due to the ambiguity of natural language queries and the dynamic complexity of the data environment, which in turn affects the efficiency of query execution.

[0006] To achieve the above objectives, this application provides a method and apparatus for generating SQL commands using conversational natural language.

[0007] In a first aspect, this application provides a method for generating SQL commands using conversational natural language. This method is implemented using a device for generating SQL commands using conversational natural language. The method includes: receiving a natural language query request input by a user; invoking a neural symbolic inference module to process the query request, extracting the query intent and a list of key entities, combining the query intent and the list of key entities through symbolic inference to generate multiple candidate SQL commands, and evaluating the confidence level of the multiple candidate SQL commands; if the highest confidence level of the multiple candidate SQL commands is lower than a set threshold, initiating multiple rounds of dialogue clarification, and feeding back the clarification information to the neural symbolic inference module for reprocessing, outputting a target candidate SQL command higher than the set threshold; invoking an adaptive SQL reconstruction engine to perform filtering, reconstruction, or optimization based on a dynamic data environment on the target candidate SQL command, generating a final SQL command; executing the final SQL command, obtaining the query result, and returning it to the user.

[0008] Optionally, the query request is parsed using a large language model to output a structured query intent and a list of key entities; the query intent and the list of key entities are then input into a symbolic inference engine, which, based on a pre-built data source description library, generates multiple candidate SQL instructions with different syntactic structures through path lookup and logical combination; the verification submodule performs syntax validation and semantic integrity evaluation on the multiple candidate SQL instructions and outputs the confidence level of the multiple candidate SQL instructions.

[0009] Optionally, the data source structure in the data source description library is organized in the form of one master and multiple slave tables, with each data source containing one master table and multiple slave tables; wherein, the primary key field of the slave table must contain the primary key field of the master table, and the field order is consistent with that of the master table.

[0010] Optionally, before parsing the query request using a large language model and outputting a structured query intent and a list of key entities, it is determined whether there are historical query conversations within a preset interval; if so, the query request is combined with the conversation history to form a context-enhanced query request, which is then parsed to output a structured query intent and a list of key entities.

[0011] Optionally, the data source name and field name in the multiple candidate SQL instructions are extracted as items to be clarified; based on the type of the items to be clarified, similar items are retrieved from the data source description library, and a natural language prompt question containing numbered options is generated; the prompt question is returned to the user, and the user is asked to input a selection or a new description; after receiving user feedback, the original question, the prompt question, and the user feedback are combined into a new input and resubmitted to the neural symbol reasoning module.

[0012] Optionally, for data source names, a word vector model is used to calculate the semantic similarity with all data source names in the data source description library, and the Top-N results are taken; for field names, candidate data sources are located in the data source description library, and name similarity or synonym matching degree is calculated within all fields of the candidate data sources to obtain matching results; similar items are generated based on the Top-N results and the matching results, and natural language prompt questions containing numbered options are generated based on the similar items.

[0013] Optionally, the data distribution changes of the data source are monitored in real time. When data drift is detected, drift features are extracted and a data drift marker is generated. Historical query pattern graphs are retrieved to find past successful queries with a query intent similarity higher than a preset similarity with the target candidate SQL command. Combining the data drift marker with the past successful queries, the SQL generation strategy is dynamically adjusted to filter, reconstruct, or optimize the target candidate SQL command and generate the final SQL command.

[0014] Optionally, dynamically adjusting SQL generation strategies includes predicate pushdown, reordering joins, or introducing temporary summary tables.

[0015] Optionally, when the query complexity of the final SQL instruction is greater than the preset complexity, the final SQL instruction is decomposed into a quick preview query and a full query, wherein the query complexity is calculated based on the data volume aggregation index and the multi-table join index; first, the quick preview query is executed to return partial sample data to the user, and at the same time, the full query is executed asynchronously in the background to provide the user with interface options.

[0016] Secondly, this application also provides an apparatus for generating SQL commands using conversational natural language, for executing a method for generating SQL commands using conversational natural language as described in the first aspect, wherein the apparatus for generating SQL commands using conversational natural language includes: a request receiving module, for receiving a natural language query request input by a user; a symbolic reasoning module, for calling a neural symbolic reasoning module to process the query request, extracting the query intent and a list of key entities, combining the query intent and the list of key entities through symbolic reasoning to generate multiple candidate SQL commands, and evaluating the confidence level of the multiple candidate SQL commands; a dialogue clarification module, for initiating multiple rounds of dialogue clarification if the highest confidence level of the multiple candidate SQL commands is lower than a set threshold, and feeding back the clarification information to the neural symbolic reasoning module for reprocessing, and outputting a target candidate SQL command higher than the set threshold; a command determination module, for calling an adaptive SQL reconstruction engine to perform filtering, reconstruction, or optimization based on a dynamic data environment on the target candidate SQL command, and generating a final SQL command; and a result return module, for executing the final SQL command, obtaining the query result, and returning it to the user.

[0017] One or more technical solutions provided in this application have at least the following technical effects or advantages:

[0018] The system receives natural language query requests from users; it then calls a neural symbolic reasoning module to process the query request, extracting the query intent and a list of key entities. Symbolic reasoning is used to combine the query intent and the list of key entities to generate multiple candidate SQL commands, and the confidence levels of these candidate SQL commands are evaluated. If the highest confidence level of the multiple candidate SQL commands is lower than a set threshold, a multi-round dialogue clarification is initiated, and the clarification information is fed back to the neural symbolic reasoning module for reprocessing, outputting a target candidate SQL command higher than the set threshold. An adaptive SQL reconstruction engine is then invoked to perform filtering, reconstruction, or optimization of the target candidate SQL command based on the dynamic data environment, generating a final SQL command. Finally, the final SQL command is executed to obtain the query result and return it to the user. In other words, the neural symbolic reasoning module processes the user's natural language query request, and when the confidence level of a candidate SQL command is low, it initiates a multi-round dialogue to clarify the user's intent, accurately understands the user's query intent, generates a corresponding SQL command, and optimizes the SQL command based on the dynamic data environment using an adaptive SQL reconstruction engine. This ensures the accuracy of SQL semantics while improving the adaptability and efficiency of query execution.

[0019] The above description is merely an overview of the technical solution of this application. To better understand the technical means of this application and to facilitate its implementation according to the description, and to make the above and other objects, features, and advantages of this application more apparent, specific embodiments of this application are described below. It should be understood that the content described in this section is not intended to identify key or important features of the embodiments of this application, nor is it intended to limit the scope of this application. Other features of this application will become readily apparent through the following description. Attached Figure Description

[0020] To more clearly illustrate the technical solutions in this application or the prior art, the drawings used in the description of the embodiments or the prior art will be briefly introduced below. Obviously, the drawings described below are merely exemplary. For those skilled in the art, other drawings can be obtained based on the provided drawings without creative effort.

[0021] Figure 1 This is a flowchart illustrating a method for generating SQL commands using conversational natural language, as described in this application.

[0022] Figure 2 This is a schematic diagram of the device for generating SQL commands using conversational natural language according to this application.

[0023] Explanation of reference numerals in the attached diagram: Request receiving module 11, symbolic reasoning module 12, dialogue clarification module 13, instruction determination module 14, result return module 15. Detailed Implementation

[0024] This application provides a method and apparatus for generating SQL commands from conversational natural language, solving the technical problem in existing technologies where the ambiguity of natural language queries and the dynamic complexity of the data environment lead to low accuracy in SQL command generation, thus affecting query execution efficiency. The method processes user-input natural language query requests through a neural symbolic reasoning module. When the confidence of candidate SQL commands is low, a multi-turn dialogue is initiated to clarify the user's intent, accurately understand the user's query intent, and generate corresponding SQL commands. An adaptive SQL refactoring engine optimizes the SQL commands based on the dynamic data environment, improving the adaptability and efficiency of query execution while ensuring the semantic accuracy of the SQL.

[0025] The technical solutions of this application will now be clearly and completely described with reference to the accompanying drawings. Obviously, the described embodiments are only a part of the embodiments of this application, and not all of them. It should be understood that this application is not limited to the exemplary embodiments described herein. All other embodiments obtained by those skilled in the art based on the embodiments of this application without creative effort are within the scope of protection of this application. It should also be noted that, for ease of description, only the parts related to this application are shown in the accompanying drawings, not all of them.

[0026] Example 1, please refer to the appendix. Figure 1 This application provides a method for generating SQL commands using conversational natural language. The method is applied to an apparatus for generating SQL commands using conversational natural language, and specifically includes the following steps:

[0027] Receive natural language query requests from users.

[0028] Specifically, users ask questions to an internal chatbot, which then receives natural language query requests—text or voice information entered by the user. Natural language query requests are instructions from users, expressed in everyday language, expressing their desire to retrieve information from a database or perform analysis; they are typically non-standardized. For example, "What were the sales figures for 2024?"

[0029] The chatbot uses natural language processing to parse the user's query intent and key entities, identifying the query's purpose, relevant fields, and conditions. Based on the parsed query, it retrieves potential data source structures from the database. Data source structure information includes table names, field names, and field types, determining which tables' data needs to be accessed, ultimately forming a complete query.

[0030] The front-end application packages the input text, along with necessary session identifiers and user identity information, into a structured request message and sends it to the back-end server over the internet. Upon receiving the request, the back-end server first performs basic validity checks, such as user authentication and request format checks. Then, it parses the core query text and passes it to the next stage of the process—the neural symbolic reasoning module. This ensures the system can understand contextualized, continuous dialogue, rather than viewing each question in isolation. The back-end service system captures and processes user request data from the front-end through technologies such as application programming interfaces (APIs), message queues, or event listeners. This transforms the data query entry point from a professional SQL editor to a natural language chat box, allowing business users without SQL programming skills to directly submit data requests, fundamentally eliminating the role of technical personnel as translators.

[0031] The query request is processed by calling the neural symbolic reasoning module, which extracts the query intent and the list of key entities. The query intent and the list of key entities are combined through symbolic reasoning to generate multiple candidate SQL instructions, and the confidence of the multiple candidate SQL instructions is evaluated.

[0032] Furthermore, this application also includes the following steps: parsing the query request using a large language model to output a structured query intent and a list of key entities; inputting the query intent and the list of key entities into a symbolic reasoning engine, which, based on a pre-built data source description library, generates multiple candidate SQL instructions with different syntactic structures through path lookup and logical combination; and performing syntax verification and semantic integrity evaluation on the multiple candidate SQL instructions through a verification submodule to output the confidence level of the multiple candidate SQL instructions.

[0033] Furthermore, this application also includes the following steps: the data source structure in the data source description library is organized in the form of one master and multiple slave tables, and each data source contains one master table and multiple slave tables; wherein, the primary key field of the slave table must contain the primary key field of the master table, and the field order is consistent with that of the master table.

[0034] Specifically, the neural symbolic reasoning module comprises a large language model, a symbolic reasoning engine, and a verification submodule. The neural part refers to the large language model based on deep neural networks, which excels at understanding and summarizing semantics from unstructured text; the symbolic part refers to the reasoning engine based on formal rules and logic, which excels at handling precise structures and relationships. The neural symbolic reasoning module combines the advantages of both, first using neural networks to understand human language, and then using symbols to assemble machine instructions.

[0035] Within the neurosymbolic reasoning module, the large language model works first, reading the current query text and dialogue history. Leveraging its powerful contextual understanding capabilities, it transforms ambiguous natural language into structured output—a machine-readable format. The large language model then parses the complete question processed by the robot, identifying the user's query intent and key entities. This large language model is a deep learning model trained on massive amounts of text data, enabling it to generate natural language text or understand the meaning of language text. Through large-scale unsupervised training, it learns the patterns and structures of natural language, simulating human language cognition and generation processes to some extent. Its main task is to transform the user's input natural language text into a structured list of query intent and key entities, understanding the grammar and semantics of the language to generate an accurate understanding of the query.

[0036] Structured query intent is a standardized and formalized expression of the user's core needs, mapping colloquial user goals to one or more predefined or inferable database operation types. For example, a user's query might be "What is the total number of orders today?", with the query intent being conditional filtering. The key entity list is a collection of information precisely extracted from the query text that must be mapped to specific elements in the database. Each entity not only contains raw text fragments but also includes its semantic role label, such as sales order, product name, sales amount, and date.

[0037] The parsed query intent and key entities are input into the symbolic inference engine. Based on the query intent and its internal data source description library, the engine generates multiple candidate SQL statements with different syntactic structures through pathfinding and logical combination. The symbolic inference engine uses inference rules to ensure that the generated SQL statements conform to the database structure and query requirements. That is, it plans paths according to a master-slave rule map: if a product name is needed but not found in the master table, the engine automatically knows to join the product information slave table; if the total amount needs to be calculated, the engine knows to join the order details slave table for aggregation. Based on these paths, the engine uses SQL syntax rules to try different logical assembly orders, such as joining first and then filtering, or filtering first and then joining, generating multiple candidate SQL statements that are logically equivalent or similar but may have different execution plans. For example, SQL1 first filters the order IDs for April 2024 from the master table, then joins the details table, then groups by product_id, calculates the total sales amount and total sales volume, and finally retrieves the first record in descending order of total sales amount. SQL2 first joins the main table and the detail table, then filters the data from April 2024 in the result set, and then performs grouping, aggregation, and sorting. SQL3 first pre-aggregates the detail table by product_id and order_id, then joins it with the main table with the filtered time, and finally performs summary sorting across orders.

[0038] The data source structure in the data source description library is organized in a master-slave table format. Each data source contains one master table and multiple slave tables. That is, each data source has only one master table, and can have multiple or zero slave tables. The primary key field of each slave table must contain the primary key field of the master table, and its position and order must be consistent with the master table. This ensures that the master and slave tables can be joined equi-valued through the primary key, and clearly defines in business terms that the slave table data depends entirely on the master table entity. For example, consider a sales order:

[0039] Data source: Sales orders; Main table: sales_order; Table description: Stores the core header information of sales orders. One order corresponds to one record in the main table.

[0040] Serial Number field name type Empty Value range / description 1 order_id String no Primary key, formatted as SO + 8 digits (e.g., SO20250001). 2 order_date date no Date of the transaction (i.e., the date the order was placed) 3 customer_id String no Customer Number 4 total_amount number no Total order amount must be ≥ 0 5 status String no Order status: Pending review / Reviewed / Completed 6 create_time Date and Time no Record creation time

[0041] Detailed table: sales_order_detail; Table description: Stores detailed information about the specific products contained in a sales order. One order can correspond to multiple products, meaning one record in the main table corresponds to multiple records in the detailed table; Primary key fields: order_id, line_number.

[0042] Serial Number field name type Empty Value range / description 1 order_id String no Foreign key, related to the main table sales_order.order_id 2 line_number Integer no Line numbers, starting from 1 and increasing sequentially, are used to identify the order of detail items. 3 product_id String no Product Number 4 quantity number no Product sales quantity must be >0 5 unit_price number no The unit price of the product must be ≥=0. 6 amount number no The detailed amount for this line is calculated from quantity * unit_price.

[0043] The validation submodule performs syntax checks and semantic integrity assessments on multiple candidate SQL commands. First, a rigorous syntax scan is performed to ensure there are no spelling or structural errors. Then, a rule compliance review is conducted, comparing each SQL statement to ensure it meets all requirements in the design specifications, such as whether all required filtering conditions are present, whether all fields to be returned are in the SELECT list, and whether the grouping and aggregation logic is correct. A confidence score is calculated for each candidate SQL command. The confidence score combines the results of the syntax check, semantic coverage, and adherence to best practices. For example, SQL1 is syntactically correct, semantically fully covers all intents and entities: time filtering applies to the main table, and the grouping, aggregation, and sorting logic is clear, resulting in a confidence score of 0.95. SQL2 has correct syntax and complete semantic coverage, but the join-then-filter approach may produce unnecessary intermediate results when the data volume is large. The efficiency is slightly inferior, but the logic is correct, and the confidence level is 0.90. SQL3 has correct syntax, but there is a potential risk in semantic coverage: the pre-aggregation in the subquery may include data from irrelevant orders if time filtering is not considered before the join, leading to incorrect results. The confidence level is 0.70, and points are deducted due to the risk of semantic ambiguity.

[0044] The neural symbolic reasoning module decodes user queries and generates multiple candidate SQL queries, ensuring accurate results. Through pathfinding and logical combination by the symbolic reasoning engine, various candidate SQL queries are generated, from which the optimal query is selected to improve efficiency. The verification submodule ensures that the generated SQL queries conform to the database structure and constraints, preventing query failures or errors due to structure mismatches.

[0045] Furthermore, this application also includes the following steps: before parsing the query request using a large language model and outputting a structured query intent and a list of key entities, it is determined whether there is a historical query dialogue within a preset interval; if so, the query request is combined with the dialogue history to form a context-enhanced query request, which is then parsed to output a structured query intent and a list of key entities.

[0046] Specifically, before processing a user's query request, the system first checks for existing query history to see if the current query is related to previous conversations. Query history includes previously asked questions by the user, the system's answers, and relevant contextual information. The conversation history is the user's previous queries and their associated contextual information during interactions with the chatbot. When generating a query, this historical information helps the system better understand the user's intent and provide more accurate queries based on the context.

[0047] The system applies preset interval rules, such as retrieving only all dialogue records from the same session within the past 30 minutes. If the search results are empty, it indicates that this is a completely new dialogue or the dialogue has been interrupted, and the system will directly use the user's latest query as input. The preset interval is a time window set to determine whether two queries belong to the same continuous dialogue session. It is a configurable duration parameter, such as 30 minutes, 1 hour, or a session cycle, to avoid incorrectly including historical dialogues that are too far apart and may no longer be relevant in the context.

[0048] If historical records are retrieved, the context building process is initiated. Following the chronological order of timestamps, each round of dialogue in the historical records is combined with the user query to form a context-enhanced query request. This context-enhanced query request is the current isolated user query, concatenated with relevant historical dialogue records in chronological order to form a richer text input containing background information. This is equivalent to providing not only the current sentence when asking a question to a large language model, but also the preceding conversations, thus providing the necessary context for the model to understand referential, ellipsis, and continuation intentions.

[0049] To support multi-turn dialogues, the system immediately queries and loads all historical dialogue records of the user in the current session from the dialogue history repository based on the session identifier. The combined query request is then input into the large language model for parsing, outputting a structured query intent and a list of key entities. Leveraging the powerful semantic understanding capabilities of the large language model, the core query intent and entities are extracted based on the context-enhanced query request. Once parsing is complete, the system selects the necessary data source structure based on the entities in the query. For example, if the current query is "profit," and the retrieved historical data is "Query the total sales and total sales volume of product A last month," the historical data and the current query are combined into the following text, ready to be sent to the large language model. In this case, the structured query intent and key entity list parsed by the large language model include product, time value, total sales, total sales volume, cost, profit, etc.

[0050] Contextual enhancement, combined with information from historical dialogues, generates more precise query requests, avoiding misunderstandings caused by linguistic ambiguity. By leveraging the context of historical queries, it understands the user's continuous needs, reducing repetitive input and improving interaction efficiency. Based on the entities in the query request, it dynamically loads the corresponding data source structure information, ensuring that the generated SQL query matches the database structure.

[0051] If the highest confidence level of the multiple candidate SQL instructions is lower than a set threshold, a multi-round dialogue clarification is initiated, and the clarification information is fed back to the neural symbol inference module for reprocessing, outputting a target candidate SQL instruction that is higher than the set threshold.

[0052] Furthermore, this application also includes the following steps: extracting the data source name and field name from the multiple candidate SQL instructions as items to be clarified; retrieving similar items from the data source description library according to the type of the items to be clarified, and generating a natural language prompt question containing a numbered option; returning the prompt question to the user, and waiting for the user to input a selection or a new description; after receiving user feedback, combining the original question, the prompt question, and the user feedback into a new input, and resubmitting it to the neural symbol reasoning module.

[0053] Furthermore, this application also includes the following steps: for data source names, using a word vector model to calculate the semantic similarity with all data source names in the data source description library, and taking the Top-N results; for field names, locating candidate data sources in the data source description library, calculating name similarity or synonym matching degree within all fields of the candidate data sources, and obtaining matching results; generating similar items based on the Top-N results and the matching results, and generating natural language prompt questions containing numbered options based on the similar items.

[0054] Specifically, if the highest confidence level of multiple candidate SQL commands is lower than a set threshold—meaning that even the best-performing candidate SQL command fails to meet the preset acceptable level—the set threshold is a pre-defined quality standard, typically a value between 0 and 1, representing the system's minimum confidence level that the generated SQL command is acceptable. When the confidence level of all candidate SQL commands is below this standard, the automatically generated solution is deemed too risky, requiring multiple rounds of clarification. Multiple rounds of clarification occur when the system cannot clearly understand the user's query intent; it initiates multiple rounds of dialogue to gradually clarify the ambiguous parts and define the query requirements through further interaction with the user.

[0055] Extracting information that needs clarification from multiple candidate SQL statements, including the data source name and field names, is a core component of the query and can lead to ambiguity or semantic ambiguity. The data source name is the name of a table in the database, encompassing multiple database tables, views, or other data structures; the field name is the column name in the table, defining the data type and content of the table.

[0056] For each data source name, a word vector model is invoked to convert it into vectors along with all standard table names in the data source description library, and cosine similarity is calculated. The top N results with the highest similarity are selected. The Top-N results are the N most likely matching options after similarity calculation, sorted from highest to lowest score, and provided as candidate suggestions to the user. N is an adjustable parameter used to balance choice richness and user decision burden. The word vector model allows the vectors corresponding to words with similar meanings to be close in position in mathematical space, thus enabling the calculation of semantic similarity between them. By calculating the similarity between the user's needs and all data source names in the data source description library, the top N data source names with the highest similarity are selected.

[0057] For field names, candidate data sources are located in the data source description library. Then, the similarity or synonym matching degree between the field names in the input query request and all field names in those data sources is calculated. Synonym matching degree is the degree of match between a field name and its synonyms. In database queries, field names may have multiple different naming conventions or expressions; synonym matching degree can find different names for the same concept.

[0058] The system generates similar items from the Top-N results and matching results to help guide users in clarifying their needs. Based on the similar items, it automatically generates natural language prompt questions with numbered options, asking the user if they are referring to a similar table or field. The generated prompt questions are returned to the user interface, and the system enters a waiting state. Users clarify by replying with the option number or entering a more accurate name, based on their business understanding. The prompts are typically: "The data source for [specific data] cannot be found. There are similar data sources in the system: 1 is [specific data]; 2 is [specific data]. Please answer with the number or noun to select the specific data source." For example, a user asks, "What was the sales revenue in 2024?" After receiving the question, the chatbot rewrites the question and calls the main model. The main model returns a clarification indicator and prompts: "The data sources related to sales in the system are: 1. Sales order history; 2. Sales orders. Please enter the number or name to select the specific data source to query." The chatbot stores this round of dialogue in the dialogue history repository and returns the prompts to the user. The user enters "1." The chatbot retrieves the previous round of dialogue from the dialogue history repository, adds the latest user input, and calls the main model with the complete dialogue list as parameters. 7. The large model returns a tag that requires an additional data source structure description, and returns the specific data source as sales order history. The chatbot stores this round of dialogue in the dialogue history repository. It then retrieves the specific structure description of the sales order history from the data source description repository, uses it as the latest dialogue content, completes the dialogue list, and calls the large model again with this list as a parameter. The large model returns the specific SQL. The chatbot stores this round of dialogue in the dialogue history repository, executes the SQL, and returns the result to the user.

[0059] Upon receiving explicit feedback from the user, the system combines the original query, the clarification question posed by the system, and the user's answer in chronological order to create a new, more complete dialogue context. This enhanced context is then submitted to the neural symbolic reasoning module for a new round of processing. Since key ambiguities have been eliminated, the confidence level of the generated candidate SQL commands is typically significantly increased. This process is repeated until the output of the target candidate SQL command exceeds a set threshold.

[0060] In short, if the data source name or field name mentioned in a user's question does not match those already existing in the system, the large model is required to return clarification prompts and clarification tags. The clarification prompts will be returned to the user, who will then enter a number or name. The chatbot will combine this with the preceding dialogue to form a complete list and re-invoke the large model to retrieve the specific SQL.

[0061] In summary, when the system triggers the clarification process based on confidence assessment, it extracts the items to be clarified, namely the data source name and field name, performs intelligent matching retrieval, generates a natural language prompt question containing numbered options, returns the prompt question to the user, and awaits feedback. Simultaneously with user feedback, the system requests the large model to return an identifier of which data source structure information needs to be attached. When the chatbot rewrites the question, in addition to requesting the clarification markers, it also requests a list of names of the data source structures that need to be attached. After receiving this response, the chatbot selects the appropriate description from the data source description, attaches it to the question, and calls the large model again to obtain the results. After receiving user feedback, the system combines the original question, the prompt question, the user feedback, and the dynamically loaded specific data source structure information into a new input, which is then resubmitted to the neural symbolic inference module for deep processing.

[0062] Through multiple rounds of clarification, misunderstandings in queries caused by inconsistencies in table and field names or inaccurate user descriptions are resolved, improving query accuracy. By resubmitting the clarified query request, the generated SQL query is ensured to accurately reflect the user's needs, thereby improving query efficiency and reliability.

[0063] The adaptive SQL refactoring engine is invoked to perform filtering, refactoring, or optimization of the target candidate SQL instructions based on the dynamic data environment, generating the final SQL instructions.

[0064] Furthermore, this application also includes the following steps: real-time monitoring of data distribution changes in the data source; when data drift is detected, extracting drift features and generating data drift markers; retrieving historical query pattern graphs and finding past successful queries with a query intent similarity higher than a preset similarity with the target candidate SQL instruction; combining the data drift markers with the past successful queries, dynamically adjusting the SQL generation strategy, filtering, reconstructing, or optimizing the target candidate SQL instruction, and generating the final SQL instruction.

[0065] Furthermore, this application also includes the following steps: dynamically adjusting the SQL generation strategy, including predicate pushdown, join order rearrangement, or introducing a temporary summary table.

[0066] Specifically, this involves real-time monitoring of data source distribution changes, particularly the data distribution of certain key fields. By monitoring data characteristics, it detects the presence of data drift. Data distribution changes are the statistical changes in the actual stored content of tables in the database over time. When the deviation of a certain indicator exceeds a preset safety threshold, data drift is determined to have occurred. The engine immediately generates a structured data drift marker, recording in detail the fields, types, magnitudes, and time points of the drift. For example, the maximum and minimum values ​​of a certain field may be constantly updated; the frequency of occurrence of certain enumeration values ​​may change significantly; or the distribution pattern of new data (such as a surge in order volume in a certain region) may differ from historical patterns.

[0067] Based on the query intent of the current target candidate SQL command, the engine searches for past successful queries with similar intents in the historical query pattern graph. The similarity calculation considers not only the operation type but also the types of entities involved. After finding several highly similar historical queries, the engine analyzes the execution strategies and performance of these queries in the data environment at that time. The historical query pattern graph is a knowledge graph or database that records and summarizes the characteristics of previously successfully executed SQL queries, storing the abstract patterns of the queries, snapshots of the data environment at the time of execution, and execution performance metrics.

[0068] By combining data drift markers with past successful queries, the engine determines the optimization strategy for the current query. If data drift occurs in a critical field, historical query patterns related to that field are prioritized for reference. For example, if drift markers show a significant decrease in index selectivity for a certain field, and historical patterns indicate that an alternative join order is better in similar situations, the engine will refactor or optimize the SQL command, such as adjusting the order of conditions in the WHERE clause, changing the order of multi-table JOINs, or suggesting the use of different aggregation methods. The adaptive SQL refactoring engine is one of the core engines, capable of dynamically adjusting the SQL query generation strategy based on changes in the data environment, including optimizing the structure of the SQL query.

[0069] Dynamically adjusting SQL generation strategies includes predicate pushdown, join order reordering, and introducing temporary summary tables. Predicate pushdown advances filtering conditions to the table scan or index operation stage as early as possible to reduce unnecessary data reads and improve query efficiency. Join order reordering optimizes the order of join operations in the execution plan during SQL query execution. By reordering the join order, the size of intermediate result sets in the query can be reduced, improving query efficiency. Temporary summary tables are created during the query process to store intermediate results in order to reduce redundant calculations or improve query efficiency, especially when dealing with complex aggregate queries.

[0070] After receiving data drift markers and historical query pattern information, the adaptive SQL refactoring engine selects one or more strategies from the optimization strategy library to generate the final SQL command for the target candidate SQL command. If the data drift markers indicate a significant change in the selectivity of a table's filter field, such as from high selectivity to low selectivity, or if historical patterns suggest that early filtering can significantly reduce intermediate results, the engine will consider predicate pushdown. For example, it might push down range filter conditions for large tables as far as possible before the join operation.

[0071] If data drift indicators show a significant change in the table size ratio, a large change in the cardinality of the join fields, or historical query patterns suggest that a certain join order has performed better in similar past situations, the engine will consider rearranging the join order. For example, using a smaller table or a table with a smaller filtered result set as the front of the join to reduce the burden on subsequent joins.

[0072] If data drift indicators show a sharp increase in data volume, and the query involves complex multi-level aggregations, such as daily aggregation followed by monthly aggregation, or if historical query patterns suggest that such aggregation queries have been slow to execute in the past, but the business allows for a certain degree of approximation or summary data, the engine will consider introducing a temporary summary table. For example, a temporary table summarizing sales by product and by day can be created first, and then quarterly aggregations can be performed based on this table.

[0073] By optimizing predicate pushdown, join order rearrangement, and temporary summary tables, the execution efficiency of SQL queries is improved, especially when data drift occurs, thus avoiding performance degradation. SQL query strategies are dynamically adjusted based on data drift markers and historical query patterns, ensuring consistently high efficiency across different data environments.

[0074] Furthermore, this application also includes the following steps: when the query complexity of the final SQL instruction is greater than the preset complexity, the final SQL instruction is decomposed into a quick preview query and a full query, wherein the query complexity is calculated based on the data volume aggregation index and the multi-table join index; firstly, the quick preview query is executed to return some sample data to the user, and at the same time, the full query is executed asynchronously in the background to provide the user with interface options.

[0075] Specifically, the query complexity of the final SQL command is calculated using two main dimensions: First, the data volume aggregation metric, where the module estimates the total number of rows the query needs to process and assesses the complexity of aggregation operations (e.g., simple counting and summing are simpler than calculating percentiles or medians). Second, the multi-table join metric, where the module analyzes the number, type, and nesting level of joins. These metrics are calculated using a weighted formula to arrive at a quantified complexity value. If this value exceeds a system-preset threshold, such as an estimated execution time exceeding 15 seconds, the query is considered complex and requires the activation of a special process to optimize user experience. The query complexity is calculated by combining the data volume aggregation metric and the multi-table join metric.

[0076] Data volume aggregation metrics measure the amount of data involved in a query, typically including the size of the dataset and the number of records to be scanned. Queries with large datasets increase the computational burden on the system, thus increasing their complexity. Multi-table join metrics indicate the types and number of table joins involved in an SQL query. The complexity of the query increases exponentially with each additional table join, as it requires calculating the relationships between multiple tables.

[0077] The preset complexity is a performance threshold value set based on hardware configuration, database performance, and historical experience. When the computational complexity of the query exceeds the preset complexity, the prediction execution time will be longer, requiring the initiation of a special user experience optimization process. The query decomposition engine is activated, allowing users to choose to view the preview results immediately or wait for the complete results to be generated before viewing them.

[0078] A simplified version of the query is executed by extracting a small subset of data from the original query. This simplified version returns only a portion of the sample data or a limited number of records, displaying this sample to the user. Meanwhile, the process continues in the background, processing all the data and generating the final results. The full query involves a larger volume of data and more complex table joins, thus taking longer to execute. The user is provided with an intuitive progress indicator, such as a progress bar or estimated remaining time, and a key interface option: a link to immediately view the full results, but this option is disabled or in a waiting state until the full query is complete. Users can choose to remain on the current page and wait, or perform analysis or other operations based on the preview results. Once the full query is completed in the background, the user is notified via an on-screen notification. Users can view the complete and accurate results at any time. If a user leaves the page while waiting, the full results are saved to their personal query history for later review.

[0079] By quickly previewing queries and returning partial data in a short time, users can quickly see a summary of the query results and avoid long wait times. Users can choose whether to wait for the complete query results based on the real-time progress of the query, thus optimizing the query experience. Users can choose to view partial results immediately or continue waiting for the complete data. By breaking down queries, complex queries are avoided from being executed all at once, preventing users from waiting too long. Quick preview queries process only a portion of the data, while the complete query is executed asynchronously in the background, improving query efficiency and enhancing user satisfaction.

[0080] Execute the final SQL command to obtain the query results and return them to the user.

[0081] Specifically, once the final SQL command is generated, it will be executed. The final SQL command is typically an optimized query, including all query conditions, joins, aggregation operations, etc., ensuring the user receives accurate results. After executing the SQL query, the database retrieves records that meet the conditions from the data table and organizes them into a dataset, which is the query result. This is usually returned in tabular form, containing all the fields required by the query and the records that meet the conditions. After execution, the query results are returned to the user through the user interface. For interactive applications, the query results are usually displayed on the user interface in the form of tables, charts, or lists. For programmatic interfaces, the results can be returned to the caller via the API. After the query results are returned, the user interface displays the relevant data and, depending on the application scenario, may provide various follow-up operation options, such as viewing detailed data, exporting data, further filtering, or analysis. After executing the final SQL command, all data that meets the conditions is retrieved from the database, and the query results are returned to the user in an appropriate manner, ensuring that the user obtains accurate and complete data.

[0082] In summary, the method for generating SQL commands using conversational natural language provided in this application has the following technical effects:

[0083] The system receives natural language query requests from users; it then calls a neural symbolic reasoning module to process the query request, extracting the query intent and a list of key entities. Symbolic reasoning is used to combine the query intent and the list of key entities to generate multiple candidate SQL commands, and the confidence levels of these candidate SQL commands are evaluated. If the highest confidence level of the multiple candidate SQL commands is lower than a set threshold, a multi-round dialogue clarification is initiated, and the clarification information is fed back to the neural symbolic reasoning module for reprocessing, outputting a target candidate SQL command higher than the set threshold. An adaptive SQL reconstruction engine is then invoked to perform filtering, reconstruction, or optimization of the target candidate SQL command based on the dynamic data environment, generating a final SQL command. Finally, the final SQL command is executed to obtain the query result and return it to the user. In other words, the neural symbolic reasoning module processes the user's natural language query request, and when the confidence level of a candidate SQL command is low, it initiates a multi-round dialogue to clarify the user's intent, accurately understands the user's query intent, generates a corresponding SQL command, and optimizes the SQL command based on the dynamic data environment using an adaptive SQL reconstruction engine. This ensures the accuracy of SQL semantics while improving the adaptability and efficiency of query execution.

[0084] Example 2: Based on the same inventive concept as the method for generating SQL commands from conversational natural language in Example 1, this application also provides an apparatus for generating SQL commands from conversational natural language. Please refer to the appendix. Figure 2 The apparatus for generating SQL commands using conversational natural language includes:

[0085] The system comprises: a request receiving module 11, used to receive natural language query requests input by the user; a symbolic reasoning module 12, used to call a neural symbolic reasoning module to process the query request, extract the query intent and key entity list, combine the query intent and key entity list through symbolic reasoning to generate multiple candidate SQL instructions, and evaluate the confidence level of the multiple candidate SQL instructions; a dialogue clarification module 13, used to initiate multiple rounds of dialogue clarification if the highest confidence level of the multiple candidate SQL instructions is lower than a set threshold, and feed back the clarification information to the neural symbolic reasoning module for reprocessing, outputting a target candidate SQL instruction higher than the set threshold; an instruction determination module 14, used to call an adaptive SQL reconstruction engine to perform filtering, reconstruction, or optimization of the target candidate SQL instruction based on the dynamic data environment, generating a final SQL instruction; and a result return module 15, used to execute the final SQL instruction, obtain the query result, and return it to the user.

[0086] Furthermore, the symbolic reasoning module 12 in the device for generating SQL commands using conversational natural language is also used to: parse the query request using a large language model to output a structured query intent and a list of key entities; input the query intent and the list of key entities into a symbolic reasoning engine, which, based on a pre-built data source description library, generates multiple candidate SQL commands with different syntactic structures through path lookup and logical combination; and perform syntax verification and semantic integrity evaluation on the multiple candidate SQL commands through a verification submodule, and output the confidence level of the multiple candidate SQL commands.

[0087] Furthermore, the symbolic reasoning module 12 in the device for generating SQL commands using conversational natural language is also used to: organize the data source structure in the data source description library in the form of one master and multiple slave tables, with each data source containing one master table and multiple slave tables; wherein, the primary key field of the slave table must contain the primary key field of the master table, and the field order is consistent with that of the master table.

[0088] Furthermore, the symbolic reasoning module 12 in the device for generating SQL commands using conversational natural language is also used to: determine whether there is a historical query dialogue within a preset interval before parsing the query request through a large language model and outputting a structured query intent and a list of key entities; if so, combine the query request with the dialogue history to form a context-enhanced query request and then parse it to output a structured query intent and a list of key entities.

[0089] Furthermore, the dialogue clarification module 13 in the device for generating SQL commands using conversational natural language is also used to: extract the data source name and field name from the plurality of candidate SQL commands as items to be clarified; retrieve similar items from the data source description library according to the type of the items to be clarified, and generate a natural language prompt question containing numbered options; return the prompt question to the user, and wait for the user to input a selection or a new description; after receiving user feedback, combine the original question, the prompt question, and the user feedback into a new input, and resubmit it to the neural symbol inference module.

[0090] Furthermore, the dialogue clarification module 13 in the device for generating SQL commands using conversational natural language is also used to: for data source names, calculate the semantic similarity with all data source names in the data source description library using a word vector model, and take the Top-N results; for field names, locate candidate data sources in the data source description library, calculate the name similarity or synonym matching degree within all fields of the candidate data sources, and obtain the matching results; generate similar items based on the Top-N results and the matching results, and generate natural language prompt questions containing numbered options based on the similar items.

[0091] Furthermore, the instruction determination module 14 in the device for generating SQL instructions using conversational natural language is also used to: monitor the data distribution changes of the data source in real time; when data drift is detected, extract drift features and generate a data drift marker; retrieve historical query pattern graphs and find past successful queries with a query intent similarity higher than a preset similarity with the target candidate SQL instruction; combine the data drift marker with the past successful queries, dynamically adjust the SQL generation strategy, and filter, reconstruct, or optimize the target candidate SQL instruction to generate the final SQL instruction.

[0092] Furthermore, the instruction determination module 14 in the device for generating SQL instructions using conversational natural language is also used to: dynamically adjust the SQL generation strategy, including predicate pushdown, join order rearrangement, or the introduction of a temporary summary table.

[0093] Furthermore, the instruction determination module 14 in the device for generating SQL instructions using conversational natural language is also used to: when the query complexity of the final SQL instruction is greater than a preset complexity, decompose the final SQL instruction into a quick preview query and a full query, wherein the query complexity is calculated based on data volume aggregation metrics and multi-table join metrics; first, execute the quick preview query to return partial sample data to the user, and simultaneously, execute the full query asynchronously in the background to provide the user with interface options.

[0094] The various embodiments in this specification are described in a progressive manner, with each embodiment focusing on the differences from other embodiments. The method and specific example of generating SQL commands from conversational natural language in the foregoing embodiment one are also applicable to the apparatus for generating SQL commands from conversational natural language in this embodiment. Through the foregoing detailed description of the method for generating SQL commands from conversational natural language, those skilled in the art can clearly understand the apparatus for generating SQL commands from conversational natural language in this embodiment. Therefore, for the sake of brevity, it will not be described in detail here.

[0095] The above description of the disclosed embodiments enables those skilled in the art to make or use this application. Various modifications to these embodiments will be readily apparent to those skilled in the art, and the general principles defined herein may be implemented in other embodiments without departing from the spirit or scope of this application. Therefore, this application is not to be limited to the embodiments shown herein, but is to be accorded the widest scope consistent with the principles and novel features disclosed herein.

[0096] Obviously, those skilled in the art can make various modifications and variations to this application without departing from the spirit and scope of this application. Therefore, if such modifications and variations fall within the scope of this application and its equivalents, this application also intends to include such modifications and variations.

Claims

1. A method for generating SQL commands using conversational natural language, characterized in that, include: Receive natural language query requests from users; The query request is processed by calling the neural symbolic reasoning module, which extracts the query intent and key entity list, combines the query intent and key entity list through symbolic reasoning to generate multiple candidate SQL instructions, and evaluates the confidence of the multiple candidate SQL instructions. If the highest confidence level of the multiple candidate SQL instructions is lower than the set threshold, multiple rounds of dialogue clarification are initiated, and the clarification information is fed back to the neural symbol inference module for reprocessing, outputting the target candidate SQL instruction that is higher than the set threshold. The adaptive SQL refactoring engine is invoked to perform filtering, refactoring, or optimization of the target candidate SQL instructions based on the dynamic data environment, generating the final SQL instructions; Execute the final SQL command, obtain the query results, and return them to the user; The query request is processed by invoking the neural symbolic reasoning module, which extracts the query intent and a list of key entities. Symbolic reasoning is then used to combine the query intent and the list of key entities to generate multiple candidate SQL commands. The confidence levels of these multiple candidate SQL commands are then evaluated, including: The query request is parsed using a large language model, and a structured query intent and a list of key entities are output. The query intent and key entity list are input into the symbolic reasoning engine, which generates multiple candidate SQL commands with different syntax structures based on a pre-built data source description library through path lookup and logical combination. The verification submodule performs syntax checks and semantic integrity assessments on multiple candidate SQL instructions, and outputs the confidence level of the multiple candidate SQL instructions. The adaptive SQL refactoring engine is invoked to perform filtering, refactoring, or optimization of the target candidate SQL instructions based on the dynamic data environment, generating the final SQL instructions, including: Real-time monitoring of data distribution changes in data sources; when data drift is detected, extract drift features and generate data drift markers. Search historical query pattern graphs to find past successful queries that have a query intent similarity to the target candidate SQL command that is higher than a preset similarity. By combining data drift markers with the previously successful queries, the SQL generation strategy is dynamically adjusted to filter, reconstruct, or optimize the target candidate SQL instructions and generate the final SQL instructions. Dynamically adjusting SQL generation strategies includes predicate pushdown, reordering joins, or introducing temporary summary tables.

2. The method for generating SQL commands using conversational natural language as described in claim 1, characterized in that, The data source structure in the data source description library is organized in the form of one master and multiple slave tables, with each data source containing one master table and multiple slave tables. The primary key field of the subsidiary table must contain the primary key field of the parent table, and the field order must be consistent with that of the parent table.

3. The method for generating SQL commands using conversational natural language as described in claim 1, characterized in that, Before parsing the query request using a large language model and outputting a structured query intent and a list of key entities, it is determined whether there are any historical query conversations within a preset interval. If present, the query request is combined with the dialogue history to form a context-enhanced query request, which is then parsed to output a structured query intent and a list of key entities.

4. The method for generating SQL commands using conversational natural language as described in claim 1, characterized in that, If the highest confidence level of the multiple candidate SQL instructions is lower than a set threshold, a multi-round dialogue clarification is initiated, and the clarification information is fed back to the neural symbolic inference module for reprocessing, outputting target candidate SQL instructions that are higher than the set threshold, including: Extract the data source name and field name from the multiple candidate SQL instructions as items to be clarified; Based on the type of the item to be clarified, similar items are retrieved from the data source description library, and a natural language prompt question containing numbered options is generated; The prompt question is returned to the user, and the user is asked to input a selection or a new description. After receiving the user's feedback, the original question, the prompt question, and the user's feedback are combined into a new input and resubmitted to the neural symbol reasoning module.

5. The method for generating SQL commands using conversational natural language as described in claim 4, characterized in that, Based on the type of the item to be clarified, similar items are retrieved from the data source description library, and natural language prompt questions containing numbered options are generated, including: For the data source name, the semantic similarity with all data source names in the data source description library is calculated using a word vector model, and the Top-N results are taken. For field names, candidate data sources are located in the data source description library, and name similarity or synonym matching degree is calculated within all fields of the candidate data sources to obtain the matching results. The Top-N results and the matching results are used to generate similar items, and a natural language prompt question containing numbered options is generated based on the similar items.

6. The method for generating SQL commands using conversational natural language as described in claim 1, characterized in that, After generating the final SQL command, it also includes: When the query complexity of the final SQL instruction is greater than the preset complexity, the final SQL instruction is decomposed into a quick preview query and a full query. The query complexity is calculated based on the data volume aggregation metric and the multi-table join metric. First, the quick preview query is executed to return partial sample data to the user. At the same time, the complete query is executed asynchronously in the background to provide the user with interface options.

7. An apparatus for generating SQL commands using conversational natural language, characterized in that, The steps for implementing a method for generating SQL commands using conversational natural language according to any one of claims 1 to 6, wherein the apparatus for generating SQL commands using conversational natural language comprises: The request receiving module is used to receive natural language query requests input by the user. The symbolic reasoning module is used to call the neural symbolic reasoning module to process the query request, extract the query intent and key entity list, combine the query intent and key entity list through symbolic reasoning to generate multiple candidate SQL instructions, and evaluate the confidence of the multiple candidate SQL instructions. The dialogue clarification module is used to initiate multiple rounds of dialogue clarification if the highest confidence level of the multiple candidate SQL instructions is lower than a set threshold, and to feed the clarification information back to the neural symbol inference module for reprocessing, and output the target candidate SQL instruction that is higher than the set threshold. The instruction determination module is used to call the adaptive SQL refactoring engine to perform filtering, refactoring or optimization of the target candidate SQL instructions based on the dynamic data environment, and generate the final SQL instructions; The result return module is used to execute the final SQL command, obtain the query results, and return them to the user.

Images