Text-to-SQL (Structured Query Language) generation method and equipment, medium and product

Abstract

The invention discloses a Text-to-SQL (Structured Query Language) generation method and device, a medium and a product, and relates to the field of geographic information technology application, the method 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 corresponding nodes of each field in the source field set and the target field set in the river-lake long-system database mode knowledge graph to obtain a source node set and a target node set; the nodes in the source node set serve as starting points, the nodes in the target node set serve as terminal points, the knowledge graph is processed through the shortest path algorithm, and a path set is obtained; decomposing the natural language query text into a plurality of sub-problems by adopting a large language model and a thinking chain technology, and obtaining a dependency relationship among the sub-problems; according to the dependency relationship and the target field set, a public table expression is obtained, and then the SQL statement is obtained, and the accuracy of database query language generation under the river and lake long-term system scene can be improved.

Description

TECHNICAL FIELD

[0001] The present 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

[0002] With the deepening of the river and lake long system, related information systems have accumulated a large amount of structured business data covering water quality monitoring, pollution source control, river patrol, and event disposal. However, existing information platforms generally use preset query templates and fixed report output modes, which have rigid query conditions and weak customization capabilities. In the face of different levels of river and lake longs and differentiated query requirements of management departments, especially in complex application scenarios involving cross-table association and multi-dimensional combination analysis, the traditional menu-driven query interface exposes the defects of low interaction efficiency and insufficient decision support capability, making it difficult to support fine management decisions and limiting the improvement of river and lake management intelligence.

[0003] Text-to-SQL technology is an intelligent technology that converts user natural language queries (Text) into executable database query language (SQL). It aims to reduce the threshold of database interaction and allow users to access the required data through natural language without deep understanding of SQL syntax. Text-to-SQL technology converts natural language problems into database query language, providing an effective way to solve the above problems. Currently, this technology has made some progress in open general fields (such as WikiSQL, Spider) and industrial, financial, medical, and other vertical fields, but it still faces significant challenges when applied to river and lake long systems, which have domain-specific business scenarios. On the one hand, the river and lake management data model usually contains dozens of highly coupled business tables, and existing methods are not accurate when generating SQL statements involving multi-table joins and hierarchical associations. On the other hand, the river and lake long system professional terms (such as "inferior V class water", "black and odorous water body", "shoreline encroachment") and spatial location relationship descriptions (such as "500 meters downstream", "areas flowing through") in natural language queries have significant semantic gaps with the database physical model, making entity alignment and relationship mapping difficult. SUMMARY

[0004] The purpose of the present 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 river and lake long system scenario.

[0005] To achieve the above purpose, the present application provides the following solutions: In a first aspect, the present application provides a Text-to-SQL generation method, comprising: obtaining a natural language query text input by a user.

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

[0007] Nodes corresponding to each field in the source field set and the target field set in a river-lake management database schema knowledge graph are determined to obtain a source node set and a target node set; the river-lake management database schema knowledge graph is constructed based on a river-lake management database; and the nodes in the river-lake management database schema knowledge graph include table nodes and field nodes, the table nodes are names of tables in the river-lake management database, and the field nodes are fields in the tables.

[0008] Nodes in the source node set are taken as starting points of paths, and nodes in the target node set are taken as ending points of the paths, and a shortest path algorithm is used to process the river-lake management database schema knowledge graph to obtain a path set.

[0009] For any path in the path set, attributes of each node, each target field node, and an association relationship between each target table node corresponding to the path are extracted 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.

[0010] A large language model and a thinking chain technology are used 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-lake management business rule.

[0011] A common table expression of each sub-question is obtained according to the dependency relationship between the sub-questions and the target field set.

[0012] A SQL statement of the natural language query text input by the user is obtained according to the dependency relationship between the sub-questions and the common table expression of each sub-question.

[0013] In a second aspect, a computer device is provided, which includes a memory, a processor, and a computer program stored in the memory and executable on the processor, and the processor executes the computer program to implement the Text-to-SQL generation method in any of the preceding aspects.

[0014] In a third aspect, a computer readable storage medium is provided, which stores a computer program, and the computer program is executed by a processor to implement the Text-to-SQL generation method in any of the preceding aspects.

[0015] In a fourth aspect, the present application provides a computer program product comprising a computer program which, when executed by a processor, implements the Text-to-SQL generation method of any of the preceding aspects.

[0016] According to the specific embodiments provided in the present application, the present application has the following technical effects: the present application provides a Text-to-SQL generation method, device, medium and product, the present application generates SQL statements by using the river-lake long system database mode knowledge graph and a large language model under the guidance of a thinking chain step-by-step reasoning mechanism, and adopts a common table expression technology to realize end-to-end generation from a natural language query to a database query statement, effectively solves the multi-step reasoning deviation problem, significantly improves the semantic accuracy of complex queries, effectively improves the SQL generation accuracy of natural language queries in the river-lake long system scenario, and can provide a natural language interactive data query core engine for a river-lake long system information system to realize flexible and intelligent natural language interactive data query. BRIEF DESCRIPTION OF DRAWINGS

[0017] In order to more clearly illustrate the technical solutions in the embodiments of the present application or the prior art, the drawings needed in the embodiments will be briefly introduced as follows. Obviously, the drawings in the following description are only some embodiments of the present application, and other drawings can be obtained by those skilled in the art without creative labor on the basis of these drawings.

[0018] Figure 1 A flowchart of a Text-to-SQL generation method provided by an embodiment of the present application.

[0019] Figure 2 A river-lake long system river patrol management business database mode knowledge graph provided by an embodiment of the present application.

[0020] Figure 3 A database mode association flowchart realized by using a graph.

[0021] Figure 4 A progressive SQL generation mechanism flowchart.

[0022] Figure 5 A common table expression schematic diagram.

[0023] Figure 6 A shortest path schematic diagram.

[0024] Figure 7 A SQL statement schematic diagram of a preset river-lake long system business complex query.

[0025] Figure 8 A structural schematic diagram of a computer device provided by an embodiment of the present 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, each target field node, and the association relationship between each target table node corresponding to the path 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 association relationship is the primary-foreign key association relationship and the business logic association relationship.

[0034] Step 106: Using a large language model and a thinking chain technology, the natural language query text input by the user is decomposed into multiple sub-questions and the dependency relationship between the sub-questions is obtained according to the schema information corresponding to each path and the preset river and lake long-term business rules.

[0035] Step 107: According to the dependency relationship between each sub-question and the target field set, a common table expression of each sub-question is obtained.

[0036] Step 108: According to the dependency relationship between each sub-question and the common table expression (CTE) of each sub-question, a SQL statement of the natural language query text input by the user is obtained. The common table expression is a feature in the SQL language, which creates a temporary result set (named query result) using the WITH clause. This result set can be referenced multiple times by subsequent SQL statements in the same query. CTE can effectively improve the logical clarity and maintainability of complex queries, and is particularly suitable for step-by-step construction of nested queries.

[0037] The database schema refers to the logical structure definition of the database, including tables, fields, data types, primary keys, and foreign keys, and their mutual relationships, which are used to describe the organization method, storage format, and constraint conditions of data. In another exemplary embodiment of the present application, the construction process of the river and lake long-term database schema knowledge graph specifically includes: parsing the river and lake long-term database to obtain the table structure of each table in the river and lake long-term database, the attributes of each field in each table, and the annotation information in each table.

[0038] The entity relationship extraction is performed on the table structure of each table in the river and lake long-term database, the attributes of each field in each table, and the annotation information in each table to obtain the entities in the river and lake long-term database and the relationships between the entities.

[0039] The initial knowledge graph is constructed according to the entities in the river and lake long-term database and the relationships between the entities.

[0040] The initial knowledge graph is completed according to the preset river and lake long-term business rules and the water field knowledge to obtain the river and lake long-term database schema knowledge graph.

[0041] In practical applications, the river and lake long system database is parsed to obtain the table structure of each table in the river and lake long system database, the attributes of each field in each table, and the annotation information in each table. The table structure of each table in the river and lake long system database, the attributes of each field in each table, and the annotation information in each table are subjected to entity relationship extraction to obtain entities in the river and lake long system database and the relationships between the entities; an initial knowledge graph is constructed according to the entities in the river and lake long system database and the relationships between the entities, specifically: the database table structure, field attributes and annotation information in the river and lake long system database are parsed by using a large language model, then entity recognition is performed to recognize table entities, field entities and field attributes, relationship extraction is performed to extract primary key relationships, foreign key relationships and field attribution relationships and the like, and finally a triple set G=(V, E) is generated, wherein V represents a node and E represents a relationship between two nodes; the triple set is stored as an initial knowledge graph by using a Neo4j graph database, and the initial knowledge graph is a sparse knowledge graph.

[0042] In practical applications, the initial knowledge graph is subjected to a completion operation according to preset river and lake long system business rules and water field knowledge to obtain a river and lake long system database mode knowledge graph, specifically: in view of problems such as the lack of implicit foreign keys, the fuzzy definition of cross-table association and the like in the database, on the basis of the initial knowledge graph, preset river and lake long system business rules and water field knowledge are introduced to complete the relationships, missing edges (such as undefined foreign key relationships) are added, an extended triple set is generated, and finally a river and lake long system database mode knowledge graph containing all associated information is formed, and a dense knowledge graph is formed by performing implicit relationship completion.

[0043] Taking the river and lake long system patrol business as an example, entity recognition and relationship 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, and river and lake long system related business rules are supplemented to generate a river and lake long system patrol business database mode knowledge graph as shown in Figure 2 .

[0044] In another example embodiment of the present application, the natural language query text input by the user is parsed to obtain a source field set and a target field set, specifically including: inputting the first preset prompt word, the natural language query text input by the user, and the names of each table in the river and lake management database and the fields in each table into a large language model to obtain the source field set and the target field set. The natural language query text input by the user is parsed using a large language model, the natural language query text input by the user and the names of each table and the fields in the database are taken as input, and a special prompt word (the first preset prompt word) instruction that fuses common query features of the river and lake management (such as the whole province, the type of question, and a specific river basin) is designed to clearly require the large language model to identify the source field set S ={ s 1, s 2,…, s m}and the target field set T= { t 1 ,t 2 ,…,t n}wherein the source field refers to a field involved in the filtering condition in the query, and the target field refers to a field involved in the final returned result of the query, and the final output is a structured set S and T , s m represents the mth source field, t n represents the nth target field.

[0045] In another example embodiment of the present application, the nodes corresponding to each field in the source field set and the target field set in the river and lake management database schema knowledge graph are determined to obtain a source node set and a target node set, specifically including: using a Cypher query statement to query the nodes corresponding to each field in the source field set and the target field set in the river and lake management database schema knowledge graph to obtain the source node set and the target node set. The fields in the source field set S and the target field set T are mapped to the corresponding field nodes in the knowledge graph through the Cypher query statement.

[0046] In another example embodiment of the present application, the shortest path algorithm is the A* algorithm. Using the node set, the A* algorithm is used to calculate the shortest effective association path between the source field nodes and the target field nodes to generate a path set P ={ path 1 , path 2 ,..., path k}wherein each path represents the shortest association link from the node in the source node set to the 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] generating the WHERE clause in the common table expression of the sub-problem according to the preset business logic.

[0053] obtaining the GROUP BY clause and the HAVING clause in the common table expression of the sub-problem according to the type of the sub-problem; the type is statistics or screening.

[0054] obtaining the common table expression of the sub-problem 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-problem.

[0055] In practical application, for each sub-problem, a corresponding CTE module, that is, a common table expression, is constructed, and each CTE module adopts a standard SQL CTE syntax structure, as shown in Figure 5 Firstly, the table or the upper CTE in the FROM clause is determined according to the dependency relationship of the sub-problem; then the field list in the SELECT clause is determined based on the target field set; then the screening condition of the WHERE clause is generated according to the preset business logic; and for the sub-problem that needs to be counted or screened, the corresponding GROUP BY and HAVING clauses are generated.

[0056] In practical application, the SQL statement of the natural language query text input by the user is obtained according to the dependency relationship between the sub-problems and the common table expression of each sub-problem, specifically: based on the dependency relationship and each CTE module, all CTE modules are reorganized into a unified SQL query statement in accordance with the principle that the referenced CTE must be declared before the CTE that references it and the standard SQL WITH syntax, and the result is as shown in Figure 7 .

[0057] To meet the needs of complex business queries of the river-lake long system application, the application discloses a progressive Text-to-SQL generation method based on a river-lake long system database mode knowledge graph, first constructs a river-lake long system business database mode knowledge graph, and then executes a database mode association step calculated by using the graph, accurately locates the database mode information closely related to the user query by using the knowledge graph and combining a graph path algorithm, and the specific process is as shown in Figure 3 Firstly, the source field set and the target field set are obtained by performing semantic analysis on the user query (natural language query text), then the node set is obtained by performing node mapping, then the path set is obtained by using a graph shortest path algorithm, and then the information extraction, structured encapsulation and mode association result steps are performed; secondly, a progressive SQL generation mechanism is used to generate a SOL statement, and the specific process is as shown in Figure 4As shown, first, a plurality of sub-questions are obtained according to a complex query (a natural language query text input by a user), business logic and schema information, then a plurality of common table expressions are obtained according to data sources, processing logic and output modes of each sub-question, and finally, a SQL statement is obtained.

[0058] The application is based on a river and lake long system business database schema knowledge graph, and introduces a progressive SQL generation mechanism, that is, a large language model is used to generate SQL statements in a COT step-by-step reasoning mechanism guided manner by using a CTE technology modularization, so that end-to-end generation from a natural language query to a database query statement is realized.

[0059] The application realizes database schema association by using graph computing, supports a graph-driven schema association analysis technology for accurately mapping a natural language expression to a target database schema, and can effectively improve the generalization ability and robustness of a large language model in a sparse data scene; the progressive SQL generation mechanism can improve the generation accuracy of complex nested queries.

[0060] The application takes a river patrol record and a disposal personnel found by a river officer in a certain city as an example to illustrate the Text-to-SQL generation method, which includes two steps: 1) a large language model is used for analysis to identify a source field set including an administrative division code, a river officer name, a river officer id (a river officer table), a river patrol record id (a river patrol record table), a problem type, whether to handle (a river patrol problem table) and an administrative division name (an administrative division table). A target field set includes a river officer name (a river officer table), a river patrol time (a river patrol record table), a problem type, whether to handle and a handling personnel (a river patrol problem table). Then, based on the determined source field set and target field set, a graph path calculation algorithm is applied to obtain a shortest path set, as shown in Figure 6 , Figure 6 Part (a) of the figure shows a path in which a target field is a river officer name and a source field is an administrative division name, Figure 6 Part (b) of the figure shows a path in which a target field is a river patrol time and a source field is a river officer name, Figure 6 Part (c) of the figure shows a path in which a target field is a problem type and a source field is a river officer id. According to the source field, the target field and the path, a SQL required table is extracted as a river officer table, a river patrol record table, a river patrol problem table and an administrative division table. 2) A progressive SQL generation mechanism is used to decompose and generate sub-questions including which river officers are there in a certain city? What are the problems of illegal sand mining? Who is the river patrol record and the disposal personnel under the specified conditions? Corresponding sub-SQL is generated and spliced to form a final SQL statement, as shown in Figure 7 .

[0061] Pattern association is the core challenge of Text-to-SQL application landing. The existing few-shot context learning method directly injects samples into the large model in the way of context learning. In the case of a large number of related table information in the database schema or dynamic changes, context window overload, key information loss, model attention dispersion, and sample strong dependence and inflexibility problems are prone to occur. The vector similarity matching method simply relies on the semantic vector similarity of natural language questions and database schema names (such as table names and column names) for association, which often introduces semantic drift problems, that is, only focusing on surface lexical similarity and ignoring deep business logic association. The present application fully plays the important role of river and lake long system business field knowledge and proposes a pattern association method based on river and lake long system database schema knowledge graph, that is, the present application can provide key table association path, field constraint rule, and hierarchical semantic information of data entities and other database related information for large language models by establishing the steps of establishing a dense knowledge graph of river and lake long system database schema and the steps of realizing database schema association by using graph computation, which lays a solid foundation for the generation of reliable SQL statements. Compared with the existing mainstream "few-shot context learning" or "vector similarity matching" technology, it realizes the technical transformation from uncontrollable and black box to high credibility, high degree of explainability, and traceability.

[0062] The existing SQL generation technology relies on the end-to-end generation ability of a single large language model. When facing query requirements involving multi-layer nesting, multi-table connection or complex aggregation, it is easy to produce multi-step reasoning deviation, resulting in subquery logic error, missing connection condition or improper aggregation application, and the generated SQL statement has chaotic semantics and the result deviates from the expectation. The present application proposes a gradual SQL generation mechanism combining thought chain and public table expression, deeply integrates thought chain reasoning guidance, effectively avoids multi-step reasoning deviation, introduces the modularization encapsulation ability of public table expression, significantly improves the semantic accuracy and structural debuggability of complex queries, thereby ensuring that the Text-to-SQL task generation has higher reliability and execution efficiency, finally generates efficient and highly robust SQL query statements, and reduces the dependence on large-scale example data.

[0063] To comprehensively evaluate the performance of the Text-to-SQL generation method provided in the present application, the historical query problems of a river and lake length system business platform in a province are taken as the experimental data set, and after strict screening and manual annotation, 500 natural language queries and database query language pairs are collected. According to the logical complexity, structural features and speciality of spatial queries of the queries, the data set is divided into three types: 1) simple problems, used to test the simple semantic understanding and generation ability of the model for basic SQL syntax and single table queries, 2) complex problems, aiming to evaluate the robustness and accuracy of the model in handling multi-step logic, high complexity and non-intuitive user intent, 3) spatial query problems, used to verify the ability of the model to handle specific queries involving geographic spatial properties (such as location, distance, regional inclusion relationship, etc.) in the river and lake length system application. The detailed statistical information of the experimental data set is shown in Table 1.

[0064] Table 1 Experimental data set situation table

[0065] In order to test the effect of the present application, valid SQL (Valid SQL, VA), execution accuracy (Execution Accuracy, EX) and exact set match accuracy (Exact Set Match Accuracy, EM) are introduced as quantitative evaluation indexes of the results.

[0066] .

[0067] VA measures the proportion of SQL statements generated correctly in syntax. In the formula, x represents the number of SQL statements with correct syntax, and m represents the total number of problems.

[0068] .

[0069] EX measures the accuracy rate of the results of the generated SQL statements after execution consistent with the execution results of the standard answer. In the formula, y represents the number of SQL statements with correct execution results, and m represents the total number of problems.

[0070] .

[0071] EM measures the strict matching accuracy rate of the generated SQL statements in terms of both syntax form and semantic execution results consistent with the standard answer. In the formula, z represents the number of SQL statements with consistent execution results, and m represents the total number of problems.

[0072] An open-source framework Vanna.AI is selected as a baseline model for comparative experiments, Qwen2.5-72B is uniformly used as a benchmark large language model, and PostgreSQL is used as a benchmark database. The above 500 natural language queries are processed by the method provided in the application and Vanna.AI, and three core indexes of validity (VA), executability (EX) and exact match (EM) are used for quantitative evaluation. The experimental details of the application and Vanna.AI are shown in Table 2.

[0073] Table 2: Comparison of result indexes

[0074] The results show that in the natural language to SQL conversion task, the application exhibits significant advantages compared to the baseline model, especially in handling complex scenarios such as spatial queries specific to river and lake management applications. In terms of legality, the 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 syntax generated by the application has a lower error rate. In terms of execution accuracy, the application has made significant improvements in complex problems (0.88 vs 0.83) and spatial query problems (0.74 vs 0.54), highlighting the outstanding ability of the application in handling spatial semantics and generating executable spatial SQL. The accuracy of the exact set matching further verifies the integrity advantage of the application, demonstrating its ability to generate highly consistent SQL with the expected standard.

[0075] In an exemplary embodiment, a computer device, which can be a server or a terminal, is provided, and an internal structure diagram thereof can be as shown in Figure 8 The computer device includes a processor, a memory, an input / output interface (I / O), and a communication interface. The processor, the memory, and the input / output interface are connected through a system bus, and the communication interface is connected to the system bus through the input / output interface. The processor of the computer device is used to provide computing and control capabilities. The memory of the computer device includes a non-volatile storage medium and an internal memory. The non-volatile storage medium stores an operating system, a computer program, and a database. The internal memory provides an environment for the operation of the operating system and the computer program in the non-volatile storage medium. The database of the computer device is used to store Text-to-SQL generation data. The input / output interface of the computer device is used to exchange information between the processor and external devices. The communication interface of the computer device is used to communicate with external terminals through network connection. The computer program is executed by the processor to implement a Text-to-SQL generation method.

[0076] Those skilled in the art can understand that Figure 8 Those skilled in the art can understand that The specific computer device can include more or less components than those shown in the figure, or combine certain components, or have a different component arrangement.

[0077] In an exemplary embodiment, a computer device is provided, including a memory and a processor, the memory stores a computer program, and the processor executes the computer program to implement the above method embodiments.

[0078] In an exemplary embodiment, a computer readable storage medium is provided, which stores a computer program, and the computer program is executed by a processor to implement the above method embodiments.

[0079] In an exemplary embodiment, a computer program product is provided, which includes a computer program, and the computer program is executed by a processor to implement the above method embodiments.

[0080] 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 for analysis, stored data, displayed data, etc.) involved in the present application are all information and data authorized by the user or authorized by all parties, and the collection, use and processing of related data need to comply with relevant regulations.

[0081] Those skilled in the art can understand that all or part of the processes in the above-mentioned embodiment methods can be completed by instructing the relevant hardware through a computer program. The computer program can be stored in a non-volatile computer readable storage medium, and when the computer program is executed, the processes of the above-mentioned embodiments of the methods can be included. Any reference to a memory, a database or other medium used in the embodiments provided in the present application can include at least one of a non-volatile and a volatile memory. The non-volatile memory can include a read-only memory (ROM), a magnetic tape, a floppy disk, a flash memory, an optical storage, a high-density embedded non-volatile memory, a resistive random access memory (ReRAM), a magnetoresistive random access memory (MRAM), a ferroelectric random access memory (FRAM), a phase change memory (PCM), a graphene memory, etc. The volatile memory can include a random access memory (RAM) or an external cache memory, etc. As an illustration but not limitation, the RAM can be in various forms, such as a static random access memory (SRAM) or a dynamic random access memory (DRAM), etc.

[0082] The database involved in the embodiments provided in the present application can include at least one of a relational database and a non-relational database. The non-relational database can include a distributed database based on a blockchain, etc., without being limited thereto. The processor involved in the embodiments provided in the present application can be a general-purpose processor, a central processing unit, a graphics processing unit, a digital signal processor, a programmable logic device, a data processing logic device based on quantum computing, etc., without being limited thereto.

[0083] The technical features of the above embodiments can be combined arbitrarily. In order to make the description concise, all possible combinations of the technical features in the above embodiments are not described, however, as long as the combinations of the technical features do not exist contradictory, they should be considered as the scope of the present application.

[0084] The principles and implementation modes of the present application are described by applying specific examples in the present application. The above-mentioned embodiments are only used to help understand the method and its core idea of the present application; meanwhile, for those skilled in the art, according to the idea of the present application, the specific implementation mode and application range can be changed. In conclusion, the content of the present application should not be understood as a limitation.

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