این مقاله در واقع بخش دوم و ادامه ی مقاله ی Cross tabbing می باشد. در مقاله ی قبلی به روشهایی اشاره کردم که همگی استاندارد بودند. در این مقاله قصد دارم راه حل هایی را معرفی کنم که تنها با Microsoft T-SQL سازگاری دارند.
روش های قابل استفاده در MS SQL Server
Multiple Outer APPLY
استفاده از Subquery در ماده ی SELECT می تونه تاثیر منفی روی عملکرد Query بگذارد. شدت تاثیر بستگی به تعداد سطری دارد که Query بر می گرداند. هر چه تعداد سطرهای برگردانده شده توسط کوئری بیشتر باشد کوئری های بیشتری نیاز به اجرا شدن دارند. به طور مثال اگر کوئری ما 5 سابکوری در ماده ی SELECT داشته باشد و کوئری 1000 سطر برگرداند و به ازای هر سطر بایستی سابکوئری ها اجرا شوند که در این حالت 5 سابکوئری هر کدام 1000 بار اجرا شده که در مجموع 5000 کوئری اجرا می شود که این عدد بسیار بزرگ است.
در اینجا به جای Subquery از عملگر APPLY استفاده می کنم (که البته تاثیر مثبت زیادی ندارد). این روش موثر و کاربردی نیست و تنها برای به کمال رساندن مقاله و معرفی انواع روشها به آن اشاره شده است. query مورد نظر به این شکل می باشد:
SELECT P.product_name
, COALESCE(D1.[1990], 0) AS [1990]
, COALESCE(D2.[1991], 0) AS [1991]
, COALESCE(D3.[1992], 0) AS [1992]
, COALESCE(D4.[1993], 0) AS [1993]
, COALESCE(D5.[1994], 0) AS [1994]
, COALESCE(T.Total, 0) AS Total
FROM Products P
OUTER APPLY (SELECT SUM(qty * product_price) AS [1990]
FROM Sales S
WHERE S.product_name = P.product_name
AND S.sales_year = 1990) D1
OUTER APPLY (SELECT SUM(qty * product_price) AS [1991]
FROM Sales S
WHERE S.product_name = P.product_name
AND S.sales_year = 1991) D2
OUTER APPLY (SELECT SUM(qty * product_price) AS [1992]
FROM Sales S
WHERE S.product_name = P.product_name
AND S.sales_year = 1992) D3
OUTER APPLY (SELECT SUM(qty * product_price) AS [1993]
FROM Sales S
WHERE S.product_name = P.product_name
AND S.sales_year = 1993) D4
OUTER APPLY (SELECT SUM(qty * product_price) AS [1994]
FROM Sales S
WHERE S.product_name = P.product_name
AND S.sales_year = 1994) D5
OUTER APPLY (SELECT SUM(qty * product_price) AS Total
FROM Sales S
WHERE S.product_name = P.product_name) T;
برای اینکه بتوانیم راحت تر Query را توسعه بدهیم و خوانایی آن را افزایش دهیم بهتر است از table-valued UDF به جای Subquery ها استفاده کنیم. البته از نظر عملکرد هیچ تاثیر مثبتی نخواهد داشت. یعنی:
CREATE FUNCTION dbo.Cross_tabbing (@product_name VARCHAR(15), @year INT)
RETURNS TABLE AS
RETURN
(SELECT SUM(qty * product_price) AS total
FROM Sales S
WHERE S.product_name = @product_name
AND S.sales_year = @year);
GO
SELECT P.product_name
, D1.total AS [1990]
, D2.total AS [1991]
, D3.total AS [1992]
, D4.total AS [1993]
, D5.total AS [1994]
, T.Total
FROM Products P
OUTER APPLY dbo.Cross_tabbing(P.product_name, '1990') D1
OUTER APPLY dbo.Cross_tabbing(P.product_name, '1991') D2
OUTER APPLY dbo.Cross_tabbing(P.product_name, '1992') D3
OUTER APPLY dbo.Cross_tabbing(P.product_name, '1993') D4
OUTER APPLY dbo.Cross_tabbing(P.product_name, '1994') D5
OUTER APPLY (SELECT SUM(qty * product_price) AS Total
FROM Sales S
WHERE S.product_name = P.product_name) T;
همانطوری که مشاهده می کنید، این Query نسبت به قبلی بسیار خوانا تر شده و توسعه و گسترش آن افزایش یافته است.
(توابعی که جدول بر میگردانند دارای دو گونه ی متفاوت هستند: Inline و Multistatement. که در اینجا ما از نوع اول استفاده کرده ایم.)
Multiple Outer Joins
در بخش اول این مقاله به روشی اشاره کردم که از چندین اتصال خارجی تشکیل شده بود. در SQL Server همانطوری که نشان دادم می توانیم از توابعی که جدول بر می گردانند به جای جدول، ویو و عبارت های جدولی استفاده کنیم. حد اقل مزیتی که این توابع برای ما می توانند داشته باشند این است که قابلیت دریافت پارامتر به عنوان مقدار ورودی را دارند. این توابع دقیقا مثل View ها توسط Optimizer بهینه می شوند. پس از لحاظ عملکردی مشکلی ایجاد نخواهند کرد. در این Query که در ادامه قرار گرفته است بجای نوشتن چندین عبارت جدولی از یک تابع با مقادیر ورودی دلخواه استفاده می کنیم.
CREATE FUNCTION dbo.CrossTab (@sales_year INTEGER)
RETURNS TABLE
AS
RETURN
(SELECT product_name,
SUM(qty * product_price) AS year
FROM Sales
WHERE sales_year = @sales_year
GROUP BY product_name
);
GO
SELECT P1.product_name,
COALESCE(C1.[year], 0) AS year1,
COALESCE(C2.[year], 0) AS year2,
COALESCE(C3.[year], 0) AS year3,
COALESCE(C4.[year], 0) AS year4,
COALESCE(C5.[year], 0) AS year5,
COALESCE(totals, 0) AS totals
FROM Products P1
LEFT OUTER JOIN dbo.Crosstab(1990) AS C1
ON P1.product_name = C1.product_name
LEFT OUTER JOIN dbo.Crosstab(1991) AS C2
ON P1.product_name = C2.product_name
LEFT OUTER JOIN dbo.Crosstab(1992) AS C3
ON P1.product_name = C3.product_name
LEFT OUTER JOIN dbo.Crosstab(1993) AS C4
ON P1.product_name = C4.product_name
LEFT OUTER JOIN dbo.Crosstab(1994) AS C5
ON P1.product_name = C5.product_name
LEFT OUTER JOIN (SELECT product_name,
SUM(qty * product_price) AS totals
FROM Sales
GROUP BY product_name) totals
ON P1.product_name = totals.product_name;
PIVOT
SQL Server 2005 عملگر PIVOT را معرفی کرد که ویژه ی Pivoting طراحی شده است. این عملگر بسیار گیج کننده و درک آن بسیار دشوار است. من مزیتی در این به غیر از کوتاه شدن کد ها نمی توانم ببینم. و روش CASE را بسیار ساده تر و کاربردی تر می دانم. روش CASE به دلیل اینکه تنها نیاز به یک بار Scan داده های جدول دارد بسیار موثر است. (ولی روش های دیگری که معرفی کردیم شامل SELECT های فراوانی بودند)
SELECT P.product_name,
Pvt.[1990],
Pvt.[1991],
Pvt.[1992],
Pvt.[1993],
Pvt.[1994],
D.totals
FROM (SELECT product_name, --groups
sales_year,
qty * product_price AS total
FROM sales) D
PIVOT (SUM(total) --The aggregate that will apply to the values in the group
FOR sales_year --The source column holding the target column names
IN ([1990], [1991], [1992], [1993], [1994]) --The list of actual target column names
) AS Pvt
RIGHT OUTER JOIN Products P
ON P.product_name = Pvt.product_name
CROSS APPLY (SELECT SUM(qty * product_price) AS totals
FROM Sales
WHERE product_name = P.product_name) D;
مراجع
Plamen Ratchev (SQL Server MVP)