The product with the longest cumulative stockout time in the supermarket within a month
Some tables in a simplified supermarket inventory management system are as follows.
Firstly, there is the commodity table Commodity.txt, which records information about various products:
The following is the Stock.txt purchase record table. Supermarkets purchase goods at 5 am every day, and the table records the quantity of each product purchased each time:
The following is the final inventory table for each month, Storage.txt, which records the remaining inventory of all products on the last day of each month:
Finally, the detailed sales record table Sale.txt of the supermarket:
To assess the suitability of the procurement strategy, it is necessary to calculate the products with the longest cumulative out of stock time in June 2009. The supermarket opens at 8am and closes at 9pm, excluding out of stock during non-business hours.
Firstly, create a table to track remaining inventory and out of stock situations. Then, group the purchase and sales tables by date and loop through each record on a daily basis to instantly track out of stock situations and accumulate out of stock time. Finally, aggregate the out of stock time to identify the product with the longest out of stock time.
| A | B | |
|---|---|---|
| 1 | =T("Commodity.txt").keys(ID) | =T("Stock.txt").select(month@y(Datetime)==200906) |
| 2 | =T("Storage.txt").select(month@y(Date) ==200905) | =T("Sale.txt").select(month@y(Datetime)==200906) |
| 3 | =A2.new(datetime(Date):Datetime,Commodity, Stock:Volume) | =B2.new(Datetime,Commodity,-Volume:Volume) |
| 4 | =(A3|B1|B3).sort(Commodity,Datetime).derive(cum(Volume;Commodity):Stock) | |
| 5 | =A4.group(Commodity;~.sum(if(Stock[1]==null && Stock==0, interval@s(time(Datetime),time(21,0,0)), if(Stock[-1]==0, interval@s(time(Datetime[-1]), time(21,0,0))+13*3600*(Datetime-Datetime[-1]-1)))):oos) | |
| 6 | =A5.maxp@a(oos) |
https://try.esproc.com/splx?2V8
A1 reads the commodity information table, B1 reads the purchase record table for June 2009, A2 reads the inventory information table for the end of May 2009, and B2 reads the sales record table for June 2009.
Next, we plan to integrate inventory information, purchase information, and sales records into one calculation. To do this, we first organize the inventory information in A3 and unify the field names with the other two tables. Organize sales records in B3, where all volumes are to sell, which will result in a decrease in inventory, so change them to negative values.
A4 integrates the sorted inventory table, purchase information table, and sales record table, and adds a Stock column after sorting. Use CUM to calculate the current inventory of each product:
In SPL, records from different tables with the same structure can be merged together for calculation, similar to union in SQL, but without the need to generate a new table, it is composed of records from different tables.
A5 uses the group function to group and aggregate, and calculate the total out of stock time for each product. For each product change, if the last day is out of stock, calculate the stockout time for that day. Otherwise, if the stock in the previous record is equal to 0, it indicates that the replenishment was completed after the stock was out of stock, and the time of this stock out can be calculated. When calculating, first calculate the stockout time on the day of stockout, and then calculate how many business days have passed before restocking. There are 13 hours of stockout from 8:00 to 21:00 on each business day. After the calculation is completed, list the stockout time of each product as follows:
A6 retrieves the longest out of stock product information:
