A txt-to-sql generation method based on database sub-pattern division

Abstract

The application relates to the fields of database management and natural language processing, and specifically discloses a txt-to-sql generation method based on database sub-pattern division, which comprises the following steps: S1, constructing a database graph; S2, performing sub-pattern division on a weighted undirected graph G based on graph cutting to obtain a plurality of subgraphs, each of which corresponds to a database sub-pattern; S3, generating a sub-pattern abstract; S4, adopting a two-stage matching mechanism to select the optimal sub-pattern most relevant to a user input natural language query from a sub-pattern set; and S5, constructing a prompt word based on the optimal sub-pattern, the abstract corresponding to the optimal sub-pattern and the user natural language query, and inputting the prompt word into a large model to generate an SQL query statement. The technical scheme provided by the application fuses a database static structure and dynamic query behavior, automatically divides high correlation sub-patterns, accurately locates relevant sub-patterns through a two-stage matching, greatly reduces the input complexity of the large model, improves the accuracy and efficiency of SQL generation, and is suitable for large-scale database scenes.

Description

A method for generating txt-to-sql based on database subschema partitioning Technical Field

[0001] This invention relates to the fields of database management and natural language processing technology, specifically a txt-to-sql generation method based on database sub-schema partitioning. Background Technology

[0002] In the fields of database management and natural language processing, the text-to-SQL (TTL) technology based on the Large Language Model (LLM) has received widespread attention. This technology can convert users' natural language input into standard SQL query statements, enabling non-professionals to access databases intelligently.

[0003] However, enterprise databases typically contain hundreds of tables and complex field dependencies, resulting in a massive database schema. Existing Text-to-SQL methods often use the entire database schema and user query as input to large models, leading to excessively long inputs, inefficient model inference, and a significant drop in accuracy due to semantic interference and contextual confusion.

[0004] Meanwhile, existing technologies lack effective automated methods for database sub-schema partitioning and schema selection. On the one hand, traditional schema simplification methods rely on human experience or fixed rules, making it difficult to adapt to the dynamic structure of complex databases; on the other hand, the lack of a sub-schema selection mechanism that is associated with natural language query intent prevents generative models from reasoning in a targeted manner.

[0005] In summary, existing Text-to-SQL technologies suffer from excessively long contexts and excessive schema noise when dealing with extremely large databases. There is an urgent need for a technical solution for large databases that can automatically divide the database into sub-schemas and dynamically select sub-schemas based on natural language intent, in order to improve the performance of large models in Text-to-SQL tasks and achieve efficient, accurate, and context-controlled automatic generation of SQL statements. Summary of the Invention

[0006] To address the aforementioned problems in the prior art, this invention provides a txt-to-sql generation method based on database sub-schema partitioning, which reduces the input load of large models and improves the accuracy and efficiency of SQL generation.

[0007] To achieve the above objectives, this invention proposes a txt-to-sql generation method based on database sub-schema partitioning, comprising:

[0008] S1. Constructing the database graph: Extract structured and usage behavior information from the target database's structure definition file and query logs, and construct a weighted undirected graph G=(V,E,W).

[0009] S2. Graph Cut-Based Sub-Pattern Partitioning: The weighted undirected graph G is structurally partitioned using a graph cut-based approach to obtain several subgraphs with high structural relevance and semantic aggregation. Each subgraph corresponds to a sub-pattern in the database. The sub-pattern represents a set of table fields that are structurally closely related and frequently co-occur in query behavior.

[0010] S3. Sub-pattern summarization generation: Perform the summarization process for each sub-pattern to obtain the natural language summary corresponding to each sub-pattern, and establish an index mapping relationship between sub-patterns and summaries;

[0011] S4. Sub-pattern selection based on two-stage matching: A two-stage matching mechanism is adopted. The first stage is semantic matching based on sub-pattern summaries, and the second stage is fine-grained matching based on database sub-patterns. The optimal sub-pattern most relevant to the user's natural language query is selected from the sub-pattern set through the two-stage matching mechanism.

[0012] S5. Natural Language Query to SQL Generation: Based on the optimal sub-pattern, its corresponding summary, and the user's natural language query, construct prompt words, input the large model, and generate SQL query statements.

[0013] Preferably, in S1, the database structure definition file includes SQL table creation statements, a data dictionary, or a metadata description file, used to provide static structure information of tables, fields, primary keys, foreign keys, and indexes in the database; the query log is used to provide dynamic behavior information such as the co-occurrence relationship and access frequency of fields and tables during the actual query process.

[0014] Preferably, in S1, the constructed weighted undirected graph G includes three elements: a node set, an edge set, and a weight set. The node set V includes table nodes and field nodes, with table node weights being higher than field node weights. The edge set E includes belonging edges, foreign key edges, co-occurrence edges, and index or primary key edges. The edge weights in the weight set W reflect the dynamic characteristics of the database's static structural relationships and actual query behavior.

[0015] Preferably, in S1, the belonging edge connects the table node to its belonging field node; the foreign key edge is generated based on the foreign key constraints in the database definition file and connects the field nodes with foreign key relationships; the co-occurrence edge is generated based on the co-occurrence frequency of the field or table as statistically analyzed in the query log; and the index or primary key edge connects the index field to the primary key field or index table node.

[0016] Preferably, in S2, the specific steps of the graph-cut-based partitioning process include:

[0017] S21. Graph Cut Modeling: Partitioning a weighted undirected graph and finding subsets of the weighted undirected graph. and satisfy ,and The partitioning cost function minimizes or equivalently maximizes the internal connection weights of each subgraph, and the cost function is calculated as follows:

[0018] ;

[0019] In the formula, This is the symbol for the entire cost function, indicating that the graph is divided into k subgraphs. The total weight of the cut edges generated at that time, where k is the number of subgraphs. It is the set of vertices of the partitioned subgraph. The loop variable, ranging from 1 to k, is used to iterate through each subgraph. Let j be the loop variable, ranging from i+1 to k. Representing an edge It is an edge in the edge set E of the original graph. and This indicates that the two endpoints u and v of this edge belong to subgraph V respectively. i and V j , Representing an edge The weight reflects the strength of the connection between two nodes;

[0020] S22. Weight-guided partitioning constraints: Introduce node weight and edge weight constraint mechanisms to ensure that the partitioning results reflect the database structure logic;

[0021] S23. Generation, Partitioning, Execution, and Results: After executing steps S21-S22, the system obtains several subgraphs, each containing one or more table nodes and their related field nodes. The resulting partitioning has three characteristics: a large sum of edge weights and node weights within a subgraph indicates a tight internal relationship; small edge weights between subgraphs indicate a weak cross-subgraph relationship; and foreign key relationships are preferentially kept within the same subgraph.

[0022] S24. Optimize the partitioning structure: merge small-scale subgraphs, absorb isolated nodes, and check the connectivity of subpatterns. The optimized partitioning result is a set of structural subpatterns for the database.

[0023] Preferably, in S22, the node weight constraint assigns higher node weights to table nodes and lower node weights to field nodes, introducing node weight items to constrain the balance of partitioning; the edge weight constraint sets the highest priority for foreign key edges, prioritizes maintaining the aggregation of fields and tables for belonging edges, and co-occurrence edges play a semantic auxiliary enhancement role, keeping index or primary key edges in the same subgraph.

[0024] Preferably, in S3, the natural language summary corresponding to the sub-pattern covers the main tables and their field composition, primary and foreign key relationships between tables, common query subjects and semantic domains, and typical co-occurring fields or query keywords contained in the sub-pattern. The formal expression of the natural language summary corresponding to the sub-pattern is as follows:

[0025] ;

[0026] Among them, the summary generation function The specific form is:

[0027] ;

[0028] The established index mapping expression between sub-schemas and summaries is as follows:

[0029] ;

[0030] In the formula, For the function of generating the summary, The generated natural language summary text corresponding to the subpattern. As a sub-pattern, Tables(S i ) represents subpattern S i The collection of all tables included, Columns(S i ) represents subpattern S i The collection of all fields contained in Relations(S) i ) represents subpattern S i The set of primary and foreign key relationships between tables, Queries(S i ) represents subpattern S i A set of typical query scenarios.

[0031] Preferably, in S3, the summary generation process adopts a three-stage method of structural parsing, semantic extraction, and text generation. In the structural parsing stage, the core structural information of the sub-pattern is identified and a structural description template is generated. In the semantic extraction stage, semantic tags of fields and tables are extracted to form a set of topic tags. In the text generation stage, the structural description and semantic tags are combined to generate a natural language summary.

[0032] Preferably, in S4, the two-stage matching mechanism includes:

[0033] Phase 1: Summary-level coarse matching: Input the user query text and the summaries of each sub-pattern into the pre-trained language model to obtain semantic vectors, calculate the cosine similarity, sort according to the similarity scores, select the top k sub-patterns with the highest similarity to form a candidate set, and output the candidate sub-pattern set with the corresponding similarity scores.

[0034] Phase 2: Structural-level fine matching. Semantic parsing and intent recognition are performed on the user query text to extract the query object, constraints and operation types. The structural information of the candidate sub-patterns and the structural matching scores of the structural elements corresponding to the keywords in the query intent are calculated. The semantic similarity score and the structural matching score are fused to select the optimal sub-pattern. The selected optimal sub-pattern and its summary are used as the input context generated by the large model and together with the user query text to form the prompt word template.

[0035] Preferably, in S5, the prompt words adopt a structured construction mechanism, consisting of a system instruction part, a database schema description part, a user query part, and an output constraint part; after receiving the prompt words, the large model generates an SQL query statement through semantic understanding and structure mapping, SQL generation, and result verification processes. The result verification includes syntax checking, schema consistency verification, and optional execution testing.

[0036] Therefore, this invention proposes a txt-to-sql generation method based on database sub-schema partitioning, which has the following advantages:

[0037] (1) The database structure is automatically partitioned by graph cut algorithm without manual intervention, which can adapt to complex dynamic database structure, significantly reduce the input scale of large models and reduce context interference; a two-stage sub-pattern selection mechanism is adopted, which quickly filters candidates by semantic coarse matching and accurately locates the optimal sub-pattern by structural fine matching, which greatly improves the matching accuracy while ensuring recall.

[0038] (2) Integrate the static structure definition of the database with the historical query log, take into account both structural constraints and actual query behavior, enhance the business relevance of the pattern division, and make the sub-patterns more in line with actual application scenarios; Integrate the static structure definition of the database with the historical query log, take into account both structural constraints and actual query behavior, enhance the business relevance of the pattern division, and make the sub-patterns more in line with actual application scenarios.

[0039] The technical solution of the present invention will be further described in detail below with reference to the accompanying drawings and embodiments. Attached Figure Description

[0040] Figure 1 is a schematic diagram of the overall process of a txt-to-sql generation method based on database sub-schema partitioning according to the present invention;

[0041] Figure 2 is a schematic diagram of the txt-to-sql generation method based on database sub-schema partitioning according to the present invention, based on database definition and query log;

[0042] Figure 3 is a schematic diagram of the database graph construction process of a txt-to-sql generation method based on database sub-schema partitioning according to the present invention. Detailed Implementation

[0043] To make the technical solutions, advantages, and objectives of the present invention clearer, the technical solutions of the embodiments of the present invention will be clearly and completely described below. The described embodiments are only some, not all, of the embodiments of the present invention. All other embodiments obtained by those skilled in the art based on the described embodiments of the present invention without creative effort are within the protection scope of this application.

[0044] Unless otherwise defined, the technical or scientific terms used in this invention shall have the ordinary meaning as understood by one of ordinary skill in the art to which this invention pertains.

[0045] As shown in Figures 1-3, the present invention provides a txt-to-sql generation method based on database sub-schema partitioning, comprising:

[0046] S1. Constructing the database graph: Extract structured and usage behavior information from the target database's structure definition file and query logs, and construct a weighted undirected graph G=(V,E,W).

[0047] Database structure definition files include SQL table creation statements, data dictionaries, or metadata description files, which provide static structure information of tables, fields, primary keys, foreign keys, and indexes in the database; query logs provide dynamic behavior information such as the co-occurrence relationship and access frequency of fields and tables during actual queries.

[0048] The constructed weighted undirected graph G includes three elements: a set of nodes, a set of edges, and a set of weights. The set of nodes V includes table nodes and field nodes, with table nodes having higher weights than field nodes. The set of edges E includes belonging edges, foreign key edges, co-occurrence edges, and index or primary key edges. The edge weights in the set of weights W reflect the dynamic characteristics of the static structural relationship of the database and the actual query behavior.

[0049] Belonging edges connect table nodes to their respective field nodes; foreign key edges are generated based on foreign key constraints in the database definition file and connect field nodes with foreign key relationships; co-occurrence edges are generated based on the co-occurrence frequency of fields or tables as statistically analyzed in query logs; index or primary key edges connect index fields to primary key fields or index table nodes.

[0050] S2. Graph Cut-Based Sub-Pattern Partitioning: The weighted undirected graph G is structurally partitioned using a graph cut-based approach to obtain several subgraphs with high structural relevance and semantic aggregation. Each subgraph corresponds to a sub-pattern in the database. The sub-pattern represents a set of table fields that are structurally closely related and frequently co-occur in query behavior.

[0051] The specific steps of the graph-cut-based partitioning process include:

[0052] S21. Graph Cut Modeling: Partitioning a weighted undirected graph and finding subsets of the weighted undirected graph. and satisfy ,and The partitioning cost function minimizes or equivalently maximizes the internal connection weights of each subgraph, and the cost function is calculated as follows:

[0053] ;

[0054] In the formula, This is the symbol for the entire cost function, indicating that the graph is divided into k subgraphs. The total weight of the cut edges generated at that time, where k is the number of subgraphs. It is the set of vertices of the partitioned subgraph. The loop variable, ranging from 1 to k, is used to iterate through each subgraph. Let j be the loop variable, ranging from i+1 to k. Representing an edge It is an edge in the edge set E of the original graph. and This indicates that the two endpoints u and v of this edge belong to subgraph V respectively. i and V j , Representing an edge The weight reflects the strength of the connection between two nodes;

[0055] S22. Weight-guided partitioning constraints: Introduce node weight and edge weight constraint mechanisms to ensure that the partitioning results reflect the database structure logic;

[0056] Node weight constraints assign higher node weights to table nodes and lower node weights to field nodes, introducing node weight items to constrain the balance of partitioning; edge weight constraints set the highest priority for foreign key edges, prioritize the aggregation of fields and tables for belonging edges, co-occurrence edges play a semantic auxiliary enhancement role, and index or primary key edges are kept in the same subgraph.

[0057] S23. Generation, Partitioning, Execution, and Results: After executing steps S21-S22, the system obtains several subgraphs, each containing one or more table nodes and their related field nodes. The resulting partitioning has three characteristics: a large sum of edge weights and node weights within a subgraph indicates a tight internal relationship; small edge weights between subgraphs indicate a weak cross-subgraph relationship; and foreign key relationships are preferentially kept within the same subgraph.

[0058] S24. Optimize the partitioning structure: merge small-scale subgraphs, absorb isolated nodes, and check the connectivity of subpatterns. The optimized partitioning result is a set of structural subpatterns for the database.

[0059] S3. Sub-pattern summarization generation: Perform the summarization process for each sub-pattern to obtain the natural language summary corresponding to each sub-pattern, and establish an index mapping relationship between sub-patterns and summaries;

[0060] The natural language summary corresponding to the sub-schema covers the main tables and their field composition, the primary and foreign key relationships between the tables, common query subjects and semantic domains, and typical co-occurring fields or query keywords. The formal expression of the natural language summary corresponding to the sub-schema is as follows:

[0061] ;

[0062] Among them, the summary generation function The specific form is:

[0063] ;

[0064] The established index mapping expression between sub-schemas and summaries is as follows:

[0065] ;

[0066] In the formula, For the function of generating the summary, The generated natural language summary text corresponding to the subpattern. As a sub-pattern, Tables(S i ) represents subpattern S i The collection of all tables included, Columns(S i ) represents subpattern S i The collection of all fields contained in Relations(S) i ) represents subpattern S i The set of primary and foreign key relationships between tables, Queries(S i ) represents subpattern S i A set of typical query scenarios.

[0067] The abstract generation process employs a three-stage approach: structural parsing, semantic extraction, and text generation. The structural parsing stage identifies the core structural information of sub-patterns and generates a structural description template. The semantic extraction stage extracts semantic tags from fields and tables to form a set of topic tags. The text generation stage integrates the structural description and semantic tags to generate a natural language abstract.

[0068] S4. Sub-pattern selection based on two-stage matching: A two-stage matching mechanism is adopted to select the optimal sub-pattern most relevant to the natural language query input by the user from the sub-pattern set; specifically, it includes input definition and two-stage matching mechanism.

[0069] Input is defined as:

[0070] The system receives the natural language query text input by the user, denoted as:

[0071] ;

[0072] In the formula, This represents the sentence entered by the user as a query.

[0073] The system's stored sub-schema set is represented as follows:

[0074] ;

[0075] The two-phase matching mechanism includes:

[0076] Phase 1, summary-level coarse matching: The user query text and summaries of each sub-pattern are input into a pre-trained language model to obtain semantic vectors, the expression of which is:

[0077] ;

[0078] In the formula, Q represents the user's query text;

[0079] The cosine similarity between a user query and each subpattern summary is calculated using the following expression:

[0080] ;

[0081] Based on the similarity scores, the top k most similar subpatterns are selected to form a candidate set, and the candidate subpattern set is output. , along with the corresponding similarity score;

[0082] Phase Two: Structural-level fine matching. Semantic parsing and intent recognition are performed on the user query text to extract the query object, constraints, and operation type. The structural matching score is calculated between the structural information of candidate sub-patterns and the structural elements corresponding to keywords in the query intent. The structural matching score is defined as follows:

[0083] ;

[0084] In the formula, KeyMatach represents the degree of matching between the field or table name and the query keywords, and RelationMatch represents the correspondence between the structural relationship and the query semantics. A weighted system for assessing the degree of match between field or table names and query keywords. A weighted system for the correspondence between structural relationships and query semantics;

[0085] By fusing semantic similarity scores and structural matching scores, the optimal sub-pattern is selected, which is the semantic similarity score of stage one. Structure matching score The weighted fusion is expressed as follows:

[0086] ;

[0087] In the formula, To adjust the system and balance the importance of semantic matching and structural matching;

[0088] Based on the selected optimal sub-pattern and its abstract The prompt word template is formed together with the input context generated by the large model and the user query text.

[0089] S5. Natural Language Query to SQL Generation: Based on the optimal sub-pattern, its corresponding summary, and the user's natural language query, suggestion words are constructed, and the large model is used to generate SQL query statements. This includes input definition, suggestion word construction mechanism, and large model generation and parsing mechanism.

[0090] Input definition: The input consists of the user's natural semantic query Q from the query text input by the user, and the optimal database sub-schema S determined by the sub-schema selection module. S and Sub-pattern Sub-pattern summary T of the corresponding summary It consists of three parts;

[0091] The prompt keywords adopt a structured construction mechanism: they consist of a system instruction part, a database schema description part, a user query part, and an output constraint part.

[0092] The system instruction section is used to constrain the model's task objectives, ensuring that the generation task focuses on generating SQL queries; the database schema description section provides structured information about the database, including table names, field names, and foreign key relationships; the user query section represents the user's natural language query requirements; and the output constraint section limits the model's output format, ensuring that only SQL statements are generated.

[0093] The final prompt template is as follows:

[0094] ;

[0095] After receiving the prompt words, the large model generates an SQL query statement through semantic understanding and structural mapping, SQL generation, and result verification. Result verification includes syntax checking, pattern consistency verification, and optional execution testing.

[0096] Example 1

[0097] This embodiment uses a power grid marketing business database as an application scenario to explain the specific implementation process of the present invention. The database contains core business data such as user registration, meter reading records, and electricity bills. The embodiment details the specific implementation process of a txt-to-sql generation method based on database sub-schema partitioning.

[0098] S1. Database graph construction:

[0099] Data Collection and Analysis: Collect the SQL table creation statements, data dictionary, and other structure definition files of the power grid marketing business database, as well as historical query logs. Extract three table nodes (user_registration, meter_reading, and billing) from the structure definition files, and extract the field nodes of each table (such as user_id, name, meter_id, reading_date, amount_due, etc.). Identify the primary key (such as user_id), foreign key (such as meter_id in the meter_reading table related to meter_id in the user_registration table), and index information. Analyze the co-occurrence relationships of fields from the query logs, such as the high-frequency co-occurrence records of user_id and amount_due, and meter_id and reading_value.

[0100] Graph structure construction:

[0101] Node set V: The table node weight is set to twice the field node weight to enhance the table's aggregation.

[0102] Edge set E: Establish belonging edges (connecting the table and the belonging field), foreign key edges (connecting meter_reading.meter_id and user_registration.meter_id), co-occurrence edges (associating high-frequency co-occurrence fields based on log statistics), and index or primary key edges (connecting the primary key field and the index field).

[0103] Weight set W: Home edge weight 1.0, foreign key edge weight 3.0, index or primary key edge weight 2.0, co-occurrence edge weight set to 0.5. The calculation (co-occurrence frequency or 50) reflects the strength of business association.

[0104] S2. Sub-pattern partitioning based on graph cut:

[0105] Graph cut modeling: formalizes the database graph as a minimum cut problem, minimizing the sum of edge weights across subgraphs and maximizing the edge weight density within subgraphs;

[0106] Constraints apply: Follow node weight constraints (table nodes have higher weights) and edge weight constraints (foreign key edges have the highest priority).

[0107] Partitioning and Optimization: After executing the graph cut algorithm, three subgraphs are obtained. After merging small-scale subgraphs, absorbing isolated nodes, and checking connectivity, three structured sub-patterns are formed:

[0108] Sub-pattern 1 (Customer Basic Information): Includes a user registration table and field nodes such as user_id, name, address, phone_number, and meter_id;

[0109] Sub-pattern 2 (meter reading data record): includes a meter reading record table and field nodes such as reading_id, meter_id, reading_date, and reading_value, and retains the foreign key association with the customer basic information sub-pattern;

[0110] Sub-pattern 3 (Electricity Bill Management): Includes an electricity bill table and field nodes such as billing_period_start, consumption, amount_due, and payment_status, retaining the association with user_id from the customer basic information sub-pattern.

[0111] S3, Sub-pattern Summary Generation:

[0112] Structure analysis: Identify the table field composition and foreign key relationships of each sub-schema, such as "the meter reading record table is associated with the user registration table through meter_id";

[0113] Semantic extraction: Extracting semantic tags, such as "customer information", "meter reading data", and "bill query".

[0114] Text generation:

[0115] Sub-pattern 1 Summary T1: This sub-pattern includes a user registration form with fields such as user ID, name, address, and meter ID, used to store basic registration information for electricity customers. Common query keywords are user ID and meter ID.

[0116] Submode 2 Summary T2: This submode includes a meter reading record table and fields such as meter reading ID, meter ID, meter reading date, and meter reading value. It is associated with the customer's basic information through the meter ID and is mainly used to query meter reading data for a specific time period.

[0117] Sub-pattern 3 Summary T3: This sub-pattern includes an electricity bill table with fields such as billing cycle, electricity consumption, amount due, and payment status. It is associated with the customer's basic information through the user ID and is used for bill query and payment status statistics.

[0118] S4. Sub-pattern selection based on two-phase matching:

[0119] Suppose a user enters a natural language query Q: "Query the unpaid bills for user ID 1001".

[0120] Abstract-level coarse matching: Input Q with T1, T2, and T3 into the BERT model to generate semantic vectors. After calculating the cosine similarity, T3 (related to bill query) has the highest similarity and enters the candidate set together with T1 (related to user ID) (k=2).

[0121] Structural-level fine matching: Parsing the query objects (user ID, unpaid bills), constraints (user_id=1001, payment_status=Unpaid), and operation type (query) of Q; calculating the structural matching score, sub-pattern 3, which contains core fields such as amount_due and payment_status and their association with user_id, has the highest structural matching score; fusing semantic similarity and structural matching score, sub-pattern 3 is determined to be the optimal sub-pattern S. Its abstract is T .

[0122] S5. Natural language query to SQL generation:

[0123] Prompt word construction:

[0124] System command: "You are a database query generation assistant. Generate the corresponding SQL statement based on the provided database structure and natural language question."

[0125] Database schema description: The database subschema includes the electricity billing table billing (fields: billing_period_start, billing_period_end, consumption, amount_due, payment_status, user_id) and the user registration table user_registration (fields: user_id, name, meter_id). Foreign key relationship: billing.user_id - user_registration.user_id;

[0126] User query: "Query unpaid bills for user ID 1001;"

[0127] Output constraint: "Output only the SQL statement, without explanation."

[0128] SQL Generation and Validation: After receiving prompts, the large model generates SQL statements through semantic understanding and structural mapping, and then performs syntax checking and schema consistency verification.

[0129] SELECT u.name, u.meter_id, b.billing_period_start, b.billing_period_end, b.amount_due

[0130] FROM billing b

[0131] JOIN user_registration u ON b.user_id = u.user_id

[0132] WHERE b.payment_status = 'Unpaid' AND u.user_id = 1001;

[0133] Therefore, this invention provides a txt-to-sql generation method based on database sub-schema partitioning. By integrating the static structure definition of the database with historical query logs to construct a weighted undirected graph, the method automatically partitions sub-schemas using a graph cut algorithm and generates natural language summaries. Then, through two-stage semantic-structure matching, the optimal sub-schema is accurately located, reducing the input complexity and contextual interference of large models, improving the accuracy of SQL generation and inference efficiency, and adapting to the Text-to-SQL task requirements of large-scale databases.

[0134] Finally, it should be noted that the above embodiments are only used to illustrate the technical solutions of the present invention and not to limit them. Although the present invention has been described in detail with reference to preferred embodiments, those skilled in the art should understand that modifications or equivalent substitutions can still be made to the technical solutions of the present invention, and these modifications or equivalent substitutions cannot cause the modified technical solutions to deviate from the spirit and scope of the technical solutions of the present invention.

Claims

1. A method for generating txt-to-sql based on database subschema partitioning, characterized in that, include: S1. Constructing the Database Graph: Extract structured and usage behavior information from the target database's structure definition file and query logs to construct a weighted undirected graph G=(V,E,W); S2. Graph Cut-Based Sub-schema Partitioning: Partition the weighted undirected graph G using a graph cut-based approach to obtain several subgraphs with structural relevance and semantic aggregation. Each subgraph corresponds to a sub-schema in the database, where each sub-schema represents a set of table fields that are structurally closely related and co-occur in query behavior; S3. Sub-schema Summary Generation: Perform a summary generation process on each sub-schema to obtain a natural language summary corresponding to each sub-schema, establishing a relationship between sub-schemas and summaries. Index mapping relationship; S4, Sub-pattern selection based on two-stage matching: A two-stage matching mechanism is adopted. The first stage is semantic matching based on sub-pattern summaries, and the second stage is fine-grained matching based on database sub-patterns. The optimal sub-pattern most relevant to the user's natural language query is selected from the sub-pattern set through the two-stage matching mechanism; S5, Natural language query to SQL generation: Based on the optimal sub-pattern, its corresponding summary and the user's natural language query, prompt words are constructed and input into the large model to generate SQL query statements; In S2, the specific steps of the sub-pattern partitioning based on graph cut include: S21, Graph cut modeling: Partitioning the weighted undirected graph and finding a subset of the weighted undirected graph. And satisfy the condition that they do not intersect. And covering the entire series The partitioning cost function minimizes or equivalently maximizes the internal connection weights of each subgraph, and the cost function is calculated as follows: In the formula, This is the symbol for the entire cost function, indicating that the graph is divided into k subgraphs. The total weight of the cut edges generated at that time, where k is the number of subgraphs. It is the set of vertices of the partitioned subgraph. The loop variable, ranging from 1 to k, is used to iterate through each subgraph. Let j be the loop variable, ranging from i+1 to k. Representing an edge It is an edge in the edge set E of the original graph. and This indicates that the two endpoints u and v of this edge belong to subgraph V respectively. i and V j , Representing an edge The weights reflect the strength of the connection between two nodes; S22, Weight-guided partitioning constraints: Introducing a node weight and edge weight constraint mechanism, the node weight constraint is that the table node weight is greater than the field node weight, and the node weight item is introduced to constrain the partition balance; the edge weight constraint is that foreign key edges are given the highest priority, belonging edges prioritize maintaining the aggregation of fields and tables, co-occurrence edges play a semantic auxiliary enhancement role, and index or primary key edges are kept in the same subgraph; S23, Generating partitioning execution and results: After executing steps S21-S22, the system obtains several subgraphs, each containing one or more table nodes and their related field nodes; the obtained partitioning results have three characteristics: the sum of the weights of edges and nodes within a subgraph reflects the tight internal association; the weights of edges between subgraphs reflect the weak cross-subgraph connection; foreign key relationships are prioritized to be kept in the same subgraph; S24, Partitioning structure optimization: Subgraph merging, isolated node absorption, and sub-pattern connectivity checks, the optimized partitioning result is a set of structural sub-patterns of the database.

2. The method for generating txt-to-sql based on database sub-schema partitioning according to claim 1, characterized in that, In S1, the structure definition file of the target database includes SQL table creation statements, data dictionary or metadata description file, which are used to provide static structure information of tables, fields, primary keys, foreign keys and indexes in the database; The query log is used to provide dynamic behavioral information on the co-occurrence relationship and access frequency of fields and tables during the actual query process.

3. The method for generating txt-to-sql based on database sub-schema partitioning according to claim 1, characterized in that, In S1, the constructed weighted undirected graph G includes three elements: a node set, an edge set, and a weight set. The node set V includes table nodes and field nodes, with table node weights being higher than field node weights. The edge set E includes belonging edges, foreign key edges, co-occurrence edges, and index or primary key edges. The edge weights in the weight set W reflect the dynamic characteristics of the database's static structural relationships and actual query behavior.

4. The method for generating txt-to-sql based on database sub-schema partitioning according to claim 3, characterized in that, In S1, the belonging edge connects the table node to its belonging field node; the foreign key edge is generated based on the foreign key constraints in the database definition file and connects the field nodes with foreign key relationships; the co-occurrence edge is generated based on the co-occurrence frequency of the field or table in the query log statistics; and the index or primary key edge connects the index field to the primary key field or index table node.

5. The method for generating txt-to-sql based on database sub-schema partitioning according to claim 1, characterized in that, In S3, the natural language summary corresponding to the sub-pattern covers the main tables and their field composition, the primary and foreign key relationships between tables, common query subjects and semantic domains, and typical co-occurring fields or query keywords contained in the sub-pattern. The formal expression of the natural language summary corresponding to the sub-pattern is as follows: Among them, the summary generation function The specific form is: The established index mapping expression between sub-schemas and summaries is as follows: In the formula, For the function of generating the summary, The generated natural language summary text corresponding to the subpattern. As a sub-pattern, Subpattern The collection of all tables included. Subpattern A collection containing all fields. Subpattern The set of primary and foreign key relationships between tables. Subpattern A set of typical query scenarios.

6. The method for generating txt-to-sql based on database sub-schema partitioning according to claim 1, characterized in that, In S3, the summary generation process adopts a three-stage method of structural parsing, semantic extraction and text generation. In the structural parsing stage, the core structural information of the sub-pattern is identified and a structural description template is generated. The semantic extraction stage extracts semantic tags from fields and tables to form a set of topic tags; the text generation stage integrates structural descriptions and semantic tags to generate a natural language summary.

7. The method for generating txt-to-sql based on database sub-schema partitioning according to claim 1, characterized in that, In S4, the two-stage matching mechanism includes: Stage 1, summary-level coarse matching: inputting the user query text and the summaries of each sub-pattern into a pre-trained language model to obtain semantic vectors, calculating cosine similarity, sorting according to similarity scores, selecting the top k sub-patterns with the highest similarity to form a candidate set, and outputting the candidate sub-pattern set with corresponding similarity scores; Stage 2, structure-level fine matching: performing semantic parsing and intent recognition on the user query text, extracting query objects, constraints, and operation types, calculating the structural information of candidate sub-patterns and the structural matching score of structural elements corresponding to keywords in the query intent, fusing semantic similarity scores and structural matching scores, selecting the optimal sub-pattern, and using the selected optimal sub-pattern and its summary as the input context generated by the large model, together with the user query text, to form a prompt word template.

8. The method for generating txt-to-sql based on database sub-schema partitioning according to claim 1, characterized in that, In S5, the prompt words adopt a structured construction mechanism, consisting of a system instruction part, a database schema description part, a user query part, and an output constraint part. After receiving the prompt words, the large model generates an SQL query statement through semantic understanding and structure mapping, SQL generation, and result verification. Result verification includes syntax checking, schema consistency verification, and optional execution testing.

Images