-- partitioned outer join demo
-- www.db-nemec.com
-- 7.1.2005
------------------------
drop table  sales;
drop table times;
drop table products;
drop table customers;
--
create table products
(prod_id number not null);
--
create table customers
(customer_id number not null);
---
create table times
(time_id number not null);
--
create table sales
(prod_id number,
 time_id number,
 customer_id number,
 amount_sold number);
--- populate
insert into times
select rownum from dba_objects where rownum <= 5; 
insert into products
select rownum from dba_objects where rownum <= 3; 
insert into customers
select rownum from dba_objects where rownum <= 4; 
commit;
---
truncate table sales;
-- prod_id, time_id, customer_id,amount_sold
insert into sales values (1,1,1,10);
insert into sales values (1,1,2,1);
insert into sales values (2,3,2,12);
insert into sales values (2,2,3,8);
insert into sales values (1,1,1,20);
insert into sales values (2,2,1,21);
insert into sales values (2,2,2,19);
insert into sales values (3,2,3,30);
insert into sales values (3,3,2,35);
insert into sales values (3,1,1,31);
insert into sales values (2,3,4,40);
commit;
-- sales overview
select * from sales
order by time_id, prod_id, customer_id;
--- sales per time
select time_id, sum(amount_sold)  amount_sold
from sales
group by time_id
order by time_id;
-- sales per time wihtout gaps
select t.time_id, sum(nvl(amount_sold,0))  amount_sold
from times t, sales s
where t.time_id = s.time_id(+)
group by t.time_id
order by t.time_id;
-- ansi right
select t.time_id, nvl(amount_sold,0) amount_sold from 
(select time_id, sum(amount_sold)  amount_sold
from sales 
group by time_id) s
right outer join times t
on (t.time_id = s.time_id)
order by t.time_id;
--- sales per time and product
select s.prod_id, t.time_id, nvl(amount_sold,0) amount_sold from 
(select prod_id, time_id, sum(amount_sold)  amount_sold
from sales 
group by prod_id, time_id) s
right outer join times t
on (t.time_id = s.time_id)
order by s.prod_id, t.time_id;
-- partition by
select 
s.prod_id, t.time_id, nvl(amount_sold,0) amount_sold
from 
(select prod_id, time_id, sum(amount_sold)  amount_sold
 from sales
 group by prod_id, time_id) s
PARTITION BY (prod_id)
right outer join
times t on (t.time_id = s.time_id)
order by s.prod_id, t.time_id
;
--- alternative aproach
select 
t.prod_id, t.time_id, nvl(amount_sold,0) amount_sold
from 
(select prod_id, time_id, sum(amount_sold)  amount_sold
 from sales
 group by prod_id, time_id) s
right outer join
(select time_id, prod_id from times, products) t -- cartesian product
on (t.time_id = s.time_id and t.prod_id = s.prod_id)
order by t.prod_id, t.time_id
;
--- time restricted
select 
s.prod_id, t.time_id, nvl(amount_sold,0) amount_sold
from 
(select prod_id, time_id, sum(amount_sold)  amount_sold
 from sales
 group by prod_id, time_id) s
PARTITION BY (prod_id)
right outer join
times t on (t.time_id = s.time_id)
where t.time_id in (2,3)
order by s.prod_id, t.time_id
;
-- all three dims for time 1,2
select a.prod_id,c.customer_id, a.time_id, nvl(amount_sold,0) amount_sold
from 
(select 
s.prod_id, t.time_id, s.customer_id, amount_sold
from (
 select prod_id, time_id, customer_id, sum(amount_sold)  amount_sold
 from sales
 where time_id in (2,3)
 group by prod_id, time_id, customer_id) s
PARTITION BY (prod_id)
right outer join
times t on (t.time_id = s.time_id)
where t.time_id in (2,3)) a
PARTITION BY (prod_id, time_id)
right outer join
customers c on (a.customer_id = c.customer_id)
order by a.prod_id,c.customer_id, a.time_id;