استخدام الدالات التجميعية

مكتمل

يوفر T-SQL دوال مجمعة مثل SUM وMAX وAVG لإجراء عمليات حسابية تأخذ قيمًا متعددة وإرجاع نتيجة واحدة.

التعامل مع الدوال المجمعة

تعمل معظم الاستعلامات التي نظرنا إليها على صف واحد في وقت واحد، باستخدام جملة WHERE لتصفية الصفوف. يتوافق كل صف يتم إرجاعه مع صف واحد في مجموعة البيانات الأصلية.

يتم توفير العديد من الدوال المجمعة في SQL Server. في هذا القسم، سنلقي نظرة على الدوال الأكثر شيوعًا مثل SUM وMIN وMAX وAVG وCOUNT.

عند العمل مع دوال مجمعة، يجب مراعاة النقاط التالية:

• ترجع الدوال التجميعية قيمة واحدة (عددية) ويمكن استخدامها في عبارات SELECT في أي مكان تقريبًا يمكن استخدام قيمة واحدة. على سبيل المثال، يمكن استخدام هذه الدوال في عبارات SELECT وHAVING وORDER BY. ومع ذلك، لا يمكن استخدامها في جملة WHERE.
• تتجاهل الدوال التجميعية القيم الخالية، إلا في حالة استخدام COUNT (*).
• لا تحتوي الدوال التجميعية في قائمة SELECT على رأس عمود ما لم توفر اسمًا مستعارًا باستخدام AS.
• تعمل الدوال المجمعة في قائمة SELECT على جميع الصفوف التي تم تمريرها إلى عملية SELECT. إذا لم يكن هناك عبارة GROUP BY، فسيتم تلخيص جميع الصفوف التي تفي بأي عامل تصفية في جملة WHERE. ستتعلم المزيد عن GROUP BY في الموضوع التالي.
• ما لم تكن تستخدم GROUP BY، فلا يجب عليك دمج الدوال التجميعية مع الأعمدة غير المدمجة في الدوال في نفس قائمة SELECT.

لتوسيع نطاق الدوال المدمجة، يوفر SQL Server آلية للدوال التجميعية التي يحددها المستخدم عبر .NET Common Language Runtime (CLR). هذا الموضوع خارج نطاق هذه الوحدة النمطية.

دوال تجميعية مدمجة

كما ذكرنا، يوفر Transact-SQL العديد من الدوال المجمعة المدمجة. وتشمل الدوال الشائعة الاستخدام ما يلي:

اسم الدالة

إعراب

الوصف

SUM

SUM (expression)

لتجميع كافة القيم الرقمية غير الفارغة في عمود.

AVG

AVG (expression)

حساب متوسط جميع القيم الرقمية غير الخالية في عمود (مجموع / عدد).

MIN

MIN (expression)

تُرجع أصغر رقم أو أقرب تاريخ / وقت أو سلسلة متواجدة لأول مرة (وفقًا لقواعد فرز الترتيب).

MAX

MAX (expression)

تُرجع أكبر رقم أو آخر تاريخ / وقت أو السلسلة الأخيرة (وفقًا لقواعد فرز الترتيب).

العد أو COUNT_BIG

COUNT (*) أو COUNT (تعبير)

باستخدام (*)، تحسب كل الصفوف، بما في ذلك الصفوف ذات القيم الخالية. عند تحديد عمود كتعبير، ترجع عدد الصفوف غير الخالية لهذا العمود. ترجع COUNT عددًا صحيحًا؛ ترجع COUNT_BIG big_int.

لاستخدام تجميع مضمن في جملة SELECT، خذ بعين الاعتبار المثال التالي في قاعدة بيانات نموذج MyStore:

SELECT AVG(ListPrice) AS AveragePrice,
       MIN(ListPrice) AS MinimumPrice,
       MAX(ListPrice) AS MaximumPrice
FROM Production.Product;

تبدو نتائج هذا الاستعلام على هذا النحو:

AveragePrice

MinimumPrice

MaximumPrice

744.5952

2.2900

3578.2700

لاحظ أن المثال أعلاه يلخص جميع الصفوف من جدول Production.Product. يمكننا بسهولة تعديل الاستعلام لإرجاع متوسط وأدنى وأقصى أسعار للمنتجات في فئة معينة عن طريق إضافة عبارة WHERE، مثل هذا:

SELECT AVG(ListPrice) AS AveragePrice,
       MIN(ListPrice) AS MinimumPrice,
       MAX(ListPrice) AS MaximumPrice
FROM Production.Product
WHERE ProductCategoryID = 15;

عند استخدام التجميعات في عبارة SELECT، يجب استخدام جميع الأعمدة المشار إليها في قائمة SELECT كمدخلات لدالة تجميع أو الإشارة إليها في عبارة GROUP BY.

خذ بعين الاعتبار الاستعلام التالي الذي يحاول تضمين الحقل ProductCategoryID في النتائج المجمعة:

SELECT ProductCategoryID, AVG(ListPrice) AS AveragePrice,
MIN(ListPrice) AS MinimumPrice,
MAX(ListPrice) AS MaximumPrice
FROM Production.Product;

يؤدي تشغيل هذا الاستعلام إلى الخطأ التالي

رسالة 8120 ، المستوى 16 ، الحالة 1 ، السطر 1

العمود «Production.ProductCategoryID» غير صالح في قائمة التحديد لأنه غير مضمن في أي دالة تجميع أو في عبارة GROUP BY.

يعامل الاستعلام كافة الصفوف باعتبارها مجموعة مجمعة واحدة. لذلك، يجب استخدام كافة الأعمدة باعتبارها مدخلات لتجميع الدوال.

في الأمثلة السابقة، قمنا بتجميع البيانات الرقمية مثل السعر والكميات في المثال السابق. يمكن أيضًا استخدام بعض الدوال المجمعة لتلخيص بيانات التاريخ والوقت والحرف. توضح الأمثلة التالية استخدام التجميعات ذات التواريخ والأحرف:

يعرض هذا الاستعلام الشركة الأولى والأخيرة بالاسم، باستخدام MIN وMAX:

SELECT MIN(CompanyName) AS MinCustomer, 
       MAX(CompanyName) AS MaxCustomer
FROM SalesLT.Customer;

سيعيد هذا الاستعلام القيم الأولى والأخيرة لـ CompanyName في تسلسل ترتيب النسخ لقاعدة البيانات، والذي يكون في هذه الحالة ترتيبًا أبجديًا:

MinCustomer

MaxCustomer

متجر للدراجات

شركة دراجات صفراء

قد تتداخل دوال أخرى مع دوال تجميعية.

على سبيل المثال، يتم استخدام الدالة العددية YEAR في المثال التالي لإرجاع جزء السنة فقط من تاريخ الطلب، قبل أن يتم تقييم الدوال MIN وMAX:

SELECT MIN(YEAR(OrderDate)) AS Earliest,
       MAX(YEAR(OrderDate)) AS Latest
FROM Sales.SalesOrderHeader;

الأقدم

الأحدث

2008

2021

يمكن أيضًا استخدام الدالتين MIN وMAX مع بيانات التاريخ، لإرجاع أقدم وأحدث القيم الزمنية. ومع ذلك، لا يمكن استخدام الدوال AVG وSUM إلا للبيانات الرقمية، والتي تشمل الأعداد الصحيحة والمال وأنواع البيانات العشرية والعشرية.

استخدام DISTINCT مع الدوال التجميعية

يجب أن تكون على دراية باستخدام DISTINCT في عبارة SELECT لإزالة الصفوف المكررة. عند استخدامها مع دالة تجميعية، تزيل DISTINCT القيم المكررة من عمود الإدخال قبل حساب قيمة الملخص. دالة DISTINCT مفيدة عند تلخيص التكرارات الفريدة للقيم، مثل العملاء في جدول الطلبات.

يُرجع المثال التالي عدد العملاء الذين قدموا طلبات، بصرف النظر عن عدد الطلبات التي قدموها:

SELECT COUNT(DISTINCT CustomerID) AS UniqueCustomers
FROM Sales.SalesOrderHeader;

تقوم COUNT (<some_column>) بحساب عدد الصفوف التي لها بعض القيمة في العمود. إذا لم تكن هناك قيم خالية، فستكون COUNT(<some_column>) هي نفسها COUNT (*). تحسب COUNT (DISTINCT <some_column>) عدد القيم المختلفة الموجودة في العمود.

استخدام دوال تجميعية مع القيمة الخالية

من المهم أن تكون على دراية بالوجود المحتمل للقيم الخالية في بياناتك، وكيف تتفاعل القيمة الخالية مع مكونات استعلام T-SQL، بما في ذلك الدالة التجميعية. هناك بعض الاعتبارات التي ينبغي لك الإلمام بها:

• باستثناء COUNT المستخدمة مع الخيار (*)، تتجاهل دوال T-SQL التجميعية القيم الخالية. على سبيل المثال، ستضيف دالة SUM قيمًا غير خالية فقط. القيم الخالية لا تقيم إلى الصفر. تحسب COUNT (*) جميع الصفوف، بغض النظر عن القيمة أو عدم القيمة في أي عمود.
• قد يؤدي وجود القيم الخالية في عمود إلى حسابات غير دقيقة لـ AVG، والتي ستجمع الصفوف المأهولة فقط وتقسيم هذا المجموع على عدد الصفوف غير الخالية. قد يكون هناك اختلاف في النتائج بين AVG(<column>) و (SUM(<column>)/COUNT(*)).

على سبيل المثال، ضع في اعتبارك الجدول التالي المسمى t1:

C1

C2

1

قيمة فارغة

2

10

3

20

4

30

5

40

6

50

يوضح هذا الاستعلام الفرق بين كيفية تعامل AVG مع القيمة الخالية وكيف يمكنك حساب المتوسط باستخدام عمود SUM / COUNT (*) محسوب:

SELECT SUM(c2) AS sum_nonnulls, 
    COUNT(*) AS count_all_rows, 
    COUNT(c2) AS count_nonnulls, 
    AVG(c2) AS average, 
    (SUM(c2)/COUNT(*)) AS arith_average
FROM t1;

وستكون النتيجة:

sum_nonnulls

count_all_rows

count_nonnulls

average

arith_average

150

6

5

30

25

في مجموعة النتائج هذه، العمود المسمى average هو التجميع الذي يحصل داخليًا على مجموع 150 ويقسم على عدد القيم غير الخالية في العمود c2. سيكون الحساب 150/5 أو 30. يقسم العمود المسمى arith_average المجموع صراحةً على عدد كل الصفوف، وبالتالي يكون الحساب هو 150/6 أو 25.

إذا كنت بحاجة إلى تلخيص كافة الصفوف، سواء كانت خالية أم لا، ففكر في استبدال القيم الخالية بقيمة أخرى لن تتجاهلها الدالة التجميعية. يمكنك استخدام الدالة COALESCE لهذا الغرض.