মধ্যে
তাই এখন, আপনি মৌলিক বিষয়গুলির সাথে পরিচিত, আসুন আমরা আরও উন্নত 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 
আমি অনুমান করি যে এখানে কী ঘটেছে তা আমাদের আরও ঘনিষ্ঠভাবে দেখতে হবে:
প্রথমত, এমপ্লয়ি_আইডি এখন নিচের ক্রমে সাজানো হয়েছে।
উইন্ডো ফাংশন প্রয়োগের ফলে কলামে, এখন একটি ক্রমবর্ধমান যোগফল রয়েছে।
আমি বিশ্বাস করি আপনি ক্রমবর্ধমান সমষ্টির সাথে পরিচিত। এর সারমর্মটি সহজ - ক্রমবর্ধমান যোগফল বা চলমান মোটের অর্থ "এখন পর্যন্ত কত।" ক্রমবর্ধমান যোগফলের সংজ্ঞা হল একটি প্রদত্ত ক্রমগুলির যোগফল যা আরও যোগ করার সাথে বৃদ্ধি পাচ্ছে বা বড় হচ্ছে।
আমাদের উদাহরণে এখানে যা আছে: সর্বোচ্চ কর্মচারী_আইডি মান সহ কর্মচারীর জন্য, বেতন 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
এই ক্ষেত্রে, ক্রমবর্ধমান মোট বিভাগ দ্বারা গণনা করা হবে।
এনবি ! যদি উভয় বৈশিষ্ট্যই over() এক্সপ্রেশনে নির্দিষ্ট করা থাকে, তাহলে পার্টিশনটি সর্বদা প্রথমে আসে, এর পরে ক্রম দ্বারা । উদাহরণস্বরূপ: over(কর্মচারী_আইডি দ্বারা বিভাগের আদেশ দ্বারা বিভাজন) ।
আমরা ক্রমবর্ধমান সমষ্টি নিয়ে আলোচনা করার পরে, আমাদের বলতে হবে যে সম্ভবত এটিই একমাত্র প্রকার ক্রমবর্ধমান মোট যা প্রায়শই ব্যবহৃত হয়। ক্রমবর্ধমান গড় এবং ক্রমবর্ধমান গণনা, বিপরীতে, খুব কমই ব্যবহৃত হয়।
তবুও, আমরা ক্রমবর্ধমান গড় গণনার একটি উদাহরণ দেব — এটি আমাদেরকে একটি নির্দিষ্ট বিন্দু পর্যন্ত মানের সিরিজের গড় বলে:
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
আসুন প্রতিটি র্যাঙ্কিং উইন্ডো ফাংশন দিয়ে যাই:
উইন্ডো ফাংশন row_number() over(অর্ডার by salary desc) সারিগুলিকে বেতনের নিচের ক্রম অনুসারে স্থান দেয় এবং সারি সংখ্যা নির্ধারণ করে। উল্লেখ্য যে অ্যানি এবং টনির একই বেতন রয়েছে, তবে তাদের আলাদা নম্বর দেওয়া হয়েছে।
উইন্ডো ফাংশন র্যাঙ্ক() ওভার (বেতনের বিবরণ অনুসারে ক্রম ) বেতনের নিচের ক্রম অনুসারে র্যাঙ্ক নির্ধারণ করে। এটি অভিন্ন মানের জন্য একই র্যাঙ্ক বরাদ্দ করে, কিন্তু পরবর্তী মানটি একটি নতুন সারি নম্বর পায়।
উইন্ডো ফাংশন dense_rank() over(অর্ডার by salary desc) বেতনের নিচের ক্রম অনুসারে র্যাঙ্ক নির্ধারণ করে। এটি অভিন্ন মানগুলির জন্য একই র্যাঙ্ক বরাদ্দ করে।
উইন্ডো ফাংশন percent_rank() over(বেতনের বিবরণ অনুসারে অর্ডার) হল বর্তমান সারির আপেক্ষিক (শতাংশ) র্যাঙ্ক, সূত্র দ্বারা গণনা করা হয়: (র্যাঙ্ক - 1) / (পার্টিশনে মোট সারির সংখ্যা - 1)।
উইন্ডো ফাংশন ntile(5) ওভার (বেতনের বিবরণ অনুসারে) সারির সংখ্যাকে 5টি সমান অংশে ভাগ করে এবং প্রতিটি অংশে একটি সংখ্যা নির্ধারণ করে। এনটাইল(5) ফাংশনের ভিতরে অংশের সংখ্যা নির্দিষ্ট করা আছে।
এনবি ! মোট ফাংশনের বিপরীতে, যেমন, যোগফল(বেতন), র্যাঙ্কিং ফাংশন, যেমন, row_number(), ভিতরে একটি কলাম নিবেন না। যাইহোক, ntile(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
আপনি দেখতে পাচ্ছেন, ফাংশন ল্যাগ (বেতন) ওভার (বেতন অনুসারে) বেতনগুলিকে এক সারি দ্বারা নীচে স্থানান্তরিত করে, এবং ফাংশন লিড (বেতন) ওভার (বেতন অনুসারে অর্ডার) বেতনগুলিকে এক সারি দ্বারা উপরে স্থানান্তরিত করে। যদিও এই ফাংশনগুলি বেশ একই রকম, আমি ল্যাগ() ব্যবহার করা আরও সুবিধাজনক বলে মনে করি।
এনবি ! এই ফাংশনের জন্য, over() এক্সপ্রেশনে প্যারামিটার দ্বারা ক্রম নির্দিষ্ট করা বাধ্যতামূলক। আপনি পার্টিশন দ্বারা পার্টিশন ব্যবহার করেও বিভাজন নির্দিষ্ট করতে পারেন, তবে এটি বাধ্যতামূলক নয়।
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-এ উইন্ডো ফাংশনগুলির ক্ষমতাগুলি আরও ভালভাবে বুঝতে সাহায্য করবে এবং আপনাকে রুটিন কাজগুলিতে আরও আত্মবিশ্বাসী এবং দ্রুত করে তুলবে৷
