By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
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
4 rows affected
select * from articles;
| POS | REF_POS | ARTICLE | QUANTITY | MAXQUANTITY |
|---|---|---|---|---|
| 0 | null | prod1 | 3 | 6 |
| 1 | null | prod1 | 3 | 6 |
| 2 | null | prod1 | 8 | 6 |
| 7 | 2 | addon_for_pos2 | 16 | null |
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
| POS | REF_POS | ARTICLE | QUANTITY | MAXQUANTITY | BIN_TAG | BIN_TAG2 | EFFECTIVE_QUANTITY | PREV_QUANTITY | EFFECTIVE_NAME | RATIO | ID |
|---|---|---|---|---|---|---|---|---|---|---|---|
| 0 | null | prod1 | 3 | 6 | 1 | 1 | 3 | 0 | prod1 | 1 | prod1_limit_1 |
| 1 | null | prod1 | 3 | 6 | 1 | 1 | 3 | 0 | prod1 | 1 | prod1_limit_1 |
| 2 | null | prod1 | 8 | 6 | 2 | 1 | 6 | 0 | prod1 | 1 | prod1_limit_2 |
| 2 | null | prod1 | 8 | 6 | 3 | 2 | 2 | 6 | prod1 | 1 | prod1_limit_3 |
| 7 | 2 | addon_for_pos2 | 16 | 12 | 1 | 1 | 12 | 0 | prod1 | 2 | prod1_limit_2 |
| 7 | 2 | addon_for_pos2 | 16 | 12 | 2 | 2 | 4 | 12 | prod1 | 2 | prod1_limit_3 |