The average dwell time of a certain type of product in the supermarket
Below are some tables in a simplified supermarket inventory management system.
Firstly, there is the commodity table Commodity.txt, which records information about various products:
The following is the purchase record table Stock.txt. The supermarket purchases goods at 5 am every day, and the table records the quantity of each product purchased each time:
Finally, there is the supermarket's sales record table Sale.txt:
To investigate the sales situation, it is necessary to calculate the average dwell time of "Fresh" products in the supermarket in June 2009.
Firstly, we need to solve how to calculate the "average dwell time". The supermarket cannot keep separate records for each item, so calculating the dwell time for each item is not feasible. Through mathematical conversion, it can be known that the "average dwell time" can be obtained by subtracting the average purchase time from the average selling time. This algorithm can solve our current problem. As long as the purchase and sales records of the specified category of products are filtered out, it is also easy to calculate the average time.
| A | B | |
|---|---|---|
| 1 | =T("Commodity.txt").keys(ID) | |
| 2 | =T("Stock.txt").select(month@y(Datetime)==200906) | =T("Sale.txt").select(month@y(Datetime)==200906) |
| 3 | >cate="Fresh",day1=date(2009,6,1) | >A2.switch(Commodity,A1), B2.switch(Commodity,A1) |
| 4 | =A2.select(Commodity.Category==cate) | =B2.select(Commodity.Category==cate) |
| 5 | =A4.sum(Volume) | =A4.sum(interval@s(day1, Datetime)*Volume) |
| 6 | =B4.sum(Volume) | =B4.sum(interval@s(day1, Datetime)*Volume) |
| 7 | =(A5-A6)*interval@s(day1, elapse@m(day1,1)) | =round((A7+B6-B5)/A5/(3600*24),1) |
https://try.esproc.com/splx?33l
A1 reads the commodity information table, A2 reads the purchase record table for June 2009, and B2 reads the sales record table for June 2009.
A3 sets the variables used for calculation, sets category cate as 'fresh', and sets the initial calculation time as 0 o’clock on June 1, 2009.
B3 uses the switch function to make foreign key joins between commodity information and purchase and sales information, converting product sequence numbers into corresponding product records. In A4 and B4, select the purchase and sales information for fresh produce.
A5 calculates the total purchase quantity of all fresh products, B5 calculates the sum of the product of the time difference between the purchase time and the starting time multiplied by the purchase quantity:
A6 calculates the total sales volume of all fresh products, B6 calculates the sum of the product of the time difference between the sales time and the starting time multiplied by the sales quantity:
If both purchase and sales are calculated based on the starting time, A7 calculates the total dwell time of fresh products. In B7, adjustments are made based on the results of B5 and B6 to obtain the actual total dwell time. After dividing by the total number of products, the total average dwell seconds can be obtained, which can then be converted to the average dwell days with one decimal place:
