Training method of sql generation model for water conservancy river length system

Abstract

The application provides a training method of an SQL generation model for a water conservancy river length system, including obtaining a training sample set for a target database; performing mode link processing on each sample in the training sample set based on a business knowledge graph to obtain mode information corresponding to natural language questions in the training sample set; fine-tuning a pre-trained language model based on the training sample set and the mode information using contrast learning; obtaining execution cost feedback of an SQL statement output by the fine-tuned language model in the target database; updating at least one of a weight of an edge in the business knowledge graph and a training parameter of the contrast learning based on the execution cost feedback, and iteratively performing fine-tuning until an SQL generation model is obtained. The execution efficiency and stability of the SQL statement generated by the trained model in the target database can be improved, and the training sample acquisition, mode information matching, and model parameter adjustment in the training process form an interrelated optimization path.

Description

Technical Field

[0001] This application relates to the field of water conservancy engineering technology, specifically to a training method for an SQL generation model oriented towards the river chief system in water conservancy. Background Technology

[0002] As the data volume accumulated by various business systems continues to grow, the need for business users to query databases using natural language is becoming increasingly prominent. SQL generation technology, as a key technology connecting natural language queries and database operations, directly impacts the usability of data queries through the syntactic executability and execution efficiency of the generated SQL statements. SQL generation methods based on large language models are currently the main technical approach. These methods fine-tune pre-trained language models to enable them to output corresponding SQL statements based on natural language queries.

[0003] In SQL generation applications targeting specific databases, the following objective technical challenges exist. Target databases typically possess different syntax systems, function sets, and table structure organization methods than general open-source databases, posing corresponding requirements for the syntactic compatibility of the generated SQL statements. For the generated SQL statements, merely satisfying syntactic executableness is insufficient for practical use. The actual execution cost of the SQL statement in the target database also affects the query experience, and this execution cost depends on runtime information obtained from the target database's own execution plan parsing capabilities. Furthermore, the collaborative relationships between the acquisition and organization of training data, the establishment of the association between natural language questions and database schema information, and the setting of model training parameters significantly impact the stability of the SQL generation from the final trained model.

[0004] When the training process cannot effectively coordinate the above-mentioned steps and cannot incorporate runtime signals such as the execution cost of the target database into the model training process, the resulting SQL generation model is prone to unstable execution efficiency of the generated SQL statements when facing specific business queries. Summary of the Invention

[0005] In view of this, this application provides a training method for a SQL generation model for the river chief system in water conservancy, so as to improve the execution efficiency and stability of SQL statements generated by the trained model in the target database, and to make the training sample acquisition, pattern information matching and model parameter adjustment and other links in the training process form an interrelated optimization path.

[0006] The first aspect of this application provides a training method for a SQL generation model oriented towards the river chief system in water conservancy, comprising: Obtain a training sample set for the target database; Based on the business knowledge graph, pattern linking processing is performed on each sample in the training sample set to obtain pattern information corresponding to the natural language questions in the training sample set; wherein, the weight of at least one edge in the business knowledge graph is related to the SQL execution cost on the path corresponding to that edge; Based on the training sample set and the pattern information, contrastive learning is used to fine-tune the pre-trained language model; wherein, the training parameters of the contrastive learning are at least related to the sample complexity score and the weights of the edges in the business knowledge graph; Obtain the execution cost feedback of the SQL statement output by the fine-tuned language model in the target database; Based on the execution cost feedback, at least one of the weights of the edges in the business knowledge graph and the training parameters of the contrastive learning is updated, and the fine-tuning is performed iteratively until the SQL generation model is obtained.

[0007] Preferably, obtaining the training sample set for the target database includes: For at least one pre-defined business scenario, generate natural language question samples based on at least one variable and at least one sentence template corresponding to that business scenario; Based on the natural language question sample and the syntax rules of the target database, generate SQL statement samples corresponding to the natural language question sample; The natural language question samples and the SQL statement samples are used as training sample pairs and added to the training sample set.

[0008] Preferably, before using the natural language question sample and the SQL statement sample as training sample pairs, the training method further includes: Data cleaning is performed on the natural language problem samples and the SQL statement samples; Perform at least one of the following checks on the cleaned SQL statement sample: field integrity check, length check, and SQL syntax check; The natural language question samples and the SQL statement samples that pass the verification are used as the training sample pairs.

[0009] Preferably, the complexity score of each SQL statement sample in the training sample set satisfies: in, Score the complexity. The number of related data tables in the SQL statement sample. The number of filter conditions in the SQL statement sample. The number of aggregate functions in the SQL statement sample. The number of nested subqueries in the SQL statement sample. , , , All of these are preset weighting coefficients.

[0010] Preferably, after obtaining the training sample set for the target database, the training method further includes: Based on the complexity score of each SQL statement sample and the corresponding business feature vector, the training sample set is downsampled so that the proportion of training samples of each complexity level in the training sample set meets the preset distribution.

[0011] Preferably, the training sample set further includes at least one negative sample, and the SQL statement corresponding to the negative sample is marked as empty; The at least one negative sample includes any one of the following: unauthorized query sample, privacy information query sample, and query sample that exceeds the preset business scope.

[0012] Preferably, before performing pattern linking processing on each sample in the training sample set based on the business knowledge graph, the training method further includes: The business knowledge graph is constructed using the data tables in the target database as nodes and the relationships between the data tables as edges. The pattern linking process includes: Map the natural language questions in the training sample set to the corresponding concept nodes in the business knowledge graph; A bounded search is performed along the edges of the business knowledge graph on the corresponding concept nodes to obtain at least one data table node related to the natural language problem. The weights of the edges in the business knowledge graph satisfy the following: in, Let be the weight of the edge, and d be the number of hops in the shortest path from the corresponding concept node to the target data table node. For user issues With table Semantic cosine similarity between metadata Here, α is the cost factor for the SQL execution plan, β is the decay rate parameter, and λ is the cost penalty coefficient.

[0013] Preferably, the step of fine-tuning the pre-trained language model using contrastive learning includes: For the SQL statement samples in the training sample set, corresponding pseudo-negative samples are generated according to preset dialect conversion rules. The pre-trained language model is trained based on the SQL statement samples, the corresponding pseudo-negative samples, and the training parameters of the contrastive learning.

[0014] Preferably, the training parameters for the contrastive learning include adaptive margins and a loss function; Wherein, the adaptive margin satisfies: in, For the adaptive margin, The baseline constant of the preset boundary for the algorithm, The score is the space structure complexity of the corresponding SQL statement. γ is the edge weight decay value dynamically generated by the target core business table during the bounded search phase of the knowledge graph, and γ is the smoothing control coefficient that coordinates the proportion of the two cross-domain features in the penalty adjustment. The loss function satisfies: in, Let the loss function be... For cross-entropy loss term, As a positive sample, These are pseudo-negative samples. and These represent the generation probabilities of the pre-trained language model for positive samples and pseudo-negative samples, respectively. λ1 is the cost factor for the SQL execution plan, λ2 is the weight coefficient for the comparative loss, and λ3 is the weight for the execution cost reward.

[0015] Preferably, downsampling the training sample set includes: The training sample set is sorted in ascending order of complexity score for each SQL statement sample, and the sorted training sample set is divided into N consecutive buckets. For the i-th bucket, the center point of the sampling result of the (i-1)-th bucket is used as the front reference point, and the center point of all samples in the (i+1)-th bucket is used as the back reference point; wherein, the first bucket and the last bucket both use the sample corresponding to the median of the complexity score as the reference point; Based on the area of ​​the triangle formed by each sample point in the i-th bucket, the previous reference point, and the next reference point, the sample point with the largest triangle area is selected from the i-th bucket as the sampling result of the i-th bucket, where i is an integer between 1 and N.

[0016] Preferably, the business feature vector is generated based on at least two types of features; The at least two types of features include the business scenario category corresponding to the training sample and the syntax structure category contained in the SQL statement sample; The business feature vector is obtained by concatenating the at least two types of features after performing one-hot encoding.

[0017] Preferably, the iterative fine-tuning is performed until the SQL generation model is obtained, including: After each round of fine-tuning, the fine-tuned language model is evaluated to obtain the pass rate and average execution cost of the fine-tuned language model. When the improvement in the pass rate is lower than a first preset threshold and the reduction in the average execution cost is lower than a second preset threshold in multiple consecutive rounds of evaluation, the iteration ends and the current fine-tuned language model is used as the SQL generation model.

[0018] The SQL generation model training method provided in the first aspect of this application introduces edge weights related to execution cost in the pattern linking stage, so that the matching of training samples and pattern information takes into account both business relevance and execution efficiency; by associating the training parameters of contrastive learning with complexity scores and edge weights in the business knowledge graph, the adaptability of the training process to different sample characteristics is enhanced; by obtaining the execution cost feedback of the target database and using it to update the edge weights of the business knowledge graph and the contrastive learning training parameters, a closed-loop optimization path for the training process is constructed, so that the execution efficiency of the SQL statements generated by the model can be continuously optimized. Attached Figure Description

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

[0020] Figure 1 A flowchart illustrating the training method provided in an embodiment of this application; Figure 2 A schematic diagram of the overall architecture of the SQL generation model training system for the river chief system in water conservancy provided in this application embodiment; Figure 3 This is a schematic diagram of a customized data generation process provided in an embodiment of this application; Figure 4 This is a schematic diagram of the semantic projection filtering algorithm provided in the embodiments of this application; Figure 5 This is a schematic diagram of the closed-loop optimization iteration process provided in an embodiment of this application; Figure 6This is a schematic diagram comparing the SQL sample distribution complexity before and after the improved LTTB downsampling provided in an embodiment of this application. Figure 7 This is a schematic diagram of a business knowledge graph provided for an embodiment of this application. Detailed Implementation

[0021] In the following description, specific details such as particular system architectures and techniques are set forth for illustrative purposes and not for limitation, in order to provide a thorough understanding of the embodiments of this application. However, those skilled in the art will understand that this application may also be implemented in other embodiments without these specific details. In other instances, detailed descriptions of well-known systems, apparatuses, circuits, and methods have been omitted so as not to obscure the description of this application with unnecessary detail.

[0022] References to "one embodiment" or "some embodiments" as described in this specification mean that one or more embodiments of this application include a specific feature, structure, or characteristic described in connection with that embodiment. Therefore, the phrases "in one embodiment," "in some embodiments," "in other embodiments," "in still other embodiments," etc., appearing in different parts of this specification do not necessarily refer to the same embodiment, but rather mean "one or more, but not all, embodiments," unless otherwise specifically emphasized. The terms "comprising," "including," "having," and variations thereof mean "including but not limited to," unless otherwise specifically emphasized.

[0023] In one embodiment, this application provides a training method for an SQL generation model oriented towards the river chief system in water conservancy. Please refer to [link / reference]. Figure 1 The training method includes: S1: Obtain the training sample set for the target database; S2: Based on the business knowledge graph, perform pattern linking processing on each sample in the training sample set to obtain pattern information corresponding to the natural language questions in the training sample set; wherein, the weight of at least one edge in the business knowledge graph is related to the SQL execution cost on the path corresponding to that edge; S3: Based on the training sample set and the pattern information, the pre-trained language model is fine-tuned using contrastive learning; wherein the training parameters of the contrastive learning are at least related to the sample complexity score and the weights of the edges in the business knowledge graph; S4: Obtain the execution cost feedback of the SQL statement output by the fine-tuned language model in the target database; S5: Based on the execution cost feedback, update at least one of the edge weights in the business knowledge graph and the training parameters of the contrastive learning, and iteratively perform the fine-tuning until the SQL generation model is obtained.

[0024] The SQL generation model provided in this application can be applied to business scenarios such as water conservancy business query systems, river chief system information management platforms, and government data sharing and exchange platforms. Specifically, the model can be deployed on the server side of water conservancy bureaus, river basin management agencies, or environmental protection departments to process natural language query requests input by end users (such as water conservancy inspectors, river chiefs, and business analysts).

[0025] In practical applications, this SQL generation model receives natural language questions from users on the front-end interface regarding basic river information, incident handling statistics, river chief performance, water quality index analysis, or equipment operation and maintenance status. Based on the trained model parameters, it automatically converts the natural language questions into structured query language (SQL) statements that conform to the syntax specifications of the target database (e.g., DM database). Subsequently, the generated SQL statements are sent to the back-end database for execution to obtain query results (such as business data like the number of times river water quality meets standards and incident handling timeliness statistics), and the query results are then fed back to the front-end interface for display.

[0026] This application method is particularly suitable for handling water conservancy business scenarios involving multi-table joins, complex aggregation calculations, and specific domestic database functions (such as DATEADD_S and WM_CONCAT from DM Database). It can effectively solve the technical problems of low execution efficiency, poor syntax compatibility, and inability to accurately map business terms (such as 'river section patrol rate' and 'cross-regional coordination') of SQL statements generated by general large models on water conservancy-specific databases in existing technologies, and achieve efficient and accurate retrieval and analysis of water conservancy business data.

[0027] The training method provided in this embodiment consists of four parts working together: customized data generation, model fine-tuning and optimization, inference execution verification, and quantization evaluation iteration. Specifically, the customized data generation part is responsible for acquiring the training sample set as described in step S1; the model fine-tuning and optimization part is responsible for the pattern linking processing as described in step S2 and the fine-tuning process as described in step S3; the inference execution verification part is responsible for acquiring the execution cost feedback as described in step S4; and the quantization evaluation iteration part is responsible for the parameter updating and iteration process as described in step S5.

[0028] In this embodiment, the target database can be the DM database. The pre-trained language model is selected from pre-trained large language models with basic code and SQL generation capabilities.

[0029] Please see Figure 2 , Figure 2 This diagram illustrates the overall architecture of the SQL generation model training system for the river chief system in water conservancy provided in this embodiment. Figure 2The modules arranged from top to bottom are a customized data generation module, a model fine-tuning and optimization module, an inference execution and verification module, and a quantization evaluation and iteration module. Adjacent modules are connected through data flow paths to form a forward training path. The output of the quantization evaluation and iteration module is connected to the input of the customized data generation module, the model fine-tuning and optimization module, and the inference execution and verification module through feedback paths, so that the execution cost feedback can be transmitted to the upstream modules along the feedback paths to drive iterative optimization.

[0030] Please see Figure 1 , Figure 1 This diagram illustrates the flowchart of the training method provided in this embodiment. Figure 1 The execution order of steps S1 to S5 is shown from top to bottom. The output of step S5 is represented by a loop arrow pointing to the input of step S3, indicating the iterative execution of the fine-tuning process until the iteration termination condition is met, at which point the SQL generation model is output.

[0031] In this embodiment, by associating the weight of at least one edge in the business knowledge graph with the SQL execution cost of the path corresponding to that edge, the execution efficiency of the corresponding SQL statement can be considered during the pattern linking stage, avoiding the exposure of execution efficiency issues only in the subsequent fine-tuning stage. By associating the training parameters of contrastive learning with the sample complexity score and the weights of edges in the business knowledge graph, the training process can dynamically adjust the training intensity according to the complexity of the training samples and the execution characteristics of the pattern information, improving the model's learning effect on samples of different complexities. By obtaining the execution cost feedback of the SQL statement output by the fine-tuned language model, the performance information of the target database runtime is included in the model optimization scope. By using the execution cost feedback to update at least one of the edge weights in the business knowledge graph and the training parameters of contrastive learning and iteratively executing the fine-tuning, a closed-loop optimization path for the training process is constructed. Thus, this embodiment creates a coupled feedback between the data layer, semantic layer, and parameter layer, forming a unified optimization goal among execution cost, business relevance, and training stability.

[0032] In one embodiment, this application provides a training method for an SQL generation model oriented towards the river chief system for water conservancy, the training method comprising steps S1 to S5; wherein, step S1, obtaining a training sample set for a target database, includes: S11: For at least one pre-defined business scenario, generate natural language question samples based on at least one variable corresponding to the business scenario and at least one sentence template; S12: Based on the natural language question sample and the syntax rules of the target database, generate an SQL statement sample corresponding to the natural language question sample; S13: Add the natural language question sample and the SQL statement sample as training sample pairs to the training sample set.

[0033] In this embodiment, the acquisition of the training sample set is based on a customized modification of the OmniSQL framework. The target database for the synthesized data is fixed as a single target business database, and Chinese field comments and business scenario example values ​​are added to each data table in the database (e.g., river information table, event handling table, river chief information table) to ensure that the generated data matches the target business data structure. The acquisition process embeds domestic database syntax adaptation rules, clarifying field naming conventions, function usage restrictions, and table association syntax requirements, so that the generated SQL statement samples are compatible with the domestic database environment. When the target database is DM database, the function usage restrictions include the calling format of DM-specific date function DATEADD_S and aggregate function WM_CONCAT, and the field naming conventions cover case sensitivity requirements.

[0034] Step S11 employs a three-tiered data generation approach combining business scenario classification, variable replacement, and sentence randomization. The water conservancy river chief system business is divided into six core business scenarios: river basic information query, event handling statistics, river chief performance query, cross-regional data aggregation, water quality indicator analysis, and equipment operation and maintenance query. For each business scenario, 3 to 5 core variables are preset, such as river name, time range, statistical dimensions, and filtering conditions. Twelve basic sentence templates are designed, covering formal written language, colloquial expressions, descriptive statements, and concise instruction sentences. Initial natural language problem samples are generated by randomly filling the core variables and randomly combining the basic sentence templates. Further synonym replacement and word order adjustment are performed; for example, "query" is replaced with "statistics," "get," or "retrieve," and "number of times Jinjiang's water quality met standards in 2024" is adjusted to "statistics on Jinjiang's water quality meeting standards in 2024," thus giving the natural language problem samples stylistic diversity.

[0035] The problem generation template used in step S11, taking an event handling scenario as an example, can be represented in JSON format, as follows: { "scene": "Event handling statistics", "variables": ["{river_name}", "{time_range}", "{disposal_status}", "{stat_dim}"], "templates": [ "Query the {disposal_status} event {stat_dim} for {time_range}{river_name}?" What are the relevant events for {disposal_status} within {time_range} for {river_name} during {time_range}? "Please collect data on events with a status of {disposal_status} within the {time_range} and {river_name} basin." What is the status of the {river_name} event within {disposal_status} in {time_range} under {stat_dim}? ] } In the above template, the scene field identifies the business scenario; the variables field is an array of variable placeholders, which contains four variables in sequence: river name, time range, disposal status, and statistical dimension; the templates field is an array of sentence templates. The natural language question sample is generated by randomly filling the variable placeholders and randomly combining the sentence templates.

[0036] Step S12 constructs an SQL generation framework that combines table field mapping, syntax rule constraints, and business logic associations. It establishes a mapping dictionary between natural language keywords and domestic database fields, for example, mapping "number of times water quality meets standards" to COUNT(CASE WHEN water_quality='meets standards'THEN 1 END); embeds corresponding syntax constraints, such as using "||" for string concatenation and the DATE_FORMAT function for date filtering in the DM database; and automatically matches table association logic according to business scenarios, for example, the river chief performance query associates the "River Chief Information Table" and the "Performance Record Table," with the associated field being leader_id.

[0037] The SQL generation template used in step S12, taking an event handling scenario as an example, can be represented in JSON format, as follows: { "scene": "Event handling statistics", "table_join": "event_disposal INNER JOIN river_info ON event_disposal.river_id = river_info.river_id", “field_mapping”: { "Number of events": "COUNT(event_id)" "Processing time": "AVG(DATEDIFF(DAY, create_time, dispose_time))", "Number of river sections involved": "COUNT(DISTINCT RIVER_SEG_ID)" }, "sql_template": "SELECT {stat_dim_expr} FROM {table_join} WHERERIVER_SEG.RIVER_NAME = '{river_name}' AND EVENT_DISPOSAL.create_time BETWEEN'{time_start}' AND '{time_end}' AND EVENT_DISPOSAL.disposal_status ='{disposal_status}' GROUP BY {group_by_expr};” } In the template above, the `table_join` field describes the inner join relationship between the `event_disposal` table and the `river_info` table through the `river_id` field; the `field_mapping` field is a dictionary mapping statistical dimension names to SQL aggregation expressions, including three mappings: "number of events" mapped to `COUNT(event_id)`, "disposal duration" mapped to `AVG(DATEDIFF(DAY,create_time, dispose_time))`, and "number of river segments involved" mapped to `COUNT(DISTINCT RIVER_SEG_ID)`; the `sql_template` field provides a SQL statement template based on the above table joins and field mappings. The SQL statement template includes statistical dimension expressions in the `SELECT` clause, table join structures in the `FROM` clause, filtering by river segment name, creation time interval, and disposal status in the `WHERE` clause, and grouping expressions in the `GROUP BY` clause.

[0038] Please see Figure 3 , Figure 3 This diagram illustrates the customized data generation process provided in this embodiment. Figure 3The data generation process is presented in six steps from top to bottom: target database adaptation and modification, business scenario classification, variable and sentence template setting, natural language problem sample generation, corresponding SQL statement sample generation, and adding training sample pairs to the training sample set. These six steps are connected in sequence to form a complete data generation path.

[0039] This embodiment generates natural language question samples for at least one preset business scenario, combining at least one corresponding variable and at least one sentence template, so that the training samples have both business relevance and language style diversity; it generates corresponding SQL statement samples based on the grammar rules of the target database, so that the generated SQL statement samples are naturally compatible with the grammar environment of the target database, avoiding the introduction of grammar adaptation errors in the subsequent training stage; the natural language question samples and the corresponding SQL statement samples are added to the training sample set as training sample pairs, so that the sample granularity of the training samples corresponds to the actual business query requirements.

[0040] In one embodiment, this application provides a training method for an SQL generation model oriented towards the river chief system for water conservancy, the training method including steps S1 to S5 and steps S11 to S13; wherein, before using the natural language question sample and the SQL statement sample as training sample pairs, the training method further includes: S121: Perform data cleaning on the natural language problem samples and the SQL statement samples; S122: Perform at least one of the following checks on the cleaned SQL statement sample: field integrity check, length check, and SQL syntax check; S123: The natural language question sample and the SQL statement sample that have passed the verification are used as the training sample pair.

[0041] Step S121 employs a three-level regularization cleaning process, which includes: matching Markdown code block wrapper identifiers (including "``sql" and "``") with regular expressions to remove them from the natural language question samples and the SQL statement samples; matching JSON outer structures in the form of "{...}" with regular expressions to remove them; and matching redundant prefixes (e.g., "SQL:" and "Query SQL:") with regular expressions to delete them. The execution order of the three-level regularization cleaning process is: first, remove Markdown identifiers; then, remove JSON outer structures; and finally, delete redundant prefixes. After step S121 is completed, the effective sample rate of the natural language question samples and the SQL statement samples is not less than 98%, and the format of the SQL statement samples conforms to the execution requirements of the target database.

[0042] The field integrity check in step S122 ensures that each sample contains three core fields: the natural language question, the corresponding SQL statement, and the table structure information, without any missing fields. The length check ensures that the total length of the input text and tags does not exceed 8192 tokens. The SQL syntax check is implemented by calling the EXPLAIN command of the target database, while simultaneously parsing the execution plan cost. Filter SQL statement samples with syntax errors and execution plan costs. High-efficiency, high-cost samples with a value greater than 0.6 are identified. The verification execution order in step S122 is: field integrity verification, length verification, and SQL syntax verification, with decreasing priority. The handling strategy for verification failures is to directly discard the corresponding training sample pair. After step S122 is completed, the effective sample rate of the training sample set reaches over 99%. The high-efficiency, high-cost sample features discovered in step S122 are fed back to steps S11 and S12 to optimize the generation strategies of the natural language problem samples and the SQL statement samples, reducing the occurrence of high-efficiency, high-cost samples in subsequent generation.

[0043] This embodiment performs data cleaning on the natural language problem samples and the SQL statement samples, removing Markdown identifiers, JSON outer structures, and redundant prefixes from the generated data to ensure that the format of the training samples conforms to the requirements of the target database for SQL statements. The cleaned SQL statement samples are then subjected to field integrity verification, length verification, and SQL syntax verification to ensure that the training samples meet the training requirements in three dimensions: structural integrity, sequence length, and syntactic executability. High-efficiency sample features discovered during the verification process are fed back to the data generation stage, allowing the data generation strategy to be continuously optimized based on the verification results.

[0044] In one embodiment, this application provides a training method for an SQL generation model oriented towards the river chief system for water conservancy, the training method including steps S1 to S5 and steps S11 to S13; wherein, the complexity scores of each SQL statement sample in the training sample set satisfy the following relationship: (1) in, Score the complexity. The number of related data tables in the SQL statement sample. The number of filter conditions in the SQL statement sample. The number of aggregate functions in the SQL statement sample. The number of nested subqueries in the SQL statement sample. , , , All of these are preset weighting coefficients.

[0045] The optimal values, ranges, and basis for setting each parameter in formula (1) are shown in the table below: This embodiment incorporates the number of related tables, the number of filtering conditions, the number of aggregate functions, and the number of nested levels into a weighted sum of the complexity score, enabling the complexity score to comprehensively reflect the syntactic complexity of the SQL statement samples. By assigning different weight coefficients to each of the four dimensions, the complexity score reflects the actual impact of each dimension on the model's learning difficulty, with the highest weight given to the number of nested levels to more accurately capture the characteristics of SQL statement samples in complex business scenarios. This complexity score provides a unified quantitative basis for subsequent complexity balancing processing and the determination of comparative learning training parameters.

[0046] In one embodiment, this application provides a training method for an SQL generation model oriented towards the river chief system for water conservancy, the training method including steps S1 to S5 and steps S11 to S13; wherein, after obtaining the training sample set for the target database, the training method further includes: S14: Based on the complexity score of each SQL statement sample and the corresponding business feature vector, the training sample set is downsampled so that the proportion of training samples of each complexity level in the training sample set meets the preset distribution.

[0047] Step S14 employs an improved LTTB (Largest Triangle Three Buckets) algorithm to downsample the training sample set. Compared to stratified sampling and SMOTE algorithms, the improved LTTB algorithm can efficiently achieve downsampling while preserving core features, thus meeting the requirements for balancing the complexity of training samples.

[0048] Based on the complexity score described in formula (1), each SQL statement sample in the training sample set is divided into five complexity levels according to the complexity score. The score range, core features, and corresponding preset sample proportions of each complexity level are shown in the table below: When the proportion of high-complexity samples in the training sample set is too high, for example, the proportion of samples with nested queries and multi-table joins exceeds 30%, the improved LTTB algorithm is used to downsample the high-complexity samples in the training sample set. The improved LTTB algorithm uses the complexity score as one of the representative measurement dimensions for sampling and the business feature vector as another representative measurement dimension, so that the retained samples are both representative in terms of complexity and in line with business requirements.

[0049] Please see Figure 6 , Figure 6 This diagram illustrates the comparison of SQL sample distribution complexity before and after the improved LTTB downsampling provided in this embodiment. Figure 6 The left side of the chart shows the sample distribution before downsampling in the form of a bar chart. The vertical axis represents the number of samples, and the horizontal axis represents the five complexity levels of single-table query, multi-condition filtering, aggregation statistics, related query and nested query. The height of the bars shows the skewed state of the high complexity sample ratio. Figure 6 The right side of the chart displays the sample distribution after downsampling in the form of a bar chart. The vertical and horizontal axes are labeled in the same way as the left side. The height of the bars shows the balanced state of the sample proportion of each complexity level, which conforms to the preset distribution.

[0050] This embodiment downsamples the training sample set based on two dimensions: complexity score and business feature vector. This downsampling process simultaneously considers the syntactic complexity of the SQL statement samples and the business scenario coverage of the training samples, avoiding business scenario bias caused by downsampling based on a single complexity dimension. It ensures that the proportion of training samples at each complexity level in the training sample set meets a preset distribution, laying the foundation for stable output of the model under different complexity query scenarios. The LTTB-type downsampling algorithm reduces the sample size while retaining core features, making the overhead of the downsampling process controllable.

[0051] In one embodiment, this application provides a training method for an SQL generation model for the river chief system in water conservancy. The training method includes steps S1 to S5. The training sample set further includes at least one negative sample, and the SQL statement corresponding to the negative sample is marked as empty. The at least one negative sample includes any one of the following: unauthorized query sample, privacy information query sample, and query sample exceeding the preset business scope.

[0052] After the basic samples of the natural language question samples and the SQL statement samples are generated, at least one negative sample is introduced into the training sample set. The specific types of negative samples include unauthorized queries, personal privacy inquiries, and queries exceeding the business scope. The ratio of negative samples to positive samples is set to 1:10, a setting designed to ensure the model can identify business boundaries without affecting the training effect of positive samples. For each negative sample, its corresponding SQL statement is labeled as "NULL," thereby training the model to identify business boundaries. After recognizing the query corresponding to the negative sample, the fine-tuned language model returns a fixed prompt message: "The current query exceeds the business scope of the water conservancy river chief system and cannot generate SQL."

[0053] In this embodiment, at least one of the following is introduced as a negative sample in the training sample set: unauthorized query samples, privacy information query samples, and query samples exceeding the preset business scope. This ensures that the training sample set covers the sample types required for business boundary identification. The SQL statements corresponding to the negative samples are marked as empty, enabling the model to learn the ability to determine that corresponding inputs should not generate SQL statements during training. The ratio of negative samples to positive samples is set to 1:10, so that the model retains its business boundary identification ability without affecting the training effect of positive samples, and avoids the trained model generating SQL statements that do not conform to business specifications.

[0054] In one embodiment, this application provides a training method for an SQL generation model oriented towards the river chief system for water conservancy, the training method including steps S1 to S5; wherein, before performing pattern linking processing on each sample in the training sample set based on a business knowledge graph, the training method further includes: S21: Construct the business knowledge graph using the data tables in the target database as nodes and the relationships between the data tables as edges; The pattern linking process includes: S22: Map the natural language questions in the training sample set to the corresponding concept nodes in the business knowledge graph; S23: Perform a bounded search along the edges in the business knowledge graph to obtain at least one data table node related to the natural language problem; The weights of the edges in the business knowledge graph satisfy the following: (2) in, Let be the weight of the edge, and d be the number of hops in the shortest path from the corresponding concept node to the target data table node. For user issues With table Semantic cosine similarity between metadata Here, α is the cost factor for the SQL execution plan, β is the decay rate parameter, and λ is the cost penalty coefficient.

[0055] The business knowledge graph constructed in step S21 uses target database tables (e.g., RIVER_CHIEF, RIVER_SEG, EVENT_DISPOSAL) as nodes and foreign key relationships and business semantic associations between tables as edges. It encodes unique business concepts of the water conservancy river chief system, such as statistical metrics like "river section patrol rate," "event handling timeliness," and "cross-regional coordination," into path patterns in the graph. Examples include a four-hop path of "river chief → managed river section → patrol record → event handling" and a three-hop path of "river section → water quality index → ​​compliance statistics." The initial construction of the business knowledge graph is based on the water conservancy river chief system business specifications and database table structure. Subsequent updates to the business knowledge graph are driven by new table association patterns discovered during data synthesis, creating a bidirectional driving force.

[0056] Step S22 uses a lightweight business intent recognition module to map the natural language question to the corresponding concept node in the business knowledge graph. Step S23 performs a bounded breadth-first search along the edges of the business knowledge graph to obtain all relevant data tables and their core fields associated with the corresponding concept node. During the search process, an edge weight decay function is introduced so that the relevance weight of data tables farther away from the business concept decays exponentially, thereby controlling the token overhead of the Prompt while ensuring a high recall rate.

[0057] The balance coefficient α is used to control the trade-off between graph structural distance and text semantic similarity. The preferred value of α is 0.6, with a range of 0.5 to 0.8. The preferred value of the attenuation rate parameter β is 0.3, with a range of 0.2 to 0.5. The preferred value of the cost penalty coefficient λ is 0.2, with a range of 0.1 to 0.3. The sim(q, The text was vectorized using the Sentence-BERT model and then calculated using cosine similarity. It is obtained from feedback in the inference execution phase and is used to apply exponential penalties to high-cost association paths to avoid generating high-efficiency SQL in the future.

[0058] When the weight of the edge When the weight is below a preset threshold θ, the corresponding data table is removed from the candidate set. That is, N in the Top-N candidate set is the number of data tables with a weight higher than θ, achieving dynamic adjustment. The preferred value of the preset threshold θ is 0.4, with a range of 0.3 to 0.6. The weight of the edge... The calculation results are synchronously fed back to the contrastive learning training, which is used to dynamically and adaptively generate the contrastive marginal threshold, thereby realizing the coupling between the semantic layer and the parameter layer.

[0059] Please see Figure 4 , Figure 4This diagram illustrates the semantic projection filtering algorithm provided in this embodiment. Figure 4 The process is presented from top to bottom in seven steps: natural language question input, business intent recognition, concept node mapping, bounded breadth-first search, edge weight decay calculation, candidate data table weight comparison and threshold elimination, and candidate data table set output. These seven steps are connected in sequence to form a complete pathway for pattern linking processing. Among them, the candidate data table weight comparison and threshold elimination step has a feedback path pointing to the contrastive learning training parameter generation step, which represents the connection relationship where the edge weight calculation results are synchronously fed back to the contrastive learning training.

[0060] Please see Figure 7 , Figure 7 This diagram illustrates the business knowledge graph provided in this embodiment. Figure 7 In the diagram, circular nodes represent the data tables in the target database, and the connections between nodes represent the relationships between the data tables. Multiple connections converge on the same node to reflect the semantic structure of multi-table cascading queries. Figure 7 The example uses a four-step path of "river chief → managed river section → patrol record → incident handling" and a three-step path of "river section → water quality index → ​​compliance statistics" as path patterns. The direction of the arrows on the path reflects the direction of foreign key relationships or business semantic associations.

[0061] This embodiment constructs the business knowledge graph using data tables in the target database as nodes and the relationships between data tables as edges. This allows the business knowledge graph to directly map the structure and business relationships of the target database, avoiding reliance on external knowledge sources. The natural language problem is mapped to the corresponding concept node in the business knowledge graph, and a bounded search is performed along the edges, enabling the pattern linking process to obtain data table nodes semantically related to the natural language problem. The edge weights are designed as a combination of graph structure distance decay, semantic similarity, and SQL execution cost, ensuring that the selection of candidate data tables simultaneously considers structural distance, business relevance, and execution efficiency. Exponential decay is applied to data tables far from business concepts, and exponential penalties are applied to high-cost association paths, keeping the candidate data table set at a reasonable size and saving Prompt token overhead. The edge weight calculation results are fed back to the generation stage of the comparative learning training parameters, extending the execution cost awareness of pattern linking to the adjustment of training parameters.

[0062] In one embodiment, based on the aforementioned business knowledge graph and pattern linking processing results, this application further designs a target business-oriented Prompt template. The Prompt template includes three parts: system prompts, user input templates, and output format constraints. The Prompt template is fully adapted to the target business and target database syntax.

[0063] The system prompt is as follows: You are an expert proficient in the water conservancy and river chief system business and the SQL syntax of the DM database. Based on the provided database table structure, you need to convert natural language business requirements into standard SQL statements that can be directly executed in the DM database. Requirements are: 1. Strictly adhere to the DM database syntax specifications and use DM-specific functions (e.g., date function DATEADD_S, string concatenation function ||, aggregate function WM_CONCAT); 2. Accurately map water conservancy and river chief system business terminology (e.g., "river section disposal" corresponds to the event_disposal table, "township-level river chief" corresponds to LEVEL_NAME='township' in the RIVER_CHIEF table); 3. The SQL statement must include necessary WHERE conditions and GROUP BY and HAVING clauses, and field aliases must use Chinese characters and be consistent with the business requirements; 4. Do not add any SQL comments or explanatory text, only output the SQL statement, and wrap it in a Markdown code block.

[0064] The user input template adopts a structured text format and includes two parts: database table structure information and water conservancy river chief system business requirements. An example of the user input template is as follows: User input template (JSON format): ### Database table structure information {table_schema} ### Water Conservancy River Chief System Business Needs {natural_language_question} In the above user input template, `table_schema` is a placeholder used to fill in the table structure information of the target database; `natural_language_question` is a placeholder used to fill in the user's natural language query question.

[0065] Examples of fields in each table of the database table structure information are as follows: Table name: RIVER_CHIEF (River Chief Details Table) Fields: ID (unique identifier ID, VARCHAR2(32)), NAME (personnel name, VARCHAR2(255)), LEVEL_ID (river chief level, INTEGER), LEVEL_NAME (river chief level name, VARCHAR2(90)), RIVER_SEG_ID (river section ID, VARCHAR2(255)), RIVER_SEG_NAME (river section name, VARCHAR2(255)), ADM_POS (administrative position, VARCHAR2(90)) Table name: RIVER_SEG (River Section Details Table) Fields: ID (unique identifier ID, VARCHAR2(64)), NAME (river segment name, VARCHAR2(90)), RIVER_SEG_ID (river segment code, VARCHAR2(90)), RIVER_NAME (river name, VARCHAR2(90)), LENGTH (river segment length, DECIMAL(24,6)) Table name: EVENT_DISPOSAL (Event Handling Table) Fields: event_id (event ID, VARCHAR(20)), RIVER_SEG_ID (associated river segment ID, VARCHAR(255)), create_time (creation time, DATETIME), disposal_status (disposal status, VARCHAR(8)), disposal_content (disposal content, VARCHAR(500)) This embodiment implements an automated conversion process from JSON samples to training samples based on the HuggingFace datasets library: The original samples are loaded to obtain a dataset containing three fields: natural_question, standard_sql, and table_schema; table_schema and natural_question are concatenated according to the user input template to generate the model input text; standard_sql is wrapped into Markdown code blocks according to the output format constraints and used as training labels; the input text and labels are encoded using a tokenizer to generate input_ids, attention_mask, and labels.

[0066] In one embodiment, this application provides a training method for an SQL generation model oriented towards the river chief system for water conservancy, the training method including steps S1 to S5; wherein, the step of fine-tuning the pre-trained language model using contrastive learning includes: S31: For the SQL statement samples in the training sample set, generate corresponding pseudo-negative samples according to the preset dialect conversion rules. S32: Train the pre-trained language model based on the SQL statement samples, the corresponding pseudo-negative samples, and the training parameters of the contrastive learning.

[0067] For each positive SQL sample from the target database in the training sample set By calling the mapping rule library from "target database syntax to general SQL syntax" through the rule engine, the corresponding pseudo-negative samples are automatically generated. The pseudo-negative samples maintain the query semantics but replace the target database-specific syntax with general SQL syntax. Examples of the dialect conversion rules include: replacing DATEADD_S(DAY, -7, CURRENT_DATE) with DATE_SUB(CURRENT_DATE,INTERVAL 7 DAY); and replacing the string concatenation operator "||" with a CONCAT function call. Simultaneously, a dynamic adaptive contrast margin threshold is introduced. This dynamic threshold is derived from the SQL complexity score extracted by the previously improved LTTB algorithm. With knowledge graph edge weights Coupling generation.

[0068] The preset dialect conversion rules used in step S31 reflect the systematic differences between the target database and the general open-source database in date functions, string concatenation operators, and other syntactic aspects, enabling the generated pseudo-negative samples to cover common confusion types of the model at the syntactic level of the target database. The preset dialect conversion rules used in step S31 are expanded based on the error patterns accumulated during the self-correction phase, allowing the comparison signals in the next round of fine-tuning to more accurately cover actual high-frequency error types.

[0069] In this embodiment, the target database-specific syntax in the positive SQL samples of the target database is replaced with general SQL syntax according to a preset dialect conversion rule. This automatically generates pseudo-negative samples that maintain the same query semantics, allowing for large-scale acquisition of these pseudo-negative samples without manual annotation and enabling accurate grammatical comparison with the positive samples. The pre-trained language model is trained based on the SQL statement samples, the corresponding pseudo-negative samples, and the training parameters of the contrastive learning. During training, the model learns to distinguish between the target database-specific syntax and general SQL syntax, preventing the model from misusing general SQL syntax in the target database during generation. Error patterns accumulated during the self-correction phase are fed back to expand the preset dialect conversion rule, ensuring that the contrast signal continuously covers high-frequency error types as training iterates.

[0070] In one embodiment, this application provides a training method for an SQL generation model oriented towards the river chief system for water conservancy. The training method includes steps S1 to S5 and steps S31 to S32; wherein the training parameters of the contrastive learning include an adaptive margin and a loss function; the adaptive margin satisfies the following relationship: (3) in, For the adaptive margin, The baseline constant of the preset boundary for the algorithm, The score is the space structure complexity of the corresponding SQL statement. γ is the edge weight decay value dynamically generated by the target core business table during the bounded search phase of the knowledge graph, and γ is the smoothing control coefficient that coordinates the proportion of the two cross-domain features in the penalty adjustment. The loss function satisfies: (4) in, Let the loss function be... For cross-entropy loss term, As a positive sample, These are pseudo-negative samples. and These represent the generation probabilities of the pre-trained language model for positive samples and pseudo-negative samples, respectively. λ1 is the cost factor for the SQL execution plan, λ2 is the weight coefficient for the comparative loss, and λ3 is the weight for the execution cost reward.

[0071] In this embodiment, the basic marginal constant The preferred value for the smoothing control coefficient γ is 0.5, and the preferred value for the contrast loss weighting coefficient is 0.1. The preferred value is 0.3, and the execution cost reward weight is... The preferred value is 0.2, the Normalized to the 0-1 interval. The adaptive margin... SQL complexity score extracted from the previously improved LTTB algorithm Edge weights of the business knowledge graph Coupled generation. The aforementioned By superimposing a comparative marginal loss term on the standard cross-entropy loss and incorporating a reward signal that is aware of the SQL execution cost, the training process is coupled with the inference execution process.

[0072] In this embodiment, the adaptive margin is designed as a function of complexity score and business knowledge graph edge weights. This allows the adaptive margin to be dynamically adjusted based on the complexity of the samples and the execution characteristics of the pattern information. A larger contrast interval is required for samples with high complexity and high execution cost, while a smaller contrast interval is required for samples with low complexity and low execution cost. An execution cost reward term is introduced into the loss function, so that the training process rewards samples that generate highly efficient SQL statements and penalizes samples that generate highly inefficient SQL statements. The cross-entropy loss term, contrast margin loss term, and execution cost reward term are superimposed to form the total loss function, enabling the training process to simultaneously optimize three objectives: grammatical correctness, dialect discrimination, and execution efficiency.

[0073] In one embodiment, this application configures the execution environment and training parameters for model fine-tuning based on the aforementioned adaptive margin and loss function. This embodiment performs model fine-tuning based on the FastLanguageModel encapsulated by unsloth, selecting a pre-trained language model with basic code and SQL generation capabilities as the base model, such as Qwen-2.5 Coder 7B as the primary fine-tuning target, and using Qwen-34B to verify the impact of model architecture differences on performance. The core training parameters include: a maximum sequence length of 8192; a learning rate of 2e-5 with a linear decay strategy; three training epochs, chosen to ensure the model fully learns business features while avoiding overfitting; full parameter fine-tuning to preserve the model's complete expressive power, while gradient checkpointing and bf16 accuracy optimization are enabled to alleviate memory pressure; lightweight methods such as LoRA are not used because the water conservancy and river chief system business scenarios are complex, and full parameter fine-tuning can better enable the model to learn business semantics and the target database syntax features, ensuring the accuracy of generated SQL statements; and a total of 880 training data entries, covering various complexity levels and business scenarios.

[0074] In one embodiment, this application provides a training method for an SQL generation model oriented towards the river chief system for water conservancy, the training method including steps S1 to S5 and step S14; wherein, downsampling the training sample set includes: S141: Sort the training sample set in ascending order of complexity score of each SQL statement sample, and divide the sorted training sample set into N consecutive buckets; S142: For the i-th bucket, the center point of the sampling result of the (i-1)-th bucket is used as the front reference point, and the center point of all samples in the (i+1)-th bucket is used as the back reference point; wherein, the first bucket and the last bucket both use the sample corresponding to the median of the complexity score as the reference point; S143: Based on the area of ​​the triangle formed by each sample point in the i-th bucket, the previous reference point, and the next reference point, select the sample point with the largest triangle area from the i-th bucket as the sampling result of the i-th bucket, where i is an integer between 1 and N.

[0075] To address the time-series data adaptation characteristics of the original LTTB algorithm, this embodiment makes the following modifications to the original LTTB algorithm: The time axis X of the original LTTB algorithm is mapped to an SQL complexity score. Map the numerical axis Y of the original LTTB algorithm to the sample business feature vector V; determine the reference points for the front and rear buckets according to step S142; the number of buckets N is equal to the number of high-complexity samples after target sampling.

[0076] For any sample point P within the bucket, the area of ​​the triangle formed by P, the previous reference point A, and the subsequent reference point B is calculated according to the following relationship: (5) in, Let the area of ​​the triangle be... , , These are the complexity scores for the former reference point A, the latter reference point B, and the sample point P, respectively. , , , These are the L2 norms of the business feature vector V corresponding to the front reference point A, the back reference point B, and the sample point P, respectively; the larger the area of ​​the triangle, the stronger the representativeness of the corresponding sample in both the complexity and business feature dimensions.

[0077] Please see Figure 6 , Figure 6 This diagram illustrates the comparison of SQL sample distribution complexity before and after the improved LTTB downsampling provided in this embodiment. Figure 6 The left side of the chart displays the sample distribution before downsampling in the form of a bar chart. The horizontal axis of the bar chart represents five complexity levels: single-table query, multi-condition filtering, aggregation statistics, related queries, and nested queries. The vertical axis represents the number of samples. The difference in the height of the bars reflects the skewness of the sample distribution at each complexity level before downsampling. Figure 6 The right side of the chart displays the sample distribution after downsampling in the form of a bar chart. The horizontal and vertical axes are labeled in the same way as on the left side, and the difference in the height of the bars reflects the degree of balance in the sample distribution after downsampling.

[0078] In this embodiment, the training sample set is divided into N consecutive buckets after being sorted in ascending order of complexity score. This allows the downsampling process to operate based on the local structure within each bucket, avoiding the computational overhead of global sorting and comparison. The center point of the sampling result of the previous bucket is used as the front reference point, all sample center points of the next bucket are used as the back reference points, and the median complexity sample is used as the reference point for the first and last buckets. This ensures that the downsampling reference standards of each bucket are continuous within the bucket and correlated between buckets, conforming to the forward traversal structure of sampling in ascending order of complexity score. The sample result is based on the largest area of ​​the triangle formed by the sample point and the front and back reference points, ensuring that the retained samples are both representative of complexity and meet the business characteristics requirements.

[0079] In one embodiment, this application provides a training method for an SQL generation model oriented towards the river chief system for water conservancy. The training method includes steps S1 to S5 and step S14. The business feature vector is generated based on at least two types of features. The at least two types of features include the business scenario category corresponding to the training sample and the grammatical structure category contained in the SQL statement sample. The business feature vector is obtained by concatenating the at least two types of features after one-hot encoding.

[0080] The business feature vector is a 12-dimensional vector, corresponding to six major business scenarios and six types of SQL syntax features related to the river chief system in water conservancy. The six major business scenarios include river basic information query, event handling statistics, river chief performance query, cross-regional data aggregation, water quality index analysis, and equipment operation and maintenance query. The six types of SQL syntax features correspond to different syntactic structures of SQL statements. The business feature vector is generated by one-hot encoding of the six major business scenarios and the six types of SQL syntax features, ensuring that the sampling process simultaneously considers complexity distribution and business scenario coverage.

[0081] In this embodiment, the business feature vector is designed as a composite encoding result of two types of features: business scenario category and SQL syntax structure category. This allows the business feature vector to simultaneously reflect the characteristics of the training samples in both the business and syntax dimensions. By concatenating the two types of features after one-hot encoding, each dimension of the business feature vector has a clear semantic interpretation, facilitating its use as a representative measure during downsampling. By simultaneously incorporating both the business scenario category and SQL syntax structure category dimensions into the vector encoding, the downsampling process gains another independent representative measure dimension in addition to the complexity scoring dimension, thereby improving the sample diversity of the sampling results.

[0082] In one embodiment, this application provides a training method for an SQL generation model oriented towards the river chief system for water conservancy, the training method comprising steps S1 to S5; wherein, the iterative execution of fine-tuning until the SQL generation model is obtained includes: S51: After each round of fine-tuning, the fine-tuned language model is evaluated to obtain the pass rate and average execution cost of the fine-tuned language model. S52: When the increase in pass rate is lower than a first preset threshold and the decrease in average execution cost is lower than a second preset threshold in multiple consecutive rounds of evaluation, the iteration ends and the current fine-tuned language model is used as the SQL generation model.

[0083] Step S51 employs a third-party large model (e.g., Qwen-34B) as an automated scorer to evaluate the SQL statements generated by the fine-tuned language model. The inputs to the scorer include natural language questions, standard SQL statements and their execution results, model-generated SQL statements and their execution results, and SQL execution costs. The evaluation uses a four-level scoring standard: a score of 80 to 100 indicates accuracy, meaning the corresponding SQL statement fully meets business requirements and the execution result is correct. ≤0.3 indicates high execution efficiency; scores of 50 to 79 indicate basic accuracy, with slight deviations in semantics or statistical methods that do not affect core business requirements; 0.3 < A score ≤0.6 indicates moderate execution efficiency; scores between 21 and 49 indicate SQL errors, corresponding to semantic or syntax errors that result in results that do not meet business requirements. A score >0.6 indicates extremely low execution efficiency; scores from 0 to 20 indicate serious errors, corresponding to SQL statements that cannot be executed or are completely irrelevant to business requirements. The scorer also outputs detailed score explanations. The score boundaries of the four-level scoring standard are set based on the business requirements for SQL accuracy, focusing on distinguishing between usable (50 points and above) and unusable (below 50 points), while also refining the difference between completely accurate and basically usable. The pass rate is defined as the proportion of samples corresponding to accurate scores and basically correct scores out of the total sample. The evaluation results are written to an Excel file, and different scoring levels are marked with color, such as green for accurate scores and red for serious errors, for easy browsing; the pass rate of each model and the sample distribution of each scoring level are statistically analyzed to provide quantitative data support for model optimization.

[0084] In step S52, the first preset threshold is preferably 3%, and the second preset threshold is preferably 5%. That is, the iteration ends when the pass rate of the consecutive rounds of evaluation increases by no more than 3% and the average execution cost decreases by no more than 5%. The specific operation process of each round of iteration includes: evaluation result analysis, data generation optimization (supplementing low-scoring scenarios and high-efficiency scenario samples), model re-fine-tuning (updating dynamic coupling parameters), inference verification (synchronously extracting execution cost), and re-evaluation.

[0085] In this embodiment, after each round of fine-tuning, the fine-tuned language model is evaluated to obtain two quantitative indicators: pass rate and average execution cost, so that the iterative process has a quantitative measure of progress. The increase in pass rate and the decrease in average execution cost over multiple rounds are used as the termination condition, so that the iteration termination judgment takes into account both the correctness of SQL generation and the execution efficiency, avoiding the bias caused by a single dimension judgment. The current fine-tuned language model is used as the output of the SQL generation model when the termination condition is triggered, so that the training process has a clear convergence endpoint.

[0086] In one embodiment, this application provides a training method for an SQL generation model oriented towards the river chief system for water conservancy. The training method includes steps S1 to S5; wherein, step S4 specifically includes: submitting the cleaned SQL statement to the target database for execution, and simultaneously calling the underlying EXPLAIN command of the target database to parse the physical operator overhead and time cost factor during the execution of the SQL statement. , will the Normalized to the 0-1 range for subsequent business knowledge graph weight updates and model fine-tuning optimization. For query-type SQL, such as river chief information queries and river issue statistics, the execution results are converted into JSON format for comparison and analysis; for write operation SQL such as update and insert, such as river chief information updates, the number of rows affected by the execution is recorded, and a transaction rollback mechanism is set to prevent impact on production data.

[0087] If the SQL statement fails to execute, the error type (including syntax error, permission error, and timeout error) and error context information are recorded. Differentiated handling strategies are adopted for different error types: syntax errors trigger secondary correction first; permission errors return a "no corresponding query permission" message; timeout errors are resolved by optimizing the SQL statement and re-executing. The secondary correction process includes capturing the error stack, constructing an auxiliary prompt "Generated SQL execution error: [Error message], please regenerate SQL conforming to the syntax of the DM database based on this error, strictly using DM-specific functions, avoiding the use of open-source database functions, and optimizing execution efficiency to avoid full table scans," triggering the fine-tuned language model to perform secondary correction generation. The maximum number of secondary corrections is limited to 2. If execution still fails after two corrections, an execution failure message is returned. All generated SQL statements, execution results, error messages, and... All values ​​are synchronously written to an Excel file to form a complete execution record, which facilitates subsequent quantitative evaluation and model optimization.

[0088] In one embodiment, this application provides a training method for an SQL generation model oriented towards the river chief system for water conservancy. The training method includes steps S1 to S5; wherein, the execution cost feedback in step S5 is used for updates in multiple directions, specifically including: feeding back the error patterns accumulated in the self-correction phase to the preset dialect conversion rule base in step S31, so that the comparison signal in the next round of fine-tuning more accurately covers the actual high-frequency error types; and ... The values ​​are fed back to the business knowledge graph, and the weights of table association edges are adjusted through a dynamic cost penalty decay function to achieve dynamic evolution of the business knowledge graph and avoid the generation of high-cost SQL in the future. High-cost SQL samples are fed back to the data generation process in step S1 to supplement the training samples for this type of scenario and strengthen the model's learning of high-cost SQL generation. New table association patterns discovered during the execution process are fed back to the business knowledge graph and the data generation process to achieve bidirectional optimization.

[0089] Please see Figure 5 , Figure 5 This diagram illustrates the closed-loop optimization iterative process provided in this embodiment. Figure 5 The diagram uses five rectangles to represent the five stages of evaluation result analysis, data generation optimization, model fine-tuning, inference verification, and re-evaluation. These five stages form a closed loop, with a feedback path connecting the middle of the loop to the graphic icons of the business knowledge graph and the preset dialect conversion rule base. The arrows on the feedback path indicate the transmission direction of execution cost feedback, error mode feedback, and new table association mode feedback between the five stages and the business knowledge graph and the preset dialect conversion rule base.

[0090] In other embodiments, the pre-trained language model can be an open-source model with code generation capabilities, such as DeepSeek-Coder or CodeLlama, or a domestic large language model, such as Baichuan Intelligent Agent or Wenxin Yiyan; the learning rate of the fine-tuning process can be adjusted in the range of 1e-5 to 3e-5 according to the characteristics of the selected model, and the training epochs are kept at 3.

[0091] In other embodiments, the generation of the natural language question samples can adopt a few-sample generation method guided by the business knowledge graph, which automatically generates complex query samples with multiple table associations through business association paths in the business knowledge graph, replacing the variable substitution method based on fixed sentence templates; the ratio of negative samples to positive samples can be adjusted within the range of 1:8 to 1:15, and the type of negative samples can be expanded to at least one of syntax error samples, business logic error samples, and semantic ambiguity samples.

[0092] In other embodiments, the data cleaning can adopt a structured cleaning method based on SQL syntax parse tree, which extracts the core SQL execution logic and removes redundant format and irrelevant text through a syntax parser, in order to replace the three-level cleaning process based on regular expressions; the execution order of the cleaning process can be adjusted according to the sample format, and the effective sample rate after the cleaning is not less than 95%.

[0093] In other embodiments, the bounded search along the edges of the business knowledge graph to the corresponding concept node can be replaced by any one of depth-first search, shortest path-first search, or node relevance ranking search based on graph neural networks; the nodes of the business knowledge graph can be extended to at least one of field-level nodes and business statistical nodes in addition to data table nodes and business concept nodes, and the edge type can be extended to at least one of field association edges, business membership edges, and statistical dependency edges in addition to foreign key relationship edges.

[0094] In other embodiments, the edge weights in the business knowledge graph can be any one of the following: a dynamic decay function based on business scenario popularity, an adaptive decay function based on historical query hit rate, or an automatic edge weight calculation mechanism based on graph attention networks, to replace the dynamic cost penalty decay function described in formula (2); the sim(q, The calculation of ) can be performed using the TF-IDF model, BGE model or Text2vec model to achieve text vectorization, in order to replace the Sentence-BERT model.

[0095] In other embodiments, the secondary correction process of the fine-tuned language model can adopt a multi-round self-correction mechanism based on error type classification, or an error correction method based on retrieval enhancement, injecting solutions and optimization cases of high-frequency errors into the auxiliary prompt; the maximum number of corrections in the secondary correction can be adjusted within the range of 1 to 3 times; the execution result of the SQL statement can be output in a structured table format or plain text format in addition to JSON format; for write operation SQL statements, in addition to the transaction rollback mechanism, a test environment pre-execution method can be adopted to avoid affecting production business data.

[0096] In other embodiments, the evaluation of the SQL statements generated by the fine-tuned language model in step S51 can be carried out by any one of the following: automated evaluation based on the consistency of execution results, manual review and scoring based on the matching degree of business semantics, or performance scoring based on SQL execution efficiency. The final score is calculated by weighting multiple dimensions of indicators to replace the four-level automated scoring system based on the third-party large model. The automated evaluation based on the consistency of execution results includes result set comparison and execution success rate statistics. The score boundaries of the four-level scoring standard can be adjusted according to the business requirements for SQL accuracy.

[0097] In one embodiment, taking the river chief system business scenario as an application example, the complete field definitions of a typical business data table in the target database are as follows. The field definitions of the RIVER_CHIEF river chief details table are shown in the following table: The field definitions of the RIVER_SEG river segment details table are shown in the table below: In one embodiment, the effectiveness of the method described above in the water conservancy river chief system operational test set is illustrated below. The following table compares the pass rates and score distribution of the original Qwen2.5Coder7B model, the Qwen3-Max model, and the model fine-tuned using the method of this application on the test set: As shown in the table above, after fine-tuning the method described in this application, the model with a smaller number of parameters achieved a pass rate on the test set comparable to that of a model with trillions of parameters. The pass rates for different complexity scenarios are as follows: single-table query pass rate 92%, join query pass rate 85%, aggregation statistics pass rate 80%, multi-condition filtering pass rate 88%, and nested query pass rate 70%. The comparison of the target database-specific syntax error rates before and after fine-tuning is as follows: error rate before fine-tuning 48%, error rate after fine-tuning 9%. Through the execution cost awareness mechanism, the average execution cost of the generated SQL statements is reduced by 40%, avoiding high-efficiency execution methods such as full table scans, and adapting to the query needs of massive business data.

[0098] The following are some typical Q&A examples of applying the method of this application to a water conservancy river chief system business scenario.

[0099] Example 1: The natural language question is "Which river sections in xx city have experienced incidents requiring handling?", and the corresponding SQL statement is: Correct SQL: SELECT DISTINCT RIVER_SEG_NAME FROM EVENT_DISPOSAL WHERE CITY = 'xx city' Example 2: The natural language question is "How many different river sections have reported emergencies in the past 6 months?", and the corresponding SQL statement is: Correct SQL: SELECT COUNT(DISTINCT RIVER_SEG_NAME) FROM EVENT_DISPOSAL WHERE SEVERITY = 'Emergency' AND REPORT_TIME >= DATEADD(MONTH, -6, CURRENT_DATE) Example 3: The natural language question is: "Which river chiefs actually patrolled the river twice within a certain statistical period? Please sort the top 20 river chiefs by statistical period from most recent to oldest and by actual patrol count from highest to lowest, listing their names, level IDs, level names, actual patrol counts, and statistical periods." The corresponding SQL statement is: Correct SQL: SELECT DISTINCT NAME, LEVEL_ID, LEVEL_NAME, ACTUAL_NUMBER, DATE FROM PATROL_CHIEF WHERE ACTUAL_NUMBER = 2 ORDER BY DATE DESC, ACTUAL_NUMBER DESC LIMIT 20 The above embodiments are only used to illustrate the technical solutions of this application, and are not intended to limit them. Although this application has been described in detail with reference to the foregoing embodiments, those skilled in the art should understand that modifications can still be made to the technical solutions described in the foregoing embodiments, or equivalent substitutions can be made to some of the technical features. Such modifications or substitutions do not cause the essence of the corresponding technical solutions to deviate from the spirit and scope of the technical solutions of the embodiments of this application, and should all be included within the protection scope of this application.

Claims

1. A training method for an SQL generation model oriented towards the river chief system for water conservancy, characterized in that, include: Obtain a training sample set for the target database; Based on the business knowledge graph, pattern linking processing is performed on each sample in the training sample set to obtain pattern information corresponding to the natural language questions in the training sample set; wherein, the weight of at least one edge in the business knowledge graph is related to the SQL execution cost on the path corresponding to that edge; Based on the training sample set and the pattern information, contrastive learning is used to fine-tune the pre-trained language model; wherein, the training parameters of the contrastive learning are at least related to the sample complexity score and the weights of the edges in the business knowledge graph; Obtain the execution cost feedback of the SQL statement output by the fine-tuned language model in the target database; Based on the execution cost feedback, at least one of the weights of the edges in the business knowledge graph and the training parameters of the contrastive learning is updated, and the fine-tuning is performed iteratively until the SQL generation model is obtained.

2. The training method according to claim 1, characterized in that, The process of obtaining the training sample set for the target database includes: For at least one pre-defined business scenario, generate natural language question samples based on at least one variable and at least one sentence template corresponding to that business scenario; Based on the natural language question sample and the syntax rules of the target database, generate SQL statement samples corresponding to the natural language question sample; The natural language question samples and the SQL statement samples are used as training sample pairs and added to the training sample set.

3. The training method according to claim 2, characterized in that, Before using the natural language question sample and the SQL statement sample as training sample pairs, the training method further includes: Data cleaning is performed on the natural language problem samples and the SQL statement samples; Perform at least one of the following checks on the cleaned SQL statement sample: field integrity check, length check, and SQL syntax check; The natural language question samples and the SQL statement samples that pass the verification are used as the training sample pairs.

4. The training method according to claim 2, characterized in that, The complexity scores of each SQL statement sample in the training sample set satisfy the following: in, Score the complexity. The number of related data tables in the SQL statement sample. The number of filter conditions in the SQL statement sample. The number of aggregate functions in the SQL statement sample. The number of nested subqueries in the SQL statement sample. , , , All of these are preset weighting coefficients.

5. The training method according to claim 4, characterized in that, After obtaining the training sample set for the target database, the training method further includes: Based on the complexity score of each SQL statement sample and the corresponding business feature vector, the training sample set is downsampled so that the proportion of training samples of each complexity level in the training sample set meets the preset distribution.

6. The training method according to claim 1 or 2, characterized in that, The training sample set also includes at least one negative sample, and the SQL statement corresponding to the negative sample is marked as empty; The at least one negative sample includes any one of the following: unauthorized query sample, privacy information query sample, and query sample that exceeds the preset business scope.

7. The training method according to claim 1, characterized in that, Before performing pattern linking processing on each sample in the training sample set based on the business knowledge graph, the training method further includes: The business knowledge graph is constructed using the data tables in the target database as nodes and the relationships between the data tables as edges. The pattern linking process includes: Map the natural language questions in the training sample set to the corresponding concept nodes in the business knowledge graph; A bounded search is performed along the edges of the business knowledge graph on the corresponding concept nodes to obtain at least one data table node related to the natural language problem. The weights of the edges in the business knowledge graph satisfy the following: in, Let be the weight of the edge, and d be the number of hops in the shortest path from the corresponding concept node to the target data table node. For user issues With table Semantic cosine similarity between metadata Here, α is the cost factor for the SQL execution plan, β is the decay rate parameter, and λ is the cost penalty coefficient.

8. The training method according to claim 1, characterized in that, The fine-tuning of the pre-trained language model using contrastive learning includes: For the SQL statement samples in the training sample set, corresponding pseudo-negative samples are generated according to preset dialect conversion rules. The pre-trained language model is trained based on the SQL statement samples, the corresponding pseudo-negative samples, and the training parameters of the contrastive learning.

9. The training method according to claim 7 or 8, characterized in that, The training parameters for the contrastive learning include adaptive margins and a loss function; Wherein, the adaptive margin satisfies: in, For the adaptive margin, The baseline constant of the preset boundary for the algorithm, The score is given for the spatial structure complexity of the corresponding SQL statement. γ is the edge weight decay value dynamically generated by the target core business table during the bounded search phase of the knowledge graph, and γ is the smoothing control coefficient that coordinates the proportion of the two cross-domain features in the penalty adjustment. The loss function satisfies: in, Let the loss function be... For cross-entropy loss term, As a positive sample, These are pseudo-negative samples. and These represent the generation probabilities of the pre-trained language model for positive samples and pseudo-negative samples, respectively. λ1 is the cost factor for the SQL execution plan, λ2 is the weight coefficient for the comparative loss, and λ3 is the weight for the execution cost reward.

10. The training method according to claim 5, characterized in that, The downsampling of the training sample set includes: The training sample set is sorted in ascending order of complexity score for each SQL statement sample, and the sorted training sample set is divided into N consecutive buckets. For the i-th bucket, the center point of the sampling result of the (i-1)-th bucket is used as the front reference point, and the center point of all samples in the (i+1)-th bucket is used as the back reference point; wherein, the first bucket and the last bucket both use the sample corresponding to the median of the complexity score as the reference point; Based on the area of ​​the triangle formed by each sample point in the i-th bucket, the previous reference point, and the next reference point, the sample point with the largest triangle area is selected from the i-th bucket as the sampling result of the i-th bucket, where i is an integer between 1 and N.

11. The training method according to claim 5, characterized in that, The business feature vector is generated based on at least two types of features; The at least two types of features include the business scenario category corresponding to the training sample and the syntax structure category contained in the SQL statement sample; The business feature vector is obtained by concatenating the at least two types of features after performing one-hot encoding.

12. The training method according to claim 1, characterized in that, The iterative execution of the fine-tuning until the SQL generation model is obtained includes: After each round of fine-tuning, the fine-tuned language model is evaluated to obtain the pass rate and average execution cost of the fine-tuned language model. When the improvement in the pass rate is lower than a first preset threshold and the reduction in the average execution cost is lower than a second preset threshold in multiple consecutive rounds of evaluation, the iteration ends and the current fine-tuned language model is used as the SQL generation model.

Images