سلسلة sql للمحترفين التجميع والترتيب في SQL

سنتحدث في هذه المقالة عن كيفية استخدام العبارتين GROUP BY و ORDER BY لأجل تجميع نتائج الاستعلامات في SQL وترتيبها.

التجميع عبر GROUP BY

يمكن تجميع نتائج استعلام SELECT حسب عمود واحد أو أكثر باستخدام عبارة ‎GROUP BY‎ والتي تُجمّع كل النتائج التي لها نفس القيمة في الأعمدة المُجمَّعة (grouped columns). وينتج عنها جدول من النتائج الجزئية، بدلًا من إرجاع نتيجة واحدة.

يمكن استخدام GROUP BY مع دوال التجميع (aggregation functions) لتحديد كيفية تجميع الأعمدة باستخدام العبارة ‎HAVING‎.

مثال على استخدام GROUP BY

تشبه GROUP BY عبارة for each المُستخدمة في الكثير من لغات البرمجة.

إليك الاستعلام التالي:

SELECT EmpID, SUM (MonthlySalary)
FROM Employee
GROUP BY EmpID

في الشيفرة أعلاه، نريد الحصول على مجموع الحقل MonthlySalary لكل قيم EmpID، مثلًا، في الجدول التالي:

+-----+----------------------+
|EmpID|MonthlySalary|
+-----+----------------------+
|1      |200               |
+-----+----------------------+
|2      |300               |
+-----+----------------------+

سنحصل على النتيجة التالية:

+-+---+
|1|200|
+-+---+
|2|300|
+-+---+

لا يبدو أنّ Sum تفعل أيّ شيء، وذلك لأنّ مجموع عدد ما يساوي العدد نفسه.

إليك الآن الجدول التالي:

+-----+---------------------+
|EmpID|MonthlySalary|
+-----+---------------------+
|1    |200                |
+-----+---------------------+
|1    |300               |
+-----+---------------------+
|2    |300              |
+-----+---------------------+

سنحصل بتطبيق الاستعلام نفسه على النتيجة التالية:

+-+---+
|1|500|
+-+---+
|2|300|
+-+---+

ترشيح نتائج GROUP BY باستخدام عبارة HAVING

ترشِّح عبارة HAVING نتائج GROUP BY.

سنستخدم في الأمثلة التالية قاعدة البيانات Library المُعرّفة في الفصل الأول.

أمثلة

إعادة جميع المؤلفين الذين كتبوا أكثر من كتاب (مثال حي).

SELECT
  a.Id,
  a.Name,
 COUNT(*) BooksWritten
FROM BooksAuthors ba
 INNER JOIN Authors a ON a.id = ba.authorid
GROUP BY
  a.Id,
  a.Name
HAVING COUNT(*) > 1    --  HAVING BooksWritten > 1  يكافئ
;

إعادة جميع الكتب التي أُلِّفت من قبل ثلاثة مؤلفين أو أكثر (مثال حي).

SELECT
  b.Id,
  b.Title,
 COUNT(*) NumberOfAuthors
FROM BooksAuthors ba
 INNER JOIN Books b ON b.id = ba.bookid
GROUP BY
  b.Id,
  b.Title
HAVING COUNT(*) > 3    -- HAVING NumberOfAuthors > 3  يكافئ
;

استخدام GROUP BY لحساب عدد الصفوف لكل مدخل فريد في عمود معيّن

لنفترض أننا نريد عدّ أو حساب مجاميع فرعية لقيمة معينة في عمود.

إليك الجدول التالي:

في حال عدم استخدام العبارة GROUP BY، ستعيد COUNT إجمالي عدد الصفوف:

SELECT Count(*) Number_of_Westerosians
FROM Westerosians

سنحصل على الخرج الناتج:

في حال استخدام GROUP BY، يمكن عدّ المستخدمين لكل قيمة في عمود معين كما يوضح المثال التالي:

SELECT GreatHouseAllegience House, Count(*) Number_of_Westerosians
FROM Westerosians
GROUP BY GreatHouseAllegience

الخرج الناتج:

من الشائع الجمع بين العبارتين GROUP BY و ORDER BY لترتيب النتائج تصاعديا أو تنازليًا:

SELECT GreatHouseAllegience House, Count(*) Number_of_Westerosians
FROM Westerosians
GROUP BY GreatHouseAllegience
ORDER BY Number_of_Westerosians Desc

الخرج الناتج:

تجميع ROLAP (استخراج البيانات) - ROLAP aggregation

يوفر معيار SQL معاملين تجميعيَين (aggregate operators) إضافيين. تُستخدم القيمة ALL للكناية عن جميع القيم التي يمكن أن تأخذها السمة (attribute).

المُعاملان هما:

• with data cube: يوفّر كل التوليفات الممكنة لسمات وسيط العبارة (argument attributes of the clause).
• with roll up: يوفر المجموع الناتج عن اعتبار السمات مُرتّبة من اليسار إلى اليمين مع مقارنتها بكيفية إدراجها في وسيط العبارة (argument of the clause).

تدعم إصدارات SQL القياسية التالية هذه الميزات: 1999 - 2003 - 2006 - 2008 - 2011.

أمثلة

إليك الجدول التالي:

استخدام عبارة cube:

select Food,Brand,Total_amount
from Table
group by Food,Brand,Total_amount with cube

الخرج الناتج:

استخدام roll up:

select Food,Brand,Total_amount
from Table
group by Food,Brand,Total_amount with roll up

الخرج الناتج:

الترتيب عبر ORDER BY

الترتيب حسب رقم العمود (بدلاً من اسمه)

يمكنك استخدام رقم العمود (يبدأ العمود الموجود في أقصى اليسار من الرقم "1") للإشارة إلى العمود الذي يستند الترتيب إليه.

• إيجابيات: هذا الخيار مناسب في حال كانت هناك إمكانية لتغيير أسماء الأعمدة لاحقًا، لأنّه سيجنّبك كسر الشيفرة.
• سلبيات: سيُضعف استخدام رقم العمود بدل اسمه مقروئية الاستعلام (وازن مثلا بين ORDER BY Reputation و 'ORDER BY 14'.).

يرتب الاستعلام التالي النتيجة حسب المعلومات الموجودة في العمود رقم ‎3‎ بدلاً من الاعتماد اسم العمود ‎Reputation‎.

SELECT DisplayName, JoinDate, Reputation FROM Users ORDER BY 3

الخرج الناتج:

استخدام ORDER BY مع TOP لإعادة أعلى س صفًّا بناءً على قيمة العمود

في هذا المثال، يمكنك استخدام GROUP BY و TOP لتحديد الصفوف المُعادة وترتيبها. لنفترض أنّك تريد الحصول على أفضل 5 مستخدمين من حيث السمعة في موقع متخصص في الأسئلة والأجوبة.

بدون ORDER BY

يعيد هذا الاستعلام أعلى 5 صفوف مرتبة حسب الإعداد الافتراضي، والذي هو Id في هذه الحالة، أي العمود الأول في الجدول (رغم أنّه لن يظهر في النتائج).

SELECT TOP 5 DisplayName, Reputation
FROM Users

الخرج الناتج:

استخدام ORDER BY

سنستخدم ORDER BY في المثال التالي:

SELECT TOP 5 DisplayName, Reputation
FROM Users
ORDER BY Reputation desc

الخرج الناتج:

ملاحظات

تستخدم بعض إصدارات SQL (مثل MySQL) العبارة ‎LIMIT‎ في نهاية ‎SELECT‎ ، بدلًا من استخدام ‎TOP‎ في البداية كما هو موضّح في المثال التالي:

SELECT DisplayName, Reputation
FROM Users
ORDER BY Reputation DESC
LIMIT 5

الترتيب المخصص

لترتيب الجدول ‎Employee‎ حسب القسم department، يمكنك استخدام التعليمة ‎ORDER BY Department‎. أمّا إن أردت ترتيبه ترتيبًا غير أبجدي، فيجب عليك تحويل قيم ‎Department‎ إلى قيم أخرى قابلة للترتيب؛ يمكن فعل ذلك باستخدام عبارة CASE:

في المثال التالي:

SELECT *
FROM Employee
ORDER BY CASE Department
 WHEN 'HR'            THEN 1
 WHEN 'Accountant'  THEN 2
 ELSE                        3
 END;

سنحصل على الخرج:

الترتيب بالكنى

بسبب طريقة معالجة الاستعلامات المنطقية، يمكن ترتيب نتائج الاستعلامات حسب الكُنى (Order by Alias).

SELECT DisplayName, JoinDate as jd, Reputation as rep
FROM Users
ORDER BY jd, rep

كما يمكن استخدام الترتيب النسبي (relative order) للأعمدة -أي الترتيب حسب رقم العمود- في عبارة الاختيار select. سنعود إلى المثال أعلاه، ولكن بدلاً من استخدام الكُنية، سنستخدم الترتيب النسبي.

SELECT DisplayName, JoinDate as jd, Reputation as rep
FROM Users
ORDER BY 2, 3

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

في المثال التالي:

SELECT DisplayName, JoinDate, Reputation FROM Users ORDER BY JoinDate, Reputation

سيكون الخرج:

المُعاملان المنطِقيان AND و OR

AND و OR معاملان منطقيان يُستخدمان لبناء الشروط المنطقية.

إليك الجدول التالي:

في المثال التالي:

select Name from table where Age>10 AND City='Prague'

سنحصل على الخرج:

وفي هذا المثال:

select Name from table where Age=10 OR City='Prague'

سنحصل على الخرج:

ترجمة -وبتصرّف- للفصول 7 و8 و9 من الكتاب SQL Notes for Professionals

اقرأ أيضًا:

• المقال التالي: تنفيذ تعليمات شرطية عبر CASE في SQL
• المقال السابق: جلب الاستعلامات عبر SELECT في SQL
• النسخة العربية الكاملة لكتاب ملاحظات للعاملين بلغة SQL 1.0.0