MySQL الدوال التجميعية: المجموع، العد، AVG & الأعلى

ما هي الدوال التجميعية في MySQL?

An دالة مجمعة تقرأ هذه الدالة عدة صفوف من عمود واحد وتدمجها في قيمة واحدة. وتتمحور دوال التجميع حول:

• إجراء العمليات الحسابية على صفوف متعددة
• من عمود واحد من الجدول
• وإرجاع قيمة واحدة.

يحدد معيار ISO خمس (5) دوال تجميعية، وهي:

1. بالإحصاء
2. مجموع
3. AVG
4. دقيقة
5. وفر

قاعدة واحدة تنطبق على الخمسة جميعاً: تتجاهل الدوال التجميعية القيم الفارغة (NULL).. COUNT(*) هو الاستثناء الوحيد، وسننظر في السبب أدناه.

لماذا استخدام الوظائف المجمعة

تختلف متطلبات المعلومات باختلاف مستويات المؤسسة. فعادةً ما يهتم كبار المديرين بالأرقام الإجمالية، وليس بالتفاصيل الفردية.

تتيح لنا الوظائف التجميعية إنتاج بيانات ملخصة بسهولة من قاعدة البيانات الخاصة بنا.

على سبيل المثال، قد تحتاج الإدارة إلى التقارير التالية من قاعدة بيانات myflix الخاصة بنا:

• الأفلام الأقل إيجارًا.
• معظم الأفلام المستأجرة.
• متوسط ​​عدد مرات استئجار كل فيلم في الشهر.

جميع التقارير المذكورة أعلاه مستمدة من دوال التجميع. دعونا نلقي نظرة على كل منها بالتفصيل.

وظيفة العد

تقوم دالة COUNT بإرجاع العدد الإجمالي للقيم في الحقل المحدد، وذلك لكل من أنواع البيانات الرقمية وغير الرقمية. مثل كل دالة تجميعية، فإن دالة COUNT(column) تستبعد القيم الفارغة (NULL).

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

يحتوي جدول تأجير الأفلام على هذه البيانات:

لنفترض أننا نريد الحصول على عدد مرات تأجير الفيلم ذي المعرف 2.

SELECT COUNT(`movie_id`) FROM `movierentals` WHERE `movie_id` = 2;

تنفيذ هذا في MySQL طاولة العمل يُرجع الاستعلام ضد قاعدة بيانات myflixdb القيمة 3، لأن ثلاثة صفوف تحمل رقم الفيلم 2.

كلمة رئيسية مميزة

يجيب العد على سؤال "كم عدد؟". السؤال التالي عادةً ما يكون "كم عدد؟" مختلف "الواحدات"، وهذا هو الغرض من DISTINCT.

تستبعد الكلمة المفتاحية DISTINCT النتائج المكررة من نتائجنا حسب المجموعةping القيم المتطابقة معًا، تمامًا كما يوضح الرسم التوضيحي أعلاه.

لنبدأ أولاً بتنفيذ استعلام بسيط.

SELECT `movie_id` FROM `movierentals`;

والآن نفس الاستعلام مع الكلمة المفتاحية DISTINCT:

SELECT DISTINCT `movie_id` FROM `movierentals`;

يستبعد الأمر DISTINCT السجلات المكررة:

COUNT مقابل COUNT(*) مقابل COUNT(DISTINCT): أيها يجب عليك استخدامه؟

يمكن أيضًا وضع DISTINCT في الداخل دالة تجميعية، وهنا يضيع معظم المبتدئين tracيتم حساب k من الصفوف فعليًا. تعمل النماذج الأربعة أدناه جميعها على نفس جدول تأجير الأفلام المكون من خمسة صفوف الموضح سابقًا، ومع ذلك، لا تُرجع جميعها نفس العدد. يكمن الاختلاف في سؤالين: هل يحسب النموذج الصفوف أم القيم، وهل يحتفظ بالبيانات المكررة؟

SELECT COUNT(*) AS `all_rows`,
       COUNT(`return_date`) AS `returned_rows`,
       COUNT(DISTINCT `movie_id`) AS `unique_movies`
FROM `movierentals`;

💡 نصيحة: استخدم COUNT(*) لحساب عدد الصفوف، وCOUNT(column) عندما تعني القيمة NULL "غير منطبق"، وCOUNT(DISTINCT column) للقيم الفريدة. عكس DISTINCT هو ALL - وهو الوضع الافتراضي، ولذلك نادرًا ما يُستخدم.

وظيفة مين

وظيفة مين تُرجع أصغر قيمة في حقل الجدول المحدد.

لنفترض أننا نريد معرفة السنة التي صدر فيها أقدم فيلم في مكتبتنا. MySQLتعطينا دالة MIN ذلك.

SELECT MIN(`year_released`) FROM `movies`;

النتيجة:

وظيفة ماكس

كما يوحي الاسم، فإن الدالة MAX هي عكس الدالة MIN. هو - هي إرجاع أكبر قيمة من حقل الجدول المحدد.

لنفترض أننا نريد معرفة السنة التي صدر فيها أحدث فيلم في قاعدة بياناتنا. المثال التالي يُعيدها.

SELECT MAX(`year_released`) FROM `movies`;

النتيجة:

مجموع وظيفة

تستخدم الدالتان MIN وMAX قيمة موجودة من عمود. أما الدالتان SUM و AVG احسب رقمًا جديدًا من العمود بأكمله.

لنفترض أننا نريد معرفة إجمالي المدفوعات التي تمت حتى الآن. MySQL مجموع وظيفة تُرجع مجموع جميع القيم في العمود المحدد. يعمل SUM على الحقول الرقمية فقطو يتم استبعاد القيم الفارغة من النتيجة.

يوضح الجدول التالي البيانات الموجودة في جدول المدفوعات.

الاستعلام الموضح أدناه يحصل على جميع المدفوعات التي تمت ويجمعها في نتيجة واحدة: 2500 + 2000 + 6000 = 10500.

SELECT SUM(`amount_paid`) FROM `payments`;

النتيجة:

AVG وظيفة

استخدم MySQL AVG وظيفة إرجاع متوسط ​​القيم في عمود محدد. تمامًا مثل الدالة SUM، فهي يعمل فقط على أنواع البيانات الرقمية.

لنفترض أننا نريد إيجاد متوسط ​​المبلغ المدفوع. يمكننا استخدام الاستعلام التالي، الذي يقسم المجموع 10500 على صفوف الدفع الثلاثة غير الفارغة.

SELECT AVG(`amount_paid`) FROM `payments`;

النتيجة:

⚠️ تحذير: AVG يقسم على عدد الصفوف غير الفارغة، وليس على عدد صفوف الجدول. يتم تجاهل القيمة الفارغة بدلاً من احتسابها كصفر، مما يؤدي إلى رفع المتوسط ​​بشكل غير ملحوظ. استخدم AVG(IFNULL(`amount_paid`, 0)) عندما تعني القيمة المفقودة الصفر.

مثال عملي: دمج الدوال التجميعية مع عبارة GROUP BY

أعادت كل دالة من الدوال المذكورة أعلاه رقمًا واحدًا للجدول بأكمله. إضافة GROUP BY تُرجع العبارة رقمًا واحدًا لكل مجموعة بدلاً من ذلك - وهكذا تُبنى التقارير الحقيقية.

يقوم المثال التالي بتجميع الأعضاء حسب الاسم، ثم يحسب إجمالي عدد المدفوعات، ومتوسط ​​مبلغ الدفعة، والمجموع الكلي لمبالغ الدفع لكل عضو.

SELECT m.`full_names`,
       COUNT(p.`payment_id`) AS `paymentscount`,
       AVG(p.`amount_paid`) AS `averagepaymentamount`,
       SUM(p.`amount_paid`) AS `totalpayments`
FROM members m, payments p
WHERE m.`membership_number` = p.`membership_number`
GROUP BY m.`full_names`;

تنفيذ المثال أعلاه في MySQL يُعطينا برنامج Workbench النتائج التالية.

يربط الاستعلام الجدولين في عبارة WHERE - وهو أسلوب الربط بالفاصلة القديم. أما الكود الحديث فيكتب نفس المنطق بشكل صريح. الربط الداخلي … تشغيللاحظ أيضًا أنه يجب أن يظهر كل عمود غير مجمع في قائمة SELECT في GROUP BY، أو MySQL 5.7 والإصدارات اللاحقة ترفض الاستعلام ضمن ONLY_FULL_GROUP_BY. انظر رسمي MySQL مرجع دالة التجميع.

الأسئلة الشائعة