Prepare test data for the sales management system
Prepare simulated data for testing a sales management system. Request to generate 4 tables, with the following data requirements:
Salesperson table, requires 50 salespersons in 5 regions: NorthChina, SouthChina, CentralChina, Southwest, Northwest; 4 types of educational backgrounds: Senior High School, Associate Degree, Bachelor, Master. The table structure is as follows:
The client table requires 200 client data, with the following structure:
Product table, requiring simulation of 30 types of products, with the following structure:
Sales contract table, requiring simulation of 10000 sales records, distributed over a period of one year in 2024:
There are two key issues that need to be addressed when creating test data: first, how to implement the entry of a large amount of data, and second, how to ensure that the inserted data has a certain degree of randomness. For the first point, we can use the loop insertion of esProc to implement; For the second point, it can be implemented using the rand function.
| A | B | C | |
|---|---|---|---|
| 1 | [NorthChina,SouthChina,CentralChina,Southwest,Northwest] | ||
| 2 | [Senior High School,Associate Degree,Bachelor,Master] | ||
| 3 | >n1=50,n2=200,n3=30,n4=1000,len1=A1.len(),len2=A2.len() | ||
| 4 | =create(ID,Name,Education ,Area) | >A4.insert(0:n1,~,"Salesperson"/#, A2(rand(len2)+1), A1(rand(len1)+1)) | |
| 5 | =create(ID,Name,Contact,Address,Phone) | >A5.insert(0:n2,~,"Client"/#,"---","------", "87654321") | |
| 6 | =create(ID,Name,Price) | >A6.insert(0:n3,~, "Product"/#, rand(91)*100+1000) | |
| 7 | 2024-1-1 | =create(ContractNo,Client,Product,Sale, SellDate,Quantity) | |
| 8 | >B7.insert(0:n4,~,A5(rand(n2)+1).ID,A6(rand(n3)+1).ID,A4(rand(n1)+1).ID,A7+rand(365), rand(5)+1) | ||
| 9 | >T("test_Sale.txt":A4) | =T("test_Sale.txt") | |
| 10 | >T("test_Client.txt":A5) | =T("test_Client.txt") | |
| 11 | >T("test_Product.txt":A6) | =T("test_Product.txt") | |
| 12 | >T("test_Contract.txt":B7) | =T("test_Contract.txt") |
A1 and A2 prepare some constant sequences required for creating data, including sales regions and employee education levels.
A3 defines some variables used, including the data volume of each table, as well as the total number of regions and educational backgrounds.
A4 creates a salesperson table. B4 generates random data. The ID field can be filled in with numbers from 1 to 50, the name field can be combined with Salesperson and ID, and the education and region fields can be randomly selected from the constant sequences of B1 and A1 using the rand function. After execution, the result of A4 is as follows:
A5 creates client table. B5 generates data, using sequence numbers to fill in the ID field, combining the Client and ID fields for the name, and filling in multiple "-" characters for the contact and address fields since they are not important. Similarly, the phone field is not important, just write a random number.
A6 creates a product table. B6 randomly generates data, and the ID and name can be modeled after the previous two tables. For the price field, use the rand function to generate multiples of 100 with a lower limit of 1000 and an upper limit of 10000.
Finally, construct the contract table. A7 defines the initial date of the contracts, B7 creates a table sequence, A8 randomly fills in data, and the ID field uses the sequence number as before. The Client, Product, and Sale fields are randomly selected from the previously generated table using the rand function, and the ID field value is extracted. Even if their IDs are not integers, they can still be used normally; The SellDate field can be generated using the initial date in A7 and rand function to ensure it falls within the year 2024 range; The Quantity field can be randomly selected within a certain range, here is 1-5.
Finally, use the T function to write the result table sequence to a file. After outputting to the file, you can use the T function to read and view it.