محمد الميداوي

سنتحدث في هذه المقالة عن كيفية استخدام العبارتين 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 لحساب عدد الصفوف لكل مدخل فريد في عمود معيّن

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

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

Name GreatHouseAllegience
Arya Stark
Cercei Lannister
Myrcella Lannister
Yara Greyjoy
Catelyn Stark
Sansa Stark

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

SELECT Count(*) Number_of_Westerosians
FROM Westerosians

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

Number_of_Westerosians
6

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

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

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

House Number_of_Westerosians
Stark 3
Greyjoy 1
Lannister 2

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

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

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

House Number_of_Westerosians
Stark 3
Lannister 2
Greyjoy 1

تجميع 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.

أمثلة

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

Food Brand Total_amount
Pasta Brand1 100
Pasta Brand2 250
Pizza Brand2 300

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

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

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

Food Brand Total_amount
Pasta Brand1 100
Pasta Brand2 250
Pasta ALL 350
Pizza Brand2 300
Pizza ALL 300
ALL Brand1 100
ALL Brand2 550
ALL ALL 650

استخدام roll up:

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

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

Food Brand Total_amount
Pasta Brand1 100
Pasta Brand2 250
Pizza Brand2 300
Pasta ALL 350
Pizza ALL 300
ALL ALL 650

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

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

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

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

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

SELECT DisplayName, JoinDate, Reputation FROM Users ORDER BY 3

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

DisplayName JoinDate Reputation
Community 2008-09-15 1
Jarrod Dixon 2008-10-03 11739
Geoff Dalgas 2008-10-03 12567
Joel Spolsky 2008-09-16 25784
Jeff Atwood 2008-09-16 37628

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

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

بدون ORDER BY

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

SELECT TOP 5 DisplayName, Reputation
FROM Users

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

DisplayName Reputation
Community 1
Geoff Dalgas 12567
Jarrod Dixon 11739
Jeff Atwood 37628
Joel Spolsky 25784

استخدام ORDER BY

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

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

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

DisplayName Reputation
JonSkeet 865023
Darin Dimitrov 661741
BalusC 650237
Hans Passant 625870
Marc Gravell 601636

ملاحظات

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

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

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

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

Name Department
Hasan IT
Yusuf HR
Hillary HR
Joe IT
Merry HR
Ken Accountant

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

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

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

Name Department
Yusuf HR
Hillary HR
Merry HR
Ken Accountant
Hasan IT
Joe IT

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

بسبب طريقة معالجة الاستعلامات المنطقية، يمكن ترتيب نتائج الاستعلامات حسب الكُنى (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

سيكون الخرج:

DisplayName JoinDate Reputation
Community 2008-09-15 1
Jeff Atwood 2008-09-16 25784
Joel Spolsky 2008-09-16 37628
Jarrod Dixon 2008-10-03 11739
Geoff Dalgas 2008-10-03 12567

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

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

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

Name Age City
Bob 10 Paris
Mat 20 Berlin
Mary 24 Prague

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

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

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

Name
Mary

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

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

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

Name
Bob
Mary

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





تفاعل الأعضاء


لا توجد أيّة تعليقات بعد



يجب أن تكون عضوًا لدينا لتتمكّن من التعليق

انشاء حساب جديد

يستغرق التسجيل بضع ثوان فقط


سجّل حسابًا جديدًا

تسجيل الدخول

تملك حسابا مسجّلا بالفعل؟


سجّل دخولك الآن