Abinitio Interview Question # 14 | Dense_Rank() implementation in abinitio
Watch my YouTube video for explanation :
Please look into the class notes here for your references:
SELECT deptno,
ename,
sal,
Dense_rank() OVER (PARTITION BY deptno ORDER BY sal) "rank"
FROM emp
WHERE deptno = 30;
DEPTNO ENAME SAL rank(Dense Rank)
---------- ---------- ---------- ----------
30 JAMES 950 1
30 WARD 1250 2
30 MARTIN 1250 2
30 TURNER 1500 3
30 ALLEN 1600 4
30 BLAKE 2850 5
Can you achieve it in Abinitio?
type temporary_type=
begin
decimal(",") prv_amt;
decimal(",") ranking;
decimal(",") d_ranking;
end;
temp::initialize(in)=
begin
temp.ranking::1;
temp.prv_amt::in.SAL;
temp.d_ranking::1;
end;
out::scan(temp,in)=
begin
out.ranking::if(in.SAL>temp.prv_amt) temp.d_ranking else temp.ranking;
out.prv_amt::in.SAL;
out.d_ranking::if(in.SAL>temp.prv_amt)temp.ranking +1 else temp.ranking;
end;
out::finalize(temp,in)=
begin
out.*::in.*;
out.RANK::temp.ranking;
end;
OutputDML-
record
string(",") DEPTNO;
string(",") ENAME;
decimal(",") SAL;
decimal("\n") RANK;
end;
Fore more Abinitio, AWS and data engineering videos please subscribe , view , like and share my YouTube channel
Click DataPundit
Comments
Post a Comment