What i had learnt today is about Partitioned outer joins introduced in Oracle database 10g.
Data is stored in sparse form. That is if no value exists for a given time (date) then row rows exists in sales table.
Lets setup a simple example, All I want to do is compare this month sale's with last month sales.
Start with this data.
rajesh@10GR2> select * from sales;
ITEM TXN_DT TOTAL_AMT
----- ----------- ----------
A 01-JUN-2010 210
A 01-JUL-2010 270
A 01-AUG-2010 240
A 01-OCT-2010 327
A 01-NOV-2010 312
B 01-JUN-2010 410
B 01-AUG-2010 570
B 01-SEP-2010 640
B 01-OCT-2010 427
B 01-NOV-2010 312
10 rows selected.
Elapsed: 00:00:00.01
rajesh@10GR2>
Notice, how i am missing here. No sales for Item 'A' in SEP-2010 and for Item 'B' in JUL-2010. But how can i compare this month Transaction amount with previous month transaction amount?
rajesh@10GR2> select item,
2 txn_dt,
3 lag(txn_dt) over(partition by item order by txn_dt) as previous_txn_dt,
4 total_amt,
5 lag(total_amt) over(partition by item order by txn_dt) as previous_txn_amt
6 from sales
7 order by item, txn_dt;
ITEM TXN_DT PREVIOUS_T TOTAL_AMT PREVIOUS_TXN_AMT
----- ---------- ---------- --------- ----------------
A 01-JUN-10 210
A 01-JUL-10 01-JUN-10 270 210
A 01-AUG-10 01-JUL-10 240 270
A 01-OCT-10 01-AUG-10 327 240
A 01-NOV-10 01-OCT-10 312 327
B 01-JUN-10 410
B 01-AUG-10 01-JUN-10 570 410
B 01-SEP-10 01-AUG-10 640 570
B 01-OCT-10 01-SEP-10 427 640
B 01-NOV-10 01-OCT-10 312 427
10 rows selected.
Notice how the output is compared, Item 'A' OCT-2010 compared with AUG-2010, But that isn't what i needed. I need to compare with last_month (NULL since SEP-2010 for item 'A' does not exists in sales table).
So, how can the missing data be filled for proper comparison? The way to achieve this is with the following query.
rajesh@10GR2> with datas as
2 (
3 select add_months( to_date('01/JUN/2010','dd/mon/yyyy') , (column_value - 1) ) as gap_dates
4 from table(vtable(7))
5 ),
6 unique_items as
7 (
8 select distinct item from sales
9 ),
10 dates_items as
11 ( select * from datas, unique_items )
12 select d.item,
13 d.gap_dates as current_month,
14 lag(d.gap_dates) over(partition by d.item order by d.gap_dates) as previous_month,
15 nvl(s.total_amt,0) as current_month_total_amt,
16 lag(s.total_amt,1,0) over(partition by d.item order by d.gap_dates) as previous_month_total_amt
17 from sales s , dates_items d
18 where s.item (+) = d.item
19 and s.txn_dt (+) = d.gap_dates
20 order by d.item, d.gap_dates
21 /
ITEM CURRENT_MO PREVIOUS_M CURRENT_MONTH_TOTAL_AMT PREVIOUS_MONTH_TOTAL_AMT
----- ---------- ---------- ----------------------- ------------------------
A 01-JUN-10 210 0
A 01-JUL-10 01-JUN-10 270 210
A 01-AUG-10 01-JUL-10 240 270
A 01-SEP-10 01-AUG-10 0 240
A 01-OCT-10 01-SEP-10 327
A 01-NOV-10 01-OCT-10 312 327
A 01-DEC-10 01-NOV-10 0 312
B 01-JUN-10 410 0
B 01-JUL-10 01-JUN-10 0 410
B 01-AUG-10 01-JUL-10 570
B 01-SEP-10 01-AUG-10 640 570
B 01-OCT-10 01-SEP-10 427 640
B 01-NOV-10 01-OCT-10 312 427
B 01-DEC-10 01-NOV-10 0 312
14 rows selected.
Elapsed: 00:00:00.06
But with the introduction of PARTITIONED OUTER JOINS in Oracle 10g, this can be achieved very simple.
rajesh@10GR2> with datas as
2 (
3 select add_months( to_date('01/JUN/2010','dd/mon/yyyy') , (column_value - 1) ) as gap_dates
4 from table(vtable(7))
5 )
6 select s.item,
7 d.gap_dates as current_month,
8 lag(d.gap_dates) over(partition by s.item order by d.gap_dates) as previous_month,
9 nvl(s.total_amt,0) as current_month_total_amt,
10 lag(s.total_amt,1,0) over(partition by s.item order by d.gap_dates) as previous_month_total_amt
11 from datas d left outer join sales s partition by (item)
12 on (d.gap_dates = s.txn_dt )
13 order by s.item, d.gap_dates
14 /
ITEM CURRENT_MO PREVIOUS_M CURRENT_MONTH_TOTAL_AMT PREVIOUS_MONTH_TOTAL_AMT
----- ---------- ---------- ----------------------- ------------------------
A 01-JUN-10 210 0
A 01-JUL-10 01-JUN-10 270 210
A 01-AUG-10 01-JUL-10 240 270
A 01-SEP-10 01-AUG-10 0 240
A 01-OCT-10 01-SEP-10 327
A 01-NOV-10 01-OCT-10 312 327
A 01-DEC-10 01-NOV-10 0 312
B 01-JUN-10 410 0
B 01-JUL-10 01-JUN-10 0 410
B 01-AUG-10 01-JUL-10 570
B 01-SEP-10 01-AUG-10 640 570
B 01-OCT-10 01-SEP-10 427 640
B 01-NOV-10 01-OCT-10 312 427
B 01-DEC-10 01-NOV-10 0 312
14 rows selected.
Elapsed: 00:00:00.04
rajesh@10GR2>
No comments:
Post a Comment