آشنایی با CTEs ها (بخش دوم CTE بازگشتی)

همانطور که در مقاله قبل , با یکی از انواع CTEs ها به نام CTE غیر بازگشتی آشنا شدیم و راجع به خصوصیات , مزایا و معایب آنها صحبت کردیم حال در این مقاله قصد داریم با نوع دیگر از آن به نام CTE بازگشتی یا Recursive CTE آشنا شویم و در آخر به بررسی یک سناریو کاربردی بپردازیم

یکی از مزایای استفاده از Recursive CTE پیاده سازی ساختار درختی است . اگر بخواهیم مثالی از ساختار درختی را بیان کنیم میتوان به چارت سازمانی اشاره کرد به عنوان مثال هر سازمانی یک مدیر دارد هر مدیری میتواند با یک سری معاون در ارتباط باشد و هر معاونی با یک یا چند مدیر میانی و هر مدیر میانی با یک سری کارمند و ... در ارتباط باشند. همانطور که مشاهده میکنید با یک ساختار درختی رو به رو هستیم حال اگر بخواهیم همچین ساختارهایی را در SQL Server پیاده سازی کنیم میتوان از CTE های بازگشتی استفاده کنیم

زمانی که ما از Recursive CTE استفاده میکنیم , در بخش Sub Query آن , دیگر یک کوئری نمی نویسیم در اینجا بیش از یک کوئری مینویسیم که این دو کوئری از طریق یک Set Operators که در بیشتر مواقع UNION ALL است با یکدیگر در ارتباط هستند و اینکه این ارتباط به چه شکلی است در ادامه توضیح خواهم داد

همانطور که در تصویر بالا میبینید در CTE بازگشتی یک بخشی به نام Anchor Member و بخش دیگری به نامRecursive Member وجود دارد که این دو بخش از طریق یک Set Operators با هم در ارتباط هستند.

حال برای درک بیشتر این موضوع میخواهیم به بررسی مثالی بپردازیم . فرض کنید قرار است اعداد 1 تا 100 را تولید کنیم برای این کار ما از 2 روش استفاده میکنیم و با یکدیگر مقایسه خواهیم کرد

روش اول با استفاده از CROSS JOIN :

• در ابتدا جدولی به اسم Numbers ایجاد میکنیم
• مقادیر 0 تا 9 را در جدول Numbers درج میکنیم
• طبق فرمول موجود اعداد 1 تا 100 را تولید میکنیم

نتیجه کوئری بالا به صورت زیر میباشد

روش دوم با استفاده از Recursive CTE :

• در ابتدا ما Anchor Member را مشخص و عدد 1 را برای ما تولید میکند
• در قسمت Recursive Member ما میتوانیم اسم CTE را صدا بزنیم . به عبارت دیگر قسمت Anchor با نام CTE مشخص میشود یعنی هرگاه بگوییم CTE انگار همان SELECT 1 را میخواهیم

و نتیجه دستور بالا به صورت زیر میباشد.

حال اگر بخواهیم این دو کوئری را از لحاظ Cost با هم بررسی کنیم نتیجه این قیاس به صورت زیر می باشد

همانطور که مشاهده میکنید کوئری که با استفاده از روش CROSS JOIN نوشته شده است به نسب کوئری که با استفاده از روش Recursive CTE نوشته شده است یک نسبت 100 به 0 میباشد

نکته ای که خیلی باید دقت داشته باشید این است که ماکزیموم فرآیند تکرار به صورت پیشفرض 100 تا است

اما برای رفع این مشکل یک option وجود دارد که ما میتوانیم در انتهای کوئری هایی که از نوع CTE بازگشتی است استفاده کنیم که این option ها عبارت است از :

MAXRECURSION: 0 (Infinite)
MAXRECURSION: 100 (Default)
MAXRECURSION: 32767 (Max value)

حالا سوالی که در اینجا مطرح میشود این است که چه فرقی بین مقدار 0 (نامحدود) با مقدار 32767 که همان (Max value) است وجود دارد؟ اگر ما بخواهیم به صورت عددی وارد کنیم نهایت تا 32767 میتوانیم مشخص کنیم اما اگر ما مقدار نامحدود را انتخاب کردیم باید در قسمت WHERE در Recursive Member کنترل نماییم

سناریو کاربردی :

همانطور که قبلا صحبت کردیم میپردازیم به یک سناریو کاملا کاربردی در خصوص CTE بازگشتی

فرض کنید ما یک جدولی داریم به اسم Employees که مشخصات هر کارمند را در آن ثبت میکنیم

در این جدول فیلدی وجود دارد به نام mgrid که این فیلد رئیس هر کارمند را مشخص میکند

این جدول مطابق چارت زیر طراحی شده است.

در این مثال ما میخواهیم بدانیم که بالا دستی های کارمند شماره 9 چه کسانی هستند.

• اول بخش Anchor Member را مشخص میکنیم (هدف ما بالا دستی های کارمند شماره 9 است )
• دوم بخش Recursive Member را مشخص میکنیم که فرآیند تکرار را برای ما انجام دهد .

خروجی کوئری بالا بصورت زیر میباشد.

همانطور که مشاهده میکنید ما با یک ساختار بازگشتی روبرو هستیم که فرآیند تکرار را برای ما انجام میدهد . و در آخر فرض کنید که ما با استفاده از روش های دیگر میخواستیم چنین سلسله مراتبی را تولید کنیم چه حجمی از کوئری باید نوشته میشد؟ و چه هزینه ای برای ما داشت ؟و چقدر خوانا بودن کوئری را برای ما مشکل ساز میکرد...

موفق باشید .