Oracle analytic functions
I got an email from someone a couple of days ago. The email said
##############
hi,
i am having a problem here pls help me
this is my i/p and o/p pls tell me logic to develop this
input table or file
101,abc,5000
101,abc,2000
101,abc,1000
101,abc,1500
102,aaa,2000
102,aaa,1500
102,aaa,500
103,asa,1000
103,asa,300
103,asa,600
o/p table or file
0,101,abc,5000
1,101,abc,3000(i.e.,5000-2000)
2,101,abc,2000(i.e.,3000-1000)
3,101,abc,500(i.e.,2000-1500)
0,102,aaa,2000
1,102,aaa,500(i.e.,2000-1500)
2,102,aaa,0(i.e.,500-500)
0,103,asa,1000
1,103,asa,700(i.e.,1000-300)
2,103,asa,100(i.e.,700-600)
thanks and regards
******
##################
Oracle analytic functions were on my mind just like Ray charles (he had Georgia). Even though I knew how to use analytic functions I had to do some research. But I finally got the answer.
First of all the input data didn’t make sense because there was no spelled out requirements provided. After my research, I took a close look at it and found that by adding a sequence number to the table this can be easily done. So I created a test case and assigned a sequence number to the table along with other columns.
This is what I could come up with.
SQL> select banner from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0 Production
TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production
SQL> Create table t1 ( col1 number, txt varchar2(5), col2 number, seq number);
Table created.
SQL> desc t1
Name Null? Type
----------------------------------------- -------- ----------------------------
COL1 NUMBER
TXT VARCHAR2(5)
COL2 NUMBER
SEQ NUMBER
SQL> insert into t1 values (101,'abc',5000,1);
1 row created.
SQL> insert into t1 values (101,'abc',2000,2);
1 row created.
SQL> insert into t1 values (101,'abc',1000,3);
1 row created.
SQL> insert into t1 values (101,'abc',1500,4);
1 row created.
SQL> insert into t1 values (102,'aaa',2000,1);
1 row created.
SQL> insert into t1 values (102,'aaa',1500,2);
1 row created.
SQL> insert into t1 values (102,'aaa', 500,3);
1 row created.
SQL> insert into t1 values (103,'asa',1000,1);
1 row created.
SQL> insert into t1 values (103,'asa', 300,2);
1 row created.
SQL> insert into t1 values (103,'asa', 600,3);
1 row created.
SQL> commit;
Commit complete.
SQL> select * from t1 order by COL1, SEQ;
COL1 TXT COL2 SEQ
---------- ----- ---------- ----------
101 abc 5000 1
101 abc 2000 2
101 abc 1000 3
101 abc 1500 4
102 aaa 2000 1
102 aaa 1500 2
102 aaa 500 3
103 asa 1000 1
103 asa 300 2
103 asa 600 3
10 rows selected.
*****You are about to witness the power of analytics in ORACLE.*****
SQL> select col1,txt,seq
2 ,first_value(col2) over (partition by col1 order by seq)
3 - sum(case when seq=1 then 0 else col2 end)
4 over (partition by col1 order by seq) "HaveFun"
5 from t1
6 order by col1,seq;
COL1 TXT SEQ HaveFun
---------- ----- ---------- ----------
101 abc 1 5000
101 abc 2 3000
101 abc 3 2000
101 abc 4 500
102 aaa 1 2000
102 aaa 2 500
102 aaa 3 0
103 asa 1 1000
103 asa 2 700
103 asa 3 100
10 rows selected.
Don’t write code for something that can be done so easily if you have the right tools.
hope this helps
cheers
Kirtan
Comments feed