Oracle sql split up rows to fill maxquantity

user_16936299 0
Viewed 69

say i have a list of articles, which i want to split to fill maxvalues:

id | name  | quantity | maxquantity
1  | name_a|        3 |           5
2  | name_a|        1 |           5
3  | name_a|        3 |           5
4  | name_a|        5 |           5
5  | name_b|        7 |           4
6  | name_b|        2 |           4

i want to create packages grouped by name, filled up to the maxvalues to get the following results:

id | name  | quantity | maxquantity | tag          | effective_quantity 
1  | name_a|        3 |           5 | name_a_part1 |                  3
2  | name_a|        1 |           5 | name_a_part1 |                  1
3  | name_a|        3 |           5 | name_a_part1 |                  1
                                                                        ^- sum() = maxquantity

3  | name_a|        3 |           5 | name_a_part2 |                  2
4  | name_a|        5 |           5 | name_a_part2 |                  3
                                                                        ^- sum() = maxquantity

4  | name_a|        5 |           5 | name_a_part3 |                  2
                                                                        ^- sum() = maxquantity or the rest of name_a

5  | name_b|        7 |           4 | name_b_part1 |                  4
                                                                        ^- sum() = maxquantity

5  | name_b|        7 |           4 | name_b_part2 |                  3
6  | name_b|        2 |           4 | name_b_part2 |                  1
                                                                        ^- sum() = maxquantity

6  | name_b|        2 |           4 | name_b_part3 |                  1
                                                                        ^- sum() = maxquantity or the rest of name_b
oracle sql
2 Answers

One pretty simple method is to explode the data into a separate row for each item, calculate the bins at that level, and then reaggregate:

with cte (id, name, quantity, maxquantity, n) as (
      select id, name, quantity, maxquantity, 1 as n
      from t
      union all
      select id, name, quantity, maxquantity, n + 1
      from cte
      where n < quantity
     ) 
select id, name, quantity, maxquantity,
       count(*) as number_in_bin,
       ceil(bin_counter / maxquantity) as bin_number
from (select cte.*,
             row_number() over (partition by name order by id, n) as bin_counter
      from cte
     ) cte
group by id, name, quantity, maxquantity, ceil(bin_counter / maxquantity) 
order by id, bin_number;

Here is a db<>fiddle.

user_11440350

You can do it with a single recursive query using analytic functions:

WITH split_bins (id, name, quantity, maxquantity, tag, effective_quantity, prev_quantity) AS (
  SELECT id,
         name,
         quantity,
         maxquantity,
         FLOOR(
           COALESCE(
             SUM(quantity) OVER (
               PARTITION BY name
               ORDER BY id
               ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING
             ),
             0
           )
           / maxquantity
         ) + 1,
         LEAST(
           maxquantity
           - MOD(
               COALESCE(
                 SUM(quantity) OVER (
                   PARTITION BY name
                   ORDER BY id
                   ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING
                 ),
                 0
               ),
               maxquantity
             ),
           quantity
         ),
         0
  FROM   articles
UNION ALL
  SELECT id,
         name,
         quantity,
         maxquantity,
         tag + 1,
         LEAST(
           quantity - prev_quantity - effective_quantity,
           maxquantity
         ),
         prev_quantity + effective_quantity
  FROM   split_bins
  WHERE  prev_quantity + effective_quantity < quantity
)
SEARCH DEPTH FIRST BY id SET id_order
SELECT id,
       name,
       quantity,
       maxquantity,
       name || '_part' || tag AS tag,
       effective_quantity
FROM   split_bins;

Which, for the sample data:

CREATE TABLE articles (id, name, quantity, maxquantity ) AS
SELECT 1, 'name_a', 3, 5 FROM DUAL UNION ALL
SELECT 2, 'name_a', 1, 5 FROM DUAL UNION ALL
SELECT 3, 'name_a', 3, 5 FROM DUAL UNION ALL
SELECT 4, 'name_a', 5, 5 FROM DUAL UNION ALL
SELECT 5, 'name_b', 7, 4 FROM DUAL UNION ALL
SELECT 6, 'name_b', 2, 4 FROM DUAL UNION ALL
SELECT 7, 'name_c', 6, 2 FROM DUAL UNION ALL
SELECT 8, 'name_c', 2, 2 FROM DUAL;

Outputs:

ID NAME QUANTITY MAXQUANTITY TAG EFFECTIVE_QUANTITY
1 name_a 3 5 name_a_part1 3
2 name_a 1 5 name_a_part1 1
3 name_a 3 5 name_a_part1 1
3 name_a 3 5 name_a_part2 2
4 name_a 5 5 name_a_part2 3
4 name_a 5 5 name_a_part3 2
5 name_b 7 4 name_b_part1 4
5 name_b 7 4 name_b_part2 3
6 name_b 2 4 name_b_part2 1
6 name_b 2 4 name_b_part3 1
7 name_c 6 2 name_c_part1 2
7 name_c 6 2 name_c_part2 2
7 name_c 6 2 name_c_part3 2
8 name_c 2 2 name_c_part4 2

db<>fiddle here

user_15092640
Related