خانه SQL Server در مورد توابع Aggregate در SQL چه میدانید؟ SQL Server دستورات SQL نوشته شده توسط: تیم فنی نیک آموز تاریخ انتشار: ۲۵ بهمن ۱۴۰۰ آخرین بروزرسانی: 24 آبان 1402 زمان مطالعه: 10 دقیقه ۵ (۱) مسئله در زبان کوئری 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/ برای بدست آوردن اطلاعات بیشتر در مورد دیگر دستورات SQL ، به مقاله زیر مراجعه کنید. چه رتبه ای میدهید؟ میانگین ۵ / ۵. از مجموع ۱ اولین نفر باش دانلود مقاله در مورد توابع Aggregate در SQL چه میدانید؟ فرمت PDF 11 صفحه حجم 1 مگابایت دانلود مقاله معرفی نویسنده مقالات 401 مقاله توسط این نویسنده محصولات 0 دوره توسط این نویسنده تیم فنی نیک آموز معرفی محصول ایمان باقری دوره آموزشی کوئری نویسی پیشرفته 1.450.000 تومان مقالات مرتبط ۰۲ آبان SQL Server ابزار Database Engine Tuning Advisor؛ مزایا، کاربردها و روش استفاده تیم فنی نیک آموز ۱۵ مهر SQL Server معرفی Performance Monitor ابزار مانیتورینگ SQL Server تیم فنی نیک آموز ۱۱ مهر SQL Server راهنمای جامع مانیتورینگ بکاپ ها در SQL Server تیم فنی نیک آموز ۰۸ مهر SQL Server Resource Governor چیست؟ آشنایی با نحوه پیکربندی و اهمیت های آن تیم فنی نیک آموز دیدگاه کاربران لغو پاسخ دیدگاه نام و نام خانوادگی ایمیل ذخیره نام، ایمیل و وبسایت من در مرورگر برای زمانی که دوباره دیدگاهی مینویسم. موبایل برای اطلاع از پاسخ لطفاً مرا با خبر کن ثبت دیدگاه Δ