بررسی مفهوم Table Expression - بخش اول

در ادامـه ی مفهوم Subquery، با بخش دیگری از این مفهوم یعنی Table Expressionها آشنا خواهید شد. در اینجا دو نمونه از Table Expressionها، تحت عنوان Derived Table و CTE را معرفی می کنیم.

یک نمونه دیگر از Subquery براساس نتایج، Multi-Valued یا Table Expressionها هستند که بر اساس ساختار جدولی می باشند. در حقیقت Table Expressionها ما را به سمت رویکرد ماژولار می برد. یک سیستم بزرگ را به چند زیر سیستم کوچک تبدیل می کنیم و بر اساس آنها کار خود را انجام می دهیم. در نتیجه پیچیدگی کار کمتر می شود و در صورت داشتن تغییرات، فقط همان زیر سیستم رو تغییر می دهیم.

انواع Table Expressionها به VIEW ،CTE (Common Table Expression) ،Derived Table و Inline TVF (Inline Table value Function) تقسیم می شود.

در Table Expressionها از ORDER BY نمی توانیم استفاده کنیم، ستون های ما باید دارای نام باشند و همچنین نام ستون ها باید منحصر به فرد باشند.

نوع Derived Table

به آنها Table Subquery هم گفته می شود و در FROM کوئری بیرونی تعریف می شود. عمر آن نیز محدود به همان FROM می باشد و بعد از آن قابل استفاده نمی باشد.

در این کوئری، شرکت‌ هایی که بیش از 10 سفارش داشته‌ اند را نمایش می دهیم.

SELECT TMP.CompanyName, Tmp.Num FROM
(SELECT
	C.CompanyName,
	(SELECT COUNT(O.OrderID) FROM dbo.Orders AS O
		WHERE C.CustomerID = o.CustomerID) AS Num
FROM dbo.Customers AS C) AS Tmp
	WHERE TMP.Num > 10;
GO

در FROM کوئری بیرونی، Subquery خود را نوشتیم و از نتایج آن در بخش Where استفاده کردیم.

در یک کوئری دیگر، مشتری هایی را نمایش خواهیم داد که در هر فاکتور بیش از 5 مورد کالا سفارش داده اند.

SELECT DISTINCT * FROM
(SELECT
	O.CustomerID,
	(SELECT COUNT(OD.OrderID) FROM dbo.OrderDetails AS OD
		WHERE OD.OrderID = O.OrderID) AS NUM
FROM dbo.Orders AS O) AS TMP
	WHERE TMP.NUM > 5;
GO

نوع CTE (Common Table Expression)

این نوع از Table Expression، جایگزینی برای Derived Table می باشد. در Derived Table قابلیت استفاده مجدد نداشتیم اما CTE این امکان را به ما می دهد. به دو صورت بازگشتی و غیر بازگشتی قابل پیاده سازی می باشد.

ساختار CTE غیر بازگشتی به این صورت می باشد.

WITH <CTE_Name> [(<Column_List>)]
AS
(
	<Inner_Query_Defining_CTE>
)
<Outer_Query_Against_CTE>;

در این کوئری، فهرست کد و نام شرکت مشتری های تهرانی را با استفاده از CTE غیر بازگشتی نمایش می دهیم.

WITH Tehran_Cust
AS
(
	SELECT
		C.CustomerID, C.CompanyName
	FROM dbo.Customers AS C
		WHERE C.City = N'تهران'
)
SELECT * FROM Tehran_Cust;
GO

اگر بخواهیم نام ستون ها را تعیین کنیم، طبق ساختار باید مقابل نام CTE، نام ستون ها را هم در نظر بگیریم.

WITH Tehran_Customers (Col1,Col2) 
AS
(
	SELECT
		CustomerID , CompanyName
	FROM Customers AS C
		WHERE C.City = N'تهران'
)
SELECT
	T.Col1,T.Col2
FROM Tehran_Customers AS T;
GO

ساختار CTE تو در تو به این صورت می باشد.

WITH <CTE_Name1> [(<column_list>)]
AS
(
	<inner_query_defining_CTE>
),
	<CTE_Name2> [(<column_list>)]
AS
(
	<inner_query_defining_CTE>
)
	<outer_query_against_CTE>;

هر CTE، به CTEهای قبل از خودش دسترسی دارد.

در این کوئری، فهرست تعداد مشتریان هر سال و سال قبل از آن را نمایش می دهیم و میزان افزایش یا کاهش تعداد مشتری نسبت به سال قبل را محاسبه می کنیم.

WITH Current_Year
AS
(
	SELECT
		YEAR(OrderDate) AS OrderYear,
		COUNT(DISTINCT CustomerID) AS Cust_Num
	FROM dbo.Orders AS O
	GROUP BY YEAR(OrderDate)
),
Previous_Year
AS
(
	SELECT
		YEAR(OrderDate) AS OrderYear,
		COUNT(DISTINCT CustomerID) AS Cust_Num
	FROM dbo.Orders AS O
	GROUP BY YEAR(OrderDate)
)
SELECT
	Current_Year.OrderYear,
	Current_Year.Cust_Num,
	Previous_Year.OrderYear AS Previous_Cust_Num,
	Current_Year.Cust_Num - Previous_Year.Cust_Num AS Growth
FROM Current_Year
LEFT JOIN Previous_Year
	ON Current_Year.OrderYear = Previous_Year.OrderYear + 1;
GO

نکته: پس از تعریف چندین CTE به‌ صورت تو در تو، استفاده از آن‌‌ ها در چندین دستور جداگانه در کوئری بیرونی CTE امکان‌ پذیر نیست. این موضوع در مورد CTEهای غیر تو در تو هم برقرار است‌.

ساختار CTE بازگشتی به این صورت می باشد.

CTE Recutsive:
WITH <CTE_Name> [(<Column_List>)]
AS
(
	<Anchor_Member>
	UNION ALL
	<Recursive_Member>
)
<Outer_Query_Against_CTE>;

قسمت CTE، شامل دو بخش Anchor و Recursive می باشد که توسط یک set operator عملیات مربوط به آن set operator را انجام می دهیم. Anchor_Member جایی است که ما می خواهیم از آنجا کار خود را شروع کنیم.

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

در این کوئری، با استفاده از CTE بازگشتی اعداد یک تا هزار را نمایش خواهیم داد.

WITH CTE 
AS
(
	SELECT 1 AS Num
		UNION ALL
	SELECT Num + 1 AS X FROM CTE
		WHERE Num < 1000
)
SELECT * FROM CTE
OPTION(MAXRECURSION 1000);
GO

در یک ساختار CTE بازگشتی به طور پیش فرض یک حلقه تا صد بار می تواند تکرار شود. با استفاده از OPTION(MAXRECURSION 1000)، عدد مورد نظر خودم را مشخص کردیم. اگر مقدار آن را صفر بدهیم بی نهایت می شود و بیشترین عددی که می تواند بگیرد 32767 می باشد.

فرض کنید یک جدول به نام Employees داریم که ساختار کارمندان ما را نمایش می دهد. هر کارمند علاوه بر EmployeeID، یک Mgrid دارد که مقادیرش را از EmployeeID می گیرد و این جدول با خودش یک relation درختی دارد.

در این کوئری، زیر مجموعه های کارمند شماره 2 را با استفاده از CTE Recursive، نمایش می دهیم.

WITH Employees_CTE AS
(
SELECT
	EmployeeID, Mgrid, Firstname, Lastname
FROM dbo.Employees
	WHERE EmployeeID = 2
UNION ALL
SELECT
	E.EmployeeID, E.Mgrid, E.Firstname, E.Lastname
FROM Employees_CTE AS Emp_CTE
JOIN dbo.Employees AS E
	ON E.mgrid = Emp_CTE.EmployeeID
)
SELECT
	EmployeeID, Mgrid, Firstname, Lastname
FROM Employees_CTE;
GO 

ابتدا بخش Anchor برای کارمند شماره 2 اجرا می شود و نتایج آن در یک جدول موقت ذخیره می شود. سپس بخش Recursive اجرا خواهد شد. همانطور که می بینید در این مرحله ارتباطی میان CTE ایجاد شده از بخش Anchor با جدول Employees برقرار شده است. مجددا بخش Recursive برای کارمند بعدی انجام می شود و این فرآیند برای تمام کارمندان اجرا خواهد شد.

پایان