There are two tables in a certain database. The original inventory table data_add stores multiple batches of inventory for multiple items. Each batch of inventory has a starting number START_NUM and an ending number END_NUM, representing the range of an interval.
The consumption table data_cons stores multiple batches of consumption for multiple items, with each batch of consumption being an interval.
Now we need to calculate the current inventory of each item, which is the difference set between the multi segment range of the original inventory and the multi segment range of consumption, and represent the result as a multi segment range. The interval of the original inventory may be consumed into discontinuous multiple intervals, in which case multiple records need to be naturally generated, with each record corresponding to an interval. For example, the interval of the original inventory [500:1200] is consumed into two intervals [500:849] and [1101:1200].
SQL cannot represent sets with variables, making it inconvenient to perform operations between sets, and the code is very cumbersome. SPL can represent sets with variables, making it easy to express various set operations:
A1-B1： Load data.
A2： Use the group function to group the original inventory by item, but do not aggregate. Convert each interval within the group into a small set of continuous sequence, and then union them into a large set. ~ represents the current group, the function 'to' can generate a continuous sequence based on the start and end numbers.
B2： Apply the same processing to the consumption table.
A3=A2.join(ITEM_ID,B2,b)：Use the join function to perform left join by item ID.
A4=A3.derive([a, b].merge@d()…..) Add a calculated column and first calculate the difference between the original inventory set and the consumption set of each item. The merge function merges ordered sets, @d represents calculating the difference set. Note that the sequence after the difference set is discontinuous, such as 849 and 1101.
group@i(~!=~[-1]+1)Then perform conditional grouping to each difference set, grouping the consecutive sequence into the same group, such as 849 and 1101 being assigned to the second and third groups, respectively. The function group is used for grouping, by default is equivalence grouping, @i represents grouping by condition, and ~[-1] represents the previous member.
A5=A4.news(diff; ITEM_ID, ~1:START_NUM, ~.m(-1):END_NUM)Generate a new record using each sequence in the diff field of each record in A4, where the start and end numbers of the new interval are taken from the beginning and end of each sequence. The function news can expand each member of a set into one record. ~.m(-1) represents the last member in ~, ~.m(1) represents the first member in ~, abbreviated as ~1.
esProcSPL is free,Download~