Text-to-sql generation method, device, medium and product

Abstract

The application discloses a Text-to-SQL generation method, device, medium and product, relates to the application field of geographic information technology, and comprises the following steps: analyzing a natural language query text input by a user to obtain a source field set and a target field set; determining nodes corresponding to each field in the source field set and the target field set in a river and lake length system database mode knowledge graph to obtain a source node set and a target node set; taking a node in the source node set as a starting point, taking a node in the target node set as a terminal point, processing the knowledge graph by using a shortest path algorithm to obtain a path set; decomposing the natural language query text into multiple sub-questions by using a large language model and a thinking chain technology and obtaining a dependency relationship between the sub-questions; obtaining a common table expression according to the dependency relationship and the target field set, and further obtaining an SQL statement. The application can improve the accuracy of generating a database query language in a river and lake length system scenario.

Description

Technical Field

[0001] This application relates to the field of geographic information technology applications, and in particular to a Text-to-SQL generation method, device, medium, and product. Background Technology

[0002] With the deepening of the river and lake chief system, relevant information systems have accumulated massive amounts of structured business data covering multiple dimensions such as water quality monitoring, pollution source control, river patrol, and incident handling. However, existing information platforms generally adopt preset query templates and fixed report output modes, which have problems such as rigid query conditions and weak customization capabilities. Faced with the differentiated query needs of river chiefs and management departments at different levels, especially in complex application scenarios involving cross-table association and multi-dimensional combined analysis, the traditional menu-driven query interface exposes the defects of low interaction efficiency and insufficient decision support capabilities, making it difficult to support refined management decisions and limiting the improvement of the level of intelligent river and lake governance.

[0003] Text-to-SQL technology is an intelligent technology that converts users' natural language queries (Text) into executable database query language (SQL). It aims to lower the barrier to database interaction, allowing users to access required data through natural language without needing in-depth knowledge of SQL syntax. Text-to-SQL technology provides an effective way to solve the aforementioned problems by transforming natural language issues into database query language. Currently, this technology has made some progress in open general domains (such as WikiSQL and Spider) and vertical fields such as industry, finance, and healthcare. However, it still faces significant challenges in application scenarios with domain-specific characteristics, such as the river and lake management system. On the one hand, river and lake management data models typically contain dozens of highly coupled business tables. Existing methods lack accuracy in generating SQL statements involving these multi-table joins and hierarchical relationships. On the other hand, the professional terminology related to the river and lake management system contained in natural language queries (such as "Class V water quality," "black and odorous water bodies," and "shoreline encroachment") and spatial location descriptions (such as "500 meters downstream" and "areas flowing through") have significant semantic gaps with the physical schema of the database, leading to difficulties in entity alignment and relation mapping. Summary of the Invention

[0004] The purpose of this application is to provide a Text-to-SQL generation method, device, medium, and product that can effectively improve the accuracy of generating database query language in the context of the river and lake chief system.

[0005] To achieve the above objectives, this application provides the following solution: In a first aspect, this application provides a Text-to-SQL generation method, including: obtaining natural language query text input by the user.

[0006] The user-input natural language query text is parsed to obtain a source field set and a target field set; the source field set includes the fields involved in the filtering conditions when querying according to the natural language query text; the target field set includes the fields involved in the query results when querying according to the natural language query text.

[0007] The knowledge graph of the river and lake chief system database pattern is obtained by determining the corresponding nodes of each field in the source field set and the target field set in the knowledge graph of the river and lake chief system database pattern. The knowledge graph of the river and lake chief system database pattern is constructed based on the river and lake chief system database. The nodes in the knowledge graph of the river and lake chief system database pattern include table nodes and field nodes. The table nodes are the names of the tables in the river and lake chief system database, and the field nodes are the fields in the tables.

[0008] Using nodes in the source node set as the starting point of the path and nodes in the target node set as the ending point of the path, the shortest path algorithm is used to process the knowledge graph of the river and lake chief system database to obtain a path set.

[0009] For any path in the path set, extract the attributes of each node and each target field node corresponding to the path, and the association relationship between each target table node to obtain the pattern information corresponding to the path; the target field node is the field node corresponding to the path; the target table node is the table node corresponding to the path.

[0010] Using large language model and mind chain technology, the natural language query text input by the user is decomposed into multiple sub-questions based on the pattern information corresponding to each path and the preset river and lake chief system business rules, and the dependency relationship between each sub-question is obtained.

[0011] The common table expression for each subproblem is obtained based on the dependencies between subproblems and the target field set.

[0012] Based on the dependencies between subproblems and the common table expression of each subproblem, the SQL statement for the natural language query text input by the user is obtained.

[0013] Secondly, this application provides a computer device, including: a memory, a processor, and a computer program stored in the memory and executable on the processor, wherein the processor executes the computer program to implement the Text-to-SQL generation method described in any of the preceding claims.

[0014] Thirdly, this application provides a computer-readable storage medium having a computer program stored thereon, which, when executed by a processor, implements the Text-to-SQL generation method described in any of the preceding claims.

[0015] Fourthly, this application provides a computer program product, including a computer program that, when executed by a processor, implements the Text-to-SQL generation method described in any of the preceding claims.

[0016] According to the specific embodiments provided in this application, this application has the following technical effects: This application provides a Text-to-SQL generation method, device, medium, and product. This application generates SQL statements by using a knowledge graph of the river and lake chief system database pattern and a large language model, guided by a step-by-step reasoning mechanism of the thinking chain, and using common table expression technology. This achieves end-to-end generation from natural language query to database query statement, which can effectively solve the problem of multi-step reasoning bias and significantly improve the semantic accuracy of complex queries. It effectively improves the SQL generation accuracy of natural language queries in the river and lake chief system scenario, and can provide a core engine for natural language interactive data query for the river and lake chief system information system, realizing flexible and intelligent natural language interactive data query. Attached Figure Description

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

[0018] Figure 1 This is a flowchart illustrating a Text-to-SQL generation method provided in one embodiment of this application.

[0019] Figure 2 A knowledge graph of the river and lake chief system patrol and management business database provided in one embodiment of this application.

[0020] Figure 3 A flowchart illustrating the process of using graph computation to achieve database schema association.

[0021] Figure 4 This is a flowchart of the progressive SQL generation mechanism.

[0022] Figure 5 This is a diagram illustrating the common expression.

[0023] Figure 6 This is a diagram illustrating the shortest path.

[0024] Figure 7 This is a schematic diagram of the SQL statement for a complex query in the river and lake chief system.

[0025] Figure 8 This is a schematic diagram of the structure of a computer device provided in an embodiment of this application. Detailed Implementation

[0026] The technical solutions of the embodiments of this application will be clearly and completely described below with reference to the accompanying drawings. Obviously, the described embodiments are only some embodiments of this application, and not all embodiments. Based on the embodiments of this application, all other embodiments obtained by those skilled in the art without creative effort are within the scope of protection of this application.

[0027] To make the above-mentioned objectives, features and advantages of this application more apparent and understandable, the application will be further described in detail below with reference to the accompanying drawings and specific embodiments.

[0028] This application provides a Text-to-SQL generation method. In one exemplary embodiment, such as... Figure 1 As shown, the Text-to-SQL generation method includes the following steps 101 to 108.

[0029] Step 101: Obtain the natural language query text input by the user.

[0030] Step 102: Parse the natural language query text input by the user to obtain a source field set and a target field set; the source field set includes the fields involved in the filtering conditions when querying according to the natural language query text; the target field set includes the fields involved in the query results when querying according to the natural language query text.

[0031] Step 103: Determine the corresponding nodes in the River and Lake Chief System database schema knowledge graph for each field in the source field set and target field set, thus obtaining the source node set and target node set. The River and Lake Chief System database schema knowledge graph is constructed based on the River and Lake Chief System database. The River and Lake Chief System database schema knowledge graph adopts a hybrid node structure. The nodes in the River and Lake Chief System database schema knowledge graph include table nodes and field nodes. Table nodes are the names of tables in the River and Lake Chief System database, representing each table in the database. Field nodes are the fields in the tables, representing each field in the tables. Edge relationships include field affiliation relationships (used to represent the relationship between field nodes and table nodes), primary key relationships, foreign key relationships (used to represent the relationship between table nodes), business logic association relationships (used to represent the relationship between table nodes), etc.

[0032] Step 104: Using the nodes in the source node set as the starting point of the path and the nodes in the target node set as the ending point of the path, the shortest path algorithm is used to process the knowledge graph of the river and lake chief system database to obtain the path set.

[0033] Step 105: For any path in the path set, extract the attributes of each node and each target field node corresponding to the path, and the relationships between each target table node to obtain the schema information corresponding to the path; the target field node is the field node corresponding to the path; the target table node is the table node corresponding to the path. The relationships are primary and foreign key relationships as well as business logic relationships.

[0034] Step 106: Using large language model and mind chain technology, the natural language query text input by the user is decomposed into multiple sub-problems based on the pattern information corresponding to each path and the preset river and lake chief system business rules, and the dependency relationship between each sub-problem is obtained.

[0035] Step 107: Obtain the common table expression for each subproblem based on the dependencies between subproblems and the target field set.

[0036] Step 108: Based on the dependencies between subproblems and the Common Table Expression (CTE) of each subproblem, obtain the SQL statement for the user-input natural language query text. A Common Table Expression is a feature in SQL that uses the WITH clause to create a temporary result set (named query results). This result set can be referenced multiple times in subsequent SQL statements within the same query. CTEs effectively improve the logical clarity and maintainability of complex queries, and are particularly suitable for building nested queries step-by-step.

[0037] A database schema refers to the logical structure definition of a database, including elements such as tables, fields, data types, primary keys, and foreign keys, and their interrelationships. It describes the organization, storage format, and constraints of data. In another exemplary embodiment of this application, the construction process of the knowledge graph of the river and lake chief system database schema specifically includes: parsing the river and lake chief system database to obtain the table structure of each table, the attributes of each field in each table, and the annotation information in each table.

[0038] Entity relationships are extracted from the table structure, field attributes, and annotation information of each table in the river and lake chief system database to obtain the entities and relationships between them.

[0039] An initial knowledge graph is constructed based on the entities in the river and lake chief system database and the relationships between them.

[0040] Based on the preset business rules of the river and lake chief system and knowledge in the water affairs field, the initial knowledge graph is completed to obtain the knowledge graph of the river and lake chief system database model.

[0041] In practical applications, the river and lake chief system database is parsed to obtain the table structure, field attributes, and annotation information of each table. Entity relation extraction is then performed on the table structure, field attributes, and annotation information to obtain the entities and relationships between them. An initial knowledge graph is constructed based on these entities and their relationships. Specifically, a large language model is used to parse the table structure, field attributes, and annotation information of the river and lake chief system database. Entity recognition is then performed to identify table entities, field entities, and field attributes. Relationship extraction is then performed to extract primary key relationships, foreign key relationships, and field affiliation relationships, ultimately generating a set of triples G=(V, E), where V represents a node and E represents the relationship between two nodes. Finally, the Neo4j graph database is used to store this set of triples as the initial knowledge graph, which is a sparse knowledge graph.

[0042] In practical applications, the initial knowledge graph is completed based on the preset river and lake chief system business rules and water resources knowledge to obtain the river and lake chief system database schema knowledge graph. Specifically, to address common issues in databases such as missing implicit foreign keys and ambiguous cross-table relationship definitions, the preset river and lake chief system business rules and water resources knowledge are introduced to complete the relationships on the basis of the initial knowledge graph. Missing edges (such as undefined foreign key relationships) are added, and an extended triple set is generated. Finally, a river and lake chief system database schema knowledge graph containing all related information is formed. A dense knowledge graph is formed by performing implicit relationship completion.

[0043] Taking the river and lake chief system's patrol and management operations as an example, entity identification and relation extraction are performed on the patrol record table (patrol_record), the patrol problem table (patrol_problem), the river chief information table (chief_info), the administrative division table (region), and the river table (river). Relevant business rules for the river and lake chief system are then added to generate a database schema knowledge graph for the river and lake chief system's patrol and management operations, such as... Figure 2 As shown.

[0044] In another exemplary embodiment of this application, the natural language query text input by the user is parsed to obtain a source field set and a target field set. Specifically, this includes inputting a first preset prompt word, the natural language query text input by the user, and the names and fields of each table in the river and lake chief system database into a large language model to obtain the source field set and the target field set. The large language model is used to parse the natural language query text input by the user, taking the natural language query text input by the user and the names and fields of each table in the database as input. A special prompt word (first preset prompt word) instruction that integrates commonly used query features of the river and lake chief system (such as province, problem type, specific watershed) is designed to explicitly require the large language model to identify the source field set. S ={ s 1, s 2,…, s m} and target field set T= { t 1 ,t 2 ,…,t n}, where source fields refer to the fields involved in the filtering conditions of the query, target fields refer to the fields involved in the final query results, and the final output is a structured collection. S and T , s m This represents the m-th source field. t n This represents the nth target field.

[0045] In another exemplary embodiment of this application, determining the corresponding nodes in the river and lake chief system database schema knowledge graph for each field in the source field set and the target field set, thus obtaining the source node set and the target node set, specifically includes: using a Cypher query statement to query the nodes in the river and lake chief system database schema knowledge graph that correspond to each field in the source field set and the target field set, thereby obtaining the source node set and the target node set. The fields in the source field set S and the target field set T are then mapped to corresponding field nodes in the knowledge graph using a Cypher query statement.

[0046] In another exemplary embodiment of this application, the shortest path algorithm is the A* algorithm. Using a set of nodes, the A* algorithm is employed to calculate the shortest effective association path between the source field node and the target field node, generating a path set. P ={ path 1 , path 2 ,..., path k Each path represents the shortest association link from a node in the source node set to a node in the target node set. pathk This represents the k-th path.

[0047] In practical applications, the attributes of each node and target field node corresponding to the path, as well as the relationships between each target table node, are extracted to obtain the pattern information corresponding to the path. Specifically, based on the path set P, the pattern information required by the SQL is accurately extracted. The table information and field information in the pattern information are obtained by traversing each path in the path set and extracting all field nodes and table nodes passed by the path. The field attributes and relationships in the pattern information are obtained by querying the knowledge graph through Cypher. Finally, the extracted information is organized into structured database pattern information, which serves as the direct input for the next step of SQL generation.

[0048] In another exemplary embodiment of this application, a large language model and mind chain technology are used to decompose the natural language query text input by the user into multiple sub-questions based on the pattern information corresponding to each path and the preset river and lake chief system business rules, and to obtain the dependency relationship between each sub-question. Specifically, the first prompt word is obtained by using mind chain technology.

[0049] The first prompt word, the user-input natural language query text, the pattern information corresponding to each path, and the preset river and lake chief system business rules are input into the large language model to obtain the sub-problems and the dependencies between them. This step analyzes the semantics of the user query. The user-input natural language query text, database pattern information (pattern information corresponding to each path), and preset river and lake chief system business rules are used as input to the large language model. Combining the Chain-of-Thought (CoT) reasoning mechanism, the first prompt word is designed to guide the large language model to analyze the final goal of the query step by step, the required intermediate steps and data, and the logical dependencies between the intermediate steps. In this way, the complex query is decomposed into multiple sub-problems, each sub-problem corresponding to a data processing step, and arranged in order according to the dependencies to obtain a sequence of sub-problems. The Chain-of-Thought is a prompting technology that guides the large language model to analyze and solve problems step by step. By allowing the model to display the intermediate thinking steps of reasoning, it improves its logical reasoning ability in complex tasks and the accuracy and interpretability of the output results.

[0050] In another exemplary embodiment of this application, a common table expression for each subproblem is obtained based on the dependencies between subproblems and the target field set. Specifically, this includes: for any subproblem, constructing the FROM clause in the common table expression of the subproblem based on the dependencies between the subproblem and other subproblems; the other subproblems are all subproblems other than the subproblem mentioned above.

[0051] The SELECT clause in the public table expression of the subproblem is constructed based on the target set of fields.

[0052] The WHERE clause in the public table expression of the sub-problem is generated according to the preset business logic.

[0053] The GROUP BY clause and HAVING clause in the common table expression of the subproblem are obtained according to the type of the subproblem; the type is statistical or filtering.

[0054] The common table expression for the subproblem is obtained based on the FROM clause, SELECT clause, WHERE clause, GROUP BY clause, and HAVING clause in the common table expression of the subproblem.

[0055] In practical applications, for each sub-problem, a corresponding CTE module, i.e., a common table expression, is constructed. Each CTE module adopts the standard SQL CTE syntax structure, such as... Figure 5 As shown. First, based on the dependencies of the sub-problems, determine the tables or parent CTEs in the FROM clause; then, based on the target field set, determine the field list in the SELECT clause; next, generate the filtering conditions in the WHERE clause according to the preset business logic; for sub-problems that need to be statistically analyzed or filtered, generate the corresponding GROUP BY and HAVING clauses.

[0056] In practical applications, the SQL statement for the user's natural language query text is obtained based on the dependencies between subproblems and the common table expressions of each subproblem. Specifically, based on dependencies and each CTE module, and adhering to principles such as the requirement that a referenced CTE must be declared before the CTE that references it, and standard SQL WITH syntax, all CTE modules are reorganized into a unified SQL query statement. The result is as follows: Figure 7 As shown.

[0057] This application addresses the need for complex business queries in the river and lake chief system application, disclosing a progressive Text-to-SQL generation method based on a knowledge graph of the river and lake chief system database schema. First, a knowledge graph of the river and lake chief system business database schema is constructed. Second, steps are performed to achieve database schema association using graph computation. Utilizing the knowledge graph and combining it with a graph path algorithm, database schema information closely related to the user's query is accurately located. The specific process is as follows: Figure 3 As shown, firstly, semantic parsing is performed on the user query (natural language query text) to obtain the source field set and target field set. Then, node mapping is performed to obtain the node set. Next, the graph shortest path algorithm is used to obtain the path set. Then, information extraction, structured encapsulation, and pattern association are performed. Finally, a progressive SQL generation mechanism is used to generate SQL statements. The specific process is as follows: Figure 4As shown, firstly, multiple sub-problems are obtained based on the complex query (natural language query text input by the user), business logic, and pattern information. Then, multiple common table expressions are obtained based on the data source, processing logic, and output pattern of each sub-problem. Finally, the SQL statement is obtained.

[0058] This application is based on the knowledge graph of the business database model of the river and lake chief system, and introduces a progressive SQL generation mechanism. That is, using a large language model, under the guidance of the COT step-by-step reasoning mechanism, the CTE technology is used to modularly generate SQL statements, realizing end-to-end generation from natural language query to database query statement.

[0059] This application utilizes graph computing to achieve database schema association steps, supporting graph-driven schema association parsing technology that accurately maps natural language representations to target database schemas. This effectively improves the generalization ability and robustness of large language models in data-sparse scenarios; the progressive SQL generation mechanism can improve the generation accuracy of complex nested queries.

[0060] This application uses the example of "a river chief in a certain city discovering illegal sand mining patrol records and the personnel handling the situation" to illustrate the Text-to-SQL generation method proposed in this application. Specifically, it includes two steps: 1) Parsing using a large language model to identify the source field set, which includes: administrative division code, river chief's name, river chief ID (river chief table), patrol record ID (patrol record table), problem type, whether it has been handled (patrol problem table), and administrative division name (administrative division table). The target field set includes: river chief's name (river chief table); patrol time (patrol record table), problem type, whether it has been handled, and the personnel handling the situation (patrol problem table). Then, based on these determined source and target field sets, a graph path calculation algorithm is applied to obtain the shortest path set, such as... Figure 6 As shown, Figure 6 Part (a) represents the path where the target field is the river chief's name and the source field is the administrative division name. Figure 6 Part (b) represents the path where the target field is the patrol time and the source field is the river chief's name. Figure 6 Part (c) indicates a path where the target field is the question type and the source field is the river chief's ID. Based on the source field, target field, and path, the tables required for the SQL are extracted as the river chief table, patrol record table, patrol question table, and administrative division table. 2) A progressive SQL generation mechanism is adopted to decompose and generate sub-questions, including: Who are the river chiefs in a certain city? What are the problems of illegal sand mining? Under specified conditions, who are the patrol record and the personnel responsible for handling the situation? Corresponding sub-SQLs are generated and concatenated to form the final SQL statement, such as... Figure 7 As shown.

[0061] Schema association is a core challenge for the practical application of Text-to-SQL. Existing few-shot context learning methods directly inject samples into large models through context learning. When there is a lot of related table information in the database schema or it changes dynamically, it is easy to encounter problems such as context window overload, loss of key information, model attention diversion, and strong dependence on samples, which are inflexible. Vector similarity matching methods rely solely on the semantic vector similarity between natural language questions and database schema names (such as table names and column names) for association. This often introduces the semantic drift problem, that is, it only focuses on the surface word similarity and ignores the deep business logic association. This application fully leverages the crucial role of knowledge within the river and lake chief system's operational domain, proposing a pattern association method based on a knowledge graph of the river chief system's database patterns. Specifically, this application establishes a dense knowledge graph of the river and lake chief system's database patterns and utilizes graph computation to achieve database pattern association. This provides large language models with key database-related information, such as table association paths, field constraint rules, and hierarchical semantic information of data entities. This lays a solid foundation for the subsequent generation of reliable SQL statements. Compared to existing mainstream techniques based on "few-shot context learning" or "vector similarity matching," this application represents a technological shift from uncontrollable and black-box approaches to highly reliable, highly interpretable, and traceable methods.

[0062] Existing SQL generation technologies rely on the end-to-end generation capabilities of a single large language model. When faced with queries involving multi-level nesting, multi-table joins, or complex aggregations, they are prone to multi-step reasoning biases, leading to subquery logic errors, missing join conditions, or improper aggregation applications. This results in semantically confused SQL statements and results that deviate from expectations. This application proposes a progressive SQL generation mechanism that combines thought chains and common table expressions. It deeply integrates thought chain reasoning guidance to effectively avoid multi-step reasoning biases. By introducing the modular encapsulation capabilities of common table expressions, it significantly improves the semantic accuracy and structural debuggability of complex queries. This ensures higher reliability and execution efficiency in the generation of Text-to-SQL tasks, ultimately generating efficient and robust SQL query statements and reducing reliance on large-scale sample data.

[0063] To comprehensively evaluate the performance of the Text-to-SQL generation method provided in this application, historical query problems from a provincial river and lake chief system business platform were used as the experimental dataset. After rigorous screening and manual annotation, 500 natural language query and database query language pairs were collected. Based on the logical complexity, structural characteristics, and spatial query specificity of the queries, the dataset was divided into three types: 1) Simple problems, used to test the model's ability to understand and generate simple semantics for basic SQL syntax and single-table queries; 2) Complex problems, aimed at evaluating the model's robustness and accuracy in handling multi-step logic, highly complex, and non-intuitive user intents; and 3) Spatial query problems, used to verify the model's ability to handle queries specific to the river and lake chief system application that involve geospatial attributes (such as location, distance, and regional inclusion relationships). Detailed statistical information of the experimental dataset is shown in Table 1.

[0064] Table 1. Experimental Dataset Information

[0065]

[0066] To verify the effectiveness of this application, Valid SQL (VA), Execution Accuracy (EX), and Exact Set Match Accuracy (EM) are introduced as quantitative evaluation indicators of the results.

[0067] .

[0068] VA measures the percentage of generated SQL statements that are syntactically correct. In the formula, x represents the number of syntactically correct SQL statements, and m represents the total number of problems.

[0069] .

[0070] EX measures the accuracy of the generated SQL statements in matching the results of the standard answer after execution. In the formula, y represents the number of SQL statements that produce correct results, and m represents the total number of questions.

[0071] .

[0072] EM measures the accuracy of strict matching where the generated SQL statement is completely identical to the standard answer in both syntax and semantics. In the formula, z represents the number of generated SQL statements that produce identical results to the standard SQL, and m represents the total number of questions.

[0073] The open-source framework Vanna.AI was selected as the baseline model for comparative experiments. Qwen2.5-72B was used as the benchmark large language model and PostgreSQL as the benchmark database. The methods provided in this application and Vanna.AI were used to process the aforementioned 500 natural language queries, and three core indicators—Validity (VA), Exercisability (EX), and Exact Match (EM)—were used for quantitative evaluation. Experimental details of this application and Vanna.AI are shown in Table 2.

[0074] Table 2 Comparison of Results Indicators

[0075]

[0076] The results show that this application demonstrates a significant advantage over the baseline model in the natural language to SQL conversion task, especially in handling complex scenarios such as spatial queries unique to the river and lake chief system application. In terms of validity rate, this application outperforms the Vanna.AI framework in simple problems (0.96 vs 0.90), complex problems (0.87 vs 0.80), and spatial query problems (0.72 vs 0.66), indicating that the SQL generated by this application has a lower syntax error rate. Regarding execution accuracy, the improvement in complex problems (0.88 vs 0.83) and spatial query problems (0.74 vs 0.54) is particularly significant, highlighting the application's outstanding ability to handle spatial semantics and generate executable spatial SQL. The application's integrity advantage is further validated by the higher accuracy of stricter exact set matching, demonstrating its ability to generate SQL highly consistent with expected standard SQL.

[0077] In one exemplary embodiment, a computer device is provided, which may be a server or a terminal, and its internal structure diagram may be as follows. Figure 8 As shown, this computer device includes a processor, memory, input / output interfaces (I / O), and a communication interface. The processor, memory, and I / O interfaces are connected via a system bus, and the communication interface is also connected to the system bus via the I / O interfaces. The processor provides computational and control capabilities. The memory includes non-volatile storage media and internal memory. The non-volatile storage media stores the operating system, computer programs, and a database. The internal memory provides the environment for the operating system and computer programs stored in the non-volatile storage media. The database stores Text-to-SQL generated data. The I / O interfaces are used for exchanging information between the processor and external devices. The communication interface is used for communicating with external terminals via a network connection. When the computer program is executed by the processor, it implements a Text-to-SQL generation method.

[0078] Those skilled in the art will understand that Figure 8 The structure shown is merely a block diagram of a portion of the structure related to the present application and does not constitute a limitation on the computer device to which the present application is applied. Specific computer devices may include more or fewer components than those shown in the figure, or combine certain components, or have different component arrangements.

[0079] In one exemplary embodiment, a computer device is provided, including a memory and a processor, wherein the memory stores a computer program, and the processor executes the computer program to implement the above-described method embodiments.

[0080] In one exemplary embodiment, a computer-readable storage medium is provided storing a computer program that, when executed by a processor, implements the above-described method embodiments.

[0081] In one exemplary embodiment, a computer program product is provided, including a computer program that, when executed by a processor, implements the above-described method embodiments.

[0082] It should be noted that the user information (including but not limited to user device information, user personal information, etc.) and data (including but not limited to data used for analysis, data stored, data displayed, etc.) involved in this application are all information and data authorized by the user or fully authorized by all parties, and the collection, use and processing of the relevant data must comply with relevant regulations.

[0083] Those skilled in the art will understand that all or part of the processes in the above embodiments can be implemented by a computer program instructing related hardware. The computer program can be stored in a non-volatile computer-readable storage medium. When executed, the computer program can include the processes of the embodiments described above. Any references to memory, databases, or other media used in the embodiments provided in this application can include at least one of non-volatile and volatile memory. Non-volatile memory can include read-only memory (ROM), magnetic tape, floppy disk, flash memory, optical memory, high-density embedded non-volatile memory, resistive random access memory (ReRAM), magnetic random access memory (MRAM), ferroelectric random access memory (FRAM), phase change memory (PCM), graphene memory, etc. Volatile memory can include random access memory (RAM) or external cache memory, etc. By way of illustration and not limitation, RAM can take many forms, such as Static Random Access Memory (SRAM) or Dynamic Random Access Memory (DRAM).

[0084] The databases involved in the embodiments provided in this application may include at least one type of relational database and non-relational database. Non-relational databases may include, but are not limited to, blockchain-based distributed databases. The processors involved in the embodiments provided in this application may be general-purpose processors, central processing units, graphics processing units, digital signal processors, programmable logic devices, quantum computing-based data processing logic devices, etc., and are not limited to these.

[0085] The technical features of the above embodiments can be combined in any way. For the sake of brevity, not all possible combinations of the technical features in the above embodiments are described. However, as long as there is no contradiction in the combination of these technical features, they should be considered to be within the scope of this specification.

[0086] This document uses specific examples to illustrate the principles and implementation methods of this application. The descriptions of the above embodiments are only for the purpose of helping to understand the methods and core ideas of this application. Furthermore, those skilled in the art will recognize that, based on the ideas of this application, there will be changes in the specific implementation methods and application scope. Therefore, the content of this specification should not be construed as a limitation of this application.

Claims

1. A Text-to-SQL generation method, characterized by, The Text-to-SQL generation method comprises: obtaining a natural language query text input by a user; parsing the natural language query text input by the user to obtain a source field set and a target field set; the source field set comprises fields involved in a filtering condition when the natural language query text is queried; the target field set comprises fields involved in a query result when the natural language query text is queried; determining nodes corresponding to each field in the source field set and the target field set in a river and lake regulation database schema knowledge graph to obtain a source node set and a target node set; the river and lake regulation database schema knowledge graph is constructed based on a river and lake regulation database; nodes in the river and lake regulation database schema knowledge graph comprise table nodes and field nodes, the table nodes are names of tables in the river and lake regulation database, and the field nodes are fields in the tables; taking a node in the source node set as a path starting point and taking a node in the target node set as a path ending point, processing the river and lake regulation database schema knowledge graph by using a shortest path algorithm to obtain a path set; for any path in the path set, extracting attributes of each node, each target field node, and an association relationship between each target table node corresponding to the path to obtain schema information corresponding to the path; the target field node is a field node corresponding to the path; and the target table node is a table node corresponding to the path; using a large language model and a thinking chain technology to decompose the natural language query text input by the user into multiple sub-questions and obtain a dependency relationship between the sub-questions according to the schema information corresponding to each path and a preset river and lake regulation business rule; obtaining a common table expression of each sub-question according to the dependency relationship between the sub-questions and the target field set; obtaining an SQL statement of the natural language query text input by the user according to the dependency relationship between the sub-questions and the common table expression of each sub-question. 2.The Text-to-SQL generation method of claim 1, wherein, The construction process of the river and lake regulation database schema knowledge graph comprises: parsing the river and lake regulation database to obtain table structures of each table in the river and lake regulation database, attributes of each field in each table, and annotation information in each table; performing entity relationship extraction on the table structures of each table in the river and lake regulation database, the attributes of each field in each table, and the annotation information in each table to obtain entities in the river and lake regulation database and relationships between the entities; constructing an initial knowledge graph according to the entities in the river and lake regulation database and the relationships between the entities; performing a completion operation on the initial knowledge graph according to a preset river and lake regulation business rule and water field knowledge to obtain the river and lake regulation database schema knowledge graph. 3.The Text-to-SQL generation method of claim 1, wherein, The parsing of the natural language query text input by the user to obtain the source field set and the target field set comprises: inputting a first preset prompt word, the natural language query text input by the user, and names of each table in the river and lake regulation database and fields in each table into a large language model to obtain the source field set and the target field set. 4.The Text-to-SQL generation method of claim 1, wherein, The determination of nodes corresponding to each field in the source field set and the target field set in the river and lake regulation database schema knowledge graph to obtain the source node set and the target node set comprises: The Cypher query statement is used to query nodes corresponding to each field in the source field set and the target field set in the river and lake long system database mode knowledge graph, to obtain a source node set and a target node set. 5.The Text-to-SQL generation method of claim 1, wherein, The large language model and the thinking chain technology are used to decompose the natural language query text input by the user into multiple sub-questions and obtain the dependency relationship between the sub-questions according to the mode information corresponding to each path and the preset river and lake long system business rules, specifically including: The first prompt word is obtained by using the thinking chain technology. The first prompt word, the natural language query text input by the user, the mode information corresponding to each path, and the preset river and lake long system business rules are input into the large language model to obtain each sub-question and the dependency relationship between the sub-questions. 6.The Text-to-SQL generation method of claim 1, wherein, The common table expression of each sub-question is obtained according to the dependency relationship between the sub-questions and the target field set, specifically including: For any one sub-question, the FROM clause in the common table expression of the sub-question is constructed according to the dependency relationship between the sub-question and other sub-questions; the other sub-questions are all sub-questions except the sub-question; The SELECT clause in the common table expression of the sub-question is constructed according to the target field set; The WHERE clause in the common table expression of the sub-question is generated according to the preset business logic; The GROUP BY clause and the HAVING clause in the common table expression of the sub-question are obtained according to the type of the sub-question; The common table expression of the sub-question is obtained based on the FROM clause, the SELECT clause, the WHERE clause, the GROUP BY clause, and the HAVING clause in the common table expression of the sub-question.

7. The Text-to-SQL generation method of claim 1, wherein, The shortest path algorithm is the A* algorithm.

8. A computer device comprising: A memory, a processor, and a computer program stored on the memory and executable on the processor, characterized in that the processor executes the computer program to implement the Text-to-SQL generation method of any one of claims 1-7.

9. A computer-readable storage medium having stored thereon a computer program, characterized in that The computer program is executed by the processor to implement the Text-to-SQL generation method of any one of claims 1-7.

10. A computer program product comprising a computer program, characterized in that, The computer program is executed by the processor to implement the Text-to-SQL generation method of any one of claims 1-7.

Images