Practical MySQL & MariaDB

MySQL ROW_NUMBER() ব্যাখ্যা (MySQL 8.0): র‍্যাঙ্কিং, টপ-এন কুয়েরি এবং ডেডুপ্লিকেশন

ফাংশনসমূহ
目次

১. পরিচিতি

MySQL সংস্করণ 8.0 অনেক নতুন বৈশিষ্ট্য নিয়ে এসেছে, এবং তার মধ্যে সবচেয়ে উল্লেখযোগ্য হল উইন্ডো ফাংশনের সমর্থন। এই প্রবন্ধে আমরা সবচেয়ে বেশি ব্যবহৃত ফাংশনগুলোর একটি: ROW_NUMBER()-এর উপর মনোযোগ দেব।

ROW_NUMBER() ফাংশন ডেটা বিশ্লেষণ ও রিপোর্টিংয়ের জন্য শক্তিশালী ক্ষমতা প্রদান করে, নির্দিষ্ট শর্তের ভিত্তিতে ডেটা সাজানো ও র‍্যাঙ্ক করা সহজ করে। এই প্রবন্ধে মৌলিক ব্যবহার, ব্যবহারিক উদাহরণ এবং পুরোনো MySQL সংস্করণের বিকল্প পদ্ধতি সবকিছু ব্যাখ্যা করা হয়েছে।

লক্ষ্য পাঠকগণ

  • মৌলিক SQL জ্ঞানযুক্ত নবীন থেকে মধ্যম স্তরের ব্যবহারকারী
  • MySQL ব্যবহার করে ডেটা প্রক্রিয়াকরণ ও বিশ্লেষণকারী ইঞ্জিনিয়ার ও ডেটা বিশ্লেষক
  • সর্বশেষ MySQL সংস্করণে মাইগ্রেট করার কথা ভাবছেন এমন সবাই

ROW_NUMBER() এর সুবিধা

এই ফাংশন আপনাকে নির্দিষ্ট শর্তের ভিত্তিতে প্রতিটি সারিতে একটি অনন্য সংখ্যা নির্ধারণ করতে দেয়। উদাহরণস্বরূপ, “বিক্রয়ের অবরোহ ক্রমে র‍্যাঙ্ক তৈরি করা” বা “ডুপ্লিকেট ডেটা বের করে সাজানো” এমন কুয়েরি সহজে লিখতে পারবেন।

পুরোনো সংস্করণে, ব্যবহারকারী-সংজ্ঞায়িত ভেরিয়েবল ব্যবহার করে জটিল কুয়েরি লিখতে হতো। ROW_NUMBER() ব্যবহার করলে আপনার SQL সহজ এবং পাঠযোগ্য হয়ে যায়।

এই প্রবন্ধে আমরা নির্দিষ্ট কুয়েরি উদাহরণ ব্যবহার করে তা নবীন-বন্ধু শৈলীতে ব্যাখ্যা করব। পরবর্তী অংশে আমরা এই ফাংশনের মৌলিক সিনট্যাক্স ও আচরণকে আরও বিস্তারিতভাবে দেখব।

২. ROW_NUMBER() ফাংশন কী?

ROW_NUMBER() ফাংশন, MySQL 8.0-এ নতুনভাবে যোগ করা একটি উইন্ডো ফাংশন, যা সারিগুলিকে ধারাবাহিক সংখ্যা প্রদান করে। এটি নির্দিষ্ট ক্রমে এবং/অথবা প্রতিটি গ্রুপের মধ্যে সারিগুলিকে নম্বরায়িত করতে পারে, যা ডেটা বিশ্লেষণ ও রিপোর্টিংয়ের জন্য অত্যন্ত উপযোগী। এখানে আমরা ব্যবহারিক উদাহরণসহ মৌলিক সিনট্যাক্স বিশদভাবে ব্যাখ্যা করব।

ROW_NUMBER() এর মৌলিক সিনট্যাক্স

প্রথমে, ROW_NUMBER()-এর মৌলিক ফরম্যাট নিম্নরূপ।

SELECT
    column_name,
    ROW_NUMBER() OVER (PARTITION BY group_column ORDER BY sort_column) AS row_num
FROM
    table_name;

প্রতিটি উপাদানের অর্থ

  • ROW_NUMBER() : প্রতিটি সারিতে ধারাবাহিক সংখ্যা নির্ধারণ করে।
  • OVER : উইন্ডো ফাংশনের উইন্ডো সংজ্ঞায়িত করতে ব্যবহৃত কীওয়ার্ড।
  • PARTITION BY : নির্দিষ্ট কলামের ভিত্তিতে ডেটা গ্রুপ করে। ঐচ্ছিক। যদি বাদ দেওয়া হয়, তবে সব সারিতে একসাথে নম্বরায়ন করা হয়।
  • ORDER BY : নম্বর নির্ধারণের জন্য ব্যবহৃত সাজানোর ক্রম, অর্থাৎ সোর্টিং মানদণ্ড নির্ধারণ করে।

মৌলিক উদাহরণ

উদাহরণস্বরূপ, ধরুন আপনার কাছে “sales” নামের একটি টেবিল আছে, যার ডেটা নিম্নরূপ।

employeedepartmentsale
ASales Department500
BSales Department800
CDevelopment Department600
DDevelopment Department700

বিক্রয়ের অবরোহ ক্রমে প্রতিটি বিভাগে ধারাবাহিক সংখ্যা নির্ধারণ করতে নিম্নলিখিত কুয়েরি ব্যবহার করুন।

SELECT
    employee,
    department,
    sale,
    ROW_NUMBER() OVER (PARTITION BY department ORDER BY sale DESC) AS row_num
FROM
    sales;

ফলাফল

employeedepartmentsalerow_num
BSales Department8001
ASales Department5002
DDevelopment Department7001
CDevelopment Department6002

এই ফলাফল থেকে দেখা যায় যে প্রতিটি বিভাগের মধ্যে বিক্রয়ের র‍্যাঙ্ক প্রদর্শিত হয়েছে।

PARTITION BY কীভাবে ব্যবহার করবেন

উপরের উদাহরণে, ডেটা “department” কলামের ভিত্তিতে গ্রুপ করা হয়েছে। এটি প্রতিটি বিভাগের জন্য একটি আলাদা ক্রম নির্ধারণ করে।

যদি PARTITION BY বাদ দেন, তবে সব সারিতে একক ক্রমে নম্বরায়ন করা হয়।

SELECT
    employee,
    sale,
    ROW_NUMBER() OVER (ORDER BY sale DESC) AS row_num
FROM
    sales;

ফলাফল

employeesalerow_num
B8001
D7002
C6003
A5004

ROW_NUMBER() এর বৈশিষ্ট্য ও সতর্কতা

  • অনন্য নম্বরায়ন : মান একই হলেও নির্ধারিত সংখ্যা অনন্য থাকে।
  • NULL হ্যান্ডলিং : যদি ORDER BY-তে NULL থাকে, তবে ঊর্ধ্বক্রমে প্রথমে এবং অবরোহক্রমে শেষের দিকে প্রদর্শিত হয়।
  • পারফরম্যান্স প্রভাব : বড় ডেটাসেটের ক্ষেত্রে ORDER BY ব্যয়বহুল হতে পারে, তাই সঠিক ইনডেক্সিং গুরুত্বপূর্ণ।

৩. ব্যবহারিক কেসসমূহ

এখানে MySQL-এর ROW_NUMBER() ফাংশন ব্যবহার করে বাস্তবিক দৃশ্যপটগুলো উপস্থাপন করা হয়েছে। এই ফাংশনটি ডেটা র‍্যাঙ্কিং এবং ডুপ্লিকেট হ্যান্ডলিংয়ের মতো বহু বাস্তবিক ক্ষেত্রে উপযোগী।

৩-১. প্রতিটি গ্রুপের মধ্যে র‍্যাঙ্কিং

উদাহরণস্বরূপ, ধরুন আপনি বিক্রয় ডেটা ব্যবহার করে “প্রতিটি বিভাগের মধ্যে বিক্রয় অনুযায়ী কর্মচারীদের র‍্যাঙ্ক করতে” চান। নিচের ডেটাসেটটি উদাহরণ হিসেবে ব্যবহার করুন।

employeedepartmentsale
ASales Department500
BSales Department800
CDevelopment Department600
DDevelopment Department700

কোয়েরি উদাহরণ: বিভাগের ভিত্তিতে বিক্রয় র‍্যাঙ্কিং

SELECT
    employee,
    department,
    sale,
    ROW_NUMBER() OVER (PARTITION BY department ORDER BY sale DESC) AS rank
FROM
    sales;

ফলাফল:

employeedepartmentsalerank
BSales Department8001
ASales Department5002
DDevelopment Department7001
CDevelopment Department6002

এইভাবে, প্রতিটি বিভাগ বিক্রয়ের অবরোহ ক্রমে নিজস্ব ক্রম পায়, যা র‍্যাঙ্ক তৈরি করা সহজ করে।

3-2. শীর্ষ N সারি বের করা

পরবর্তীতে, আমরা এমন একটি ক্ষেত্রে নজর দেব যেখানে আপনি “প্রতিটি বিভাগের মধ্যে বিক্রয় অনুযায়ী শীর্ষ ৩ জন কর্মচারী বের করতে” চান।

কোয়েরি উদাহরণ: শীর্ষ N সারি বের করা

WITH RankedSales AS (
    SELECT
        employee,
        department,
        sale,
        ROW_NUMBER() OVER (PARTITION BY department ORDER BY sale DESC) AS rank
    FROM
        sales
)
SELECT
    employee,
    department,
    sale
FROM
    RankedSales
WHERE
    rank <= 3;

ফলাফল:

employeedepartmentsale
BSales Department800
ASales Department500
DDevelopment Department700
CDevelopment Department600

এই উদাহরণটি প্রতিটি বিভাগের মধ্যে বিক্রয় অনুযায়ী শুধুমাত্র শীর্ষ ৩টি সারি পুনরুদ্ধার করে। আপনি দেখতে পাচ্ছেন, ROW_NUMBER() র‍্যাঙ্কিংয়ের পাশাপাশি শীর্ষ ফলাফল ফিল্টার করার জন্যও উপযুক্ত।

3-3. ডুপ্লিকেট ডেটা খুঁজে বের করা এবং মুছে ফেলা

ডাটাবেসে কখনও কখনও ডুপ্লিকেট রেকর্ড থাকে। এমন ক্ষেত্রে, আপনি ROW_NUMBER() ব্যবহার করে সেগুলি সহজে পরিচালনা করতে পারেন।

কোয়েরি উদাহরণ: ডুপ্লিকেট সনাক্ত করা

SELECT *
FROM (
    SELECT
        employee,
        sale,
        ROW_NUMBER() OVER (PARTITION BY employee ORDER BY sale DESC) AS rank
    FROM
        sales
) tmp
WHERE rank > 1;

একই কর্মচারীর নামের জন্য একাধিক রেকর্ড থাকলে এই কোয়েরি ডুপ্লিকেট সনাক্ত করে।

কোয়েরি উদাহরণ: ডুপ্লিকেট মুছে ফেলা

DELETE FROM sales
WHERE id IN (
    SELECT id
    FROM (
        SELECT
            id,
            ROW_NUMBER() OVER (PARTITION BY employee ORDER BY sale DESC) AS rank
        FROM
            sales
    ) tmp
    WHERE rank > 1
);

সারাংশ

ROW_NUMBER() বিভিন্ন পরিস্থিতিতে উপযোগী, যেমন:

  1. প্রতিটি গ্রুপের মধ্যে র‍্যাঙ্কিং
  2. শীর্ষ N সারি বের করা
  3. ডুপ্লিকেট সনাক্ত করা এবং মুছে ফেলা

এটি জটিল ডেটা প্রক্রিয়াকরণ এবং বিশ্লেষণকে সহজ ও আরও কার্যকর করে।

4. অন্যান্য উইন্ডো ফাংশনের সঙ্গে তুলনা

MySQL 8.0-এ, ROW_NUMBER() ছাড়াও RANK() এবং DENSE_RANK() এর মতো উইন্ডো ফাংশন রয়েছে যা র‍্যাঙ্কিং এবং অবস্থান গণনার জন্য ব্যবহার করা যায়। যদিও তাদের ভূমিকা সমান, তাদের আচরণ ও ফলাফল ভিন্ন। এখানে আমরা প্রতিটি ফাংশন তুলনা করব এবং কখন ব্যবহার করতে হবে তা ব্যাখ্যা করব।

4-1. RANK() ফাংশন

RANK() ফাংশন র‍্যাঙ্ক নির্ধারণ করে, সমান মানের জন্য একই র‍্যাঙ্ক দেয় এবং পরবর্তী র‍্যাঙ্ক নম্বরটি বাদ দেয়।

মৌলিক সিনট্যাক্স

SELECT
    column_name,
    RANK() OVER (PARTITION BY group_column ORDER BY sort_column) AS rank
FROM
    table_name;

উদাহরণ

নিচের ডেটা ব্যবহার করে বিক্রয় র‍্যাঙ্ক গণনা করুন।

employeedepartmentsale
ASales Department800
BSales Department800
CSales Department600
DSales Department500

কোয়েরি উদাহরণ: RANK() ব্যবহার করা

SELECT
    employee,
    sale,
    RANK() OVER (ORDER BY sale DESC) AS rank
FROM
    sales;

ফলাফল:

employeesalerank
A8001
B8001
C6003
D5004

মূল বিষয়গুলো:

  • একই বিক্রয় পরিমাণ (800) থাকা A এবং B উভয়ই র‍্যাঙ্ক “1” পায়।
  • পরবর্তী র‍্যাঙ্ক “2” বাদ যায়, তাই C র‍্যাঙ্ক “3” পায়।

4-2. DENSE_RANK() ফাংশন

DENSE_RANK() ফাংশনও সমান মানের জন্য একই র‍্যাঙ্ক দেয়, তবে এটি পরবর্তী র‍্যাঙ্ক নম্বরটি বাদ দেয় না।

মৌলিক সিনট্যাক্স

SELECT
    column_name,
    DENSE_RANK() OVER (PARTITION BY group_column ORDER BY sort_column) AS dense_rank
FROM
    table_name;

উদাহরণ

উপরের একই ডেটা ব্যবহার করে, DENSE_RANK() ফাংশনটি চেষ্টা করুন।

কোয়েরি উদাহরণ: DENSE_RANK() ব্যবহার করা

SELECT
    employee,
    sale,
    DENSE_RANK() OVER (ORDER BY sale DESC) AS dense_rank
FROM
    sales;

ফলাফল:

employeesaledense_rank
A8001
B8001
C6002
D5003

মূল বিষয়গুলো:

  • একই বিক্রয় পরিমাণ (800) থাকা A এবং B উভয়কেই র‍্যাঙ্ক “1” হিসেবে গণ্য করা হয়।
  • RANK() এর বিপরীতে, পরের র‍্যাঙ্ক “2” থেকে শুরু হয়, ফলে র‍্যাঙ্কের ধারাবাহিকতা বজায় থাকে।

4-3. ROW_NUMBER() কীভাবে ভিন্ন

ROW_NUMBER() ফাংশনটি অন্য দুটির থেকে ভিন্ন কারণ এটি একটি অনন্য সংখ্যা নির্ধারণ করে, যদিও মানগুলো একই হয়।

উদাহরণ

SELECT
    employee,
    sale,
    ROW_NUMBER() OVER (ORDER BY sale DESC) AS row_num
FROM
    sales;

ফলাফল:

employeesalerow_num
A8001
B8002
C6003
D5004

মূল বিষয়বস্তু:

  • যদিও মানগুলো একই, প্রতিটি সারি একটি অনন্য সংখ্যা পায়, ফলে ডুপ্লিকেট র‍্যাঙ্ক থাকে না।
  • এটি তখন উপকারী যখন আপনাকে কঠোর ক্রম নিয়ন্ত্রণ বা প্রতিটি সারির অনন্যতা প্রয়োজন।

4-4. দ্রুত ব্যবহার-কেস সারাংশ

FunctionRanking behaviorTypical use case
ROW_NUMBER()Assigns a unique numberWhen you need sequential numbering or unique identification per row
RANK()Same rank for ties; skips the next rank numberWhen you want rankings with gaps reflecting ties
DENSE_RANK()Same rank for ties; does not skip rank numbersWhen you want continuous ranks without gaps

সারাংশ

ROW_NUMBER(), RANK(), এবং DENSE_RANK() পরিস্থিতি অনুযায়ী যথাযথভাবে ব্যবহার করা উচিত।

  1. ROW_NUMBER() সর্বোত্তম যখন আপনাকে প্রতিটি সারির জন্য অনন্য সংখ্যা প্রয়োজন।
  2. RANK() উপকারী যখন আপনি চান সমান মানগুলো একই র‍্যাঙ্ক শেয়ার করুক এবং র‍্যাঙ্কের ফাঁকগুলোকে জোর দিতে চান।
  3. DENSE_RANK() উপযুক্ত যখন আপনি ফাঁক ছাড়া ধারাবাহিক র‍্যাঙ্ক চান।

5. MySQL 8.0 এর নিচের সংস্করণের বিকল্পসমূহ

MySQL 8.0 এর আগের সংস্করণগুলোতে, ROW_NUMBER() এবং অন্যান্য উইন্ডো ফাংশন সমর্থিত নয়। তবে, আপনি ইউজার-ডিফাইন্ড ভেরিয়েবল ব্যবহার করে সমান আচরণ অর্জন করতে পারেন। এই অংশে MySQL 8.0 এর নিচের সংস্করণের জন্য ব্যবহারিক বিকল্পগুলো ব্যাখ্যা করা হয়েছে।

5-1. ইউজার-ডিফাইন্ড ভেরিয়েবল ব্যবহার করে ধারাবাহিক নম্বরিং

MySQL 5.7 এবং তার আগের সংস্করণে, আপনি ইউজার-ডিফাইন্ড ভেরিয়েবল ব্যবহার করে প্রতিটি সারিতে ধারাবাহিক সংখ্যা নির্ধারণ করতে পারেন। চলুন নিম্নলিখিত উদাহরণটি দেখি।

উদাহরণ: বিভাগ অনুযায়ী বিক্রয় র‍্যাঙ্কিং

নমুনা ডেটা:

employeedepartmentsale
ASales Department500
BSales Department800
CDevelopment Department600
DDevelopment Department700

কোয়েরি:

SET @row_num = 0;
SET @dept = '';

SELECT
    employee,
    department,
    sale,
    @row_num := IF(@dept = department, @row_num + 1, 1) AS rank,
    @dept := department
FROM
    (SELECT * FROM sales ORDER BY department, sale DESC) AS sorted_sales;

ফলাফল:

employeedepartmentsalerank
BSales Department8001
ASales Department5002
DDevelopment Department7001
CDevelopment Department6002

5-2. শীর্ষ N সারি বের করা

শীর্ষ N সারি পুনরুদ্ধার করতে, আপনি একইভাবে ইউজার-ডিফাইন্ড ভেরিয়েবল ব্যবহার করতে পারেন।

কোয়েরি:

SET @row_num = 0;
SET @dept = '';

SELECT *
FROM (
    SELECT
        employee,
        department,
        sale,
        @row_num := IF(@dept = department, @row_num + 1, 1) AS rank,
        @dept := department
    FROM
        (SELECT * FROM sales ORDER BY department, sale DESC) AS sorted_sales
) AS ranked_sales
WHERE rank <= 3;

ফলাফল:

employeedepartmentsalerank
BSales Department8001
ASales Department5002
DDevelopment Department7001
CDevelopment Department6002

এই কোয়েরি বিভাগ অনুযায়ী র‍্যাঙ্ক নির্ধারণ করে এবং তারপর শীর্ষ ৩-টির মধ্যে থাকা সারিগুলোই বের করে।

5-3. ডুপ্লিকেট সনাক্তকরণ এবং মুছে ফেলা

আপনি ইউজার-ডিফাইন্ড ভেরিয়েবল ব্যবহার করে ডুপ্লিকেট ডেটা পরিচালনা করতে পারেন।

কোয়েরি উদাহরণ: ডুপ্লিকেট সনাক্তকরণ

SET @row_num = 0;
SET @id_check = '';

SELECT *
FROM (
    SELECT
        id,
        name,
        @row_num := IF(@id_check = name, @row_num + 1, 1) AS rank,
        @id_check := name
    FROM
        (SELECT * FROM customers ORDER BY name, id) AS sorted_customers
) AS tmp
WHERE rank > 1;

কোয়েরি উদাহরণ: ডুপ্লিকেট মুছে ফেলা

DELETE FROM customers
WHERE id IN (
    SELECT id
    FROM (
        SELECT
            id,
            @row_num := IF(@id_check = name, @row_num + 1, 1) AS rank,
            @id_check := name
        FROM
            (SELECT * FROM customers ORDER BY name, id) AS sorted_customers
    ) AS tmp
    WHERE rank > 1
);

5-4. ইউজার-ডিফাইন্ড ভেরিয়েবল ব্যবহার করার সময় সতর্কতা

  1. সেশন নির্ভরতা
  • ইউজার-ডিফাইন্ড ভেরিয়েবল শুধুমাত্র বর্তমান সেশনের মধ্যে বৈধ। এগুলো ভিন্ন কোয়েরি বা সেশনের মধ্যে পুনরায় ব্যবহার করা যায় না।
  1. প্রসেসিং ক্রমের উপর নির্ভরতা
  • ইউজার-ডিফাইন্ড ভেরিয়েবল এক্সিকিউশন ক্রমের উপর নির্ভরশীল, তাই ORDER BY সঠিকভাবে সেট করা অত্যন্ত গুরুত্বপূর্ণ।
  1. SQL পাঠযোগ্যতা এবং রক্ষণাবেক্ষণযোগ্যতা
  • কোয়েরিগুলো জটিল হয়ে যেতে পারে, তাই MySQL 8.0 এবং পরের সংস্করণে উইন্ডো ফাংশন ব্যবহার করার সুপারিশ করা হয়।

সারাংশ

In MySQL 8.0 এর নিচের সংস্করণগুলোতে, আপনি উইন্ডো ফাংশনের পরিবর্তে সিকোয়েন্সিয়াল নম্বরিং এবং র‍্যাঙ্কিং বাস্তবায়নের জন্য ইউজার-ডিফাইন্ড ভেরিয়েবল ব্যবহার করতে পারেন। তবে, কুয়েরিগুলি জটিল হয়ে যাওয়ার প্রবণতা থাকায়, সম্ভব হলে নতুন সংস্করণে মাইগ্রেট করা সর্বোত্তম।

6. সতর্কতা এবং সেরা অনুশীলন

MySQL এর ROW_NUMBER() ফাংশন এবং ভেরিয়েবল-ভিত্তিক বিকল্পগুলো খুবই সুবিধাজনক, তবে সেগুলোকে সঠিক ও কার্যকরভাবে চালানোর জন্য কিছু গুরুত্বপূর্ণ বিষয় মাথায় রাখা প্রয়োজন। এই বিভাগে পারফরম্যান্স অপ্টিমাইজেশনের জন্য ব্যবহারিক সতর্কতা এবং সেরা অনুশীলনগুলো ব্যাখ্যা করা হয়েছে।

6-1. পারফরম্যান্স বিবেচনা

1. ORDER BY খরচ

ROW_NUMBER() সর্বদা ORDER BY এর সাথে ব্যবহার করা হয়। যেহেতু এটি সাজানো প্রয়োজন, বড় ডেটাসেটের ক্ষেত্রে প্রক্রিয়াকরণের সময় উল্লেখযোগ্যভাবে বাড়তে পারে।

প্রতিকার:

  • ইন্ডেক্স ব্যবহার করুন: ORDER BY তে ব্যবহৃত কলামগুলিতে ইন্ডেক্স যোগ করুন যাতে সাজানো দ্রুত হয়।
  • LIMIT ব্যবহার করুন: প্রক্রিয়াকৃত ডেটার পরিমাণ কমাতে শুধুমাত্র প্রয়োজনীয় সারি সংখ্যা পুনরুদ্ধার করুন।

উদাহরণ:

SELECT
    employee,
    sale,
    ROW_NUMBER() OVER (PARTITION BY department ORDER BY sale DESC) AS rank
FROM
    sales
LIMIT 1000;

2. বাড়তি মেমরি ব্যবহার এবং ডিস্ক I/O

উইন্ডো ফাংশনগুলো অস্থায়ী টেবিল এবং মেমরি ব্যবহার করে প্রক্রিয়াকৃত হয়। ডেটার পরিমাণ বাড়ার সঙ্গে সঙ্গে মেমরি ব্যবহার এবং ডিস্ক I/O বৃদ্ধি পেতে পারে।

প্রতিকার:

  • কুয়েরি ভাগ করুন: প্রক্রিয়াকরণকে ছোট কুয়েরিতে ভাগ করুন এবং ধাপে ধাপে ডেটা বের করুন যাতে লোড কমে।
  • অস্থায়ী টেবিল ব্যবহার করুন: বের করা ডেটা অস্থায়ী টেবিলে সংরক্ষণ করুন এবং সেখান থেকে অ্যাগ্রিগেশন চালিয়ে কাজের চাপ বিতরণ করুন।

6-2. কুয়েরি টিউনিং টিপস

1. এক্সিকিউশন প্ল্যান চেক করুন

MySQL-এ, আপনি EXPLAIN ব্যবহার করে কুয়েরি এক্সিকিউশন প্ল্যান চেক করতে পারেন। এটি আপনাকে যাচাই করতে সাহায্য করে যে ইন্ডেক্সগুলো সঠিকভাবে ব্যবহার হচ্ছে কিনা।

উদাহরণ:

EXPLAIN
SELECT
    employee,
    ROW_NUMBER() OVER (PARTITION BY department ORDER BY sale DESC) AS rank
FROM
    sales;

উদাহরণ আউটপুট:

idselect_typetabletypepossible_keyskeykey_lenrefrowsExtra
1SIMPLEsalesindexNULLsale4NULL500Using index

যদি আপনি Using index দেখেন, তা নির্দেশ করে যে ইন্ডেক্সটি যথাযথভাবে ব্যবহার হচ্ছে।

2. ইন্ডেক্স অপ্টিমাইজ করুন

ORDER BY এবং WHERE-এ ব্যবহৃত কলামগুলিতে ইন্ডেক্স যোগ করা নিশ্চিত করুন। নিম্নলিখিত বিষয়গুলোতে বিশেষ মনোযোগ দিন।

  • সিঙ্গল-কলাম ইন্ডেক্স: সহজ সাজানোর শর্তের জন্য উপযুক্ত
  • কম্পোজিট ইন্ডেক্স: যখন শর্তে একাধিক কলাম জড়িত থাকে তখন কার্যকর

উদাহরণ:

CREATE INDEX idx_department_sale ON sales(department, sale DESC);

3. ব্যাচ প্রক্রিয়াকরণ ব্যবহার করুন

একসাথে বড় ডেটাসেট প্রক্রিয়াকরণ করার পরিবর্তে, ব্যাচে ডেটা প্রক্রিয়াকরণ করে লোড কমানো যায়।

উদাহরণ:

SELECT * FROM sales WHERE department = 'Sales Department' LIMIT 1000 OFFSET 0;
SELECT * FROM sales WHERE department = 'Sales Department' LIMIT 1000 OFFSET 1000;

6-3. ডেটা সামঞ্জস্য বজায় রাখা

1. আপডেট এবং পুনরায় গণনা

সারি যুক্ত বা মুছে ফেলা হলে নম্বরিং পরিবর্তিত হতে পারে। প্রয়োজন অনুযায়ী নম্বর পুনরায় গণনা করার একটি মেকানিজম তৈরি করুন।

উদাহরণ:

CREATE VIEW ranked_sales AS
SELECT
    employee,
    sale,
    ROW_NUMBER() OVER (PARTITION BY department ORDER BY sale DESC) AS rank
FROM
    sales;

একটি ভিউ ব্যবহার করলে সর্বশেষ ডেটার ভিত্তিতে র‍্যাঙ্কিং আপডেটেড রাখা সহজ হয়।

6-4. সেরা-অনুশীলন কুয়েরি উদাহরণ

নিচে পারফরম্যান্স এবং রক্ষণাবেক্ষণযোগ্যতা বিবেচনা করে সেরা অনুশীলনের একটি উদাহরণ দেওয়া হয়েছে।

উদাহরণ: শীর্ষ N সারি বের করা

WITH RankedSales AS (
    SELECT
        employee,
        department,
        sale,
        ROW_NUMBER() OVER (PARTITION BY department ORDER BY sale DESC) AS rank
    FROM
        sales
)
SELECT *
FROM RankedSales
WHERE rank <= 3;

এই গঠনটি রিডেবিলিটি এবং পুনঃব্যবহারযোগ্যতা বাড়াতে কমন টেবিল এক্সপ্রেশন (CTE) ব্যবহার করে।

সারাংশ

ROW_NUMBER() অথবা এর বিকল্পগুলো ব্যবহার করার সময়, এই বিষয়গুলো মাথায় রাখুন:

  1. ইনডেক্স অপ্টিমাইজেশন এর মাধ্যমে গতি বৃদ্ধি করুন।
  2. এক্সিকিউশন প্ল্যান পরীক্ষা করে বটলনেক চিহ্নিত করুন।
  3. ডেটা আপডেট পরিকল্পনা করুন এবং সামঞ্জস্য বজায় রাখুন।
  4. ব্যাচ প্রসেসিং এবং CTE ব্যবহার করে লোড বিতরণ করুন।

এই সেরা অনুশীলনগুলি প্রয়োগ করলে বড়-মাপের ডেটা বিশ্লেষণ ও রিপোর্টিংয়ের জন্য কার্যকর প্রক্রিয়াকরণ সম্ভব হবে।

7. Conclusion

এই প্রবন্ধে, আমরা MySQL-এর ROW_NUMBER() ফাংশনের উপর কেন্দ্রীভূত হয়েছি, মৌলিক ব্যবহার এবং ব্যবহারিক উদাহরণ থেকে পুরনো সংস্করণের বিকল্প, সতর্কতা এবং সেরা অনুশীলন পর্যন্ত সবকিছু ব্যাখ্যা করেছি। এই অংশে, আমরা মূল বিষয়গুলো পুনরায় সংক্ষেপে উপস্থাপন করব এবং ব্যবহারিক টেকঅ্যাওয়ে সমন্বিত করব।

7-1. Why ROW_NUMBER() is useful

ROW_NUMBER() ফাংশনটি ডেটা বিশ্লেষণ ও রিপোর্টিংয়ের জন্য নিম্নলিখিতভাবে বিশেষভাবে সুবিধাজনক:

  1. গ্রুপের মধ্যে ধারাবাহিক নম্বরিং: বিভাগ বা ক্যাটেগরি ভিত্তিক র‍্যাঙ্কিং দিয়ে সহজে বিক্রয় র‍্যাঙ্ক তৈরি করা যায়।
  2. শীর্ষ N সারি বের করা: নির্দিষ্ট শর্তের ভিত্তিতে ডেটা দক্ষতার সাথে ফিল্টার ও বের করা।
  3. ডুপ্লিকেট সনাক্ত ও মুছে ফেলা: ডেটা পরিষ্কার ও সংগঠনের জন্য উপকারী।

এটি জটিল কুয়েরি সহজ করে, ফলে SQL এর পাঠযোগ্যতা ও রক্ষণাবেক্ষণযোগ্যতা উল্লেখযোগ্যভাবে বৃদ্ধি পায়।

7-2. Comparison with other window functions

RANK() এবং DENSE_RANK() এর মতো উইন্ডো ফাংশনের তুলনায়, ROW_NUMBER() ভিন্ন কারণ এটি একই মানের জন্যও একটি অনন্য নম্বর প্রদান করে।

FunctionFeatureUse case
ROW_NUMBER()Assigns a unique sequential number to each rowBest when you need unique identification or ranking with no duplicates
RANK()Same rank for ties; skips the next rank numberWhen you need tie-aware rankings and rank gaps matter
DENSE_RANK()Same rank for ties; does not skip rank numbersWhen you want continuous ranking while handling ties

সঠিক ফাংশন নির্বাচন করা:
আপনার উদ্দেশ্যের জন্য সর্বোত্তম ফাংশন নির্বাচন করলে ডেটা প্রক্রিয়াকরণ কার্যকর হয়।

7-3. Handling older MySQL versions

MySQL 8.0 এর নিচের পরিবেশের জন্য, আমরা ইউজার-ডিফাইন্ড ভেরিয়েবল ব্যবহার করে পদ্ধতি পরিচয় করিয়েছি। তবে, নিম্নলিখিত সতর্কতাগুলি বিবেচনা করা উচিত:

  • বেশি জটিল SQL এর কারণে পাঠযোগ্যতা হ্রাস পায়।
  • কিছু ক্ষেত্রে কুয়েরি অপ্টিমাইজেশন আরও কঠিন হতে পারে।
  • ডেটা সামঞ্জস্য বজায় রাখতে অতিরিক্ত হ্যান্ডলিং প্রয়োজন হতে পারে।

সম্ভব হলে, MySQL 8.0 বা তার পরের সংস্করণে মাইগ্রেট করা এবং উইন্ডো ফাংশন ব্যবহার করা শক্তভাবে সুপারিশ করা হয়।

7-4. Key points for performance optimization

  1. ইনডেক্স ব্যবহার করুন: গতি বাড়াতে ORDER BY তে ব্যবহৃত কলামগুলিতে ইনডেক্স যোগ করুন।
  2. এক্সিকিউশন প্ল্যান পরীক্ষা করুন: EXPLAIN দিয়ে পূর্বে পারফরম্যান্স যাচাই করুন।
  3. ব্যাচ প্রসেসিং গ্রহণ করুন: বড় ডেটাসেটকে ছোট ছোট অংশে ভাগ করে লোড বিতরণ করুন।
  4. ভিউ এবং CTE ব্যবহার করুন: পুনঃব্যবহারযোগ্যতা বাড়িয়ে জটিল কুয়েরি সহজ করুন।

এই কৌশলগুলি প্রয়োগ করে আপনি কার্যকর ও স্থিতিশীল ডেটা প্রক্রিয়াকরণ অর্জন করতে পারেন।

7-5. Final notes

ROW_NUMBER() একটি শক্তিশালী টুল যা ডেটা বিশ্লেষণের দক্ষতা উল্লেখযোগ্যভাবে বাড়াতে পারে।
এই প্রবন্ধে, আমরা মৌলিক সিনট্যাক্স ও ব্যবহারিক উদাহরণ থেকে সতর্কতা ও বিকল্প পর্যন্ত সবকিছু আলোচনা করেছি।

এই প্রবন্ধটি অনুসরণ করে নিজে কুয়েরি চালানোর জন্য আমরা আপনাকে উৎসাহিত করছি। আপনার SQL দক্ষতা উন্নত করলে আপনি আত্মবিশ্বাসের সঙ্গে আরও জটিল ডেটা বিশ্লেষণ ও রিপোর্টিং মোকাবেলা করতে পারবেন।

Appendix: Reference resources

  • Official documentation: MySQL Window Functions
  • Online SQL environment: SQL Fiddle (একটি টুল যা আপনাকে অনলাইনে SQL চালানো ও পরীক্ষা করার সুযোগ দেয়)