A traffic analysis method and system based on text2sql
Through the traffic analysis method based on text2sql, using a large language model to generate SQL and build a RAG system, the problems of low analysis accuracy and high employment cost in traffic data analysis are solved, and efficient and easy-to-use traffic data analysis process is realized, reducing the Workers cost and operational complexity.
Patent Information
- Application Number
- CN202411191918.9
- Authority / Receiving Office
- CN · China
- Patent Type
- Patents(China)
- Current Assignee / Owner
- Filing Date
- 2024-08-28
- Publication Date
- 2024-11-12
- Estimated Expiration
- 2044-08-28
AI Technical Summary
In the analysis of traffic data, the existing technology has problems such as low analysis accuracy, high employment cost, and low scalability of analysis requirements. The traditional method requires collaboration of multiple positions and depends on professional operations, resulting in increased communication and training costs.
Using the traffic analysis method based on text2sql, collect business SQL, perform natural language description and data enhancement, generate SQL using large language models, and build a Retrieval-augmented Generation (RAG) system to realize user natural language input into the database query and analysis, reducing Operational complexity.
It realizes simple system operation and efficient analysis, relying on the professional capabilities of large models, generating trustworthy analysis conclusions, reducing the cost of practitioners, and improving the automation and scalability of the analysis process.
Smart Images

Figure CN118689903B_ABST
Abstract
Description
Technical Field
[0001] The present invention belongs to the technical field of traffic data analysis, and in particular relates to a traffic analysis method and system based on text2sql. Background Art
[0002] In today's artificial intelligence field, large language models (LLMs) are developing rapidly, and they are gradually becoming an important driving force for technological innovation and industry applications. These models have demonstrated unprecedented ability to understand and generate natural language through massive data training, providing strong support for a variety of application scenarios. First of all, large models have broad application prospects in the field of natural language processing (NLP). They can be used for tasks such as text summarization, translation, question-answering systems, and sentiment analysis, greatly improving the efficiency and accuracy of information processing. With the continuous advancement of technology, the performance of large models in understanding and generating natural language is getting closer and closer to human levels, which has brought revolutionary changes to the fields of intelligent assistants, customer service, and content creation. Secondly, large models have great potential for cross-domain applications. They can not only process text data, but also understand various types of data such as images and sounds through multimodal learning, which has promoted the deep integration and innovative application of artificial intelligence in industries such as medical care, education, and entertainment.
[0003] With the development of network communication technology, people's daily network communication has become more convenient, but more traffic consumption has also come with it, resulting in a rapid increase in traffic data received by major operators. The demand for in-depth mining of traffic data and the realization of capabilities is also gradually increasing.
[0004] There are also many challenges in querying, analyzing and mining traffic data: on the one hand, there is a lot of data, and the amount of data generated every day is at the TB or even PB level. In this way, the query of data, the formation of charts, the analysis of conclusions, and the formation of strategies require the collaboration of multiple different positions, which greatly increases the cost of employment and communication; on the other hand, the results of the analysis are greatly affected by the basic quality of the personnel. Any problem in any of the steps described above may lead to deviations in the final conclusion.
[0005] Based on the above problems, the current solutions in the industry mainly include: relying on a third-party WEB platform to obtain data, and then professional business personnel to analyze and mine the data to form the final conclusion; because the WEB platform page provided by this method is relatively fixed and inflexible, some companies will choose to use intelligent BI to obtain data, and then form business-related targeted reports for display based on short-term analysis needs, and then business personnel will obtain operation plans through data. However, these two methods still have many shortcomings. The first method requires personnel to adapt to the operation of the WEB platform, and the scalability of analysis needs is low. The second method requires multiple positions to cooperate, which increases communication costs; if a single person completes a certain business chain, a higher level of professionalism is required, resulting in an increase in employment costs and training costs. Therefore, it is necessary to develop a new traffic analysis method and system based on text2sql to solve the existing problems. Summary of the invention
[0006] The purpose of the present invention is to provide a traffic analysis method and system based on text2sql to solve the problem of low analysis accuracy when the traffic is large.
[0007] To achieve the above object, the present invention provides the following technical solution: A traffic analysis method based on text2sql comprises the following steps:
[0008] Step 1: Collect and organize the SQL statements of each business, collect the SQL statements of each business history query of the system from various channels, and then abstract them into natural language descriptions through business, and combine the two into the initial data set;
[0009] Step 2: Perform data enhancement on the data in step 1, input the collected data into the general large model, use the large model to process the input text, generate multiple text contents with the same meaning but different expressions, and annotate them with the same data labels;
[0010] Step 3: Divide the data set and fine-tune the model. Use the above enhanced data to divide the training set, validation set, and test set; and fine-tune the sqlcoder model;
[0011] Step 4: Build a Retrieval-augmented Generation (RAG) system. Use the natural language part of the validation set data, SQL, and the corresponding table structure and table structure description to build the RAG system.
[0012] Step 5: Use the fine-tuned sqlcoder model above to pass in the user's query text content, the schema of all data tables in the database, and similar input text and SQL pairs found in the RAG system using the corresponding text content as few-shot cases, and input them into the prompt template;
[0013] Step 6: Use SQL to query data and complete data analysis. Use the SQL output by the model to query data from the corresponding database, then integrate the data into the data analysis prompt template and input it into the big model to help complete the data analysis task.
[0014] Preferably, in step 1, all query SQLs are first collected from the existing WEB system or BI system, and business behaviors are abstracted based on the SQL contents, and these business behaviors are expressed in natural language. Then, these natural language expressions and corresponding SQLs are combined in sequence to generate an initial data set.
[0015] Preferably, in step 2, the natural language description portion of the data set obtained in step 1 is input into a general large model, the input natural language content is rewritten using the large model, and the rewritten natural language description is bound to the SQL of the original description and added to the data set as new data to complete the expansion of the data set.
[0016] Preferably, in step 3, the data set after data enhancement in step 2 is randomly divided into a training set, a validation set, and a test set in a ratio of 6:2:2, and based on the large model sqlcoder in the text2sql field, the lora technology is used to perform SFT supervised fine-tuning on the sqlcoder model.
[0017] Preferably, in step 4, the vector database chromdb is selected as the storage medium, the natural language part of the data in the verification set is embedded using the bge-large-zh model, the encoding is used as the index, and the SQL part and the structure of the table used in the SQL and the table structure description are used as the data part and stored in the vector database to build a RAG system.
[0018] Preferably, in step 5, after obtaining the system input, the natural language is first embedded using bge-large-zh, and cosine similarity is used from RAG to query two similar natural language embedding codes and their corresponding data. These two pieces of data are used as cases in few-shot and filled into the prompt template together with the original input natural language, and input into the large model fine-tuned in step 3, and the SQL corresponding to the natural language is output.
[0019] Preferably, in step 6, the SQL output in the above steps is input into a database query tool to complete the data query, and the queried data is packaged into a data analysis prompt and input into a general large model for data analysis to obtain the final analysis results, and finally the queried data and the large model analysis results are output; before the prompt is input into the large model, a role needs to be pre-set for the prompt, that is, telling the large model which aspect of knowledge needs to be used to process the input data.
[0020] The present invention further provides a traffic analysis system based on text2sql, comprising:
[0021] The collection and sorting module is used to collect SQL and sort it into an initial data set;
[0022] The enhancement module is used to input the initial data set into the general large model and process it;
[0023] A partition fine-tuning module that randomly partitions the augmented dataset and fine-tunes the general large model;
[0024] Build a module to store the natural language, SQL, table structure and table structure description of the validation set into the vector database to build an enhanced generation system;
[0025] Build the reasoning module, use the fine-tuned general large model, pass in the user's query text content, the schema of all data tables in the database, and use the corresponding text content to find similar input text and SQL pairs in the enhanced generation system as cases, and input them into the prompt template;
[0026] The query analysis module is used to perform data query and analysis using SQL.
[0027] The technical effects and advantages of the present invention are as follows: the traffic analysis method and system based on text2sql, aiming at the drawbacks in the traditional data analysis and data mining workflows, combines the vertical big language model with the traditional data analysis needs, and uses the big language model to complete a whole set of processes including the generation of data acquisition SQL, the calling of the database data acquisition tool, the generation and rendering of the data table, and the generation of the final data analysis results through the input of natural language. The system is easy to operate and the analysis is efficient. Based on the professional capabilities of the big model, reliable analysis conclusions and opinions are obtained. The RAG, text2sql big model and general big model technologies are introduced. Compared with the traditional analysis method, the input of the method is simple. The user only needs to input the business content to be analyzed to complete the data acquisition and analysis, and it is easy to use. At the same time, the data acquisition, analysis and output can be integrated, and there is no need for too much operation and some other skills besides business knowledge, which can make the business personnel more focused on business analysis and reduce the personnel cost of the practitioners. BRIEF DESCRIPTION OF THE DRAWINGS
[0028] Figure 1 is a flow chart of the present invention;
[0029] Figure 2 The text2sql vertical large model fine-tuning flow chart of the present invention;
[0030] Figure 3 is a flow chart of the RAG system of the present invention;
[0031] Figure 4 The prompt template diagram of the text2sql vertical model of the present invention;
[0032] Figure 5 This is a display diagram of the input and output results of the present invention. DETAILED DESCRIPTION
[0033] The following will be combined with the drawings in the embodiments of the present invention to clearly and completely describe the technical solutions in the embodiments of the present invention. Obviously, the described embodiments are only part of the embodiments of the present invention, not all of the embodiments. Based on the embodiments of the present invention, all other embodiments obtained by ordinary technicians in this field without creative work are within the scope of protection of the present invention.
[0034] Example 1
[0035] The present invention provides Figure 1 A traffic analysis method based on text2sql as shown in the figure includes the following steps:
[0036] Step 1: Collect and organize the SQL statements of each business, collect the SQL statements of each business history query of the system from various channels, and then abstract them into natural language descriptions through business, and combine the two into the initial data set;
[0037] Step 2: Perform data enhancement on the data in step 1, input the collected data into a general large model, use the large model to process the input text, generate multiple text contents with the same meaning but different expressions, and label them with the same data tags; the general large model can be a natural language processing large model;
[0038] Step 3: Divide the data set and fine-tune the model. Use the above enhanced data to divide the training set, validation set, and test set; and fine-tune the model;
[0039] Step 4: Build the RAG system, using the natural language part of the validation set data, SQL, and the corresponding table structure and table structure description to build the RAG system;
[0040] Step 5: Use the above-mentioned fine-tuned sqlcoder model to pass in the user's query text content, the schema of all data tables in the database, and similar input text and SQL pairs found in the RAG system using the corresponding text content as cases in few-shot, and input them into the prompt template; the main function of few-shot is to guide the model to parse by introducing a small number of case samples in the prompt words. This application provides the cases that are most similar to the user's questions, so that the large model can refer to these few samples when generating SQL, imitate these samples, and obtain model outputs similar to these samples;
[0041] Step 6: Use SQL to query data and complete data analysis. Use the SQL output by the model to query data from the corresponding database, then integrate the data into the data analysis prompt template and input it into the big model to help complete the data analysis task.
[0042] In summary, if Figure 1 As shown, in a specific embodiment, the present invention can be mainly divided into several parts: first, the user's natural language input is used to find similar descriptions in RAG to form a prompt template, and then the template is input into the text2sql model to generate SQL and use the SQL query tool to query data, and the queried data is input into the large model for analysis and output together with the data.
[0043] Specifically, in step 1, first collect all query SQL from the existing WEB system or BI system, organize the query scenarios, and form a clear natural language description based on specific business needs. In addition, it is necessary to organize the structure of all tables in the query SQL and the business description of all fields in the table, and establish a mapping relationship by matching the natural language, SQL, table structure and table structure description one by one.
[0044] Specifically, in step 2, the natural language description generated in step 1 is input into a general large language model. The prompt description controls the large model to output three expressions similar to the original natural language description. The newly generated expression establishes the same SQL, table structure, and table structure description mapping with the original description, so that the original data set is expanded by 3 times.
[0045] Specifically, in step 3, if Figure 2 As shown, the above-mentioned enhanced data is used to split the data set. The main original data set is split into a training set, a validation set, and a test set. The ratio of the three is 6:2:2. The data is used to fine-tune the vertical text2sql model SQLcoder. The specific method of fine-tuning is Lora.
[0046] Specifically, in step 4, if Figure 3 As shown in the figure, the construction process of the RAG system is as follows; first, the verification data set generated in step 2 is used as the original data, the natural language part is embedded and encoded using the bge-large-zh model, and the encoded vector is used as the index, and the corresponding SQL, table structure, and table field description are stored as data in the vector database chromdb; when the user enters a new natural language for query, it is also encoded using the bge-large-zh model, and then the two most similar encoding indexes are found from the vector database, and the SQL, table structure, and field description corresponding to the two encoding indexes are retrieved.
[0047] Specifically, in step 5, if Figure 4 In the example, example_input1, example_sql1, example_input2, and example_sql2 are the two similar cases found in step 4 using the user's natural language output. Input is the natural language description of the similar case, sql is the corresponding SQL query to be generated, prompt is the natural language query description of this input, and the last table_schemas is the table structure of all tables in the two similar cases and the business description of each field in the table. In the reasoning stage, this entire template needs to be input into the sqlcoder model for reasoning to obtain the SQL statement to be queried.
[0048] Specifically, in step 6, if Figure 5 The input and output result display diagram of the device method; after the generation of SQL is completed in step 5, the SQL is handed over to the query tool for SQL query, and the found data will be re-input into the analysis model for data analysis, and the language will be organized for output; in addition, the output will also be attached with a corresponding table display to make the output more specific and intuitive. The original input of the system is the natural language description of the user's business, and the output is the specific data corresponding to the description, as well as the analysis results of these query data. At this point, the system has completed a single query and analysis task.
[0049] Example 2
[0050] The present invention further provides a traffic analysis system based on text2sql, comprising:
[0051] The collection and sorting module is used to collect SQL and sort it into an initial data set;
[0052] The enhancement module is used to input the initial data set into the general large model and process it;
[0053] A partition fine-tuning module that randomly partitions the augmented dataset and fine-tunes the general large model;
[0054] Build a module to store the natural language, SQL, table structure and table structure description of the validation set into the vector database to build an enhanced generation system;
[0055] Build the reasoning module, use the fine-tuned general large model, pass in the user's query text content, the schema of all data tables in the database, and use the corresponding text content to find similar input text and SQL pairs in the enhanced generation system as cases, and input them into the prompt template;
[0056] The query analysis module is used to perform data query and analysis using SQL.
[0057] In summary, when the user inputs an abstract natural language expression of the data that needs to be understood and analyzed, the system will find similar expressions and SQL statements based on history, package them into prompt templates and submit them to the sqlcoder model for reasoning to generate the corresponding SQL query statement; after the SQL statement is generated, the SQL query tool will be used to query the data. After all, the queried data will be input into the general large model again for data analysis to obtain the corresponding results; finally, the data will be organized into a table and presented to the user together with the analysis results; when using the system, the user only needs to input natural language to get the data and analysis results they want, which greatly saves time and personnel costs.
[0058] Finally, it should be noted that the above is only a preferred embodiment of the present invention and is not intended to limit the present invention. Although the present invention has been described in detail with reference to the aforementioned embodiments, it is still possible for those skilled in the art to modify the technical solutions described in the aforementioned embodiments or to make equivalent substitutions for some of the technical features therein. Any modifications, equivalent substitutions, improvements, etc. made within the spirit and principles of the present invention should be included in the protection scope of the present invention.
Claims
1. A traffic analysis method based on text2sql, characterized in that: include: Collect SQL and organize it into an initial data set; The initial data set is fed into the general large model and processed; Randomly split the processed dataset and fine-tune the general large model; The natural language, SQL, table structure, and table structure description of the validation dataset are stored in a vector database to build an enhanced generation system. Using the fine-tuned general model, we input the user's query text, the schema of all tables in the database, and similar input text and SQL pairs found in the enhanced generation system using the corresponding text content as cases, and input them into the prompt template. Use SQL to query data and complete data analysis; The collecting and arranging SQL to form an initial data set includes: collecting SQL of historical queries, abstracting business behaviors based on SQL content, expressing the business behaviors in natural language, and sequentially combining the natural language expressions and corresponding SQL to generate an initial data set; The inputting the initial data set into the universal large model and processing the data set includes: inputting the collected data into the universal large model and processing the input text to generate multiple text contents with the same meaning but different expressions, and annotating the same data labels; The randomly divided processed data set and fine-tuning the general large model include: randomly dividing the data set after data augmentation into a training set, a validation set, and a test set in a ratio of 6:2:2, and using lora technology to perform SFT supervised fine-tuning on the basis of the general large model; The method of performing data query and data analysis using SQL includes: inputting the output SQL into a database query tool to complete the data query, packaging the queried data into a data analysis prompt template, inputting the data into a general large model for data analysis, obtaining analysis results, and outputting the queried data and the general large model analysis results.
2. The traffic analysis method based on text2sql according to claim 1, characterized in that: The inputting of the collected data into the general large model and processing of the input text includes: rewriting the input natural language content using the general large model, binding the rewritten natural language description with the SQL of the original description, adding it as new data to the data set, and completing the expansion of the data set.
3. The traffic analysis method based on text2sql according to claim 1, characterized in that: The storing of the natural language, SQL, table structure, and table structure description of the validation set data set into the vector database includes: using the vector database as a storage medium, embedding encoding the natural language portion of the data in the validation set using the bge-large-zh model, using the encoding as an index, and storing the SQL statement and the table structure and table structure description used in the SQL statement as data in the vector database.
4. The traffic analysis method based on text2sql according to claim 1, characterized in that: The method uses the fine-tuned general large model to input the user's query text content, the schema of all data tables in the database, and similar input text and SQL pairs found in the enhanced generation system using the corresponding text content as cases, and inputs them into the prompt template. The method includes: after obtaining the system input, first embedding the natural language using the bge-large-zh model, using cosine similarity to query two similar natural language embedding codes and their corresponding data from the enhanced generation system, filling the data as a sample case into the prompt template together with the original input natural language, inputting it into the fine-tuned general large model, and outputting the SQL corresponding to the natural language.
5. A traffic analysis system based on text2sql, characterized by: include: The collection and organization module is used to collect SQL and organize it into an initial data set; The collecting and arranging SQL to form an initial data set includes: collecting SQL of historical queries, abstracting business behaviors based on SQL content, expressing the business behaviors in natural language, and sequentially combining the natural language expressions and corresponding SQL to generate an initial data set; An enhancement module is configured to input the initial data set into the general large model and process the data set; the inputting the initial data set into the general large model and processing the data set includes: inputting the collected data into the general large model and processing the input text to generate multiple text contents with the same meaning but different expressions, and annotating the same data tags; A partitioning and fine-tuning module is used to randomly partition the processed dataset and fine-tune the general large model. The randomly partitioning and fine-tuning of the general large model includes: randomly partitioning the data augmented dataset into a training set, a validation set, and a test set in a ratio of 6:2:2, and performing SFT supervised fine-tuning based on the general large model using LoRa technology. Build a module to store the natural language, SQL, table structure, and table structure description of the validation dataset into a vector database to build an enhanced generation system; Build an inference module, using the fine-tuned general large model. Pass in the user's query text, the schema of all data tables in the database, and similar input text and SQL pairs found in the enhanced generation system using the corresponding text content as cases, and input them into the prompt template. The query analysis module is used to perform data query and data analysis using SQL; the method of performing data query and data analysis using SQL includes: inputting the output SQL into the database query tool to complete the data query, packaging the queried data into the data analysis prompt template, inputting it into the general large model for data analysis, obtaining the analysis results, and outputting the queried data and the general large model analysis results.
