CTE در SQL Server چیست؟ – راهنمای جامع همراه با مثال

برای متخصصان پایگاه داده، هیچ کاری سخت تر و چالش پذیر تر از کار با داده ها و دریافت اطلاعات لازم از آن ها نیست! اما برای آسان تر کردن این روند پیچیده و دشوار، سیستم مدیریت پایگاه داده SQL Server ابزاری به نام CTE را معرفی کرده است تا به راحتی بتوانید این کار ها را مدیریت کرده و امکان کار کردن با داده های پیچیده تر را نیز داشته باشید. این راهنمای جامع برای آشنایی با نحوه کارکرد ابزار CTE در SQL برای شما عزیزان، جمع آوری شده است پس توصیه می کنیم که آن را از دست ندهید!

CTE چیست؟

CTE یا Common Table Expression، مجموعه ای از نتایج موقتی است که در جایی ذخیره نشده و پس از پرس و جو های SQL حذف می شوند. CTE ابزاری محبوب در میان متخصصان پایگاه داده است که در یک دستور SQL ایجاد شده و سبب خوانایی بهتر و آسان تر شدن پرس و جو های پیچیده می شود.

استفاده از CTE در SQL (روش پایه)

نحوه کلی نوشتن CTE به صورت زیر است:

WITH cte_name (column_1, column_2, column_3,...)
AS (cte_query_definition)

SELECT *
FROM cte_name;

این درخواست های اطلاعاتی یا همان Query ها از دو بخش تشکیل شده اند که بخش اول آن ها CTE است.برای تعریف CTE در SQL از موارد زیر باید استفاده کنیم:

• WITH
  عبارات CTE با کلمه With شروع شده و معرفی می شوند.
  
  
• cte_name
  نام عبارت که به عنوان یک شناسه معتبر شناخته می شود، باید با سایر نام های CTE های تعریف شده در عبارت WITH متفاوت باشد اما می تواند هم نام با View و نام جدول باشد.
  
  
• cte_query_definition
  این دستور از عبارتی به نام SELECT ایجاد شده است که نتیجه عملکرد آن، تولید یک CTE است. این دستور قابلیت تعریف CTE دیگری نداشته و حتماً باید دارای شرایط لازم برای ایجاد یک view را داشته باشد. اگر CTE_query_settings بیش از یک مورد باشد، پس به ترکیب query ها با عملگر هایی مانند UNION ALL و INTERSECT داریم.

و به دو صورت زیر می توان از CTE با روش پایه استفاده کرد:

1_ با استفاده از عبارت WITH

سرور SQL، درخواست های اطلاعاتی یا همان Query های فرعی را در WITH اجرا کرده و پس از استخراج داده، آن را در یک رابطه موقتی ذخیره می کند. در نهایت query اصلی برای ارائه نتیجه نهایی از همان  رابطه موقتی استفاده می کند. مثال زیر، لیست محصولات طبق واحد های فروخته شده بازیابی کرده و نشان می دهد:

WITH TopSoldProducts AS
(
SELECT
p.ProductID,
p.Name AS ProductName,
SUM(sod.OrderQty) AS TotalQuantitySold
FROM
Production.Product p
JOIN Sales.SalesOrderDetail sod ON p.ProductID = sod.ProductID
GROUP BY
p.ProductID, p.Name
)

SELECT
productID,
Product Name
TotalQuantitySold
FROM
TopSoldProducts;

نتیجه به این صورت به نمایش در می آید:

2_ با استفاده از چند CTE در یک query

در موقعیت هایی که برای رسیدن به نتیجه نیاز به بیش از یک CTE داریم، مجبور به نوشتن چند query جدا از هم نیستید زیرا SQL قابلیت ترکیب چند query با عملگر هایی که بالاتر به آن ها اشاره کردیم را ایجاد کرده است. موارد لازم برای ایجاد یک query با چند CTE به صورت زیر است:

• مجاز بودن یک query اصلی و کلمه WITH
• جدا سازی CTE با کمک کاما ( نیازی به استفاده از کاما قبل از query اصلی نیست.)

مثال زیر، ادامه کد بالاست اما با CTE های بیشتر:

WITH ProductCategoryCTE AS (
SELECT
ProductCategoryID,
Name AS CategoryName
FROM
Production.ProductCategory
),
ProductSubcategoryCTE AS (
SELECT
ProductSubcategoryID,
Name AS SubcategoryName,
ProductCategoryID
FROM
Production.ProductSubcategory
),
ProductDetailCTE AS (
SELECT
p.ProductID,
p.Name AS ProductName,
p.ProductNumber,
p. Color,
p.ListPrice,
ps.SubcategoryName,
pc.CategoryName
FROM
Production.Product AS p
LEFT JOIN
ProductSubcategoryCTE AS ps ON p.ProductSubcategoryID = ps.ProductSubcategoryID
LEFT JOIN
ProductCategoryCTE AS pc ON ps.ProductCategoryID = pc.ProductCategoryID
)
SELECT
pd.ProductID,
pd.ProductName,
pd.ProductNumber,
pd. Color,
pd.ListPrice,
pd.SubcategoryName,
pd.CategoryName
FROM
ProductDetailCTE AS pd
ORDER BY
pd.ProductID;

نتیجه به این صورت به نمایش در می آید:

 استفاده از CTE در SQL (روش پیشرفته)

اگر بخواهیم نگاه جزئی تری بیانداریم، دو نوع CTE داریم که به صورت زیر عمل می کنند:

1_ CTE بازگشتی

CTE های بازگشتی، نتایج برگردانده شده توسط WITH را به صورت مکرر ارجاع می دهند تا به نتیجه مورد نظر برسد. Syntax کلی CTE بازگشتی به صورت زیر است:

WITH RECURSIVE cte_name AS (
cte_query_definition (the anchor member)
UNION ALL
cte_query_definition (the recursive member)
)
SELECT *
FROM cte_name;

تفاوت های ساختار CTE های بازگشتی با غیر بازگشتی شامل موارد زیر است:

• استفاده از WITH RECURSIVE
• دارای عضو لنگر (query اول)
• اتصال عضو لنگر به عضو بازگشتی با عملگر های UNION یا UNION ALL

مثال زیر، نحوه استفاده از CTE بازگشتی را نشان می دهد که نشان دهنده سلسله مراتبی از مناطق فروش است که شامل مواردی مانند نام ها، مناطق و غیره است:

WITH RecursiveCTE AS (
SELECT
TerritoryID,
name
CountryRegionCode,
[Group],
1 AS Level
FROM Sales.SalesTerritory
WHERE TerritoryID = 1 -- Starting TerritoryID
UNION ALL
SELECT
T.TerritoryID,
T. Name,
T.CountryRegionCode,
T. [Group],
CTE.Level + 1 AS Level
FROM Sales.SalesTerritory T
JOIN RecursiveCTE CTE ON T.TerritoryID = CTE.TerritoryID
WHERE CTE.Level < 100
)
SELECT * FROM RecursiveCTE;

نتیجه به این صورت به نمایش در می آید:

2_ CTE تودرتو

CTE های تودرتو به این شکل هستند که می توانید از تعریف یک CTE در داخل تعریف یک CTE دیگر استفاده کنید. رایج ترین نوع استفاده از CTE ها تودرتو، محسابه میانگین و یا مقایسه های گروهی است. ساختار کلی یک CTE تودرتو به شکل زیر است:

WITH cte_expression_1 as (
cte_query_1
),
cte_expression_2 as (
cte_query_2 -- (in the FROM clause refers to cte_expression_1)
)
SELECT FROM cte_expression_2;

در مثال زیر، با استفاده از query های تودرتو گزارش هایی از اطلاعات فروش کل ارائه می شود:

WITH ProductSalesByCategory AS (
SELECT
pc.Name AS Category,
psc.Name AS Subcategory,
p.ProductID,
p.Name AS ProductName,
SUM(sod.LineTotal) AS TotalSales
FROM
Sales.SalesOrderDetail sod
INNER JOIN
Production.Product p ON sod.ProductID = p.ProductID
INNER JOIN
Production.ProductSubcategory psc ON p.ProductSubcategoryID = psc.ProductSubcategoryID
INNER JOIN
Production.ProductCategory pc ON psc.ProductCategoryID = pc.ProductCategoryID
GROUP BY
pc.Name, psc.Name, p.ProductID, p.Name
),
CategoryTotalSales AS (
SELECT
Categories
SUM(TotalSales) AS CategoryTotal
FROM
ProductSalesByCategory
GROUP BY
Category
)
SELECT
c.Category,
c.CategoryTotal,
p.ProductName,
p.TotalSales
FROM
ProductSalesByCategory p
JOIN
CategoryTotalSales c ON p.Category = c.Category
ORDER BY
c.Category, p.ProductName;

نتیجه به این صورت به نمایش در می آید:

CTE و سایر ویژگی های SQL

در ادامه قرار است، سایر ویژگی های SQL و شباهت های آن ها با CTE را بررسی بررسی کنیم که به صورت زیر است:

1_ CTE و جدول موقت

جدول های موقت، با پشتیبانی از شاخص ها در طول جلسه در دسترس هستند اما CTE ها فقط در بعضی query ها موجود هستند. جدول های موقت کارشان مدیریت داده های بزرگ است، اما CTE ها وظیفه دیگری دارند که در بالا به آن اشاره شده است.

2_ CTE و query فرعی

CTE ها با قرار گرفتن در ابتدای query ها، نیاز به نامگذاری دارن اما query های فرعی نیازی به نامگذاری ندارند. از CTE ها می توان بار ها استفاده کرد اما query ها فقط یک بار استفاده می شوند. همچنین، query های فرعی قابلیت سازگاری با عبارت های WHERE و IN و EXISTS را برخلاف CTE ها دارند.

سخن آخر

در آخر می توانیم بگوییم که CTE ها به عنوان ابزاری قدرتمند در زمینه ساده سازی و بهبود کارایی کد های SQL می شود. این ابزار با کمک به توسعه دهندگان و متخصصان پایگاه داده برای خوانایی بهتر کد ها و نگهداری از داده های آن ها، موجب فراهم کردن تجربه بهتر کاربری می شود. امدواریم که با مطالعه این راهنما به جواب تمامی سوالات خود در مورد CTE رسیده باشید. اگر سوال دیگری در این حوزه دارید، در قسمت دیدگاه با ما به اشتراک بگذارید.