چگونگی ماکزیمم مقدار از چندین ستون در جدول SQL Server بیابیم؟

مسئله

گاهی اوقات لازم است حداکثر یا حداقل مقدار را از ستون‌های مختلف در جدولی با همان نوع داده پیدا کنید. به عنوان مثال ما یک جدول داریم و سه ستون از نوع DATETIME داریم عبارتند از: UpdateByApp1Date، UpdateByApp2Date، UpdateByApp3Date. می‌خواهیم داده‌ها را از جدول بازیابی کنیم و در جدول دیگری بارگذاری کنیم، اما می‌خواهیم ماکزیمم تاریخ را از بین این سه ستون یافته و به عنوان LastUpdateDate در جدول جدید انتخاب کنیم. SQL Server به ما این امکان را می‌دهد که این کار را به روش‌های مختلف حل کنیم. در این مقاله به توضیح این راه حل‌ها و مقایسه عملکرد آن‌ها خواهیم پرداخت.

راه حل ها

بیایید فرض کنیم که یک جدول نمونه با پنج ستون داریم که سه ستون از آن‌ها دارای نوع داده DATETIME هستند. داده‌های این جدول از سه اپلیکیشن مختلف به روز رسانی می‌شود و داده‌های به روز رسانی برای هر اپلیکیشن به ترتیب در ستون‌های UpdateByApp1Date، UpdateByApp2Date، UpdateByApp3Date، ذخیره می‌شوند. در اینجا کدی برای ساخت جدول و افزودن برخی داده‌های نمونه وجود دارد.

IF (OBJECT_ID('tempdb..##TestTable') IS NOT NULL)
DROP TABLE ##TestTable
CREATE TABLE ##TestTable
(
ID INT IDENTITY(1,1) PRIMARY KEY,
Name NVARCHAR(40),
UpdateByApp1Date DATETIME,
UpdateByApp2Date DATETIME,
UpdateByApp3Date DATETIME
)
INSERT INTO ##TestTable(Name, UpdateByApp1Date, UpdateByApp2Date, UpdateByApp3Date )
VALUES('ABC', '2015-08-05','2015-08-04', '2015-08-06'),
('NewCopmany', '2014-07-05','2012-12-09', '2015-08-14'),
('MyCompany', '2015-03-05','2015-01-14', '2015-07-26')

SELECT * FROM ##TestTable

تسک ما یافتن آخرین تاریخ به روز رسانی برای هر ردیف با استفاده از سه تاریخ است. چهار راه حل برای این کار ارائه خواهیم داد.

با نگاه کردن به داده‌های بالا، این نتیجه‌ای است که باید انتظار داشته باشیم:

راه حل اول

راه حل اول به شرح زیر است:

SELECT
ID,
(SELECT MAX(LastUpdateDate)
FROM (VALUES (UpdateByApp1Date),(UpdateByApp2Date),(UpdateByApp3Date)) AS UpdateDate(LastUpdateDate))
AS LastUpdateDate
FROM ##TestTable

راه حل دوم

ما می‌توانیم این کار را با استفاده از UNPIVOT انجام دهیم:

SELECT ID, MAX(UpdateDate) AS LastUpdateDate
FROM ##TestTable
UNPIVOT ( UpdateDate FOR DateVal IN ( UpdateByApp1Date, UpdateByApp2Date, UpdateByApp3Date ) ) AS u
GROUP BY ID, Name

راه حل سوم

این کار را می‌توان با استفاده از UNION انجام داد:

SELECT ID, MAX(UpdateDate) AS LastUpdateDate
FROM
(
SELECT ID, UpdateByApp1Date AS UpdateDate
FROM ##TestTable
UNION
SELECT ID, UpdateByApp2Date AS UpdateDate
FROM ##TestTable
UNION
SELECT ID, UpdateByApp3Date AS UpdateDate
FROM ##TestTable
) ud
GROUP BY ID

راه حل چهارم

برای راه حل چهارم نیز از UNION استفاده می‌کنیم:

SELECT ID,
( SELECT MAX(UpdateDate) AS LastUpdateDate
FROM
( SELECT tt.UpdateByApp1Date AS UpdateDate
UNION
SELECT tt.UpdateByApp2Date
UNION
SELECT tt.UpdateByApp3Date
) ud
) LastUpdateDate
FROM ##TestTable tt

مقایسه کارایی

همان طور که می‌بینیم دو راه حل اول کد فشرده‌تری دارند و بنابراین احتمالاً استفاده از آن برای توسعه‌دهندگان آسان‌تر است، اما در مورد کارایی چه می‌توان گفت؟ بیایید کارایی این راه حل‌ها را با هم مقایسه کنیم.

در زیر یک جدول جدید ایجاد می‌کنیم و داده‌های آزمایشی بیشتری را به جدول خود اضافه می‌کنیم:

TRUNCATE TABLE ##TestTable
DECLARE @DateFrom DATE = '2012-01-01',
@DateTo DATE = '2015-08-14',
@UpdateDate1 DATE,
@UpdateDate2 DATE,
@UpdateDate3 DATE,
@i INT = 1
--Value 1000000 is used for having enough data for testing. Please choose appropriate value for your server to avoid overloading it.
WHILE (@i < 1000000)
BEGIN
SET @UpdateDate1 = DATEADD(DAY, RAND(CHECKSUM(NEWID()))*(1+DATEDIFF(DAY, @DateFrom, @DateTo)), @DateFrom)
SET @UpdateDate2 = DATEADD(DAY, RAND(CHECKSUM(NEWID()))*(1+DATEDIFF(DAY, @DateFrom, @DateTo)), @DateFrom)
SET @UpdateDate3 = DATEADD(DAY, RAND(CHECKSUM(NEWID()))*(1+DATEDIFF(DAY, @DateFrom, @DateTo)), @DateFrom)
INSERT INTO ##TestTable(Name, UpdateByApp1Date, UpdateByApp2Date, UpdateByApp3Date )
VALUES(CAST(NEWID() AS NVARCHAR(36)), @UpdateDate1,@UpdateDate2,@UpdateDate3)
SET @i=@i+1
END

اکنون هر چهار کوئری را با هم در یک پنجره کوئری اجرا می‌کنیم:

--۱
SELECT
ID,
(SELECT MAX(LastUpdateDate)
FROM (VALUES (UpdateByApp1Date),(UpdateByApp2Date),(UpdateByApp3Date)) AS UpdateDate(LastUpdateDate))
AS LastUpdateDate
FROM ##TestTable
--۲
SELECT ID, MAX(UpdateDate) AS LastUpdateDate
FROM ##TestTable
UNPIVOT ( UpdateDate FOR DateVal IN ( UpdateByApp1Date, UpdateByApp2Date, UpdateByApp3Date ) ) AS u
GROUP BY ID, Name
--۳
SELECT ID, MAX(UpdateDate) AS LastUpdateDate
FROM
(
SELECT ID, UpdateByApp1Date AS UpdateDate
FROM ##TestTable
UNION
SELECT ID, UpdateByApp2Date AS UpdateDate
FROM ##TestTable
UNION
SELECT ID, UpdateByApp3Date AS UpdateDate
FROM ##TestTable
) ud
GROUP BY ID
--۴
SELECT ID,
( SELECT MAX(UpdateDate) AS LastUpdateDate
FROM
( SELECT tt.UpdateByApp1Date AS UpdateDate
UNION
SELECT tt.UpdateByApp2Date
UNION
SELECT tt.UpdateByApp3Date
) ud
) LastUpdateDate
FROM ##TestTable tt

در قسمت Actual Execution Plan می‌توان دید که هزینه کوئری نسبت به دسته، برای اولین کوئری ۷ درصد، برای کوئری دوم ۱۱ درصد، برای کوئری سوم ۱۶ درصد و برای کوئری چهارم ۶۶ درصد است. همچنین می‌توانیم ببینیم که هر کوئری از یک طرح اجرایی کاملاً متفاوت استفاده می‌کند.ما می‌توانیم این کوئری‌ها را جداگانه اجرا کنیم و ببینیم که زمان اجرای اولین کوئری کمتر از بقیه است. اگر کوئری‌ها روی سرور شما خیلی سریع اجرا می‌شوند، می‌توانید تعداد ردیف‌های جدول تست خود را افزایش دهید تا مقایسه واضح‌تری از زمان‌های اجرا داشته باشید. بنابراین همان طور که می‌بینیم، اولین کوئری بهینه‌ترین است، همچنین دارای کد فشرده است و انتخاب خوبی برای محاسبه ماکزیمم از میان ستون‌های با یک نوع داده است. این کوئری نه تنها ماکزیمم یا مینیمم را با کمترین زمان اجرا محاسبه می‌کند، می‌توانیم کد را تغییر دهیم تا میانگین، مجموع و غیره را از گروهی از ستون‌های با همان نوع داده پیدا کند.

ما همچنین می‌توانیم این کار را با ایجاد یک تابع که ماکزیمم یا مینیمم را از میان پارامترهای داده شده پیدا می‌کند حل کنیم:

ufnGetMaximumDate (@Date1 datetime, @Date2 datetime, @Date3 datetime)

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

نتیجه‌گیری

در SQL Server می‌توانیم با استفاده از روش‌های مختلف، ماکزیمم یا مینیمم مقدار را از ستون‌های مختلف از یک نوع داده پیدا کنیم. عملکرد و کد فشرده جز مزیت‌ها محسوب می‌شود. همان طور که می‌بینیم اولین راه حل در مقاله ما بهترین کارایی را دارد و همچنین کد نسبتا فشرده‌ای دارد. لطفاً این ارزیابی‌ها و مقایسه‌ها را تخمینی در نظر بگیرید، عملکردی که مشاهده خواهید کرد به ساختار جدول، index های روی ستون‌ها و غیره بستگی دارد.

منابع

https://www.mssqltips.com/sqlservertip/4067/find-max-value-from-multiple-columns-in-a-sql-server-table/