this is an extended question to this already answered Thread
say i have a list of articles, which i want to split to fill maxvalues including addon-articles (no. 7), which refer to other positions:
id | ref | name | quantity | maxquantity
1 | null | name_a| 3 | 5
2 | null | name_a| 1 | 5
3 | null | name_a| 3 | 5
4 | null | name_a| 5 | 5
5 | null | name_b| 7 | 4
6 | null | name_b| 2 | 4
7 | 5 | add_1 | 14 | null
i want to create packages grouped by name, filled up to the maxvalues, keeping the reference-relationship and the ratio of referenced-articles to referencing-articles to get the following results:
1 | null | name_a| 3 | 5 | name_a_part1 | 3
2 | null | name_a| 1 | 5 | name_a_part1 | 1
3 | null | name_a| 3 | 5 | name_a_part1 | 1
^- sum() = maxquantity
3 | null | name_a| 3 | 5 | name_a_part2 | 2
4 | null | name_a| 5 | 5 | name_a_part2 | 3
^- sum() = maxquantity
4 | null | name_a| 5 | 5 | name_a_part3 | 2
^- sum() = maxquantity or the rest of name_a
5 | null | name_b| 7 | 4 | name_b_part1 | 4
^- sum() = maxquantity
5 | null | name_b| 7 | 4 | name_b_part2 | 3
6 | null | name_b| 2 | 4 | name_b_part2 | 1
^- sum() = maxquantity
6 | null | name_b| 2 | 4 | name_b_part3 | 1
^- sum() = maxquantity or the rest of name_b
7 | 5 | add_1| 14| null | name_b_part1 | 8
7 | 5 | add_1| 14| null | name_b_part2 | 6
ratio of pos5 to pos7 is 1:2
the name or the number of the final bins should match between referenced-articles and referencing-articles
I managed to get solve this issue.
create the table via
CREATE TABLE articles (pos, ref_pos, article, quantity, maxquantity ) AS
SELECT 0, NULL, 'prod1', 3, 6 FROM DUAL UNION ALL
SELECT 1, NULL, 'prod1', 3, 6 FROM DUAL UNION ALL
SELECT 2, NULL, 'prod1', 8, 6 FROM DUAL UNION ALL
SELECT 7, 2, 'addon_for_pos2', 16, NULL FROM DUAL
and this sql will get the correct Results:
WITH split_bins (pos, ref_pos, article, quantity, maxquantity, bin_tag, bin_tag2, effective_quantity, prev_quantity,effective_name, ratio) AS (
-- ################### the first static iteration
SELECT pos,
ref_pos,
article,
quantity,
-- ################### calculate the max-quantity
COALESCE(
maxquantity, CONNECT_BY_ROOT maxquantity * quantity / CONNECT_BY_ROOT quantity
) AS maxquantity,
-- ################### calculate the bin_tag for grouping
FLOOR(
COALESCE(
SUM(quantity) OVER (
PARTITION BY article
ORDER BY pos
ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING
),
0
)
/ COALESCE(
maxquantity, CONNECT_BY_ROOT maxquantity * quantity / CONNECT_BY_ROOT quantity
)
) + 1 as bin_tag,
-- ################### calculate the bin_tag for grouping supplements to correct bin
FLOOR(
COALESCE(
SUM(quantity) OVER (
PARTITION BY article, pos
ORDER BY pos
ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING
),
0
)
/ COALESCE(
maxquantity, CONNECT_BY_ROOT maxquantity * quantity / CONNECT_BY_ROOT quantity
)
) + 1 as bin_tag2,
-- ################### calculate the effective quantity
LEAST(
COALESCE(
maxquantity, CONNECT_BY_ROOT maxquantity * quantity / CONNECT_BY_ROOT quantity
)
- MOD(
COALESCE(
SUM(quantity) OVER (
PARTITION BY article
ORDER BY pos
ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING
),
0
),
COALESCE(
maxquantity, CONNECT_BY_ROOT maxquantity * quantity / CONNECT_BY_ROOT quantity
)
),
quantity
) AS effective_quantity,
-- ################### previously used quantity (start with zero)
0 AS prev_quantity,
-- ################### propagate the referenced article to the referencing articles
CONNECT_BY_ROOT article AS effective_name,
-- ################### calculate the ratio of main articles and addons (just dev)
quantity / CONNECT_BY_ROOT quantity AS ratio
FROM
articles START WITH ref_pos IS NULL CONNECT BY PRIOR pos = ref_pos
-- ################### the 2nd to n iteration
UNION ALL
--(pos, ref_pos, article, quantity, maxquantity, bin_tag, effective_quantity, prev_quantity,effective_name, ratio)
SELECT pos,
ref_pos,
article,
quantity,
maxquantity,
-- ################### increase the identifier
bin_tag + 1 as bin_tag,
bin_tag2 + 1 as bin_tag2,
-- ################### calculate the current effective_quantity
LEAST(
quantity - prev_quantity - effective_quantity,
maxquantity
) as effective_quantity,
-- ################### calculate the prev_quantity for next iteration
prev_quantity + effective_quantity as prev_quantity,
effective_name,
ratio
FROM split_bins
WHERE prev_quantity + effective_quantity < quantity
)
-- ################### final select data from with-clause
SELECT pos, ref_pos, article, quantity, maxquantity, bin_tag, bin_tag2,effective_quantity, prev_quantity,effective_name, ratio,effective_name||'_limit_'||connect_by_root bin_tag as id
FROM split_bins START WITH ref_pos IS NULL CONNECT BY PRIOR pos = ref_pos and PRIOR bin_tag2=bin_tag2
order by pos, bin_tag;