A text2sql optimization method based on dimension model modeling

Abstract

The application provides a text2sql optimization method based on a dimensional model, comprising the following steps: S1: analyzing a text2sql application business scenario, defining an application field, collecting a capability problem set, determining data requirements and business processes; S2: performing word segmentation processing on the capability problem set, and listing entity tables; S3: determining facts, dimensions and corresponding attributes of a dimensional model; S4: constructing a dimensional model bus matrix according to the dimensional model; S5: constructing an entity information table of the dimensional model, and converting the entity information table into an entity dictionary; S6: constructing a named entity recognition model based on the dictionary, and recognizing entities, involved tables, associated conditions, query conditions and grouping fields in a question; S7: constructing an aggregation function judgment model to recognize aggregation functions and aggregation entity fields; and S8: converting the recognition results of steps S6 and S7 into executable sql query statements. The method can simplify the complexity of sql templates in a template matching algorithm, and simultaneously reduce the difficulty of dictionary construction.

Description

Technical Field

[0001] This invention relates to the field of natural language processing technology, and in particular to a text2sql optimization method based on dimensional modeling. Background Technology

[0002] In real life, there are numerous databases storing information from various industries. SQL is a database query language with great flexibility and powerful functionality. However, SQL has a relatively high learning curve, and for computer professionals, writing a large number of accurate SQL statements for different databases and application scenarios is quite cumbersome. If we had a tool that could automatically convert our descriptions into SQL queries and then let the computer execute them, we could easily query databases, greatly improving our efficiency in life and work.

[0003] Text-to-SQL is a technology that transforms natural language into SQL query statements. Text2SQL (text to SQL) is a task in the field of natural language understanding and semantic parsing. Its goal is to directly convert the user's natural language into executable SQL query statements given a relational database (or table). It breaks down the barriers between humans and structured databases, providing users who are not familiar with business tables or query languages ​​with tools to interact with databases, thereby improving user efficiency and the value of structured data.

[0004] Early text2sql tasks required manually defined fixed rules, lacked semantic understanding, and were also lacking in transferability and scalability. Then came pipeline methods, which could not directly generate SQL but inferred SQL statements from intermediate expressions, and could not handle complex user input, resulting in poor model performance. Finally, deep learning-based algorithms, with encoder-decoder frameworks becoming mainstream, were developed. The encoder uses neural networks to perform semantic encoding and interaction of natural language, while the decoder uses grammatical rules or neural networks to perform end-to-end decoding and output.

[0005] Current deep learning-based algorithms perform well on single-table text2sql tasks, but require a large amount of training data. Their performance is less than satisfactory when performing multi-table joins. Furthermore, current text2sql algorithms mainly optimize recognition performance from the model perspective, because when performing multi-table joins, the model needs to better construct the mapping relationship between text and SQL, make better use of the attributes in the table, and pay more attention to the decoding process. However, they rarely reduce the difficulty of the task from the data perspective. Summary of the Invention

[0006] The purpose of this invention is to overcome the problems of high task difficulty and low query accuracy of existing text2sql methods when performing multi-table join queries. It proposes a text2sql optimization method based on dimensional modeling, which can reduce the task difficulty and improve the query accuracy when performing multi-table join queries.

[0007] To achieve the above objectives, the present invention adopts the following technical solution:

[0008] A text2sql optimization method based on dimensional modeling, comprising the following steps:

[0009] S1: Analyze the business scenarios of text2sql applications, define the application domain, collect capability question sets, and determine data requirements and business processes;

[0010] S2: Combining domain knowledge, the set of capability questions collected in step S1 is processed by word segmentation, keywords are sorted out and summarized into entities, and an entity table is listed.

[0011] S3: Based on the entity table and the capability question set in step S1, determine the facts, dimensions, and corresponding attributes of the dimensional model numerical model;

[0012] S4: Construct the dimensional model bus matrix based on the dimensional model in step S3;

[0013] S5: Combining the entity table in step S2, construct an entity information table for the dimensional model in step S3, and convert the entity information table into an entity dictionary;

[0014] S6: Construct a dictionary-based named entity recognition model to identify entities, tables involved, association conditions, query conditions, and grouping fields in questions;

[0015] S7: Construct an aggregate function judgment model to identify aggregate functions and aggregate entity fields;

[0016] S8: Based on the identification results of steps S6 and S7, convert them into executable SQL query statements.

[0017] Furthermore, in step S1, the competency question set is a list of questions that can be answered.

[0018] Furthermore, in step S3, based on the entity table and the original entity-relationship modeling table, and combined with the business processes in the capability problem set, the original entity-relationship model is transformed into a dimensional model containing fact tables and dimension tables, following the idea of ​​dimensional modeling.

[0019] Furthermore, in step S3, dimensional modeling includes the following steps:

[0020] S3.1: Select a business process, which defines specific design goals, granularity, dimensions, and facts;

[0021] S3.2: Declare granularity, which is the meaning of the measure in the fact table of the selected business process;

[0022] S3.3: Identify the dimensions and their corresponding attributes, where dimensions are descriptive information about the elements of events in the fact table, and attributes are descriptions used to filter and classify facts;

[0023] S3.4: Confirm the facts. Facts are the measurements of business process events. There is a one-to-one relationship between a fact table row and the measurement event described according to the granularity of the fact table.

[0024] Furthermore, in step S4, a dimensional model bus matrix is ​​constructed, including clarifying the types of business processes, clarifying the dimensions related to each business process, and adding two columns to the business process to distinguish the granularity and measurement of business process events.

[0025] Furthermore, in step S5, each entity contains the following information: the first character of the entity term, the entity term, the attribute value of the dimension table corresponding to the specific entity value in the database or the fact of the fact table, the field name of the entity in the dimension model, and the name of the dimension table or fact table in which the entity is located in the dimension model.

[0026] Furthermore, step S6 specifically includes the following steps:

[0027] S6.1: Segment the question into a character sequence, and use the first character as the first character to match it with the entity dictionary; if no entity is matched, output the character.

[0028] S6.2: If the first character matches an entity in the entity list, combine the characters following it in the character sequence to form an entity word, and then match it with the entity dictionary. If a match is found, continue combining the following characters to match entities until the longest entity in the entity list is matched and then return. If no match is found, output the character.

[0029] S6.3: Starting from the character following the returned entity or character, repeat steps 1 and 2 until all characters in the character sequence have been traversed, and return all entities and other characters that are not entities.

[0030] Furthermore, step S7 specifically includes the following steps:

[0031] S7.1: The BERT pre-trained model converts the entity recognition results into vectors to represent the sentence;

[0032] S7.2: Deep learning embedding modules convert entity recognition results into vectors to represent the sentence;

[0033] S7.3: Concatenate the vector based on the BERT pre-trained model embedding representation and the vector based on the deep learning embedding representation together to represent the meaning of the sentence;

[0034] S7.4: Based on task requirements, continuously train and adjust the concatenation vector and transformer model to identify and return the aggregation functions and aggregation entity fields in the sentence.

[0035] Furthermore, in step S8, the entities, tables involved, association conditions, query conditions, and grouping fields in the question identified in step S6, and the aggregate functions and aggregate entities identified in step S7 are parsed into executable SQL query statements.

[0036] Furthermore, the optimization method also includes step S9: maintaining and updating the entity information table.

[0037] Compared with existing technologies, the text2sql optimization method based on dimensional modeling provided by this invention has the following beneficial effects:

[0038] 1. The text2sql optimization method based on dimensional modeling provided in this invention does not employ the traditional entity-relationship model, but instead uses a dimensional model. This simplifies the complexity of SQL templates in template matching algorithms, thereby reducing the difficulty of the text2sql problem. Simultaneously, it maps the dimensions, attributes, and attribute values ​​in the dimensional model to dictionary concepts in dictionary-based entity recognition algorithms, reducing the difficulty of dictionary construction. By using a dimensional model to reduce task difficulty from a data perspective, it improves the conversion efficiency and accuracy of text2sql, thereby increasing user productivity.

[0039] 2. The text2sql optimization method based on dimensional model modeling provided by this invention adopts entity recognition based on entity dictionary. First, in combination with the domain, the capability question set is processed by word segmentation, keywords are sorted out and summarized into entities, and entity tables are listed. Then, the entity tables are transformed into entity dictionary in combination with dimensional model. Each entity contains information such as the first character of the entity word, the entity word, the attribute value of the corresponding dimensional table or the fact of the fact table in the database, the field name of the entity in the dimensional model, and the name of the dimensional table or fact table in which the entity is located in the dimensional model.

[0040] Among these features, the first character of the entity term and the entity term itself facilitate better identification of characters as entities or multiple characters that can be combined to form an entity. Specific entity values ​​in the database correspond to attribute values ​​in dimension tables or facts in fact tables. The entity's field name in the dimensional model, and the name of the dimension table or fact table in the dimensional model, etc., facilitate mapping the entity to the corresponding fact table or attribute table. This improves recognition accuracy and makes the named entity recognition module easier to maintain.

[0041] 3. Based on the conceptual characteristics of dimension tables in dimensional models, the attribute values ​​in the dimension table are directly related to the entities in the question, and these attribute values ​​are typically discrete data. Therefore, this invention uses dimension tables to generate an entity dictionary, or entity information table, for named entity recognition, facilitating entity identification.

[0042] 4. Considering the conceptual characteristics of entity tables in dimensional models, the measures in fact tables are directly related to the intent of the questions. Furthermore, questions typically do not reflect specific measure values ​​but rather the aggregation method of the measures, and a scenario often does not contain many measure types. Therefore, this invention uses a text classification algorithm to determine aggregation functions and aggregated entity fields. Attached Figure Description

[0043] Figure 1 A flowchart illustrating the text2sql optimization method based on dimensional modeling provided by this invention;

[0044] Figure 2 This is a schematic diagram of the entity recognition process in this invention;

[0045] Figure 3 This is a schematic diagram illustrating the process of determining aggregate functions and aggregate entity fields in this invention;

[0046] Figure 4 Here is a flowchart of the text2sql parser workflow;

[0047] Figure 5 This is a diagram showing the results of dictionary-based entity recognition.

[0048] Figure 6 The results of aggregation function identification and aggregation entity identification are shown in the figure;

[0049] Figure 7 A composite image of the information extraction results;

[0050] Figure 8 To transform the SQL key-value pair graph;

[0051] Figure 9 To parse the key-value pairs of SQL key information into an executable SQL graph;

[0052] Figure 10 This is a screenshot of the query results. Detailed Implementation

[0053] The specific embodiments of the present invention will be further described below with reference to the accompanying drawings.

[0054] Please refer to Figure 1-4 , Figure 1 A flowchart illustrating the text2sql optimization method based on dimensional modeling provided by this invention; Figure 2 This is a schematic diagram of the entity recognition process in this invention; Figure 3 This is a schematic diagram illustrating the process of determining aggregate functions and aggregate entity fields in this invention; Figure 4 This is a flowchart of the text2sql parser workflow.

[0055] The text2sql optimization method based on dimensional modeling provided by this invention includes the following steps:

[0056] S1: Analyze the business scenarios of text2sql applications, define the application domain, collect capability question sets, and determine data requirements and business processes;

[0057] S2: Combining domain knowledge, the set of capability questions collected in step S1 is processed by word segmentation, keywords are sorted out and summarized into entities, and an entity table is listed.

[0058] S3: Based on the entity table and the capability question set in step S1, determine the facts, dimensions, and corresponding attributes of the dimensional model numerical model;

[0059] S4: Construct the dimensional model bus matrix based on the dimensional model in step S3;

[0060] S5: Combining the entity table in step S2, construct an entity information table for the dimensional model in step S3, and convert the entity information table into an entity dictionary;

[0061] S6: Construct a dictionary-based named entity recognition model to identify entities, tables involved, association conditions, query conditions, and grouping fields in questions;

[0062] S7: Construct an aggregate function judgment model to identify aggregate functions and aggregate entity fields;

[0063] S8: Based on the identification results of steps S6 and S7, convert them into executable SQL query statements.

[0064] Furthermore, in step S1, the application business scenarios of text2sql are analyzed and the application domains are defined. This can be done by using customer narratives in the form of user stories to determine the requirements and tasks, thereby defining the domains involved.

[0065] Based on user stories, collect a set of capability questions, which is a list of questions that can be answered, thereby clarifying the corresponding data requirements and business processes. Business processes are operational activities completed by the organization, so as many user stories as possible should be collected.

[0066] In step S2, combining domain knowledge, the set of capability questions collected in step S1 is segmented into words, keywords are extracted and summarized into entities, and an entity table is created. This segmentation falls under the category of natural language processing. For a sentence, humans can use their knowledge to understand which parts are words and which are not, but computers cannot accurately identify them. Therefore, string segmentation is necessary so that computers can recognize which strings can form words. Segmentation must incorporate domain knowledge, especially for specialized terms which should be segmented based on professional knowledge.

[0067] In step S3, the facts, dimensions, and corresponding attributes of the dimensional model are determined by combining the capability question set in step S1. Specifically, based on the entity table and the original entity-relationship modeling table, and combined with the business processes in the capability question set, the original entity-relationship model is converted into a dimensional model containing a fact table and a dimension table, following the idea of ​​dimensional modeling.

[0068] Dimensional modeling involves breaking down each business process into fact tables and dimension tables. Fact tables correspond to specific metrics, while dimension tables describe the state or context of the facts. A dimensional model consists of a series of fact tables containing numerical metrics, which are then surrounded by dimension tables with textual context. The fact table, containing a large amount of business metric data, is the core table. A fact represents a specific business metric, such as the line loss rate, failure rate, or failure duration of a distribution unit. The fact tables in the dimensional model store these business metrics, representing the performance metrics of events within the business process. Dimensions are an indispensable component of facts; they are the context of a fact, describing the state of a particular aspect when the fact occurs, such as time dimensions, distribution unit dimensions, regional dimensions, and anomaly dimensions.

[0069] Dimensional models separate objective factual data from subjective descriptive data to simplify business calculations. Essentially, they de-standardize descriptive data to simplify query methods and logic.

[0070] The text-to-sql optimization method based on dimensional modeling provided in this invention does not employ the traditional entity-relationship model, but instead uses a dimensional model. Entity-relationship modeling is application-oriented, follows the third normal form, and is a design technique aimed at eliminating data redundancy. Dimensional modeling, on the other hand, is analysis-oriented and can increase data redundancy and employ denormalization to improve query performance. By adopting a dimensional model, the task difficulty is reduced from a data perspective, thereby improving the efficiency and accuracy of text-to-sql conversion and enhancing user productivity.

[0071] In step S3, dimensional modeling includes the following steps:

[0072] S3.1: Select a business process, which defines specific design goals, granularity, dimensions, and facts;

[0073] S3.2: Declare granularity, which is the meaning of the measure in the fact table of the selected business process;

[0074] S3.3: Identify the dimensions and their corresponding attributes, where dimensions are descriptive information about the elements of events in the fact table, and attributes are descriptions used to filter and classify facts;

[0075] S3.4: Confirming Facts. Facts are measures of business process events. There is a one-to-one relationship between a fact table row and a measure event described at the granularity of the fact table. The measure values ​​in the fact table are generally called facts. Typically, the most useful facts are numeric facts and additive facts.

[0076] Furthermore, in step S4, a dimensional model bus matrix is ​​constructed, including clarifying the types of business processes, defining the dimensions related to each business process, and adding two columns to the business process to distinguish the granularity and measurement of business process events. The dimensions are consistent dimensions, meaning different business processes can share dimensions; each row of the bus matrix represents a business process event. Taking the analysis of distribution network line loss management data as an example, the dimensional model bus matrix for constructing distribution network line loss management data is shown in Table 1.

[0077] Table 1. Dimensional Model Bus Matrix for Data Acquisition of Line Loss Management in Distribution Networks

[0078]

[0079] The explanation is that Y represents the inclusion of this dimension, and N represents its exclusion.

[0080] In step S5, each entity contains the following information: the first character of the entity term, the entity term, the attribute value of the dimension table corresponding to the specific entity value in the database or the fact in the fact table, the field name of the entity in the dimension model, and the name of the dimension table or fact table in the dimension model in which the entity resides. The entity dictionary structure is a dictionary type, with each entity corresponding to a dictionary containing entity information table information. Table 2 shows the structure of the entity information table.

[0081] Table 2 Structure of Entity Information Table

[0082]

[0083] Based on the conceptual characteristics of dimension tables in dimensional models, the attribute values ​​in the dimension table are directly related to the entities in the question, and these attribute values ​​are usually discrete data. Therefore, this invention uses dimension tables to generate an entity dictionary, or entity information table, for named entity recognition, facilitating entity identification.

[0084] The first character of an entity term and the entity term itself facilitate better identification of characters as entities or multiple characters that can be combined to form an entity. Specific entity values ​​in the database correspond to attribute values ​​in dimension tables or facts in fact tables. The entity's field names in the dimensional model, and the name of the dimension table or fact table in which the entity resides, etc., make it easy to map to the corresponding fact table or attribute table. This improves recognition accuracy and makes the named entity recognition module easier to maintain.

[0085] In step S6, the input to the entity recognition process is a question containing t1, t2, ..., tn characters; and a dictionary containing entities of the desired domain, represented as c1, c2, ..., cm.

[0086] The output of the entity recognition process is: to identify all entities in the question, as well as other characters that are not entities, and the tables, association conditions, query conditions, and grouping fields involved.

[0087] Specifically, it includes the following steps:

[0088] S6.1: Segment the question into a character sequence, and use the first character as the first character to match it with the entity dictionary; if no entity is matched in the dictionary, output the character.

[0089] S6.2: If the first character matches an entity in the entity list, combine the characters following it in the character sequence to form an entity word, and then match it with the entity dictionary. If a match is found, continue combining the following characters to match entities until the longest entity in the entity list is matched and then return. If no match is found, output the character.

[0090] S6.3: Starting from the character following the returned entity or character, repeat steps 1 and 2 until all characters in the character sequence have been traversed, and return all entities and other characters that are not entities.

[0091] In step S7, the input to the aggregation function and aggregation field recognition process is: the dictionary-based named entity recognition result of the question [t1, entity class 1, ..., entity class 2, tn], which is a sequence where t1 is a non-entity character and entity class 1 is an entity.

[0092] The output is: identifying aggregate functions and aggregate entity fields in the sentence.

[0093] Specifically, the following steps are included:

[0094] S7.1: The BERT pre-trained model converts entity recognition results into vectors to represent the sentence. The vectors contain word vectors and sentence embedding vectors, which can reflect the contextual semantic information of the sentence.

[0095] S7.2: Deep learning embedding modules convert entity recognition results into vectors to represent the sentence;

[0096] S7.3: Concatenate the vector based on the BERT pre-trained model embedding representation and the vector based on the deep learning embedding representation together to represent the meaning of the sentence;

[0097] S7.4: Based on task requirements, continuously train and adjust the concatenation vector and transformer model to identify and return the aggregation functions and aggregation entity fields in the sentence.

[0098] The aggregation function discriminant model is a text classification model built on BERT embeddings and deep learning embeddings, transforming the problem of aggregation function and aggregation entity recognition into a text classification and prediction problem. It requires labeling the aggregation functions and aggregated entities for each capability question set, dividing them into training, validation, and test sets; constructing a multi-layered deep learning model; training the model using the training set data, then continuously validating the model's performance and optimizing its parameters using the validation set until a certain threshold is reached; then evaluating the model's prediction performance using the test set, adjusting the model structure or adding new data to retrain the model until the test set evaluation metrics meet the application requirements; finally, using the trained model, performing aggregation function and aggregation entity recognition on the input sentences.

[0099] Considering the conceptual characteristics of entity tables in dimensional models, the measures in fact tables are directly related to the intent of the questions. Furthermore, questions typically do not reflect specific measure values ​​but rather the aggregation method of the measures, and a scenario often does not contain many measure types. Therefore, this invention uses a text classification algorithm to determine the aggregation function and aggregated entity fields.

[0100] In step S8, the entities, tables involved, association conditions, query conditions, and grouping fields in the questions identified in step S6, and the aggregate functions and aggregate entities identified in step S7 are parsed into executable SQL query statements.

[0101] The optimization method also includes step S9: maintaining and updating the entity information table, including but not limited to adding new words during online maintenance. Specifically, online operations personnel collect synonym pairs and call relevant programs to add them to the entity information table. A word pair includes both the new word and its corresponding original word. The procedure for adding word pairs to the entity information table is as follows:

[0102] S9.1: Query the entity information table to determine if there exists an entity alias equal to the new word and an entity value equal to the original word that is already in the entity information table. If not, proceed to the next step.

[0103] S9.2: Query the entity information table to determine if there is any data where the entity alias is equal to the original term. If not, return a message indicating that the entity value does not exist. This is because the original term to be added might be an alias for an entity value. Furthermore, the entity information table already contains a record with an entity value matching the entity alias during initialization.

[0104] S9.3: If the entity value exists in one or more records, query and copy those records. Then replace the entity alias in the records with the new word and replace the first character of the entity with the first character of the new word. Specific implementation examples:

[0106] When this invention is applied to a transformer substation to determine the substation with the highest cumulative number of wiring anomalies, the process is as follows:

[0107] 1. Enter the question sent = 'Which transformer area has the most cumulative incorrect wiring anomalies this month?'

[0108] 2. Dictionary-based entity recognition, with recognition results as follows: Figure 5 As shown, the question is identified using a dictionary-based entity recognition method. The entity values ​​[station area, this month, incorrect connection exception] are identified and converted into their corresponding entity names [tg_id, this_month_flag, abn_reason]. The table name is then extracted from the dictionary based on the entity name and entity value.

[0109] 3. Aggregate function recognition and entity recognition based on BERT embedding and entity type embedding, with recognition results as follows: Figure 6 As shown, the model identifies the corresponding aggregate function `sum` and the aggregate field entity name `abn_nums`.

[0110] 4. Merge the information extraction results from steps 2 and 3. The merged result is as follows: Figure 7 As shown.

[0111] 5. SQL statement generation and query

[0112] The information extracted in step 4 is converted into SQL key-value pairs. The generation process is as follows: Figure 8 As shown.

[0113] This includes the output column `output_cols`, the tables involved, the filters, the join operations of multiple tables, the grouping field `group_by_cols`, the sorting field `order_by_cols`, and the sorting order `descend`.

[0114] The SQL key-value pairs are parsed into executable SQL. The parsing process is as follows: Figure 9 As shown.

[0115] Parse into executable SQL statements and execute them, returning query results that meet the filtering conditions. The query results are as follows: Figure 10 As shown.

[0116] The above description is only of the preferred embodiment of the present invention and should not be construed as limiting the scope of the claims. The present invention is not limited to the above embodiments, and variations in its specific structure are permitted. All variations made within the scope of the independent claims of the present invention are also within the scope of protection of the present invention.

[0117] Unless otherwise defined, all technical and scientific terms used herein have the same meaning as commonly understood by one of ordinary skill in the art to which this invention pertains. The terminology used herein in the description of the invention is for the purpose of describing particular embodiments only and is not intended to be limiting of the invention.

Claims

1. A text2sql optimization method based on dimensional modeling, characterized in that, The optimization method includes the following steps: S1: Analyze the business scenarios of text2sql applications, define the application domain, collect capability question sets, and determine data requirements and business processes; S2: Combining domain knowledge, the set of capability questions collected in step S1 is processed by word segmentation, keywords are sorted out and summarized into entities, and an entity table is listed. S3: Based on the entity table and the capability question set in step S1, determine the facts, dimensions, and corresponding attributes of the dimensional model; S4: Construct the dimensional model bus matrix based on the dimensional model in step S3; S5: Combining the entity table in step S2, construct an entity information table for the dimensional model in step S3, and convert the entity information table into an entity dictionary; S6: Construct a dictionary-based named entity recognition model to identify entities, tables involved, association conditions, query conditions, and grouping fields in questions; S7: Construct an aggregate function judgment model to identify aggregate functions and aggregate entity fields; S8: Based on the identification results of steps S6 and S7, convert them into executable SQL query statements; The set of capability questions is a list of questions that can be answered. The model for identifying aggregate functions and aggregate entity fields includes: S7.1: The Bert pre-trained model converts entity recognition results into vectors; S7.2: Deep learning embedding models convert entity recognition results into vectors; S7.3: Concatenate the vectors based on the BERT pre-trained model embedding representation and the vectors based on the deep learning embedding representation; S7.4: Based on task requirements, continuously train and adjust the concatenation vector and transformer model to identify and return the aggregation functions and aggregation entity fields in the sentence.

2. The text2sql optimization method based on dimensional modeling according to claim 1, characterized in that, In step S3, based on the entity table and the original entity-relationship modeling table, and combined with the business processes in the capability problem set, the original entity-relationship model is converted into a dimensional model containing fact tables and dimension tables.

3. The text2sql optimization method based on dimensional modeling according to claim 2, characterized in that, In step S3, dimensional modeling includes the following steps: S3.1: Select the business process, which defines the design goals, granularity, dimensions, and facts; S3.2: Declare granularity, which is the meaning of the measure in the fact table of the selected business process; S3.3: Identify the dimensions and their corresponding attributes, where dimensions are descriptive information about the elements of events in the fact table, and attributes are descriptions used to filter and classify facts; S3.4: Confirm the facts. Facts are the measurements of business process events. There is a one-to-one relationship between a fact table row and the measurement event described according to the granularity of the fact table.

4. The text2sql optimization method based on dimensional modeling according to claim 3, characterized in that, In step S4, a dimensional model bus matrix is ​​constructed, which includes clarifying the types of business processes, clarifying the dimensions related to each business process, and adding two columns to the business process to distinguish the granularity and measurement of business process events.

5. The text2sql optimization method based on dimensional modeling according to claim 1, characterized in that, In step S5, each entity contains the following information: the first character of the entity term, the entity term, the attribute value of the dimension table corresponding to the specific entity value in the database or the fact of the fact table, the field name of the entity in the dimension model, and the name of the dimension table or fact table in which the entity is located in the dimension model.

6. The text2sql optimization method based on dimensional modeling according to claim 1, characterized in that, Step S6 specifically includes the following steps: S6.1: Segment the question into a character sequence, and use the first character as the first character to match it with the entity dictionary; if no entity is matched, output the character. S6.2: If the first character matches an entity in the entity list, combine the characters following it in the character sequence to form an entity word, and then match it with the entity dictionary. If a match is found, continue combining the following characters to match entities until the longest entity in the entity list is matched and then return. If no match is found, output the character. S6.3: Starting from the character following the returned entity or character, repeat steps 1 and 2 until all characters in the character sequence have been traversed, and return all entities and other characters that are not entities.

7. The text2sql optimization method based on dimensional modeling according to claim 1, characterized in that, In step S8, the entities, tables involved, association conditions, query conditions, and grouping fields in the questions identified in step S6, and the aggregate functions and aggregate entities identified in step S7 are parsed into executable SQL query statements.

8. The text2sql optimization method based on dimensional modeling according to claim 1, characterized in that, The optimization method also includes step S9: maintaining and updating the entity information table.

Images