Home » SQL & PL/SQL » SQL & PL/SQL » Help Required in SQL Analytic Function. (Oracle 11gR2, 11.2.0.1.0, RHEL 5.0)
Help Required in SQL Analytic Function. [message #616273] Sat, 14 June 2014 07:44 Go to next message
jimit_shaili
Messages: 237
Registered: June 2006
Location: India, Ahmedabad
Senior Member
Dear Friends,

I want to allocate maximum possible stock against requirement.

Requirement : 26 Kgs.
Available Stock : 40 Kgs.

In order to allocate maximum possible kgs, i tried out below query.
Upto chest_no 6 it allocates perfectly(18 kgs) and so on.
I want such a query which should pick up the chest_no 9
to allocate maximum remaining kgs rather next available chest_no 7 (means 18kgs + 7kgs rather then 18kgs + 8kgs).
Please either correct my try or suggest some new technique of SQL.

 CREATE TABLE DSP.STOCK
 (
   CHEST_NO   NUMBER(8),
   CHEST_KGS  NUMBER(8),
   PID        NUMBER(8)
 );

Insert into DSP.STOCK   (CHEST_NO, CHEST_KGS, PID) Values   (1, 2, 999);
Insert into DSP.STOCK   (CHEST_NO, CHEST_KGS, PID) Values   (2, 2, 999);
Insert into DSP.STOCK   (CHEST_NO, CHEST_KGS, PID) Values   (3, 2, 999);
Insert into DSP.STOCK   (CHEST_NO, CHEST_KGS, PID) Values   (4, 4, 999);
Insert into DSP.STOCK   (CHEST_NO, CHEST_KGS, PID) Values   (5, 4, 999);
Insert into DSP.STOCK   (CHEST_NO, CHEST_KGS, PID) Values   (6, 4, 999);
Insert into DSP.STOCK   (CHEST_NO, CHEST_KGS, PID) Values   (7, 7, 999);
Insert into DSP.STOCK   (CHEST_NO, CHEST_KGS, PID) Values   (8, 7, 999);
Insert into DSP.STOCK   (CHEST_NO, CHEST_KGS, PID) Values   (9, 8, 999);

commit;

select chest_no,chest_kgs
,case when (28-sum(sum ((chest_kgs))) over (partition by pid order by pid rows unbounded preceding)) >= 0 then 1 else 0 end status
from stock
group by chest_no,chest_kgs,pid;

  CHEST_NO  CHEST_KGS     STATUS
---------- ---------- ----------
         1          2          1
         2          2          1
         3          2          1
         4          4          1
         5          4          1
         6          4          1
         7          7          1
         8          7          0
         9          8          0


Here status = 1 indicates allocation done and status = 0 indicates allocation pending.

Regards

Jimit

Report message to a moderator

Re: Help Required in SQL Analytic Function. [message #616275 is a reply to message #616273] Sat, 14 June 2014 09:43 Go to previous messageGo to next message
Michel Cadot
Messages: 68647
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Not really clear.
From the result you seem to want all chests up to but not exceeding the given total in the order of chest_no.
If this is not the case, then provide a test case where increasing chest_kgs and chest_id are not the same order.
What is the purpose of PID? If it has nothing to do with the question then remove it.

Or maybe the result you showed is NOT the one you want. In this case, show us the one you want instead, we don't care about a wrong result.

Report message to a moderator

Re: Help Required in SQL Analytic Function. [message #616280 is a reply to message #616275] Sat, 14 June 2014 18:02 Go to previous messageGo to next message
manubatham20
Messages: 566
Registered: September 2010
Location: Seattle, WA, USA
Senior Member
Looks to me an example of 0/1 Knapsack problem. I remember Michel doing similiar algorithm appx. 2 years back for some hard disk space.

Best Fit Sum

Below may help.
https://community.oracle.com/message/11294260

Below may be of your interest.
http://math.stackexchange.com/questions/436299/how-to-reduce-0-1-knapsack-to-knapsack-like-problem-with-overflow

Manu

Report message to a moderator

Re: Help Required in SQL Analytic Function. [message #616282 is a reply to message #616280] Sun, 15 June 2014 00:13 Go to previous messageGo to next message
Michel Cadot
Messages: 68647
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
I remember Michel doing similiar algorithm


With final solution at http://www.orafaq.com/forum/mv/msg/170554/505531/#msg_505531 and following.

But actual solution(s) depend on the actual problem and so on the answer to the questions I posted.

Report message to a moderator

Re: Help Required in SQL Analytic Function. [message #616337 is a reply to message #616273] Mon, 16 June 2014 00:17 Go to previous messageGo to next message
jimit_shaili
Messages: 237
Registered: June 2006
Location: India, Ahmedabad
Senior Member
Thanks Friends,

In actual case i want allocation to be done PID. (Thatswhy i used it in partition and order by clause of window). Here i dont want to break any chest to allocate available kgs. following is the result that i'm trying to get.

select chest_no,chest_kgs
,case when (26-sum(sum ((chest_kgs))) over (partition by pid order by pid rows unbounded preceding)) >= 0 then 1 else 0 end status
from stock
group by chest_no,chest_kgs,pid;          
                                           
  CHEST_NO  CHEST_KGS     STATUS  RESULT_REQ
---------- ---------- ----------  ----------
         1          2          1	   1
         2          2          1	   1
         3          2          1	   1
         4          4          1	   1
         5          4          1	   1
         6          4          1	   1
         7          7          1	   0
         8          7          0	   0
         9          8          0	   1


Regards

Jimit

[Updated on: Mon, 16 June 2014 00:17]

Report message to a moderator

Re: Help Required in SQL Analytic Function. [message #616341 is a reply to message #616337] Mon, 16 June 2014 00:27 Go to previous messageGo to next message
Michel Cadot
Messages: 68647
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
In actual case i want allocation to be done PID.


What does this mean? Any missing word in the sentence?

Quote:
In actual case i want allocation to be done PID. (Thatswhy i used it in partition and order by clause of window). Here i dont want to break any chest to allocate available kgs. following is the result that i'm trying to get.


Still not clear.
What is this query? Why 26 now and 26 in the first post?

Tell us WITH WORDS not the example what are the rules (like I did it above).
Why CHEST_NO 9 and not 7?
Why not CHEST_NO 9, 8, 7, 6?

Once again post a test case where CHEST_NO and CHEST_KGS are not the same order and give us the result then.

Report message to a moderator

Re: Help Required in SQL Analytic Function. [message #616482 is a reply to message #616273] Tue, 17 June 2014 07:29 Go to previous messageGo to next message
jimit_shaili
Messages: 237
Registered: June 2006
Location: India, Ahmedabad
Senior Member
Below is the test case, hope that will clear my problem as well as your requirement to better understanding my issue.

CREATE TABLE DSP.STOCK
(
  PID        NUMBER(8),
  CHEST_NO   NUMBER(8),
  CHEST_KGS  NUMBER(8)
)
/

Insert into STOCK   (PID, CHEST_NO, CHEST_KGS) Values   (2383, 101, 6);
Insert into STOCK   (PID, CHEST_NO, CHEST_KGS) Values   (2383, 85, 9);
Insert into STOCK   (PID, CHEST_NO, CHEST_KGS) Values   (2383, 154, 7);
Insert into STOCK   (PID, CHEST_NO, CHEST_KGS) Values   (2383, 256, 10);
Insert into STOCK   (PID, CHEST_NO, CHEST_KGS) Values   (5656, 799, 15);
Insert into STOCK   (PID, CHEST_NO, CHEST_KGS) Values   (5656, 3, 10);
Insert into STOCK   (PID, CHEST_NO, CHEST_KGS) Values   (135, 201, 6);
Insert into STOCK   (PID, CHEST_NO, CHEST_KGS) Values   (135, 409, 8);
Insert into STOCK   (PID, CHEST_NO, CHEST_KGS) Values   (135, 15, 9);
Insert into STOCK   (PID, CHEST_NO, CHEST_KGS) Values   (135, 40, 15);

select pid,chest_no,chest_kgs
,case when (23-sum(sum ((chest_kgs))) over (partition by pid order by chest_kgs rows unbounded preceding)) >= 0 then 1 else 0 end status
from stock
group by chest_no,chest_kgs,pid;

      PID   CHEST_NO  CHEST_KGS     STATUS
--------- ---------- ---------- ----------
      135        201          6          1
      135        409          8          1
      135         15          9          1
      135         40         15          0
     2383        101          6          1
     2383        154          7          1
     2383         85          9          1
     2383        256         10          0
     5656          3         10          1
     5656        799         15          0


Status = 1 = Allocation Done.
Status = 0 = Allocation Pending.

Logical Rules :
1) Allocate maximum chest_kgs from available stock chests.
2) Chest can not broken to loose(partial allocation) to fulfill the requirement.
3) Allocation should be start from smallest kgs chests to biggest kgs chests.

Required Kgs : 23

With above query for PID = 2383, query allocates chest_no 101,154,85 which means (6+7+9 = 22 kgs).
Here in above case it allocates 22 kgs wherein requirement is 23 kgs.
I want such a query which will check for next available chest which can fulfill the order more optimally.
In above case for PID = 2383, it can be achieved by allocating chest_no 101,154,256 which means(6+7+10 = 23 kgs).

I hope I've clearly stated my problem.

Regards

Jimit

Report message to a moderator

Re: Help Required in SQL Analytic Function. [message #616539 is a reply to message #616273] Wed, 18 June 2014 02:10 Go to previous messageGo to next message
jimit_shaili
Messages: 237
Registered: June 2006
Location: India, Ahmedabad
Senior Member
Dear Friends

Is there anymore clarity required on above issue?

Regards

Jimit

Report message to a moderator

Re: Help Required in SQL Analytic Function. [message #616611 is a reply to message #616539] Thu, 19 June 2014 00:51 Go to previous messageGo to next message
jimit_shaili
Messages: 237
Registered: June 2006
Location: India, Ahmedabad
Senior Member
Friends please suggest some solution or hint a new way to solve a above problem.

Regards

Jimit

Report message to a moderator

Re: Help Required in SQL Analytic Function. [message #616614 is a reply to message #616611] Thu, 19 June 2014 01:23 Go to previous messageGo to next message
Michel Cadot
Messages: 68647
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

What should be the result for 25, 28 and 30 kg?

[Updated on: Thu, 19 June 2014 01:24]

Report message to a moderator

Re: Help Required in SQL Analytic Function. [message #616617 is a reply to message #616614] Thu, 19 June 2014 01:58 Go to previous messageGo to next message
jimit_shaili
Messages: 237
Registered: June 2006
Location: India, Ahmedabad
Senior Member
If Requiment is 25 then,
select pid,chest_no,chest_kgs
,case when (25-sum(sum ((chest_kgs))) over (partition by pid order by chest_kgs rows unbounded preceding)) >= 0 then 1 else 0 end status
from stock
group by chest_no,chest_kgs,pid;

   PID   CHEST_NO  CHEST_KGS     STATUS	REQUIRED
------ ---------- ---------- ----------	----------
   135        201          6          1		1
   135        409          8          1		1
   135         15          9          1		1
   135         40         15          0		0
  2383        101          6          1		1
  2383        154          7          1		1
  2383         85          9          1		0
  2383        256         10          0		1
  5656          3         10          1		1
  5656        799         15          1		1


If Requiment is 28 then,
select pid,chest_no,chest_kgs
,case when (28-sum(sum ((chest_kgs))) over (partition by pid order by chest_kgs rows unbounded preceding)) >= 0 then 1 else 0 end status
from stock
group by chest_no,chest_kgs,pid;

   PID   CHEST_NO  CHEST_KGS     STATUS	REQUIRED
------ ---------- ---------- ----------	----------
   135        201          6          1		1
   135        409          8          1		1
   135         15          9          1		1
   135         40         15          0		0
  2383        101          6          1		1
  2383        154          7          1		1
  2383         85          9          1		0
  2383        256         10          0		1
  5656          3         10          1		1
  5656        799         15          1		1


If Requiment is 30 then,

select pid,chest_no,chest_kgs
,case when (30-sum(sum ((chest_kgs))) over (partition by pid order by chest_kgs rows unbounded preceding)) >= 0 then 1 else 0 end status
from stock
group by chest_no,chest_kgs,pid;

   PID   CHEST_NO  CHEST_KGS     STATUS	REQUIRED
------ ---------- ---------- ----------	----------
   135        201          6          1		1
   135        409          8          1		1
   135         15          9          1		0
   135         40         15          0		1
  2383        101          6          1		1
  2383        154          7          1		1
  2383         85          9          1		0
  2383        256         10          0		1
  5656          3         10          1		1
  5656        799         15          1		1


Regards

Jimit

Report message to a moderator

Re: Help Required in SQL Analytic Function. [message #616619 is a reply to message #616617] Thu, 19 June 2014 02:17 Go to previous messageGo to next message
Michel Cadot
Messages: 68647
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

You mean: Smile
SQL> l
  1  select pid,chest_no,chest_kgs
  2  ,case when (25-sum(sum ((chest_kgs))) over (partition by pid order by chest_kgs rows unbounded preceding)) >= 0 then 1 else 0 end status
  3  from stock
  4  group by chest_no,chest_kgs,pid
  5* order by pid, chest_kgs, chest_no
SQL> /
       PID   CHEST_NO  CHEST_KGS     STATUS
---------- ---------- ---------- ----------
       135        201          6          1
       135        409          8          1
       135         15          9          1
       135         40         15          0
      2383        101          6          1
      2383        154          7          1
      2383         85          9          1
      2383        256         10          0
      5656          3         10          1
      5656        799         15          1

10 rows selected.

PID 2383 does not reach 25, why chest 256 is not used?

SQL> select pid,chest_no,chest_kgs
  2  ,case when (28-sum(sum ((chest_kgs))) over (partition by pid order by chest_kgs rows unbounded preceding)) >= 0 then 1 else 0 end status
  3  from stock
  4  group by chest_no,chest_kgs,pid
  5  order by pid, chest_kgs, chest_no
  6  /
       PID   CHEST_NO  CHEST_KGS     STATUS
---------- ---------- ---------- ----------
       135        201          6          1
       135        409          8          1
       135         15          9          1
       135         40         15          0
      2383        101          6          1
      2383        154          7          1
      2383         85          9          1
      2383        256         10          0
      5656          3         10          1
      5656        799         15          1

PID 135 does not reach 28, why chest 40 is not used?
PID 2383 does not reach 28, why chest 256 is not used?

SQL> l
  1  select pid,chest_no,chest_kgs
  2  ,case when (30-sum(sum ((chest_kgs))) over (partition by pid order by chest_kgs rows unbounded preceding)) >= 0 then 1 else 0 end status
  3  from stock
  4  group by chest_no,chest_kgs,pid
  5* order by pid, chest_kgs, chest_no
SQL> /
       PID   CHEST_NO  CHEST_KGS     STATUS
---------- ---------- ---------- ----------
       135        201          6          1
       135        409          8          1
       135         15          9          1
       135         40         15          0
      2383        101          6          1
      2383        154          7          1
      2383         85          9          1
      2383        256         10          0
      5656          3         10          1
      5656        799         15          1

PID 135 does not reach 30, why chest 40 is not used?
PID 2383 does not reach 30, why chest 256 is not used?

Report message to a moderator

Re: Help Required in SQL Analytic Function. [message #616622 is a reply to message #616619] Thu, 19 June 2014 02:52 Go to previous messageGo to next message
jimit_shaili
Messages: 237
Registered: June 2006
Location: India, Ahmedabad
Senior Member
When Requirement is 25 Kgs then, For PID 2383 chest 256 can not be used. Because its logical rule that imposed by operational constraints to open a chest in loose, rather say partially allocation from chest. In above case if i overrule,then for PID 2383, chest no (101,154,85) with kgs (6+7+9) [Full chest allocation] and chest no 256 with kgs 3 (out of 10) can be allocated.

Regards

Jimit

Report message to a moderator

Re: Help Required in SQL Analytic Function. [message #616630 is a reply to message #616622] Thu, 19 June 2014 03:28 Go to previous messageGo to next message
Michel Cadot
Messages: 68647
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

OK so you want the greatest <= possible sum starting from the lowest chest_kg and maybe skipping one (or more?) chest if the next value is better?
Now what is the result if you want 8 kg and have the following chests: 1, 2, 3, 4, 5? Explain.

Report message to a moderator

Re: Help Required in SQL Analytic Function. [message #616634 is a reply to message #616630] Thu, 19 June 2014 03:57 Go to previous messageGo to next message
jimit_shaili
Messages: 237
Registered: June 2006
Location: India, Ahmedabad
Senior Member
Dear Michel

Following will be the scenario...
Insert into STOCK   (PID, CHEST_NO, CHEST_KGS) Values   (1225, 123, 1);
Insert into STOCK   (PID, CHEST_NO, CHEST_KGS) Values   (1225, 456, 4);
Insert into STOCK   (PID, CHEST_NO, CHEST_KGS) Values   (1225, 789, 3);
Insert into STOCK   (PID, CHEST_NO, CHEST_KGS) Values   (1225, 678, 2);
Insert into STOCK   (PID, CHEST_NO, CHEST_KGS) Values   (1225, 234, 5);


select pid,chest_no,chest_kgs
,case when (8-sum(sum ((chest_kgs))) over (partition by pid order by chest_kgs rows unbounded preceding)) >= 0 then 1 else 0 end status
from stock
where pid = 1225
group by chest_no,chest_kgs,pid
order by pid, chest_kgs, chest_no

       PID   CHEST_NO  CHEST_KGS     STATUS	REQUIRED
---------- ---------- ---------- ----------	--------
      1225        123          1          1		1
      1225        678          2          1		1
      1225        789          3          1		0
      1225        456          4          0		0
      1225        234          5          0		1


For all other PID
select pid,chest_no,chest_kgs
,case when (8-sum(sum ((chest_kgs))) over (partition by pid order by chest_kgs rows unbounded preceding)) >= 0 then 1 else 0 end status
from stock
--where pid = 1225
group by chest_no,chest_kgs,pid
order by pid, chest_kgs, chest_no
      
       PID   CHEST_NO  CHEST_KGS     STATUS	REQUIRED
---------- ---------- ---------- ----------	--------
       135        201          6          1	0
       135        409          8          0	1
       135         15          9          0	0
       135         40         15          0	0
      1225        123          1          1	1
      1225        678          2          1	1
      1225        789          3          1	0
      1225        456          4          0	0
      1225        234          5          0	1
      2383        101          6          1	0
      2383        154          7          0	1
      2383         85          9          0	0
      2383        256         10          0	0
      5656          3         10          0	0
      5656        799         15          0     0



Regards

Jimit

[Updated on: Thu, 19 June 2014 04:12]

Report message to a moderator

Re: Help Required in SQL Analytic Function. [message #616657 is a reply to message #616634] Thu, 19 June 2014 05:43 Go to previous messageGo to next message
Michel Cadot
Messages: 68647
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

OK, now if you have (in a PID): 1, 2, 3, 4, 5 and 6 what should be the result for sum 8?
Same question with: 1, 2, 3, 4, 5 and 7, still for sum 8?

Note: I don't want to bother you, I just want to clarify the specifications.

[Updated on: Thu, 19 June 2014 05:44]

Report message to a moderator

Re: Help Required in SQL Analytic Function. [message #616665 is a reply to message #616657] Thu, 19 June 2014 06:24 Go to previous messageGo to next message
jimit_shaili
Messages: 237
Registered: June 2006
Location: India, Ahmedabad
Senior Member
You are not bothering me at all, you are helping me to solve my issue. And in both the above cases (in a PID) query should allocate chests with 1,2 & 5 kgs for sum of 8.

Report message to a moderator

Re: Help Required in SQL Analytic Function. [message #616675 is a reply to message #616665] Thu, 19 June 2014 09:26 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA
Senior Member
Powermultiset solution:

SQL> create or replace
  2    type stock_obj_type
  3      as object(
  4                chest_no number,
  5                chest_kgs number
  6               )
  7  /

Type created.

SQL> create or replace
  2    type stock_obj_tbl_type
  3      as table of stock_obj_type
  4  /

Type created.

SQL> create or replace
  2    type NumList
  3      as table of number(8)
  4  /

Type created.

SQL> variable max_kgs number
SQL> exec :max_kgs := 23;

PL/SQL procedure successfully completed.

SQL> with t1 as (
  2              select  pid,
  3                      cast(
  4                           collect(stock_obj_type(chest_no,chest_kgs))
  5                           as stock_obj_tbl_type
  6                          ) stock_set
  7                from  stock
  8                where chest_kgs <= :max_kgs
  9                group by pid
 10             ),
 11       t2 as (
 12              select  pid,
 13                      column_value stock_set,
 14                      (
 15                       select  sum(x.chest_kgs)
 16                         from  table(column_value) x
 17                      ) total_kgs
 18                from  t1,
 19                      table(
 20                            powermultiset(stock_set)
 21                           )
 22            ),
 23      t3 as (
 24             select  pid,
 25                     stock_set,
 26                     total_kgs,
 27                     dense_rank() over(partition by pid order by :max_kgs - total_kgs) rank
 28               from  t2
 29               where total_kgs <= :max_kgs
 30            )
 31  select  pid,
 32          (select cast(collect(chest_no) as NumList) from table(stock_set)) chest_no_set,
 33          (select cast(collect(chest_kgs) as NumList) from table(stock_set)) chest_kgs_set,
 34          total_kgs
 35    from  t3
 36    where rank = 1
 37    order by :max_kgs - total_kgs
 38  /

       PID CHEST_NO_SET                 CHEST_KGS_SET                 TOTAL_KGS
---------- ---------------------------- ---------------------------- ----------
       135 NUMLIST(40, 409)             NUMLIST(15, 8)                       23
       135 NUMLIST(201, 15, 409)        NUMLIST(6, 9, 8)                     23
      2383 NUMLIST(101, 256, 154)       NUMLIST(6, 10, 7)                    23
      5656 NUMLIST(799)                 NUMLIST(15)                          15

SQL> exec :max_kgs := 25;

PL/SQL procedure successfully completed.

SQL> with t1 as (
  2              select  pid,
  3                      cast(
  4                           collect(stock_obj_type(chest_no,chest_kgs))
  5                           as stock_obj_tbl_type
  6                          ) stock_set
  7                from  stock
  8                where chest_kgs <= :max_kgs
  9                group by pid
 10             ),
 11       t2 as (
 12              select  pid,
 13                      column_value stock_set,
 14                      (
 15                       select  sum(x.chest_kgs)
 16                         from  table(column_value) x
 17                      ) total_kgs
 18                from  t1,
 19                      table(
 20                            powermultiset(stock_set)
 21                           )
 22            ),
 23      t3 as (
 24             select  pid,
 25                     stock_set,
 26                     total_kgs,
 27                     dense_rank() over(partition by pid order by :max_kgs - total_kgs) rank
 28               from  t2
 29               where total_kgs <= :max_kgs
 30            )
 31  select  pid,
 32          (select cast(collect(chest_no) as NumList) from table(stock_set)) chest_no_set,
 33          (select cast(collect(chest_kgs) as NumList) from table(stock_set)) chest_kgs_set,
 34          total_kgs
 35    from  t3
 36    where rank = 1
 37    order by :max_kgs - total_kgs
 38  /

       PID CHEST_NO_SET                 CHEST_KGS_SET                 TOTAL_KGS
---------- ---------------------------- ---------------------------- ----------
      2383 NUMLIST(101, 256, 85)        NUMLIST(6, 10, 9)                    25
      5656 NUMLIST(799, 3)              NUMLIST(15, 10)                      25
       135 NUMLIST(40, 15)              NUMLIST(15, 9)                       24

SQL> exec :max_kgs := 28;

PL/SQL procedure successfully completed.

SQL> with t1 as (
  2              select  pid,
  3                      cast(
  4                           collect(stock_obj_type(chest_no,chest_kgs))
  5                           as stock_obj_tbl_type
  6                          ) stock_set
  7                from  stock
  8                where chest_kgs <= :max_kgs
  9                group by pid
 10             ),
 11       t2 as (
 12              select  pid,
 13                      column_value stock_set,
 14                      (
 15                       select  sum(x.chest_kgs)
 16                         from  table(column_value) x
 17                      ) total_kgs
 18                from  t1,
 19                      table(
 20                            powermultiset(stock_set)
 21                           )
 22            ),
 23      t3 as (
 24             select  pid,
 25                     stock_set,
 26                     total_kgs,
 27                     dense_rank() over(partition by pid order by :max_kgs - total_kgs) rank
 28               from  t2
 29               where total_kgs <= :max_kgs
 30            )
 31  select  pid,
 32          (select cast(collect(chest_no) as NumList) from table(stock_set)) chest_no_set,
 33          (select cast(collect(chest_kgs) as NumList) from table(stock_set)) chest_kgs_set,
 34          total_kgs
 35    from  t3
 36    where rank = 1
 37    order by :max_kgs - total_kgs
 38  /

       PID CHEST_NO_SET                 CHEST_KGS_SET                 TOTAL_KGS
---------- ---------------------------- ---------------------------- ----------
      2383 NUMLIST(256, 154, 85)        NUMLIST(10, 7, 9)                    26
      5656 NUMLIST(799, 3)              NUMLIST(15, 10)                      25
       135 NUMLIST(40, 15)              NUMLIST(15, 9)                       24

SQL> exec :max_kgs := 30;

PL/SQL procedure successfully completed.

SQL> with t1 as (
  2              select  pid,
  3                      cast(
  4                           collect(stock_obj_type(chest_no,chest_kgs))
  5                           as stock_obj_tbl_type
  6                          ) stock_set
  7                from  stock
  8                where chest_kgs <= :max_kgs
  9                group by pid
 10             ),
 11       t2 as (
 12              select  pid,
 13                      column_value stock_set,
 14                      (
 15                       select  sum(x.chest_kgs)
 16                         from  table(column_value) x
 17                      ) total_kgs
 18                from  t1,
 19                      table(
 20                            powermultiset(stock_set)
 21                           )
 22            ),
 23      t3 as (
 24             select  pid,
 25                     stock_set,
 26                     total_kgs,
 27                     dense_rank() over(partition by pid order by :max_kgs - total_kgs) rank
 28               from  t2
 29               where total_kgs <= :max_kgs
 30            )
 31  select  pid,
 32          (select cast(collect(chest_no) as NumList) from table(stock_set)) chest_no_set,
 33          (select cast(collect(chest_kgs) as NumList) from table(stock_set)) chest_kgs_set,
 34          total_kgs
 35    from  t3
 36    where rank = 1
 37    order by :max_kgs - total_kgs
 38  /

       PID CHEST_NO_SET                 CHEST_KGS_SET                 TOTAL_KGS
---------- ---------------------------- ---------------------------- ----------
       135 NUMLIST(201, 40, 15)         NUMLIST(6, 15, 9)                    30
      2383 NUMLIST(256, 154, 85)        NUMLIST(10, 7, 9)                    26
      5656 NUMLIST(799, 3)              NUMLIST(15, 10)                      25

SQL>

SY.

Report message to a moderator

Re: Help Required in SQL Analytic Function. [message #616699 is a reply to message #616665] Thu, 19 June 2014 14:25 Go to previous messageGo to next message
Michel Cadot
Messages: 68647
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

One last question.
If you have 1, 2, 3, 4, 5, 6, 7 and sum=23.
You have an exact solution with 1+2+3+4+6+7 but I think it does not follow your rule which is I think only the last value can not be the row just following the previous one.
Else you have an approximate solution with: 1+2+3+4+5+7=22.
What is the correct solution in this case?

Report message to a moderator

Re: Help Required in SQL Analytic Function. [message #616827 is a reply to message #616699] Sat, 21 June 2014 00:39 Go to previous messageGo to next message
jimit_shaili
Messages: 237
Registered: June 2006
Location: India, Ahmedabad
Senior Member
In above case, following the optimum allocation without breaking the chest, the best suitable solution of allocation will be 1+2+3+4+6+7 for requirement of 23kgs.

Regards

Jimit

Report message to a moderator

Re: Help Required in SQL Analytic Function. [message #616829 is a reply to message #616827] Sat, 21 June 2014 00:56 Go to previous messageGo to next message
Michel Cadot
Messages: 68647
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
 

SQL> select * from STOCK order by pid, chest_kgs, chest_no;
       PID   CHEST_NO  CHEST_KGS
---------- ---------- ----------
       135        201          6
       135        409          8
       135         15          9
       135         40         15
      1225        123          1
      1225        678          2
      1225        789          3
      1225        456          4
      1225        234          5
      2345        123          1
      2345        678          2
      2345        789          3
      2345        456          4
      2345        234          5
      2345        890          6
      2345        567          7
      2383        101          6
      2383        154          7
      2383         85          9
      2383        256         10
      3456        123          1
      3456        678          2
      3456        789          3
      3456        456          4
      3456        234          5
      3456        567          7
      5656          3         10
      5656        799         15

SQL> def TOT=23
SQL> col chest_path format a30
SQL> col sum_kgs    format a30
SQL> with
  2    data as (
  3      select pid, chest_no, chest_kgs,
  4             row_number() over (partition by pid order by chest_kgs, chest_no) rn
  5      from stock
  6    ),
  7    step1 as (
  8      select pid,
  9             substr(sys_connect_by_path(chest_no,'/'),2) chest_path,
 10             substr(sys_connect_by_path(rn,'/'),2) rn_path,
 11             substr(sys_connect_by_path(chest_kgs,'+'),2) sum_kgs
 12      from data
 13      connect by prior pid = pid
 14          and prior rn < rn
 15          and chest_kgs <= &TOT
 16      start with rn = 1 and chest_kgs < &TOT
 17    ),
 18    step2 as (
 19      select pid, chest_path, sum_kgs,
 20             to_number(xmlquery(sum_kgs returning content).getstringval()) tot_kgs,
 21             row_number() over
 22               (partition by pid
 23                order by to_number(xmlquery(sum_kgs returning content).getstringval()) desc,
 24                         rn_path) rn
 25      from step1
 26      where to_number(xmlquery(sum_kgs returning content).getstringval()) <= &TOT
 27    )
 28  select pid, chest_path, sum_kgs, tot_kgs
 29  from step2
 30  where rn = 1
 31  order by pid
 32  /
       PID CHEST_PATH                     SUM_KGS                           TOT_KGS
---------- ------------------------------ ------------------------------ ----------
       135 201/409/15                     6+8+9                                  23
      1225 123/678/789/456/234            1+2+3+4+5                              15
      2345 123/678/789/456/890/567        1+2+3+4+6+7                            23
      2383 101/154/256                    6+7+10                                 23
      3456 123/678/789/456/234/567        1+2+3+4+5+7                            22
      5656 3                              10                                     10

SQL> def TOT=25
SQL> /
       PID CHEST_PATH                     SUM_KGS                           TOT_KGS
---------- ------------------------------ ------------------------------ ----------
       135 201/409/15                     6+8+9                                  23
      1225 123/678/789/456/234            1+2+3+4+5                              15
      2345 123/678/456/234/890/567        1+2+4+5+6+7                            25
      2383 101/85/256                     6+9+10                                 25
      3456 123/678/789/456/234/567        1+2+3+4+5+7                            22
      5656 3/799                          10+15                                  25

SQL> def TOT=28
SQL> /
       PID CHEST_PATH                     SUM_KGS                           TOT_KGS
---------- ------------------------------ ------------------------------ ----------
       135 201/409/15                     6+8+9                                  23
      1225 123/678/789/456/234            1+2+3+4+5                              15
      2345 123/678/789/456/234/890/567    1+2+3+4+5+6+7                          28
      2383 101/85/256                     6+9+10                                 25
      3456 123/678/789/456/234/567        1+2+3+4+5+7                            22
      5656 3/799                          10+15                                  25

SQL> def TOT=30
SQL> /
       PID CHEST_PATH                     SUM_KGS                           TOT_KGS
---------- ------------------------------ ------------------------------ ----------
       135 201/15/40                      6+9+15                                 30
      1225 123/678/789/456/234            1+2+3+4+5                              15
      2345 123/678/789/456/234/890/567    1+2+3+4+5+6+7                          28
      2383 101/85/256                     6+9+10                                 25
      3456 123/678/789/456/234/567        1+2+3+4+5+7                            22
      5656 3/799                          10+15                                  25

SQL> def TOT=8
SQL> /
       PID CHEST_PATH                     SUM_KGS                           TOT_KGS
---------- ------------------------------ ------------------------------ ----------
       135 201                            6                                       6
      1225 123/678/234                    1+2+5                                   8
      2345 123/678/234                    1+2+5                                   8
      2383 101                            6                                       6
      3456 123/678/234                    1+2+5                                   8

Are they the results you expect (I don't speak about the form but the values for the above data)?

[Updated on: Sat, 21 June 2014 01:22]

Report message to a moderator

Re: Help Required in SQL Analytic Function. [message #616830 is a reply to message #616829] Sat, 21 June 2014 01:51 Go to previous messageGo to next message
jimit_shaili
Messages: 237
Registered: June 2006
Location: India, Ahmedabad
Senior Member
Dear Michel

Almost done. Still it seems to have one problem, when requirement is 23kgs it should allocate chest_no 799 with 15kgs (For PID=5656), rather then allocating chest_no 3 with 10kgs. Currently trying to decode your solution step by step, it required more knowledge regarding functions and features you have used to solve the problem. For better understanding required to go through the docs.

Regards

Jimit

Report message to a moderator

Re: Help Required in SQL Analytic Function. [message #616831 is a reply to message #616830] Sat, 21 June 2014 02:00 Go to previous messageGo to next message
Michel Cadot
Messages: 68647
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Ah! The result does need to "start" with the lowest chest_kgs, I misunderstood what you want.
What you want is the best fit less or equal the sum you give in the maximum number of chests (which mean you first want the lowest chest_kgs), is this correct?

Report message to a moderator

Re: Help Required in SQL Analytic Function. [message #616832 is a reply to message #616831] Sat, 21 June 2014 03:09 Go to previous messageGo to next message
jimit_shaili
Messages: 237
Registered: June 2006
Location: India, Ahmedabad
Senior Member
Yes, what you mean is correct.

Report message to a moderator

Re: Help Required in SQL Analytic Function. [message #616845 is a reply to message #616832] Sat, 21 June 2014 08:13 Go to previous messageGo to next message
Michel Cadot
Messages: 68647
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Here are 2 solutions, they are close and give the same result here but may not with another test case.
Both choose the total the closest to (but not exceeding) the given total but the first one chooses the set of chests that starts with the smallest chest among all the possible solutions when the second one first chooses the set with the most chests.
SQL> col chest_path format a30
SQL> col sum_kgs    format a30
SQL> def TOT=23
SQL> with
  2    data as ( -- number the chest from smallest to biggest per PID
  3              -- keep only the chests smaller than the given &TOT
  4      select pid, chest_no, chest_kgs,
  5             row_number() over (partition by pid order by chest_kgs, chest_no) rn
  6      from stock
  7      where chest_kgs <= &TOT
  8    ),
  9    all_possible_combinations as (
 10      select pid,
 11             substr(sys_connect_by_path(chest_no,'/'),2) chest_path,
 12             substr(sys_connect_by_path(rn,'/'),2) rn_path,
 13             substr(sys_connect_by_path(chest_kgs,'+'),2) sum_kgs
 14      from data
 15      connect by prior pid = pid
 16          and prior rn < rn
 17    ),
 18    all_combinations_for_&TOT as (
 19      -- number the combination from the best fit to the worst one for each PID
 20      select pid, chest_path, sum_kgs,
 21             to_number(xmlquery(sum_kgs returning content).getstringval()) tot_kgs,
 22             row_number() over
 23               (partition by pid
 24                order by to_number(xmlquery(sum_kgs returning content).getstringval()) desc,
 25                         rn_path) rn
 26      from all_possible_combinations
 27      where to_number(xmlquery(sum_kgs returning content).getstringval()) <= &TOT
 28    )
 29  -- select the best ones for each PID
 30  select pid, chest_path, sum_kgs, tot_kgs
 31  from all_combinations_for_&TOT
 32  where rn = 1
 33  order by pid
 34  /
       PID CHEST_PATH                     SUM_KGS                           TOT_KGS
---------- ------------------------------ ------------------------------ ----------
       135 201/409/15                     6+8+9                                  23
      1225 123/678/789/456/234            1+2+3+4+5                              15
      2345 123/678/789/456/890/567        1+2+3+4+6+7                            23
      2383 101/154/256                    6+7+10                                 23
      3456 123/678/789/456/234/567        1+2+3+4+5+7                            22
      5656 799                            15                                     15

6 rows selected.

SQL> def TOT=25
SQL> /
       PID CHEST_PATH                     SUM_KGS                           TOT_KGS
---------- ------------------------------ ------------------------------ ----------
       135 15/40                          9+15                                   24
      1225 123/678/789/456/234            1+2+3+4+5                              15
      2345 123/678/456/234/890/567        1+2+4+5+6+7                            25
      2383 101/85/256                     6+9+10                                 25
      3456 123/678/789/456/234/567        1+2+3+4+5+7                            22
      5656 3/799                          10+15                                  25

6 rows selected.

SQL> def TOT=28
SQL> /
       PID CHEST_PATH                     SUM_KGS                           TOT_KGS
---------- ------------------------------ ------------------------------ ----------
       135 15/40                          9+15                                   24
      1225 123/678/789/456/234            1+2+3+4+5                              15
      2345 123/678/789/456/234/890/567    1+2+3+4+5+6+7                          28
      2383 154/85/256                     7+9+10                                 26
      3456 123/678/789/456/234/567        1+2+3+4+5+7                            22
      5656 3/799                          10+15                                  25

6 rows selected.

SQL> def TOT=30
SQL> /
       PID CHEST_PATH                     SUM_KGS                           TOT_KGS
---------- ------------------------------ ------------------------------ ----------
       135 201/15/40                      6+9+15                                 30
      1225 123/678/789/456/234            1+2+3+4+5                              15
      2345 123/678/789/456/234/890/567    1+2+3+4+5+6+7                          28
      2383 154/85/256                     7+9+10                                 26
      3456 123/678/789/456/234/567        1+2+3+4+5+7                            22
      5656 3/799                          10+15                                  25

6 rows selected.

SQL> def TOT=8
SQL> /
       PID CHEST_PATH                     SUM_KGS                           TOT_KGS
---------- ------------------------------ ------------------------------ ----------
       135 409                            8                                       8
      1225 123/678/234                    1+2+5                                   8
      2345 123/678/234                    1+2+5                                   8
      2383 154                            7                                       7
      3456 123/678/234                    1+2+5                                   8

5 rows selected.

SQL> col chest_path format a30
SQL> col sum_kgs    format a30
SQL> def TOT=23
SQL> with
  2    data as ( -- number the chest from smallest to biggest per PID
  3              -- keep only the chests smaller than the given &TOT
  4      select pid, chest_no, chest_kgs,
  5             row_number() over (partition by pid order by chest_kgs, chest_no) rn
  6      from stock
  7      where chest_kgs <= &TOT
  8    ),
  9    all_possible_combinations as (
 10      select pid,
 11             substr(sys_connect_by_path(chest_no,'/'),2) chest_path,
 12             substr(sys_connect_by_path(rn,'/'),2) rn_path,
 13             substr(sys_connect_by_path(chest_kgs,'+'),2) sum_kgs,
 14             level lvl
 15      from data
 16      connect by prior pid = pid
 17          and prior rn < rn
 18    ),
 19    all_combinations_for_&TOT as (
 20      -- number the combination from the best fit to the worst one for each PID
 21      select pid, chest_path, sum_kgs,
 22             to_number(xmlquery(sum_kgs returning content).getstringval()) tot_kgs,
 23             row_number() over
 24               (partition by pid
 25                order by to_number(xmlquery(sum_kgs returning content).getstringval()) desc,
 26                         lvl desc, rn_path) rn
 27      from all_possible_combinations
 28      where to_number(xmlquery(sum_kgs returning content).getstringval()) <= &TOT
 29    )
 30  -- select the best ones for each PID
 31  select pid, chest_path, sum_kgs, tot_kgs
 32  from all_combinations_for_&TOT
 33  where rn = 1
 34  order by pid
 35  /
       PID CHEST_PATH                     SUM_KGS                           TOT_KGS
---------- ------------------------------ ------------------------------ ----------
       135 201/409/15                     6+8+9                                  23
      1225 123/678/789/456/234            1+2+3+4+5                              15
      2345 123/678/789/456/890/567        1+2+3+4+6+7                            23
      2383 101/154/256                    6+7+10                                 23
      3456 123/678/789/456/234/567        1+2+3+4+5+7                            22
      5656 799                            15                                     15

6 rows selected.

SQL> def TOT=25
SQL> /
       PID CHEST_PATH                     SUM_KGS                           TOT_KGS
---------- ------------------------------ ------------------------------ ----------
       135 15/40                          9+15                                   24
      1225 123/678/789/456/234            1+2+3+4+5                              15
      2345 123/678/456/234/890/567        1+2+4+5+6+7                            25
      2383 101/85/256                     6+9+10                                 25
      3456 123/678/789/456/234/567        1+2+3+4+5+7                            22
      5656 3/799                          10+15                                  25

6 rows selected.

SQL> def TOT=28
SQL> /
       PID CHEST_PATH                     SUM_KGS                           TOT_KGS
---------- ------------------------------ ------------------------------ ----------
       135 15/40                          9+15                                   24
      1225 123/678/789/456/234            1+2+3+4+5                              15
      2345 123/678/789/456/234/890/567    1+2+3+4+5+6+7                          28
      2383 154/85/256                     7+9+10                                 26
      3456 123/678/789/456/234/567        1+2+3+4+5+7                            22
      5656 3/799                          10+15                                  25

6 rows selected.

SQL> def TOT=30
SQL> /
       PID CHEST_PATH                     SUM_KGS                           TOT_KGS
---------- ------------------------------ ------------------------------ ----------
       135 201/15/40                      6+9+15                                 30
      1225 123/678/789/456/234            1+2+3+4+5                              15
      2345 123/678/789/456/234/890/567    1+2+3+4+5+6+7                          28
      2383 154/85/256                     7+9+10                                 26
      3456 123/678/789/456/234/567        1+2+3+4+5+7                            22
      5656 3/799                          10+15                                  25

6 rows selected.

SQL> def TOT=8
SQL> /
       PID CHEST_PATH                     SUM_KGS                           TOT_KGS
---------- ------------------------------ ------------------------------ ----------
       135 409                            8                                       8
      1225 123/678/234                    1+2+5                                   8
      2345 123/678/234                    1+2+5                                   8
      2383 154                            7                                       7
      3456 123/678/234                    1+2+5                                   8

5 rows selected.

The differences are line 14 (of 2nd query) and lines 25/26 where level/lvl, giving the number of chests in the set, is introduced.

[Updated on: Sat, 21 June 2014 08:52]

Report message to a moderator

Re: Help Required in SQL Analytic Function. [message #616991 is a reply to message #616845] Tue, 24 June 2014 00:12 Go to previous messageGo to next message
jimit_shaili
Messages: 237
Registered: June 2006
Location: India, Ahmedabad
Senior Member
Dear Michel,

You are really the rock star of this forum, absolute perfect solution to problem. Still need to learn more about xmlquery feature before implementing on real scenario. Good support & great solution.

Regards

Jimit

Report message to a moderator

Re: Help Required in SQL Analytic Function. [message #616992 is a reply to message #616991] Tue, 24 June 2014 00:18 Go to previous message
Michel Cadot
Messages: 68647
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

In this case the xmlquery expression is just the equivalent of "eval" function in some language: it takes a string and computes it.

[Updated on: Tue, 24 June 2014 00:18]

Report message to a moderator

Previous Topic: Exchange partition from table at remote database
Next Topic: can we use 'Select' in IF statement
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Fri Apr 26 21:47:34 CDT 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.