SQL উইন্ডো ফাংশন বোঝার জন্য একটি শিক্ষানবিস গাইড - পার্ট 2

মধ্যে পূর্ববর্তী নিবন্ধ , আমরা উইন্ডো ফাংশন সম্পর্কে কথা বলেছি — বিভিন্ন বিশ্লেষণাত্মক সমস্যা সমাধানের জন্য একটি দুর্দান্ত হাতিয়ার; তারা আপনাকে জটিল প্রশ্নগুলি লেখার প্রয়োজন ছাড়াই উন্নত বিশ্লেষণ এবং ডেটা ম্যানিপুলেশনের মতো বৈশিষ্ট্যগুলিতে অ্যাক্সেস দিতে পারে। আপনি পড়া চালিয়ে যাওয়ার আগে, আমি প্রথম অংশ দিয়ে শুরু করার সুপারিশ করব কারণ এটি SQL-এ উইন্ডো ফাংশনগুলি কীভাবে কাজ করে তার প্রাথমিক ধারণা বুঝতে সাহায্য করবে।

তাই এখন, আপনি মৌলিক বিষয়গুলির সাথে পরিচিত, আসুন আমরা আরও উন্নত SQL ধারণাগুলি অন্বেষণ করি। এটি প্রথম নজরে কিছুটা জটিল মনে হতে পারে, তবে আমি সমস্ত সম্ভাব্য ক্ষেত্রে নতুনদের জন্য উপযুক্ত সহজ উদাহরণ প্রদান করব, তাই আমার ড্রিফট ধরা সহজ হবে।

বিষয়বস্তু ওভারভিউ

• Cumulative Sum
• Ranking Window Functions
• Use cases
• Offset window functions
• Key Takeaways

ক্রমবর্ধমান সমষ্টি

আমরা ইতিমধ্যে উদাহরণ বিবেচনা করেছি যেখানে over() এক্সপ্রেশনের হয় কোন প্যারামিটার ছিল না বা প্যারামিটার দ্বারা একটি পার্টিশন ছিল। এখন, আমরা over() এক্সপ্রেশনের জন্য দ্বিতীয় সম্ভাব্য প্যারামিটারটি দেখব — ক্রম অনুসারে।

আসুন কর্মচারী আইডি, কর্মচারীর নাম, বিভাগ, বেতন এবং সমস্ত বেতনের যোগফল অনুরোধ করি:

 select employee_id, employee_name, department, salary, sum(salary) over() from salary 

এখন, আমরা over() এক্সপ্রেশনে প্যারামিটার দ্বারা ক্রম যুক্ত করব:

 select employee_id, employee_name, department, salary, sum(salary) over(order by employee_id desc) from salary 

আমি অনুমান করি যে এখানে কী ঘটেছে তা আমাদের আরও ঘনিষ্ঠভাবে দেখতে হবে:

1. প্রথমত, এমপ্লয়ি_আইডি এখন নিচের ক্রমে সাজানো হয়েছে।

   
   

2. উইন্ডো ফাংশন প্রয়োগের ফলে কলামে, এখন একটি ক্রমবর্ধমান যোগফল রয়েছে।

   
   

আমি বিশ্বাস করি আপনি ক্রমবর্ধমান সমষ্টির সাথে পরিচিত। এর সারমর্মটি সহজ - ক্রমবর্ধমান যোগফল বা চলমান মোটের অর্থ "এখন পর্যন্ত কত।" ক্রমবর্ধমান যোগফলের সংজ্ঞা হল একটি প্রদত্ত ক্রমগুলির যোগফল যা আরও যোগ করার সাথে বৃদ্ধি পাচ্ছে বা বড় হচ্ছে।

আমাদের উদাহরণে এখানে যা আছে: সর্বোচ্চ কর্মচারী_আইডি মান সহ কর্মচারীর জন্য, বেতন 3700, এবং ক্রমবর্ধমান যোগফলও 3700। দ্বিতীয় কর্মচারীর বেতন 1500, এবং ক্রমবর্ধমান যোগফল হল 5200। তৃতীয় কর্মচারী , 2900 এর বেতন সহ, 8100 এর ক্রমবর্ধমান যোগফল রয়েছে এবং আরও অনেক কিছু।

ওভার() এক্সপ্রেশনে প্যারামিটার দ্বারা ক্রম নির্দেশ করে। উইন্ডো ফাংশন একত্রিত করার ক্ষেত্রে, এটি ক্রমবর্ধমান মোটের জন্য ক্রম নির্ধারণ করে।

over() এক্সপ্রেশনে, বৈশিষ্ট্য দ্বারা বিভাজন এবং ক্রম উভয়ই নির্দিষ্ট করা যেতে পারে।

 select employee_id, employee_name, department, salary, sum(salary) over(partition by department order by employee_id desc) from salary 

এই ক্ষেত্রে, ক্রমবর্ধমান মোট বিভাগ দ্বারা গণনা করা হবে।

আমরা ক্রমবর্ধমান সমষ্টি নিয়ে আলোচনা করার পরে, আমাদের বলতে হবে যে সম্ভবত এটিই একমাত্র প্রকার ক্রমবর্ধমান মোট যা প্রায়শই ব্যবহৃত হয়। ক্রমবর্ধমান গড় এবং ক্রমবর্ধমান গণনা, বিপরীতে, খুব কমই ব্যবহৃত হয়।

তবুও, আমরা ক্রমবর্ধমান গড় গণনার একটি উদাহরণ দেব — এটি আমাদেরকে একটি নির্দিষ্ট বিন্দু পর্যন্ত মানের সিরিজের গড় বলে:

 select employee_id, employee_name, department, salary, avg(salary) over(order by employee_id desc) from salary 

র‌্যাঙ্কিং উইন্ডো ফাংশন

আমরা মানগুলির একটি সেটের মধ্যে একটি মানের অবস্থান নির্ধারণ করতে র‌্যাঙ্কিং উইন্ডো ফাংশন ব্যবহার করি। ওভার ক্লজের মধ্যে ORDER BY অভিব্যক্তিটি র‌্যাঙ্কিংয়ের ভিত্তি নির্দেশ করে, প্রতিটি মান তার মনোনীত পার্টিশনের মধ্যে একটি র‌্যাঙ্ক বরাদ্দ করে। যখন সারিগুলি র‌্যাঙ্কিংয়ের মানদণ্ডের জন্য অভিন্ন মান শেয়ার করে, তখন তাদের একই র‌্যাঙ্ক বরাদ্দ করা হয়।

র‌্যাঙ্কিং উইন্ডো ফাংশনগুলি কীভাবে কাজ করে তা দেখতে, আসুন বেতন টেবিল থেকে নিম্নলিখিত কলামগুলির অনুরোধ করি: কর্মচারী আইডি, কর্মচারীর নাম, বিভাগ এবং বেতন:

 select employee_id, employee_name, department, salary from salary 

এখন, আমরা উইন্ডো ফাংশন row_number() over() সহ আরও একটি কলাম যোগ করি :

 select employee_id, employee_name, department, salary, row_number() over() from salary 

উইন্ডো ফাংশন row_number() over() তাদের ক্রম পরিবর্তন না করেই সারিতে সংখ্যা নির্ধারণ করেছে। এখন পর্যন্ত, এটি আমাদের কাছে খুব বেশি মূল্য আনে না, তাই না?

কিন্তু আমরা যদি বেতনের অবরোহ ক্রমে সারি সংখ্যা করতে চাই? এটি অর্জন করার জন্য, আমাদের সাজানোর ক্রম নির্দিষ্ট করতে হবে, অন্য কথায়, over() এক্সপ্রেশনে প্যারামিটার দ্বারা ক্রমটি পাস করুন।

 select employee_id, employee_name, department, salary, row_number() over(order by salary desc) from salary 

আমরা তুলনা করার জন্য ক্যোয়ারীতে অবশিষ্ট র‌্যাঙ্কিং ফাংশন যোগ করব:

 select employee_id, employee_name, department, salary, row_number() over(order by salary desc), rank() over(order by salary desc), dense_rank() over(order by salary desc), percent_rank() over(order by salary desc), ntile(5) over(order by salary desc) from salary

আসুন প্রতিটি র‌্যাঙ্কিং উইন্ডো ফাংশন দিয়ে যাই:

1. উইন্ডো ফাংশন row_number() over(অর্ডার by salary desc) সারিগুলিকে বেতনের নিচের ক্রম অনুসারে স্থান দেয় এবং সারি সংখ্যা নির্ধারণ করে। উল্লেখ্য যে অ্যানি এবং টনির একই বেতন রয়েছে, তবে তাদের আলাদা নম্বর দেওয়া হয়েছে।

   
   

2. উইন্ডো ফাংশন র‌্যাঙ্ক() ওভার (বেতনের বিবরণ অনুসারে ক্রম ) বেতনের নিচের ক্রম অনুসারে র‌্যাঙ্ক নির্ধারণ করে। এটি অভিন্ন মানের জন্য একই র‌্যাঙ্ক বরাদ্দ করে, কিন্তু পরবর্তী মানটি একটি নতুন সারি নম্বর পায়।

   
   

3. উইন্ডো ফাংশন dense_rank() over(অর্ডার by salary desc) বেতনের নিচের ক্রম অনুসারে র‌্যাঙ্ক নির্ধারণ করে। এটি অভিন্ন মানগুলির জন্য একই র‌্যাঙ্ক বরাদ্দ করে।

   
   

4. উইন্ডো ফাংশন percent_rank() over(বেতনের বিবরণ অনুসারে অর্ডার) হল বর্তমান সারির আপেক্ষিক (শতাংশ) র্যাঙ্ক, সূত্র দ্বারা গণনা করা হয়: (র্যাঙ্ক - 1) / (পার্টিশনে মোট সারির সংখ্যা - 1)।

   
   

5. উইন্ডো ফাংশন ntile(5) ওভার (বেতনের বিবরণ অনুসারে) সারির সংখ্যাকে 5টি সমান অংশে ভাগ করে এবং প্রতিটি অংশে একটি সংখ্যা নির্ধারণ করে। এনটাইল(5) ফাংশনের ভিতরে অংশের সংখ্যা নির্দিষ্ট করা আছে।

   
   

ব্যবহারের ক্ষেত্রে

র‍্যাঙ্কিং উইন্ডো ফাংশন ব্যবহার করে ব্যবহারিক কাজগুলি অন্বেষণ করার সময়। আমরা কর্মচারী আইডি, কর্মচারীর নাম, বিভাগ এবং বেতন প্রদর্শন করব এবং বেতনের নিচের ক্রম অনুসারে সারি নম্বর বরাদ্দ করব।

 select employee_id, employee_name, department, salary, row_number() over(order by salary desc) from salary 

কখনও কখনও, আপনাকে বিভাগগুলির (বিভাগ) মধ্যে বেতনের ক্রমানুসারে সারি সংখ্যা করতে হতে পারে। এটা over() এক্সপ্রেশনে বৈশিষ্ট্য দ্বারা পার্টিশন যোগ করে করা যেতে পারে:

 select employee_id, employee_name, department, salary, row_number() over(partition by department order by salary desc) from salary 

আসুন কাজটিকে আরও চ্যালেঞ্জিং করে তুলি। আমাদের সর্বোচ্চ বেতনের সাথে প্রতি বিভাগে মাত্র একজন কর্মচারী রাখতে হবে। এটি একটি subquery ব্যবহার করে অর্জন করা যেতে পারে:

 select * from ( select employee_id, employee_name, department, salary, row_number() over(partition by department order by salary desc) as rn from salary ) as t2 where rn = 1 

এবং আরও একটি উদাহরণ, যদি আমাদের প্রতিটি শহরে সর্বোচ্চ বেতন সহ তিনজন কর্মচারী প্রদর্শন করতে হয়, আমরা নিম্নলিখিতগুলি করব:

 select * from ( select employee_id, employee_name, city, salary, row_number() over(partition by city order by salary desc) as rn from salary ) as t2 where rn <= 3 

এই ধরনের কাজগুলি খুব সাধারণ, বিশেষ করে যখন আপনাকে কিছু বৈশিষ্ট্যের ঊর্ধ্বগামী বা অবরোহ ক্রমে বিভাগ (গ্রুপ) এর মধ্যে একটি নির্দিষ্ট সংখ্যক সারি প্রদর্শন করতে হবে। অনুশীলনে, আমি ধারাবাহিকভাবে উইন্ডো ফাংশন row_number() over() , এবং অবশ্যই, dense_rank() over() পাশাপাশি ব্যবহার করি।

অফসেট উইন্ডো ফাংশন

এই ফাংশনগুলি আপনাকে বর্তমান সারি থেকে তাদের দূরত্বের উপর ভিত্তি করে অন্যান্য সারি থেকে ডেটা ফেরত দেওয়ার অনুমতি দেয়। এটিকে আরও চাক্ষুষ করতে, আসুন first_value(), last_value(), এবং nth_value() ফাংশনগুলি দিয়ে যাই।

 select t1.*, first_value(salary)over(partition by department), last_value(salary)over(partition by department), nth_value(salary,2)over(partition by department) from salary as t1 order by department 

 select t1.*, first_value(salary)over(partition by department order by salary decs), last_value(salary)over(partition by department order by salary decs), nth_value(salary,2)over(partition by department order by salary decs) from salary as t1 order by department

ফাংশন first_value(বেতন) over( বিভাগ দ্বারা বিভাজন) এবং last_value(বেতন) over( বিভাগ দ্বারা বিভাজন) বিভাগ (বিভাগ) এর মধ্যে প্রথম এবং শেষ বেতন মান প্রদর্শন করে।

এর পরিবর্তে, ফাংশন nth_value(বেতন, 2) ওভার(বিভাগ দ্বারা বিভাজন) বিভাগ (বিভাগ) এর মধ্যে দ্বিতীয় বেতন মান দেখায়। অনুগ্রহ করে মনে রাখবেন যে nth_value() এ, একটি অতিরিক্ত আর্গুমেন্ট নির্দিষ্ট করা হয়েছে - বিভাগের মধ্যে সারি নম্বর। আমাদের ক্ষেত্রে, সারি সংখ্যা 2, তাই ফাংশন দ্বিতীয় বেতন মান প্রদর্শন করে।

উপরোক্ত ছাড়াও, ল্যাগ() এবং লিড() ফাংশন রয়েছে। ল্যাগ() ফাংশনটি বর্তমান সারির আগের সারি থেকে মান পেতে ব্যবহৃত হয়। লিড() ফাংশনটি একটি সারি থেকে মান পেতে ব্যবহৃত হয় যা বর্তমান সারিকে সফল করে।

 select t1.*, lag(salary)over(order by salary), lead(salary)over(order by salary) from salary as t1 

আপনি দেখতে পাচ্ছেন, ফাংশন ল্যাগ (বেতন) ওভার (বেতন অনুসারে) বেতনগুলিকে এক সারি দ্বারা নীচে স্থানান্তরিত করে, এবং ফাংশন লিড (বেতন) ওভার (বেতন অনুসারে অর্ডার) বেতনগুলিকে এক সারি দ্বারা উপরে স্থানান্তরিত করে। যদিও এই ফাংশনগুলি বেশ একই রকম, আমি ল্যাগ() ব্যবহার করা আরও সুবিধাজনক বলে মনে করি।

 select t1.*, lag(salary)over(partition by department order by salary) from salary as t1 order by department 

এখানে, lag() আগের মতো একই ফাংশন সম্পাদন করে, কিন্তু এখন বিশেষভাবে বিভাগগুলির মধ্যে (বিভাগ)।

কী Takeaways

এবং অবশেষে, আমরা আজকে যা কভার করেছি তার একটি দ্রুত ওভারভিউ:

• ক্রমবর্ধমান যোগফল প্রতিটি পরবর্তী সংযোজনের সাথে সঞ্চিত একটি অনুক্রমের চলমান মোটের প্রতিনিধিত্ব করে।

  
  

• র‌্যাঙ্কিং উইন্ডো ফাংশনগুলি মানগুলির একটি সেটের মধ্যে একটি মানের অবস্থান নির্ধারণ করতে ব্যবহৃত হয়, র‌্যাঙ্কিংয়ের ভিত্তি নির্দিষ্ট করে অভিব্যক্তি দ্বারা ক্রম ।

  
  

• অফসেট উইন্ডো ফাংশন f irst_value() , last_value() , এবং nth_value() অন্তর্ভুক্ত করে, বর্তমান সারি থেকে তাদের দূরত্বের উপর ভিত্তি করে অন্যান্য সারি থেকে ডেটা পুনরুদ্ধার সক্ষম করে। ল্যাগ() এবং লিড() ফাংশন সম্পর্কে ভুলবেন না। ল্যাগ() ফাংশনটি বর্তমান সারির পূর্ববর্তী সারি থেকে মান পেতে সহজ হতে পারে, যখন বর্তমান সারিটি সফল করে এমন একটি সারি থেকে মান পেতে লিড() ফাংশন ব্যবহার করা হয়।

  
  

আমার সাথে যোগদানের জন্য ধন্যবাদ. আমি আশা করি এই নিবন্ধটি আপনাকে SQL-এ উইন্ডো ফাংশনগুলির ক্ষমতাগুলি আরও ভালভাবে বুঝতে সাহায্য করবে এবং আপনাকে রুটিন কাজগুলিতে আরও আত্মবিশ্বাসী এবং দ্রুত করে তুলবে৷