এসকিউএল উইন্ডো ফাংশন এবং তাদের ক্ষমতা বোঝার জন্য একটি শিক্ষানবিস গাইড

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

যদিও উইন্ডো ফাংশনগুলি প্রায় 20 বছর ধরে রয়েছে, অনেক SQL বিকাশকারী এখনও তাদের উপলব্ধি করা কঠিন বলে মনে করেন। এমনকি অভিজ্ঞ ডেভেলপারদের জন্য স্ট্যাকওভারফ্লো থেকে কোড কপি এবং পেস্ট করা অস্বাভাবিক নয় যে এটি আসলে কী করে তা না বুঝে। এই নিবন্ধটি সাহায্য করার জন্য এখানে! আমি উইন্ডো ফাংশনগুলিকে এমনভাবে ব্যাখ্যা করব যাতে বোঝা সহজ হয় এবং বাস্তব জগতে কীভাবে কাজ করে তা আপনাকে দেখানোর জন্য উদাহরণ প্রদান করব।

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

এসকিউএল এবং উইন্ডো ফাংশন জগতে স্বাগতম! আপনি যদি সবে শুরু করছেন, আপনি সঠিক জায়গায় আছেন। এই নিবন্ধটি শিক্ষানবিস-বান্ধব, স্পষ্ট ব্যাখ্যা সহ এবং কোন জটিল পরিভাষা বা উন্নত ধারণা নেই। আপনি সহজে অনুসরণ করতে সক্ষম হবেন, এমনকি যদি আপনি বিষয়টিতে সম্পূর্ণ নতুন হন।

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

• উইন্ডো ফাংশনের সাথে ব্যবহৃত ফাংশনের ধরন
  • সমষ্টিগত ফাংশন
  • র‌্যাঙ্কিং ফাংশন
  • মান ফাংশন
• মোট উইন্ডো ফাংশন
• কী Takeaways

উইন্ডো ফাংশনের সাথে ব্যবহৃত ফাংশনের ধরন

তিনটি প্রধান ধরনের ফাংশন রয়েছে যেগুলিতে উইন্ডো ফাংশনগুলি সারিগুলির একটি সেটের উপর প্রয়োগ করা যেতে পারে (একটি তথাকথিত উইন্ডো): এগুলি হল সমষ্টি, র‌্যাঙ্কিং এবং মান ফাংশন৷ নীচের ছবিতে, আপনি প্রতিটি বিভাগে পড়ে বিভিন্ন ফাংশনের নাম দেখতে পারেন।

সমষ্টিগত ফাংশন

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

• SUM: কলামের সমস্ত মান যোগ করে

• COUNT: NULL মান বাদ দিয়ে কলামে মানের সংখ্যা গণনা করে

• AVG: কলামে গড় মান খুঁজে বের করে

• MAX: কলামের সর্বোচ্চ মান চিহ্নিত করে

• MIN: কলামের সর্বনিম্ন মান চিহ্নিত করে

  
  

র‌্যাঙ্কিং ফাংশন

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

• ROW_NUMBER: একটি পার্টিশনে প্রতিটি নতুন রেকর্ডে একটি ক্রমিক র‌্যাঙ্ক নম্বর বরাদ্দ করে
• RANK: ফলাফল সেটে প্রতিটি সারির জন্য র‌্যাঙ্ক নির্দিষ্ট করে। এই ক্ষেত্রে, যদি সিস্টেমটি অভিন্ন মানগুলি সনাক্ত করে, তবে এটি তাদের জন্য একই র‌্যাঙ্ক বরাদ্দ করবে এবং পরবর্তী মানটি এড়িয়ে যাবে।
• DENSE_RANK: ফলাফল সেটের একটি পার্টিশনের মধ্যে প্রতিটি সারিতে একটি র‍্যাঙ্ক নির্ধারণ করে। RANK ফাংশনের বিপরীতে, ফাংশনটি পরবর্তী কোনো মান এড়িয়ে না গিয়ে অভিন্ন মানের জন্য র‌্যাঙ্ক প্রদান করে।
• NTILE: বর্তমান লাইনটি কোন গ্রুপের অন্তর্গত তা আমাদের নির্ধারণ করতে দেয়। গ্রুপের সংখ্যা বন্ধনীতে দেওয়া আছে।
  

মান ফাংশন

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

• LAG বা LEAD: একটি স্ব-যোগদান অপারেশন না করেই পূর্ববর্তী বা পরবর্তী সারি থেকে ডেটা অ্যাক্সেস করুন। এই ফাংশনগুলি বিশেষভাবে সহায়ক যখন সমস্যাগুলি সমাধান করার জন্য যেগুলির জন্য একই ফলাফল সেট বা পার্টিশনের মধ্যে একটি সারির সাথে অন্য সারির তুলনা করা প্রয়োজন, যেমন সময়ের সাথে পার্থক্য গণনা করা।
• FIRST_VALUE বা LAST_VALUE: একটি সংজ্ঞায়িত উইন্ডো বা পার্টিশন থেকে প্রথম বা শেষ মান পুনরুদ্ধার করুন। এই ফাংশনগুলি বিশেষভাবে কার্যকর যখন আপনি একটি নির্দিষ্ট সময়ের মধ্যে পার্থক্য গণনা করতে চান।

উইন্ডো ফাংশনগুলির সাথে শুরু করতে, আসুন একটি অনুমানমূলক 'বেতন' টেবিল তৈরি করি এবং এটি ডেটা দিয়ে পূরণ করি।

টেবিল তৈরি:

 create table salary ( employee_id smallint, employee_name varchar(10), department varchar(20), city varchar(20), salary real )

টেবিল পূরণ করা:

 insert into salary values ( 1 ,'Tony' ,'R&D', 'New York', 3000); insert into salary values ( 2 ,'James' ,'Project management', 'London', 4000); insert into salary values ( 3 ,'Dina' ,'Engineering', 'Tokyo', 5000); insert into salary values ( 4 ,'Annie' ,'Security', 'London', 3000); insert into salary values ( 5 ,'Tom' ,'R&D', 'New York', 3500); insert into salary values ( 6 ,'Stan' ,'Project management', 'New York', 4200); insert into salary values ( 7 ,'Jessa' ,'Sales', 'London', 5300); insert into salary values ( 8 ,'Ronnie' ,'R&D', 'Tokyo', 2900); insert into salary values ( 9 ,'Andrew' ,'Engineering', 'New York', 1500); insert into salary values (10,'Dean' ,'Sales', 'Tokyo', 3700)

আমরা সফলভাবে 'বেতন' টেবিলটি পূরণ করেছি কিনা তা পরীক্ষা করা যাক:

 select * from salary 

পরবর্তী প্রশ্ন আমাদের টেবিল থেকে কর্মীদের নাম এবং বেতন দেখাবে:

 select employee_name, salary from salary 

বেতনের যোগফল, গড় বেতন, সর্বোচ্চ, সর্বনিম্ন এবং সারির সংখ্যা গণনা করা হল সামগ্রিক ফাংশনের কিছু সাধারণ ব্যবহারের ক্ষেত্রে:

যখন একটি সমষ্টিগত ফাংশন প্রয়োগ করা হয়, তখন বেতনগুলি একত্রিত হয় এবং এক লাইনে দেখানো হয়।

কিন্তু আমরা যদি 'বেতন' টেবিল থেকে কর্মচারীদের নাম এবং বেতন এবং তৃতীয় কলামে, সমস্ত বেতনের সমষ্টি প্রদর্শন করতে চাই? এই মান সব সারি জন্য একই হতে হবে.

এটি একটি উইন্ডো ফাংশন ব্যবহার করার একটি মহান সুযোগ!

 select employee_name, salary, sum(salary) over() as sum_salary from salary 

আসুন উইন্ডো ফাংশনটি ঘনিষ্ঠভাবে দেখে নেওয়া যাক যা sum(salary) over() এর প্রতিটি সারিতে বেতনের যোগফল গণনা করে।

over() এক্সপ্রেশনটি একটি উইন্ডো বা সারিগুলির একটি সেট সংজ্ঞায়িত করে যার উপর ফাংশনটি কাজ করে। আমাদের উদাহরণে, উইন্ডোটি পুরো টেবিল, যার অর্থ ফাংশনটি সমস্ত সারি জুড়ে প্রযোজ্য হবে।

over() এক্সপ্রেশনটি তখনই কাজ করে যখন over() এর আগে অনুরোধ করা ফাংশনগুলির সাথে পেয়ার করা হয়।

উদাহরণস্বরূপ, sum(salary) over() , যেখানে sum() একটি সমষ্টিগত ফাংশন। এবং পুরো এক্সপ্রেশনের sum(salary) over() হল একটি সামগ্রিক উইন্ডো ফাংশন।

আমি আগেই বলেছি, যে সমস্ত ফাংশনগুলিতে উইন্ডো ফাংশন প্রয়োগ করা হয় সেগুলিকে তিনটি গ্রুপে ভাগ করা যেতে পারে: সমষ্টি, র‌্যাঙ্কিং এবং মান ফাংশন।

সমষ্টিগত ফাংশন sum() , count() , avg() , min() , max() একত্রে over() এক্সপ্রেশন একত্রিত উইন্ডো ফাংশনের একটি গ্রুপ তৈরি করে।

এই নিবন্ধে, আমরা এই নির্দিষ্ট ধরণের উইন্ডো ফাংশনগুলিতে মনোনিবেশ করব।

মোট উইন্ডো ফাংশন

উদাহরণে ফিরে!

কর্মচারীদের নাম অনুরোধ করা যাক; তাদের বেতন; সমস্ত বেতনের যোগফল; গড়, সর্বোচ্চ এবং সর্বনিম্ন বেতন; কর্মীদের সংখ্যা।

 select employee_name, salary, sum(salary) over(), avg(salary) over(), max(salary) over(), min(salary) over(), count(*) over() from salary 

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

 select employee_name, salary, sum(salary)over(), salary/sum(salary)over() as share from salary order by salary/sum(salary)over() desc 

আমরা চতুর্থ কলামে প্রতিটি বেতনের জন্য মোট বেতন বাজেটের শতাংশ গণনা করেছি। জেসার বেতনের পরিমাণ বেতনের জন্য পুরো বাজেটের প্রায় 15%।

মনে রাখবেন যে আমরা সেই সূত্রটিও রেখেছি যা শতাংশের salary/sum(salary)over() এর দ্বারা order by গণনা করে। একটি উইন্ডো ফাংশন শুধুমাত্র আউটপুট select নয়, order by পাওয়া যাবে।

আরেকটি উদাহরণ: আসুন কোম্পানির গড় বেতনের সাথে বেতনের তুলনা করি।

 select employee_name, salary, avg(salary)over(), salary-avg(salary)over() as diff_salary from salary order by salary-avg(salary)over() 

আমরা দেখতে পাচ্ছি, অ্যান্ড্রু-এর বেতন গড়ের চেয়ে 2110 কম, এবং জেসার গড় থেকে 1690 বেশি।

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

 select employee_name, department, salary from salary order by department 

এখন আমরা একই তিনটি কলাম এবং সমস্ত কর্মচারীদের বেতনের যোগফল সহ একটি কলাম অনুরোধ করব। আপনি ইতিমধ্যে জানেন যে এটি একটি উইন্ডো ফাংশন দিয়ে করা যেতে পারে।

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

কিন্তু আমরা যদি সব বেতনের যোগফল নয়, প্রতিটি বিভাগের জন্য বেতনের সমষ্টি অনুরোধ করতে চাই, যেমনটি শেষ কলামে দেখানো হয়েছে:

প্রকৌশল বিভাগের কর্মচারীদের বেতন 6500, PM বিভাগের বেতন 8200, R&D - 9400, বিক্রয় - 9000, এবং নিরাপত্তা বিভাগের - 3000।

over() এক্সপ্রেশনে প্যারামিটার partition by যোগ করে আমরা এটি করতে পারি:

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

Partition by আমাদের উইন্ডো ফাংশনটি সমস্ত সারিগুলিতে (সম্পূর্ণ উইন্ডোতে) নয়, কিন্তু কলাম বিভাগে প্রয়োগ করতে দেয়।

এটি একটি সহজ গ্রুপিং মত দেখায় না? প্রতিটি বিভাগের জন্য বেতনের যোগফল গণনা করতে, আমরা বিভাগগুলির দ্বারা একটি গ্রুপিং করব (উইন্ডো ফাংশনের স্ল্যাংয়ে বিভাগগুলি) এবং পরিমাণ গণনা করব:

 select department, sum(salary) from salary group by department 

সারমর্মে, গোষ্ঠীবদ্ধকরণ এবং partition by মধ্যে পার্থক্য হল যে group by প্রতি গোষ্ঠীতে একটি সারি প্রদান করা হয়, যখন partition by , যদিও ফাংশনের ফলাফলগুলি group by সহ একটি সমষ্টিগত ফাংশনের ফলাফলের সাথে অভিন্ন, সমষ্টি সহ সমস্ত সারি প্রদান করে একটি গ্রুপের উপর ভিত্তি করে ফাংশন।

আসুন উইন্ডো ফাংশনে ফিরে যাই:

 select employee_name, department, salary, sum(salary)over(partition by department), salary/sum(salary)over(partition by department) as shape from salary order by department 

উইন্ডো ফাংশন ব্যবহার করে, বিশেষ করে প্যারামিটার partition by , আমরা বিভাগের বেতনের যোগফল থেকে প্রতিটি কর্মচারীর বেতনের ভাগ গণনা করতে পারি। অথবা, উদাহরণস্বরূপ, বিভাগের গড় বেতনের সাথে বেতনের তুলনা করা।

কী Takeaways

সংক্ষেপে:

• একটি উইন্ডো ফাংশন সারিগুলির একটি সেট জুড়ে একটি গণনা করে যা বর্তমান সারির সাথে কোনওভাবে সম্পর্কিত,

• প্রধান ধরনের ফাংশন যেখানে উইন্ডো ফাংশন প্রয়োগ করা হয় তা হল সমষ্টি, র‌্যাঙ্কিং এবং মান ফাংশন,

• একটি উইন্ডো ফাংশন ব্যবহার করার জন্য, আপনাকে over() ধারাটি প্রয়োগ করতে হবে যা একটি ক্যোয়ারী ফলাফল সেটের মধ্যে একটি উইন্ডো (সারিগুলির একটি সেট) সংজ্ঞায়িত করে। উইন্ডো ফাংশন তারপর উইন্ডোতে প্রতিটি সারির জন্য একটি মান গণনা করে,

• আপনি যে কলামটির জন্য একত্রীকরণ করতে চান তা নির্দিষ্ট করতে, আপনাকে over() ধারায় ধারা partition by যোগ করতে হবে। Partition by কিছুটা গোষ্ঠীকরণের অনুরূপ কিন্তু প্রতি গোষ্ঠীতে একটি সারি না করে প্রয়োগকৃত সমষ্টিগত ফাংশন সহ সমস্ত সারি প্রদান করে।

  
  

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