كل ما تحتاج لمعرفته حول بيان SQL GROUP BY

يأتي جزء كبير من قوة قواعد البيانات العلائقية من تصفية البيانات وربط الجداول معًا. هذا هو السبب في أننا نمثل تلك العلاقات في المقام الأول. لكن أنظمة قواعد البيانات الحديثة توفر أسلوبًا آخر قيمًا: التجميع.

يسمح لك التجميع باستخراج معلومات موجزة من قاعدة بيانات. يتيح لك دمج النتائج لإنشاء بيانات إحصائية مفيدة. يحميك التجميع من كتابة التعليمات البرمجية للحالات الشائعة مثل حساب متوسط ​​قوائم الأرقام. ويمكن أن تقدم لأنظمة أكثر كفاءة.

---

---

ماذا تفعل فقرة GROUP BY؟

GROUP BY ، كما يوحي الاسم ، تقوم بتجميع النتائج في مجموعة أصغر. تتكون النتائج من صف واحد لكل قيمة مميزة للعمود المجمع. يمكننا إظهار استخدامه من خلال النظر في بعض البيانات النموذجية مع الصفوف التي تشترك في بعض القيم المشتركة.

جعل محرك أقراص فلاش ويندوز قابل للتمهيد 7

ما يلي هو قاعدة بيانات بسيطة للغاية مع جدولين يمثلان ألبومات التسجيلات. يمكنك إنشاء قاعدة البيانات هذه عن طريق كتابة مخطط أساسي لنظام قاعدة البيانات الذي اخترته. ال ألبومات يحتوي الجدول على تسعة صفوف بمفتاح أساسي هوية شخصية عمود وأعمدة للاسم والفنان وسنة الإصدار والمبيعات:

+----+---------------------------+-----------+--------------+-------+
| id | name | artist_id | release_year | sales |
+----+---------------------------+-----------+--------------+-------+
| 1 | Abbey Road | 1 | 1969 | 14 |
| 2 | The Dark Side of the Moon | 2 | 1973 | 24 |
| 3 | Rumours | 3 | 1977 | 28 |
| 4 | Nevermind | 4 | 1991 | 17 |
| 5 | Animals | 2 | 1977 | 6 |
| 6 | Goodbye Yellow Brick Road | 5 | 1973 | 8 |
| 7 | 21 | 6 | 2011 | 25 |
| 8 | 25 | 6 | 2015 | 22 |
| 9 | Bat Out of Hell | 7 | 1977 | 28 |
+----+---------------------------+-----------+--------------+-------+

ال الفنانين الجدول أبسط. يحتوي على سبعة صفوف مع أعمدة المعرف والاسم:

+----+---------------+
| id | name |
+----+---------------+
| 1 | The Beatles |
| 2 | Pink Floyd |
| 3 | Fleetwood Mac |
| 4 | Nirvana |
| 5 | Elton John |
| 6 | Adele |
| 7 | Meat Loaf |
+----+---------------+

يمكنك فهم الجوانب المختلفة لـ GROUP BY من خلال مجموعة بيانات بسيطة مثل هذه. بالطبع ، قد تحتوي مجموعة البيانات الواقعية على العديد والعديد من الصفوف ، لكن المبادئ تظل كما هي.

التجميع حسب عمود واحد

لنفترض أننا نريد معرفة عدد الألبومات التي لدينا لكل فنان. ابدأ بملف تحديد استعلام لجلب عمود الفنان_id:

---

---

SELECT artist_id FROM albums

يؤدي هذا إلى إرجاع جميع الصفوف التسعة ، كما هو متوقع:

+-----------+
| artist_id |
+-----------+
| 1 |
| 2 |
| 3 |
| 4 |
| 2 |
| 5 |
| 6 |
| 6 |
| 7 |
+-----------+

لتجميع هذه النتائج حسب الفنان ، قم بإلحاق العبارة GROUP BY artist_id :

---

---

SELECT artist_id FROM albums GROUP BY artist_id

مما يعطي النتائج التالية:

+-----------+
| artist_id |
+-----------+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 6 |
| 7 |
+-----------+

هناك سبعة صفوف في مجموعة النتائج ، مخفضة من إجمالي تسعة في ألبومات طاولة. كل فريد معرف الفنان له صف واحد. أخيرًا ، للحصول على العدد الفعلي ، أضف عدد(*) إلى الأعمدة المختارة:

SELECT artist_id, COUNT(*)
FROM albums
GROUP BY artist_id
+-----------+----------+
| artist_id | COUNT(*) |
+-----------+----------+
| 1 | 1 |
| 2 | 2 |
| 3 | 1 |
| 4 | 1 |
| 5 | 1 |
| 6 | 2 |
| 7 | 1 |
+-----------+----------+

تجمع النتائج زوجان من الصفوف للفنانين باستخدام معرفات 2 و 6 . لكل منها ألبومان في قاعدة بياناتنا.

متعلق ب: ورقة الغش لأوامر SQL الأساسية للمبتدئين

كيفية الوصول إلى البيانات المجمعة باستخدام دالة مجمعة

ربما تكون قد استخدمت ملف عدد وظيفة من قبل ، لا سيما في عدد(*) شكل كما رأينا أعلاه. يجلب عدد النتائج في مجموعة. يمكنك استخدامه للحصول على العدد الإجمالي للسجلات في جدول:

SELECT COUNT(*) FROM albums
+----------+
| COUNT(*) |
+----------+
| 9 |
+----------+

تعد COUNT دالة مجمعة. يشير هذا المصطلح إلى الوظائف التي تترجم القيم من صفوف متعددة إلى قيمة واحدة. غالبًا ما يتم استخدامها جنبًا إلى جنب مع بيان GROUP BY.

بدلاً من مجرد حساب عدد الصفوف ، يمكننا تطبيق دالة مجمعة على القيم المجمعة:

SELECT artist_id, SUM(sales)
FROM albums
GROUP BY artist_id
+-----------+------------+
| artist_id | SUM(sales) |
+-----------+------------+
| 1 | 14 |
| 2 | 30 |
| 3 | 28 |
| 4 | 17 |
| 5 | 8 |
| 6 | 47 |
| 7 | 28 |
+-----------+------------+

إجمالي المبيعات المعروضة أعلاه للفنانين 2 و 6 هي مبيعات ألبوماتهم المتعددة مجتمعة:

SELECT artist_id, sales
FROM albums
WHERE artist_id IN (2, 6)
+-----------+-------+
| artist_id | sales |
+-----------+-------+
| 2 | 24 |
| 2 | 6 |
| 6 | 25 |
| 6 | 22 |
+-----------+-------+

التجميع حسب الأعمدة المتعددة

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

SELECT release_year, sales, count(*)
FROM albums
GROUP BY release_year, sales

سينتج عن هذا عادةً نتائج أكثر من التجميع حسب عمود واحد:

+--------------+-------+----------+
| release_year | sales | count(*) |
+--------------+-------+----------+
| 1969 | 14 | 1 |
| 1973 | 24 | 1 |
| 1977 | 28 | 2 |
| 1991 | 17 | 1 |
| 1977 | 6 | 1 |
| 1973 | 8 | 1 |
| 2011 | 25 | 1 |
| 2015 | 22 | 1 |
+--------------+-------+----------+

لاحظ أنه في مثالنا الصغير ، هناك ألبومان فقط لهما نفس سنة الإصدار وعدد المبيعات (28 في 1977).

وظائف مجمعة مفيدة

بصرف النظر عن COUNT ، تعمل العديد من الوظائف بشكل جيد مع GROUP. تقوم كل دالة بإرجاع قيمة بناءً على السجلات التي تنتمي إلى كل مجموعة نتائج.

• تُرجع الدالة COUNT () العدد الإجمالي للسجلات المتطابقة.
• تُرجع الدالة SUM () إجمالي كل القيم في العمود المحدد المضافة.
• تُرجع الدالة MIN () أصغر قيمة في عمود معين.
• تُرجع MAX () أكبر قيمة في عمود معين.
• تُرجع الدالة AVG () المتوسط ​​المتوسط. إنه يعادل SUM () / COUNT ().

يمكنك أيضًا استخدام هذه الوظائف بدون عبارة GROUP:

مفتاح النافذة لا يعمل بنظام التشغيل windows 10

SELECT AVG(sales) FROM albums
+------------+
| AVG(sales) |
+------------+
| 19.1111 |
+------------+

استخدام GROUP BY مع عبارة WHERE

تمامًا كما هو الحال مع SELECT العادي ، لا يزال بإمكانك استخدام WHERE لتصفية مجموعة النتائج:

SELECT artist_id, COUNT(*)
FROM albums
WHERE release_year > 1990
GROUP BY artist_id +-----------+----------+
| artist_id | COUNT(*) |
+-----------+----------+
| 4 | 1 |
| 6 | 2 |
+-----------+----------+

الآن لديك فقط تلك الألبومات التي تم إصدارها بعد عام 1990 ، مجمعة حسب الفنان. يمكنك أيضًا استخدام صلة مع جملة WHERE ، بشكل مستقل عن GROUP BY:

SELECT r.name, COUNT(*) AS albums
FROM albums l, artists r
WHERE artist_id=r.id
AND release_year > 1990
GROUP BY artist_id +---------+--------+
| name | albums |
+---------+--------+
| Nirvana | 1 |
| Adele | 2 |
+---------+--------+

ومع ذلك ، لاحظ أنه إذا حاولت التصفية استنادًا إلى عمود مجمع:

SELECT r.name, COUNT(*) AS albums
FROM albums l, artists r
WHERE artist_id=r.id
AND albums > 2
GROUP BY artist_id;

سوف تحصل على خطأ:

ERROR 1054 (42S22): Unknown column 'albums' in 'where clause'

لا تتوفر الأعمدة المستندة إلى البيانات المجمعة لبند WHERE.

استخدام شرط HAVING

لذا ، كيف تقوم بتصفية مجموعة النتائج بعد حدوث التجميع؟ ال نأخذ بند يتعامل مع هذه الحاجة:

SELECT r.name, COUNT(*) AS albums
FROM albums l, artists r
WHERE artist_id=r.id
GROUP BY artist_id
HAVING albums > 1;

لاحظ أن عبارة HAVING تأتي بعد GROUP BY. خلافًا لذلك ، فهو في الأساس بديل بسيط لـ WHERE بـ HAVING. النتائج هي:

+------------+--------+
| name | albums |
+------------+--------+
| Pink Floyd | 2 |
| Adele | 2 |
+------------+--------+

لا يزال بإمكانك استخدام شرط WHERE لتصفية النتائج قبل التجميع. ستعمل جنبًا إلى جنب مع شرط HAVING للتصفية بعد التجميع:

SELECT r.name, COUNT(*) AS albums
FROM albums l, artists r
WHERE artist_id=r.id
AND release_year > 1990
GROUP BY artist_id
HAVING albums > 1;

أصدر فنان واحد فقط في قاعدة بياناتنا أكثر من ألبوم واحد بعد عام 1990:

+-------+--------+
| name | albums |
+-------+--------+
| Adele | 2 |
+-------+--------+

دمج النتائج مع GROUP BY

عبارة GROUP BY هي جزء مفيد للغاية من لغة SQL. يمكن أن يوفر معلومات موجزة عن البيانات ، لصفحة المحتويات ، على سبيل المثال. إنه بديل ممتاز لجلب كميات كبيرة من البيانات. تتعامل قاعدة البيانات مع عبء العمل الإضافي هذا جيدًا نظرًا لأن تصميمها يجعلها مثالية للمهمة.

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

يشارك يشارك سقسقة بريد الالكتروني كيفية الاستعلام عن جداول قاعدة بيانات متعددة في وقت واحد باستخدام عمليات انضمام SQL

تعرف على كيفية استخدام عمليات الانضمام إلى SQL لتبسيط الاستعلامات وتوفير الوقت وجعلك تشعر بأنك مستخدم قوي في SQL.

كيف تحجب نفسك من المواقع

اقرأ التالي مواضيع ذات صلة

• برمجة
• SQL

نبذة عن الكاتب بوبي جاك(تم نشر 58 مقالة)

بوبي متحمس للتكنولوجيا وعمل كمطور برمجيات لأكثر من عقدين من الزمن. إنه شغوف بالألعاب ، ويعمل كمحرر المراجعات في مجلة Switch Player ، ومنغمس في جميع جوانب النشر عبر الإنترنت وتطوير الويب.

المزيد من Bobby Jack

اشترك في نشرتنا الإخبارية

انضم إلى النشرة الإخبارية لدينا للحصول على نصائح تقنية ومراجعات وكتب إلكترونية مجانية وصفقات حصرية!

انقر هنا للاشتراك