Method and apparatus for table search of power service database

Abstract

This invention provides a method and apparatus for retrieving tables in a power business database, belonging to the field of power data processing. The method includes: constructing a power domain dictionary and a knowledge graph of power business database tables based on collected power domain data; parsing a list of search keywords based on the power domain dictionary and user-input natural language; retrieving a sample of recalled data tables from the power business database based on a retrieval model and the search keyword list; predicting the recalled data table samples using a prediction model to obtain the corresponding SQL elements and user intent in the power business database corresponding to the user-input natural language; generating an SQL query statement based on the SQL elements, user intent, user-input natural language, and the knowledge graph of power business database tables; and performing a retrieval based on the SQL query statement. This invention can improve the retrieval accuracy of power business databases.

Description

Technical Field

[0001] This invention relates to the field of power data processing technology, and in particular to a method and apparatus for retrieving tables in a power business database. Background Technology

[0002] With the development of artificial intelligence technology and the widespread application of dialogue systems, statistical queries on information in databases directly through natural language questions have become a research and application hotspot. As a result, the technology of converting natural language text into structured query language SQL (Text2SQL) has emerged.

[0003] In power engineering applications, systems typically contain a large number of database tables and fields. To generate SQL statements using Text2SQL technology, it is necessary to first accurately retrieve the database tables and fields corresponding to the user's question, identify the corresponding SQL elements, and then generate the SQL statements through a dedicated Text2SQL model or a large model.

[0004] However, in practical systems, on the one hand, the large number of fields in a single table, the lack of standardization in table and field names, and the abundance of similar table and field names; on the other hand, statistical queries in business systems all require queries based on multiple tables. These factors contribute to low accuracy in database table and field retrieval, and even lower accuracy in generated SQL statements. Furthermore, in applications within the power sector, user questions are concise and use specialized vocabulary, implicitly containing a large amount of domain-specific knowledge; simultaneously, the business logic is complex, making it difficult to obtain accurate mappings directly through text similarity. Summary of the Invention

[0005] This invention provides a method and apparatus for retrieving tables in a power business database to solve the problem that existing power business database tables cannot be accurately retrieved.

[0006] In a first aspect, embodiments of the present invention provide a method for retrieving tables in a power business database, including:

[0007] Based on the collected data in the power sector, a knowledge graph of the power sector dictionary and power business database tables is constructed.

[0008] Based on the power industry dictionary and user input natural language, a list of search keywords is obtained. Based on the search model and the list of search keywords, a sample of recall data table is obtained by searching the power business database.

[0009] Based on the prediction model, the recalled data table samples are predicted to obtain the corresponding SQL elements and user intent in the power business database corresponding to the user's natural language input.

[0010] SQL query statements are generated based on SQL elements, user intent, user input natural language, and knowledge graph of power business database tables, and retrieval is performed based on the SQL query statements.

[0011] In one possible implementation, constructing a dictionary for the power sector includes:

[0012] Based on the characteristics and business needs of the power sector, the terminology in the power sector is defined as multi-level categories and relationships, and a conceptual map of the power sector dictionary is constructed based on all multi-level categories.

[0013] Keywords are extracted from multi-source heterogeneous data in the power sector, and the extracted keywords are classified based on the concept graph of the power sector dictionary. Entity relationships are also labeled based on multi-source heterogeneous data in the power sector.

[0014] The electric power domain dictionary is updated by extracting terms from a pre-trained triplet extraction model, and the terms and their entity relations are imported into a graph database to construct the electric power domain dictionary.

[0015] In one possible implementation, constructing a knowledge graph of power business database tables includes:

[0016] Based on the structure of the power business database tables, user input information, and business logic, define the table attributes and field attributes of the power business database tables;

[0017] Construct a unit system table corresponding to the different specialties involved in the power sector business;

[0018] Based on the field values ​​of the power business database table and the corresponding fields in the SQL statement, a code table is constructed to represent the relationship between the field values ​​of the power business database table and the corresponding fields in the SQL statement, thus forming a business knowledge mapping.

[0019] Based on business knowledge mapping, table attributes, field names and field attributes of power business database tables, and unit system tables, a table knowledge concept graph is constructed.

[0020] Based on the content of the pre-defined database tables and the table knowledge concept graph, the table knowledge is imported into the graph database to complete the construction of the power business database table knowledge graph.

[0021] In one possible implementation, a retrieval data table sample is obtained by searching the power business database based on a retrieval model and a list of search keywords, including:

[0022] The user's input natural language is preprocessed to obtain a list of search keywords;

[0023] Based on the table attributes and field attributes in the knowledge graph of the power business database table using a pre-trained language model, a set of information text feature vectors for the power business database table is constructed.

[0024] Based on the retrieval model that integrates keyword retrieval and a list of retrieval keywords, the information text feature vector set of the power business database table is retrieved to obtain a sample of the recalled data table.

[0025] In one possible implementation, the user's natural language input is preprocessed to obtain a list of search keywords, including:

[0026] Based on natural language processing tools and a dictionary for the power industry, the user's input natural language is processed to remove stop words, time-related words, and unit words, resulting in a list of search keywords.

[0027] In one possible implementation, based on a pre-trained language model of table attributes and field attributes in the knowledge graph of the power business database tables, a set of information text feature vectors for the power business database tables is constructed, including:

[0028] Based on the knowledge graph of the power business database table, the preset information of the power business database table is concatenated to obtain the concatenated data; the preset information includes the Chinese name of the table, table description, table synonyms, Chinese name of the field, field description and field synonyms of the power business database table.

[0029] Based on a pre-trained language model and a vector database, a vector index is created on the concatenated data to obtain a set of information text feature vectors for the power business database tables.

[0030] In one possible implementation, a predictive model is used to predict the recalled data table samples to obtain the corresponding SQL elements and user intent in the power business database corresponding to the user's natural language input, including:

[0031] Based on the table name prediction model, the table names of the recalled data table samples are predicted.

[0032] Based on the field and user intent prediction model, column augmentation is used to perform tasks such as condition field prediction, query field prediction, condition field value extraction, and condition field-to-condition field value matching on the recalled data table sample, and to predict user intent.

[0033] In one possible implementation, the table name prediction model is trained based on database samples recalled by the retrieval model;

[0034] The field and user intent prediction model is trained on the dataset based on question-and-answer SQL.

[0035] In one possible implementation, SQL query statements are generated based on SQL elements, user intent, user input natural language, and a knowledge graph of power business database tables, including:

[0036] Based on the target table field information obtained from SQL elements, write regular expressions to calculate the similarity of the target table field information and correct its time and unit to be consistent with the data format of the database content;

[0037] Based on user-input natural language, corrected SQL elements, and field attribute types and relationships in the knowledge graph of the power business database tables, the corrected target table field information is input into the large language model to generate SQL query statements.

[0038] Secondly, embodiments of the present invention provide an apparatus for retrieving tables from a power business database, comprising:

[0039] The module is used to build a knowledge graph of the power sector dictionary and power business database tables based on the collected power sector data;

[0040] The pre-retrieval module is used to parse a list of search keywords based on a dictionary of power industry data and natural language input from users. Based on the retrieval model and the list of search keywords, it retrieves a sample of recall data tables from the power business database.

[0041] The determination module is used to predict the recalled data table samples based on the prediction model, and obtain the corresponding SQL elements of the power business database corresponding to the natural language input by the user;

[0042] The retrieval module is used to generate SQL query statements based on SQL elements, user-input natural language, and the knowledge graph of power business database tables, and to perform retrieval based on the SQL query statements.

[0043] This invention provides a method and apparatus for retrieving tables from a power business database. First, based on collected power sector data, a power sector dictionary and a power business database table knowledge graph are constructed. Then, based on the power sector dictionary and user-input natural language, a list of search keywords is parsed. Based on a retrieval model and the search keyword list, the power business database is retrieved to obtain a sample of recalled data tables. Next, a prediction model is used to predict the recalled data table samples to obtain the corresponding SQL elements and user intent in the power business database corresponding to the user-input natural language. Finally, an SQL query statement is generated based on the SQL elements, user intent, user-input natural language, and the power business database table knowledge graph, and the retrieval is performed based on the SQL query statement. This invention, by constructing a power sector dictionary and a power business database table knowledge graph, can better adapt to the power sector and further improve the accuracy and professionalism of the retrieval. Furthermore, this invention improves the efficiency of retrieving large amounts of database content in engineering applications by using a retrieval model and a search keyword list, and improves the accuracy of identification by predicting the recalled data table samples using a prediction model. By constructing a dictionary of the power sector and a knowledge graph of power business database tables, complex business logic is effectively handled, and the professionalism and accuracy of domain information identification are improved. Furthermore, domain knowledge can be used to support subsequent application expansion. Attached Figure Description

[0044] To more clearly illustrate the technical solutions in the embodiments of the present invention, 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 the present invention. For those skilled in the art, other drawings can be obtained based on these drawings without creative effort.

[0045] Figure 1 This is a flowchart illustrating the implementation of the power business database table retrieval method provided in this embodiment of the invention;

[0046] Figure 2 This is a block diagram of a method for retrieving tables in a power business database provided in an embodiment of the present invention;

[0047] Figure 3 This is a schematic diagram of the structure of the power field dictionary provided in an embodiment of the present invention;

[0048] Figure 4 This is a schematic diagram of the knowledge graph of the power business database table provided in an embodiment of the present invention;

[0049] Figure 5 This is a schematic diagram of the structure of the device for retrieving power business database tables provided in an embodiment of the present invention. Detailed Implementation

[0050] 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 the invention. However, those skilled in the art will understand that the invention can 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 the invention with unnecessary detail.

[0051] To make the objectives, technical solutions, and advantages of the present invention clearer, specific embodiments will be described below in conjunction with the accompanying drawings.

[0052] Figure 1 and Figure 2 The process diagram of the method for retrieving power business database tables provided in the embodiments of the present invention is described in detail below:

[0053] S110. Based on the collected data in the power sector, construct a knowledge graph of the power sector dictionary and power business database tables.

[0054] Collect data in the power sector and construct knowledge about the power sector.

[0055] Domain knowledge construction includes building a dictionary of the power domain and a knowledge graph of power business database tables.

[0056] In some embodiments, the process of constructing a dictionary for the power industry is as follows:

[0057] First, based on the characteristics and business needs of the power sector, the terminology in the power sector is defined as multi-level categories and relationships, and a conceptual map of the power sector dictionary is constructed based on all multi-level categories.

[0058] Then, keywords are extracted from multi-source heterogeneous data in the power sector, and the extracted keywords are classified based on the concept graph of the power sector dictionary. Entity relationships are also labeled based on the multi-source heterogeneous data in the power sector.

[0059] Finally, the electric power domain terminology is updated by extracting terms from the pre-trained triplet extraction model, and the terms and their entity relations are imported into the graph database to construct the electric power domain terminology.

[0060] In this embodiment, domain term categories and relationships are defined according to the characteristics and business needs of the power sector. Domain terms are defined as a three-level classification structure with 9 primary categories, 26 secondary categories, and tertiary categories corresponding to domain term entities, and 5 types of entity relationships.

[0061] For example, the first-level categories are Location, Power Facilities, Organization, Power Knowledge, Business, Customers, Power Methods, Other, and Power Profession. The second-level categories are Location, Region, Primary Equipment, Secondary Equipment, System, Administrative Level, Enterprise Level, Physical Quantity, Indicator, Electricity Consumption, Power Supply, Power Generation, Classification, Property Rights, Status, Users, User Status, Metering Method, Assessment Method, Strategy, Marketing, Customer Service Center, Equipment, Finance, Development, and Materials. Entity relationships are hierarchical, synonymous, related, abbreviation, and compound word relationships. Based on this, a structure such as... Figure 3 The conceptual map of the domain dictionary shown provides support for subsequent generalization and application in power systems.

[0062] This process involves acquiring multi-source heterogeneous data from the power industry, extracting keywords from the text content of power industry documents and web pages using the TF-IDF term frequency statistics method, classifying the keywords based on the concept graph of the constructed domain dictionary in conjunction with the database table content, and annotating entity relationships in power industry documents and web page data. A triplet extraction model is then trained to automatically extract domain terms and dynamically update the domain dictionary. Finally, the domain terms and relationships are imported into the neo4j graph database to complete the construction of the domain terminology.

[0063] Specifically, the triplet extraction model uses the commonly used Named Entity Recognition (NER) model BiLSTM+CRF and the relation extraction model BGRU. Based on domain word types, training samples are obtained by labeling power industry text data with BIO tags, and the NER task is performed using BiLSTM+CRF. Based on domain word relations, training samples are obtained by labeling the relations of paired domain words appearing in power industry text data, and the relation extraction is performed using the BGRU model.

[0064] In some embodiments, the process of constructing a knowledge graph of power business database tables is as follows:

[0065] First, based on the structure of the power business database tables, user input information, and business logic, the table attributes and field attributes of the power business database tables are defined.

[0066] Then, construct a table of organizational structures corresponding to the different specialties involved in the power sector's business.

[0067] Next, based on the field values ​​of the power business database table and the corresponding fields in the SQL statement, a code table is constructed to represent the relationship between the field values ​​of the power business database table and the corresponding fields in the SQL statement, thus forming a business knowledge mapping.

[0068] Secondly, based on business knowledge mapping, table attributes, field names and field attributes of power business database tables, and unit system tables, a table knowledge concept graph is constructed.

[0069] Finally, based on the content of the pre-defined database tables and the table knowledge concept graph, the table knowledge is imported into the graph database to complete the construction of the power business database table knowledge graph.

[0070] In this embodiment, constructing a knowledge graph of power business database tables includes constructing a table knowledge concept graph and importing database knowledge.

[0071] Based on database table structure knowledge, user input information, and business logic, table and field attributes and relationship concepts are defined. Fields in the database tables are divided into five categories: dimension fields, statistical fields, other fields, time fields, and unit fields. Table attributes are enhanced with table synonyms and table descriptions, while field attributes are enhanced with field descriptions and field synonyms to enrich the semantic information of tables and fields. Simultaneously, unit system tables corresponding to different professions are constructed, representing the standard unit systems for marketing, materials, equipment, and finance. Based on the field values ​​and their corresponding fields in the SQL statements, a code table mapping the field values ​​to the corresponding fields is constructed to complete the business knowledge mapping. Based on business knowledge and requirements, the attributes and primary / foreign key relationships of tables, fields, code tables, and unit tables are defined, and then a system is constructed as follows: Figure 4 The diagram shows a knowledge graph of the power business database tables.

[0072] By using the given database table content and the constructed table knowledge concept graph, the table knowledge is imported into Neo4j to complete the construction of the database table knowledge graph.

[0073] S120. Based on the power industry dictionary and user input natural language, a list of search keywords is obtained through parsing. Based on the search model and the list of search keywords, a sample of the recall data table is obtained by searching the power business database.

[0074] In some embodiments, the user's input natural language can be preprocessed first to obtain a list of search keywords. Then, based on a pre-trained language model, a set of information text feature vectors for the power business database tables is constructed using the table attributes and field attributes in the knowledge graph. Finally, based on the retrieval model and the list of search keywords, the set of information text feature vectors for the power business database tables is retrieved to obtain a sample of the recalled data tables.

[0075] In this embodiment, the user's input natural language can be processed by natural language processing tools and a dictionary for the power industry to remove stop words, time-related words, and unit words, thereby obtaining a list of search keywords.

[0076] Specifically, Chinese NLP tools, such as Baidu LAC, can be used in conjunction with a domain dictionary to remove stop words, time and unit words from the natural language questions input by the user, and then perform word segmentation to obtain a word list.

[0077] For example, using Chinese NLP tools such as Baidu LAC combined with a domain dictionary, stop words, time and unit words are removed from the natural language questions input by the user. The results are then segmented into words to obtain a keyword list, as shown in the following example:

[0078] {

[0079] "input": "How many customers in Shijiazhuang had the role of manager in 2023?"

[0080] "output": "The client's role is that of a manager"

[0081] }

[0082] or

[0083] {

[0084] "input": "How many customers in Shijiazhuang had the role of manager in 2023?"

[0085] "output":["Client Role","Manager","Client role is Manager"]

[0086] }

[0087] In some embodiments, a training data preparation phase is also included before the user input natural language is preprocessed.

[0088] Specifically, based on the given database structure information and provided typical question samples, the typical user input questions are labeled with a sequence of tags, including condition fields, condition field values, query fields, and table name information. The user input questions are then categorized according to user intent, resulting in question-and-answer SQL pairs of user questions and table information, in the following format:

[0089] {

[0090] "table_name":["corresponding table name"],

[0091] "question":"Enter your question",

[0092] "question_tok":"Enter the question string",

[0093] "sql":{

[0094] "sel":["query field"],

[0095] "conds":[["condition field 1","condition field value 1"]],

[0096] },

[0097] "col_value": [Values ​​for each column],

[0098] "where_val_index":[[start position of the condition field value, end position of the condition field value]],

[0099] "value_tags":[Enter question 0 / 1 tags],

[0100] "value_match":[0,0,1],

[0101] "intent": "User intent"

[0102] }

[0103] The following is an example of a question-and-answer SQL query with sample data:

[0104] {

[0105] "table_name":["Customer Role"],

[0106] "question":"How many customers in Shijiazhuang had the role of manager in 2023?"

[0107] "question_tok":["Shi","Jia","Zhuang","2023","Year","Guest","Household","Role","Color","For","Manager","Manager","Of","Have","Many","Few"],

[0108] "sql":{

[0109] "sel":["Business Partner Role Identifier"],

[0110] "conds":[["Company Code","Shijiazhuang"],["Effective Date","2023"],["Business Partner Role Type","Manager"]]

[0111] },

[0112] "col_value":["","2023","","","","","Manager","","","",""],

[0113] "where_val_index":[[0,2],[3,4],[10,12]],

[0114] "value_tags":[1,1,1,1,1,0,0,0,0,0,1,1,1,0,0,0,0],

[0115] "value_match":[0,1,1]

[0116] "intent":"query_number"

[0117] }

[0118] Based on the search requirements, a question-table sample pair dataset for retrieval is constructed. The correspondence between user input questions and search samples is labeled. When constructing the question-table sample pair dataset for retrieval, the user input needs to be preprocessed to obtain keywords. The specific format of each sample is as follows:

[0119] {

[0120] "input":"User input preprocessing result",

[0121] "para":"Field Chinese Name (Field Value) & Table Chinese Name",

[0122] "label":0 / 1

[0123] }

[0124] The question-table sample pair is as follows:

[0125] {

[0126] "question":"Customer Role Manager",

[0127] "para":"Customer Roles & Business Partner Role Types & Managers"

[0128] "label":1

[0129] }

[0130] When training data is limited, data augmentation is necessary to expand the data. This invention performs data augmentation by randomly replacing keywords in the problem. It identifies conditions in the SQL query, correlates them with values ​​in the database, and replaces them with other values ​​in that column.

[0131] In some embodiments, a full-text search tool is used to build a keyword index on the data in the table knowledge graph, and a search is performed based on the preprocessed keyword list information input by the user to obtain relevant table information content. A vector index is built using a dual-tower model combined with the Faiss vector database to obtain a set of feature vectors for the database table information text, and the similarity between two inputs is calculated using cosine distance. A dataset of question-table sample pairs used for retrieval is constructed, and the interaction model is fine-tuned to make the associated question text and data tables more similar in feature space. The keyword retrieval results are fused based on the dual-tower interaction model to obtain the recall table content.

[0132] Specifically, the database table structure, constructed for the data in the table knowledge graph and the schema of the database tables, consists of the following components: table name (table_name), table description (table_dec), table synonym (table_sim), field name (col_name), field description (col_dec), and field synonym (col_sim) (field value (col_value)) concatenated using the '&' symbol.

[0133] Table="table_name&table_sim&table_dec&col_name&col_sim(col_value)&col_dec".

[0134] For example, we can first construct the database retrieval text based on the data in the table knowledge graph and the schema of the database table. The specific format is: table name (Chinese name): table_name, table description (table_dec), table synonym (table_sim), field name (Chinese name): col_name, field description (col_dec), field synonym (col_sim), (field value): col_value, concatenated with the "&" symbol. The specific format is as follows:

[0135] Table="table_name&table_sim&table_dec&col_name&col_sim(col_value)&col_dec".

[0136] For example, Table = "Customer Roles & Business Partner Role Types & Managers".

[0137] First, based on the knowledge graph of the power business database tables, pre-defined information from the power business database tables is concatenated to obtain concatenated data. This pre-defined information includes the Chinese names, descriptions, synonyms, field names, field descriptions, and field synonyms of the power business database tables. Then, based on a dual-tower model combined with a vector database, a vector index is created on the concatenated data to obtain a set of information text feature vectors from the power business database tables.

[0138] The ElasticSearch retrieval tool was used to create a keyword index for the data in the table knowledge graph. Based on the preprocessed keyword list information input by the user, the most relevant table information content was retrieved. Then, RocketQA's dual-tower model combined with the Faiss vector database was used to create a vector index for the database table content, resulting in a set of information text feature vectors for the database table. The interaction model of the dataset was fine-tuned using question-table samples, and further retrieval was performed based on the fine-tuned model to make the related question text and data table more similar in feature space, and to narrow down the prediction range of subsequent tables and fields.

[0139] S130. Based on the prediction model, predict the recalled data table samples to obtain the corresponding SQL elements and user intent of the power business database corresponding to the user's input natural language.

[0140] In some embodiments, table name prediction can be performed on the recalled data table samples based on a table name prediction model. Then, based on a field and user intent prediction model, column augmentation is used to perform tasks such as condition field prediction, query field prediction, condition field value extraction, and condition field-to-condition field value matching on the recalled data table samples, and to predict user intent.

[0141] The table name prediction model is trained based on database samples recalled by the retrieval model, while the field and user intent prediction model is trained based on the question-and-answer SQL dataset.

[0142] Specifically, based on the retrieval recall table information, table and field prediction models are trained on the dataset using prepared question-and-answer SQL. Two BERT models are used to train one model to obtain the correct table name and another to predict SQL components such as field information and user intent. The table name prediction model is trained based on database table samples recalled by the retrieval model, calculating the table name corresponding to the user input at the BERT encoding layer. The field and user intent prediction models are trained on the dataset using question-and-answer SQL, utilizing the BERT model to complete four sub-tasks, including column prediction and field value extraction. Column prediction employs column augmentation to predict the SQL components such as the field information corresponding to the user input and to predict the user intent.

[0143] The column augmentation method utilizes the hidden layer states obtained from the encoder of the BERT pre-trained language model, calculates the attention of the token output corresponding to each column name, and performs a weighted sum with the [CLS] vector information at the beginning of the question and the [CLS] vector information corresponding to the target table to obtain the column augmentation representation of each column. The column augmentation of the t-th column can be described by the following formula:

[0144]

[0145] in, Let a represent the sum of attention weights in column t. ti h′ represents the attention weight of the i-th token in column t. [CLS] h represents the information representation of each table. cti Let r represent the Bert representation of the i-th token in column t. ct The enhanced representation of column t is achieved by adding... Attention weights in column t, and h [CLS] Global representation and h [ ′ CLS] The information is obtained from the representation of each table.

[0146] For example, for the table name prediction model, the encoder of the BERT pre-trained language model is used to concatenate the user input text (with stop words removed and time intervals) with the recall table information and then encode it. The input text and table information, as well as table information itself, are separated using [SEP][CLS]. The table name and column name are separated using [SEP]. The concatenated input information is as follows:

[0147] Input = [[CLS], w1, w2, ..., w k ,[SEP][CLS],t m1 ,t m2 ,...,t mi [SEP],h m1 ,h m1 ,...,

[0148] h mj ,[SEP][CLS],...,[SEP]], where w k t represents the k-th character entered by the user. mj h is the i-th character of the table name of the m-th related table in the table information. mj This represents the field name of the j-th column in the m-th related table information; the output vector of each token in the first and last encoding layers of Bert is extracted, concatenated, and then average pooling is used to obtain the final vector, which is then input into the linear classification layer output to finally obtain the corresponding table information;

[0149] The field and user intent prediction model mainly consists of two parts: predicting user intent and accurately predicting the corresponding field information of user input. Predicting user intent includes:

[0150] Using the encoder of a BERT pre-trained language model, after encoding the stop-word-removed user input text and the obtained target table information, the hidden state based on the first special tag [CLS] is represented as h. [CLS]As a representation of its entire input sequence, it undergoes a linear transformation and is then passed through softmax to predict the user's intent category. The above process can be described by the following formula:

[0151] p1 = sigmoid(W1h) [CLS] );

[0152] Where p1 represents the user intent category, W1 represents the learning parameters, and h [CLS] It is a global representation of the user's input question and table, and predicts the user's intent using a sigmoid function.

[0153] For example, SQL components can be divided into condition field prediction, query field prediction, condition field value extraction, and condition field-to-condition field value matching. Condition field prediction, query field prediction, condition field value extraction, and condition field-to-condition field value matching can be accomplished using column enhancement methods.

[0154] Conditional field prediction and query field prediction utilize the enhanced representation of each column and apply different linear transformations to obtain the probability of that column appearing. Conditional field prediction and query field prediction can be described by the following formulas:

[0155] p2 = sigmoid(W2r) ct );

[0156] p3 = sigmoid(W3r) ct );

[0157] Where p2 represents the probability that column t is a query field, p3 represents the probability that column t is a condition field, and W2 and W3 are learning rates, both of which are 1×d matrices.

[0158] For the task of extracting conditional field values, the user input is labeled with 0 / 1, where 0 represents a non-conditional field value and 1 represents a conditional field value. The conditional field value is extracted by performing a linear transformation on the representation of each token in the user input and using the sigmoid function to predict whether it is 1 or 0.

[0159] For the task of matching condition fields with condition field values, if the phrase consists of multiple tokens, the average of the representations of all tokens is calculated, as shown in the following formula:

[0160]

[0161] Where h v The extracted values ​​are represented by s and e, which represent the start and end positions of the condition field values, and l represents the length of the extracted values. The similarity score between the extracted phrases and the condition column is calculated, and then the sigmoid function is used to determine if the phrases and columns match. The formula is described below:

[0162] matchh i =sigmoid(μ·tan(W4h) v +W5rc i ));

[0163] matchh i This represents the matching score between the extracted value and the i-th condition field, μ is the learning rate, and W4 and W5 are d×d matrices.

[0164] The output format of SQL elements is:

[0165] {

[0166] "table_name": ["table name"],

[0167] "query_name": ["table name.query field name"],

[0168] "filter_name":["table_name.condition_field_name"],

[0169] "slots":{

[0170] "query": "query field name",

[0171] "Condition Field Label 1":"Condition Field Value 1",

[0172] "Condition Field Label 2":"Condition Field Value 2",

[0173] }

[0174] }

[0175] S140. Generate SQL query statements based on SQL elements, user intent, and user input natural language, as well as the knowledge graph of the power business database tables, and perform retrieval based on the SQL query statements.

[0176] In this embodiment, based on the target table field information obtained from the SQL elements, regular expressions can be written to calculate the similarity of the target table field information, and the time and unit can be corrected to be consistent with the data format of the database content. Then, based on the user's input natural language, the corrected SQL elements, and the field attribute types and relationships in the knowledge graph of the power business database tables, the corrected target table field information is input into the large language model to generate an SQL query statement.

[0177] In some embodiments, based on the target table field information obtained during the SQL feature recognition stage, regular expressions and similarity calculations are first written to correct the time and units to match the database content format. Then, based on user intent and combined with the field attribute types and relationships in the table knowledge graph, a prompt is written to correct the condition field values ​​to match the database table content. This, combined with the unit table, code table, and business information, is then input into the generative large language model to obtain the final SQL statement. An example of a prompt input into the large model is as follows:

[0178] The prompt states: "There are two database tables: 'Installation Point Billing Card buf_cms20_emss_blc.inst_bilg_card' and 'Energy Customer buf_cms20_emss_cuc.cust'. These two tables are linked by the customer ID cust_no. The 'Installation Point Billing Card' table contains fields for 'Month / Year Billing qty_charg_ym', 'Management Unit Code mgt_org_code', 'Summary Calculation t_settle_qty', and 'Customer ID cust_no'. The 'Energy Customer' table contains fields for 'Customer Name cust_name' and 'Customer ID cust_no'. The query field is the total..." The settlement quantity field has the following conditions: the condition field is the month and year of the charge, the field type is varchar, and the field value is [202309]. The management unit code field is also varchar. The corresponding unit code needs to be found based on the org_name relationship in the buf_cms20_emss_pbusi.p_org table. The unit name is [Shijiazhuang]. The customer name field is varchar, and the field value is [Li Gang]. The user intent is to count. Please generate the corresponding SQL query statement based on the user's question. The generated SQL statement needs to be able to correctly extract the required information from the database. User question: What was Li Gang's electricity consumption in Shijiazhuang in September?

[0179] output="SELECT t_settle_qty

[0180] FROM buf_cms20_emss_blc.inst_bilg_card inst_bilg_card

[0181] JOIN buf_cms20_emss_cuc.cust cust ON inst_bilg_card.cust_no=cust.cust_no JOIN buf_cms20_emss_pbusi.p_org org ON inst_bilg_card.mgt_org_code=org.org_no

[0182] WHERE inst_bilg_card.qty_charg_ym='202309'

[0183] AND org.org_name = 'Shijiazhuang'

[0184] AND cust.cust_name = 'Li Gang';

[0185] It should be noted that the above prompt only specifies the required content. There is no strict rule regarding the format and order of the content in the prompt, and other explanatory content can be added.

[0186] This invention effectively supports multi-table retrieval and has strong adaptability to various professional fields. By combining keyword and vector retrieval, it can efficiently retrieve table information related to questions from a large number of database tables, and by incorporating domain-specific business knowledge during the retrieval process, it further improves the accuracy and professionalism of the retrieval.

[0187] This invention simplifies the overall difficulty of the Text2SQL task while effectively handling complex business logic. It divides the entire task into two parts: SQL element identification and SQL statement generation. In SQL element identification, only the table and field content corresponding to the question are identified, and column augmentation is used to improve the accuracy of field identification. In SQL statement generation, prior language knowledge from a large model is utilized, and the SQL statement is generated by combining business logic from domain knowledge through a prompt mechanism. This avoids the complex intermediate calculation process in existing technologies, improves matching speed, and gives the invention excellent generalization ability.

[0188] This invention provides a method for retrieving tables from a power business database. First, based on collected power sector data, a power sector dictionary and a power business database table knowledge graph are constructed. Then, based on the power sector dictionary and user-input natural language, a list of search keywords is parsed. Using a retrieval model and the search keyword list, the power business database is searched to obtain a sample of recalled data tables. Next, a prediction model is used to predict the recalled data table samples, obtaining the corresponding SQL elements and user intent of the power business database corresponding to the user-input natural language. Finally, an SQL query statement is generated based on the SQL elements, user intent, user-input natural language, and the power business database table knowledge graph, and the retrieval is performed based on the SQL query statement. This invention, by constructing a power sector dictionary and a power business database table knowledge graph, can better adapt to the power sector and further improve the accuracy and professionalism of the retrieval. Furthermore, this invention's method based on a retrieval model and a search keyword list improves the efficiency of retrieving large amounts of database content in engineering applications, and the prediction model for the recalled data table samples improves the accuracy of identification. By constructing a dictionary of the power sector and a knowledge graph of power business database tables, complex business logic is effectively handled, and the professionalism and accuracy of domain information identification are improved. Furthermore, domain knowledge can be used to support subsequent application expansion.

[0189] It should be understood that the sequence number of each step in the above embodiments does not imply the order of execution. The execution order of each process should be determined by its function and internal logic, and should not constitute any limitation on the implementation process of the embodiments of the present invention.

[0190] The following are device embodiments of the present invention. For details not described in detail, please refer to the corresponding method embodiments described above.

[0191] Figure 5 The diagram shows a schematic representation of a device for retrieving a power business database table according to an embodiment of the present invention. For ease of explanation, only the parts relevant to the embodiment of the present invention are shown, and are described in detail below:

[0192] like Figure 5 As shown, a device 500 for retrieving tables from a power business database includes:

[0193] Module 510 is used to build a knowledge graph of the power sector dictionary and power business database tables based on the collected power sector data;

[0194] The pre-retrieval module 520 is used to parse a list of search keywords based on a dictionary of power fields and natural language input by the user, and to retrieve a sample of recall data tables from the power business database based on the retrieval model and the list of search keywords.

[0195] The determination module 530 is used to predict the recalled data table samples based on the prediction model to obtain the corresponding SQL elements of the power business database corresponding to the natural language input by the user.

[0196] The retrieval module 540 is used to generate SQL query statements based on SQL elements, user input natural language, and knowledge graph of power business database tables, and to perform retrieval based on the SQL query statements.

[0197] In one possible implementation, module 510 is used to define terms in the power sector as multi-level categories and relationships based on the characteristics and business needs of the power sector, and to construct a conceptual map of the power sector dictionary based on all multi-level categories.

[0198] Keywords are extracted from multi-source heterogeneous data in the power sector, and the extracted keywords are classified based on the concept graph of the power sector dictionary. Entity relationships are also labeled based on multi-source heterogeneous data in the power sector.

[0199] The electric power domain dictionary is updated by extracting terms from a pre-trained triplet extraction model, and the terms and their entity relations are imported into a graph database to construct the electric power domain dictionary.

[0200] In one possible implementation, module 510 is used to define the table attributes and field attributes of the power business database table based on the structure of the power business database table, user input information, and business logic.

[0201] Construct a unit system table corresponding to the different specialties involved in the power sector business;

[0202] Based on the field values ​​of the power business database table and the corresponding fields in the SQL statement, a code table is constructed to represent the relationship between the field values ​​of the power business database table and the corresponding fields in the SQL statement, thus forming a business knowledge mapping.

[0203] Based on business knowledge mapping, table attributes, field names and field attributes of power business database tables, and unit system tables, a table knowledge concept graph is constructed.

[0204] Based on the content of the pre-defined database tables and the table knowledge concept graph, the table knowledge is imported into the graph database to complete the construction of the power business database table knowledge graph.

[0205] In one possible implementation, the pre-retrieval module 520 is used to preprocess the user's input natural language to obtain a list of search keywords;

[0206] Based on the table attributes and field attributes in the knowledge graph of the power business database table using a pre-trained language model, a set of information text feature vectors for the power business database table is constructed.

[0207] Based on the retrieval model that integrates keyword retrieval and a list of retrieval keywords, the information text feature vector set of the power business database table is retrieved to obtain a sample of the recalled data table.

[0208] In one possible implementation, the pre-retrieval module 520 is used to process the user's input natural language by removing stop words, time-related words, and unit words based on natural language processing tools and a dictionary for the power industry, thereby obtaining a list of search keywords.

[0209] In one possible implementation, the pre-retrieval module 520 is used to concatenate preset information of the power business database table based on the knowledge graph of the power business database table to obtain concatenated data; wherein, the preset information includes the Chinese name of the table, table description, table synonyms, Chinese name of the field, field description and field synonym of the power business database table.

[0210] Based on a pre-trained language model and a vector database, a vector index is created on the concatenated data to obtain a set of information text feature vectors for the power business database tables.

[0211] In one possible implementation, module 530 is used to predict table names for the recalled data table samples based on the table name prediction model.

[0212] Based on the field and user intent prediction model, column augmentation is used to perform tasks such as condition field prediction, query field prediction, condition field value extraction, and condition field-to-condition field value matching on the recalled data table sample, and to predict user intent.

[0213] In one possible implementation, the table name prediction model is trained based on database samples recalled by the retrieval model;

[0214] The field and user intent prediction model is trained on the dataset based on question-and-answer SQL.

[0215] In one possible implementation, the retrieval module 540 is used to generate SQL query statements based on SQL elements, user intent, user input natural speech, and a knowledge graph of power business database tables, including:

[0216] Based on the target table field information obtained from SQL elements, write regular expressions to calculate the similarity of the target table field information and correct its time and unit to be consistent with the data format of the database content;

[0217] Based on user-input natural language, corrected SQL elements, and field attribute types and relationships in the knowledge graph of the power business database tables, the corrected target table field information is input into the large language model to generate SQL query statements.

[0218] In the above embodiments, the descriptions of each embodiment have different focuses. For parts that are not described in detail or recorded in a certain embodiment, please refer to the relevant descriptions of other embodiments.

[0219] Those skilled in the art will recognize that the templates, units, and algorithm steps of the various examples described in conjunction with the embodiments disclosed herein can be implemented in electronic hardware, or a combination of computer software and electronic hardware. Whether these functions are implemented in hardware or software depends on the specific application and design constraints of the technical solution. Those skilled in the art can use different methods to implement the described functions for each specific application, but such implementations should not be considered beyond the scope of this invention.

[0220] If the module / unit is implemented as a software functional unit and sold or used as an independent product, it can be stored in a computer-readable storage medium. Based on this understanding, all or part of the processes in the above embodiments of the present invention can also be implemented by a computer program instructing related hardware. The computer program can be stored in a computer-readable storage medium. When executed by a processor, the computer program can implement the steps of the above-described methods for retrieving tables in the power business database for Text2SQL. The computer program includes computer program code, which can be in the form of source code, object code, executable files, or certain intermediate forms. The computer-readable medium can include: any entity or device capable of carrying the computer program code, a recording medium, a USB flash drive, a portable hard drive, a magnetic disk, an optical disk, a computer memory, a read-only memory, a random access memory, an electrical carrier signal, a telecommunication signal, and a software distribution medium, etc.

[0221] The above-described embodiments are only used to illustrate the technical solutions of the present invention, and are not intended to limit it. Although the present invention 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 the present invention, and should all be included within the protection scope of the present invention.

Claims

1. A method for retrieving tables in a power business database, characterized in that, include: Based on the collected data in the power sector, a knowledge graph of the power sector dictionary and power business database tables is constructed. Based on the power industry dictionary and user input natural language, a list of search keywords is obtained. Based on the search model and the list of search keywords, a sample of recall data table is obtained by searching the power business database. Based on the prediction model, the recalled data table sample is predicted to obtain the corresponding SQL elements and user intent of the power business database corresponding to the user's input natural language; Based on the SQL elements, user intent, user input natural language, and the knowledge graph of the power business database table, an SQL query statement is generated, and a retrieval is performed based on the SQL query statement; The step of retrieving the power business database based on the retrieval model and the list of retrieval keywords to obtain a sample recall data table includes: The user input natural language is preprocessed to obtain a list of search keywords; based on the pre-trained language model, the table attributes and field attributes in the knowledge graph of the power business database table are used to construct a set of information text feature vectors for the power business database table; based on the retrieval model, the information text feature vectors of the power business database table are retrieved by fusing keyword retrieval and the list of search keywords to obtain a sample of the recall data table. The construction of the power field dictionary includes: Based on the characteristics and business needs of the power sector, power sector terms are defined as multi-level categories and relationships. A concept graph of the power sector dictionary is constructed based on all multi-level categories. Keywords are extracted from multi-source heterogeneous data in the power sector, and the extracted keywords are classified based on the concept graph of the power sector dictionary. Entity relationships are also labeled based on the multi-source heterogeneous data in the power sector. Power sector terms are extracted and updated using a pre-trained triplet extraction model. Finally, power sector terms and their entity relationships are imported into a graph database to construct the power sector dictionary. Constructing the knowledge graph of the power business database tables includes: Based on the structure of the power business database tables, user input information, and business logic, the table attributes and field attributes of the power business database tables are defined; a unit system table corresponding to different specialties involved in power industry business is constructed; based on the field values ​​of the power business database tables and the corresponding fields in SQL statements, a code table is constructed to represent the relationship between the field values ​​of the power business database tables and the corresponding fields in SQL statements, forming a business knowledge mapping; based on the business knowledge mapping, the table attributes, field names, and field attributes of the power business database tables, and the unit system table, a table knowledge concept graph is constructed; based on the content of the preset database tables and the table knowledge concept graph, the table knowledge is imported into a graph database to complete the construction of the power business database table knowledge graph.

2. The method for retrieving tables in a power business database according to claim 1, characterized in that, The preprocessing of the user's input natural language to obtain a list of search keywords includes: The user-input natural language is processed using natural language processing tools and the aforementioned power industry dictionary to remove stop words, time-related terms, and unit words, thereby obtaining the search keyword list.

3. The method for retrieving tables in a power business database according to claim 1, characterized in that, The method involves constructing a set of information text feature vectors for the power business database tables based on table attributes and field attributes in the knowledge graph of the power business database tables using a pre-trained language model. This set includes: Based on the knowledge graph of the power business database table, the preset information of the power business database table is concatenated to obtain concatenated data; wherein, the preset information includes the Chinese name of the table, table description, table synonyms, Chinese name of the field, field description and field synonyms of the power business database table; Based on a pre-trained language model and a vector database, a vector index is established on the concatenated data to obtain a set of information text feature vectors for the power business database table.

4. The method for retrieving tables in a power business database according to claim 1, characterized in that, The prediction based on the prediction model on the recalled data table sample, to obtain the corresponding SQL elements and user intent of the power business database corresponding to the user's input natural language, includes: Based on the table name prediction model, the table names of the recalled data table samples are predicted. Based on the field and user intent prediction model, column enhancement is used to perform tasks such as condition field prediction, query field prediction, condition field value extraction, and condition field-to-condition field value matching on the recalled data table sample, and to predict user intent.

5. The method for retrieving tables in a power business database according to claim 4, characterized in that, The table name prediction model is trained based on database samples recalled by the retrieval model; The field and user intent prediction model are obtained by training the dataset based on question-and-answer SQL.

6. The method for retrieving tables in a power business database according to claim 1, characterized in that, The process of generating an SQL query statement based on the SQL elements, user intent, user input natural language, and the knowledge graph of the power business database tables includes: Based on the target table field information obtained from the SQL elements, a regular expression is written to calculate the similarity of the target table field information and correct its time and unit to be consistent with the data format of the database content. Based on the user's input natural language, the corrected SQL elements, and the field attribute types and relationships in the knowledge graph of the power business database table, the corrected target table field information is input into the large language model to generate the SQL query statement.

7. A device for retrieving tables in a power business database, characterized in that, include: The module is used to build a knowledge graph of the power sector dictionary and power business database tables based on the collected power sector data; The pre-retrieval module is used to parse a list of search keywords based on the power industry dictionary and the user's input natural language, and to retrieve a sample of recall data tables from the power business database based on the retrieval model and the list of search keywords. The determination module is used to predict the recalled data table sample based on the prediction model to obtain the corresponding SQL element and user intent of the power business database corresponding to the user input natural language; The retrieval module is used to generate an SQL query statement based on the SQL elements, user intent, user input natural language, and the knowledge graph of the power business database table, and to perform retrieval based on the SQL query statement; The pre-retrieval module is further used to preprocess the natural language input by the user to obtain a list of search keywords; construct a set of information text feature vectors of the power business database table based on the table attributes and field attributes in the knowledge graph of the power business database table using a pre-trained language model; and retrieve the set of information text feature vectors of the power business database table based on the retrieval model that integrates keyword retrieval and the list of search keywords to obtain a recall data table sample. The construction module is also used to define power domain terms as multi-level categories and relationships based on the characteristics and business needs of the power domain, and to construct a concept graph of the power domain dictionary based on all multi-level categories; extract keywords from multi-source heterogeneous data in the power domain, classify the extracted keywords based on the concept graph of the power domain dictionary, and annotate entity relationships based on multi-source heterogeneous data in the power domain; extract power domain terms based on a pre-trained triplet extraction model to update the power domain dictionary, and import the power domain terms and their entity relationships into a graph database to construct the power domain dictionary; The construction module is also used to define the table attributes and field attributes of the power business database table based on its structure, user input information, and business logic; construct a unit system table corresponding to different specialties involved in power industry business; construct a code table of the relationship between the field values ​​of the power business database table and the corresponding fields of the SQL statement based on the field values ​​of the power business database table, forming a business knowledge mapping; construct a table knowledge concept graph based on the business knowledge mapping, the table attributes, field names and field attributes of the power business database table, and the unit system table; and import the table knowledge into a graph database based on the content of the preset database table and the table knowledge concept graph to complete the construction of the power business database table knowledge graph.

Images