Row ranking tips using RANK, DENSE_RANK and ROW_NUMBER

ওরাকল ডাটাবেজে অনেক ভাবেই ডাটা র‍্যাঙ্কিং করা যায়। সবচেয়ে সহজ সমাধান হল Aggregate functions ব্যাবহার করে। কিন্তু এ ধরনের ফাংশনে নন গ্রুপ কলাম সিলেক্ট করা যায় না। এর সহজ সমাধান হল এ ক্ষেত্রে Analytics functions ব্যাবহার করে ডাটা র‍্যাঙ্কিং করা।

RANK function: Rank function ব্যাবহার করে খুব সহজেই ডাটা র‍্যাঙ্কিং করতে পারি, যেমন –

SELECT deptno, empno, ename, sal, 
RANK() OVER (ORDER BY salary) AS rank
FROM emp

উপরের কুয়েরি রান করলে নিচের মতো রেজাল্ট পাবো –

DEPTNO	EMPNO	ENAME	SAL	RANK
20	7369	SMITH	800	1
30	7900	JAMES	950	2
20	7876	ADAMS	1100	3
30	7654	MARTIN	1250	4
30	7521	WARD	1250	4
10	7934	MILLER	1300	6
30	7844	TURNER	1500	7
30	7499	ALLEN	1600	8

উপরের র‍্যাঙ্ক কলাম খেয়াল করলে দেখতে পাবো 4 নং বার দুই বার রিপিট হয়েছে এবং 5 নং উল্লেখ নাই। দুইজনের স্যালারি সমান হবার কারনে এমনটা হয়েছে।

আমরা চাইলে DENSE_RANK function ব্যাবহার করে এই গ্যাপ দূর করতে পারি। যেমন –

SELECT deptno, empno, ename, sal,
DENSE_RANK() OVER (ORDER BY sal) AS rank
FROM emp

এখন নিচের মতো রেজাল্ট পাবো –

DEPTNO	EMPNO	ENAME	SAL	RANK
20	7369	SMITH	800	1
30	7900	JAMES	950	2
20	7876	ADAMS	1100	3
30	7654	MARTIN	1250	4
30	7521	WARD	1250	4
10	7934	MILLER	1300	5
30	7844	TURNER	1500	6
30	7499	ALLEN	1600	7

এখানে 5 নং রো দেখা গেলেও আগের মতো 4 নং রো রিপিট রয়েই গিয়েছে। এটা দূর করার জন্য রয়েছে ROW_NUMBER function নিচের উদাহরণটা লক্ষ্য করি –

SELECT deptno, empno, ename, sal,
ROW_NUMBER() OVER (ORDER BY sal) AS rank
FROM emp

Result –

DEPTNO	EMPNO	ENAME	SAL	RANK
20	7369	SMITH	800	1
30	7900	JAMES	950	2
20	7876	ADAMS	1100	3
30	7654	MARTIN	1250	4
30	7521	WARD	1250	5
10	7934	MILLER	1300	6
30	7844	TURNER	1500	7
30	7499	ALLEN	1600	8

Excellent! এখন আমরা কনো নম্বার গ্যাপ ছাড়ায় ডাটা র‍্যাঙ্কিং করতে পেরে গেছি। কিন্তু আপনার বস বলল এতে হবে না আমার ডিপার্টমেন্ট ওয়াইজ র‍্যাঙ্কিং লাগবে! Easy, এ জন্য রয়েছে PARTITION BY কিওায়ার্ড –

SELECT deptno, empno, ename, sal,
ROW_NUMBER() OVER (PARTITION BY deptno ORDER BY sal) AS rank
FROM emp

Result –

DEPTNO	EMPNO	ENAME	SAL	RANK
10	7934	MILLER	1300	1
10	7782	CLARK	2450	2
10	7839	KING	5000	3
20	7369	SMITH	800	1
20	7876	ADAMS	1100	2
20	7566	JONES	2975	3
20	7788	SCOTT	3000	4
20	7902	FORD	3000	5

Wow ডিপার্টমেন্ট ওয়াইজ র‍্যাঙ্কিং হয়ে গেছে। এখন আপনার মনে হলো না আমি ডিপার্টমেন্ট ওয়াইজ সবচেয়ে কম পেইড ওয়ার্কারদের নাম দেখতে চাই। সেটাও করতে পারেন এ ভাবে –

SELECT * FROM (
  SELECT deptno, empno, ename, sal,
  ROW_NUMBER() OVER (PARTITION BY deptno ORDER BY sal) AS rank
  FROM emp )
WHERE rank = 1

Result –

DEPTNO	EMPNO	ENAME	SAL	RANK
10	7934	MILLER	1300	1
20	7369	SMITH	800	1
30	7900	JAMES	950	1

এখন আপনি চাইলে খুব সহজেই ডিপার্টমেন্ট ওয়াইজ হায়েস্ট পেইড ওয়ার্কারের লিস্টও বের করতে পারেন। জাস্ট একটা কিওায়ার্ড অ্যাড করলেই পেয়ে যাবেন কাংখিত রেজাল্ট। কি সেই কিওয়ার্ড জানতে পারেন কমেন্ট বক্সে! সে পর্যন্ত আল্লাহ হাফেজ।