Is there any way in SAP HANA SQL to calculate % without loop operation in iterations?

Question

I have an input table as below with item and amount columns. And also have Low% (5%) and High% (50%) constant values.

Need to derive % of "amount" by "total amount" and if % is less than Low% or greater than high% then need to ignore those records and calculate % again for remaining rows by taking total amount of those rows.

Can I achieve this without using Loop operation in SAP HANA SQL?

Answer 1

This is doable with most current SQL RDBMS. What is needed for this solution are common table expressions (CTE/ "WITH" clause) and window functions.

Given these, we can do the following:

select current_time, * from m_database;

/*
CURRENT_TIME    SYSTEM_ID   DATABASE_NAME   HOST    START_TIME                  VERSION                 USAGE      
12:17:48 PM     HXE         HXE             hxehost 02/12/2019 12:12:06.815 PM  2.00.040.00.1553674765  DEVELOPMENT
*/

create column table item_amounts (item nvarchar(10) not null
                                 , amount integer not null);


insert into item_amounts values ('A', 10 ); 
insert into item_amounts values ('A', 20 ); 
insert into item_amounts values ('A', 30); 
insert into item_amounts values ('A', 40); 
insert into item_amounts values ('A', 50); 
insert into item_amounts values ('A', 60); 
insert into item_amounts values ('A', 70); 
insert into item_amounts values ('A', 80); 
insert into item_amounts values ('A', 90); 
insert into item_amounts values ('A', 100); 


select * from item_amounts;
/*
ITEM    AMOUNT
A       10    
A       20    
A       30    
A       40    
A       50    
A       60    
A       70    
A       80    
A       90    
A       100   
*/


-- first round: total per group

select
    item
   , amount
   , SUM (amount) OVER (PARTITION BY item) as item_total_amount
from
    item_amounts;

/*
ITEM    AMOUNT  ITEM_TOTAL_AMOUNT
A       10      550              
A       20      550              
A       30      550              
A       40      550              
A       50      550              
A       60      550              
A       70      550              
A       80      550              
A       90      550              
A       100     550              
*/  

Note for the OP: it is generally a good idea to provide the test data in an executable form, and not as a screenshot or just a list of text. Something like the above will do.

The first step is to perform the simple "% of total within a group"-calculation. That is a standard requirement and easy to implement with window functions.

Based on the OPs example, this implementation rounds the percentages to full integer values.

select
    item
   , amount
   , SUM (amount) OVER (PARTITION BY item) as item_total_amount
   , round(100.0 * ( amount / SUM (amount) OVER (PARTITION BY item)), 0) as pct_of_item_total
from
    item_amounts;


/*  
ITEM    AMOUNT  ITEM_TOTAL_AMOUNT   PCT_OF_ITEM_TOTAL
A       10      550                 2                
A       20      550                 4                
A       30      550                 5                
A       40      550                 7                
A       50      550                 9                
A       60      550                 11               
A       70      550                 13               
A       80      550                 15               
A       90      550                 16               
A       100     550                 18               
*/

Now, for the second "iteration", we should filter out items with PCT_OF_ITEM_TOTAL between 5 and 50. Based on the new set of items the requirement is to calculate the "% of total within a group"... Here, at the latest, we see that this is the exact same requirement again.

And sure enough, we can implement it with the exact same code. To do that, we place the first iteration into a common table expression (called stage here) and use it at the base table for the second iteration:

with stage as (
    select
        item
       , amount
       , SUM (amount) 
              OVER (PARTITION BY item) as item_total_amount
       , round(100.0 * 
                      ( amount / SUM (amount) 
                                 OVER (PARTITION BY item))
              , 0) as pct_of_item_total
    from
        item_amounts)

select 
      s.item
    , s.amount
    , SUM (s.amount) 
              OVER (PARTITION BY s.item) as item_total_amount
    , round(100.0 * 
                   ( s.amount / SUM (s.amount) 
                              OVER (PARTITION BY s.item))
              , 0) as pct_of_item_total
from 
    stage s
where 
    s.pct_of_item_total between 5 and 50;

/*
ITEM    AMOUNT  ITEM_TOTAL_AMOUNT   PCT_OF_ITEM_TOTAL
A       30      520                 6                
A       40      520                 8                
A       50      520                 10               
A       60      520                 12               
A       70      520                 13               
A       80      520                 15               
A       90      520                 17               
A       100     520                 19               
*/

And that is it already.
No loops required and not even HANA-specific features used.