Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, Jon.

Asked: September 07, 2016 - 11:44 am UTC

Last updated: August 30, 2022 - 1:33 am UTC

Version: Oracle 12

Viewed 100K+ times! This question is

You Asked

Hi,

I have to calculate accumulated value for a column, but I have to filter some rows (and it depends on that accumulated value).

For example:

CREATE TABLE dummy_table 
(
  var_id      NUMBER(5) PRIMARY KEY,
  prc          number(5),
  qty          number(5),
  v_type     varchar2(1)   
);
Insert into US_GAS.DUMMY_TABLE(VAR_ID, PRC, QTY) Values(6, 15, 40);
Insert into US_GAS.DUMMY_TABLE(VAR_ID, PRC, QTY, V_TYPE) Values (5, 14, 35, 'A');
Insert into US_GAS.DUMMY_TABLE(VAR_ID, PRC, QTY) Values (4, 13, 30);
Insert into US_GAS.DUMMY_TABLE(VAR_ID, PRC, QTY, V_TYPE) Values (3, 12, 25, 'A');
Insert into US_GAS.DUMMY_TABLE(VAR_ID, PRC, QTY) Values (2, 11, 20);
Insert into US_GAS.DUMMY_TABLE (VAR_ID, PRC, QTY) Values (1, 10, 15);
COMMIT;

select prc, qty, v_type,
        SUM (qty) OVER (/*PARTITION BY prc, var_id*/ ORDER BY prc desc, var_id desc ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) accQty
from dummy_table
order by prc desc


With the query you get this:

------------------------------------------
PRC QTY V_TYPE ACCQTY
15 40 40
14 35 A 75
13 30 105
12 25 A 130
11 20 150
10 15 165
------------------------------------------

Now, based on that accumulated value (and the value of V_TYPE), you have to discard the row of PRC = 12 (for example). The output should be this one:

------------------------------------------
PRC QTY V_TYPE ACCQTY
15 40 40
14 35 A 75
13 30 105
11 20 125
10 15 140
------------------------------------------

That is, you need to calculate the acumulated value first, and if you have to discard the row (one or more rows could be discarded), the acumulated value of the rest of rows should be update dinamically.

Can this be done in a select statement? Do I need a function or a procedure?

Thanks

-------------------------------------------------------------------------
Our request for more information:
Thanks for providing a test case. But I don't understand what the rule is. Could you clarify please? Specifically, why do you discard PRC = 12? What's the formula that tells us we can reject it? How do I know which future rows to discard? Please update this question with this information, Thanks,
-------------------------------------------------------------------------


Of course, I will add more information, based on the same data:

------------------------------------------
PRC QTY V_TYPE (ACCQTY)
15 40 40
14 35 A 75
13 30 105
12 25 A 130
11 20 150
10 15 165
------------------------------------------

Where:
PRC : Price
QTY : Quantity
V_TYPE : Can be “null”, or “A”, where “A” means that you can take all the quantity or nothing (All or nothing). Null means that you don’t need to take all Qty.
ACCQty : accumulative quantity

Now you have to take some maxium value of quantity. For example, you only can take 125 (accumulative), and you can’t divide the registers of type “A”. So you have to discard the row with price 12 (because with it you have 130). So I need a select that returns me:

------------------------------------------
PRC QTY V_TYPE ACCQTY
15 40 40
14 35 A 75
13 30 105
11 15 125
------------------------------------------

In the last row (price 11), the Qty sould be put at 15 because you only have 15 left. Can this be done with a select?

Thanks a lot again






and Chris said...

I still don't really understand what the rule is. Why are you excluding PRC = 12 but not PRC = 14? If there's a PRC = 10 of type A, what happens?

Anyway, if you want to exclude them from the running total, you just need to find a way to map quantities to null.

For example:

drop table dummy_table  purge;
CREATE TABLE dummy_table 
(
  var_id      NUMBER(5) PRIMARY KEY,
  prc          number(5),
  qty          number(5),
  v_type     varchar2(1)   
);
Insert into DUMMY_TABLE(VAR_ID, PRC, QTY) Values(6, 15, 40);
Insert into DUMMY_TABLE(VAR_ID, PRC, QTY, V_TYPE) Values (5, 14, 35, 'A');
Insert into DUMMY_TABLE(VAR_ID, PRC, QTY) Values (4, 13, 30);
Insert into DUMMY_TABLE(VAR_ID, PRC, QTY, V_TYPE) Values (3, 12, 25, 'A');
Insert into DUMMY_TABLE(VAR_ID, PRC, QTY) Values (2, 11, 20);
Insert into DUMMY_TABLE (VAR_ID, PRC, QTY) Values (1, 10, 15);
COMMIT;

select prc, qty, v_type, 
       sum(
         case 
           when v_type is null or 
               (v_type = 'A' and rn = 1 ) then qty 
         end
      ) over (order by prc desc, rn) accqty
from (
select var_id, prc, qty, v_type,
       row_number() over (partition by v_type order by prc desc, var_id desc) rn
from   dummy_table
)
order  by prc desc, var_id desc;

PRC  QTY  V_TYPE  ACCQTY  
15   40           40      
14   35   A       75      
13   30           105     
12   25   A       105     
11   20           125     
10   15           140


If you want to remove the uncounted rows from the results, put the conditions from the case in the where clause:

select prc, qty, v_type, 
       sum(
         case 
           when v_type is null or 
               (v_type = 'A' and rn = 1 ) then qty 
         end
      ) over (order by prc desc, rn) accqty
from (
select var_id, prc, qty, v_type,
       row_number() over (partition by v_type order by prc desc, var_id desc) rn
from   dummy_table
)
where  v_type is null or 
       (v_type = 'A' and rn = 1 )
order  by prc desc, var_id desc;

PRC  QTY  V_TYPE  ACCQTY  
15   40           40      
14   35   A       75      
13   30           105     
11   20           125     
10   15           140 


I've guessed that you want to keep the first row with v_type = 'A', but not the others. If this is incorrect, just update the case clause so that it returns null when there's a value you want to skip.

Rating

  (12 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

More information

Jonathan, September 08, 2016 - 12:15 pm UTC

I think I can explain better with code.
I'm trying a select than can do the same as this function:

create or replace package pkg_utl_prb
as
  type test_record is record ( var_id      NUMBER(5), prc          number(5), qty          number(5), v_type     varchar2(1) );
  type test_table is table of test_record;
  function get_test_table(max_qty number) return test_table pipelined;
end;
/

create or replace package body pkg_utl_prb as

    function <b>get_test_table</b>(max_qty number) return test_table pipelined is

        var_accQty number := 0;
        begin
        
            for item in (select * from dummy_table order by prc desc ) loop
            
                if ((item.qty + var_accQty) < max_qty) then     /* Add item */
                
                    var_accQty := var_accQty + item.qty;
                    pipe row (item);
                    
                elsif((item.qty + var_accQty) = max_qty) then   /* Add item and exit */
                
                    var_accQty := var_accQty + item.qty;
                    pipe row (item);
                    return;
                    
                else
                
                    if (item.v_type is null) then                   /* Add item (with the rest of qty)*/
                        item.qty := max_qty - var_accQty;
                        var_accQty := max_qty;
                        pipe row (item);
                        return;
                    else
                        null;                                           /* Item is not added */
                    end if;
                    
                end if;

        end loop;
        return;
    end get_test_table;
end;
/

More information

Jonathan, September 08, 2016 - 12:18 pm UTC

I think I can explain better with code. I'm trying to do in a view the same as this function:

create or replace package pkg_utl_prb
as
  type test_record is record ( var_id      NUMBER(5), prc          number(5), qty          number(5), v_type     varchar2(1) );
  type test_table is table of test_record;
  function get_test_table(max_qty number) return test_table pipelined;
end;
/

create or replace package body pkg_utl_prb as

    function <b>get_test_table</b>(max_qty number) return test_table pipelined is

        var_accQty number := 0;
        begin
        
            for item in (select * from dummy_table order by prc desc ) loop
            
                if ((item.qty + var_accQty) < max_qty) then     /* Add item */
                
                    var_accQty := var_accQty + item.qty;
                    pipe row (item);
                    
                elsif((item.qty + var_accQty) = max_qty) then   /* Add item and exit */
                
                    var_accQty := var_accQty + item.qty;
                    pipe row (item);
                    return;
                    
                else
                
                    if (item.v_type is null) then                   /* Add item (with the rest of qty)*/
                        item.qty := max_qty - var_accQty;
                        var_accQty := max_qty;
                        pipe row (item);
                        return;
                    else
                        null;                                           /* Item is not added */
                    end if;
                    
                end if;

        end loop;
        return;
    end <b>get_test_table</b>;
end;
/

Chris Saxon
September 08, 2016 - 1:37 pm UTC

Right, I think I've got it now. Seems to me the rule is:

- Keep summing quantities up to the max value
- If the running total goes over the max, then:
- If the type = 'A' ignore it and process the next row
- Otherwise return the quantity needed to bring the total up to the max

If so, you can do this with recursive subqueries. These will keep a running total according to the rules above.

case 
  when t.tot + d.qty > :mx and d.v_type is null then :mx - t.tot
  else d.qty
end qty, 
case 
 -- sum up to the max
 when t.tot + d.qty <= :mx then
   t.tot + d.qty
 -- or skip if type = 'A'
 when d.v_type = 'A' then
   t.tot
 -- otherwise return the max
 else
   :mx
end


var mx number;
exec :mx := 100;

select t.*
from   table(pkg_utl_prb.get_test_table(:mx)) t; 

VAR_ID  PRC  QTY  V_TYPE  
6       15   40           
5       14   35   A       
4       13   25 

with tots (var_id, prc, qty, type, tot, stop_cont) as (
  select * from (
    select var_id, prc, 
           case 
             when d.qty > :mx and d.v_type is null then to_number(:mx)
             else d.qty
           end qty, v_type, qty tot, 
           case 
             when d.qty > :mx and d.v_type is null then 'STOP'
             else 'CONTINUE'
           end stop_cont
    from   dummy_table d
    order  by prc desc
    fetch first 1 rows only
  )
  union all
  select d.var_id, d.prc, 
         case 
           when t.tot + d.qty > :mx and d.v_type is null then :mx - t.tot
           else d.qty
         end qty, 
         d.v_type, 
         case 
           when t.tot + d.qty <= :mx then
             t.tot + d.qty
           when d.v_type = 'A' then
             t.tot
           else
             to_number(:mx)
         end tot,
         case 
           when t.tot = :mx then
             'STOP'
           when t.tot + d.qty > :mx and d.v_type = 'A' then
             'STOP'
           else
             'CONTINUE'
         end stop_cont
  from   tots t
  join   dummy_table d
  on     t.prc - 1 = d.prc
  where  t.tot <= :mx or d.v_type = 'A'
)
  select * from tots
  where  stop_cont = 'CONTINUE';

VAR_ID  PRC  QTY  TYPE  TOT  STOP_CONT  
6       15   40         40   CONTINUE   
5       14   35   A     75   CONTINUE   
4       13   25         100  CONTINUE 

exec :mx := 125;

select t.*
from   table(pkg_utl_prb.get_test_table(:mx)) t; 

VAR_ID  PRC  QTY  V_TYPE  
6       15   40           
5       14   35   A       
4       13   30           
2       11   20

with tots (var_id, prc, qty, type, tot, stop_cont) as (
  select * from (
    select var_id, prc, 
           case 
             when d.qty > :mx and d.v_type is null then to_number(:mx)
             else d.qty
           end qty, v_type, qty tot, 
           case 
             when d.qty > :mx and d.v_type is null then 'STOP'
             else 'CONTINUE'
           end stop_cont
    from   dummy_table d
    order  by prc desc
    fetch first 1 rows only
  )
  union all
  select d.var_id, d.prc, 
         case 
           when t.tot + d.qty > :mx and d.v_type is null then :mx - t.tot
           else d.qty
         end qty, 
         d.v_type, 
         case 
           when t.tot + d.qty <= :mx then
             t.tot + d.qty
           when d.v_type = 'A' then
             t.tot
           else
             to_number(:mx)
         end tot,
         case 
           when t.tot = :mx then
             'STOP'
           when t.tot + d.qty > :mx and d.v_type = 'A' then
             'STOP'
           else
             'CONTINUE'
         end stop_cont
  from   tots t
  join   dummy_table d
  on     t.prc - 1 = d.prc
  where  t.tot <= :mx or d.v_type = 'A'
)
  select * from tots
  where  stop_cont = 'CONTINUE';

VAR_ID  PRC  QTY  TYPE  TOT  STOP_CONT  
6       15   40         40   CONTINUE   
5       14   35   A     75   CONTINUE   
4       13   30         105  CONTINUE   
2       11   20         125  CONTINUE


The stop_cont column is so you can exclude rows that should be in the total - those once the max is reached or type A's that exceed this.

Thank you

Jonathan, September 09, 2016 - 6:48 am UTC

Thank you very much. I have to study it, because I don’t understand it yet.

The query doesn’t work for me (ORA-00907, and it’s because of the FETCH line).
And I see a problem when you put this:

on     t.prc - 1 = d.prc


Because the column PRC can have any value, and you can have more than one row with the same PRC (it’s a price). You have to order the results by PRC, but it can be any price.

Thanks again, I think a have a good point to start.

Connor McDonald
September 09, 2016 - 7:54 am UTC

OK. One way around this may be to assign a number to each row first, for example:
row_number() over (order by prc desc) rn

You can then use this to walk down the rows instead of the price.

Thanks!

Jonathan, September 09, 2016 - 11:58 am UTC

Thanks! I think now I have it!

With 12c, don't forget MATCH_RECOGNIZE

Stew Ashton, September 11, 2016 - 3:04 pm UTC

select * from dummy_table
match_recognize(
  order by prc desc
  measures least(125, sum(v.qty)) tot
  all rows per match
  pattern( ^( {-a-} | v )+ )
  define a as v_type = 'A' and sum(qty) > 125,
    v as sum(v.qty) - qty < 125
);

PRC  TOT  VAR_ID QTY  V_TYPE
---  ---  ------ ---  ------
15   40   6      40   
14   75   5      35   A
13   105  4      30   
11   125  2      20
The "pattern" in human language is:

- Start from the beginning (^)
- Match one or more rows (+), each row being either an A or a V ( A|V )
- But don't show the A rows in the output ( {-A-} )
- If a row has v-type 'A' and puts the total over 125, it belongs to the A subset
- Otherwise it belongs to the V subset, as long as the total has not already reached 125.

In the MEASURES clause, only the V subset is summed, and the sum is a running total.

Asim, July 24, 2022 - 10:16 am UTC

Is this last solution is an SQL query or CQL?
Chris Saxon
July 25, 2022 - 1:26 pm UTC

Which last solution? Stew's match_recognize query? That's valid (Oracle) SQL

from model clause

Rajeshwaran, Jeyabal, July 26, 2022 - 2:05 pm UTC

some how the explanation provided above was still unclear for me, but based on the explanation from Stew's Match recognize code, could see that this seems to be a scenario for inter-row calculation's - if my understanding was true, then we can approach this using MODEL clause also. like this.

SQL> variable n number
SQL> exec :n := 125;

PL/SQL procedure successfully completed.

SQL> select x,var_id,qty,v_type,tot
  2  from (
  3  select * from t
  4  model
  5      dimension by ( row_number() over(order by prc desc) x)
  6      measures( var_id,qty,v_type,0 tot,cast(null as varchar2(5)) as marker )
  7      rules(
  8          tot[any] order by x = case when cv(x) = 1 then qty[cv()]
  9                      when qty[cv()] + tot[cv(x)-1] <= :n then qty[cv()] + tot[cv(x)-1]
 10                      when qty[cv()] + tot[cv(x)-1] > :n and v_type[cv(x)] = 'A' then tot[cv(x)-1]
 11                  end ,
 12          marker[any] order by x = case when cv(x) = 1 then '<---'
 13                  when qty[cv()] + tot[cv(x)-1] <= :n then '<---'
 14                  when qty[cv()] + tot[cv(x)-1] > :n and v_type[cv(x)] = 'A' then null end
 15              )
 16      )
 17  where marker is not null
 18  order by var_id desc
 19* /

   X    VAR_ID    QTY V_TYPE       TOT
____ _________ ______ _________ ______
   1         6     40               40
   2         5     35 A             75
   3         4     30              105
   5         2     20              125

Chris Saxon
July 26, 2022 - 4:01 pm UTC

You could; I'm not sure this is better than any of the other proposed solutions though

CQL

Asim, August 12, 2022 - 6:45 pm UTC


Pleaer refer to the post of Asim on 24 July 2022

and see this

https://docs.oracle.com/en/middleware/fusion-middleware/osa/19.1/cqlreference/pattern-recognition-match_recognize.html#GUID-34D4968E-C55A-4BC7-B1CE-C84B202217BD

It seems MATCH_RECOGNIZE is CQL not SQL

Please clarify
Connor McDonald
August 15, 2022 - 5:10 am UTC

Check out ISO/IEC 9075:2016


May be 2 match recognize clauses

Asim, August 12, 2022 - 6:56 pm UTC


Or is it like there are two seperate MATCH_RECOGNIZE clsuees 1 is SQL's other is CQL's MATCH_RECOGNIZE?

Connor McDonald
August 15, 2022 - 5:10 am UTC

MATCH_RECOGNIZE is definitely in the SQL standard

Cant get it

Asim, August 15, 2022 - 5:47 pm UTC

Then why in the above link Oracle docs says this

13.1 Understanding Pattern Recognition With MATCH_RECOGNIZE
The MATCH_RECOGNIZE clause performs pattern recognition in an Oracle CQL query. This query will export (make available for inclusion in the SELECT) the MEASURES clause values for events (tuples) that satisfy the PATTERN clause regular expression over the DEFINE clause conditions.
Connor McDonald
August 17, 2022 - 4:20 am UTC

Hmm

Asim, August 17, 2022 - 10:27 pm UTC


So does that means there are 2 match_recognize clauses, one is SQL other is CQL

Different syntax

Asim, August 29, 2022 - 1:14 am UTC

Yes its written there that its based on SQL, but arent the syntaxes of MATCH_RECOGNIZE in SQL language reffrrence and in CQL document a bit different
Connor McDonald
August 30, 2022 - 1:33 am UTC

Given that CQL and SQL are different languages, then I can think we safely assume there might be differences

More to Explore

Analytics

Analytic SQL got you confused? Check out Connor McDonald's complete video course.