You Asked
I have a following table in which we insert records through a batch process.
create table emp
(batch_id number(10),
empno number(10),
ename varchar2(35),
deptno number(10));
insert into emp
values (1,10,'XXX',10);
insert into emp
values (1,11,'YYY',10);
insert into emp
values (1,12,'ZZZ',20);
insert into emp
values (2,20,'XXX',10);
insert into emp
values (2,21,'YYY',20);
insert into emp
values (2,22,'ZZZ',20);
After inserting second batch, I want to find out percentage difference in number of employees for each department.
select distinct batch_id,deptno,ct from (
select batch_id,deptno,
count(*) over (partition by batch_id,deptno) as ct
from emp)
BATCH_ID DEPTNO CT
1 10 2
1 20 1
2 10 1
2 20 2
I want another column for batch_id=2 that will tell me percentage change for each deptno from batch_id 1 to 2. Is this possible using analytics?
Thanks
HS
and Tom said...
well, you would never use analytics in the fashion you did. IF you can use an aggregate, by all means USE IT
ops$tkyte@ORA10GR2> select batch_id, deptno, count(*)
2 from emp
3 group by batch_id, deptno
4 order by 1, 2;
BATCH_ID DEPTNO COUNT(*)
---------- ---------- ----------
1 10 2
1 20 1
2 10 1
2 20 2
No need for analytics to do batch over batch comparisons either:
ops$tkyte@ORA10GR2>
ops$tkyte@ORA10GR2> select deptno, b1, b2, case when b1 <> 0 then round( b2/b1*100, 2 ) end pct
2 from (
3 select deptno,
4 count(decode( batch_id, 1, 1 )) b1,
5 count(decode( batch_id, 2, 1 )) b2
6 from emp
7 group by deptno
8 )
9 order by deptno
10 /
DEPTNO B1 B2 PCT
---------- ---------- ---------- ----------
10 2 1 50
20 1 2 200
Always good to make test cases more interesting:
ops$tkyte@ORA10GR2> insert into emp
2 values (2,22,'ZZZ',30);
1 row created.
ops$tkyte@ORA10GR2> insert into emp
2 values (1,22,'ZZZ',40);
1 row created.
ops$tkyte@ORA10GR2> select deptno, b1, b2, case when b1 <> 0 then round( b2/b1*100, 2 ) end pct
2 from (
3 select deptno,
4 count(decode( batch_id, 1, 1 )) b1,
5 count(decode( batch_id, 2, 1 )) b2
6 from emp
7 group by deptno
8 )
9 order by deptno
10 /
DEPTNO B1 B2 PCT
---------- ---------- ---------- ----------
10 2 1 50
20 1 2 200
30 0 1
40 1 0 0
Rating
(2 ratings)
Is this answer out of date? If it is, please let us know via a Comment