NL2SQL time analysis method and device

Abstract

The invention relates to the technical field of natural language processing, and particularly provides an NL2SQL time analysis method and device, based on a regular expression, two aspects of time range and time dimension in a text are analyzed and correspond to two parts of WHERE and GROUPBY in an SQL respectively; the method comprises the following steps: S1, preprocessing a text; s2, extracting a date text related to time; s3, the date texts are merged; s4, analyzing the date text into a date object; s5, converting the date object into a time object; s6, generating a time range by using the time object; and S7, generating a time dimension by using the time object. Compared with the prior art, the method has the advantages that the accuracy and efficiency of NL2SQL conversion can be remarkably improved, so that the data query requirement of a user can be better met.

Description

Technical Field

[0001] The present invention relates to the technical field of natural language processing, and specifically provides an NL2SQL time parsing method and device. Background Art

[0002] Today, with the rapid development of big data and artificial intelligence, users' demand for data query not only continues to grow, but also shows a diversified trend. Due to its disadvantages such as cumbersome operation, the traditional database query method has gradually been unable to meet the needs of modern users who pursue convenience and efficiency. In this context, the birth of the natural language interface (NL2SQL) technology has brought a brand-new data query experience to users.

[0003] The NL2SQL technology allows users to directly interact with the database through natural language without having to master complex query statements or syntax rules. This intuitive interaction method greatly reduces the user's usage threshold, enabling even non-technical personnel to easily perform data queries, thus significantly improving the user experience.

[0004] However, despite the many conveniences brought by the NL2SQL technology, there are still many challenges in the implementation process. Among them, the complexity and diversity of time information in natural language are particularly prominent problems. In natural language, time information can appear in various forms, such as specific dates, fuzzy time ranges, and relative time descriptions. These different time expressions bring quite a lot of difficulties to the accurate parsing of NL2SQL systems.

[0005] Even more complex is that when it comes to the parsing of time ranges and time dimensions, the problem becomes even more intractable. The time range may require the system to be able to identify and process continuous or discontinuous time periods, while the time dimension requires the system to accurately understand the specific time point or time period referred to by the user. These requirements not only test the language understanding ability of NL2SQL systems, but also pose higher requirements for the algorithms and data processing capabilities behind them. Summary of the Invention

[0006] The present invention aims at the above-mentioned deficiencies of the prior art and provides a highly practical NL2SQL time parsing method.

[0007] A further technical task of the present invention is to provide an NL2SQL time parsing device with reasonable design, safety and applicability.

[0008] The technical solution adopted by the present invention to solve its technical problems is:

[0009] An NL2SQL time parsing method, based on regular expressions, includes parsing two aspects of time range and time dimension in the text, corresponding to the WHERE and GROUP BY parts in SQL respectively;

[0010] It has the following steps:

[0011] S1. Text preprocessing;

[0012] S2. Extract date text related to time;

[0013] S3. Merge the date text;

[0014] S4. Parse the date text into a date object;

[0015] S5. Convert the date object into a time object;

[0016] S6. Generate a time range using the time object;

[0017] S7. Generate a time dimension using the time object.

[0018] Furthermore, in step S1, preprocess the input natural language text by deleting interference information and unifying the digital format;

[0019] The deletion of interference information is to sequentially delete redundant interference information in the natural language text through regular matching;

[0020] The unification of the digital format is to uniformly convert the digital information in the natural language text into the Arabic numeral format through regular matching.

[0021] Furthermore, in step S2, the regular expression sequentially extracts the relevant text information that conforms to the date expression format in the natural language text. Among them, the dates involved in the regular expression include the standard date format: YYYY - MM - DD, the standard time format: HH:MM:SS, specific time points, fuzzy time expressions, relative time expressions, and specific cycle expressions.

[0022] Furthermore, in step S3, store the date text in a queue as a date text list, traverse the identified date text, use the regular expression to identify the time granularity corresponding to the current text. If the time granularity of the current text is less than that of the previous date text, then merge the two date texts.

[0023] Furthermore, in step S4, it includes:

[0024] S4 - 1. Build a time context based on the system time;

[0025] S4-2. Traverse the processed date text, identify based on the constructed time context, convert various types of date text into standard dates, store them in a date object together with the date text, and update the time context to the latest standard date after each conversion.

[0026] When encountering a date text of the time range type, the standard date is stored as the start node information of the time period, and at the same time, the date object is marked as the time range type.

[0027] S4-3. Store the parsed date objects in a queue as a list of date objects.

[0028] Furthermore, in step S5, it includes:

[0029] (5-1) When there is one element in the list of date objects, use the standard of the only element in the list of date objects as the start time, calculate the end time according to the date text and start time of the date object, identify the smallest time granularity involved in the date text according to the regular expression as the time granularity of the time object, and judge whether the span of the current time range of the date is greater than the current time granularity according to the start time, end time, and time granularity;

[0030] The multi-time period flag is marked as false, and the multi-time object list is empty;

[0031] (5-2) When there are two elements in the list of date objects, there are the following two situations for the conversion of the time objects with two elements in the list of date objects;

[0032] a. When any one of the two elements is a date object of the time range type: Mark the multi-time period flag as true, process the two date object elements respectively according to the logic of having one element in the list of date objects, store the obtained list of time objects as the multi-time object list, and use the smaller time granularity of the two date objects as the time granularity of the time object according to the regular expression;

[0033] b. When both elements are date objects of the non-time range type: Use the standard date of the earlier date object as the start time, use the standard time of the later date object as the end time, use the smaller time granularity of the two date objects as the time granularity of the time object according to the regular expression, judge whether the span of the current time range of the date is greater than the time granularity, the multi-time period flag is marked as false, and the multi-time object list is empty;

[0034] (5-3) There are multiple elements in the list of date objects:

[0035] Mark the multi - time period flag as true, and process multiple date objects in the list according to the logic that there is one element in the date object list respectively. Store the obtained time object list as a multi - time object list, and use the smaller time granularity among multiple date objects as the time granularity of the time object according to the regular expression.

[0036] Further, in step S6, it includes:

[0037] (6 - 1) When the multi - time period flag of the time object is false, use the start time and end time of the time object as the upper and lower limits of the time range as the time range filtering condition;

[0038] (6 - 2) When the multi - time period flag of the time object is true, use the concatenation of multiple time ranges in the multi - time object list of time as the time range filtering condition.

[0039] Further, in step S7, it includes:

[0040] Use the regular expression to identify whether there is a specified query time dimension in the original text. If it exists, summarize according to the time granularity as the time dimension;

[0041] If there is no specified time dimension, judge whether the multi - time period flag multiple or the cross - time granularity flag cross of the time object is true. If so, summarize according to the time granularity stored by the time object as the time dimension;

[0042] If none of the above conditions exist, summarize according to the system design scheme combined with the time granularity of the time object to configure the default time dimension summarization scheme.

[0043] An NL2SQL time parsing device, including: at least one memory and at least one processor;

[0044] The at least one memory is used to store machine - readable programs;

[0045] The at least one processor is used to call the machine - readable program and execute an NL2SQL time parsing method.

[0046] Compared with the prior art, an NL2SQL time parsing method and device of the present invention have the following prominent beneficial effects:

[0047] The present invention has a high accuracy rate for time information parsing. The optimized time parsing method design can now more accurately identify and process time - related data, effectively reducing query errors caused by parsing errors. This not only improves the accuracy of data processing but also provides more reliable query results for users.

[0048] Efficient time information parsing efficiency. Without relying on the capabilities of large models, the NL2SQL time information can be parsed locally through regular expressions and algorithms, shortening the data processing time and enabling users to obtain faster feedback when retrieving information. This speed improvement directly enhances the user experience, making it smoother and more efficient for users to obtain the required information.

[0049] Lower requirement for manual intervention. This method improves the automation level of natural language queries. By designing relevant algorithm functions, the system can more accurately capture the time information in the user's language intention, thus completing complex query tasks without frequent manual correction. Brief Description of the Drawings

[0050] In order to more clearly illustrate the technical solutions in the embodiments of the present invention or the prior art, the following will briefly introduce the drawings required for the description of the embodiments or the prior art. Obviously, the drawings in the following description are some embodiments of the present invention. For those of ordinary skill in the art, without creative efforts, other drawings can also be obtained based on these drawings.

[0051] Appendix Figure 1 is a flowchart of an NL2SQL time parsing method. Detailed Embodiments

[0052] In order to enable those skilled in the art to better understand the solution of the present invention, the following will further elaborate on the present invention in combination with specific embodiments. Obviously, the described embodiments are only some embodiments of the present invention, not all of them. Based on the embodiments of the present invention, all other embodiments obtained by those of ordinary skill in the art without creative efforts fall within the scope of protection of the present invention.

[0053] The following presents a best embodiment:

[0054] As Figure 1 shown, in this embodiment, during the process of converting natural language to SQL, the time parsing mainly includes parsing the time range and time dimension in the text, corresponding to the WHERE and GROUP BY parts in SQL respectively.

[0055] Accordingly, an NL2SQL time parsing method is proposed, based on regular expressions, with the following steps:

[0056] S1. Text preprocessing;

[0057] Perform preprocessing on the input natural language text by deleting interference information and unifying the digital format.

[0058] Among them, deleting interference information is to delete redundant interference information in the natural language text in sequence through regular matching, such as blank information such as spaces, line breaks and tab indentations, auxiliary verb information such as "的地得", and digital interference information in decimal form.

[0059] The unified digital format is to convert the digital information in the natural language text into Arabic numeral format through regular matching, such as two thousand and two is converted to 2002, and twelve is converted to 12.

[0060] S2, extracting time-related date text;

[0061] Regular expressions are used to sequentially extract relevant text information in natural language text that conforms to the date expression format. Among them, the dates that may be involved in the regular expressions are as follows.

[0062] (1) Standard date format: YYYY-MM-DD; YYYY year MM month DD day, etc.

[0063] (2) Standard time format: HH:MM:SS, etc.

[0064] (3) Specific time point: A specific time point is expressed as 5:40, including 12-hour time using AM / PM and 24-hour time.

[0065] (4) Fuzzy time expressions: yesterday, tomorrow, and today, etc.; morning, evening, and noon, etc.; recently, later, and in the future, etc.; early morning, early morning, and evening, etc.; approximate time such as about a week later, in the next few years, etc.

[0066] (5) Relative time expressions: combinations of numbers and time units, such as three days ago, nearly a year, and two months later; colloquial expressions such as last year, the year before last, and last month.

[0067] (6) Specific period expression: Commonly used time periods include quarters and weeks.

[0068] (7)Other possible date formats.

[0069] S3. Merge date texts;

[0070] When more than one date text is identified, the texts are merged based on the time granularity information contained in each date text.

[0071] Specifically:

[0072] The date texts stored in the queue are used as a date text list, the recognized date texts are traversed, and regular expressions are used to identify the time granularity corresponding to the current text, such as year, month, and day. If the time granularity of the current text is smaller than the time granularity of the previous date text (such as the time granularity of the year is greater than the month), the two date texts are merged.

[0073] S4. Parse the date text into a date object;

[0074] The preferred date object structure includes information such as time context, standard date, date text, and whether it is a date object of the time range type.

[0075] Including:

[0076] S4-1. Build a time context based on the system time;

[0077] S4-2. Traverse the processed date text, identify based on the built time context, convert various types of date text into standard dates, and store them in the date object together with the date text. After each conversion, update the time context to the latest standard date.

[0078] Note: When encountering a date text of the time range type, the standard date is stored as the start node information of this time period, and at the same time, this date object is marked as the time range type.

[0079] S4-3. Store the parsed date objects in a queue as a list of date objects.

[0080] S5. Convert the date object into a time object;

[0081] The basic information of the time object includes the start time start, end time end, time granularity granularity, cross-time granularity identifier cross, multi-time period identifier multiple, and a list of multi-time objects for describing a certain time range. Among them, the list of multi-time objects is used to store multiple parsed sub-time objects.

[0082] (5-1) There is one element in the list of date objects:

[0083] Use the standard of the only element in the list of date objects as the start time; calculate the end time according to the date text and start time of the date object; identify the smallest time granularity involved in the date text according to the regular expression as the time granularity of the time object; judge whether the span of the current time range of the date is greater than the current time granularity according to the start time, end time and time granularity; the multi-time period identifier is false; the list of multi-time objects is empty.

[0084] (5-2) There are two elements in the list of date objects: There are the following two situations for the conversion of the time object with two elements in the list of date objects.

[0085] a. When any one of the two elements is a date object of the time range type: Mark the multi-time period flag as true, and process the two date object elements respectively according to the logic when there is one element in the date object list. Store the obtained time object list as the multi-time object list, and use the smaller time granularity among the two date objects as the time granularity of the time object according to the regular expression.

[0086] b. When both elements are date objects of non-time range type: Use the standard date of the earlier date object as the start time, and use the standard time of the later date object as the end time. Use the smaller time granularity among the two date objects as the time granularity of the time object according to the regular expression. Judge whether the span of the current time range of the date is greater than the time granularity according to the start time, end time and time granularity. The multi-time period flag is false, and the multi-time object list is empty.

[0087] (5-3) When there are multiple elements in the date object list: Mark the multi-time period flag as true, and process the multiple date objects in the list respectively according to the logic when there is one element in the date object list. Store the obtained time object list as the multi-time object list, and use the smaller time granularity among the multiple date objects as the time granularity of the time object according to the regular expression.

[0088] S6. Generate a time range using the time object;

[0089] Generate the WHERE condition of the SQL based on the date information field of the table to be queried and the generated time object. At the same time, support establishing time fields with different granularities in the way of language modeling, and match according to the time granularity stored in the time object to make the recognition of the time range more accurate.

[0090] When the multi-time period flag of the time object is false, use the start time and end time of the time object as the upper and lower limits of the time range as the time range filtering condition;

[0091] When the multi-time period flag of the time object is true, splice multiple time ranges with the multi-time object list of the time as the time range filtering condition.

[0092] S7. Generate a time dimension using the time object;

[0093] Generate the GROUP BY condition of the SQL based on the date information field of the table to be queried and the generated time object. At the same time, support establishing time fields with different granularities in the way of language modeling, and match according to the time granularity stored in the time object to make the recognition of the time dimension more accurate.

[0094] Use regular expressions to identify whether there are specified query time dimensions in the original text, such as words like "every day" and "each quarter". If so, summarize according to this time granularity as the time dimension.

[0095] If there is no specified time dimension, determine whether the multi-time period identifier multiple or the cross-time granularity identifier cross of the time object is true. If so, summarize according to the time granularity stored in the time object as the time dimension.

[0096] If the above conditions do not exist, summarize according to the system design scheme combined with the time granularity of the time object to configure the default time dimension summary scheme.

[0097] Based on the above method, a NL2SQL time parsing device in this embodiment includes: at least one memory and at least one processor;

[0098] The at least one memory is used to store machine-readable programs;

[0099] The at least one processor is used to call the machine-readable program to execute a NL2SQL time parsing method.

[0100] The above specific implementation manners are only specific cases of the present invention. The patent protection scope of the present invention includes but is not limited to the above specific implementation manners. Any technical solution that conforms to the above specific implementation manners of the present invention and any appropriate changes or substitutions made by those of ordinary skill in the art shall fall within the patent protection scope of the present invention.

[0101] Although the embodiments of the present invention have been shown and described, those of ordinary skill in the art can understand that various changes, modifications, substitutions, and variations can be made to these embodiments without departing from the principles and spirit of the present invention. The scope of the present invention is defined by the appended claims and their equivalents.

Claims

1. A NL2SQL time parsing method, characterized in that Based on regular expressions, it includes the parsing of two aspects, namely the time range and time dimension in the text, corresponding to the WHERE and GROUP BY parts in SQL respectively; It has the following steps: S1. Text preprocessing; S2. Extract date texts related to time; S3. Merge the date texts; S4. Parse the date texts into date objects; S5. Convert the date objects into time objects; S6. Generate a time range using the time objects; S7. Generate a time dimension using the time objects.

2. The NL2SQL time parsing method according to claim 2, characterized in that, In step S1, preprocess the input natural language text by deleting interference information and unifying the digital format; The deletion of interference information is to sequentially delete the redundant interference information in the natural language text through regular matching; The unification of the digital format is to uniformly convert the digital information in the natural language text into the Arabic numeral format through regular matching.

3. The NL2SQL time parsing method according to claim 2, wherein In step S2, the regular expression sequentially extracts the relevant text information that conforms to the date expression format in the natural language text. Among them, the dates involved in the regular expression include the standard date format: YYYY - MM - DD, the standard time format: HH:MM:SS, specific time points, fuzzy time expressions, relative time expressions, and specific period expressions.

4. The NL2SQL time parsing method according to claim 3, wherein In step S3, store the date texts in a queue as a date text list, traverse the identified date texts, use the regular expression to identify the time granularity corresponding to the current text. If the time granularity of the current text is less than that of the previous date text, then merge the two date texts.

5. A NL2SQL time parsing method according to claim 4, characterized in that, In step S4, it includes: S4 - 1. Construct a time context based on the system time; S4 - 2. Traverse the processed date texts, identify based on the constructed time context, convert various date texts into standard dates, store them in the date object together with the date texts. After each conversion, update the time context to the latest standard date; When encountering a date text of the time range type, store the standard date as the start node information of the time period, and at the same time mark the date object as the time range type. S4 - 3. Store the parsed date objects in a queue as a date object list.

6. The NL2SQL time parsing method according to claim 5, characterized in that, In step S5, it includes: (5 - 1) When there is one element in the date object list, use the standard of the only element in the date object list as the start time, calculate the end time according to the date text and start time of the date object, identify the minimum time granularity involved in the date text according to the regular expression as the time granularity of the time object, and judge whether the span of the current time range of the date is greater than the current time granularity according to the start time, end time, and time granularity; The multi - time period flag is false, and the multi - time object list is empty; (5 - 2) When there are two elements in the date object list, there are the following two situations for the conversion of the time objects containing two elements in the date object list; a. When any one of the two elements is a date object of the time range type: Mark the multi-time period flag as true, and process the two date object elements respectively according to the logic when there is one element in the date object list. Store the obtained time object list as the multi-time object list, and use the smaller time granularity among the two date objects as the time granularity of the time object according to the regular expression; b. When both elements are date objects of non-time range type: Use the standard date of the earlier date object as the start time, and use the standard time of the later date object as the end time. Use the smaller time granularity among the two date objects as the time granularity of the time object according to the regular expression. Determine whether the span of the current time range of the date is greater than the time granularity based on the start time, end time, and time granularity. The multi-time period flag is false, and the multi-time object list is empty; (5-3) There are multiple elements in the date object list: Mark the multi-time period flag as true, and process the multiple date objects in the list respectively according to the logic when there is one element in the date object list. Store the obtained time object list as the multi-time object list, and use the smaller time granularity among the multiple date objects as the time granularity of the time object according to the regular expression.

7. A NL2SQL time parsing method according to claim 6, wherein In step S6, it includes: (6-1) When the multi-time period flag of the time object is false, use the start time and end time of the time object as the upper and lower limits of the time range as the time range filtering condition; (6-2) When the multi-time period flag of the time object is true, use the multi-time object list of time to splice multiple time ranges as the time range filtering condition.

8. A NL2SQL time parsing method according to claim 7, characterized in that In step S7, it includes: Use the regular expression to identify whether there is a specified query time dimension in the original text. If it exists, summarize according to the time granularity as the time dimension; If there is no specified time dimension, determine whether the multi-time period flag multiple or the cross-time granularity flag cross of the time object is true. If so, summarize according to the stored time granularity of the time object as the time dimension; If none of the above conditions exist, summarize according to the default time dimension summarization scheme configured in combination with the time granularity of the time object according to the system design plan.

9. An NL2SQL time parsing device, characterized in that, It includes: At least one memory and at least one processor; The at least one memory is used to store machine-readable programs; The at least one processor is used to call the machine-readable program and execute the method according to any one of claims 1 to 8.

Images