NL2SQL automatic prompting method and system based on retail commodity database

Abstract

The invention belongs to the technical field of natural language processing, and particularly provides an NL2SQL automatic prompting method based on a retail commodity database, and the method comprises the steps: collecting NL2SQL problem pairs of the retail commodity industry, and carrying out the data augmentation through employing a data augmentation strategy, and obtaining an augmented database; in combination with augmented database execution feedback and ground-truth SQL contribution feedback, constructing a preference learning data set; performing preference learning fine tuning on the DELLM by using a DPO algorithm; and generating knowledge by using the DELLM subjected to preference learning fine tuning, and generating an SQL statement by combining user query, database architecture and instructions and assisting a Text-to-SQL model. Through the generation of the dynamic cue word and the expansion of knowledge annotation, the accuracy and efficiency of the NL2SQL model in processing complex and personalized query problems are remarkably improved. Besides, through supervised fine tuning and reinforcement learning, the model can continuously learn and adapt to a new query mode, so that more stable and reliable performance is provided in practical application.

Description

Technical Field

[0001] The present disclosure relates to the technical field of natural language processing, and in particular to an NL2SQL automatic prompt method and system based on a retail commodity database. Background Art

[0002] With the rapid development of artificial intelligence technology, the application of natural language processing (NLP) in the field of database query has become more and more extensive. NL2SQL technology, which converts natural language into structured query language (SQL), plays an important role in this field. However, traditional NL2SQL methods usually rely on static prompt words, which are mainly used to describe the structure and basic information of the database. Although this method performs well in simple queries, it is powerless when dealing with complex queries. The limitation of static prompt words is that they lack flexibility and context awareness and cannot effectively respond to personalized query needs. When users query databases, they often use specific terms, aliases or fuzzy expressions, which require the model to have a higher level of understanding ability. In addition, complex queries often involve advanced functions such as indicator calculation and time expression understanding, which are beyond the capabilities of traditional static prompt words. Therefore, it is particularly important to develop an NL2SQL method that can dynamically generate prompt words. This method can not only provide personalized prompts based on the user's specific query needs, but also significantly improve the accuracy and efficiency of the model when dealing with complex queries. By introducing dynamic prompt words, NL2SQL technology can better adapt to diverse query scenarios and meet the various needs of users in database queries. Summary of the invention

[0003] The present disclosure aims to solve at least one of the technical problems existing in the prior art, and proposes an NL2SQL automatic prompt method and system based on a retail commodity database.

[0004] In a first aspect, the present disclosure provides an NL2SQL automatic prompt method based on a retail commodity database, comprising:

[0005] S1, collect NL2SQL problem pairs in the retail industry, adopt data augmentation strategies to perform data augmentation and obtain an augmented database;

[0006] S2, combines the augmented database execution feedback and ground-truth SQL contribution feedback to build a preference learning dataset;

[0007] S3, use the DPO algorithm to fine-tune DELLM for preference learning. The DPO algorithm directly optimizes on these preference data to make it better adapt to the user preferences learned from the preference learning dataset constructed in the S2 stage;

[0008] S4, uses the DELLM generated knowledge after preference learning fine-tuning, and combines user queries, database architecture and instructions to assist the Text-to-SQL model in generating SQL statements.

[0009] Preferably, before S1, the step further includes:

[0010] According to the user's question, additional information is added to generate dynamic prompt words, wherein the user's question is a question asked by the user based on the retail commodity data.

[0011] Preferably, the S1 specifically includes:

[0012] Collect NL2SQL question pairs in the retail industry, annotate the collected NL2SQL question pairs, and expand the model's knowledge in the retail industry.

[0013] Preferably, the data augmentation strategy adopted in S1 to perform data augmentation to obtain an augmented database specifically includes:

[0014] Use CPT4.0, Qwen, and Llama models to restate the problem;

[0015] Generate questions and SQL statements using CPT4.0, Qwen, and Llama models based on question types;

[0016] Use the keyword library to randomly replace the keywords in it.

[0017] Preferably, the S2 specifically includes:

[0018] S21, in the initial stage, supervised learning of LLM is performed using labeled data, so that LLM can generate preliminary knowledge based on user queries and database architecture. The optimization strategy of LLM is shown in Formula 1 to optimize LLM by minimizing the direct distance between labeled knowledge and generated knowledge;

[0019] S22, using the model trained in the initial stage to generate a knowledge set, and using this knowledge to assist an existing Text-to-SQL model to generate SQL statements, executing the generated SQL statements, and comparing the execution results with those of SQL statements generated using manually annotated ground-truth knowledge, and using an indicator function to evaluate the degree of help of the knowledge to database execution based on the matching degree of the execution results;

[0020] S23, analyze the contribution of the generated knowledge to the ground-truth SQL to ensure that the generated knowledge can correctly guide SQL prediction;

[0021] S24, compares the generated SQL statement with the ground-truth SQL to determine which sub-knowledge contributes to the SQL generation.

[0022] Preferably, the S3 specifically includes:

[0023] S31, the DPO algorithm optimizes the objective function so that the model is more inclined to generate knowledge that is more helpful for database execution and SQL generation;

[0024] S32, by exploiting the mapping relationship between the reward function and the optimal strategy, it is proved that this constrained reward maximization problem can be accurately optimized through a single-stage strategy training.

[0025] Preferably, S32 re-precision optimization specifically includes:

[0026] Formula 3 represents the preference loss function, which aims to optimize the strategy by maximizing the probability of strategy selection; by calculating the two choices K w The difference in returns from K1, this formula helps optimize the strategy to better match preferences;

[0027] Formula 4 is used to measure the performance difference between the direct preference optimization strategy and the soft strategy. l The logarithm of the probability ratio of the two strategies to choose K;

[0028] R(K)=(log(π DPO (K|I l ) / π SFT (K|I l ))) Formula 3

[0029] L PL (π DPO ; π SFT )=-E π [logσ(βR(K w )-βR(K l ))] Formula 4

[0030] Among them, E π is the expectation under strategy π, π DPO is the direct preference optimization strategy, π SFT is a soft policy, σ is a sigmoid function used to map the return difference to the probability space, β is a scaling parameter to adjust the impact of the return difference, Pπ(K) is the probability of strategy π choosing action K in a given state, and Pβ(K) is the probability of soft policy β choosing action K in a given state.

[0031] The present invention also provides an NL2SQL automatic prompt system based on a retail commodity database, the system can be used to implement an NL2SQL automatic prompt method based on a retail commodity database, the system comprising:

[0032] A data acquisition module is configured to collect NL2SQL problem pairs of the retail commodity industry and adopt a data augmentation strategy to perform data augmentation to obtain an augmented database;

[0033] The preference learning module is configured to combine the augmented database execution feedback and the ground-truth SQL contribution feedback to construct a preference learning dataset;

[0034] The reinforcement learning module is configured to utilize the DELLM generated knowledge after preference learning fine-tuning, and combine it with user queries, database schema and instructions to assist the Text-to-SQL model in generating SQL statements. BRIEF DESCRIPTION OF THE DRAWINGS

[0035] Figure 1 A flowchart of an NL2SQL automatic prompt method based on a retail commodity database provided by an embodiment of the present disclosure;

[0036] Figure 2 A functional module diagram of an NL2SQL automatic prompt system based on a retail commodity database provided in an embodiment of the present disclosure. DETAILED DESCRIPTION

[0037] In order to enable those skilled in the art to better understand the technical solution of the present disclosure, the present disclosure is further described in detail below in conjunction with the accompanying drawings and specific implementation methods.

[0038] Unless otherwise defined, the technical terms or scientific terms used in the present disclosure should be understood by people with ordinary skills in the field to which the present disclosure belongs. The "first", "second" and similar words used in the present disclosure are not any order, quantity or importance, but are only used to distinguish different components. Similarly, similar words such as "one", "one" or "the" are not limited to quantity, but are at least one. "Including" or "including" and similar words mean that the elements or objects appearing before the word cover the elements or objects listed after the word and their equivalents, without excluding other elements or objects. "Connect" or "connected" and similar words are not limited to physical or mechanical connections, but can include electrical connections, whether direct or indirect. "Up", "down", "left", "right" and the like are only used for relative positional relationships. When the absolute position of the described target changes, the relative positional relationship may also change accordingly.

[0039] In each of the accompanying drawings, the same elements are represented by similar reference numerals. For the sake of clarity, the various parts in the accompanying drawings are not all drawn to scale. In addition, some well-known parts may not be shown in the drawings.

[0040] Many specific details of the present disclosure are described below, such as component structures, materials, dimensions, processing techniques, and technologies, in order to more clearly understand the present disclosure. However, as those skilled in the art will appreciate, the present disclosure may be implemented without following these specific details.

[0041] Combination Figure 1 and Figure 2 As shown, the present invention provides an NL2SQL automatic prompt method based on a retail commodity database, comprising:

[0042] S1, collect NL2SQL problem pairs in the retail industry, adopt data augmentation strategies to perform data augmentation and obtain an augmented database;

[0043] S2, combines the augmented database execution feedback and ground-truth SQL contribution feedback to build a preference learning dataset;

[0044] S3, use the DPO algorithm to fine-tune the preference learning of DELLM;

[0045] S4, uses the DELLM generated knowledge after preference learning fine-tuning, and combines user queries, database architecture and instructions to assist the Text-to-SQL model in generating SQL statements.

[0046] By dynamically generating prompt words, the model's ability to understand user queries is enhanced, thereby improving the accuracy and success rate of SQL generation. It should be noted that the technical solution disclosed in the present invention does not limit the order of the above steps, that is, the execution order of the steps can be arranged arbitrarily.

[0047] The present invention significantly improves the accuracy and efficiency of the NL2SQL model in processing complex and personalized query problems by generating dynamic prompt words and expanding knowledge annotation. In addition, through supervised fine-tuning and reinforcement learning, the model can continuously learn and adapt to new query patterns, thereby providing more stable and reliable performance in practical applications.

[0048] The preferred solution includes before S1:

[0049] According to the user's question, additional information is added and dynamic prompt words are generated, wherein the user question is a user-asked question based on retail product data. Database prompt words are automatically generated according to the user's question to assist the model in understanding the database structure and user query intent. According to the user's question, additional information is automatically added and dynamic prompt words are generated to improve the success rate of SQL generation.

[0050] The preferred solution is step S1: collect some NL2SQL question pairs. Adopt data augmentation strategy to perform data augmentation. Label the collected questions and expand the model's knowledge in a specific field (such as the retail commodity industry) to make up for the model's lack of knowledge in the professional field.

[0051] The data augmentation strategies include: 1) using CPT4.0, Qwen, Llama and other models to restate the question; 2) using CPT4.0, Qwen, Llama and other models to generate questions and SQL statements according to the question type; 3) using the keyword library to randomly replace the keywords in it.

[0052] The preferred solution, step S2: combining database execution feedback and ground-truth SQL contribution feedback to build a preference learning dataset.

[0053] Specific steps: 1) In the initial stage, supervised learning of LLM is performed using labeled data so that it can generate preliminary knowledge based on user queries and database architecture. The optimization strategy of LLM is shown in Formula 1 to optimize LLM by minimizing the direct distance between labeled knowledge and generated knowledge. Where add is the manually labeled knowledge and gen is the knowledge generated by the model; 2) Use the model trained in the initial stage to generate a knowledge set, and use this knowledge to assist an existing Text-to-SQL model in generating SQL statements. Execute the generated SQL statements and compare them with the execution results of SQL statements generated using manually labeled ground-truth knowledge. According to the matching degree of the execution results, an indicator function is used to evaluate the degree of help of knowledge to database execution; 3) Feedback on the contribution of ground-truth SQL: Analyze the contribution of generated knowledge to ground-truth SQL to ensure that the generated knowledge can correctly guide SQL prediction. Then compare the generated SQL statements with ground-truth SQL according to Formula 2 to determine which sub-knowledge contributes to SQL generation.

[0054] L SFT = -logPr(K add |Q)=-∑K add log(K gen ) Formula 1

[0055]

[0056] The preferred solution, step S3: Reinforcement Learning with SQL Feedback (RLSF) uses the DPO algorithm to fine-tune DELLM for preference learning to improve the help of its generated knowledge for database execution accuracy and SQL generation accuracy. Based on the labeled dataset, the model is fine-tuned in a supervised manner to improve the model's understanding and processing capabilities for specific domain problems.

[0057] Specific explanation: 1) The DPO algorithm optimizes the objective function so that the model is more inclined to generate knowledge that is more helpful for database execution and SQL generation. 2) By utilizing the mapping relationship between the reward function and the optimal strategy, it is proved that this restricted reward maximization problem can be accurately optimized through a single-stage strategy training. The precise optimization is divided into two parts. Formula 3 represents the preference loss function, which aims to optimize the strategy by maximizing the probability of strategy selection. Here, π DPO is the direct preference optimization strategy, π SFT is a soft strategy. σ in the formula is a sigmoid function used to map the return difference to the probability space, and β is a scaling parameter that adjusts the impact of the return difference. By calculating the two choices K w The difference between the returns of I and K1 helps optimize the strategy to make it more in line with the preference. Formula 4 is used to measure the performance difference between the direct preference optimization strategy and the soft strategy. By calculating the return difference of K1 given the information I l The logarithm of the ratio of the probabilities of choosing K for the two strategies is given by , which provides a measure of the relative return of choosing K. This helps to evaluate and adjust the strategy to make it more effective in meeting the optimization goal.

[0058] R(K)=(log(π DPO (K|I l ) / π SFT (K|I l ))) Formula 3

[0059] L PL (π DPO ; π SFT )=-E π [logσ(βR(K w )-βR(K l ))] Formula 4

[0060] Among them, E π is the expectation under strategy π, π DPO is the direct preference optimization strategy, π SFTis a soft policy, σ is a sigmoid function used to map the return difference to the probability space, β is a scaling parameter to adjust the impact of the return difference, Pπ(K) is the probability of strategy π choosing action K in a given state, and Pβ(K) is the probability of soft policy β choosing action K in a given state.

[0061] Based on user feedback and collected preference data pairs, the DPO algorithm is used to optimize the model's preference.

[0062] The preferred solution, step S4: knowledge generation and SQL prediction, in the testing phase, uses DELLM after preference learning fine-tuning to generate knowledge, and combines user queries, database architecture and instructions to assist the existing Text-to-SQL model in generating SQL statements. Using the execution results of the SQL statements generated by the model, the SQL generation strategy of the model is optimized through the reinforcement learning algorithm.

[0063] Combination Figure 2 As shown, the embodiment of the present invention further provides an NL2SQL automatic prompt system based on a retail commodity database, which can be used to implement the above-mentioned NL2SQL automatic prompt method based on a retail commodity database, and the system includes:

[0064] A data acquisition module is configured to collect NL2SQL problem pairs of the retail commodity industry and adopt a data augmentation strategy to perform data augmentation to obtain an augmented database;

[0065] The preference learning module is configured to combine the augmented database execution feedback and the ground-truth SQL contribution feedback to construct a preference learning dataset;

[0066] The reinforcement learning module is configured to utilize the DELLM generated knowledge after preference learning fine-tuning, and combine it with user queries, database schema and instructions to assist the Text-to-SQL model in generating SQL statements.

[0067] All relevant symbols in the present invention are explained as follows:

[0068] add: manually annotated knowledge collection.

[0069] gen: The knowledge collection generated by the model.

[0070] SQLgen: SQL statements generated by the model.

[0071] SQLgtt: Ground-truth SQL, that is, manually annotated correct SQL statements.

[0072] Δ: Difference function, used to compare the differences between two SQL statements.

[0073] π: Direct preference optimization strategy.

[0074] β: Soft policy, usually used to represent non-deterministic policies.

[0075] σ(x): Sigmoid function, usually defined as σ(x) = 11 + e-xσ(x) = 1 + e-x1, used to convert the input

[0076] ΔR: Return difference, which represents the difference in returns when adopting different strategies.

[0077] τ: Scaling parameter used to adjust the impact of return differences on policy updates.

[0078] K: Specific strategies or action options.

[0079] Pπ(K) is the probability that policy π chooses action K in a given state.

[0080] Pβ(K) is the probability that the soft policy β chooses action K in a given state.

[0081] E π : The mathematical expectation under strategy π

[0082] log: Natural logarithm function, used to calculate the logarithm of the ratio of two probabilities.

[0083] L: Loss function, used to evaluate the difference between the model prediction and the actual results.

[0084] θ: model parameters.

[0085] α: learning rate, used to control the speed at which model parameters are updated during training.

[0086] D: Dataset.

[0087] N: The number of samples in the dataset.

[0088] i: index, usually used to represent a single sample in a dataset.

[0089] It is to be understood that the above embodiments are merely exemplary embodiments used to illustrate the principles of the present disclosure, but the present disclosure is not limited thereto. For those of ordinary skill in the art, various modifications and improvements can be made without departing from the spirit and substance of the present disclosure, and these modifications and improvements are also considered to be within the scope of protection of the present disclosure.

Claims

1. An NL2SQL automatic prompt method based on a retail commodity database, characterized in that: include: S1, collect NL2SQL problem pairs in the retail industry, adopt data augmentation strategies to perform data augmentation and obtain an augmented database; S2, combines the augmented database execution feedback and ground-truth SQL contribution feedback to build a preference learning dataset; S3, use the DPO algorithm to fine-tune DELLM for preference learning. The DPO algorithm optimizes directly on these preference data to make it better adapt to the user preferences learned from the preference learning dataset constructed in the S2 stage. S4, uses the DELLM generated knowledge after preference learning fine-tuning, and combines user queries, database architecture and instructions to assist the Text-to-SQL model in generating SQL statements.

2. The NL2SQL automatic prompt method based on the retail commodity database according to claim 1, characterized in that: The S1 also includes: According to the user's question, additional information is added to generate dynamic prompt words, wherein the user's question is a question asked by the user based on the retail commodity data.

3. The NL2SQL automatic prompt method based on the retail commodity database according to claim 1 is characterized in that: The S1 specifically includes: Collect NL2SQL question pairs in the retail industry, annotate the collected NL2SQL question pairs, and expand the model's knowledge in the retail industry.

4. The NL2SQL automatic prompt method based on the retail commodity database according to claim 1 is characterized in that: The data augmentation strategy adopted in S1 to perform data augmentation to obtain an augmented database specifically includes: Use CPT4.0, Qwen, and Llama models to restate the problem; Generate questions and SQL statements using CPT4.0, Qwen, and Llama models based on question types; Use the keyword library to randomly replace the keywords in it.

5. The NL2SQL automatic prompt method based on the retail commodity database according to claim 1 is characterized in that: The S2 specifically includes: S21, in the initial stage, supervised learning of LLM is performed using labeled data, so that LLM can generate preliminary knowledge based on user queries and database architecture. The optimization strategy of LLM is shown in Formula 1 to optimize LLM by minimizing the direct distance between labeled knowledge and generated knowledge; S22, using the model trained in the initial stage to generate a knowledge set, and using this knowledge to assist an existing Text-to-SQL model to generate SQL statements, executing the generated SQL statements, and comparing the execution results with those of SQL statements generated using manually annotated ground-truth knowledge, and using an indicator function to evaluate the degree of help of the knowledge to database execution based on the matching degree of the execution results; S23, analyze the contribution of the generated knowledge to the ground-truth SQL to ensure that the generated knowledge can correctly guide SQL prediction; S24, compares the generated SQL statement with the ground-truth SQL to determine which sub-knowledge contributes to the SQL generation.

6. The NL2SQL automatic prompt method based on the retail commodity database according to claim 1 is characterized in that: The S3 specifically includes: S31, the DPO algorithm optimizes the objective function so that the model is more inclined to generate knowledge that is more helpful for database execution and SQL generation; S32, by exploiting the mapping relationship between the reward function and the optimal strategy, it is proved that this constrained reward maximization problem can be accurately optimized through a single-stage strategy training.

7. The NL2SQL automatic prompt method based on the retail commodity database according to claim 6 is characterized in that: S32 re-precision optimization specifically includes: Formula 3 represents the preference loss function, which aims to optimize the strategy by maximizing the probability of strategy selection; by calculating the two choices K w and K l The formula helps optimize the strategy to better match preferences based on the difference in returns; Formula 4 is used to measure the performance difference between the direct preference optimization strategy and the soft strategy; by calculating the l The logarithm of the probability ratio of the two strategies to choose K; R(K) = (log(π DPO (K|Il) / π SFT (K|Il))) Formula 3 L PL (π DPO ; π SFT ) = -Eπ[logσ(βR(k w ) - βR(K l ))] Equation 4 Among them, E π is the expectation under strategy π, π DPO is the direct preference optimization strategy, π SFT is a soft policy, σ is a sigmoid function used to map the return difference to the probability space, β is a scaling parameter to adjust the impact of the return difference, Pπ(K) is the probability of strategy π choosing action K in a given state, and Pβ(K) is the probability of soft policy β choosing action K in a given state.

8. An NL2SQL automatic prompt system based on a retail commodity database, characterized in that: The system can be used to implement the NL2SQL automatic prompt method based on the retail commodity database as described in any one of claims 1 to 7 above, and the system includes: A data acquisition module is configured to collect NL2SQL problem pairs of the retail commodity industry and adopt a data augmentation strategy to perform data augmentation to obtain an augmented database; The preference learning module is configured to combine the augmented database execution feedback and the ground-truth SQL contribution feedback to construct a preference learning dataset; The reinforcement learning module is configured to utilize the DELLM generated knowledge after preference learning fine-tuning, and combine it with user queries, database schema and instructions to assist the Text-to-SQL model in generating SQL statements.

Images