Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

Calculate using previous column and rows

861381May 30 2011 — edited Jun 1 2011
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

Comments

Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Jun 29 2011
Added on May 30 2011
12 comments
19,428 views