A power data query method and system based on two-stage keyword recall and SQL instant governance
Patent Information
- Application Number
- CN202611005078.1
- Authority / Receiving Office
- CN · China
- Patent Type
- Applications(China)
- Current Assignee / Owner
- Filing Date
- 2026-07-07
- Publication Date
- 2026-10-16
AI Technical Summary
[0005]发明目的：本发明的目的是提供了一种基于双阶段关键词召回与SQL即时治理的电力数据查询方法及系统，解决电力系统自然语言查询中语义理解不准与数据质量不可控的问题，显著提升了查询效率、准确率与结果可信度
[0028] (1) This invention proposes a two-stage precise recall algorithm based on keyword matching. Through a two-level screening mechanism of coarse sorting and fine sorting, it achieves semantic alignment at the millisecond level, and the retrieval efficiency is significantly better than the vectorized semantic recall method.
Smart Images
Figure CN122925428A_ABST
Abstract
Description
Technical Field
[0001] This invention belongs to the field of power data processing and natural language query technology, and particularly relates to a power data query method and system based on two-stage keyword recall and real-time SQL governance. Background Technology
[0002] With the advancement of the energy internet and the construction of new power systems, power grid companies have accumulated massive amounts of operation, dispatch, equipment, and user data. Business personnel (such as dispatchers, operation mode specialists, and data analysts) have an increasing need to directly access databases via natural language. Natural Language to SQL (Text2SQL) technology, as a key path to achieving convenient data access, has become a research hotspot in the power industry.
[0003] However, existing natural language query technology faces two major challenges in practical applications in the power system: First, the accuracy of query intent matching in the semantic understanding stage is not high, especially when power business expressions are diverse and terminology is professional, mismatch problems are prone to occur, resulting in inaccurate search results; Second, even if the correct SQL statement is generated, underlying data quality problems (such as inconsistent field naming, null values, outliers, inconsistent master data, etc.) can still lead to distorted query results and affect decision-making.
[0004] Current mainstream solutions mostly employ vectorized semantic recall (such as semantic similarity calculation based on Sentence-BERT) or end-to-end large model generation. These methods suffer from high response latency, reliance on external model services, and insufficient utilization of structured patterns in specialized fields such as power. Meanwhile, traditional data governance often relies on ETL (Extract-Transform-Load) batch processing, which lags behind online query processes and makes it difficult to guarantee data quality consistency in real-time query scenarios. Summary of the Invention
[0005] Purpose of the invention: The purpose of this invention is to provide a power data query method and system based on two-stage keyword recall and real-time SQL governance, which solves the problems of inaccurate semantic understanding and uncontrollable data quality in natural language queries of power systems, and significantly improves query efficiency, accuracy and reliability of results.
[0006] Technical solution:
[0007] In a first aspect, the present invention provides a power data query method based on two-stage keyword recall and real-time SQL governance, comprising:
[0008] Obtain the natural language query question input by the user; perform word segmentation and keyword extraction on the natural language query question to form an input keyword set;
[0009] The input keyword set is matched and recalled with the sample keyword set in the historical query sample library in a two-stage process to obtain the optimal matching sample; the two-stage matching recall includes a coarse ranking stage based on keyword overlap and a fine ranking stage based on Jaccard similarity.
[0010] The optimal matching example and the power knowledge graph information are used as semantic context and input into the Text2SQL model to generate preliminary SQL statements.
[0011] The abstract syntax tree of the initial SQL statement is parsed by a metadata-driven governance rule engine to identify the target table, fields and conditions, automatically match and inject data governance rules, and generate the governed SQL statement.
[0012] Execute the SQL statement after the governance process to return the structured query results.
[0013] Furthermore, the keyword extraction adopts a word segmentation strategy based on the power industry dictionary, removing time expressions, quantifiers and stop words, and retaining core query target and condition keywords to form an input keyword set; the power industry dictionary contains complete segmented entries of power system-specific terms.
[0014] Furthermore, the coarse sorting stage based on keyword overlap specifically involves: selecting samples from the historical query sample library whose number of intersection elements between the input keyword set and the sample keyword set is greater than or equal to a preset threshold, thus forming a candidate sample set.
[0015] Furthermore, the fine sorting stage of the Jaccard similarity is specifically as follows: for each sample in the candidate sample set, sort them in descending order according to the Jaccard similarity Sim(q,e)=|Kq∩Ke| / |Kq∪Ke|, and take the Top-1 sample as the optimal matching sample, where Kq is the input keyword set and Ke is the sample keyword set.
[0016] Furthermore, the data governance rules include: field standardization alias mapping, null value handling function rewriting, data quality verification condition appending, master data alignment value mapping, and sensitive information de-identification field replacement; the governance rule engine achieves non-intrusive data governance by rewriting SQL statements.
[0017] Secondly, this invention provides a power data query system based on two-stage keyword retrieval and real-time SQL governance, comprising:
[0018] Semantic understanding module: Obtains the natural language query question input by the user; performs word segmentation and keyword extraction on the natural language query question, removes time expressions, quantifiers and stop words, retains the core query target and condition keywords, and forms an input keyword set;
[0019] Two-stage recall module: The input keyword set is matched and recalled with the sample keyword set in the historical query sample library in a two-stage process to obtain the optimal matching sample; the two-stage matching recall includes a coarse ranking stage based on keyword overlap and a fine ranking stage based on Jaccard similarity.
[0020] SQL generation module: The optimal matching example and the power knowledge graph information are used as semantic context and input into the Text2SQL model to generate preliminary SQL statements;
[0021] SQL governance engine module: Parses the abstract syntax tree of the initial SQL statement through a metadata-driven governance rule engine, identifies the target table, fields and conditions, automatically matches and injects data governance rules, and generates the governed SQL statement;
[0022] Query execution module: Executes the SQL statement after governance and returns the structured query results.
[0023] Furthermore, the keyword extraction adopts a word segmentation strategy based on an electric power professional dictionary, which contains complete segmented entries of electric power system-specific terms.
[0024] Furthermore, the coarse sorting stage based on keyword overlap specifically involves: selecting samples from the historical query sample library whose number of intersection elements between the input keyword set and the sample keyword set is greater than or equal to a preset threshold, thus forming a candidate sample set.
[0025] Furthermore, the fine sorting stage of the Jaccard similarity is specifically as follows: for each sample in the candidate sample set, sort them in descending order according to the Jaccard similarity Sim(q,e)=|Kq∩Ke| / |Kq∪Ke|, and take the Top-1 sample as the optimal matching sample, where Kq is the input keyword set and Ke is the sample keyword set.
[0026] Furthermore, the data governance rules include: field standardization alias mapping, null value handling function rewriting, data quality verification condition appending, master data alignment value mapping, and sensitive information de-identification field replacement; the governance rule engine achieves non-intrusive data governance by rewriting SQL statements.
[0027] Beneficial effects: Compared with the prior art, the present invention has the following significant advantages:
[0028] (1) This invention proposes a two-stage precise recall algorithm based on keyword matching. Through a two-level screening mechanism of coarse sorting and fine sorting, it achieves semantic alignment at the millisecond level, and the retrieval efficiency is significantly better than the vectorized semantic recall method.
[0029] (2) This invention achieves non-intrusive real-time data governance by dynamically injecting governance rules such as field standardization, null value handling, data quality verification, master data alignment and sensitive information desensitization before SQL execution through a metadata-driven governance rule engine, ensuring that each query output is based on a trustworthy and standard data view.
[0030] (3) This invention combines keyword precision recall with SQL embedded governance in a collaborative design. The two-stage recall mechanism ensures the accuracy of understanding the query intent, while the SQL embedded governance mechanism ensures the reliability and standardization of the query results. The synergistic effect of the two significantly improves the overall performance of the system.
[0031] (4) This invention effectively reduces the average response time of power data query, improves the accuracy of SQL generation, reduces the query failure rate caused by data anomalies, and effectively supports core business scenarios such as power dispatching and operation analysis. Attached Figure Description
[0032] Figure 1 This is a flowchart of the method described in this invention.
[0033] Figure 2 This is a flowchart of the two-stage keyword recall method according to an embodiment of the present invention.
[0034] Figure 3 This is a flowchart of the SQL governance engine execution process in an embodiment of the present invention. Detailed Implementation
[0035] The technical solution of the present invention will be further described below with reference to the accompanying drawings.
[0036] Example 1
[0037] This embodiment provides a power data query method based on two-stage keyword recall and real-time SQL governance, including:
[0038] S1. Obtain the natural language query question input by the user; perform word segmentation and keyword extraction on the natural language query question to form an input keyword set;
[0039] Specifically, the system receives users' natural language queries and performs word segmentation using a strategy based on a power industry dictionary. This dictionary contains complete segmented entries of power system-specific terminology, such as "total grid load," "AGC unit," "N-1 verification," "new energy output," and "main substation." Simultaneously, time expressions (such as "yesterday," "Q3"), quantifiers, and stop words are removed, retaining only the core query targets and conditional keywords to form the input keyword set.
[0040] S2. Perform a two-stage matching recall between the input keyword set and the sample keyword set in the historical query sample library to obtain the optimal matching sample; the two-stage matching recall includes a coarse ranking stage based on keyword overlap and a fine ranking stage based on Jaccard similarity.
[0041] Specifically, a two-stage matching and recall process is performed between the input keyword set and the historical query sample library. The historical query sample library contains information such as historical query questions, their corresponding keyword sets, SQL statements, and query results. The two-stage recall includes two stages: coarse sorting and fine sorting. In the coarse sorting stage, based on keyword overlap, samples from the historical query sample library with an intersection of more than or equal to a preset threshold (4 in this embodiment) are selected to form a candidate sample set. In the fine sorting stage, each sample in the candidate sample set is sorted in descending order according to the Jaccard similarity Sim(q,e)=|Kq∩Ke| / |Kq∪Ke|, and the top-1 sample is taken as the optimal matching sample, where Kq is the input keyword set and Ke is the sample keyword set.
[0042] like Figure 2 As shown, this embodiment details the two-stage keyword recall process:
[0043] S21: Keyword Extraction: Input the user question "At the time when the total power load of the entire network was at its highest yesterday, what was the total output of wind power and solar power?", and use a word segmentation strategy based on a power industry dictionary to segment the data, obtaining the word sequence: {yesterday, total power load, highest, time, wind power, solar power, total output}. After removing the time expression "yesterday" and the quantifier "that", extract the core keyword set Kq={total power load, highest, time, wind power, solar power, total output}.
[0044] S22: Coarse sorting: In the historical query sample database containing 4200 samples, calculate the intersection of Kq with the keyword set Ke of each sample. Filter samples where |Kq∩Ke|≥4 to form a candidate sample set. This can narrow down the candidate range to dozens of samples.
[0045] S23: Fine-grained ranking: For each example in the candidate example set, calculate its Jaccard similarity Sim(q,e)=|Kq∩Ke| / |Kq∪Ke|. For example, if the keyword set Ke of example A is {total grid load, peak, time, wind power, photovoltaic, total output, new energy}, then |Kq∩Ke|=6, |Kq∪Ke|=7, Sim=6 / 7≈0.857; if the keyword set Ke of example B is {total grid load, average, time, wind power, photovoltaic}, then |Kq∩Ke|=4, |Kq∪Ke|=7, Sim=4 / 7≈0.571. Sort by similarity in descending order, and take the Top-1 (i.e., example A) as the best matching example.
[0046] S24: Output the SQL template, field mapping relationship and other information of the best matching example to the semantic enhancement module.
[0047] Based on actual testing, the algorithm has an average recall time of 2.8 milliseconds and a Top-1 accuracy of 89.6% in a sample database of 4200 samples.
[0048] S3. Using the optimal matching example and the power knowledge graph information as semantic context, input the Text2SQL model to generate a preliminary SQL statement;
[0049] Specifically, the optimal matching example is enhanced with context using a power knowledge graph. The power knowledge graph contains entities such as power system equipment, indicators, regions, and time, along with their relationships. For example, "wind power" and "photovoltaic" both belong to the category of "new energy output," and "the entire network" corresponds to the region code "ALL_GRID." Through knowledge graph enhancement, implicit but unexpressed query information can be supplemented.
[0050] The semantically enhanced contextual information is input into the Text2SQL model to generate preliminary SQL statements. Based on the SQL patterns in the best-matched examples and combined with the entity mapping relationships provided by the knowledge graph, the Text2SQL model generates SQL statements that conform to power business specifications.
[0051] S4. The abstract syntax tree of the initial SQL statement is parsed by the metadata-driven governance rule engine, the target table, fields and conditions are identified, data governance rules are automatically matched and injected, and the governed SQL statement is generated.
[0052] Specifically, before SQL execution, the initial SQL statement is governed through a metadata-driven approach. The governance engine first parses the abstract syntax tree (AST) of the SQL statement to identify the target table, fields, and condition information; then, based on the metadata information in the data dictionary, it automatically matches and injects data governance rules. These rules include: field standardization alias mapping (e.g., mapping "wind power output" to the field name "wind_output"), rewriting null value handling functions (e.g., using the COALESCE function to set default values), appending data quality validation conditions (e.g., adding the condition "status=VALID"), mapping master data alignment values (e.g., mapping "the entire network" to the regional code "ALL_GRID"), and replacing sensitive information masked fields (e.g., masking the power plant name field).
[0053] The process for the SQL governance engine in this embodiment is as follows:
[0054] S41. Receive preliminary SQL statement: The preliminary SQL statement output by the SQL generation module is the original semantic expression, for example:
[0055] SELECT wind + solar FROM gen WHERE time IN (SELECT time FROM loadORDER BY load DESC LIMIT 1)
[0056] S42, AST parsing: The governance engine performs lexical and syntactic analysis on the SQL statement, constructs an abstract syntax tree (AST), identifies the target tables as gen and load, the target fields as wind, solar, and time, and the condition as a time IN subquery.
[0057] S43. Rule Matching: Based on the table and field names identified by the AST, the governance engine queries the metadata information in the data dictionary and automatically matches governance rules: the wind field matches the field normalization rule and maps it to wind_output; the solar field matches the field normalization rule and maps it to solar_output; the gen table matches the null value handling rule and adds COALESCE processing to the wind_output and solar_output fields; the load table in the subquery matches the data quality verification rule and adds the status=VALID condition; at the same time, it matches the master data alignment rule and maps the default region to ALL_GRID.
[0058] S44. Rule Injection. The governance engine dynamically injects matching governance rules into the original SQL statement through SQL rewriting. The rewritten SQL statement is as follows:
[0059] SELECT COALESCE(wind_output, 0) + COALESCE(solar_output, 0) AS total_renewable FROM generation_cleaned WHERE time IN (SELECT time FROM load_dataWHERE status='VALID' ORDER BY load DESC LIMIT 1) AND unit_status != 'MAINTENANCE'
[0060] S45. Output the post-governance SQL. The post-governance SQL statement is sent to the query execution module for execution. In this embodiment, the governance engine does not need to modify the table structure or data in the original database. It achieves non-intrusive governance through SQL rewriting, ensuring the accuracy, consistency, and compliance of the query results.
[0061] S5. Execute the SQL statement after governance and return the structured query results.
[0062] The system executes the processed SQL statement and returns the structured query results to the user. Simultaneously, it records the query information for future optimization of the query sample library.
[0063] In this embodiment, the method for constructing the power industry terminology dictionary is as follows: The power industry terminology dictionary consists of three parts: original vocabulary, standardized vocabulary, and notes. The original vocabulary consists of commonly used terms in power business, as shown in Table 1.
[0064] Table 1:
[0065] Original vocabulary Standardized vocabulary illustrate Total power load total_load Total power load of the entire power grid New energy output renewable_output Wind power + solar power and other new energy sources wind power wind_power Wind power generation (abbreviation) Photovoltaics solar power Solar photovoltaic power generation is abbreviated as Peak load time peak_load_time Peak load time Main substation main_substation Main substations AGC Units AGC_unit Automatic generator control unit N-1 verification N_1_check Power grid stability analysis methods
[0066] The power industry-specific word segmentation dictionary is built upon a knowledge base in the power sector and supports dynamic updates. When new indicators or terms are added to power-related business, users can add, delete, or modify them through the dictionary management interface, ensuring that the word segmentation dictionary remains synchronized with actual business operations.
[0067] Example 2
[0068] This embodiment provides a power data query system based on two-stage keyword recall and real-time SQL governance, including:
[0069] Semantic understanding module: Obtains the natural language query question input by the user; performs word segmentation and keyword extraction on the natural language query question, removes time expressions, quantifiers and stop words, retains the core query target and condition keywords, and forms an input keyword set;
[0070] Two-stage recall module: The input keyword set is matched and recalled with the sample keyword set in the historical query sample library in a two-stage process to obtain the optimal matching sample; the two-stage matching recall includes a coarse ranking stage based on keyword overlap and a fine ranking stage based on Jaccard similarity.
[0071] SQL generation module: The optimal matching example and the power knowledge graph information are used as semantic context and input into the Text2SQL model to generate preliminary SQL statements;
[0072] SQL governance engine module: Parses the abstract syntax tree of the initial SQL statement through a metadata-driven governance rule engine, identifies the target table, fields and conditions, automatically matches and injects data governance rules, and generates the governed SQL statement;
[0073] Query execution module: Executes the SQL statement after governance and returns the structured query results.
[0074] The keyword extraction adopts a word segmentation strategy based on a power industry dictionary, which contains complete segmented entries of power system-specific terms.
[0075] The coarse sorting stage based on keyword overlap specifically involves: selecting samples from the historical query sample library whose number of intersection elements between the input keyword set and the sample keyword set is greater than or equal to a preset threshold, thus forming a candidate sample set.
[0076] The fine sorting stage of the Jaccard similarity is specifically as follows: for each sample in the candidate sample set, sort them in descending order according to Jaccard similarity Sim(q,e)=|Kq∩Ke| / |Kq∪Ke|, and take the Top-1 sample as the optimal matching sample, where Kq is the input keyword set and Ke is the sample keyword set.
[0077] The data governance rules include: field standardization alias mapping, null value handling function rewriting, data quality verification condition appending, master data alignment value mapping, and sensitive information de-identification field replacement; the governance rule engine achieves non-intrusive data governance by rewriting SQL statements.
[0078] Example 3
[0079] This embodiment also provides a computer device, the device including one or more processors, a memory, and one or more programs, wherein the one or more programs are stored in the memory and configured to be executed by the one or more processors, and the programs, when executed by the processors, implement the steps of the power data query method based on two-stage keyword recall and SQL real-time governance as described above.
[0080] Example 4
[0081] This embodiment also provides a computer storage medium storing a computer program, which, when executed by a processor, implements the steps of a power data query method based on two-stage keyword recall and SQL real-time governance as described above.
[0082] Those skilled in the art will understand that embodiments of the present invention can be provided as methods, apparatus (systems), computer devices, or computer program products. Therefore, the present invention can take the form of a completely hardware embodiment, a completely software embodiment, or an embodiment combining software and hardware aspects. Furthermore, the present invention can take the form of a computer program product embodied on one or more computer-usable storage media (including but not limited to disk storage, CD-ROM, optical storage, etc.) containing computer-usable program code.
[0083] This invention is described with reference to a flowchart of a method according to embodiments of the invention. It should be understood that each step in the flowchart and combinations thereof can be implemented by computer program instructions. These computer program instructions can be provided to a processor of a general-purpose computer, special-purpose computer, embedded processor, or other programmable data processing device to produce a machine, such that the instructions, which execute via the processor of the computer or other programmable data processing device, generate instructions for implementing the process. Figure 1 A device for a function specified in one or more processes.
[0084] These computer program instructions may also be stored in a computer-readable storage medium that can direct a computer or other programmable data processing device to function in a particular manner, such that the instructions stored in the computer-readable storage medium produce an article of manufacture including instruction means, which are implemented in a process Figure 1 The function specified in one or more processes.
[0085] These computer program instructions may also be loaded onto a computer or other programmable data processing equipment to cause a series of operational steps to be performed on the computer or other programmable equipment to produce a computer-implemented process, thereby providing instructions that execute on the computer or other programmable equipment for implementing the process. Figure 1 Steps of a specified function in one or more processes.
Claims
1. A power data query method based on two-stage keyword recall and real-time SQL governance, characterized in that, include: Obtain the user's input in natural language to query the query; The natural language query problem is segmented and keywords are extracted to form an input keyword set; The input keyword set is matched and recalled in a two-stage process with the sample keyword set in the historical query sample library to obtain the optimal matching sample. The two-stage matching recall includes a coarse ranking stage based on keyword overlap and a fine ranking stage based on Jaccard similarity. The optimal matching example and the power knowledge graph information are used as semantic context and input into the Text2SQL model to generate preliminary SQL statements. The abstract syntax tree of the initial SQL statement is parsed by a metadata-driven governance rule engine to identify the target table, fields and conditions, automatically match and inject data governance rules, and generate the governed SQL statement. Execute the SQL statement after the governance process to return the structured query results.
2. The power data query method based on two-stage keyword recall and real-time SQL governance according to claim 1, characterized in that, The keyword extraction adopts a word segmentation strategy based on the power industry dictionary, removing time expressions, quantifiers and stop words, and retaining core query target and condition keywords to form an input keyword set; the power industry dictionary contains complete segmented entries of power system-specific terms.
3. The power data query method based on two-stage keyword recall and real-time SQL governance according to claim 1, characterized in that, The coarse sorting stage based on keyword overlap specifically involves: selecting samples from the historical query sample library whose number of intersection elements between the input keyword set and the sample keyword set is greater than or equal to a preset threshold, thus forming a candidate sample set.
4. The power data query method based on two-stage keyword recall and real-time SQL governance according to claim 1, characterized in that, The fine sorting stage of the Jaccard similarity is specifically as follows: for each sample in the candidate sample set, sort them in descending order according to Jaccard similarity Sim(q,e)=|Kq∩Ke| / |Kq∪Ke|, and take the Top-1 sample as the optimal matching sample, where Kq is the input keyword set and Ke is the sample keyword set.
5. The power data query method based on two-stage keyword recall and real-time SQL governance according to claim 1, characterized in that, The data governance rules include: field standardization alias mapping, null value handling function rewriting, data quality verification condition appending, master data alignment value mapping, and sensitive information de-identification field replacement; the governance rule engine achieves non-intrusive data governance by rewriting SQL statements.
6. A power data query system based on two-stage keyword recall and real-time SQL governance, characterized in that, include: Semantic understanding module: Acquires natural language query questions input by the user; The natural language query problem is segmented and keywords are extracted. Time expressions, quantifiers and stop words are removed, and the core query target and condition keywords are retained to form an input keyword set. Two-stage recall module: Performs two-stage matching and recall between the input keyword set and the sample keyword set in the historical query sample library to obtain the optimal matching sample; The two-stage matching recall includes a coarse ranking stage based on keyword overlap and a fine ranking stage based on Jaccard similarity. SQL generation module: The optimal matching example and the power knowledge graph information are used as semantic context and input into the Text2SQL model to generate preliminary SQL statements; SQL governance engine module: Parses the abstract syntax tree of the initial SQL statement through a metadata-driven governance rule engine, identifies the target table, fields and conditions, automatically matches and injects data governance rules, and generates the governed SQL statement; Query execution module: Executes the SQL statement after governance and returns the structured query results.
7. A power data query system based on two-stage keyword recall and real-time SQL governance as described in claim 6, characterized in that, The keyword extraction adopts a word segmentation strategy based on a power industry dictionary, which contains complete segmented entries of power system-specific terms.
8. A power data query system based on two-stage keyword recall and real-time SQL governance as described in claim 6, characterized in that, The coarse sorting stage based on keyword overlap specifically involves: selecting samples from the historical query sample library whose number of intersection elements between the input keyword set and the sample keyword set is greater than or equal to a preset threshold, thus forming a candidate sample set.
9. A power data query system based on two-stage keyword recall and real-time SQL governance as described in claim 6, characterized in that, The fine sorting stage of the Jaccard similarity is specifically as follows: for each sample in the candidate sample set, sort them in descending order according to Jaccard similarity Sim(q,e)=|Kq∩Ke| / |Kq∪Ke|, and take the Top-1 sample as the optimal matching sample, where Kq is the input keyword set and Ke is the sample keyword set.
10. A power data query system based on two-stage keyword recall and real-time SQL governance as described in claim 6, characterized in that, The data governance rules include: field standardization alias mapping, null value handling function rewriting, data quality verification condition appending, master data alignment value mapping, and sensitive information de-identification field replacement; the governance rule engine achieves non-intrusive data governance by rewriting SQL statements.