CN112347121B - Configurable natural language sql conversion method and system - Google Patents
Configurable natural language sql conversion method and system Download PDFInfo
- Publication number
- CN112347121B CN112347121B CN202011204186.4A CN202011204186A CN112347121B CN 112347121 B CN112347121 B CN 112347121B CN 202011204186 A CN202011204186 A CN 202011204186A CN 112347121 B CN112347121 B CN 112347121B
- Authority
- CN
- China
- Prior art keywords
- sql
- text
- semantic
- aggregative
- condition
- Prior art date
- Legal status (The legal status is an assumption and is not a legal conclusion. Google has not performed a legal analysis and makes no representation as to the accuracy of the status listed.)
- Active
Links
- 238000000034 method Methods 0.000 title claims abstract description 30
- 238000006243 chemical reaction Methods 0.000 title description 7
- 230000002776 aggregation Effects 0.000 claims abstract description 54
- 238000004220 aggregation Methods 0.000 claims abstract description 54
- 230000011218 segmentation Effects 0.000 claims description 43
- 238000012545 processing Methods 0.000 claims description 31
- 238000007781 pre-processing Methods 0.000 claims description 27
- 230000014509 gene expression Effects 0.000 claims description 20
- 238000000605 extraction Methods 0.000 claims description 12
- 238000013507 mapping Methods 0.000 claims description 12
- 230000008569 process Effects 0.000 claims description 9
- 238000003745 diagnosis Methods 0.000 claims description 6
- 238000012163 sequencing technique Methods 0.000 claims description 6
- 108010015046 cell aggregation factors Proteins 0.000 claims description 3
- 230000000153 supplemental effect Effects 0.000 claims description 3
- 230000036962 time dependent Effects 0.000 claims description 2
- 101100166829 Mus musculus Cenpk gene Proteins 0.000 claims 1
- 239000000284 extract Substances 0.000 claims 1
- 230000003993 interaction Effects 0.000 abstract description 3
- 230000006870 function Effects 0.000 description 5
- 230000007717 exclusion Effects 0.000 description 2
- 238000006116 polymerization reaction Methods 0.000 description 2
- 238000012549 training Methods 0.000 description 2
- 230000009286 beneficial effect Effects 0.000 description 1
- 238000013145 classification model Methods 0.000 description 1
- 238000010801 machine learning Methods 0.000 description 1
- 238000012423 maintenance Methods 0.000 description 1
- 230000004044 response Effects 0.000 description 1
- 238000006467 substitution reaction Methods 0.000 description 1
Classifications
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F16/00—Information retrieval; Database structures therefor; File system structures therefor
- G06F16/20—Information retrieval; Database structures therefor; File system structures therefor of structured data, e.g. relational data
- G06F16/24—Querying
- G06F16/242—Query formulation
- G06F16/2433—Query languages
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F40/00—Handling natural language data
- G06F40/30—Semantic analysis
Landscapes
- Engineering & Computer Science (AREA)
- Theoretical Computer Science (AREA)
- Physics & Mathematics (AREA)
- General Engineering & Computer Science (AREA)
- Computational Linguistics (AREA)
- General Physics & Mathematics (AREA)
- Data Mining & Analysis (AREA)
- Databases & Information Systems (AREA)
- Mathematical Physics (AREA)
- Health & Medical Sciences (AREA)
- Artificial Intelligence (AREA)
- Audiology, Speech & Language Pathology (AREA)
- General Health & Medical Sciences (AREA)
- Information Retrieval, Db Structures And Fs Structures Therefor (AREA)
Abstract
The invention discloses a method and a system for converting configurable natural language into sql, wherein the method specifically comprises the following steps: firstly, constructing an sql structure to preprocess a problem text, removing unimportant words, and judging an aggregation condition through semantic component matching, wherein the semantic component matching judgment determines the needed aggregation condition elements in the sql template; secondly, extracting a text time period by using a time element, acquiring conditions except the time element through the text, and realizing data query analysis requirements in a natural language interaction mode, so that comprehensive support is provided for complex business logic, and when new data is accessed into a questioning method, the system can be quickly adjusted; and extracting conditions except the time element through the condition elements, recording semantic component information to be matched of each data source and each data table, and helping users to realize data query analysis requirements in a natural language interaction mode.
Description
Technical Field
The invention relates to a language processing technology, in particular to a configurable method and a system for converting natural language into sql.
Background
The current machine natural language to sql method can only support a simple structure of select F (A) from B where C D E, where A is the table result field that one wishes to query; f is a function operation performed on the table field, such as sum, count, etc.; b is the table name of the query; c is the field name in the condition; e is the value corresponding to the field in the condition; d is the relationship of the field to the value in the condition, e.g., greater than, less than.
The element extraction algorithms in this structure generally fall into two broad categories: firstly, determining the value of A-F through matching the keywords and the regular expressions; secondly, training samples through a machine learning algorithm, wherein the values of the elements A to F are located in a limited optional range, so that the algorithm is actually a supervised classification learning, and the elements A to F respectively form an independent classification model.
The disadvantages of this mode are mainly as follows: 1. the sql structure is fixed, and advanced sql such as aggregation, table association and the like cannot be generated; 2. the applicable database is single with the service scene, and if a table is newly added in the database or a field is newly added in the table, the training needs to be carried out again.
Disclosure of Invention
The invention aims to: a method for converting configurable natural language into sql is provided to solve the above problems.
The technical scheme is as follows: a method for converting configurable natural language into sql, comprising the steps of:
step1, constructing an sql structure to preprocess a problem text;
step 2, judging the aggregation condition through semantic component matching;
step 3, extracting a text time period by using the time element;
and 4, extracting conditions except the time element through the condition element.
According to one aspect of the present invention, the sql structure determination in the step 1 is that firstly, a question text is preprocessed, unimportant words are removed, and some expressions are converted into vocabulary, and then, a template of the sql is determined through matching of semantic components;
The process for preprocessing the question text comprises the following steps:
Step 11, segmenting and sentence dividing is carried out on an input text, the text is decomposed into short sentences, and the first step of a semantic-based preprocessing flow is the same as the traditional preprocessing flow;
Step 12, word segmentation processing is carried out on the segmented and sentence-separated text, word segmentation results are analyzed, whether segmentation ambiguity exists or not is judged, and if yes, the semantic knowledge base is utilized to carry out disambiguation processing on the segmentation ambiguity of the preliminary word segmentation results;
Step 13, part-of-speech tagging and word processing are carried out on the word segmentation result, part-of-speech ambiguity of the double-class word is eliminated by utilizing semantic information and related rules of a semantic knowledge base, and the accuracy of part-of-speech tagging is improved;
Step 14, performing stop word processing and recognition of unregistered words on the word segmentation result;
Step 15, mapping a concept space by utilizing the obtained result, merging words with the same concept into the same word, selecting a consistent feature selection operation method, and extracting feature words from the text of the feature to be extracted;
and 16, forming a semantic vector of the text.
According to one aspect of the present invention, the aggregation condition discrimination in the step 2 determines the required aggregation condition elements in the sql template through semantic component matching discrimination, and the background determines the matching flow by using five tables db_ AGGREGATIVE, DB _ aggregative _value, db_ aggregative _direct, db_ aggregative _graph and db_ aggregative _limit; the db_ aggregative is an aggregation condition main table, when the matching of the problem text and the semantic components in the table is successful, the system acquires specific information in four sub-tables through the id of the rule; the db_ aggregative _value records the corresponding aggregation column name, aggregation operation and sequencing field of each aggregation type in different databases and data tables; the db_ aggregative _subject stores a mapping dictionary of the aggregated columns, and is used for translating and converting the aggregated column fields to generate language answers meeting the requirements; the db_ aggregative _graph table judges whether to present a final answer in the form of a graph according to the aggregation type; the db_ aggregative _limit table determines how many records before the returned result is reserved according to the aggregation condition and the supplementary semantic components;
the sql is obtained by natural language conversion, and the conversion process is as follows:
step 21, preprocessing a text;
step 22, determining an SQL module;
Step 23, determining an SQL trunk element value;
Step 24, judging polymerization conditions;
Step 25, determining an aggregation factor value;
Step 26, determining a time element;
Step 27, determining other time elements;
step 28, generating sql.
According to one aspect of the present invention, the time element extraction in the step 3 obtains a time-related description from the text, determines the beginning and the end of the time period, maps to a time format in a standard form, and performs semantic component matching through a db_condition_time table.
According to one aspect of the invention, the condition element extraction in the step 4 acquires other conditions except for the time element behind the where key is obtained according to the text and the data table, and depends on three tables of db_condition, db_condition_subject and db_condition_sql; and the db_condition is a main table for extracting the condition elements, and records semantic component information to be matched of each data source and each data table.
According to one aspect of the present invention, a system for converting configurable natural language into sql comprises the following modules:
The processing module is used for constructing an sql structure to preprocess the problem text;
a diagnosis module for judging the aggregation condition through semantic component matching;
a time module for extracting a text time period using the time element;
and the condition extraction module is used for acquiring conditions except the time element through texts.
According to one aspect of the invention, the processing module firstly pre-processes the question text, removes unimportant words, converts some expressions into vocabulary, and then determines the template of sql through the matching of semantic components;
The process for preprocessing the question text comprises the following steps:
Step 11, segmenting and sentence dividing is carried out on an input text, the text is decomposed into short sentences, and the first step of a semantic-based preprocessing flow is the same as the traditional preprocessing flow;
Step 12, word segmentation processing is carried out on the segmented and sentence-separated text, word segmentation results are analyzed, whether segmentation ambiguity exists or not is judged, and if yes, the semantic knowledge base is utilized to carry out disambiguation processing on the segmentation ambiguity of the preliminary word segmentation results;
Step 13, part-of-speech tagging and word processing are carried out on the word segmentation result, part-of-speech ambiguity of the double-class word is eliminated by utilizing semantic information and related rules of a semantic knowledge base, and the accuracy of part-of-speech tagging is improved;
Step 14, performing stop word processing and recognition of unregistered words on the word segmentation result;
Step 15, mapping a concept space by utilizing the obtained result, merging words with the same concept into the same word, selecting a consistent feature selection operation method, and extracting feature words from the text of the feature to be extracted;
and 16, forming a semantic vector representation model of the text.
According to one aspect of the invention, the diagnosis module determines the required aggregation condition elements in the sql template through semantic component matching judgment, and the background determines a matching flow by using five tables of db_ AGGREGATIVE, DB _ aggregative _value, db_ aggregative _direct, db_ aggregative _graph and db_ aggregative _limit; the db_ aggregative is an aggregation condition main table, when the matching of the problem text and the semantic components in the table is successful, the system acquires specific information in four sub-tables through the id of the rule; the db_ aggregative _value records the corresponding aggregation column name, aggregation operation and sequencing field of each aggregation type in different databases and data tables; the db_ aggregative _subject stores a mapping dictionary of the aggregated columns, and is used for translating and converting the aggregated column fields to generate language answers meeting the requirements; the db_ aggregative _graph table judges whether to present a final answer in the form of a graph according to the aggregation type; the db_ aggregative _limit table determines how many records before retaining the returned results based on the aggregation conditions and the supplemental semantic components.
According to one aspect of the invention, the time module obtains a time-dependent description from the text, determines the beginning and end of a time period, maps to a time format in a standard form, and performs semantic component matching through a db_condition_time table.
According to one aspect of the invention, the condition extraction module acquires other conditions except for time elements behind the where key according to the text and the data table, and depends on three tables of db_condition, db_condition_subject and db_condition_sql; and the db_condition is a main table for extracting the condition elements, and records semantic component information to be matched of each data source and each data table.
The beneficial effects are that: the invention designs a method and a system for converting configurable natural language into SQL, wherein the determination of each link of 1 and SQL is completed through the matching of semantic components, the matching scheme of the semantic components is completely based on a data table for recording and storing, the configuration can be freely realized, and when a new data source or data table is added, the adjustment can be quickly carried out;
2. All SQL templates are also stored in the data table, and elements to be filled are marked in the form of placeholders, so that the SQL templates can be freely defined, and the applicable service range is improved;
3. the semantic component matching scheme is based on vocabulary, regularization and part of speech, does not depend on a complex algorithm model, is quick in response and is simple to maintain;
The data query analysis requirement is realized through a natural language interaction mode, the powerful and flexible configuration capability provides timely and comprehensive support for complex business logic, when new data access or a new questioning method exists, the system can be quickly adjusted, and meanwhile, the semantic information in a question text can be accurately and comprehensively acquired by the system based on the sql template filling mode of semantic component matching, so that high-quality answers are generated.
Drawings
FIG. 1 is a natural language to sql flowchart of the present invention.
Detailed Description
In this embodiment, a method for converting configurable natural language into sql is characterized by comprising the steps of:
step1, constructing an sql structure to preprocess a problem text;
step 2, judging the aggregation condition through semantic component matching;
step 3, extracting a text time period by using the time element;
and 4, extracting conditions except the time element through the condition element.
In a further embodiment, the sql structure in the step 1 is determined by firstly preprocessing the question text, removing unimportant words, performing vocabulary conversion on some expressions, and then determining a template of the sql through matching of semantic components;
The process for preprocessing the question text comprises the following steps:
Step 11, segmenting and sentence dividing is carried out on an input text, the text is decomposed into short sentences, and the first step of a semantic-based preprocessing flow is the same as the traditional preprocessing flow;
Step 12, word segmentation processing is carried out on the segmented and sentence-separated text, word segmentation results are analyzed, whether segmentation ambiguity exists or not is judged, and if yes, the semantic knowledge base is utilized to carry out disambiguation processing on the segmentation ambiguity of the preliminary word segmentation results;
Step 13, part-of-speech tagging and word processing are carried out on the word segmentation result, part-of-speech ambiguity of the double-class word is eliminated by utilizing semantic information and related rules of a semantic knowledge base, and the accuracy of part-of-speech tagging is improved;
Step 14, performing stop word processing and recognition of unregistered words on the word segmentation result;
Step 15, mapping a concept space by utilizing the obtained result, merging words with the same concept into the same word, selecting a consistent feature selection operation method, and extracting feature words from the text of the feature to be extracted;
and 16, forming a semantic vector of the text.
In a further embodiment, the aggregation condition discrimination in the step 2 determines the required aggregation condition elements in the sql template through semantic component matching discrimination, and the background determines the matching flow by using five tables of db_ AGGREGATIVE, DB _ aggregative _value, db_ aggregative _direct, db_ aggregative _graph and db_ aggregative _limit; the db_ aggregative is an aggregation condition main table, when the matching of the problem text and the semantic components in the table is successful, the system acquires specific information in four sub-tables through the id of the rule; the db_ aggregative _value records the corresponding aggregation column name, aggregation operation and sequencing field of each aggregation type in different databases and data tables; the db_ aggregative _subject stores a mapping dictionary of the aggregated columns, and is used for translating and converting the aggregated column fields to generate language answers meeting the requirements; the db_ aggregative _graph table judges whether to present a final answer in the form of a graph according to the aggregation type; the db_ aggregative _limit table determines how many records before the returned result is reserved according to the aggregation condition and the supplementary semantic components;
the sql is obtained by natural language conversion, and the conversion process is as follows:
step 21, preprocessing a text;
step 22, determining an SQL module;
Step 23, determining an SQL trunk element value;
Step 24, judging polymerization conditions;
Step 25, determining an aggregation factor value;
Step 26, determining a time element;
Step 27, determining other time elements;
step 28, generating sql.
In a further embodiment, the time element extraction in the step 3 obtains a time-related description from the text, determines the beginning and the end of the time period, maps to a time format in a standard form, and performs matching of semantic components through a db_condition_time table.
In a further embodiment, the condition element extracting in the step 4 obtains other conditions except for the time element behind the where keyword according to the text and the data table, and relies on three tables of db_condition, db_condition_subject and db_condition_sql; and the db_condition is a main table for extracting the condition elements, and records semantic component information to be matched of each data source and each data table.
In a further embodiment, a system for converting configurable natural language into sql comprises the following modules:
The processing module is used for constructing an sql structure to preprocess the problem text; the processing module is used for preprocessing a problem text, removing unimportant words, converting some expressions into vocabulary, and determining a template of sql through matching of semantic components; the background of the system is maintained through three tables of db_ PREHANDLE, DB _structure and db_structure_case; the db_ prehandle is a text preprocessing table, and words which need to be replaced for the text are stored through two field keys and values;
The db_structure is a table of sql templates, each of which determines the basic framework of the query sql, in the format of "select { SQLPHRASE } from { table1} wher1=1 { condition1} { timeCondition1_1} { groupColumn } { sort }";
the db_structure_case is a main table matched with the sql template through semantic components, wherein a structure_id field is the sql template id corresponding to the semantic components, and the rest fields record sql main element information such as a database, a table name, an associated field, a nested field and the like, and the information and the sql template structure are determined together;
The process for preprocessing the question text comprises the following steps:
Step 11, segmenting and sentence dividing is carried out on an input text, the text is decomposed into short sentences, and the first step of a semantic-based preprocessing flow is the same as the traditional preprocessing flow;
Step 12, word segmentation processing is carried out on the segmented and sentence-separated text, word segmentation results are analyzed, whether segmentation ambiguity exists or not is judged, and if yes, the semantic knowledge base is utilized to carry out disambiguation processing on the segmentation ambiguity of the preliminary word segmentation results;
Step 13, part-of-speech tagging and word processing are carried out on the word segmentation result, part-of-speech ambiguity of the double-class word is eliminated by utilizing semantic information and related rules of a semantic knowledge base, and the accuracy of part-of-speech tagging is improved;
Step 14, performing stop word processing and recognition of unregistered words on the word segmentation result;
Step 15, mapping a concept space by utilizing the obtained result, merging words with the same concept into the same word, selecting a consistent feature selection operation method, and extracting feature words from the text of the feature to be extracted;
step 16, forming a semantic vector representation model of the text;
A diagnosis module for judging the aggregation condition through semantic component matching; the diagnosis module determines the required aggregation condition elements in the sql template through semantic component matching judgment, and the background determines a matching flow by using five tables of db_ AGGREGATIVE, DB _ aggregative _value, db_ aggregative _subject, db_ aggregative _graph and db_ aggregative _limit; the db_ aggregative is an aggregation condition main table, when the matching of the problem text and the semantic components in the table is successful, the system acquires specific information in four sub-tables through the id of the rule; the db_ aggregative _value records functions and sequencing fields of each aggregation type in different databases and data tables, such as corresponding aggregation column names, aggregation operations, such as count/sum/avg and the like; the db_ aggregative _subject stores a mapping dictionary of the aggregated columns, and is used for translating and converting the aggregated column fields to generate language answers meeting the requirements; the db_ aggregative _graph table judges whether to present a final answer in the form of a graph according to the aggregation type; the db_ aggregative _limit table determines how many records before the returned result is reserved according to the aggregation condition and the supplementary semantic components;
A time module for extracting a text time period using the time element; the time module acquires time-related description from the text, determines the beginning and the end of a time period, maps the time-related description into a time format in a standard form, and performs semantic component matching through a db_condition_time table; the semantic component element matching is a means for confirming each sql element, each semantic component consists of a core expression, co-occurrence words, exclusion words, parts of speech and substitution words, the core expression consists of words or regular expressions and is a core of semantic matching, and the core expression is stored by words, regex, co _ words, exclusive _words and natural fields in a database; the co-occurrence word refers to a word which is necessary to be simultaneously present when the core condition is satisfied; the term exclusion means that if the core condition is met, the matching is unsuccessful; the part of speech refers to that a certain part of speech must be met when the core condition is met, and the background carries out word segmentation and part of speech tagging on the problem by hanlp; the substitute words are words which need to be filtered when the core condition is converted into the elements in the sql after the matching is successful;
The description of the text over time is generalized into two forms: a. the exact point in time is directly set forth, for example, "3 months to 8 months in 2019"; b. describing the far-near relationship with the current moment, such as 'near three days', for the first expression, directly recording normalized year, month and day digital expressions, such as '2019', successfully matching with the regular expression '20 [0-2] [0-9] year', wherein the value of the year1 field is 2019; for the second type of expression, the second type of expression is stored in a database in the form of a java function template, for example, the "near three days" is successfully matched with "[ near| (past) ] [0-9] + [ day|day ]", and the corresponding day1 field is "GETPASTDAY (value)", wherein GETPASTDAY represents calling the function GETPASTDAY in the java program, and { value } represents the value to which the regular expression is matched, namely 3; when the Java background code reads the expression, a function is automatically called, and the time of day three before is returned; the time element extraction also depends on a db_condition_time_table table in which the time field in each table of each data source is defined;
A condition extraction module for acquiring conditions other than the time element through text; the condition extraction module acquires other conditions except for a time element behind the where key according to the text and the data table, and depends on three tables of db_condition, db_condition_subject and db_condition_sql; the db_condition is a main table for extracting the condition elements, and semantic component information to be matched of each data source and each data table is recorded; in addition, in the data table of each row, the sql field is a foreign key of a template id of a conditional element, for example, db_condition_sql, for example, "and id= { value }", and the value field is a { value } value filled in the template; the dictionary field is the dictionary id of db_condition_subject, which represents a dictionary matched in batches, and when the types of core expressions in semantic components are too many, the dictionary associated with foreign keys can be used for replacing the core expressions.
In summary, the present invention has the following advantages: the key semantic elements of each part form an internal mutually exclusive semantic cluster, and the SQL template, the aggregation condition elements, the time elements and other condition elements can be determined through the matching of the key semantic elements of each part, so that an accurate SQL sentence is generated, and the key semantic elements of each part can be freely configured through the maintenance of a structured table in a database.
In addition, the specific features described in the above embodiments may be combined in any suitable manner, and in order to avoid unnecessary repetition, various possible combinations are not described further.
Claims (6)
1. A method for converting configurable natural language into sql, comprising the steps of:
step 1, constructing an SQL structure to preprocess a problem text, determining an SQL module and determining an SQL trunk element value;
Step 2, judging aggregation conditions through semantic component matching, and determining an aggregation factor value;
step 3, extracting a text time period by using the time element, and determining the time element;
step 4, acquiring conditions except the time elements through texts, and determining other time elements;
Step 5, generating sql;
Firstly, preprocessing a problem text, removing unimportant words, converting some expressions into vocabulary, and then, determining a template of the sql through matching of semantic components;
The background of the system is maintained through three tables of db_ PREHANDLE, DB _structure and db_structure_case; the db_ prehandle is a text preprocessing table, and words which need to be replaced for the text are stored through two field keys and values;
the db_structure is a table of sql templates, each of which determines the basic framework of the query sql in the format of "select { SQLPHRASE } from { table1} wuere1=1 { condition1} { timeCondition1_1} { groupColumn } { sort }";
the db_structure_case is a main table matched with the sql template through semantic components, wherein a structure_id field is the sql template id corresponding to the semantic components, and other fields record the main element information of a database, a table name, an associated field and a nested field sql, and the main element information and the sql template structure are determined together;
The process for preprocessing the question text comprises the following steps:
Step 11, segmenting and sentence dividing is carried out on an input text, the text is decomposed into short sentences, and the first step of a semantic-based preprocessing flow is the same as the traditional preprocessing flow;
Step 12, word segmentation processing is carried out on the segmented and sentence-separated text, word segmentation results are analyzed, whether segmentation ambiguity exists or not is judged, and if yes, the semantic knowledge base is utilized to carry out disambiguation processing on the segmentation ambiguity of the preliminary word segmentation results;
Step 13, part-of-speech tagging and word processing are carried out on the word segmentation result, part-of-speech ambiguity of the double-class word is eliminated by utilizing semantic information and related rules of a semantic knowledge base, and the accuracy of part-of-speech tagging is improved;
Step 14, performing stop word processing and recognition of unregistered words on the word segmentation result;
Step 15, mapping a concept space by utilizing the obtained result, merging words with the same concept into the same word, selecting a consistent feature selection operation method, and extracting feature words from the text of the feature to be extracted;
Step 16, forming a semantic vector of the text;
in the step 2, the aggregation condition elements needed in the sql template are determined through semantic component matching determination, and the background determines a matching flow by using five tables of db_ AGGREGATIVE, DB _ aggregative _value, db_ aggregative _direct, db_ aggregative _graph and db_ aggregative _limit; the db_ aggregative is an aggregation condition main table, when the matching of the problem text and the semantic components in the table is successful, the system acquires specific information in four sub-tables through the id of the rule; the db_ aggregative _value records the corresponding aggregation column name, aggregation operation and sequencing field of each aggregation type in different databases and data tables; the db_ aggregative _subject stores a mapping dictionary of the aggregated columns, and is used for translating and converting the aggregated column fields to generate language answers meeting the requirements; the db_ aggregative _graph table judges whether to present a final answer in the form of a graph according to the aggregation type; the db_ aggregative _limit table determines how many records before retaining the returned results based on the aggregation conditions and the supplemental semantic components.
2. The method for converting configurable natural language into sql according to claim 1, wherein the time element extraction in the step 3 obtains a time-related description from text, determines the beginning and end of a time period, maps to a time format of standard form, and performs semantic component matching through a db_condition_time table.
3. The method for converting configurable natural language into sql according to claim 1, wherein the condition element in the step 4 extracts other conditions except for a time element behind a where keyword is obtained according to text and data table, and depends on three tables db_condition, db_condition_subject, db_condition_sql; db_condition is a main table for extracting the condition elements, and semantic component information to be matched of each data source and each data table is recorded.
4. A system for configurable natural language translation sql, comprising:
The processing module is used for constructing an sql structure to preprocess the problem text;
a diagnosis module for judging the aggregation condition through semantic component matching;
a time module for extracting a text time period using the time element;
a condition extraction module for acquiring conditions other than the time element through text;
the processing module is used for preprocessing a problem text, removing unimportant words, converting some expressions into vocabulary, and determining a template of sql through matching of semantic components;
The background of the system is maintained through three tables of db_ PREHANDLE, DB _structure and db_structure_case; the db_ prehandle is a text preprocessing table, and words which need to be replaced for the text are stored through two field keys and values;
The db_structure is a table of sql templates, each of which defines a basic framework of the query sql, and the format is "select { SQLPHRASE } from { table1} wuere1=1 { condition1} { timeCondition1_1} { groupColumn } { solt }";
the db_structure_case is a main table matched with the sql template through semantic components, wherein a structure_id field is the sql template id corresponding to the semantic components, and other fields record the main element information of a database, a table name, an associated field and a nested field sql, and the main element information and the sql template structure are determined together;
The process for preprocessing the question text comprises the following steps:
Step 11, segmenting and sentence dividing is carried out on an input text, the text is decomposed into short sentences, and the first step of a semantic-based preprocessing flow is the same as the traditional preprocessing flow;
Step 12, word segmentation processing is carried out on the segmented and sentence-separated text, word segmentation results are analyzed, whether segmentation ambiguity exists or not is judged, and if yes, the semantic knowledge base is utilized to carry out disambiguation processing on the segmentation ambiguity of the preliminary word segmentation results;
Step 13, part-of-speech tagging and word processing are carried out on the word segmentation result, part-of-speech ambiguity of the double-class word is eliminated by utilizing semantic information and related rules of a semantic knowledge base, and the accuracy of part-of-speech tagging is improved;
Step 14, performing stop word processing and recognition of unregistered words on the word segmentation result;
Step 15, mapping a concept space by utilizing the obtained result, merging words with the same concept into the same word, selecting a consistent feature selection operation method, and extracting feature words from the text of the feature to be extracted;
step 16, forming a semantic vector representation model of the text;
The diagnosis module determines the required aggregation condition elements in the sql template through semantic component matching judgment, and the background determines a matching flow by using five tables of db_ AGGREGATIVE, DB _ aggregative _value, db_ aggregative _subject, db_ aggregative _graph and db_ aggregative _limit; the db_ aggregative is an aggregation condition main table, when the matching of the problem text and the semantic components in the table is successful, the system acquires specific information in four sub-tables through the id of the rule; the db_ aggregative _value records the corresponding aggregation column name, aggregation operation and sequencing field of each aggregation type in different databases and data tables; the db_ aggregative _subject stores a mapping dictionary of the aggregated columns, and is used for translating and converting the aggregated column fields to generate language answers meeting the requirements; the db_ aggregative _graph table judges whether to present a final answer in the form of a graph according to the aggregation type; the db_ aggregative _limit table determines how many records before retaining the returned results based on the aggregation conditions and the supplemental semantic components.
5. The system for converting natural language into sql in claim 4, wherein the time module obtains a time-dependent description from text, determines a beginning and an end of a time period, maps to a time format in a standard form, and performs semantic component matching through a db_condition_time table.
6. The system for converting configurable natural language into sql according to claim 4, wherein said condition extraction module obtains other conditions except for time elements behind a where key according to text and data tables, and relies on three tables db_condition, db_condition_subject, db_condition_sql; and the db_condition is a main table for extracting the condition elements, and records semantic component information to be matched of each data source and each data table.
Priority Applications (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN202011204186.4A CN112347121B (en) | 2020-11-02 | 2020-11-02 | Configurable natural language sql conversion method and system |
Applications Claiming Priority (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN202011204186.4A CN112347121B (en) | 2020-11-02 | 2020-11-02 | Configurable natural language sql conversion method and system |
Publications (2)
| Publication Number | Publication Date |
|---|---|
| CN112347121A CN112347121A (en) | 2021-02-09 |
| CN112347121B true CN112347121B (en) | 2024-05-28 |
Family
ID=74355812
Family Applications (1)
| Application Number | Title | Priority Date | Filing Date |
|---|---|---|---|
| CN202011204186.4A Active CN112347121B (en) | 2020-11-02 | 2020-11-02 | Configurable natural language sql conversion method and system |
Country Status (1)
| Country | Link |
|---|---|
| CN (1) | CN112347121B (en) |
Families Citing this family (2)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN117875908B (en) * | 2024-03-08 | 2024-07-23 | 蒲惠智造科技股份有限公司 | Work order processing method and system based on enterprise management software SAAS |
| CN118296035B (en) * | 2024-06-03 | 2024-09-06 | 浙江大华技术股份有限公司 | Sentence generation method, sentence generation device, and computer storage medium |
Citations (7)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN104657440A (en) * | 2015-01-30 | 2015-05-27 | 欧阳江 | Structured query statement generating system and method |
| CN104657439A (en) * | 2015-01-30 | 2015-05-27 | 欧阳江 | Generation system and method for structured query sentence used for precise retrieval of natural language |
| CN105701253A (en) * | 2016-03-04 | 2016-06-22 | 南京大学 | Chinese natural language interrogative sentence semantization knowledge base automatic question-answering method |
| CN107451153A (en) * | 2016-05-31 | 2017-12-08 | 北京京东尚科信息技术有限公司 | The method and apparatus of export structure query statement |
| CN109002516A (en) * | 2018-07-06 | 2018-12-14 | 国网电子商务有限公司 | A kind of searching method and device |
| CN111159330A (en) * | 2018-11-06 | 2020-05-15 | 阿里巴巴集团控股有限公司 | Database query statement generation method and device |
| CN111324631A (en) * | 2020-03-19 | 2020-06-23 | 成都海天数联科技有限公司 | Method for automatically generating sql statement by human natural language of query data |
Family Cites Families (3)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| US8554650B1 (en) * | 2002-07-31 | 2013-10-08 | Ariba, Inc. | Importable template |
| US7376645B2 (en) * | 2004-11-29 | 2008-05-20 | The Intellection Group, Inc. | Multimodal natural language query system and architecture for processing voice and proximity-based queries |
| US11550783B2 (en) * | 2019-04-18 | 2023-01-10 | Sap Se | One-shot learning for text-to-SQL |
-
2020
- 2020-11-02 CN CN202011204186.4A patent/CN112347121B/en active Active
Patent Citations (7)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN104657440A (en) * | 2015-01-30 | 2015-05-27 | 欧阳江 | Structured query statement generating system and method |
| CN104657439A (en) * | 2015-01-30 | 2015-05-27 | 欧阳江 | Generation system and method for structured query sentence used for precise retrieval of natural language |
| CN105701253A (en) * | 2016-03-04 | 2016-06-22 | 南京大学 | Chinese natural language interrogative sentence semantization knowledge base automatic question-answering method |
| CN107451153A (en) * | 2016-05-31 | 2017-12-08 | 北京京东尚科信息技术有限公司 | The method and apparatus of export structure query statement |
| CN109002516A (en) * | 2018-07-06 | 2018-12-14 | 国网电子商务有限公司 | A kind of searching method and device |
| CN111159330A (en) * | 2018-11-06 | 2020-05-15 | 阿里巴巴集团控股有限公司 | Database query statement generation method and device |
| CN111324631A (en) * | 2020-03-19 | 2020-06-23 | 成都海天数联科技有限公司 | Method for automatically generating sql statement by human natural language of query data |
Non-Patent Citations (2)
| Title |
|---|
| "DBPal: A Fully Pluggable NL2SQL Training Pipeline";Nathaniel Weir 等;《Research 26: Usability and Natural Language User Interfaces》;20200619;第2347-2361页 * |
| "自然语言生成多表SQL查询语句技术研究";曹金超 等;《计算机科学与探索》;20191014;第1133-1141页 * |
Also Published As
| Publication number | Publication date |
|---|---|
| CN112347121A (en) | 2021-02-09 |
Similar Documents
| Publication | Publication Date | Title |
|---|---|---|
| CN116628173B (en) | Intelligent customer service information generation system and method based on keyword extraction | |
| US10380065B2 (en) | Method for establishing a digitized interpretation base of dongba classic ancient books | |
| CN113032418B (en) | Method for converting complex natural language query into SQL (structured query language) based on tree model | |
| CN112347121B (en) | Configurable natural language sql conversion method and system | |
| CN112380848B (en) | Text generation method, device, equipment and storage medium | |
| CN112445894A (en) | Business intelligent system based on artificial intelligence and analysis method thereof | |
| CN115098706A (en) | Network information extraction method and device | |
| RU61442U1 (en) | SYSTEM OF AUTOMATED ORDERING OF UNSTRUCTURED INFORMATION FLOW OF INPUT DATA | |
| CN114780582A (en) | Natural answer generating system and method based on form question and answer | |
| CN114528312A (en) | Method and device for generating structured query language statement | |
| CN111831624A (en) | Data table creating method and device, computer equipment and storage medium | |
| CN117743526A (en) | Table question-answering method based on large language model and natural language processing | |
| CN117271558A (en) | Language query model construction method, query language acquisition method and related devices | |
| CN114239579A (en) | Electric power searchable document extraction method and device based on regular expression and CRF model | |
| CN118170907A (en) | Document intelligent label system based on deep neural network and implementation method thereof | |
| CN117875307A (en) | Text parsing method and device for intelligent question and answer | |
| CN117688220A (en) | Multi-mode information retrieval method and system based on large language model | |
| CN117573797A (en) | Test question retrieval method based on large language model | |
| CN117473054A (en) | Knowledge graph-based general intelligent question-answering method and device | |
| CN114842982B (en) | Knowledge expression method, device and system for medical information system | |
| CN114398492B (en) | Knowledge graph construction method, terminal and medium in digital field | |
| CN112488593B (en) | Auxiliary bid evaluation system and method for bidding | |
| CN115017271A (en) | Method and system for intelligently generating RPA flow component block | |
| CN118394954B (en) | Knowledge graph construction method and system for standard data elements of biomedical data set | |
| CN112836047B (en) | Electronic medical record text data enhancement method based on sentence semantic replacement |
Legal Events
| Date | Code | Title | Description |
|---|---|---|---|
| PB01 | Publication | ||
| PB01 | Publication | ||
| SE01 | Entry into force of request for substantive examination | ||
| SE01 | Entry into force of request for substantive examination | ||
| GR01 | Patent grant | ||
| GR01 | Patent grant |