در مورد توابع Aggregate در SQL چه می‌دانید؟

مسئله

در زبان کوئری T-SQL در پایگاه داده SQL Server می‌توانید از توابع مختلف برای انجام محاسبات روی داده‌های عددی استفاده کنید. این نوع محاسبات، مشابه محاسباتی هستند که در اکسل انجام می‌دهید. در این مقاله قصد داریم به توابع Aggregate بپردازیم. توابع Aggregate چیست و چگونه می‌توانید از آن‌ها استفاده کنید.

راه‌حل

یک تابع Aggregate برخی از محاسبات (مانند مجموع یا میانگین) را روی مجموعه‌ای از مقادیر انجام می‌دهد و یک مقدار واحد را برمی‌گرداند. به عنوان مثال، وقتی چند سلول را در اکسل با مقادیر عددی انتخاب می‌کنیم، توابع Aggregate خاصی، قابل محاسبه هستند:

در این مثال، ما ۸ سلول را در اکسل انتخاب کردیم. می‌توانیم ببینیم که تعداد سلول‌ها ۸ است، که مقدار متوسط آن حدود ۱۱.۴۸ است و مجموع همه مقادیر حدود ۹۱.۵۸ است. ما می‌توانیم همین محاسبات را در SQL Server انجام دهیم. به طور معمول، شما در بالای مقادیر عددی در داخل یک ستون از یک جدول، محاسبه می‌کنید.

توابع Aggregate

محاسبات پایه با توابع SQL Aggregate

نشان می‌دهیم که چگونه می‌توانیم برخی از محاسبات Aggregate را در یک دستور SELECT محاسبه کنیم. کوئری زیر حداقل مقدار (SELECT MIN)، حداکثر مقدار (SELECT MAX)، میانگین (SELECT AVG) و تابع جمع (SELECT SUM) را برای ستون SalesAmount محاسبه می‌کند:

SELECT
MinAmt = MIN([SalesAmount]),
MaxAmt = MAX([SalesAmount]),
AvgAmt = AVG([SalesAmount]),
TotalAmt = SUM([SalesAmount])
FROM [AdventureWorksDW2017].[dbo].[FactInternetSales

یک ردیف در سربرگ Results با یک نتیجه عددی برای هر تابع برگردانده می‌شود. توابع Aggregate مانند این مواردی که مثال زده شد، نیاز دارند که ستون ورودی، عددی باشد مانند SalesAmount اما اگر ستون ورودی دارای نوع داده دیگری باشد، یک خطا برگردانده می‌شود:

تابع Count این محدودیت را ندارد. می‌توانید از این تابع در ستون‌هایی با هر نوع داده (به استثنای تصویر و متن) استفاده کنید. این تابع، تعداد موارد موجود در ستون را می‌شمارد.

SELECT
MinAmt = MIN([SalesAmount]),
MaxAmt = MAX([SalesAmount]),
AvgAmt = AVG([SalesAmount]),
TotalAmt = SUM([SalesAmount]),
Cnt = COUNT([ShipDate])
FROM [AdventureWorksDW2017].[dbo].[FactInternetSales];

تفاوت بین SUM و COUNT در این است که SELECT SUM تمام مقادیر داخل ستون را با هم جمع می‌کند، در حالی که SELECT COUNT فقط تعداد موارد موجود در ستون را می‌شمارد. تفاوت را می‌توان با این کوئری نمونه به وضوح مشاهده کرد:

SELECT
MinAmt = MIN([SalesAmount]),
MaxAmt = MAX([SalesAmount]),
AvgAmt = AVG([SalesAmount]),
TotalAmt = SUM([SalesAmount]),
Cnt = COUNT([ShipDate])
FROM [AdventureWorksDW2017].[dbo].[FactInternetSales];

توابع SUM و COUNT تنها در صورتی نتیجه یکسان را برمی‌گردانند که ستون فقط شامل مقادیر ۱ باشد. با COUNT، می‌توانید به جای تعداد موارد در یک ستون، تعداد ردیف‌های جدول را بشمارید. این را می‌توان با استفاده از SELECT COUNT(*) انجام داد.

SELECT
MinAmt = MIN([SalesAmount]),
MaxAmt = MAX([SalesAmount]),
AvgAmt = AVG([SalesAmount]),
TotalAmt = SUM([SalesAmount]),
Cnt = COUNT([ShipDate]),
TotalCnt = COUNT (*)
FROM [AdventureWorksDW2017].[dbo].[FactInternetSales];

مقادیر NULL

توابع Aggregate، مقادیر NULL را نادیده می‌گیرند، به استثنای تابع COUNT(*)همان طور که می‌بینید، تعداد و مجموع اصلی ثابت می‌ماند، حتی اگر یک رکورد اضافی به داده‌های نمونه اضافه شده باشد. COUNT(*) ردیف با مقدار NULL را نیز می‌شمارد.

عبارات به جای ستون

همچنین می‌توانید از عبارات در داخل توابع Aggregate استفاده کنید. نتیجه عبارت باید عددی باشد، به جز تابع COUNT . برای مثال، سینتکس زیر را ببینید:

WITH cte_sampledata AS
(
SELECT 1 AS mycolumn
UNION ALL
SELECT 2
UNION ALL
SELECT 3
UNION ALL
SELECT 4
UNION ALL
SELECT 5
UNION ALL
SELECT NULL
)
SELECT SumColumn = SUM(mycolumn), CountColumn = COUNT(mycolumn), TotalCount = COUNT(*)
FROM [cte_sampledata];

استفاده از DISTINCT

همچنین می‌توان از distinct در داخل یک تابع Aggregation استفاده کرد. بیایید یک ردیف اضافی با مقدار ۱ به مثال خود اضافه کنیم:

WITH cte_sampledata AS
(
SELECT 1 AS mycolumn
UNION ALL
SELECT 2
UNION ALL
SELECT 3
UNION ALL
SELECT 4
UNION ALL
SELECT 5
UNION ALL
SELECT NULL
UNION ALL
SELECT 1
)
SELECT SumColumn = SUM(mycolumn), CountColumn = COUNT(mycolumn), TotalCount = COUNT(*)
,SumDistinct = SUM(DISTINCT mycolumn), CntDistinct = COUNT(DISTINCT mycolumn)
FROM [cte_sampledata];

نحوه محاسبات بالا:

• SumColumn: مجموع همه مقادیر به جز NULL را در نظر می‌گیرد. ۱۶ =۱ + ۲ + ۳ + ۴ + ۵ + ۱.
• CountColumn: مقادیر غیر NULL ستون را می‌شمارد. این مجموعه {۱،۲،۳،۴،۵،۱} است که دارای ۶ عضو است.
• TotalCount: فقط تعداد ردیف‌ها را می‌شمارد که ۷ است.
• SumDistinct: مجموع تمام مقادیر متمایز را می‌گیرد و NULL در نظر گرفته نمی‌شود.

۱۵ =۱ + ۲ + ۳ + ۴ + ۵

• CntDistinct مقادیر منحصر به فرد غیر NULL ستون را می‌شمارد. این مجموعه {۱،۲،۳،۴،۵} است که دارای ۵ عضو است.

افزودن ستون‌های بیشتر به SELECT

تا به حال ما فقط توابع Aggregation را در دستور SELECT خود داشتیم. اگر ستون‌های دیگر را اضافه کنید، با خطای زیر مواجه خواهید شد:هنگامی که شروع به استفاده از توابع Aggregation کردید، یک ستون باید یا در داخل یک تابع Aggregation باشد یا در یک عبارت GROUP BY. این مورد به موتور پایگاه داده می‌گوید که روی کدام ستون‌ها می‌خواهید تابع Aggregation را محاسبه کنید. مثال ما به صورت زیر خواهد بود:

SELECT OrderYear = YEAR(OrderDate), [SalesAmount] = SUM([SalesAmount])
FROM [AdventureWorksDW2017].[dbo].[FactInternetSales]
GROUP BY YEAR(OrderDate);

این کوئری اکنون به معنای زیر است: مجموع SalesAmount را برای سفارش‌ها در هر سال محاسبه می‌کند.مهم است که در عبارت GROUP BY از همان عبارت جلوی SELECT استفاده کنید. اگر فقط از OrderDate استفاده می‌کردیم، نتیجه زیر را دریافت می‌کردیم:در اینجا SalesAmount را به ازای هر تاریخ سفارش جمع‌آوری کرده‌ایم، اما فقط عدد سال در ستون اول نمایش داده می شود که این منجر به نتایج گیج کننده می‌شود.

محاسبات Subtotal و Grand Totla

فرض کنید داده‌های نمونه زیر را داریم:اگر یک جدول pivot در اکسل ایجاد کنیم، می‌توانیم ببینیم که به طور خودکار SubTotal و Grand Total را برای ما محاسبه می‌کند:آیا می‌توانیم در T-SQL هم همین کار را انجام دهیم؟ خوشبختانه می‌توانیم از گزینه‌های CUBE، ROLLUP یا GROUPING SETS استفاده کنیم. با استفاده از ROLLUP توضیح دهیم:

WITH SampleData AS
(
SELECT [Month] = 'October' , Category = 'A', Amount = 1000 UNION ALL
SELECT [Month] = 'October' , Category = 'A', Amount = 500 UNION ALL
SELECT [Month] = 'November', Category = 'A', Amount = 750 UNION ALL
SELECT [Month] = 'November', Category = 'A', Amount = 1250 UNION ALL
SELECT [Month] = 'October' , Category = 'B', Amount = 450 UNION ALL
SELECT [Month] = 'October' , Category = 'B', Amount = 650 UNION ALL
SELECT [Month] = 'November', Category = 'B', Amount = 800 UNION ALL
SELECT [Month] = 'October' , Category = 'C', Amount = 100 UNION ALL
SELECT [Month] = 'November', Category = 'C', Amount = 350
)
SELECT
[Month]
,Category
,Amount = SUM(Amount)
FROM [SampleData]
GROUP BY ROLLUP([Month],[Category]);

می‌توانیم ببینیم که گزینه ROLLUP ردیف‌های اضافی با مقادیر NULL اضافه کرده است. در ردیف ۴، ماه نوامبر است اما ستون Category این ردیف NULL است. به این معنی است که این ردیف Subtotal ماه نوامبر را نشان می‌دهد. همین امر برای ردیف ۸ برای ماه اکتبر صادق است. ردیف آخر Grand Totlal را نشان می‌دهد، که در آن ستون‌های Month و Category هر دو NULL هستند.

چگونه می‌توانیم به راحتی تشخیص دهیم که کدام ردیف‌ها Subtotal یا Grand Total هستند؟ اگر داده‌های منبع حاوی مقادیر NULL نیز باشند چه می‌شود؟ ما می‌توانیم این کار را با تابع GROUPING انجام دهیم. این تابع اگر ستونی Aggregate شده باشد ۱ و در غیر این صورت صفر را برمی‌گرداند. به دلیل آن که ما فقط می‌توانیم یک ستون را در هر زمان بررسی کنیم، بنابراین باید آن را دو بار در کوئری خود قرار دهیم:

WITH SampleData AS
(
SELECT [Month] = 'October' , Category = 'A', Amount = 1000 UNION ALL
SELECT [Month] = 'October' , Category = 'A', Amount = 500 UNION ALL
SELECT [Month] = 'November', Category = 'A', Amount = 750 UNION ALL
SELECT [Month] = 'November', Category = 'A', Amount = 1250 UNION ALL
SELECT [Month] = 'October' , Category = 'B', Amount = 450 UNION ALL
SELECT [Month] = 'October' , Category = 'B', Amount = 650 UNION ALL
SELECT [Month] = 'November', Category = 'B', Amount = 800 UNION ALL
SELECT [Month] = 'October' , Category = 'C', Amount = 100 UNION ALL
SELECT [Month] = 'November', Category = 'C', Amount = 350
)
SELECT
[Month],
Category,
Amount = SUM(Amount),
SubTotalFlag = GROUPING([Category]),
GrandTotalFlag = GROUPING([Month])
FROM [SampleData]
GROUP BY ROLLUP([Month],[Category]);

تابع GROUPING_ID می‌تواند سطح گروه‌بندی چندین ستون را به طور هم‌ زمان نشان دهد.برای درک عدد صحیح برگشتی، باید به دو flag به عنوان ورودی باینری نگاه کنید. وقتی ستون Category، Aggregate می‌شود، ۱ دریافت می‌شود، اما برگشتی ستون Month صفر است. رقم باینری ۰۱ می‌شود که با ۱ مطابقت دارد. وقتی هر دو ستون Category و Month، Aggregate شوند، هر دو flag برابر ۱ هستند. رقم باینری ۱۱ با عدد ۳ مطابقت دارد.

توابع CUBE و GROUPING_SETS به شما کنترل بیشتری بر روی اینکه کدام subtotal ها محاسبه می‌شوند، خواهد داد، اما توضیح این توابع از اهداف این آموزش SQL خارج هستند. اگر می خواهید در مورد آنها بیشتر بدانید به مقاله مختص آن ها باید مراجعه کنید.

منابع

https://www.mssqltips.com/sqlservertip/7062/sql-aggregate-functions-code-samples/