on 07-05-2016 4:51 PM
Hi,
We are developing a report to see a car part number selling status for eg: Fast Moving, Medium Moving or Slow Moving based on business defined logic. The logic is as below.
1) Count how many sale order lines the parts are
2) Find the percentage of sale order line of each part against total number of sale order lines
3) Sort the result descending based on percentage, and mark first 80 percentage parts as Fast moving, next 15 % as medium and next 5% as Slow moving.
I can bring below result using analytical view based on sale order line item table.
But i need "Speed" Column to be calculated based on the above logic.
I am thinking of using Cursor in Scripted Calculation view to loop through the each and every record in the table and sum the percentage in each and every row in a sum variable and mark as "Fast" till it reaches 80 if it reaches 80 then clear the sum variable, then again mark as Medium till it reaches 15 then clear the variable, then mark rest as "Slow".
Please let me know if this is right and best approach. As per my knowledge it is not possible to achieve this in Graphical view, please correct me if i am wrong. Is there any other way i can use in scripted calc view to achieve this.
Thanks
Rithika
Hi Rithika,
This can be calculated using scripted Calc View / Stored Procedure (Can be called from Scripted CV)
without writing cursor.
As It it not involve any looping, It will be good in terms of performance.
Analytic view Result:
Logic to calculated Speed Type:
Create Procedure Proc_SPEEDTYPE_CALC (OUT OP_TT_FINAL_RESULT "INF713775"."JUL7_TT_1")
LANGUAGE SQLSCRIPT
SQL SECURITY INVOKER
As
BEGIN
Declare OP Integer;
Declare OP_FAST Integer;
Declare OP_MEDIUM Integer;
Declare OP_SLOW Integer;
Declare OP_TT_FAST "INF713775"."JUL7_TT_1";
Declare OP_TT_MEDIUM "INF713775"."JUL7_TT_1";
Declare OP_TT_SLOW "INF713775"."JUL7_TT_1";
Select count(*) into OP from Jul7_Speed;
OP_FAST = Round (0.8 * OP);
OP_MEDIUM = Round (0.15 * OP);
OP_SLOW = Round (0.05 * OP);
OP_TT_FAST = Select PartNumber, Sale_Order_Line_Count, Percentage, 'FAST' As Speed from Jul7_Speed Order By Percentage desc Limit :OP_FAST;
OP_TT_MEDIUM = Select PartNumber, Sale_Order_Line_Count, Percentage, 'MEDIUM' As Speed from Jul7_Speed Order By Percentage desc Limit :OP_MEDIUM OFFSET :OP_FAST;
OP_TT_SLOW = Select PartNumber, Sale_Order_Line_Count, Percentage, 'SLOW' As Speed from Jul7_Speed Order By Percentage Limit :OP_SLOW;
OP_TT_FINAL_RESULT = Select * from :OP_TT_FAST UNION Select * from :OP_TT_MEDIUM UNION Select * from :OP_TT_SLOW;
END
The Final Output:
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi Muthuram,
Thank you for the analysis. But this one will not give expected result. Below part in your code
Select count(*) into OP from Jul7_Speed;
OP_FAST = Round (0.8 * OP);
OP_MEDIUM = Round (0.15 * OP);
OP_SLOW = Round (0.05 * OP)
It will give percentage of line count in the total number of lines for eg if total number of lines is 13 then 80% of 13 lines is 10 rows, 15 percentage is 2 rows, 5% is remaining rows.
But what i am looking for is i want to find out first set of rows which has total sum of 80 in percentage col (0 - 80 %) , this set will be marked as FAST and next set rows contributes to sum of 15 in percentage col(81% to 95%) , this set will be marked as Medium and (96% to 100%) which is 5% marked as SLOW.
Thanks
Rithika
Hi Rithika,
I hope you expecting like this.
Create Procedure Proc_SPEEDTYPE_CALC_2 (OUT OP_TT_FINAL_RESULT "INF713775"."JUL7_TT_2")
LANGUAGE SQLSCRIPT
SQL SECURITY INVOKER
As
BEGIN
OP_TT_FINAL_RESULT = Select PartNumber, Sale_Order_Line_Count, Percentage,
Sum(Percentage) OVER (order by PERCENTAGE desc, PartNumber) As CUMULATIVE_COUNT,
Case
When Sum(Percentage) OVER (order by PERCENTAGE desc, PartNumber) <= 80 Then
'FAST'
When Sum(Percentage) OVER (order by PERCENTAGE desc, PartNumber) > 80 AND Sum(Percentage) OVER (order by PERCENTAGE desc, PartNumber) <= 95 Then
'MEDIUM'
Else
'SLOW'
End
As Speed
from Jul7_Speed;
END
-----------------------------------------------------------------------------
Table Type:
Create Type "INF713775"."JUL7_TT_2" As Table
(PARTNUMBER Varchar(10),
SALE_ORDER_LINE_COUNT Integer,
PERCENTAGE Integer,
CUMULATIVE_COUNT Integer,
SPEED Varchar(10))
-------------------------------------------------------------------------
Call Proc_SPEEDTYPE_CALC_2 (?)
Hope, It would meet your requirement
Regards,
Muthuram
Maybe I'm not quite getting what you want to do, but to me this reads like you want to look into PERCENTILE functions:
with base as (select part_no, cnt,
(select sum(cnt) from part_cnts) tot_cnt,
round(( cnt/((select sum(cnt) from part_cnts)/100)),2) perc_of_tot_cnt,
sum(cnt) over (order by cnt desc) cumm_sum
from part_cnts
order by cnt desc)
select part_no, cnt, tot_cnt, perc_of_tot_cnt, cumm_sum,
round(percentile_cont (0.8) within group (order by cumm_sum asc) over() ,2 )thres_08,
round(percentile_cont (0.95) within group (order by cumm_sum asc) over(),2) thres_095,
case
when percentile_cont (0.8) within group (order by cumm_sum asc) over() >= cumm_sum then
'FAST'
when percentile_cont (0.95) within group (order by cumm_sum asc) over() >= cumm_sum then
'MEDIUM'
else 'SLOW'
end grp
from base
order by cnt desc;
See infos here SAP HANA Academy - SQL Functions: PERCENTILE_CONT and PERCENTILE_DISC - YouTube and here http://help.sap.com/saphelp_hanaplatform/helpdata/en/20/a353327519101495dfd0a87060a0d3/content.htm.
This approach doesn't use the percentage calculation for the classification (but I left it in for you to compare). The idea here is to first do a cummulative sum on the cnts and to calculate the threshold values for your desired percentages (aka percentiles).
Now, all you've got to do is to check whether the thresholds are met and assign the class.
Hi Muthuram,
You are a star . Your solution solved my issue. But i am interested to know how this window function OVER() works, how it adds the previous record with current record and is this part number in the following statement is partition by (order by PERCENTAGE desc, PartNumber).
Thanks a lot.
Regards,
Rithika
Hi Rithika,
Window Function --> Sum(Percentage) OVER (Order by PERCENTAGE)
will calculate cumulative sum by column Percentage.
As per your requirement, you are calculating First 80% which part number have High Percentages.
So I used the Function.
Sum(Percentage) OVER (Order by PERCENTAGE desc)
If you note down the results,
cumulative count is calculated first record itself as 42 (summing up three 14)
So I included the Key Partnumber as well in the function
Sum(Percentage) OVER (Order by PERCENTAGE desc, PartNumber)
and hence It is giving correct results.
To know more about window Function,
Please refer the link.
Window Functions - SAP HANA SQL and System Views Reference - SAP Library
Regards,
Muthuram
Hello Rithika,
I'm guessing you don't actually need cursor for what you're trying to achieve as you can try using window functions to help you with that. I believe using WITH clause might be a way out as well. Hopefully I understood your scenario the right way.
I don't have your data but using the classic abap sflight data I could come up with something that you can translate to your reality and evaluate if it makes sense or not. If that's the best approach I can't confirm. I'd avoid cursors though.
Anyhow, the idea was to use table SBOOK as input and play around with the FLDATE column (which you can use it as your part number column) and I counted the number of bookids for each one of the calendar month (essentially yyyymm from FLDATE).
After calculating the individual counts (on variable v1), the total counts (on variable v2) and the percentage (on varibale v3) you can use a percent_rank window function over the percentage calculated before in order to evaluate where does the value fit (the first 80% of results, the next 15% and the remaining 5%):
DO BEGIN
v1 = select left(replace(FLDATE,'-',''),6) as calmonth, count(BOOKID) as individual_cnt
from SAPABAP1.SBOOK group by left(replace(FLDATE,'-',''),6);
v2 = select sum(individual_cnt) as total from :v1;
v3 = select a.CALMONTH, a.INDIVIDUAL_CNT, b.total, round(to_decimal(a.INDIVIDUAL_CNT/b.total)*100,4) as perc
from :v1 a cross join :v2 b;
select CALMONTH, INDIVIDUAL_CNT, PERC, RANK,
(CASE WHEN RANK<=0.8 THEN 'Fast'
WHEN RANK > 0.8 and RANK <= 0.95 THEN 'Medium'
ELSE 'Slow' END) as RANK_STR
from (select CALMONTH, INDIVIDUAL_CNT, PERC, percent_rank() over (order by perc desc) as rank
from :v3 order by perc desc);
END;
This will result in something like:
CALMONTH;INDIVIDUAL_CNT;PERC ;RANK ;RANK_STR
201307 ;261595 ;0.6262;0.0 ;Fast
201308 ;261494 ;0.626 ;0.0055248618784530384;Fast
200608 ;261482 ;0.626 ;0.0055248618784530384;Fast
200307 ;261419 ;0.6258;0.016574585635359115 ;Fast
[...]
200909 ;229991 ;0.5506;0.7955801104972375 ;Fast
201409 ;229972 ;0.5505;0.8121546961325967 ;Medium
201406 ;229940 ;0.5504;0.8176795580110497 ;Medium
200806 ;229872 ;0.5503;0.8232044198895028 ;Medium
201209 ;229796 ;0.5501;0.8287292817679558 ;Medium
[...]
201607 ;180380 ;0.4318;0.9447513812154696 ;Medium
201606 ;120685 ;0.2889;0.9502762430939227 ;Slow
201603 ;85959 ;0.2057;0.9558011049723757 ;Slow
[...]
199504 ;1 ;0 ;0.9834254143646409 ;Slow
Took a little less than 5s to finish the results for a 4M record table on SP12. I didn't spent too much time looking at resource consumption for this query but it might be a greedy one.
I tried to think on the graphical way of doing that but so far could not think of anything easy.
I suggest you to check WITH clause and other window functions that might be worth using here.
ps.: I guess you can use SUM as window function as well and re-use one of the other window functions here possibly subquerying your way to one single query. Haven't spent much time on it though.
BRs,
Lucas de Oliveira
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Well actually you can lower that down to a single (&sad) select SQL by using SUM as a window function as well:
select
CALMONTH,
INDIVIDUAL_CNT,
TOTAL,
PERCENTAGE,
RANK,
(CASE WHEN RANK<=0.8 THEN 'Fast'
WHEN RANK > 0.8 and RANK <= 0.95 THEN 'Medium'
ELSE 'Slow'
END) as RANK_STR
from (select
CALMONTH,
INDIVIDUAL_CNT,
TOTAL,
round(to_decimal(INDIVIDUAL_CNT/total)*100, 4) PERCENTAGE,
percent_rank() over (order by round(to_decimal(INDIVIDUAL_CNT/total)*100,4) desc) as rank
from (select
CALMONTH,
INDIVIDUAL_CNT,
SUM(INDIVIDUAL_CNT) over () as total
from (
select left(replace(FLDATE,'-',''), 6) as calmonth,
count(BOOKID) as individual_cnt from SAPABAP1.SBOOK
group by left(replace(FLDATE,'-',''),6))))
Performance was actually good (considering I'm using a single small table and not a complex view): total execution of 3.8s with 2.9s on JECalculate (replace and left function on the innermost select query). So if you don't have to calculate anything it should be wa(a)aay faster. It also consumed 3.6GB to execute it in my case:
ps.: Still haven't tried with WITH clause.
BRs,
Lucas de Oliveira
You can abuse window functions a bit more and actually lower that down to the following:
select *, (CASE
WHEN RANK_100 <=80.00 THEN 'Fast'
WHEN RANK_100 > 80.00 and RANK_100 <= 95.00 THEN 'Medium'
ELSE 'Slow'
END) as RANK_STR from (
select
FLDATE,
count(BOOKID) as individual_cnt,
sum(count(BOOKID)) over() as total_cnt,
round(to_decimal((count(BOOKID)/sum(count(BOOKID)) over())*100),2) as contrib,
percent_rank() over(order by count(BOOKID) desc) as rank,
round(percent_rank() over(order by count(BOOKID) desc)*100,1,ROUND_HALF_UP) as rank_100
from SAPABAP1.SBOOK
group by FLDATE)
(I saved the CALMONTH thing this time for the sake of simplicity)
BRs,
Lucas de Oliveira
Hi Lucas,
Thanks a lot for your effort and time on this. I think you have understood this scenario in different way. Please check the below example
I don't want to check individual percentage of a Part number. Cumulatively if parts percentage sum which gives first 80% then it is FAST, Cumulatively next 15% of sum is Medium, And next 5% is SLOW. That is why i want percentage of previous record to be added to current record till it reaches first 80 percentage, then it should be cleared to sum next 15% sum. Business want to see group of parts which contributes to FAST, Medium and SLOW.
Please let me know if you are clear with this example and let me know the possible way to do it if Cursor is not the good option.
Thanks,
Rithika
User | Count |
---|---|
84 | |
24 | |
12 | |
9 | |
7 | |
6 | |
5 | |
5 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.