An algorithm for data exploration analysis through natural language

Abstract

The application discloses an algorithm for data exploration analysis through natural language, including nl2sql, the nl2sql includes data layer, APP layer and post-processing module;Data layer is used to support each task module of APP layer and includes word segmentation model, text similarity model, word2vec module, keyword library;APP layer is used for semantic analysis, specifically, according to the information required by report sql statement, module division is carried out;The post-processing module is used for the information obtained by each module of the APP layer is assembled and the result is expanded, and the bottom is covered;The application is suitable for data expansion, and the logic is strong, and the result return is mostly based on specific rules, improves the query efficiency;Support custom rule expansion, keywords and expression methods can be added in the custom rule library.

Description

TECHNICAL FIELD

[0001] The present application relates to the technical field of intelligent data, in particular to an algorithm for data exploration analysis through natural language. BACKGROUND

[0002] In the current high-speed development of the Internet, big data analysis has become a trend, and is used in various industries. Through analysis, a lot of valuable information can be obtained to assist businesses in making business and business decisions. However, in daily work, big data analysis faces two major challenges: first, the threshold for big data analysis is high, and both professional development skills and professional data analysis skills are required; second, the timeliness is low and the cost of individualization is high: professional personnel are needed to analyze and form specific analysis reports.

[0003] Therefore, with the development of artificial intelligence technology, the research and development direction of semantic analysis based on nlp technology and conversion into data query sql and chart form has been valued and developed. The research on big data intelligent analysis based on nlp technology is divided into two categories: one is a specific field or fixed table logic implementation scheme, which requires additional manual rule setting work in the case of adding table logic; the other is a multi-task training framework based on bert pre-training model, which requires a large amount of labeled data for training, and needs to re-label data and train the model in the case of adding table logic. The current research content is biased towards supervised training or manual rule method, which cannot expand new ones and requires a large amount of additional labeled data for training.

[0004] Therefore, the present application designs an algorithm for data exploration analysis through natural language to solve the above problems. SUMMARY

[0005] The present application aims to provide an algorithm for data exploration analysis through natural language to solve the problem of the current research content being biased towards supervised training or manual rule method, which cannot expand new ones and requires a large amount of additional labeled data for training.

[0006] To achieve the above-mentioned purpose, the present application provides the following technical solutions:

[0007] An algorithm for data exploration analysis by natural language, including nl2sql, the nl2sql includes data layer, APP layer and post-processing module; the data layer is used to support the various task modules of APP layer and includes word segmentation model, text similarity model, word2vec module, keyword library; the APP layer is used for semantic analysis, specifically according to the information required by the report sql statement to divide the module, divided into: table name identification, chart type identification, limit identification, index and aggregation function identification, dimension identification, where condition identification; the post-processing module is used to assemble and expand the information obtained by each module of the APP layer, and the bottom.

[0008] Preferably, the keyword library is specifically two custom data structures generated based on the user-defined table structure and the uploaded data extraction, one is the user-defined table structure, and the other is the retrieval dictionary generated according to the user-defined table structure and data; the user-defined table structure and the retrieval dictionary are serialized and deserialized by the pickle library to ensure the consistency of data between multiple processes.

[0009] Preferably, the word segmentation model integrates the jieba word segmentation library, specifically including a professional word library generated by using the user-defined table structure and the retrieval dictionary, and a part-of-speech tagging function based on deep learning is enabled; the word2vec module is specifically a pre-trained word and word vector correspondence table; the text similarity model is specifically a module integrating edit distance, cosin distance based on word2vec, WMD distance based on word2vec, and word similarity algorithm based on synonym forest.

[0010] Preferably, the table name identification is specifically WMD distance and edit distance calculation according to the user query and each table name and table description information, and the key word dictionary is established by using the word frequency filtering method according to the dimension column in each table, the table name, the hit keyword in the user query is matched accurately, and finally the weighted similarity calculation is performed on these results to obtain an inverted table name, so that the table with high similarity is selected for further analysis.

[0011] The chart type identification is specifically based on the commonly used Chinese description of chart type, manually preparing a dictionary, and supporting a regular statement matching mode; the matching mode adopts accurate matching: that is, the user query needs to specify the specific chart type word, if the matching is successful, the user specified chart type is adopted; if not, the chart type is supplemented according to the index and dimension in the post-processing module, that is, the appropriate chart type is used for display.

[0012] Preferably, the limit identification also includes the orderby identification, and the specific steps are as follows:

[0013] SA1: First, perform the conversion of Chinese numerals to Arabic numerals on the input query.

[0014] SA2: Obtain all patterns in the query through the regular dictionary. The regular dictionary stores all regular items for matching keywords, and each regular item is called a pattern. The query may contain one or more patterns, and there may be overlap or complementary relationships between multiple patterns. For the case of multiple patterns, synthesis and screening are required.

[0015] SA3: Parse the patterns into corresponding SQL. LIMIT and ORDER BY are parts of the conditions, and the parsed SQL is a component of the complete SQL statement. Each component may contain the corresponding associated column, value, or operator. By establishing syntactic dependency relationships, find the column on which each pattern depends. The establishment of syntactic dependency relationships is obtained through deep learning of the language model.

[0016] Preferably, the conversion of Chinese numerals to Arabic numerals includes direct narration and value narration. The direct narration conversion is specifically as follows: Establish a dictionary of "{'one': 1, 'two': 2, 'both': 2, 'three': 3, 'four': 4, 'five': 5,'six': 6,'seven':  7, 'eight': 8, 'nine': 9, 'zero': 0}", and perform character-by-character conversion.

[0017] The value narration conversion is specifically as follows: Obtain continuous segments containing numerical expressions, extract the characters such as "ten", "hundred", "thousand", "ten thousand", "hundred million" in the expressions, and perform corresponding operations.

[0018] Preferably, the index recognition specifically includes the following steps:

[0019] SB1: For the case of the index column: According to whether the column alias of the index column is included in the user query, if so, the match is successful, otherwise there is no result.

[0020] SB2: For the case of the dimension column: According to whether the column alias of the dimension class is included in the user query, if the regular condition is met, the match is successful; if the regular condition is not met, the match is unsuccessful. Add regular conditions to avoid conflicts with results such as filter and dimension.

[0021] SB3: If there is no result in the exact match, perform a similarity match.

[0022] SB31: After removing the time, table keywords, and dimension column matching keywords in the user query, re-segment and perform word tagging.

[0023] SB32: Calculate the alias of the index column and the segmented result of the query one by one based on the wmd algorithm of word2vec, and use the token with the largest similarity as the keyword result;

[0024] SB33: Arrange each index column according to the similarity;

[0025] The aggregation function specifically comprises the following steps:

[0026] SC1: According to the commonly used Chinese description of the aggregation function, manually formulate a dictionary, and support the model of regular matching statement.

[0027] SC2: The aggregation function uses accurate matching. If it can be matched, the user-specified one is used; if it cannot be matched, the default bottom-up strategy is used in result assembly;

[0028] SD1: Combine the results of SB33-SC2 to form the result;

[0029] The aggregation function is empty: according to the index type, select the appropriate default aggregation operation function; the index type is a numerical value, and the index column is used by default sum operation function; the index type is a string, and the dimension column is used by default uniqExact de-duplication count operation function;

[0030] The aggregation function is not empty: arrange and combine the index column and the aggregation function to form the result; for the dimension column, filter out the illegal operation functions such as sum.

[0031] Preferably, the dimension recognition is specifically that in the dimension column, the words accurately matched with the column name of the dimension will become the potential objects of group by according to the accurate matching keywords; in the time dimension, the key sentences expressing the time dimension are identified, and then put into the objects of group by after adding appropriate clickhouse functions.

[0032] Preferably, the where condition recognition includes dimension column filtering, index column filtering and time condition filtering; the dimension column filtering extracts the enumerated type data in the table when there is a value in a certain column in the query expression, and puts it into the keyword dictionary for matching; when the query has described the key sentence, the column name is identified, and the identified column name is used to form a specific expression form, and all the statements related to the column name are matched in the form of regular matching.

[0033] The index column filtering refers to uniformly converting all Chinese represented numerical values in the text into Arabic numerals, and then splicing the column and the operator to form a filtering condition in the sql, extracting some numerical value descriptions from the natural language, and converting the descriptions to the sql to form the filtering condition of the corresponding index according to the object of the description; the numerical value description is Chinese or Arabic numerals;

[0034] The time condition filtering specifically refers to obtaining the corresponding numerical value and the corresponding time dimension by using a regular expression or a dictionary setting conversion rule, and converting into an actual time condition.

[0035] Preferably, 10, the result assembly specifically refers to three cases:

[0036] a: except for the index, the rest of the results are filled in if there is a result, otherwise they are empty;

[0037] b: according to how many indexes, generate how many results; according to the reuse of words in the query, if one of the keywords is reused more than 2, the result will be discarded;

[0038] c: in the case that the index column result is empty, only fill in one result with an empty index column;

[0039] The result expansion and bottoming specifically includes the following strategies:

[0040] Time bottoming strategy: in the case that the user does not specify the time, four default time periods of this month, this week, today and this year are used for completion; the completion strategy is to complete the time period of this month first, and then complete the time period of this week, and so on for all results under a single table;

[0041] Dimension and chart type correction strategy: the program imports the list of requirements, matches the dimensions and indexes existing in the results, and finds the most suitable chart type; after selecting the chart type, if there are missing or more dimensions or indexes, complete them according to the specific rules; the rule is obtained according to the cross correlation matrix generated when the view is initialized, and the correlation weight of the dimension and the view table name; specific chart types will also complete specific columns, such as trend line charts, which will complete the type as date column as dimension, and the function will automatically adjust according to the expected number of data points;

[0042] Sorting bottoming strategy: when the column of orderby is the same as the index column, the aggregation function of the index column needs to be filled in the column of orderby.

[0043] Chart type bottoming strategy: when the data is single row and single column, that is, the dimension is empty, change the chart type to the flipper, that is, enlarge the display of a single index.

[0044] Compared with the prior art, the present application has the following advantages:

[0045] The program design of the present application is completely decoupled from data and can add different types of tables to adapt to data expansion; the logic is strong, and most of the result returns are based on specific rules, and in the use process of the user, specific expression methods are adapted, thereby improving the query efficiency; self-defined rule expansion is supported, and keywords and expression methods can be added in the self-defined rule library. BRIEF DESCRIPTION OF DRAWINGS

[0046] In order to more clearly illustrate the technical solutions of the embodiments of the present application, the following will briefly introduce the drawings needed to be used in the embodiment description. Obviously, the drawings in the following description are only some embodiments of the present application, and other drawings can also be obtained by those skilled in the art without creative labor on the basis of these drawings.

[0047] Figure 1 The present application nl2sql technical solution schematic diagram;

[0048] Figure 2 The present application limit and order by identification flow chart;

[0049] Figure 3 The present application Chinese number to Arabic number conversion flow chart;

[0050] Figure 4 The present application index and aggregation function identification flow chart. DETAILED DESCRIPTION

[0051] The technical solutions in the embodiments of the present application will be described clearly and completely in the following with reference to the drawings in the embodiments of the present application. Obviously, the described embodiments are only some of the embodiments of the present application, not all. Based on the embodiments in the present application, all other embodiments obtained by those skilled in the art without creative labor are within the scope of protection of the present application.

[0052] Please refer to Figures 1-4 The present application provides a technical solution:

[0053] An algorithm for data exploration analysis through natural language, including nl2sql, the nl2sql including a data layer, an APP layer and a post-processing module; the data layer is used to support various task modules of the APP layer and includes a word segmentation model, a text similarity model, a word2vec module, and a keyword library; the APP layer is used for semantic analysis, specifically, the information required by the report sql statement is divided into modules, including table name recognition, chart type recognition, limit recognition, index and aggregation function recognition, dimension recognition, and where condition recognition; the post-processing module is used to assemble and expand the results of the information obtained by each module of the APP layer, and to provide a backup.

[0054] Data layer

[0055] The data layer mainly includes a word segmentation model, a text similarity model, a word2vec, and a keyword library. The word library data and algorithm model in this part serve as a bottom support module to support various task modules of the APP layer.

[0056] Keyword library: based on the user-defined table structure and uploaded data extraction, two custom data structures are generated. One is the user-defined table structure, and the other is the retrieval dictionary generated according to the user-defined table structure and data. In order to ensure the consistency of data between multiple processes, the pickle library is used to serialize and deserialize the two dictionary data;

[0057] Word segmentation model: integrated jieba word segmentation library, one is to generate a professional word library based on two user-defined dictionary libraries to ensure the accuracy of professional terms in a specific field; and the other is to enable word part-of-speech tagging based on deep learning to ensure the performance and accuracy of word segmentation and part-of-speech tagging;

[0058] Word2vec module: pre-trained word and word vector correspondence table;

[0059] Text similarity model: integrated algorithms such as edit distance, word2vec-based cosin distance, word2vec-based WMD distance, and synonym-based word similarity.

[0060] APP layer

[0061] The APP module divides the information required by the report sql statement into modules, including table name recognition, chart type recognition, limit recognition, index and aggregation function recognition, dimension recognition, and where condition recognition.

[0062] Where the table name recognition and chart type recognition and other tasks between the tasks are not dependent relationship, but the index recognition, dimension, limit and order by and where conditions and other tasks are dependent on the results of the table name recognition, need to calculate and match according to the specific table structure.

[0063] So the execution order of each task of APP layer is: table name recognition, chart type recognition

[0064] Table name recognition:

[0065] a. According to the user query and each table name and table description information, WMD distance and edit distance calculation is carried out, and the keyword dictionary is established by using word frequency filtering according to the dimension column in each table, table name, and the hit keywords in the user query are matched accurately, and finally the weighted similarity calculation is carried out on these results, to obtain a table name index, so as to select the similar table for further analysis;

[0066] Chart type recognition:

[0067] b. According to the commonly used Chinese description of chart type, artificial dictionary is made, such as comparison, column and trend, and so on. In addition, the mode of regular expression matching is also supported;

[0068] c. The matching mode adopts accurate matching, that is, the user query needs to specify the specific chart type word, such as matching success, then the user specified chart type is adopted; If not, the chart type is supplemented in the post-processing module according to the index and dimension condition, that is, the appropriate chart type is used for display.

[0069] Limit and order by recognition:

[0070] a. limit and order by generally appear together, specify regular expression, such as ranking, sorting, maximum 5, minimum 10, highest 3 and lowest 8, etc. If matching is successful, the corresponding value is obtained as the value of limit attribute; According to different regular terms, the direction of sorting, order or reverse order is obtained; Each time a unit of regular term is matched successfully, a mode is generated, and a query may contain one or more modes, after processing, only one mode is finally retained.

[0071] The input sentence is analyzed by part of speech, when the regular matching is successful, the center word of the regular term is obtained, through the dependency relationship between words, the noun depended by the regular term is obtained, and the column corresponding to the order by is obtained by text similarity matching between the noun and the column description of the current table.

[0072] The limit and the order by identify the specific flow as follows:

[0073] The input query is first converted from Chinese numerals to Arabic numerals. For example, "five kinds" in "the five kinds of goods with the largest sales in Guangdong Province this year" is converted to "5 kinds". If it is "twenty-five kinds" or "three hundred and fifty-six kinds", it is also converted to "25 kinds" and "356 kinds". Here, a method for converting from Chinese numerals to Arabic numerals is generated (detailed below).

[0074] The second step is to obtain all the patterns in the query through the regular dictionary. The regular dictionary stores all the regular terms used to match the keywords. Each regular term is called a pattern. The regular dictionary is editable, and new patterns can be added according to predefined rules. For example, the regular term "top\\d{1,2}" can match keywords such as "top3" and "top5" from the query. topN is a pattern, and its corresponding sql parsing expression is "ORDER BY desc limit#". "#" is a placeholder. For example, when "top5" is matched, "5" is filled into "limit 5". Therefore, in each pattern, there can be one or more placeholders. When a regular term is matched, the value in the matched term is filled into the placeholder according to the corresponding pattern. The regular dictionary stores the correspondence between the regular term and the pattern, as well as the corresponding parsing rules.

[0075] The query can contain one or more patterns, and multiple patterns can have overlapping or complementary relationships. For multiple patterns, synthesis and screening may be required. Currently, the limit and the order by contain only one pattern. The case of multiple patterns is described in the next chapter "Time condition filtering".

[0076] The next step is to parse the pattern into the corresponding sql. Since limit and order by are part of the condition, the sql parsed here is also a component of the complete sql statement. Each component can contain corresponding dependent columns, values, or operators. This part contains dependent columns and values. For example, "the five kinds of goods with the largest sales this year", the matched pattern is "the largest N kinds", and the parsing expression is "ORDER BY desc limit#", which contains the value 5 and the dependent column "sales".

[0077] In this query, "the largest sales" and "five" modify "goods", and "this year" and "the largest" jointly modify "sales", that is, "the largest" depends on the "sales" column. By establishing a syntactic dependency relationship, find the column each pattern depends on, the database has a column "sales_val" that matches "sales", so the sql part parsed by this unit is "ORDER BY sales_val desc limit 5".

[0078] The establishment of syntactic dependency is obtained by deep learning of language model. Obtain a set of language data, perform word segmentation on each sentence, and label the relationship between words as "no relationship", "A modifies B", and "B modifies A". A 3-class text data set can be obtained, and the model is trained to realize the reasoning of syntactic dependency.

[0079] Supplement: a method for converting Chinese numerals to Arabic numerals

[0080] Convert Chinese numerals such as "five", "twenty-five", and "three hundred and sixty-five" into Arabic numerals. Chinese numerals can be divided into two types: direct statements and value statements.

[0081] For example, 2003: direct statement is two thousand and three; value statement is two thousand and three.

[0082] For direct statements, the conversion method is relatively simple, and the following dictionary is established: {'one':1, 'two':2, 'two':2, 'three':3, 'four':4, 'five':5,'six':6,'seven':7, 'eight':8, 'nine':9, 'zero':0}, and the words are converted one by one.

[0083] For value statements, the words "ten, hundred, thousand, ten thousand, and hundred million" in the expression need to be extracted and operated accordingly.

[0084] If the value statement form of the number does not contain the words "ten, hundred, thousand, ten thousand, and hundred million", it is equivalent to a direct statement.

[0085] The specific process of this method is as follows: first, obtain a continuous segment containing a number expression, such as "three thousand five hundred and sixty-eight", and finally, both types of numbers are converted to Arabic numerals, such as 3568.

[0086] Indicator and aggregation function recognition:

[0087] a. Aggregation function

[0088] i. According to the commonly used Chinese description of aggregation functions, manually develop a dictionary, such as avg for average, average price, and other words, and sum for sum, total, and other words;

[0089] ii. In addition, the model supports regular expression matching, such as counting the number of dimensions in the column, and filtering with "most [or least] [0-9]" regular expressions to avoid conflicts with the limit module;

[0090] iii. The aggregation function uses exact matching, such as matching the user-specified function, and if there is no match, the default fallback strategy is used during result assembly;

[0091] b. Indicator identification

[0092] i. Exact matching

[0093] 1. For the case of indicator columns: according to whether the column alias of the indicator column is included in the user query, if it is, the matching is successful; otherwise, there is no result;

[0094] 2. For the case of dimension columns: according to whether the column alias of the dimension column is included in the user query, if it is and meets the regular conditions such as "xx quantity", the matching is successful; if the conditions are not met, the matching is unsuccessful. Adding regular conditions can avoid conflicts with filter and dimension results;

[0095] ii. Similarity matching, if exact matching has no result, similarity matching is performed

[0096] 1. Remove time, table keywords, and dimension column matching keywords from the user query, and re-tokenize and tag the parts of speech;

[0097] 2. Then use the wmd algorithm based on word2vec to calculate the alias of the indicator column and the tokenized results of the query one by one, and use the token with the highest similarity as the keyword result;

[0098] 3. Finally, arrange the indicator columns according to the similarity

[0099] c. Aggregation function and indicator assembly: combine the results of the previous two steps to form the result

[0100] i. The aggregation function is empty: according to the indicator type, select the appropriate default aggregation operation function;

[0101] 1. If the indicator type is numerical and the indicator column, use the sum function by default;

[0102] 2. If the indicator type is a string and the dimension column, use the uniqExact function to remove duplicates and count by default

[0103] ii. The aggregation function is not empty: arrange and combine the indicator column and the aggregation function to form the result;

[0104] Note: For the case of dimension columns, filter out the illegal operation functions such as sum.

[0105] Dimension recognition:

[0106] a. Dimension column: According to the accurate matching of keywords, the words accurately matched with the column name of the dimension will become the potential object of groupby;

[0107] b. Time dimension: Key sentences expressing time dimension such as every week, every month and every year will be identified accordingly, and then put into the object of group by after adding appropriate clickhouse functions;

[0108] Where condition recognition: where conditions include dimension column filtering, indicator column filtering and time condition filtering

[0109] Dimension column filtering: There are basically two forms of dimension column condition filtering:

[0110] a. The first is that there is a value in a column in the query expression; for example, the table wants to filter out the sales of food, and the sql statement is 'where category in ['food']'; in this case, the enumerated type data in the table will be extracted and put into the keyword dictionary for matching;

[0111] b. Another is that the query describes a key sentence, for example,'sales of the delivery address starting with Guangdong'; in this sentence, 'delivery address' is the column name, which forms a specific expression; in this program, regular matching is used to match all these column-related sentences, including '(column name) contains..', '(column name) ends with..' and other expressions;

[0112] Indicator column filtering:

[0113] a. Indicator column filtering refers to extracting some numerical descriptions from natural language, and converting them into sql filtering conditions according to the objects described.

[0114] For example, "sales are greater than three thousand", three thousand is a numerical value, and the object described by the numerical value is sales, and the operator is >; search for the column that matches "sales" in the database, such as sales_val, and then form the filtering condition where sales_val>3000;

[0115] b. Numerical description may be Chinese representation (three thousand), may be Arabic numerals (3000), before doing index column filter, need to first convert all Chinese representation of numerical value in the text to Arabic numerals, and then splice into a filter condition in SQL with column and operator.

[0116] The method of index column filtering is basically the same as the method flow identified by limit and orderby.

[0117] Time condition filtering:

[0118] a. Time condition filtering is similar to index column filtering, most cases are to extract numerical description, but more complex than index extraction. The simplest case is "a certain year, month and day", which contains three numerical values, corresponding to year, month and day. There is also "the first quarter" corresponding to the month interval, such as the first quarter corresponding to January to March. Regular expressions are used to obtain the corresponding numerical value and time dimension, and converted into actual time conditions such as where sales_date = "2021-09-03" or where sales_date > 2021-01-01 and sales_date < 2021-03-31.

[0119] b. It also includes many other non-numerical expressions, such as "this month", "last month", "last year", "this week", "late this month", etc. This kind of expression needs to be replaced into the time representation of year, month and day in the form of dictionary, and then further calculated. For example, "last month", because it needs to know that it is September 2021 now, to get August 2021 as last month, so this processing is two steps. First, convert "last month" to %m1 month, %m1 means to subtract 1 month from the current month, then get the current month, subtract 1 month and output as time again, that is, do the second conversion of %m1 month to get August 2021. Therefore, by analogy, it can be deduced that "this month" needs to be converted to "%m0", and the second conversion is September 2021. If it is "the past 6 months", then it is converted to "%m6 to %m0", and the second conversion is "March 2021 to September 2021".

[0120] c. There are symbols such as %m0 in the dictionary, such as %Y1 for last year, %d2 for the day before yesterday, %w1 for last week, %q0 for this quarter, etc. There are also some fixed dates that do not need to be converted twice. For example, "New Year's Day: January 1", "National Day: October 1", "Double Eleven: November 11", etc. This kind of expression can be directly converted once.

[0121] d. All the natural language descriptions of time above will be converted to the corresponding date, or Y-m-d to Y-m-d, according to the conversion rules set by the dictionary, through 1-time conversion or 2-time conversion, that is

[0122] where sales_date = "Y-m-d" or where sales_date > 'Y-m-d' and sales_date < 'Y-m-d'

[0123] e. The time dimension contains two modes, one is "Y year m month d day", and the other is "Y year m month d day to Y year m month d day", where the latter covers the former and produces overlap. Therefore, the matching mode is also extracted in this method span, that is, the starting position of the matching item in the query, such as span = [3, 8] represents that the matching item appears in the 3rd to 8th character of the query. For all matching items in the query, if the span of the two overlaps, the longer matching item is retained.

[0124] Post-processing module

[0125] a. Result assembly

[0126] i. First, except for the index, the rest of the results are filled in if there is a result, otherwise they are empty;

[0127] ii. Finally, according to how many indexes, generate how many results; but according to the reuse of words in the query, if one of the keywords is reused more than 2, the result will be discarded;

[0128] iii. If the index column result is empty, only fill in one result with an empty index column;

[0129] b. Result expansion and bottom-up

[0130] i. Time bottom-up strategy

[0131] In the case of no explicit time for the user, four default time periods, this month, this week, today and this year, are used to complete. The completion strategy is: first complete the time period of this month for all results under a single table, then complete the time period of this week, and so on.

[0132] Note:

[0133] 1. Completion is not to discard the original result, but to copy a result for completion and retain both results;

[0134] 2. The results of the completed time will be weighted, and will be placed in front of the results without time, and will be arranged in the order of this month, this week, today and this year;

[0135] ii. Dimension & Chart Type Correction Strategy

[0136] a. In most cases, the user will not define what type of chart is needed, and various icons have certain requirements for dimensions and indicators. The program will import the required list, match the dimensions and indicators present in the results, and find the most suitable chart type.

[0137] b. After the icon type is selected, if there are missing or more dimensions or indicators than the chart type, they will be completed according to specific rules. The rules are based on the cross-correlation matrix generated when the view is initialized, and the correlation weight of the dimension and the view table name to achieve the purpose of completing more relevant columns. Specific chart types will also complete specific columns, such as trend charts, which will complete columns of type date as dimensions, and their functions (toYear, toMonth, etc.) will be automatically adjusted according to the expected number of data points.

[0138] iii. Sorting Backup Strategy

[0139] When the orderby column is the same as the indicator column, the aggregate function of the indicator column needs to be filled in the orderby column.

[0140] iv. Chart Type Backup Strategy

[0141] When the data is a single row and column, i.e. the dimension is empty, change the chart type to a flipper, i.e. enlarge the display of a single indicator.

[0142] The program design of the present application is completely decoupled from the data, and different types of tables can be added to adapt to data expansion. It has strong logic, and the results returned are mostly based on specific rules, which can adapt to specific expression methods during user use, thereby improving query efficiency. It supports custom rule expansion, and keywords and expression methods can be added in the custom rule library.

[0143] In the description of the present specification, the description of the terms "one embodiment", "example", "specific example" and the like means that the specific features, structures, materials or characteristics described in conjunction with the embodiment or example are included in at least one embodiment or example of the present application. In the present specification, the illustrative description of the above terms does not necessarily refer to the same embodiment or example. Moreover, the described specific features, structures, materials or characteristics can be combined in any one or more embodiments or examples in a suitable manner.

[0144] The preferred embodiments of the application disclosed above are only to facilitate the elucidation of the application. The preferred embodiments do not describe all the details of the application and limit the application to the specific embodiments described. Obviously, many modifications and variations can be made in light of the teachings above. The description is chosen and described in order to best explain the principles of the application and its practical application to thereby enable others skilled in the art to best utilize the application and get the best results from the application. The application is only limited by the claims and their full scope and equivalents.

Claims

1. A method for data exploration analysis through natural language, characterized in that Include nl2sql, the nl2sql includes data layer, APP layer and post-processing module; The data layer is used to support the various task modules of APP layer and includes word segmentation model, text similarity model, word2vec module, keyword library; The APP layer is used for semantic analysis, specifically according to the information required by the report sql statement to divide the module, divided into: table name recognition, chart type recognition, limit recognition, index and aggregation function recognition, dimension recognition, where condition recognition; The post-processing module is used to assemble and expand the information obtained by each module of the APP layer, and the bottom; The keyword library is specifically two custom data structures generated based on user-defined table structure and uploaded data extraction, one is user-defined table structure, and the other is retrieval dictionary generated according to user-defined table structure and data; The user-defined table structure and retrieval dictionary are serialized and deserialized by pickle library to ensure the consistency of data between multiple processes; The result assembly is specifically three cases: a: except for the index, the rest of the results are filled in, and the empty one is empty; b: according to how many indexes, combine to generate how many results; According to the reuse of words in each result query, when one of the keywords is reused more than 2, the result will be discarded; c: in the case of empty index column result, only fill in the result of empty index column; The result expansion and bottoming out specifically includes the following strategies: Time bottoming out strategy: for the case that the user does not specify the time, four default time periods of this month, this week, today and this year are used to complete; The completion strategy is to complete the time period of this month first, and then complete the time period of this week, and so on for all the results under a single table; Dimension and chart type correction strategy: the program imports the required list, matches the dimensions and indexes existing in the results, and finds the most suitable chart type; After the chart type is selected, if there are missing or redundant dimensions or indexes in the chart type, they will be completed according to the specific rules; The rule is obtained according to the cross correlation matrix generated when the view is initialized and the correlation between the dimension and the view table name; The specific chart type will also complete the specific column, and the trend line chart will complete the column of type date as the dimension, and its function will be automatically adjusted according to the expected number of data points; Order bottoming out strategy: when the column of orderby is the same as the index column, the aggregation function of the index column needs to be filled in the column of orderby; Chart type bottoming out strategy: when the data is single row and single column, that is, the dimension is empty, change the chart type to flipper, that is, enlarge the display of single index.

2. The method for data exploration analysis through natural language according to claim 1, characterized in that : The word segmentation model integrates the jieba word segmentation library, specifically including a professional word library generated using a user-defined table structure and retrieval dictionary, and enabling the词性 annotation function based on deep learning; the word2vec module is specifically a pre-trained对照表 of words and word vectors; the text similarity model is specifically a module integrating the edit distance, cosine distance based on word2vec, WMD distance based on word2vec, and the word similarity algorithm based on thesaurus.

3. The method for data exploration analysis through natural language of claim 1, wherein : The table name recognition specifically calculates the WMD distance and edit distance based on the user query and each table name and table description information, establishes a keyword dictionary using the word frequency filtering method based on the dimensions and table names within each table, precisely matches the hit keywords in the user query, and finally calculates the weighted similarity of these results to obtain an inverted list of table names, thereby selecting tables with high similarity for further analysis; The chart type recognition specifically formulates a dictionary manually according to the common Chinese descriptions of chart types and supports the pattern of regularized statement matching; the matching pattern uses exact matching: that is, the user query needs to clearly specify the words of the specific chart type. If the match is successful, the chart type specified by the user is used; if not, the chart type is supplemented according to the indicator and dimension conditions in the post-processing module, that is, the appropriate chart type is used for display.

4. The method for data exploration analysis through natural language of claim 1, wherein : The limit recognition also includes orderby recognition, and the specific steps are as follows: SA1: First, convert Chinese numerals in the input query to Arabic numerals; SA2: Obtain all patterns in the query through a regular dictionary. The regular dictionary stores all regular items used to match keywords, and each regular item is called a pattern; the query may contain one or more patterns, and there may be overlap or complementary relationships between multiple patterns. For the case of multiple patterns, synthesis and screening are required; SA3: Parse the corresponding sql according to the pattern. limit and orderby are part of the conditions, and the parsed sql is a component of the complete sql statement; each component contains the corresponding associated column, value, or operator. By establishing a syntactic dependency relationship, find the column on which each pattern depends; the establishment of the syntactic dependency relationship is obtained through deep learning of the language model.

5. The method for data exploration analysis through natural language of claim 4, wherein : In SA1, the conversion of Chinese numerals to Arabic numerals includes direct narration and value narration; the direct narration conversion is specifically: establish a dictionary "{‘one’:1,‘two’:2,‘two’:2,‘three’:3,‘four’:4,‘five’:5,‘six’:6,‘seven’:7,‘eight’:8,‘nine’:9,‘zero’:0}", and convert word by word; The value narration conversion is specifically: obtain the continuous segment containing the digital expression, extract the words "ten, hundred, thousand, ten thousand, hundred million" in the expression and perform the corresponding operations.

6. The method for data exploration analysis through natural language of claim 1, wherein : The indicator recognition in the indicator and aggregation function recognition specifically includes the following steps: SB1: For the case of index column: According to whether the column alias of index column is contained in the user query, if yes, the matching is successful, otherwise there is no result; SB2: For the case of dimension column: According to whether the column alias of dimension column is contained in the user query, if the regular condition is met, the matching is successful; if the regular condition is not met, the matching is unsuccessful; the regular condition is added to avoid the conflict with filter and dimension result; SB3: If there is no result of exact matching, similarity matching is performed; SB31: After removing the time, table keyword and dimension column matching keyword in the user query, re-segmentation and part-of-speech tagging are performed; SB32: The wmd method based on word2vec is used to calculate the alias of index column and the segmented result of query one by one, and the token with the largest similarity is used as the keyword result; SB33: The index columns are arranged according to the similarity; The index and aggregation function recognition specifically includes the following steps: SC1: According to the commonly used Chinese description of aggregation function, a dictionary is manually prepared, and a model supporting regular matching is also prepared; SC2: The aggregation function uses exact matching, if it can be matched, the user specified one is used; if it cannot be matched, the default bottom-up strategy is used in result assembly; SD1: The results of SB33-SC2 are combined to form the result; In the case of empty aggregation function: according to the index type, the default aggregation operation function is selected; if the index type is numerical, index column, the sum operation function is used by default; if the index type is string, dimension column, the uniqExact de-duplication operation function is used by default; In the case of non-empty aggregation function: the index column and the aggregation function are arranged and combined to form the result; for the case of dimension column, the operation function using sum is filtered out.

7. The method for data exploration analysis through natural language of claim 1, wherein The dimension recognition specifically includes that in the dimension column, the words accurately matched with the column name of the dimension will become the potential objects of group by; in the time dimension, the key sentences expressing the time dimension are recognized, and then put into the objects of group by after adding the clickhouse function.

8. The method for data exploration analysis through natural language of claim 1, wherein The where condition recognition includes dimension column filtering, index column filtering and time condition filtering; the dimension column filtering extracts the enumeration type data in the table when a value in a certain column exists in the query expression, and puts it into the keyword dictionary for matching; when the query describes the key sentences, the column name is recognized, and a specific expression is formed by using the recognized column name, and all the sentences related to the column name are matched by using the regular matching form; The index column filtering is to uniformly convert all numerical values expressed in Chinese in the text into Arabic numerals, and then jointly splice the column and the operator into a filtering condition in sql; some numerical value descriptions are extracted from the natural language, and the filtering condition of the corresponding index is formed in sql according to the description object; the numerical value description is Chinese or Arabic numeral. The time condition filtering specifically comprises: acquiring corresponding numerical values and corresponding time dimensions by using a conversion rule set by a regular expression or a dictionary, and converting into actual time conditions.

Images