12 Replies Latest reply on Jun 1, 2011 2:04 PM by 861381

    Calculate using previous column and rows

    861381
      Hello TNO members,

      I have a complicated problem I need to solve, how ever I am missing knowledge about calculating using previous rows and columns in current select.

      Test data
      with t as (
        select 1 as box, 1 as box_group, 10 as max_qty from dual union all
        select 2, 1, 15 from dual union all
        select 3, 1, 40 from dual union all
        select 4, 1, 45 from dual union all
        select 5, 2, 15 from dual union all
        select 6, 2, 20 from dual union all
        select 7, 2, 20 from dual union all
        select 8, 3, 20 from dual)
      Expected Output result
      box box_group max_qty assigned_from_60
      1   1         10      10   
      2   1         15      15
      3   1         40      17
      4   1         45      18
      5   2         15      0
      6   2         20      0
      7   2         20      0
      8   3         20      0
      The problem:
      In total 60 items are shared among the boxes in the same group, ordered by the lowest max_qty.
      10 items can be assign to each box in group 1. (Used items: 40)
      The remaining 20 items will be assigned to the other boxes in group 1.
      5 more items can be assign to each box in group 1 (Used items: 15)
      The remaining 15 items will be assigned to the remaining boxes in group 1.
      2 more items can be assign to each box in group 1 (used items: 4)
      One item remains. When items cannot be shared equally among the remaining boxes, ordered by the highest max_quantity, assign +1 till no item remains.

      My solution in steps:
      1. Calculate max_qty difference. How can I calculate the difference between the max_qty from box 1 and 2? Tricky is not to calculate the difference between different groups.

      This means output result should be something like
      box box_group max_qty qty_dif
      1   1         10      10   
      2   1         15      5
      3   1         40      25
      4   1         45      5
      5   2         15      15
      6   2         20      5
      7   2         20      0
      8   3         20      20
      2. Remaining boxes in the same group. I want to know how many boxes are in the same group. Especially the remaining boxes when the current max_quantity is filled.
      Using the following code does not result in the correct output, what is wrong or missing here?
      count(*) over(partition by box_group order by max_qty asc range between current row and unbounded following) 
      This means output result should be something like
      box box_group max_qty qty_dif rem_boxes
      1   1         10      10      4   
      2   1         15      5       3
      3   1         40      25      2
      4   1         45      5       1
      5   2         15      15      3
      6   2         20      5       2
      7   2         20      0       1
      8   3         20      20      1
      3. Calculate costs. This one is faily easy rem_boxes * qty_dif (*per row*)

      This means output result should be something like
      box box_group max_qty qty_dif rem_boxes cost
      1   1         10      10      4         40 
      2   1         15      5       3         15
      3   1         40      25      2         50
      4   1         45      5       1         5
      5   2         15      15      3         45
      6   2         20      5       2         10
      7   2         20      0       1         0
      8   3         20      20      1         20
      4. Calculate rem_items. 60 - (rem_boxes * qty_dif of box 1) - (rem_boxes * qty_dif of box 2) - (rem_boxes * qty_dif
      of box n). How can I calculate using results of previous rows? (*all, not per group*)

      This means output result should be something like
      box box_group max_qty qty_dif rem_boxes cost rem_items
      1   1         10      10      4         40   20
      2   1         15      5       3         15   5
      3   1         40      25      2         50   -45
      4   1         45      5       1         5    -50
      5   2         15      15      3         45   -95
      6   2         20      5       2         10   -105
      7   2         20      0       1         0    -105
      8   3         20      20      1         20   -125
      5. Assign full quantity. For each row check if rem_items > 0 then 1 else 0

      This means output result should be something like
      box box_group max_qty qty_dif rem_boxes cost rem_items assign
      1   1         10      10      4         40   20        1
      2   1         15      5       3         15   5         1
      3   1         40      25      2         50   -45       0
      4   1         45      5       1         5    -50       0
      5   2         15      15      3         45   -95       0
      6   2         20      5       2         10   -105      0
      7   2         20      0       1         0    -105      0
      8   3         20      20      1         20   -125      0
      6. Calculate assign quantity attemp 1. Calculate assign quantity of remaining boxes per group
      When assign = 1 then max_qty else pervious a_qty (*within same group*)

      This means output result should be something like
      box box_group max_qty qty_dif rem_boxes cost rem_items assign a_qty
      1   1         10      10      4         40   20        1       10
      2   1         15      5       3         15   5         1       15
      3   1         40      25      2         50   -45       0       15
      4   1         45      5       1         5    -50       0       15
      5   2         15      15      3         45   -95       0       0
      6   2         20      5       2         10   -105      0       0
      7   2         20      0       1         0    -105      0       0
      8   3         20      20      1         20   -125      0       0
      How to solve the rest, I do not know yet. Any other suggestion to solve this problem, is welcome.

      Since I'm not really a professional this is what I tried till now
      with z as (
        select 1 as box, 1 as box_group, 10 as max_qty from dual union all
        select 2, 1, 15 from dual union all
        select 3, 1, 40 from dual union all
        select 4, 1, 45 from dual union all
        select 5, 2, 15 from dual union all
        select 6, 2, 20 from dual union all
        select 7, 2, 20 from dual union all
        select 8, 3, 20 from dual)
      
      select u.*,
             case 
               when u.assign = 2 then u.max_qty
               when u.assign = 1 then 0
               when u.assign = 0 then 0
             end as assigned_qty
      from
        (
          select v.*,
                 case
                   when 60 - sum(v.max_qty) over (order by v.box_group, v.max_qty, v.box) >= 0
                     and v.rem_items_before >= 0 then 2
                   when 60 - sum(v.max_qty) over (order by v.box_group, v.max_qty, v.box) < 0
                     and v.rem_items_before > 0 then 1 else 0
                 end as assign
          from
            (
              select w.*,
                     w.rem_items_after + w.max_qty as rem_items_before
                     
              from
                (
                  select x.*,
                         60 - x.qty_assigned as rem_items_after
                         
                  from   
                    (
                      select y.*,
                             y.max_qty * y.rem_boxes as total_cost,
                             sum(y.max_qty) over (order by y.box_group, y.max_qty, y.box) as qty_assigned
                      from  
                        (
                          select z.*,
                                 count(*) over (partition by z.box_group order by z.max_qty, z.box asc range between current row and unbounded following) as rem_boxes
                          from z
                        ) y
                    ) x
                ) w
            ) v
        ) u
      Kind regards,

      Metro

      Edited by: 858378 on 30-mei-2011 4:39

      Edited by: 858378 on 30-mei-2011 5:05
        • 1. Re: Calculate using previous column and rows
          Frank Kulash
          Hi,

          Thanks for inlcuding the sample data in a useful form.
          Don't forget to say which version of Oracle you're using. The solution below works in Oracle 9 (and up), but there might be a simpler and/or more efficient solution using MODEL (introduced in Oracle 10) or recursive WITH clauses (new in Oracle 11.2).

          Let's make sure I understand the problem.
          You have a given number if items (:total_items = 60 in the example you gave), and you want to distribute them over the rows in a given box_group (:target_box_group=1 in this example) as evenly as possible, such that no box is assigned more than its max_qty.
          Rows that do not have box_group = :target_box_group play no real role in this problem, but they have to be included in the output.
          Is that right?

          If so, here's one way:
          VARIABLE  target_box_group     NUMBER
          VARIABLE  total_items          NUMBER
          
          EXEC  :target_box_group :=  1;
          EXEC  :total_items      := 60;
          
               
          WITH     cntr     AS
          (
               SELECT     LEVEL     AS n
               FROM     (  SELECT  MAX (max_qty)     AS max_max_qty
                       FROM    t
                       WHERE   box_group     = :target_box_group
                    )
               CONNECT BY  LEVEL  <= max_max_qty
          )
          ,     got_r_num     AS
          (
               SELECT     t.box
               ,     c.n
               ,     ROW_NUMBER () OVER ( ORDER BY  c.n
                                   ,         t.max_qty     DESC
                                 )     AS r_num
               FROM     cntr     c
               JOIN     t          ON c.n     <= t.max_qty
               WHERE     t.box_group     = :target_box_group
          )
          ,     got_assigned     AS
          (
               SELECT       box
               ,       COUNT (*)     AS assigned
               FROM       got_r_num
               WHERE       r_num     <= :total_items
               GROUP BY  box
          )
          SELECT     t.*
          ,     NVL (a.assigned, 0)     AS assigned
          FROM          t
          LEFT OUTER JOIN     got_assigned     a  ON     t.box     = a.box
          ORDER BY     t.box_group
          ,          t.max_qty
          ;
          What results would you want if :total_items is greater than the sum of all the max_qtys in the given box_group? The query above would leave some items unassigned.
          1 person found this helpful
          • 2. Re: Calculate using previous column and rows
            861381
            Thanks,

            I am using version 10g.

            The number of items is parameter.
            The box group is not a parameter.

            You are correct about the number of items, which is an input parameter. However, the box group number is not an input parameter.
            I need to excercise with partitioning, this is one of the goals of the excercise.

            A number of items is given, meaning that it does not matter whether I have 60, 100 or 1000 items. The boxes with the lowest group number are filled with items first.
            A box cannot exceed its maximum quantity. All items need to be distributed evenly among the boxes in the same group.
            I think you understand the main problem correctly.

            When total items > sum(max_qty) of group, then do the same for the next group.

            I have another silly question
            with t as (
            select 1 as box from dual union all
            select 2 from dual union all
            select 3 from dual union all
            select 4 from dual union all
            select 5 from dual union all
            select 6 from dual union all
            select 7 from dual union all
            select 8 from dual 
            ) 
            
            select v.*
            from
            (
            select t.box, sum(t.box) over (order by t.box) as summed
            from t
            where t.box <> 4
            order by t.box) v
            I still want to show box 4. With result 6, because 1+2+3 = 6. How can I do this?

            Kind regards,

            Metro

            Edited by: 858378 on 30-mei-2011 7:46
            • 3. Re: Calculate using previous column and rows
              Frank Kulash
              858378 wrote:
              Thanks,

              I am using version 10g.

              The number of items is parameter.
              The box group is not a parameter.

              You are correct about the number of items, which is an input parameter. However, the box group number is not an input parameter.
              ...
              A number of items is given, meaning that it does not matter whether I have 60, 100 or 1000 items. The boxes with the lowest group number are filled with items first.
              ... All items need to be distributed evenly among the boxes in the same group.
              So you want to fill the lowest box_group first, as evenly as possible.
              If the lowest box_group isn't big enough, then you want to fill it completely, and try to put the rest into the next box_group. Is that it?
              If so, you just have to remove the WHERE clauses that reference a particular box_group, and add box-group to the analytic ORDER BY clause:
              WITH     cntr     AS
              (
                   SELECT     LEVEL     AS n
                   FROM     (  SELECT  MAX (max_qty)     AS max_max_qty
                           FROM    t
                           WHERE   box_group     = :target_box_group
                        )
                   CONNECT BY  LEVEL  <= max_max_qty
              )
              ,     got_r_num     AS
              (
                   SELECT     t.box
                   ,     c.n
                   ,     ROW_NUMBER () OVER ( ORDER BY  c.n
                                       ,         t.max_qty     DESC
                                     )     AS r_num
                   FROM     cntr     c
                   JOIN     t          ON c.n     <= t.max_qty
                   WHERE     t.box_group     = :target_box_group
              )
              ,     got_assigned     AS
              (
                   SELECT       box
                   ,       COUNT (*)     AS assigned
                   FROM       got_r_num
                   WHERE       r_num     <= :total_items
                   GROUP BY  box
              )
              SELECT     t.*,     NVL (a.assigned, 0)     AS assigned
              FROM          t
              LEFT OUTER JOIN     got_assigned     a  ON     t.box     = a.box
              ORDER BY     t.box_group
              ,          t.max_qty
              ;
              I need to excercise with partitioning, this is one of the goals of the excercise.
              Are you saying this is part of a homework assignment? Post the complete assignment, and give some context about what you covered in the most recent unit before the assignment was given.

              This doesn't seem to be a good problem for partitioning. PARTITION BY, in analytic fucntions, is used when you want to do the same thing to different groups independently. That's not the case here. How you fill box_group=n is dependent on whether or not you filled box_groups 1, 2, 3, ..., n-1. Part of learning about a technique (such as PARTITION BY) is learning when to use it. The lessone here is that this is not an appropriate place to use that technique.
              I have another silly question
              If it's a separate question, even if it uses the same table, then it's better to start a separate thread.
              with t as (
              select 1 as box from dual union all
              select 2 from dual union all
              select 3 from dual union all
              select 4 from dual union all
              select 5 from dual union all
              select 6 from dual union all
              select 7 from dual union all
              select 8 from dual 
              ) 
              
              select v.*
              from
              (
              select t.box, sum(t.box) over (order by t.box) as summed
              from t
              where t.box <> 4
              order by t.box) v
              I still want to show box 4. With result 6, because 1+2+3 = 6. How can I do this?
              Do you want to the rows where box < 4? How about the rows with box > 4? What will the summed column contain for them? Always post the results you want.
              Perhaps you want something like this:
              SELECT     t.*
              ,     SUM (box) OVER ( ORDER BY      box
                              ROWS BETWEEN  UNBOUNDED PRECEDING
                                   AND      1      PRECEDING
                               )     AS summed
              FROM    t;
              Edited by: Frank Kulash on May 31, 2011 9:44 AM
              Sorry! I posted a duplicate of my original query. See my next message below for the revised query.
              1 person found this helpful
              • 4. Re: Calculate using previous column and rows
                861381
                Hello, thanks for your help so far.

                The course I am in, teaches you how to use the basic pl sql language such are selecting from tables.
                I have learned things about
                - SELECT
                - FROM
                - WHERE
                - GROUP BY
                - ORDER BY
                - SUB SELECTION IN SELECT
                - SUB SELECTION IN FROM
                - SUM, COUNT, MIN, MAX
                - CASES
                - INNER, OUTER, LEFT, FULL, CROSS JOINS

                We are now at partitioning.

                It's written in Dutch, I tried to translate it, so it might not be well written English.
                *Excercise 192*
                Distribution center 'One by one' wants to automate the distribution process.
                ...
                Items are distributed to the boxes one by one. 
                It is similar to the next excercise, but this above was a lot more straight to the point.
                *Excercise 193*
                
                Distribution center 'All in one box' wants to automate the distribution process. 
                One of the major changes in this process is to distribute items equally to all boxes in the same group. 
                This means starting from the lowest quantity, assign the lowest quantity to all boxes in the same group if possible. 
                If this is not possible distribute the amount of items divided by the number of boxes in the same group. If the amount of items per box is lower than 1 and not 0.
                Divide the remaining items per box ordered by the box with the highest quantity, till there are no items left. 
                When it is possible to distribute the lowest quantity to all boxes, move up to the next box in the same group. 
                When all boxes in the same group are filled to their maximum quantity, move up to the next group and repeat this process.
                The following information is available
                box box_group max_qty 
                1   1         10        
                2   2         15      
                3   1         40      
                4   1         45      
                5   1         15      
                6   3         20      
                7   2         20      
                8   2         20      
                A) Order the following information by box_group and max_qty as described.
                B) Calculate the distribution results for 60, 120, 170 items.

                When I have 60 items, the following output result should be
                box box_group max_qty assigned_from_60
                1   1         10      10   
                2   1         15      15
                3   1         40      17
                4   1         45      18
                5   2         15      0
                6   2         20      0
                7   2         20      0
                8   3         20      0
                Box 1 and 2 can be filled completely as you can fill atleast 10 to box 1,2,3,4 and an additional 5 to box 2, 3 and 4.
                The last item goes to the box 4.

                when I have 120 items
                box box_group max_qty assigned_from_120
                1   1         10      10   
                2   1         15      15
                3   1         40      40
                4   1         45      45
                5   2         15      3
                6   2         20      3
                7   2         20      4
                8   3         20      0
                when I have 170 items
                box box_group max_qty assigned_from_170
                1   1         10      10   
                2   1         15      15
                3   1         40      40
                4   1         45      45
                5   2         15      15
                6   2         20      20
                7   2         20      20
                8   3         20      5
                C) Sum the maximum quantities per group
                D) Get the amount of boxes in each group
                E) Create a column for remaining boxes per group
                F) For each distinct quantity, count the amount of boxes with this quantity
                G) Calculate how many items are required to fill all boxes in the same group
                H) Create a plan how to solve this distribution problem described in the introduction?
                I) Solve this problem using your plan.

                Is this problem solvable? I still haven't figured out how to do it.


                About the sum question, I created a new thread and it is solved. But thanks for your help.

                Kind regards,

                Metro

                Edited by: 858378 on 31-mei-2011 1:37
                • 5. Re: Calculate using previous column and rows
                  Aketi Jyuuzou
                  create table testT(box,box_group,max_qty) as
                  select 1,1,10 from dual union all
                  select 2,1,15 from dual union all
                  select 3,1,40 from dual union all
                  select 4,1,45 from dual union all
                  select 5,2,15 from dual union all
                  select 6,2,20 from dual union all
                  select 7,2,20 from dual union all
                  select 8,3,20 from dual;

                  Sometimes,SQL is not effective to solve business problems.
                  Therefore I recommend to use TableFunction.
                  create or replace type Print347Type as object(
                  box              number(2),
                  box_group        number(2),
                  max_qty          number(2),
                  assigned_from_60 number(2));
                  /
                  
                  create or replace type Print347TypeSet as table of Print347Type;
                  /
                  
                  create or replace function PrintR return Print347TypeSet PipeLined IS
                      outR Print347Type := Print347Type(NULL,NULL,NULL,NULL);
                      cursor cur is select box,box_group,max_qty,0 as assigned_from_60
                                    from testT order by max_qty desc;
                      type saveDataDef is table of cur%rowType index by binary_integer;
                      saveData saveDataDef;
                  
                      assignValue pls_Integer :=60;
                  begin
                      open cur;
                      fetch cur bulk collect into saveData;
                      close cur;
                  
                      while (assignValue > 0) loop
                          for I in 1..saveData.Last Loop
                              if saveData(I).box_group = 1 then
                                  if saveData(I).assigned_from_60 < saveData(I).max_qty then
                                      saveData(I).assigned_from_60 := saveData(I).assigned_from_60+1;
                                      assignValue := assignValue-1;
                                      exit when not (assignValue > 0);
                                  end if;
                              end if;
                          end Loop;
                      end Loop;
                  
                      for I in 1..saveData.Last Loop
                          outR.box              := saveData(I).box;
                          outR.box_group        := saveData(I).box_group;
                          outR.max_qty          := saveData(I).max_qty;
                          outR.assigned_from_60 := saveData(I).assigned_from_60;
                          pipe row(outR);
                      end Loop;
                  end;
                  /
                  
                  select * from table(PrintR)
                  order by box_group,box;
                  
                  BOX  BOX_GROUP  MAX_QTY  ASSIGNED_FROM_60
                  ---  ---------  -------  ----------------
                    1          1       10                10
                    2          1       15                15
                    3          1       40                17
                    4          1       45                18
                    5          2       15                 0
                    6          2       20                 0
                    7          2       20                 0
                    8          3       20                 0
                  1 person found this helpful
                  • 6. Re: Calculate using previous column and rows
                    Frank Kulash
                    Hi, Metro,
                    858378 wrote:
                    Hello, thanks for your help so far.

                    The course I am in, teaches you how to use the basic pl sql language such are selecting from tables.
                    Is it about PL/SQL or SQL?
                    I have learned things about
                    - SELECT
                    - FROM
                    - WHERE
                    - GROUP BY
                    - ORDER BY
                    - SUB SELECTION IN SELECT
                    - SUB SELECTION IN FROM
                    - SUM, COUNT, MIN, MAX
                    - CASES
                    - INNER, OUTER, LEFT, FULL, CROSS JOINS
                    All of these are parts of the SQL language, not PL/SQL.
                    We are now at partitioning.
                    Are you specifically at partitioning, or iare you at a point where the book talks about analytic functions, which sometimes, but not always, have a PARTTION BY clause?
                    It's written in Dutch, I tried to translate it, so it might not be well written English.

                    Excercise 192
                    Distribution center 'One by one' wants to automate the distribution process.
                    ...
                    Items are distributed to the boxes one by one.
                    Sorry, I can't figure out what Exercise 192 is, based on just that.
                    It is similar to the next excercise, but this above was a lot more straight to the point.
                    *Excercise 193*
                    
                    Distribution center 'All in one box' wants to automate the distribution process. 
                    One of the major changes in this process is to distribute items equally to all boxes in the same group. 
                    This means starting from the lowest quantity, assign the lowest quantity to all boxes in the same group if possible. 
                    If this is not possible distribute the amount of items divided by the number of boxes in the same group. If the amount of items per box is lower than 1 and not 0.
                    Divide the remaining items per box ordered by the box with the highest quantity, till there are no items left. 
                    When it is possible to distribute the lowest quantity to all boxes, move up to the next box in the same group. 
                    When all boxes in the same group are filled to their maximum quantity, move up to the next group and repeat this process. ...
                    So Exercise 193 is what you asked yesterday, right?
                    A) Order the following information by box_group and max_qty as described.
                    B) Calculate the distribution results for 60, 120, 170 items.

                    When I have 60 items, the following output result should be
                    box box_group max_qty assigned_from_60
                    1   1         10      10   
                    2   1         15      15
                    3   1         40      17
                    4   1         45      18
                    5   2         15      0
                    6   2         20      0
                    7   2         20      0
                    8   3         20      0
                    Box 1 and 2 can be filled completely as you can fill atleast 10 to box 1,2,3,4 and an additional 5 to box 2, 3 and 4.
                    The last item goes to the box 4.

                    when I have 120 items
                    box box_group max_qty assigned_from_120
                    1   1         10      10   
                    2   1         15      15
                    3   1         40      40
                    4   1         45      45
                    5   2         15      3
                    6   2         20      3
                    7   2         20      4
                    8   3         20      0
                    Based on what you posted, it seems like the following should be equally acceptable:
                    box box_group max_qty assigned_from_120
                    1   1         10      10   
                    2   1         15      15
                    3   1         40      40
                    4   1         45      45
                    5   2         15      3
                    6   2         20      4
                    7   2         20      3
                    8   3         20      0
                    That is, the last 10 items have to be distributed among the 3 boxes in box_group=2 as equally as possible. So one box will get 4 items and the others will get 3. The extra item will go to the box with the highest max_qty, but in this case, there is a tie: box 6 has just as much of a claim to having the highest max_qty as box 7. The line marked "***** Add if needed *****" in the query blow guarantees that, in case of a tie like this, the box with the higher box value will be considered "larger" than another box with the same max_qty.
                    when I have 170 items
                    box box_group max_qty assigned_from_170
                    1   1         10      10   
                    2   1         15      15
                    3   1         40      40
                    4   1         45      45
                    5   2         15      15
                    6   2         20      20
                    7   2         20      20
                    8   3         20      5
                    I accidentally posted the wrong query yesterday. This is what I should have posted:
                     WITH     cntr     AS
                    (
                         SELECT     LEVEL     AS n
                         FROM     (  SELECT  MAX (max_qty)     AS max_max_qty
                                 FROM    z
                    --             WHERE   box_group     = :target_box_group          -- *****  Removed  *****
                              )
                         CONNECT BY  LEVEL  <= max_max_qty
                    )
                    ,     got_r_num     AS
                    (
                         SELECT     z.box
                         ,     c.n
                         ,     ROW_NUMBER () OVER ( ORDER BY  box_group          -- *****  Added  *****
                                                   ,            c.n
                                             ,         z.max_qty     DESC
                                             ,            box          DESC     -- ***** Add if needed  *****
                                           )     AS r_num
                         FROM     cntr     c
                         JOIN     z          ON c.n     <= z.max_qty
                    --     WHERE     z.box_group     = :target_box_group               -- *****  Removed  *****
                    )
                    ,     got_assigned     AS
                    (
                         SELECT       box
                         ,       COUNT (*)     AS assigned
                         FROM       got_r_num
                         WHERE       r_num     <= :total_items
                         GROUP BY  box
                    )
                    SELECT     z.*,     NVL (a.assigned, 0)     AS assigned
                    FROM          z
                    LEFT OUTER JOIN     got_assigned     a  ON     z.box     = a.box
                    ORDER BY     z.box_group
                    ,          z.max_qty
                    ;
                    Yesterday, I described how you need to remove 2 lines and add 1, but the code I posted was the unchanged query. The query above is what I should have posted then. Look for comments beginning "*****" above for the changes. I apologize for my mistake.
                    This query gets the results you posted for all 3 values of :total_items that you posted. If it doesn't work for some other value, or some other data, post the new values and the correct results you want from them, and point out where the query above is wrong.
                    C) Sum the maximum quantities per group
                    D) Get the amount of boxes in each group
                    E) Create a column for remaining boxes per group
                    F) For each distinct quantity, count the amount of boxes with this quantity
                    G) Calculate how many items are required to fill all boxes in the same group
                    H) Create a plan how to solve this distribution problem described in the introduction?
                    I) Solve this problem using your plan.
                    Are these the steps that the book suggests using?
                    I don't understand that approach. It might be a good way to solve the problem without using a computer. It might be a good way to solve the problem using a procedural language, such as PL/SQL. It might be one way of solving the problem in SQL, but I think it will be more complicated and less efficient than what I psoted.
                    The approach above is iterative; that is, you repeat certain steps, with different values. For example, you distribute a certain number of items to all boxes in a box_group. The you remove the smallest box(es) from the group, and repeat, distributing the remaining items among the remianing boxes. That's not hard to do in a language like PL/SQL, where you have loops and variables. In SQL, the closest thing to that is the MODEL clause. I'm sure you could write a MODEL solution to this problem, but, if your book hasn't mentioned MODEL yet, then that's certainly not what it's expecting you to do.

                    Even using the approach in steps A) trhough G) above, I don't see how a PARTITION BY would help.
                    • 7. Re: Calculate using previous column and rows
                      Aketi Jyuuzou
                      There are part2 which can take parameter.
                      create or replace type Print347Type_part2 as object(
                      box             number(2),
                      box_group       number(2),
                      max_qty         number(2),
                      assigned_fromXX number(2));
                      /
                      
                      create or replace type Print347TypeSet_part2 as table of Print347Type_part2;
                      /
                      
                      create or replace function PrintR_part2(hiki_assignValue number)
                      return Print347TypeSet_part2 PipeLined IS
                          outR Print347Type_part2 := Print347Type_part2(NULL,NULL,NULL,NULL);
                          cursor cur is select box,box_group,max_qty,0 as assigned_fromXX,
                                        max(box_group) over() as maxBoxGroup
                                        from testT order by box_group,max_qty desc;
                          type saveDataDef is table of cur%rowType index by binary_integer;
                          SD saveDataDef;
                      
                          TargetBoxGroup  number :=1;
                          IsAssigned      boolean;
                          assignValue     number:=hiki_assignValue;
                      begin
                          open cur;
                          fetch cur bulk collect into SD;
                          close cur;
                      
                          while (assignValue > 0) loop
                              IsAssigned := false;
                              for I in 1..SD.Last Loop
                                  if SD(I).box_group = TargetBoxGroup then
                                      if SD(I).assigned_fromXX < SD(I).max_qty then
                                          SD(I).assigned_fromXX := SD(I).assigned_fromXX+1;
                                          assignValue := assignValue-1;
                                          exit when not (assignValue > 0);
                                          IsAssigned := true;
                                      end if;
                                  end if;
                              end Loop;
                              if IsAssigned=false then TargetBoxGroup:= TargetBoxGroup+1; end if;
                              exit when SD(1).maxBoxGroup < TargetBoxGroup;
                          end Loop;
                      
                          for I in 1..SD.Last Loop
                              outR.box             := SD(I).box;
                              outR.box_group       := SD(I).box_group;
                              outR.max_qty         := SD(I).max_qty;
                              outR.assigned_fromXX := SD(I).assigned_fromXX;
                              pipe row(outR);
                          end Loop;
                      end;
                      / 
                      
                      sho err
                      
                      select * from table(PrintR_part2(120))
                      order by box_group,box;
                      
                      BOX  BOX_GROUP  MAX_QTY  ASSIGNED_FROMXX
                      ---  ---------  -------  ---------------
                        1          1       10               10
                        2          1       15               15
                        3          1       40               40
                        4          1       45               45
                        5          2       15                3
                        6          2       20                4
                        7          2       20                3
                        8          3       20                0
                      
                      select * from table(PrintR_part2(170))
                      order by box_group,box;
                      
                      BOX  BOX_GROUP  MAX_QTY  ASSIGNED_FROMXX
                      ---  ---------  -------  ---------------
                        1          1       10               10
                        2          1       15               15
                        3          1       40               40
                        4          1       45               45
                        5          2       15               15
                        6          2       20               20
                        7          2       20               20
                        8          3       20                5
                      
                      select * from table(PrintR_part2(999))
                      order by box_group,box;
                      
                      BOX  BOX_GROUP  MAX_QTY  ASSIGNED_FROMXX
                      ---  ---------  -------  ---------------
                        1          1       10               10
                        2          1       15               15
                        3          1       40               40
                        4          1       45               45
                        5          2       15               15
                        6          2       20               20
                        7          2       20               20
                        8          3       20               20
                      • 8. Re: Calculate using previous column and rows
                        861381
                        Actually it's not a book. It's excercises from college.

                        I think the teacher just created those herself. The teacher mentioned about partitioning as the excercise is under the topic, partitioning.
                        Excercise 192 is excercise 193 in an easier form, because you fill the boxes one by one and not sharing equally.
                        My teacher told me, that indeed a model or table function would solve the problem. So both great job :D

                        How ever, this could be done with just using some queries, she says.
                        And I believe XD
                        with z as (
                          select 1 as box, 1 as box_group, 10 as max_qty from dual union all
                          select 2, 1, 15 from dual union all
                          select 3, 1, 40 from dual union all
                          select 4, 1, 45 from dual union all
                          select 5, 2, 15 from dual union all
                          select 6, 2, 20 from dual union all
                          select 7, 2, 20 from dual union all
                          select 8, 3, 20 from dual)
                        
                        select x.*,
                               case when x.state = 2 then x.max_qty
                                    when x.state = 1 then floor(x.rem_qty/x.rem_boxes)
                                    else 0
                               end as assign
                        from  (select y.*,
                                      count(case when y.dis_qty_after <= 60 then 1 else 0 end) over (partition by y.box_group order by y.max_qty, y.box asc range between current row and unbounded following) as rem_boxes,
                                      60 - sum(case when y.dis_qty_after <= 60 then y.max_qty else 0 end) over (order by y.box, y.box_group, y.max_qty) as rem_qty,
                                      min(y.dis_qty_before) over (partition by y.box_group) as min_qty_group,
                                      max(y.dis_qty_after) over (partition by y.box_group) as max_qty_group,
                                      case 
                                        when y.dis_qty_after <= 60 then 2
                                        else 
                                          case
                                            when min(y.dis_qty_before) over (partition by y.box_group) < 60
                                            and  max(y.dis_qty_after) over (partition by y.box_group) >= 60
                                            then 1 
                                            else 0 
                                          end
                                      end as state    
                               from  (select z.*,
                                      sum(z.max_qty) over (order by z.box, z.box_group, z.max_qty) - z.max_qty as dis_qty_before,
                                      sum(z.max_qty) over (order by z.box, z.box_group, z.max_qty) - z.max_qty + z.max_qty * count(*) over (partition by z.box_group order by z.max_qty, z.box asc range between current row and unbounded following) as dis_qty_after
                                      from   z
                                     ) y
                              ) x
                        This is how much I have. I'm failing at calculating row numbers remaining with a count condition. Partitioning can be used to calculate for certain columns. But indeed, I think partition has no major influence in this excercise.
                        • 9. Re: Calculate using previous column and rows
                          Frank Kulash
                          Hi,
                          858378 wrote:
                          ... How ever, this could be done with just using some queries, she says.
                          I agree. In my last message, I posted a query that does it, without using PL/SQL or MODEL.
                          And I believe XD
                          I'm sorry, I don't understand. What does "XD" mean here?
                          >
                          with z as (
                          select 1 as box, 1 as box_group, 10 as max_qty from dual union all
                          select 2, 1, 15 from dual union all
                          select 3, 1, 40 from dual union all
                          select 4, 1, 45 from dual union all
                          select 5, 2, 15 from dual union all
                          select 6, 2, 20 from dual union all
                          select 7, 2, 20 from dual union all
                          select 8, 3, 20 from dual)
                          
                          select x.*,
                          case when x.state = 2 then x.max_qty
                          when x.state = 1 then floor(x.rem_qty/x.rem_boxes)
                          else 0
                          end as assign
                          from  (select y.*,
                          count(case when y.dis_qty_after <= 60 then 1 else 0 end) over (partition by y.box_group order by y.max_qty, y.box asc range between current row and unbounded following) as rem_boxes,
                          60 - sum(case when y.dis_qty_after <= 60 then y.max_qty else 0 end) over (order by y.box, y.box_group, y.max_qty) as rem_qty,
                          min(y.dis_qty_before) over (partition by y.box_group) as min_qty_group,
                          max(y.dis_qty_after) over (partition by y.box_group) as max_qty_group,
                          case 
                          when y.dis_qty_after <= 60 then 2
                          else 
                          case
                          when min(y.dis_qty_before) over (partition by y.box_group) < 60
                          and  max(y.dis_qty_after) over (partition by y.box_group) >= 60
                          then 1 
                          else 0 
                          end
                          end as state    
                          from  (select z.*,
                          sum(z.max_qty) over (order by z.box, z.box_group, z.max_qty) - z.max_qty as dis_qty_before,
                          sum(z.max_qty) over (order by z.box, z.box_group, z.max_qty) - z.max_qty + z.max_qty * count(*) over (partition by z.box_group order by z.max_qty, z.box asc range between current row and unbounded following) as dis_qty_after
                          from   z
                          ) y
                          ) x
                          This is how much I have. I'm failing at calculating row numbers remaining with a count condition. Partitioning can be used to calculate for certain columns. But indeed, I think partition has no major influence in this excercise.
                          Whenever you have a problem, post the sample data (as you did), the results you want from that data, and an explanation of how you get those results from that data. I'm sure you have a clear idea of what this much of the query is supposed to do, but, to me, it is a riddle, wrapped in a mystery, inside an enigma.
                          • 10. Re: Calculate using previous column and rows
                            861381
                            Thanks for helping :) 'XD' is a smiley.

                            I got the solution now. I took some time, and some input of you :D.

                            I agree, I did not describe well enough, but sometimes it is really hard to explain what you really need.
                            I will try to do better next time.
                            with z as (
                              select 1 as box, 1 as box_group, 10 as max_qty from dual union all
                              select 2, 1, 15 from dual union all
                              select 3, 1, 40 from dual union all
                              select 4, 1, 45 from dual union all
                              select 6, 2, 15 from dual union all
                              select 7, 2, 20 from dual union all
                              select 8, 2, 20 from dual union all
                              select 9, 3, 20 from dual)
                            
                            select x.*,
                                   case when x.state = 2 then x.max_qty
                                        when x.state = 1 then 
                                                           case when floor(x.rem_qty/x.sum_i_boxes) * x.sum_i_boxes <> x.rem_qty 
                                                             then floor(x.rem_qty/x.sum_i_boxes) + case 
                                                                                                   when x.rem_boxes <= x.rem_qty - floor(x.rem_qty/x.sum_i_boxes) * x.sum_i_boxes then 1 else 0 end
                                                             else floor(x.rem_qty/x.sum_i_boxes)
                                                           end
                                        else 0
                                   end as assign
                                   from  (select y.*,
                                                 sum(y.in_complete_boxes) over (partition by y.box_group) as sum_i_boxes,
                                                 163 - sum(case when y.dis_qty_after <= 163 then y.max_qty else 0 end) over (order by y.box, y.box_group, y.max_qty) as rem_qty,
                                                 min(y.dis_qty_before) over (partition by y.box_group) as min_qty_group,
                                                 max(y.dis_qty_after) over (partition by y.box_group) as max_qty_group,
                                                 case 
                                                   when y.dis_qty_after <= 163 then 2
                                                   else case
                                                          when min(y.dis_qty_before) over (partition by y.box_group) < 163
                                                          and  max(y.dis_qty_after) over (partition by y.box_group) >= 163
                                                          then 1 
                                                          else 0 
                                                        end
                                                 end as state
                                          from  (select z.*,
                                                        sum(z.max_qty) over (order by z.box, z.box_group, z.max_qty) - z.max_qty as dis_qty_before,
                                                        sum(z.max_qty) over (order by z.box, z.box_group, z.max_qty) - z.max_qty + z.max_qty * count(*) over (partition by z.box_group order by z.max_qty, z.box asc range between current row and unbounded following) as dis_qty_after,
                                                        count(*) over (partition by z.box_group order by z.max_qty, z.box asc range between current row and unbounded following) as rem_boxes,
                                                        case when sum(z.max_qty) over (order by z.box, z.box_group, z.max_qty) - z.max_qty + z.max_qty * count(*) over (partition by z.box_group order by z.max_qty, z.box asc range between current row and unbounded following) > 163 then 1 else 0 end as in_complete_boxes
                                                 from   z
                                         ) y
                                  ) x
                            • 11. Re: Calculate using previous column and rows
                              861381
                              Thanks for helping :) 'XD' is a smiley.

                              I got the solution now. I took some time, and some input of you :D.

                              I agree, I did not describe well enough, but sometimes it is really hard to explain what you really need.
                              I will try to do better next time.
                              with z as (
                                select 1 as box, 1 as box_group, 10 as max_qty from dual union all
                                select 2, 1, 15 from dual union all
                                select 3, 1, 40 from dual union all
                                select 4, 1, 45 from dual union all
                                select 6, 2, 15 from dual union all
                                select 7, 2, 20 from dual union all
                                select 8, 2, 20 from dual union all
                                select 9, 3, 20 from dual)
                              
                              select x.*,
                                     case when x.state = 2 then x.max_qty
                                          when x.state = 1 then 
                                                             case when floor(x.rem_qty/x.sum_i_boxes) * x.sum_i_boxes <> x.rem_qty 
                                                               then floor(x.rem_qty/x.sum_i_boxes) + case 
                                                                                                     when x.rem_boxes <= x.rem_qty - floor(x.rem_qty/x.sum_i_boxes) * x.sum_i_boxes then 1 else 0 end
                                                               else floor(x.rem_qty/x.sum_i_boxes)
                                                             end
                                          else 0
                                     end as assign
                                     from  (select y.*,
                                                   sum(y.in_complete_boxes) over (partition by y.box_group) as sum_i_boxes,
                                                   163 - sum(case when y.dis_qty_after <= 163 then y.max_qty else 0 end) over (order by y.box, y.box_group, y.max_qty) as rem_qty,
                                                   min(y.dis_qty_before) over (partition by y.box_group) as min_qty_group,
                                                   max(y.dis_qty_after) over (partition by y.box_group) as max_qty_group,
                                                   case 
                                                     when y.dis_qty_after <= 163 then 2
                                                     else case
                                                            when min(y.dis_qty_before) over (partition by y.box_group) < 163
                                                            and  max(y.dis_qty_after) over (partition by y.box_group) >= 163
                                                            then 1 
                                                            else 0 
                                                          end
                                                   end as state
                                            from  (select z.*,
                                                          sum(z.max_qty) over (order by z.box, z.box_group, z.max_qty) - z.max_qty as dis_qty_before,
                                                          sum(z.max_qty) over (order by z.box, z.box_group, z.max_qty) - z.max_qty + z.max_qty * count(*) over (partition by z.box_group order by z.max_qty, z.box asc range between current row and unbounded following) as dis_qty_after,
                                                          count(*) over (partition by z.box_group order by z.max_qty, z.box asc range between current row and unbounded following) as rem_boxes,
                                                          case when sum(z.max_qty) over (order by z.box, z.box_group, z.max_qty) - z.max_qty + z.max_qty * count(*) over (partition by z.box_group order by z.max_qty, z.box asc range between current row and unbounded following) > 163 then 1 else 0 end as in_complete_boxes
                                                   from   z
                                           ) y
                                    ) x
                              • 12. Re: Calculate using previous column and rows
                                861381
                                Thanks for helping :) 'XD' is a smiley.

                                I got the solution now. I took some time, and some input of you :D.

                                I agree, I did not describe well enough, but sometimes it is really hard to explain what you really need.
                                I will try to do better next time.
                                with z as (
                                  select 1 as box, 1 as box_group, 10 as max_qty from dual union all
                                  select 2, 1, 15 from dual union all
                                  select 3, 1, 40 from dual union all
                                  select 4, 1, 45 from dual union all
                                  select 6, 2, 15 from dual union all
                                  select 7, 2, 20 from dual union all
                                  select 8, 2, 20 from dual union all
                                  select 9, 3, 20 from dual)
                                
                                select x.*,
                                       case when x.state = 2 then x.max_qty
                                            when x.state = 1 then 
                                                               case when floor(x.rem_qty/x.sum_i_boxes) * x.sum_i_boxes <> x.rem_qty 
                                                                 then floor(x.rem_qty/x.sum_i_boxes) + case 
                                                                                                       when x.rem_boxes <= x.rem_qty - floor(x.rem_qty/x.sum_i_boxes) * x.sum_i_boxes then 1 else 0 end
                                                                 else floor(x.rem_qty/x.sum_i_boxes)
                                                               end
                                            else 0
                                       end as assign
                                       from  (select y.*,
                                                     sum(y.in_complete_boxes) over (partition by y.box_group) as sum_i_boxes,
                                                     163 - sum(case when y.dis_qty_after <= 163 then y.max_qty else 0 end) over (order by y.box, y.box_group, y.max_qty) as rem_qty,
                                                     min(y.dis_qty_before) over (partition by y.box_group) as min_qty_group,
                                                     max(y.dis_qty_after) over (partition by y.box_group) as max_qty_group,
                                                     case 
                                                       when y.dis_qty_after <= 163 then 2
                                                       else case
                                                              when min(y.dis_qty_before) over (partition by y.box_group) < 163
                                                              and  max(y.dis_qty_after) over (partition by y.box_group) >= 163
                                                              then 1 
                                                              else 0 
                                                            end
                                                     end as state
                                              from  (select z.*,
                                                            sum(z.max_qty) over (order by z.box, z.box_group, z.max_qty) - z.max_qty as dis_qty_before,
                                                            sum(z.max_qty) over (order by z.box, z.box_group, z.max_qty) - z.max_qty + z.max_qty * count(*) over (partition by z.box_group order by z.max_qty, z.box asc range between current row and unbounded following) as dis_qty_after,
                                                            count(*) over (partition by z.box_group order by z.max_qty, z.box asc range between current row and unbounded following) as rem_boxes,
                                                            case when sum(z.max_qty) over (order by z.box, z.box_group, z.max_qty) - z.max_qty + z.max_qty * count(*) over (partition by z.box_group order by z.max_qty, z.box asc range between current row and unbounded following) > 163 then 1 else 0 end as in_complete_boxes
                                                     from   z
                                             ) y
                                      ) x