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

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

Published by

Ali Asgor

Graduate in GES, OCP holder, Self-motivated app developer, Programming lover, Lazy person...

One thought on “Row ranking tips using RANK, DENSE_RANK and ROW_NUMBER”

Leave a Reply to sean xiao Cancel reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.