Text2SQL system and control method thereof for actual power scenario

Abstract

The application discloses a Text2SQL system for an actual power scene and a control method thereof, relates to the technical field of power data query, and comprises a query template library used for storing subsequent synthetic data, a data synthesis module used for generating a semantic natural natural language query and a corresponding SQL query and storing the natural language query and the SQL query in the query template library, a data correction module used for performing difference analysis on an original SQL query generated by the data synthesis module and a correct SQL query manually labeled and correcting the original SQL query, a model fine-tuning module used for adjusting the generated SQL statement to generate an SQL query statement meeting business requirements, and an execution module used for converting a natural language query input by a user into a structured SQL statement, adjusting the SQL statement through the model fine-tuning module, querying through the query template library and returning a query result. The application further discloses a control method of the Text2SQL system for the actual power scene. The application can improve query efficiency and accuracy.

Description

Technical Field

[0001] This invention relates to the field of power data query technology, and in particular to a Text2SQL system and its control method for practical power scenarios. Background Technology

[0002] With the deepening of the digital and intelligent transformation of the power industry, massive amounts of power equipment data, operation and maintenance data, load data, and dispatch data are constantly being collected and stored in power information systems. Against this backdrop, the demand for efficient acquisition and utilization of this data by power companies and related units has exploded. Natural language queries enable operation and maintenance personnel, dispatchers, and managers to access and analyze data more conveniently, assisting in operation and maintenance management, accident analysis, and dispatch decision-making. However, current traditional power data query methods mostly rely on professionals writing SQL statements or scripts, which is unfriendly to non-technical personnel and severely restricts the release of data value. Text2SQL technology in the field of natural language processing provides a new solution for power scenarios. However, existing Text2SQL technologies are mostly concentrated on general-domain public datasets, such as Spider and WikiSQL, which differ significantly from actual power business scenarios. General models lack an understanding of the data structure, terminology, indicators, and business rules in the power field, resulting in poor implementation in the power industry. Therefore, some power information systems have introduced Large Language Models (LLMs) to convert natural language queries into SQL queries. However, there are several challenges in using LLM for Text2SQL tasks in the power sector:

[0003] 1. Complex Table and Field Information: The number of database tables involved in power scenarios is enormous, often reaching thousands. Filtering out the tables and fields relevant to the query question is challenging. It requires accurately selecting the corresponding tables and column names from the database, and then interpreting this information to aid the model's understanding. This serves as contextual clues for the next step in NL2SQL, helping the large language model perform accurate statement parsing.

[0004] 2. Insufficient Knowledge Adaptation in the Power Sector: The power sector is a vertical domain scenario, and general-purpose large-scale models suffer from a lack of domain knowledge adaptation when responding to user questions related to this domain. For example, power databases contain a large number of power-related terms, such as "distribution transformer," "number of sensitive users," and "branch line," but existing large-scale language models do not have a strong understanding of these vertical domain terms. Therefore, we need to efficiently fine-tune vertical domain knowledge into the large-scale language model.

[0005] 3. The illusion problem affects accuracy: Large language models suffer from a significant illusion problem, and NL2SQL is a task that requires high accuracy. This manifests as potentially biased generated SQL statements, such as generating non-existent variable names or characters that do not match the question, leading to SQL statements failing to execute.

[0006] 4. Limitations of real-world power scenarios: Real-world power scenarios are limited by computing power, making it difficult to deploy LLMs with extremely high parameter counts. Furthermore, there are high requirements for response time, making it difficult for large parameter models with slow inference speeds to generate responses within the specified time. Summary of the Invention

[0007] The technical problem to be solved by this invention is to provide a Text2SQL system and its control method for actual power scenarios, which can overcome the shortcomings of the existing technology, improve query efficiency and accuracy, and provide strong support for intelligent power operation and maintenance, intelligent scheduling and auxiliary decision-making.

[0008] To solve the above-mentioned technical problems, the technical solution adopted by the present invention is as follows.

[0009] A Text2SQL system for real-world power scenarios includes,

[0010] A query template library, containing natural language query styles and corresponding SQL structures, is used to store subsequent synthesized data;

[0011] The data synthesis module is used to instantiate and populate placeholders in the template using preset rules and candidate dictionaries, automatically generate semantically natural language queries and their corresponding SQL queries, and store them in the query template library;

[0012] The data correction module is used to perform difference analysis between the original SQL query generated by the data synthesis module and the correct SQL query annotated by humans, correct the original SQL query, and rewrite the new synthesized data.

[0013] The model fine-tuning module is used to adjust the generated SQL statements to produce SQL query statements that meet business requirements.

[0014] The execution module converts the user-input natural language query into a structured SQL statement, then the model fine-tuning module adjusts the SQL statement, and finally the query is performed through the query template library and the query results are returned.

[0015] A control method for the aforementioned Text2SQL system designed for real-world power scenarios includes the following steps:

[0016] Establish a query template library containing natural language query styles and corresponding SQL structures to store subsequent synthesized data;

[0017] The data synthesis module uses preset rules and candidate dictionaries to instantiate and populate placeholders in the template, automatically generating semantically natural language queries and their corresponding SQL queries, and storing them in the query template library;

[0018] The data correction module performs a difference analysis between the original SQL query generated by the data synthesis module and the correct SQL query annotated by the human, corrects the original SQL query, and rewrites the new synthesized data.

[0019] The user's natural language query input is converted into a structured SQL statement by the execution module, then the SQL statement is adjusted by the model fine-tuning module, and finally the query is performed in the query template library and the query results are returned.

[0020] As a preferred approach, the data synthesis module synthesizes data by updating templates in real time. The formal definition of the synthesis query q is: ,in This represents the distribution over the template space π, used for random template selection. It is a specific template sampled from this distribution. template The placeholders in the text, where n is the number of placeholders. It is the instantiation value of the placeholder, sampled from the conditional distribution. C represents context information. This means combining the template with the sampled values ​​to generate the final natural language query and its corresponding SQL expression. The distribution of the entire synthetic query dataset is as follows: .

[0021] Preferably, the data correction module compares the SQL queries with erroneous inferences from the model with manually annotated real SQL queries and extracts the differences based on rules as edit tags. Individual edit tags are then categorized according to their location. The input to the data correction module is defined as follows: Where q represents the user query, and s and s ’ The SQL statement is not the same as the target SQL statement. e is the edit tag, which can be M: missing, R: replacement, U: redundant, and k is the number of edit tags.

[0022] Let the parameter for freezing the data correction module be: The distillation data obtained is Then the conditional probability distribution of the data correction module for .

[0023] Preferably, the model fine-tuning module includes a 48-layer decoder that alternates between multi-head self-attention mechanisms and feedforward networks, and introduces residual connections and normalization mechanisms between layers; the normalization mechanism is described in the following formula: ,in The dimension is The real vector space, For a single location, the hidden vector, Let be the channel dimension of the model, and ε be a minimal constant used for numerical stability. For learnable parameters, channel-wise gain;

[0024] Let the input sequence be represented by X, and after normalization it be... ,set up The learnable projection matrix has H attention heads and a dimension of [missing information]. Bullish attention Represented as,

[0025] ,

[0026] in For the output of the i-th attention head, For the query, key, and value of the i-th attention head, As a causal mask, For scaling terms, This is for splicing operations.

[0027] The beneficial effects of adopting the above technical solution are as follows:

[0028] 1. This invention can abstract a general query structure from a small amount of manually labeled data and construct a standardized query template containing variable placeholders. By instantiating and filling placeholders with rules and models, synthetic data that is highly consistent with the distribution of real data is generated in batches, thereby effectively alleviating the problem of scarce manually labeled data and having the ability to quickly expand to new scenarios.

[0029] 2. This invention automatically extracts structural or semantic deviations by comparing erroneous SQL queries generated by the model with real SQL, generating edit tags such as missing, replacement, and redundancy. The teacher model, with frozen parameters, then uses these edit tags to rewrite and generate corrected data. Compared to directly rewriting queries against the target SQL, extracting edit tags accurately locates errors and their causes, guiding the teacher model to generate synthetic data consistent with the error distribution to fine-tune the student model. This mechanism enables the system to achieve data-level self-correction and continuous optimization, reducing the cost of manual intervention.

[0030] 3. This invention constructs the synthesized data into standardized instruction-output data pairs and introduces structured contextual prompts in the instruction part, including background information such as user natural language queries, time and location, as well as structured information such as database tables, fields and function descriptions. Compared with the general approach of only using database schema information and user queries as contextual prompts, this is more engineering-oriented.

[0031] 4. This invention seamlessly integrates template data synthesis, automatic correction based on imitation, and model fine-tuning into an end-to-end process, achieving automated generation from natural language to SQL queries. The system features low latency, high accuracy, and good deployability, meeting the actual business needs of the power sector and effectively improving the natural language query experience.

[0032] 5. This invention enables continuous evolution of system data and models through automatic analysis of newly added erroneous queries and iterative updates of templates / data. It can adapt to changes in power business needs and complex query structures, and maintain model performance and stability over the long term. Attached Figure Description

[0033] Figure 1 This is a schematic diagram of a specific embodiment of the present invention. Detailed Implementation

[0034] In the following description of the embodiments, 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 are omitted so as not to obscure the description of this application with unnecessary detail.

[0035] The Text2SQL system proposed in this invention for practical power scenarios includes,

[0036] The query template library contains natural language query styles and corresponding SQL structures, which are used to store subsequent synthesized data.

[0037] The data synthesis module is used to instantiate and populate placeholders in the template using preset rules and candidate dictionaries, automatically generate semantically natural language queries and their corresponding SQL queries, and store them in the query template library.

[0038] The data correction module is used to perform difference analysis between the original SQL query generated by the data synthesis module and the correct SQL query annotated by humans, correct the original SQL query, and rewrite the new synthesized data.

[0039] The model fine-tuning module is used to adjust the generated SQL statements to produce SQL query statements that meet business requirements.

[0040] The execution module converts the user-input natural language query into a structured SQL statement, then the model fine-tuning module adjusts the SQL statement, and finally the query is performed through the query template library and the query results are returned.

[0041] Reference Figure 1 The control method for the Text2SQL system described above, designed for real-world power scenarios, includes the following steps:

[0042] Establish a query template library containing natural language query styles and corresponding SQL structures to store subsequent synthesized data.

[0043] The data synthesis module uses preset rules and candidate dictionaries to instantiate and populate placeholders in the template, automatically generating semantically natural language queries and their corresponding SQL queries, and storing them in the query template library.

[0044] The data correction module performs a difference analysis between the original SQL query generated by the data synthesis module and the manually annotated correct SQL query, corrects the original SQL query, and rewrites the new synthesized data.

[0045] The user's natural language query input is converted into a structured SQL statement by the execution module, then the SQL statement is adjusted by the model fine-tuning module, and finally the query is performed in the query template library and the query results are returned.

[0046] When designing the query template library for the power industry, common query structures in the power industry were abstracted from a small number of manually annotated natural language queries. Standardized templates with variable placeholders were designed, such as over twenty templates like "time + location + aggregate function". Each template includes natural language query styles and corresponding SQL structures. Large-scale synthetic data is generated in batches by subsequently filling in placeholders. This template library can be dynamically expanded according to business needs to ensure coverage of more complex or new query scenarios.

[0047] Template-based data synthesis aims to systematically address the scarcity of manually labeled data in the power sector. The core of this methodology lies in identifying and abstracting a general query structure from a limited pool of manually labeled query samples, constructing standardized templates containing variable placeholders, and then instantiating and filling these placeholders using rules and models. This generates a large amount of synthetic data that is highly consistent with the source data in terms of distribution. Based on the query structure of a small amount of manually labeled data, we extracted more than 20 query templates, including "Time", "Location", "Single Enumeration", "Multiple Enumerations", "Time + Single Location", "Time + Multiple Locations", "Time + Single Enumeration", "Time + Multiple Enumerations", "Time + Other Conditions", "Time + Single Location + Single Enumeration", "Time + Multiple Locations + Single Enumeration", "Time + Location + Multiple Enumerations", "Time + Location + Any Combination of Single Enumeration Conditions", "Time + Location + Any Combination of Multiple Multi-Select Enumeration Conditions", "Time + Location + Other Conditions", "Ordinary Aggregate + Location", "Ordinary Aggregate + Time", "Ordinary Aggregate + Time + Location + Other Conditions", "Ordinary Aggregate + Single Enumeration", "Ordinary Aggregate + Time + Location", "Ordinary Aggregate + Time + Single Enumeration", "Ordinary Aggregate + Time + Location + Multiple Enumerations", "Ordinary Aggregate + Time + Location + Other Conditions", "Ordinary Aggregate + Time + Location + Enumeration", "Ordinary Aggregate + Time + Location + Group Statistics", "Sort (Time)", "Sort (Time + Single Location)", "Sort (Time + Location + Summary Primary Key)", etc. Each template has corresponding natural language queries and standard SQL queries with populated placeholders. For example, for the template "Ordinary Aggregation + Time + Location", the corresponding natural language query is "[date][place] of [table_name] of [function][column_name]", and the SQL query is "SELECT {$function}({$column}) FROM {$table} WHERE {$date} and {$place}". The template design essentially covers all query structures for common power scenarios, and a large amount of synthetic data consistent with manually labeled data distribution can be obtained by filling in placeholders such as "[date]" and "{$column}". Furthermore, the template design is scalable; new query structures can be synthesized by updating the template in real time.

[0048] The data synthesis module synthesizes data by updating templates in real time. The formal definition of the synthesized query q is: ,in This represents the distribution over the template space π, used for random template selection. It is a specific template sampled from this distribution. template The placeholders in the text, where n is the number of placeholders. It is the instantiation value of the placeholder, sampled from the conditional distribution. C represents context information. This means combining the template with the sampled values ​​to generate the final natural language query and its corresponding SQL expression. The distribution of the entire synthetic query dataset is as follows: .

[0049] The core mechanism of the data correction module lies in the deep analysis of erroneous queries generated by the LLM (Limited Language Model) as the student model, which has been fine-tuned using template-synthesized data. Edit tags representing structural or semantic deviations are extracted, and an LLM with a high number of parameters and frozen parameters is used as the teacher model. These edit tags serve as guiding information, directly mimicking and generating new, corrected synthetic data. In the power industry, due to the extremely complex table structures and field information in business scenarios, models are prone to generating non-compliant queries. The key advantage of the mimicry-based synthesis method is that it bypasses the tedious process of manually annotating and explaining rules for complex table structures and fields. It allows the model to autonomously learn and infer the correct synthesis paradigm directly from previously generated erroneous data, thereby achieving efficient data self-correction and iterative optimization.

[0050] The data correction module compares the SQL queries with erroneous inferences from the model with manually annotated real SQL queries and extracts the differences based on rules as edit tags. Individual edit tags are then categorized according to their occurrence position. The input to the data correction module is defined as follows: Where q represents the user query, and s and s ’ The SQL statement is not the same as the target SQL statement. 'e' is the edit tag, which can be M: missing, R: replacement, or U: redundant. 'k' is the number of edit tags.

[0051] Let the parameter for freezing the data correction module be: The distillation data obtained is Then the conditional probability distribution of the data correction module for .

[0052] Compared to directly using user queries and target SQL statements as input for imitation, extracting additional editing tags can more clearly pinpoint the location and cause of query errors, and generate synthetic data that matches the distribution of erroneous queries. This synthetic data is used to further fine-tune the model, helping it to self-correct and update, and continuously iterate and update based on new erroneous queries.

[0053] The model fine-tuning module is the core execution unit of the system, responsible for accurately translating natural language questions into SQL queries. It uses large-scale power sector data generated by the data synthesis module as a foundation, selecting a language model with moderate parameters that is more conducive to engineering deployment for fine-tuning. The foundation model used in this embodiment is Qwen2.5-14B, an autoregressive language model based on a Transformer decoder (Decoder-only). It consists of 48 stacked decoder layers, employing an alternating structure of multi-head self-attention and feed-forward network (FFN), and introducing residual connections and normalization mechanisms between layers to ensure stable training and efficient inference under large-scale parameter conditions. The normalization mechanism is described in the following formula: ,in The dimension is The real vector space, For a single location, the hidden vector, Let be the channel dimension of the model, and ε be a minimal constant used for numerical stability. The channel-wise gain is a learnable parameter.

[0054] Let the input sequence be represented by X, and after normalization it be... ,set up The learnable projection matrix has H attention heads and a dimension of [missing information]. Bullish attention Represented as,

[0055] ,

[0056] in For the output of the i-th attention head, For the query, key, and value of the i-th attention head, As a causal mask, For scaling terms, This is for assembly operations. The model has approximately 14 billion parameters. While ensuring strong semantic understanding and logical reasoning capabilities, it has lower memory usage and inference latency compared to GPT-4 level models, making it more suitable for engineering deployment in power business scenarios.

[0057] In this process, key hyperparameters such as learning rate, number of iterations, and LoRA rank are systematically and meticulously tuned to maximize the model's domain adaptability and generalization ability. The core objective of this module is to achieve the highest accuracy in SQL query generation while ensuring efficient deployment in real-world business scenarios and keeping inference latency within acceptable limits, thus achieving the optimal balance between theoretical performance and practical application effectiveness. To adapt the synthesized data to the instruction fine-tuning paradigm of large-scale language models, we meticulously designed data construction and prompt word engineering, transforming each set of synthesized data into standard "instruction-output" data pairs. The synthesized natural language query is placed in the instruction part, and its corresponding SQL query serves as the expected output that the model needs to learn and generate. Based on this, the instruction part is further combined with the table creation statement to construct a structured context prompt word. This systematically integrates all prerequisites required for generating SQL, including the user-input natural language query, spatiotemporal reference information, and complete schema information of the target database, covering the table or view name, functional description, and detailed definitions of all its columns. Compared to general Text2SQL tasks that only use database schema information and user queries as context, this design incorporates additional engineering information such as spatiotemporal reference information. This ensures that the model acquires sufficient background engineering knowledge and information during fine-tuning, facilitating accurate understanding of user intent and generation of precise queries. The objective function during the training phase is: 'x' represents the contextual prompts for the input, which include natural language queries, spatiotemporal reference information, and database schema. Let t be the unknown symbol in the target SQL sequence. Let θ be the conditional probability distribution of the base model Qwen2.5-14B under parameter θ, and T be the total length of the target sequence.

[0058] To improve training efficiency and reduce memory overhead, we used the Llama-Factory framework for model fine-tuning, employing LoRA (Low-Rank Adaptation) supervised fine-tuning. Based on the results, we fine-tuned hyperparameters such as the learning rate and the rank of LoRA to improve the model's accuracy and generalization ability. We also used BF16 precision to reduce memory overhead in real-world power applications. Under the LoRA mechanism, the original weight matrix W was frozen, and low-rank increments were introduced only in some key layers. , R is a trainable parameter matrix. d*r With R r*k Let A and B represent real matrix spaces of dimensions d*r and r*k, respectively. The intermediate dimension r between A and B is a pre-defined low-rank decomposition dimension, whose value is much smaller than the dimensions d and k of the original weight matrices. The parameter update form during training is as follows: By minimizing Gradient backpropagation only updates the matrix. This allows for domain adaptation while maintaining the capabilities of the base model, with significantly reduced storage and computing costs.

[0059] Example 1

[0060] This embodiment focuses on data synthesis based on common query patterns in power business databases. The specific steps are as follows:

[0061] Step S11: Template extraction. From over twenty templates, samples are selected based on the common query formats of the current table or view for data synthesis. For example, for the view "v_line_outage_static_city", the template "Time + Single Location" is sampled. The corresponding natural language query is obtained as "[date][place] of [table_name] of [column_name]", and the SQL query is "SELECT {$column} FROM {$table} WHERE {$date} AND {$place}". The view name is then filled into the {$table} placeholder in the template SQL and the [table_name] placeholder in the corresponding natural language query.

[0062] Step S12: Time processing. All tables and views can be divided into statistical tables and event tables, and different time processing rules will be applied. Specifically, statistical tables will provide fuzzy times, while event tables will provide detailed times. In addition, the time columns used by tables and views are also different and will be processed separately. For the statistical view "v_line_outage_static_city" and the "Time + Single Location" template in the example above, the time will be extracted from a specific year, month, and day, or year / month / day. For example, if "month" is extracted, a year will be randomly generated (e.g., "2024"). The {$date} placeholder in the SQL will be filled with "data_time like '2024%' AND date_type = 2", and the [date] placeholder in the corresponding natural language query will be filled with "monthly in 2024".

[0063] Step S13: Location processing. Set the actual place names involved in the power system as a candidate set and categorize them by organization, line, etc. Extract corresponding candidate locations based on the place name column. For example, in the above example, the location column of the view uses the `organ_name` field. "Changzhou Power Supply Branch" is extracted from the candidate locations. The `{$place}` placeholder in the SQL is filled with "organ_name LIKE %Changzhou Power Supply Branch%", and the `[place]` placeholder in the corresponding natural language query is filled with "Yuehu".

[0064] Step S14: Model Population. The SQL query for the template after time and location processing is "SELECT {$column}FROM v_line_outage_static_city WHERE data_time like '2024%' AND date_type = 2 AND organ_name LIKE %Changzhou Power Supply Branch Company%", and the corresponding natural language query is "[column_name] of v_line_outage_static_city of Changzhou Power Supply Branch Company every month in 2024". The SQL query with column placeholders, the natural language query, the table or view description, and field information are input into the LLM for final field population (randomly selecting 1-3 fields). The natural language query is then refined to obtain the final synthesized data.

[0065] {"type": "Time + Single Location","query": "Number of City-Level Power Lines Out of Service and Number of Outages per Month in Changzhou Power Supply Branch in 2024","sql": "SELECT outage_line_num, outage_line_timesFROM v_line_outage_org_static_city WHERE data_time LIKE '2024%' AND date_type= 2 AND organ_name LIKE '%Changzhou Power Supply Branch%'","table": "v_line_outage_org_static_city"}.

[0066] Example 2

[0067] This embodiment utilizes a large language model (teacher model) to learn from and imitate the errors made in the previous round of fine-tuning of the model (student model), generating new, corrected, high-quality data. The specific steps are as follows:

[0068] Step S21: Edit tag extraction. Based on rule comparison, the erroneous SQL queries generated by the previous model and the manually labeled standard SQL queries are used to obtain edit tags. For example, for the following erroneous data:

[0069] { "query": "Find the names of the top 5 cities with the most affected photovoltaic substations due to outages", "table":"ads_bra_line_outage_org_static", "target": "SELECT CITY_NAME, SUM(FAULT_EFEC_GPC_TRANS_NUM) AS total_fault_gpc FROM ads_bra_line_outage_org_staticGROUP BY CITY_NAME ORDER BY total_fault_gpc DESC LIMIT 5", "predict": "SELECT CITY_NAME, FAULT_EFEC_GPC_TRANS_NUM FROM ads_bra_line_outage_org_static GROUPBY CITY_NAME ORDER BY FAULT_EFEC_GPC_TRANS_NUM DESC LIMIT 5", "flag": "INCORRECT"}.

[0070] The following editing tags can be extracted:

[0071] "edit": { "select": { "replacement": [ [ "sum(fault_efec_gpc_trans_num)", "fault_efec_gpc_trans_num" ] ], "missing": [], "unecessary": []}, "where": { "replacement": [], "missing": [], "unecessary": []}, "groupby":{ "replacement": [], "missing": [], "unecessary": []}, "orderby": { "replacement": [ [ "sum(fault_efec_gpc_trans_num) desc limit 5", "fault_efec_gpc_trans_num desc limit 5" ] ], "missing": [], "unecessary": []}}}.

[0072] It can be seen that the model error for this data is due to the incorrect application of the aggregate function SUM() to the fault_efec_gpc_trans_num column, which is a replacement error. This edit label can help the teacher model to accurately locate the error location and cause.

[0073] Step S22: Teacher Model Imitation. The original natural language query, an incorrect SQL query, a standard SQL query, and edit tags are used as input to the teacher model. The teacher model is required to output multiple imitation data points referencing the edit tags. For example, based on the data example above, the following imitation data can be generated:

[0074] {

[0075] "query": "Names of the top 5 power supply stations most affected by planned power outages",

[0076] "sql": "SELECT STATION_NAME, SUM(PLAN_EFEC_POP_TRANS_NUM) AS total_plan_pop FROM ads_bra_line_outage_org_static GROUP BY STATION_NAME ORDER BYtotal_plan_pop DESC LIMIT 5"

[0077] },

[0078] {

[0079] "query": "Find the names of the top 10 cities whose service outages affect the most sensitive users",

[0080] "sql": "SELECT CITY_NAME, SUM(FAULT_OUTAGE_SEN_CONS_NUM) AS total_sen_cons FROM ads_bra_line_outage_org_static GROUP BY CITY_NAME ORDER BYtotal_sen_cons DESC LIMIT 10"

[0081] },

[0082] {

[0083] "query": "List the top 5 districts and counties with the most service outages affecting users' livelihoods",

[0084] "sql": "SELECT district_name, SUM(INVL_OUTAGE_LIVE_CONS_TIMES) AStotal_live_times FROM ads_bra_line_outage_org_static GROUP BY district_nameORDER BY total_live_times DESC LIMIT 5"

[0085] },

[0086] {

[0087] "query": "Find the top 5 cities where planned power outages will have the greatest impact on the number of users switching from coal to electricity",

[0088] "sql": "SELECT CITY_NAME, SUM(PLAN_COALTOELEC_CONS_NUM) AS total_coal_cons FROM ads_bra_line_outage_org_static GROUP BY CITY_NAME ORDER BYtotal_coal_cons DESC LIMIT 5"

[0089] },

[0090] {

[0091] "query": "Find the names of the top 5 cities whose electricity outages have the greatest impact on the number of users with non-electricity consumption",

[0092] "sql": "SELECT CITY_NAME, SUM(FAULT_OUTAGE_ZERO_CONS_NUM) AS total_zero_cons FROM ads_bra_line_outage_org_static GROUP BY CITY_NAME ORDER BYtotal_zero_cons DESC LIMIT 5"

[0093] }

[0094] Step S23: Filter the imitation data. Since the imitation data is directly generated from the model, there may be an LLM illusion. It is necessary to filter the data based on the execution results in the database and only keep the data that does not report errors in the database query and whose query results are not empty as new synthetic data to continue to fine-tune the model.

[0095] Example 3

[0096] This embodiment constructs detailed instruction-based prompts and uses LoRa technology to efficiently fine-tune a large language model, thereby creating a precise Text2SQL model for the power industry. The specific steps are as follows:

[0097] Step S31: Prompt word construction. The synthesized data generated by the data synthesis module constructs an "instruction-output" data pair. The instruction portion includes contextual information such as the user's natural language query, time, and location, as well as a complete schema description of the target database (including table names, function descriptions, and field information). An example is as follows: "instruction": "Today is 2025-07-17. You are an expert proficient in SQL. Now, please generate the corresponding SQL query statement based on the user's natural language query Query and related view information."

[0098] Note: When generating SQL statements, please refer to the provided view information to avoid using column names that do not exist in the view, and to ensure the data types of the columns are correct. If the view description includes requirements for location and time filtering, please strictly adhere to them. If a location name is specified in the query, please do not use other location names for filtering. Additionally, views with "static" in their name are usually statistical views, while those with "event" are usually event views. Statistical views do not require aggregation, while the query results of event views typically require aggregation.

[0099] [View Name]

[0100] v_line_outage_org_static_city

[0101] [View Description]

[0102] City-level power outage statistics view

[0103] [View column information]

[0104] [{'name': 'organ_code', 'column_description': 'Organization code;', 'type': 'NUMBER'}, {'name': 'organ_name', 'column_description': 'Organization name;', 'type': 'VARCHAR2', {'name': 'p_organ_name', 'column_description': 'P_ORGAN_NAME', 'type': 'VARCHAR2'}, {'name': 'data_time', 'column_description':'Data time', 'type': 'VARCHAR2'}, {'name': 'date_type', 'column_description':'Time category 1:Year 2:Month 3:Day', 'type': 'NUMBER'}, {'name': 'Year', 'column_description': 'Year', 'type': 'NUMBER'}, {'name': 'month', 'column_description': 'month', 'type': 'NUMBER'}, {'name': 'day', 'column_description': 'day', 'type': 'NUMBER'}, {'name': 'line_num', 'column_description': 'currently managed line number', 'type': 'NUMBER'}, {'name': 'outage_line_num', 'column_description': 'number of out-of-service lines', 'type': 'NUMBER'}, {'name': 'fault_outage_line_num', 'column_description': 'number of out-of-service lines due to fault', 'type': 'NUMBER'}, {'name': 'plan_outage_line_num', 'column_description': 'Number of planned outage lines', 'type': 'NUMBER'}, {'name': 'outage_line_times', 'column_description': 'Number of line outages', 'type': 'NUMBER'}

[0105] [User Inquiry]

[0106] The monthly data for the outage number and outage frequency of the Changzhou Power Supply Branch in 2024 is as follows: "input": "", "output": "SELECT outage_line_num, outage_line_times FROM v_line_outage_org_static_city WHERE data_time LIKE '2024%' AND date_type = 2 ANDorgan_name LIKE '%Changzhou Power Supply Branch%'".

[0107] The above LLM contextual cue design enables the model in the fine-tuning stage to fully understand the query background and power industry knowledge, and accurately generate SQL queries that meet business needs.

[0108] Step S32: Hyperparameter Setting and Model Fine-tuning. We used the Llama-Factory framework to fine-tune the Qwen2.5-14B model using LoRa. The hyperparameter settings were determined through multiple rounds of testing. Fine-tuning was performed on the learning rate, LoRa rank, and number of iterations to maximize the balance between output query accuracy and generalization ability. BF16 precision was used to reduce memory overhead in real-world deployment scenarios, making our system easier to deploy on terminals. The specific hyperparameter settings during the fine-tuning phase are as follows:

[0109] per_device_train_batch_size: 1

[0110] gradient_accumulation_steps: 8

[0111] learning_rate: 1.0e-4

[0112] num_train_epochs: 3

[0113] lr_scheduler_type: cosine

[0114] warmup_ratio: 0.1

[0115] bf16: true

[0116] ddp_timeout: 180000000

[0117] resume_from_checkpoint: null.

[0118] This invention has better adaptability and generalization ability in the power field. It can flexibly handle the unique location, equipment name, time range, statistical needs, etc. of the power industry, significantly reduce the technical threshold, improve query efficiency and accuracy, and provide strong support for intelligent operation and maintenance, intelligent scheduling and auxiliary decision-making of power.

[0119] Those skilled in the art will clearly understand that, for the sake of convenience and brevity, the above-described division of functional units and modules is merely an example. In practical applications, the above functions can be assigned to different functional units and modules as needed, that is, the internal structure of the device can be divided into different functional units or modules to complete all or part of the functions described above. The functional units and modules in the embodiments can be integrated into one processing unit, or each unit can exist physically separately, or two or more units can be integrated into one unit. The integrated unit can be implemented in hardware or as a software functional unit. Furthermore, the specific names of the functional units and modules are only for easy differentiation and are not intended to limit the scope of protection of this application. The specific working process of the units and modules in the above system can be referred to the corresponding process in the foregoing method embodiments, and will not be repeated here.

[0120] The present invention can implement all or part of the processes in the methods of the above embodiments, or it can be implemented by a computer program instructing related hardware. The computer program can be stored in a computer-readable storage medium, and when the computer program is executed by a processor, it can implement the steps of the various method embodiments described above. The computer program includes computer program code, which can be in the form of source code, object code, executable file, or some intermediate form. The computer-readable medium can include: any entity or device capable of carrying computer program code, recording media, USB flash drive, portable hard drive, magnetic disk, optical disk, computer memory, read-only memory (ROM), random access memory (RAM), electrical carrier signals, telecommunication signals, and software distribution media, etc. It should be noted that the content included in the computer-readable medium can be appropriately added or removed according to the requirements of legislation and patent practice in the jurisdiction. For example, in some jurisdictions, according to legislation and patent practice, the computer-readable medium does not include electrical carrier signals and telecommunication signals.

[0121] The foregoing has shown and described the basic principles, main features, and advantages of the present invention. Those skilled in the art should understand that the present invention is not limited to the above embodiments. The embodiments and descriptions in the specification are merely illustrative of the principles of the invention. Various changes and modifications can be made to the invention without departing from its spirit and scope, and all such changes and modifications fall within the scope of the present invention as claimed. The scope of protection of this invention is defined by the appended claims and their equivalents.

Claims

1. A control method for a Text2SQL system oriented towards real-world power scenarios, wherein the Text2SQL system oriented towards real-world power scenarios includes, A query template library, containing natural language query styles and corresponding SQL structures, is used to store subsequent synthesized data; The data synthesis module is used to instantiate and populate placeholders in the template using preset rules and candidate dictionaries, automatically generate semantically natural language queries and their corresponding SQL queries, and store them in the query template library; The data correction module is used to perform difference analysis between the original SQL query generated by the data synthesis module and the correct SQL query annotated by humans, correct the original SQL query, and rewrite the new synthesized data. The model fine-tuning module is used to adjust the generated SQL statements to produce SQL query statements that meet business requirements. The execution module is used to convert the natural language query input by the user into a structured SQL statement, then the model fine-tuning module adjusts the SQL statement, and finally the query is performed through the query template library and the query results are returned. Its characteristics include the following steps: Establish a query template library containing natural language query styles and corresponding SQL structures to store subsequent synthesized data; The data synthesis module uses preset rules and candidate dictionaries to instantiate and populate placeholders in the template, automatically generating semantically natural language queries and their corresponding SQL queries, and storing them in the query template library; The data synthesis module synthesizes data by updating templates in real time. The formal definition of the synthesized query q is: ,in This represents the distribution over the template space π, used for random template selection. It is a specific template sampled from this distribution. template The placeholders in the text, where n is the number of placeholders. It is the instantiation value of the placeholder, sampled from the conditional distribution. C represents context information. This means combining the template with the sampled values ​​to generate the final natural language query and its corresponding SQL expression. The distribution of the entire synthetic query dataset is as follows: ; The data correction module performs a difference analysis between the original SQL query generated by the data synthesis module and the correct SQL query annotated by the human, corrects the original SQL query, and rewrites the new synthesized data. The user's natural language query input is converted into a structured SQL statement by the execution module, then the SQL statement is adjusted by the model fine-tuning module, and finally the query is performed in the query template library and the query results are returned.

2. The control method for a Text2SQL system oriented towards actual power scenarios according to claim 1, characterized in that: The data correction module compares SQL queries with erroneous inferences from the model with manually annotated real SQL queries and extracts the differences based on rules as edit tags. Individual edit tags are then categorized according to their occurrence position. The input to the data correction module is defined as follows: Where q represents the user query, and s and s ’ These are the erroneous SQL statement and the target SQL statement, respectively. 'e' is the edit label, with values ​​including M: Missing, R: Replace, and U: Redundant. Let the parameter for freezing the data correction module be: The distillation data obtained is Then the conditional probability distribution of the data correction module for .

3. The control method for a Text2SQL system oriented towards actual power scenarios according to claim 2, characterized in that: The model fine-tuning module includes a 48-layer decoder that alternates between multi-head self-attention mechanisms and feedforward networks, and introduces residual connections and normalization mechanisms between layers; the normalization mechanism is described in the following formula: ,in The dimension is The real vector space, For a single location, the hidden vector, Let be the channel dimension of the model, and ε be a minimal constant used for numerical stability. For the channel-wise gain of the learnable parameter, h i Let h be the component value of the hidden vector h in the i-th dimension; Let the input sequence be represented by X, and after normalization it be... ,set up The learnable projection matrix has H attention heads and a dimension of [missing information]. Bullish attention Represented as, ， ， ， in For the output of the i-th attention head, For the query, key, and value of the i-th attention head, As a causal mask, For scaling terms, For the concatenation operation, Q, K, and V are the query matrix, key matrix, and value matrix, respectively.

Images