كيفية استخدام GROUP BY و ORDER BY في SQL

المقدمة

قواعد بيانات لغة الاستعلام الهيكلية (SQL) يمكن أن تخزن وتدير الكثير من البيانات عبر العديد من الجداول. مع مجموعات بيانات كبيرة، من المهم فهم كيفية فرز البيانات، خاصة لتحليل مجموعات النتائج أو تنظيم البيانات للتقارير أو الاتصالات الخارجية.

يوجد تحقيقين شائعين في SQL يساعدان في فرز بياناتك وهما GROUP BY و ORDER BY. يقوم العبارة GROUP BY بفرز البيانات عن طريق تجميعها بناءً على الأعمدة التي تحددها في الاستعلام ويتم استخدامها مع وظائف التجميع. أما ORDER BY فتسمح لك بتنظيم مجموعات النتائج ترتيب أبجديًا أو عدديًا وبترتيب تصاعدي أو تنازلي.

في هذا البرنامج التعليمي، ستقوم بفرز نتائج الاستعلام في SQL باستخدام عبارات GROUP BY و ORDER BY. كما ستمارس تنفيذ وظائف التجميع وعبارة WHERE في استعلاماتك لفرز النتائج بشكل أكبر.

المتطلبات المسبقة

لمتابعة هذا الدليل، ستحتاج إلى جهاز كمبيوتر يعمل بنظام إدارة قواعد البيانات العلاقوية (RDBMS) الذي يستخدم SQL. تم التحقق من التعليمات والأمثلة في هذا البرنامج التعليمي باستخدام البيئة التالية:

• A server running Ubuntu 20.04, with a non-root user with sudo administrative privileges and firewall enabled. Follow our Initial Server Setup with Ubuntu 20.04 to get started.
• MySQL المثبتة والمؤمنة على الخادم. اتبع دليلنا كيفية تثبيت MySQL على Ubuntu 20.04 لإعداد هذا. يفترض هذا الدليل أيضًا أنك قمت بإعداد مستخدم MySQL غير جذري، كما هو موضح في الخطوة 3 من هذا الدليل.

لممارسة فرز نتائج البيانات في هذا البرنامج التعليمي، ستحتاج إلى قاعدة بيانات وجدول محمل ببيانات عينية. إذا لم يكن لديك واحد جاهز للإدراج، يمكنك قراءة القسم التالي الاتصال بـ MySQL وإعداد قاعدة بيانات عينية لتعلم كيفية إنشاء قاعدة بيانات وجدول. سيشير هذا البرنامج التعليمي إلى هذه القاعدة البيانات والجدول على مدار الدرس.

الاتصال بـ MySQL وإعداد قاعدة بيانات عينية

إذا كانت قاعدة بيانات SQL الخاصة بك تعمل على خادم بعيد، قم بتسجيل الدخول إلى الخادم الخاص بك من جهاز الكمبيوتر المحلي الخاص بك:

ssh sammy@your_server_ip

ثم، افتح نافذة MySQL prompt وقم بتعديل sammy باستخدام معلومات حساب المستخدم الخاصة بك في MySQL:

mysql -u sammy -p

قم بإنشاء قاعدة بيانات بالاسم movieDB:

CREATE DATABASE movieDB;

إذا تم إنشاء قاعدة البيانات بنجاح، ستتلقى الناتج التالي:

Output
Query OK, 1 row affected (0.01 sec)

لتحديد قاعدة البيانات movieDB، قم بتشغيل البيان التالي باستخدام الأمر USE:

USE movieDB;

Output
Database changed

بعد تحديد قاعدة البيانات، قم بإنشاء جدول ضمنها. لهذا المثال التعليمي، سنقوم بإنشاء جدول يخزن معلومات حول عروض السينما المحلية. سيحتوي هذا الجدول على الأعمدة السبعة التالية:

• theater_id: يخزن قيم من نوع البيانات int لغرف عرض السينما، وسيكون هذا العمود هو المفتاح الرئيسي للجدول، مما يعني أن كل قيمة في هذا العمود ستكون معرف فريد لصفها الخاص.المفتاح الرئيسي
• date: يستخدم نوع البيانات DATE لتخزين التاريخ المحدد بالسنة والشهر واليوم الذي تم عرض الفيلم فيه. يتبع هذا النوع من البيانات المعايير التالية: أربعة أرقام للسنة، وحد أقصى من اثنين من الأرقام للشهر واليوم (YYYY-MM-DD).
• time: يمثل موعد عرض الفيلم المجدول باستخدام نوع البيانات TIME بالساعات والدقائق والثواني (HH:MM:SS).
• movie_name: يخزن اسم الفيلم باستخدام نوع البيانات varchar بحد أقصى 40 حرفًا.
• نوع_الفيلم: يستخدم نوع البيانات varchar بحد أقصى 30 حرفًا، لاحتواء معلومات عن نوع كل فيلم.
• إجمالي_الضيوف: يعرض إجمالي عدد الضيوف الذين حضروا عرض فيلم بنوع البيانات int.
• تكلفة_التذكرة: يستخدم نوع البيانات decimal، بدقة تصل إلى أربعة أرقام ومقياس واحد، مما يعني أن القيم في هذا العمود يمكن أن تحتوي على أربعة أرقام، واثنين من الأرقام عن يمين الفاصلة العشرية. يمثل هذا العمود تكلفة التذكرة لعرض الفيلم المحدد.

أنشئ جدولًا يحمل اسم قاعة_السينما يحتوي على كل من هذه الأعمدة بتشغيل الأمر التالي CREATE TABLE:

CREATE TABLE movie_theater (
theater_id int, 
date DATE,
time TIME, 
movie_name varchar(40),
movie_genre varchar(30),
guest_total int,
ticket_cost decimal(4,2),
PRIMARY KEY (theater_id)
); 

بعد ذلك، أدخل بعض البيانات العينية إلى الجدول الفارغ:

INSERT INTO movie_theater
(theater_id, date, time, movie_name, movie_genre, guest_total, ticket_cost)
VALUES
(1, '2022-05-27', '10:00:00', 'Top Gun Maverick', 'Action', 131, 18.00),
(2, '2022-05-27', '10:00:00', 'Downton Abbey A New Era', 'Drama', 90, 18.00),
(3, '2022-05-27', '10:00:00', 'Men', 'Horror', 100, 18.00),
(4, '2022-05-27', '10:00:00', 'The Bad Guys', 'Animation', 83, 18.00),
(5, '2022-05-28', '09:00:00', 'Top Gun Maverick', 'Action', 112, 8.00),
(6, '2022-05-28', '09:00:00', 'Downton Abbey A New Era', 'Drama', 137, 8.00),
(7, '2022-05-28', '09:00:00', 'Men', 'Horror', 25, 8.00),
(8, '2022-05-28', '09:00:00', 'The Bad Guys', 'Animation', 142, 8.00),
(9, '2022-05-28', '05:00:00', 'Top Gun Maverick', 'Action', 150, 13.00),
(10, '2022-05-28', '05:00:00', 'Downton Abbey A New Era', 'Drama', 118, 13.00),
(11, '2022-05-28', '05:00:00', 'Men', 'Horror', 88, 13.00),
(12, '2022-05-28', '05:00:00', 'The Bad Guys', 'Animation', 130, 13.00);

Output
Query OK, 12 rows affected (0.00 sec)
Records: 12  Duplicates: 0  Warnings: 0

بمجرد إدخال البيانات، أنت الآن جاهز لبدء ترتيب نتائج الاستعلام في SQL.

استخدام GROUP BY

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

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

SELECT column_1, AGGREGATE_FUNCTION(column_2) FROM table GROUP BY column_1;

لتوضيح كيف يمكنك استخدام تعليمات GROUP BY، فلنقل أنك تقود حملة لإطلاق عدة أفلام، وترغب في تقييم نجاح جهودك التسويقية. تطلب من مسرح محلي مشاركة البيانات التي جمعوها من الضيوف يومي الجمعة والسبت. ابدأ بمراجعة البيانات عن طريق تشغيل SELECT ورمز * لتحديد “كل عمود” من جدول movie_theater:

SELECT * FROM movie_theater;

Output
+------------+------------+----------+-------------------------+-------------+-------------+-------------+
| theater_id | date       | time     | movie_name              | movie_genre | guest_total | ticket_cost |
+------------+------------+----------+-------------------------+-------------+-------------+-------------+
|          1 | 2022-05-27 | 10:00:00 | Top Gun Maverick        | Action      |         131 |       18.00 |
|          2 | 2022-05-27 | 10:00:00 | Downton Abbey A New Era | Drama       |          90 |       18.00 |
|          3 | 2022-05-27 | 10:00:00 | Men                     | Horror      |         100 |       18.00 |
|          4 | 2022-05-27 | 10:00:00 | The Bad Guys            | Animation   |          83 |       18.00 |
|          5 | 2022-05-28 | 09:00:00 | Top Gun Maverick        | Action      |         112 |        8.00 |
|          6 | 2022-05-28 | 09:00:00 | Downton Abbey A New Era | Drama       |         137 |        8.00 |
|          7 | 2022-05-28 | 09:00:00 | Men                     | Horror      |          25 |        8.00 |
|          8 | 2022-05-28 | 09:00:00 | The Bad Guys            | Animation   |         142 |        8.00 |
|          9 | 2022-05-28 | 05:00:00 | Top Gun Maverick        | Action      |         150 |       13.00 |
|         10 | 2022-05-28 | 05:00:00 | Downton Abbey A New Era | Drama       |         118 |       13.00 |
|         11 | 2022-05-28 | 05:00:00 | Men                     | Horror      |          88 |       13.00 |
|         12 | 2022-05-28 | 05:00:00 | The Bad Guys            | Animation   |         130 |       13.00 |
+------------+------------+----------+-------------------------+-------------+-------------+-------------+
12 rows in set (0.00 sec)

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

نظرًا لأنك عملت على أفلام من عدة أنواع مختلفة، فأنت مهتم بمعرفة مدى قبولها من قبل جمهور السينما. على وجه الخصوص، ترغب في معرفة المتوسط ​​لعدد الأشخاص الذين شاهدوا كل نوع من الأفلام. استخدم SELECT لاسترداد أنواع الأفلام المختلفة من عمود movie_genre. ثم طبق وظيفة التجميع AVG على عمود guest_total، استخدم AS لإنشاء اسم مستعار لعمود يسمى average، وقم بتضمين تعليمة GROUP BY لتجميع النتائج حسب movie_genre. سيوفر تجميعها بهذه الطريقة لك النتائج المتوسطة لكل نوع من الأفلام:

SELECT movie_genre, AVG(guest_total) AS average
FROM movie_theater 
GROUP BY movie_genre;

Output
+-------------+----------+
| movie_genre | average  |
+-------------+----------+
| Action      | 131.0000 |
| Drama       | 115.0000 |
| Horror      |  71.0000 |
| Animation   | 118.3333 |
+-------------+----------+
4 rows in set (0.00 sec)

تقدم هذه النتيجة المتوسط ​​الأربعة لكل نوع في مجموعة movie_genre. استنادًا إلى هذه المعلومات، جذبت أفلام الحركة أعلى متوسط ​​لعدد الضيوف لكل عرض.

الآن، دعنا نفترض أنك ترغب في قياس عائدات المسرح على يومين منفصلين. تعيد الاستعلام التالي القيم من عمود date، بالإضافة إلى القيم التي تعيد بواسطة وظيفة التجميع SUM. تحديدا، ستقوم وظيفة التجميع SUM بتضمين معادلة رياضية بين قوسين لضرب (باستخدام العامل *) عدد الضيوف الإجمالي بتكلفة التذكرة، والتي تمثل على النحو التالي: SUM(guest_total * ticket_cost). يتضمن هذا الاستعلام العبارة AS لتوفير الاسم المستعار total_revenue للعمود الذي تعيده الوظيفة التجميعية. ثم أكمل الاستعلام بعبارة GROUP BY لتجميع نتائج الاستعلام حسب عمود date:

SELECT date, SUM(guest_total * ticket_cost) 
AS total_revenue 
FROM movie_theater 
GROUP BY date;

Output
+------------+---------------+
| date       | total_revenue |
+------------+---------------+
| 2022-05-27 |       7272.00 |
| 2022-05-28 |       9646.00 |
+------------+---------------+
2 rows in set (0.00 sec)

نظرًا لاستخدامك GROUP BY لتجميع عمود date، تقدم النتيجة إجمالي الإيرادات في مبيعات التذاكر لكل يوم، في هذه الحالة، 7,272 دولارًا ليوم الجمعة 27 مايو، و9,646 دولارًا ليوم السبت 28 مايو.

الآن تخيل أنك ترغب في التركيز على وتحليل فيلم واحد: The Bad Guys. في هذ scenario، تريد معرفة كيف تؤثر التوقيت ونقاط السعر على اختيار العائلة لمشاهدة فيلم رسوم متحركة. لهذا الاستعلام استخدم الوظيفة الجماعية MAX لاسترداد أقصى ticket_cost، مع التأكد من تضمين AS لإنشاء الاسم المستعار لعمود price_data. بعد ذلك، استخدم الجملة WHERE لتضييق النتائج حسب movie_name للفيلم “The Bad Guys” فقط، واستخدم AND أيضًا لتحديد أوقات العرض الأكثر شيوعًا بناءً على أرقام guest_total التي كانت أكثر من 100 باستخدام عامل المقارنة >. ثم أكمل الاستعلام بعبارة GROUP BY واجمعها حسب time:

SELECT time, MAX(ticket_cost) AS price_data 
FROM movie_theater
WHERE movie_name = "The Bad Guys" 
AND guest_total > 100
GROUP BY time;

Output
+----------+------------+
| time     | price_data |
+----------+------------+
| 09:00:00 |       8.00 |
| 05:00:00 |      13.00 |
+----------+------------+
2 rows in set (0.00 sec)

وفقًا لهذه النتائج، حضر المزيد من الضيوف فيلم The Bad Guys في وقت العرض المبكر الساعة 9:00 صباحًا، الذي كانت تكلفة التذكرة فيه أقل وتبلغ 8.00 دولار لكل تذكرة. ومع ذلك، تشير هذه النتائج أيضًا إلى أن ضيوف الفيلم دفعوا سعر التذكرة الأعلى 13.00 دولار في الساعة 5:00 مساءً، مما يوحي بأن العائلات يفضلون العروض التي ليست في وقت متأخر من اليوم وسيدفعون قليلاً أكثر للحصول على تذكرة. يبدو أن هذا تقديرًا عادلًا عند مقارنته بالساعة 10:00 مساءً عندما كان فيلم The Bad Guys لديه فقط 83 ضيفًا وكان سعر التذكرة 18.00 دولار. يمكن أن تكون هذه معلومات مفيدة لتزويد مدير دار السينما بأدلة تشير إلى أن فتح المزيد من الفترات الزمنية للعروض الصباحية والمسائية المبكرة يمكن أن يزيد من حضور الجمهور الذي يقوم بصنع الخيار بناءً على الوقت المفضل ونقطة السعر.

يرجى ملاحظة أنه على الرغم من أن GROUP BY يُستخدم دائمًا تقريبًا مع وظيفة تجميع، قد تكون هناك استثناءات، على الرغم من كونها غير محتملة. ومع ذلك، إذا كنت ترغب في تجميع نتائجك بدون وظيفة تجميع، يمكنك استخدام بيان DISTINCT لتحقيق نفس النتيجة. يقوم بناء عبارة DISTINCT بإزالة أي تكرارات في مجموعة النتائج عن طريق إرجاع القيم الفريدة في العمود، ويمكن استخدامه فقط مع بيان SELECT. على سبيل المثال، إذا كنت ترغب في تجميع جميع الأفلام معًا حسب الاسم، يمكنك القيام بذلك باستخدام الاستعلام التالي:

SELECT DISTINCT movie_name FROM movie_theater;

Output
+-------------------------+
| movie_name              |
+-------------------------+
| Top Gun Maverick        |
| Downton Abbey A New Era |
| Men                     |
| The Bad Guys            |
+-------------------------+
4 rows in set (0.00 sec)

كما تتذكر من استعراض جميع البيانات في الجدول، كانت هناك تكرارات في أسماء الأفلام نظرًا لوجود عروض متعددة. لذلك، قام DISTINCT بإزالة تلك التكرارات وجمع القيم الفريدة بفعالية تحت العمود الواحد movie_name. وهذا متطابق تمامًا مع الاستعلام التالي، الذي يتضمن بيان GROUP BY:

SELECT movie_name FROM movie_theater GROUP BY movie_name;

الآن بعد أن قمت بممارسة استخدام GROUP BY مع وظائف التجميع، ستتعلم كيفية ترتيب نتائج الاستعلام الخاص بك باستخدام بيان ORDER BY.

باستخدام ORDER BY

وظيفة البيانة ORDER BY هي فرز النتائج بترتيب تصاعدي أو تنازلي استنادًا إلى الأعمدة التي تحددها في الاستعلام. وبناءً على نوع البيانات المخزنة بواسطة العمود الذي تحدده بعد ذلك، ستنظم ORDER BY هذه النتائج بترتيب أبجدي أو رقمي. بشكل افتراضي، ستقوم ORDER BY بفرز النتائج بترتيب تصاعدي. ومع ذلك، إذا كنت تفضل الترتيب التنازلي، فيجب عليك تضمين الكلمة الرئيسية DESC في استعلامك. يمكنك أيضًا استخدام بيانة ORDER BY مع GROUP BY، ولكن يجب أن تأتي بعد ذلك من أجل العمل بشكل صحيح. وبالمثل، يجب أن تأتي ORDER BY أيضًا بعد بيانة FROM وشرط WHERE. الصيغة العامة لاستخدام ORDER BY كما يلي:

SELECT column_1, column_2 FROM table ORDER BY column_1;

لنواصل مع البيانات العينية لصالة السينما ونمارس ترتيب النتائج باستخدام ORDER BY. ابدأ بالاستعلام التالي الذي يسترد القيم من العمود guest_total وينظم تلك القيم الرقمية باستخدام بيانة ORDER BY:

SELECT guest_total FROM movie_theater 
ORDER BY guest_total;

Output
+-------------+
| guest_total |
+-------------+
|          25 |
|          83 |
|          88 |
|          90 |
|         100 |
|         112 |
|         118 |
|         130 |
|         131 |
|         137 |
|         142 |
|         150 |
+-------------+
12 rows in set (0.00 sec)

نظرًا لأن استعلامك حدد عمودًا يحتوي على قيم رقمية، فقد قامت بيانة ORDER BY بتنظيم النتائج بترتيب رقمي تصاعدي، بدءًا من 25 تحت العمود guest_total.

إذا كنت تفضل ترتيب العمود بترتيب تنازلي، يمكنك إضافة كلمة المفتاح DESC في نهاية الاستعلام. بالإضافة إلى ذلك، إذا كنت ترغب في ترتيب البيانات حسب القيم الحرفية تحت movie_name، يجب أن تحدد ذلك في استعلامك. دعنا ننفذ هذا النوع من الاستعلام باستخدام ORDER BY لترتيب العمود movie_name بالقيم الحرفية بترتيب تنازلي. قم بترتيب النتائج بشكل أكبر عن طريق تضمين عبارة WHERE لاسترجاع البيانات حول الأفلام التي تعرض الساعة 10:00 مساءً من عمود الوقت:

SELECT movie_name FROM movie_theater
WHERE time = '10:00:00' 
ORDER BY movie_name DESC;

Output
+-------------------------+
| movie_name              |
+-------------------------+
| Top Gun Maverick        |
| The Bad Guys            |
| Men                     |
| Downton Abbey A New Era |
+-------------------------+
4 rows in set (0.01 sec)

تعرض مجموعة النتائج هذه أربعة عروض أفلام مختلفة في الساعة 10:00 مساءً بترتيب أبجدي تنازلي، بدءًا من Top Gun Maverick إلى Downtown Abbey A New Era.

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

في هذا الاستعلام، استخدم SUM وضمن الضيوف الإضافيين الـ 12 في كل عرض فيلم عن طريق تنفيذ عملية الجمع + ثم إضافة 12 إلى guest_total. تأكد من توضيح هذا في قوسين. ثم، قم بضرب هذا المجموع بقيمة تذكرة الدخول ticket_cost باستخدام العامل *، وأكمل المعادلة الرياضية بإغلاق الأقواس في النهاية. أضف الفقرة AS لإنشاء الاسم المستعار للعمود الجديد بعنوان total_revenue. ثم، استخدم GROUP BY لتجميع نتائج total_revenue لكل فيلم استنادًا إلى البيانات المستردة من العمود movie_name. وأخيرًا، استخدم ORDER BY لتنظيم النتائج تحت العمود الجديد total_revenue بترتيب تصاعدي:

SELECT movie_name, SUM((guest_total + 12) * ticket_cost) 
AS total_revenue
FROM movie_theater 
GROUP BY movie_name 
ORDER BY total_revenue;

Output
+-------------------------+---------------+
| movie_name              | total_revenue |
+-------------------------+---------------+
| Men                     |       3612.00 |
| Downton Abbey A New Era |       4718.00 |
| The Bad Guys            |       4788.00 |
| Top Gun Maverick        |       5672.00 |
+-------------------------+---------------+
4 rows in set (0.00 sec)

تُخبرنا مجموعة النتائج هذه عن إجمالي الإيرادات لكل فيلم مع بيع تذاكر الضيوف الإضافيين الـ 12 وتنظم إجمالي مبيعات التذاكر بترتيب تصاعدي من الأدنى إلى الأعلى. من هذا، نتعلم أن فيلم Top Gun Maverick حصل على أكبر مبيعات للتذاكر، بينما حصل فيلم Men على أقلها. في الوقت نفسه، كانت أفلام The Bad Guys و Downton Abbey A New Era قريبة جدًا في إجمالي مبيعات التذاكر.

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

الختام

فهم كيفية استخدام عبارات GROUP BY و ORDER BY مهم لفرز النتائج والبيانات الخاصة بك. سواء كنت ترغب في تنظيم النتائج المتعددة تحت مجموعة واحدة، أو تنظيم إحدى الأعمدة الخاصة بك ترتيبا أبجديا تنازليا، أو القيام بكليهما معًا في نفس الوقت؛ فإنه يعتمد عليك وعلى النتيجة المرغوبة لديك. كما تعلمت أيضًا عن طرق أخرى لترتيب النتائج بشكل أعمق باستخدام عبارة WHERE. إذا كنت ترغب في معرفة المزيد، تفضل بزيارة الدرس التعليمي الخاص بنا حول كيفية استخدام البطاقات النمطية في SQL لممارسة تصفية النتائج باستخدام عبارة LIKE.