-- analytic_functions.sql
set echo on
alter session set nls_language=english;
set pause off
set pagesize 100
set linesize 120
spool analytic_functions.lst
-- +version+
select * from v$version;
--- set up environment
-- +setup+
-- product table
drop table prod;
create table prod as
select product_id, month_id,
-- an unusual way to define DATE column containing all NULLs
case when 1=0 then sysdate else to_date(null) end month, 
0 qty, 0 price, '              '||to_char(NULL) product_desc
from
(select rownum product_id from dba_objects where rownum < 5) pr,
(select rownum month_id from dba_objects where rownum < 13) pr
where --- product 4 has some holes
NOT (product_id = 4 and month_id in (1,4,5,8,10));
---
-- define product description
update prod set product_desc = 'linear' where product_id = 1;
update prod set product_desc = 'geometric' where product_id = 2;
update prod set product_desc = 'very stable' where product_id = 3;
update prod set product_desc = 'lineal sparce' where product_id = 4;
---
--- set qty
--
update prod set qty = 1 + (2*(month_id-1)),
price = 10 - (0.2*(month_id-1))
where product_id = 1;
--
update prod set qty = 1 + power(2,(month_id-1)),
price = round(1000  / power(2,(month_id-1)),2)
where product_id = 2;
--
update prod set qty = 10 + trunc(dbms_random.value(0,3))-1,
price = 10 - trunc(dbms_random.value(0,3))-1
where product_id = 3;
---
update prod set qty = month_id,
price = 10 + month_id
where product_id = 4;
-- and time dimension lookup
drop table month;
create table month
(month_id number,
 month_first_date date,
 month_desc varchar2(30),
 year number);
--- insert full year 2003
insert into month
select rownum month_id,
to_date('01-'||to_char(rownum,'09')||'-2003','dd-mm-yyyy'),
to_char(to_date('01-'||to_char(rownum,'09')||'-2003','dd-mm-yyyy'),'MONTH','NLS_DATE_LANGUAGE = english') month_desc,
2003 year
from prod where rownum < 13;
-- and start of 2004
insert into month
select 12+rownum month_id,
to_date('01-'||to_char(rownum,'09')||'-2004','dd-mm-yyyy'),
to_char(to_date('01-'||to_char(rownum,'09')||'-2003','dd-mm-yyyy'),'MONTH','NLS_DATE_LANGUAGE = english') month_desc,
2004 year
from prod where rownum < 3;
---
--- add MONTH column to product table
alter table month 
ADD CONSTRAINT month_pk1 PRIMARY KEY (month_id) -- required for UJV
;
-- init month in product
UPDATE 
(SELECT p.month month, m.month_first_date from prod p, month m where p.month_id = m.month_id)
set month = month_first_date;
commit;
-- +prods+
select * from prod order by product_id, month_id;
-- +months+
select * from month order by month_id;
--- +rollup+
--- ROLLUP qty and amount per quarter and product
select
decode(GROUPING(to_char(month,'YYYY-Q')),1,'All Quarters',
       to_char(month,'YYYY-Q')) as month,
decode(GROUPING(product_desc),1,'All Products',product_desc) as product,
sum(qty) qty, sum(qty * price) amount
from prod
group by ROLLUP (to_char(month,'YYYY-Q'), product_desc);
-- +cube+
--- CUBE qty and amount per quarter and product
select
decode(GROUPING(to_char(month,'YYYY-Q')),1,'All Quarters',
       to_char(month,'YYYY-Q')) as month,
decode(GROUPING(product_desc),1,'All Products',product_desc) as product,
sum(qty) qty, sum(qty * price) amount
from prod
group by CUBE (to_char(month,'YYYY-Q'), product_desc);
-- +cube2+
--- CUBE qty and amount per quarter and product - subtotals only
select
decode(GROUPING(to_char(month,'YYYY-Q')),1,'All Quarters',
       to_char(month,'YYYY-Q')) as month,
decode(GROUPING(product_desc),1,'All Products',product_desc) as product,
sum(qty) qty, sum(qty * price) amount
from prod
group by CUBE (to_char(month,'YYYY-Q'), product_desc)
having GROUPING(to_char(month,'YYYY-Q')) + GROUPING(product_desc) >= 1;
-- +runsum+
---- runing sums (balance)
select 
month, product_desc, 
qty,
sum(qty) over(partition by month) total_month, -- total qty in month
sum(qty) over(order by month) running_total,   -- total qty from the beginning
sum(qty) over (partition by product_desc order by month) running_total_product
-- total per product from beginning
from prod
order by product_desc,month;
-- +perc+
--- percentage and additive percetage
select 
month, product_desc, qty, 
round(rtr*100,2) as perc_total,round(rtr_sum*100,2) as perc_r_total,
round(rtr_prod*100,2) as perc_prod,round(rtr_prod_sum*100,2) as perc_prod_r_total
from (
select 
month, product_desc, 
qty, rtr,
--- 
-- additive percentage from the beginning
sum(rtr) over(order by product_desc, month) rtr_sum, 
rtr_prod,
sum(rtr_prod) over(partition by product_desc order by month) rtr_prod_sum
from (
select 
month, product_desc, 
qty,
-- percentage of qty in total qty of the two products
RATIO_TO_REPORT(qty) over() rtr, 
-- percentage of qty in total qty per product
RATIO_TO_REPORT(qty) over(partition by product_desc)  rtr_prod
from prod
where product_desc IN ('geometric','linear')
))
order by product_desc, month;
-- interpretation of the colums
-- QTY - quantity o fteh prduct sold in the month
-- perc_total - QTY / total sale of this product in percent
-- perc_r_total - sum of perc_total form the beginning
-- perc_prod - QTY / total sale of both selected products in percent
-- perc_prod_r_total - sum of perc_prod form the beginning
--
-- +perctriv+
-- percentage trivial computation
select
product_desc, month, qty,
round(100 * qty / sum(qty) over(partition by product_desc),3) as prod_proc
from prod
order by product_desc, qty;
-- +rank+
--- ranking
--- top 5 months per product
select * from (
select
month, product_desc,qty * price as amount,
RANK() OVER (partition by product_desc order by (qty * price) desc) as prod_rank
from prod
) 
where prod_rank<=5
order by product_desc, prod_rank;
-- +morerank+
----- get more with dense_rank
--- consider the tie break rules on very stable product
select * from (
select
month, product_desc,qty * price as amount,
DENSE_RANK() OVER (partition by product_desc order by (qty * price) desc) as prod_rank
from prod
) 
where prod_rank<=5
order by product_desc, prod_rank;
--- +surerank+
---- use row_number if you need ranking 1 to 5 exactly
select * from (
select
month, product_desc,qty * price as amount,
ROW_NUMBER() OVER (partition by product_desc order by (qty * price) desc, month) as prod_rank
from prod
) 
where prod_rank<=5
order by product_desc, prod_rank;
--- +max+
---- max - find the best month per product
---
select 
month, product_desc,amount
from (
select
month, product_desc,qty*price as amount,
MAX(qty*price) OVER (partition by product_desc) as max_amount
from prod
) where amount = max_amount;
---- be carefull, if there are dups you can receive more records!
select 
month, product_desc,amount
from (
select
month, product_desc,qty*price as amount,
MIN(qty*price) OVER (partition by product_desc) as min_amount
from prod
) where amount = min_amount;
-- +suremax+
--- use ROW_NUMBER in that case
select 
month, product_desc,amount
from (
select
month, product_desc,qty*price as amount,
ROW_NUMBER() OVER (partition by product_desc order by qty*price desc,month) as rn
from prod
) where rn = 1;
-- +lastnext+
---- last and next value
select 
month, product_desc,qty,
qty_lag, qty_lead,
qty-qty_lag as diff_last_month,
qty_lead - qty as diff_next_month
from (
select
month, product_desc, qty,
LAG(qty,1,0) OVER (partition by product_desc order by month) as qty_lag,
LEAD(qty,1,0) OVER (partition by product_desc order by month) as qty_lead
from prod
)
order by product_desc, month;
-- +surenext+
--- caution - not valid if missing data
-- the same example using RANGE (i.e. logical offset instead of row offset) 
select 
month, product_desc,qty,
qty-qty_last_month as diff_last_month,
qty_next_month - qty as diff_next_month
from (
select
month, product_desc, qty,
FIRST_VALUE(qty) 
  over (partition by product_desc order by month range interval '1' month preceding)  as qty_last_month,
LAST_VALUE(qty) 
  over (partition by product_desc order by month range between current row and interval '1' month following) as qty_next_month
from prod
)
order by product_desc, month;
-- +nullfirst+
---- NULLS first/last
select
month.month_first_date month, product_desc, 
 qty,
 ROW_NUMBER() OVER (order by qty NULLS LAST) as qty_order,
 price,
  ROW_NUMBER() OVER (order by price NULLS FIRST) as price_order
from (select * from prod where product_desc = 'lineal sparce') p
  RIGHT OUTER JOIN month
  ON (month.month_id = p.month_id)
order by 4;
-- +3months+
--- 3 months sum
-- see '2' month: current month + 2 preceding give 3 months!
select
month, product_desc, qty,
SUM(qty) 
  over (partition by product_desc order by month range interval '2' month preceding)  as qty_3_month
from prod
order by product_desc, month;
-- +runavg+
-- running AVG 3 months
select
month, product_desc, qty,
round(AVG(qty) 
  over (partition by product_desc 
        order by month range interval '2' month preceding),1) as avg_qty_3_month
from prod
order by product_desc, month;
-- +dsinterval+
--- interval definition 
-- 4 days 5 hours + 12 minutes + 10.222 seconds
select to_timestamp('01-01-2003','dd-mm-yyyy hh24:mi:ss.ff') + 
       INTERVAL '4 5:12:10.222' DAY TO SECOND(3) as tstamp
from dual;
-- 4 days 5 hours + 12 minutes
select to_date('01-01-2003','dd-mm-yyyy') + 
       INTERVAL '4 5:12' DAY TO MINUTE as date_d
from dual;
-- 400 days + 5 hours
-- if day-precision > 2, the precision must be defined explicitely
select to_date('01-01-2003','dd-mm-yyyy') + 
       INTERVAL '400 5' DAY(3) TO HOUR as date_d
from dual;
-- 400 days
select to_date('01-01-2003','dd-mm-yyyy') + INTERVAL '400' DAY(3) as date_d
from dual;
-- +yminterval+
--- interval definition 
-- 4 years + 1 month
select to_date('01-01-2003','dd-mm-yyyy') + 
       INTERVAL '4-1' YEAR TO MONTH as date_d
from dual;
-- 4 years
select to_date('01-01-2003','dd-mm-yyyy') + INTERVAL '4' YEAR as date_d
from dual;
-- +intkonv+
--- interval conversion
select to_date('01-01-2003','dd-mm-yyyy') + NUMTOYMINTERVAL(4,'year') as date_d
from dual;
-- 
select to_date('01-01-2003','dd-mm-yyyy') + 
       NUMTODSINTERVAL(100, 'day') as date_d
from dual;
-- 
select to_date('01-01-2003','dd-mm-yyyy') + 
       NUMTODSINTERVAL(100, 'second') as date_d
from dual;
-- +top25+
-- top 25% products 
--- cume dist (percentile)
-- top 25 percent of the products (that is, products whose sales amount is 
-- greater or equal than the amounts for 75 percent 
-- of the other products).
select * from (
select
product_desc, sum(qty*price) amount,
round(100 * CUME_DIST() over(order by sum(qty*price) asc)) as prod_percentile
from prod
group by product_desc
) where prod_percentile >= 75;
-- the worst product (of four) has percentile 25% (i.e. 1/4)
-- +top25/2+
-- percentile of the month per product
-- top 25% of months per product sales
select * from (
select
product_desc, month, qty,
round(100 * CUME_DIST() over(partition by product_desc order by qty asc)) 
  as prod_percentile
from prod
) where prod_percentile >= 75
order by product_desc, 4;
-- +ntile+
--- ntile
-- divides the month per product in 5 groups based on qty
select
product_desc, month, qty,
ntile(5) over(partition by product_desc order by qty) as prod_ntile
from prod
order by product_desc, qty;
-- +hyprank+
--- hypothetical rank
-- which rank would yield the sell of 8 products 
select 
product_desc,
RANK(8) within group (order by qty) as hypo_rank 
from prod
group by product_desc
order by product_desc;
-- i.e. qty=8 would rank as 4th month
-- +getsnap+
--- get snapshot
-- i.e. take the nearest preceeding record to selected snapshot timestamp
select month, product_desc,qty,price
from (
select month, product_desc,qty,price,
row_number() over (partition by product_desc order by month desc) rn
from prod
where month <= to_date('01.04.2003','dd.mm.yyyy') --<<< your snapshot time here
) where rn = 1
;
-- verify that you must get all products, event if tehre is no sell on selected
-- snapshot time
-- +ojoin+
-- left outer join 
-- months without sales are returned only once, without relation to product
select product_desc,
      month_first_date month,
      nvl(qty,0) qty
from month left outer join
      prod 
      on ( month.month_first_date =
           prod.month )
order by product_desc, month;
-- +pojoin+
--- partitioned join by product
--- for aech month all products are returned
select product_desc,
      month_first_date month,
      nvl(qty,0) qty
from month left outer join
      prod partition by (product_desc)
      on ( month.month_first_date =
           prod.month )
order by product_desc, month_first_date;
--
-- +initprice+
-- initial and final price per product
select product_desc,
to_char(min(month),'mm-yyyy') first_month,
min(price) -- as month is unique within product_id, the function plays no role,
           -- you may use max, sum, avg with the same result
           KEEP (dense_rank FIRST order by month) init_price,
avg(price) -- verify it usign avg, getting same result ...
           KEEP (dense_rank FIRST order by month) init_price_same_result,		    
to_char(max(month),'mm-yyyy') last_month,
min(price) KEEP (dense_rank LAST order by month) final_price
from prod a
group by product_desc;
-- +sample+
---- sample columns in group by queries 
---- sample product with price per month
---- important bith product and price are select from one record!
---- i.e. NOT min(price), min(product_desc)
select month, 
min(product_desc) KEEP (dense_rank FIRST 
                   order by month,   -- group by columns and ..
				            price, product_desc  -- sample columns
				   ) as sample_prod,
min(price) 
   KEEP (dense_rank FIRST order by month,price, product_desc) as sample_price,
--- another sample
min(product_desc) 
   KEEP (dense_rank LAST order by month,price, product_desc) as sample_prod2,
min(price) KEEP (dense_rank LAST 
                   order by month, price, product_desc) as sample_price2
from  prod
group by month;
-- and quit we are ready..
quit;

select month, qty , count(*)
from  prod
group by month, qty;