Introduction
ChatGPT was restless in March, and Microsoft was restless, so Yong should be restless? Impossible, absolutely impossible. To put it simply, Brother Yong successfully integrated chatGPT3.5 into bg-tinkle software in March and released version v1.0.5. After the integration of bg-tinkle, operations such as database modification, statistics, and deletion are all in one sentence, which is very NICE.
After reading this article, you will learn about the major events of chatGPT in March, and experience how to use AI to help database optimization, SQL design and other operations? And understand the idea of development and implementation of this function?
Review of the event: a restless event in March
-
ChatGPT competing products appear: (Google’s) Bard, (Baidu’s) Wenxinyiyan
-
chatGPT releases a new version: chatGPT4 is grandly released (but Brother Yong is not CARE, because it is too expensive and not eligible)
-
The chatGPT finished product appears: Microsoft Office is officially connected to chatGPT, and you no longer need to register for classes to learn after writing word, ppt, and excel (but the price is not cheap~)
Database + AI: Functional Experience
The AI function is carried out in the form of chat, and then the chat results can be directly executed SQL and graph making operations.
Through the database software, directly obtain the database table structure, and then use AI to help you design statistical analysis, modification, optimization, etc. on this basis, and directly generate SQL statements. Then you can execute SQL with one click in the result, or generate a statistical graph of SQL.
AI Completion Table Note
Don’t be afraid to forget to write notes, one-click to let AI supplement + one-click to run, and the work will be completed in less than 10 seconds! Great praise~
AI design table index
You can send the commonly used SQL statements of the table, and then let it design a reasonable index. The index it designs is quite in line with industry norms. Great praise~
AI creates a new table
Based on the existing database, refer to design a new table. Divided into multiple sessions to communicate, there is still no problem.
AI results run directly
The query SQL in the AI result will be executed automatically, and the result will be displayed in a tabular form. At the same time, if there is a problem with the relevant SQL, it also supports execution after editing.
AI results are made into charts
The SQL statements generated by AI can be directly transferred to the chart generation function, such as fast and normal line charts, pie charts, histograms, tree diagrams, Sankey diagrams, etc.
Database + AI: Use Help-Open Dialog
In the database software, it is very simple to enter the AI chat, just follow the following 2 steps:
-
Select the table you want to operate, you can choose multiple sheets;
-
Right click and select Sql Chat to open the chat window
ChatGPT access requires Token:
If you have your own token, you can directly click the small gear in the lower right corner and fill in your own token.
If you don’t have your own token, you can get a token through the Reward Token Function’, and then you can access it.
Database + AI: Use Help – Save Session
The database AI chat conversation will be saved to the user’s local area so that it can be opened again to save traffic. To customize the saved file name, refer to the following steps:
-
Click on the ‘pinion’ in the lower right corner
-
Fill in the saved ‘file name’ in the pop-up dialog box
-
Send the request again, and the program will automatically save the session.
To open it, you only need to double-click the corresponding session name (for example, double-click 3 in the figure below to open the session again).
Database + AI: Implementation ideas
It also implements the above chat function with session context. The principle of its implementation is actually very simple, but it is a bit expensive. The specific implementation idea is as follows:
-
Store all the messages in the conversation in a collection, such as the first code below
-
Filter the dialog messages in the collection that failed requests, such as the second code below
ps: The main reason for filtering out messages that fail to process is to reduce the requested Token and save costs;
-
Request the interface and obtain the response data, analyze the SQL statement on the response data, and mark it for front-end rendering demonstration and execution, such as the third code below
// 1. Store all dialogue messages
List<ChatMessageDto> dtos = JSON.parseArray(tempMessages, ChatMessageDto.class);
// 2. Filter failed messages
List<ChatMessage> messages = dtos.stream().filter(item -> item.getIsCall()==null||item.getIsCall()).map(item ->{
ChatMessage chatMessage = new ChatMessage();
chatMessage.setRole(item.getRole());
chatMessage.setContent(item.getContent());
return chatMessage;
}
).collect(Collectors.toList());
ChatMessageDto result = new ChatMessageDto();
List<ChatCompletionChoice> chatCompletionChoices = AiUtils.chatGpt(token, messages);
for (ChatCompletionChoice chatCompletionChoice : chatCompletionChoices) {
String content = chatCompletionChoice. getMessage(). getContent();
// 3. Convert the string message into a structured message, which is convenient for running SQL
result.setChatTerms(AiUtils.parseChatMessage(content));
result. setContent(content);
result.setRole(chatCompletionChoice.getMessage().getRole());
dtos.add(result);
}
Example 3:
Unstructured data:
\\ \\ Statistical dimension:\\ \\ 1. Statistics of total sales by user ID\\ 2. Statistics of sales proportion by payment method\\ 3. Statistics of order quantity and sales by order status Amount\\ 4. Calculate the monthly sales according to the order time\\ \\ SQL statement:\\ \\ 1. Calculate the total sales according to the user ID\\ \\ ```sql\\ SELECT user_id , SUM(total_fee) as total_sales\\ FROM tb_order\\ GROUP BY user_id;\\ ```\\ \\ 2. Calculate the proportion of sales by payment method\\ \\ ```sql\ nSELECT payment_type, SUM(total_fee) as total_sales, \\ ROUND(SUM(total_fee)/(SELECT SUM(total_fee) FROM tb_order)*100,2) as sales_percentage\\ FROM tb_order\\ GROUP BY payment_type;\\ ` ``\\ \\ 3. Count order quantity and sales according to order status\\ \\ ```sql\\ SELECT status, COUNT(*) as order_count, SUM(total_fee) as total_sales\\ FROM tb_order \\ GROUP BY status;\\ ```\\ \\ 4. Calculate the monthly sales according to the order time\\ \\ ```sql\\ SELECT DATE_FORMAT(create_time, '%Y- %m') as month, SUM(total_fee) as total_sales\\ FROM tb_order\\ GROUP BY month;\\ ```
Convert to structured data:
[
{
"content": "\\
\\
Statistical dimensions:\\
\\
1. Total sales by user ID\\
2. Sales ratio by payment method\\
3. Order status Statistics of order quantity and sales\\
4. Statistics of monthly sales according to order placement time\\
\\
SQL statement:\\
\\
1. Statistics of total sales according to user ID\\
\\
",
"type": "MES"
},
{
"content": "\\
SELECT user_id, SUM(total_fee) as total_sales\\
FROM tb_order\\
GROUP BY user_id;\\
",
"type": "SQL"
},
{
"content": "\\
\\
2. Calculate the proportion of sales by payment method\\
\\
",
"type": "MES"
},
{
"content": "\\
SELECT payment_type, SUM(total_fee) as total_sales, \\
ROUND(SUM(total_fee)/(SELECT SUM(total_fee) FROM tb_order)*100,2) as sales_percentage\\
FROM tb_order \\
GROUP BY payment_type;\\
",
"type": "SQL"
},
{
"content": "\\
\\
3. Count order quantity and sales according to order status\\
\\
",
"type": "MES"
},
{
"content": "\\
SELECT status, COUNT(*) as order_count, SUM(total_fee) as total_sales\\
FROM tb_order\\
GROUP BY status;\\
",
"type": "SQL"
},
{
"content": "\\
\\
4. Statistics of monthly sales according to order time\\
\\
",
"type": "MES"
},
{
"content": "\\
SELECT DATE_FORMAT(create_time, '%Y-%m') as month, SUM(total_fee) as total_sales\\
FROM tb_order\\
GROUP BY month;\\
",
"type": "SQL"
}
]