Database Tutorials Recursive Queries এবং CTE (Common Table Expressions) গাইড ও নোট

Recursive Queries এবং CTE (Common Table Expressions) SQL-এ জটিল ডেটাবেস কুয়েরি লেখার জন্য ব্যবহৃত শক্তিশালী কৌশল। এগুলি বিশেষভাবে প্রয়োজন হয় যখন ডেটাবেসে হায়ারারকিক্যাল (Hierarchical) অথবা গ্রাফ-ভিত্তিক (Graph-based) ডেটা থাকে এবং সেই ডেটার মধ্যে সম্পর্ক অনুসন্ধান করতে হয়। এখানে Recursive Queries হল এমন কুয়েরি, যা নিজের মধ্যে পুনরাবৃত্তি হতে পারে, এবং CTE হল একটি নামকৃত কুয়েরি, যা পরে মূল কুয়েরিতে পুনরায় ব্যবহার করা যায়।

1. Common Table Expressions (CTE)

CTE হল একটি নামকৃত কুয়েরি ব্লক যা SQL স্টেটমেন্টের মধ্যে ব্যবহার করা হয়। CTE ব্যবহার করার মাধ্যমে একটি সাব-কুয়েরি বা মাঝারি স্টেপ তৈরি করা হয়, যা পরে পুরো কুয়েরি চলাকালে পুনরায় ব্যবহার করা যায়। CTE সাধারণত WITH কিওয়ার্ড দিয়ে শুরু হয় এবং পরে মূল কুয়েরি বা অন্যান্য কুয়েরিতে এটি রেফারেন্স করা যায়।

CTE এর সুবিধা:

• পুনঃব্যবহারযোগ্য: একাধিকবার কুয়েরির মধ্যে CTE ব্যবহার করা যেতে পারে।
• পাঠযোগ্যতা: বড় বা জটিল কুয়েরিগুলিকে ছোট ছোট অংশে বিভক্ত করে কোডের পাঠযোগ্যতা বাড়ানো যায়।
• ডিবাগিং সহজ করা: CTE এর মধ্যে ইন্টারমিডিয়েট ফলাফল দেখা যায়, তাই ত্রুটি চিহ্নিত করা সহজ হয়।

CTE সিনট্যাক্স:

WITH cte_name AS (
    SELECT column1, column2
    FROM table_name
    WHERE condition
)
SELECT column1, column2
FROM cte_name;

উদাহরণ:

WITH department_sales AS (
    SELECT department_id, SUM(sales) AS total_sales
    FROM employees
    GROUP BY department_id
)
SELECT department_id, total_sales
FROM department_sales
WHERE total_sales > 100000;

এখানে, WITH কিওয়ার্ড ব্যবহার করে একটি CTE তৈরি করা হয়েছে, যা employees টেবিল থেকে department_id এবং total_sales বের করে, এবং পরে সেই CTE ব্যবহার করে যে বিভাগগুলির মোট বিক্রি ১০০,০০০ এর বেশি, তা দেখানো হয়েছে।

2. Recursive Queries

Recursive Queries হল এমন ধরনের কুয়েরি, যা নিজেই নিজে পুনরাবৃত্তি হতে পারে। একটি recursive CTE এমন কুয়েরি তৈরি করে, যা প্রথমে একটি বেস কেস (base case) রান করে এবং পরে পুনরায় নিজের ওপর কাজ করে, একটি নির্দিষ্ট শর্ত পর্যন্ত। এটি সাধারণত হায়ারারকিক্যাল ডেটা বা গ্রাফ ডেটা এর ক্ষেত্রে ব্যবহৃত হয়, যেমন একে অন্যের সাথে সম্পর্কযুক্ত টেবিল।

Recursive CTE এর স্ট্রাকচার:

1. Anchor Member (Base Case): এটি সাধারণত মূল ডেটার প্রথম অংশ, যেটি রিকার্সিভ কুয়েরির প্রথম অংশ।
2. Recursive Member: এটি সেই অংশ যেটি পুনরাবৃত্তি করে এবং anchor member এর সাথে একত্রিত হয়।
3. Termination Condition: এটি সেই শর্ত যা নিশ্চিত করে যে রিকার্সিভ কুয়েরি কতবার পুনরাবৃত্তি হবে।

Recursive CTE সিনট্যাক্স:

WITH RECURSIVE cte_name AS (
    -- Anchor member (Base case)
    SELECT column1, column2
    FROM table_name
    WHERE condition

    UNION ALL

    -- Recursive member
    SELECT t.column1, t.column2
    FROM table_name t
    JOIN cte_name c ON t.column1 = c.column2
    WHERE condition
)
SELECT * FROM cte_name;

উদাহরণ:

ধরা যাক, আমাদের একটি employees টেবিল রয়েছে, যেখানে employee_id, manager_id এবং name রয়েছে, এবং আমরা একটি কর্মচারীর অধীনকারীদের (subordinates) লিস্ট বের করতে চাই।

employees টেবিলের উদাহরণ:

WITH RECURSIVE employee_hierarchy AS (
    -- Base case: Select the top manager (John)
    SELECT employee_id, name, manager_id
    FROM employees
    WHERE manager_id IS NULL

    UNION ALL

    -- Recursive case: Select employees reporting to a manager
    SELECT e.employee_id, e.name, e.manager_id
    FROM employees e
    JOIN employee_hierarchy eh ON e.manager_id = eh.employee_id
)
SELECT * FROM employee_hierarchy;

ফলাফল:

এখানে employee_hierarchy নামক একটি recursive CTE ব্যবহার করা হয়েছে, যা প্রথমে John কে বেস কেস হিসেবে নেয় এবং তারপর UNION ALL ব্যবহার করে তার অধীনে থাকা কর্মচারীদের (subordinates) বের করে।

Recursive Queries এবং CTE এর সুবিধা:

1. হায়ারারকিক্যাল ডেটা ম্যানেজমেন্ট: যেমন পারিবারিক গাছ (family tree), কর্মচারীদের হায়ারারকি, পণ্য ক্যাটালগ, যেখানে সম্পর্কের স্তর থাকে, সেখানে CTE এবং recursive queries অত্যন্ত কার্যকর।
2. পাঠযোগ্যতা ও কোড সিমপ্লিসিটি: CTE ব্যবহার করে বড় কুয়েরি গুলি ছোট অংশে ভাগ করা যায়, যা কোড পড়তে এবং বুঝতে সহজ করে তোলে।
3. পারফরম্যান্স: অনেক সময় recursive queries ব্যবহার করা হলে কোডের পারফরম্যান্স উন্নত হয়, কারণ বার বার JOIN এর পরিবর্তে একবারে সমগ্র ডেটার সম্পর্ক বের করা হয়।
4. ডেটাবেস অপটিমাইজেশন: CTE ও recursive queries ডেটাবেসের অপটিমাইজেশনের জন্য ব্যবহার করা হয়, কারণ এগুলি জটিল কুয়েরি লিখতে সহায়তা করে।

সারাংশ

• CTE (Common Table Expression) হল একটি নামকৃত কুয়েরি যা মূল কুয়েরিতে পুনরায় ব্যবহার করা যায়, এবং এটি কোডের পাঠযোগ্যতা এবং পুনঃব্যবহারযোগ্যতা নিশ্চিত করে।
• Recursive Queries হল এমন কুয়েরি যা নিজে নিজে পুনরাবৃত্তি হতে পারে, এবং এটি সাধারণত হায়ারারকিক্যাল বা গ্রাফ-ভিত্তিক ডেটা ম্যানিপুলেশন করার জন্য ব্যবহৃত হয়।

এগুলি SQL এর শক্তিশালী কৌশল যা ডেটা অনুসন্ধানে এবং বিশ্লেষণে সহায়তা করে।