OLAP Functions Db2 for i: Gimme some more !

0
322

Does anybody remember “1, 2, 3 ,4 gimme some more ” by DDSound , was 1978 and I was a child …

When I tried to use DB2 for i OLAP functions to make a simple ABC Analysis (Pareto) I found out that OVER() work only with ROW_NUMBER(), RANK() and DENSE_RANK(), if I need a SUM() or AVG() or something else I can’t use OVER() !

“Hey Rochester, Gimme some more !”

In other DBs (MSSql too !) you can do something like

Select CustomerId,
       Sales,
       sum(Sales) OVER(order by Sales desc) as CumulativeSales,
       sum(Sales) OVER() as TotalSales,
       case when sum(Sales) OVER(order By Sales desc) / sum(Sales) OVER() <= 0.8 then 'A'
            when sum(Sales) OVER(order By Sales desc) / sum(Sales) OVER() <= 0.9 then 'B'
                 else 'C' end as ClassABC
from mytable

But with DB2 for i we need to use some not so fashion workaround …

let me show an example.

Open your Rdi 9.5 sp3 and “Launc Run SQL Scritps” (read Here if you don’t know this new Rdi feature) or what do you normally use to launch SQL Script, copy and past those rows:

CREATE or REPLACE TABLE qtemp.testinvoice
(InvoiceNr char(15),
 InvoiceDate date,
 CustomerId char(15),
 Totval decimal(11, 2) );

delete from qtemp.testinvoice where 1=1;

Insert into qtemp.testinvoice values('160001', '01/01/2016', '100101', 6000.00);
Insert into qtemp.testinvoice values('160002', '01/03/2016', '100102', 100.00);
Insert into qtemp.testinvoice values('160003', '01/05/2016', '100101', 2000.00);
Insert into qtemp.testinvoice values('160004', '01/05/2016', '100103', 1700.00);
Insert into qtemp.testinvoice values('160005', '01/05/2016', '100104', 1400.00);
Insert into qtemp.testinvoice values('160006', '01/10/2016', '100105', 1800.00);
Insert into qtemp.testinvoice values('160007', '01/12/2016', '100102', 300.00);
Insert into qtemp.testinvoice values('160008', '01/30/2016', '100102', 200.00);
Insert into qtemp.testinvoice values('160009', '01/31/2016', '100103', 660.00);
Insert into qtemp.testinvoice values('160010', '01/31/2016', '100104', 800.00);
Insert into qtemp.testinvoice values('160011', '01/31/2016', '100104', 8800.00);
Insert into qtemp.testinvoice values('160012', '01/31/2016', '100103', 100.00);
Insert into qtemp.testinvoice values('160013', '01/31/2016', '100103', 1030.00);
Insert into qtemp.testinvoice values('160014', '01/31/2016', '100106', 30.00);
Insert into qtemp.testinvoice values('160015', '01/31/2016', '100107', 1230.00);
Insert into qtemp.testinvoice values('160016', '01/31/2016', '100108', 1800.00);
Insert into qtemp.testinvoice values('160017', '01/31/2016', '100109', 800.00);
Insert into qtemp.testinvoice values('160018', '01/31/2016', '100106', 500.00);

Select * from qtemp.testinvoice;

with TotalCustomer as
 (select CustomerId, sum(totval) as Sales
 from qtemp.testinvoice
 group by CustomerId
 order by Customerid)
select CustomerId, Sales, Position, CumulativeSales, TotalSales, 
    cast(CumulativeSales*100.00/TotalSales as Decimal(5, 2)) As Perc,
    case when CumulativeSales/TotalSales<=0.8 then 'A'
         when CumulativeSales/TotalSales<=0.9 then 'B' else 'C' end as ClassABC
from (
select CustomerId, Sales,
 dense_rank() over(order by sales desc) as Position,
 cast((select sum(sales) from TotalCustomer b where b.sales>=a.sales ) as Decimal(11,2))
     as CumulativeSales,
 cast((select sum(sales) from TotalCustomer c) as Decimal(11,2)) 
     as TotalSales
 from TotalCustomer a ) x
 order by Position

I use OVER to number position of my customer, and it works, but I have to use some subquery to calculate TotalSales and CumulativeSales.

This is what you get

CustomerId Sales Position CumulativeSales TotalSales Perc ClassABC
100104 11000.00 1 11000.00 29250.00 37.60 A
100101 8000.00 2 19000.00 29250.00 64.95 A
100103 3490.00 3 22490.00 29250.00 76.88 A
100108 1800.00 4 26090.00 29250.00 89.19 B
100105 1800.00 4 26090.00 29250.00 89.19 B
100107 1230.00 6 27320.00 29250.00 93.40 C
100109 800.00 7 28120.00 29250.00 96.13 C
100102 600.00 8 28720.00 29250.00 98.18 C
100106 530.00 9 29250.00 29250.00 100.00 C

 

Does anybody knows a better way, more elegant ?