التجميع وGROUP BY في SQL

لماذا يهم

كثير من عيوب البيانات لا يُرى صفاً بصف: طلب يختلف إجماليه عن أسطره، أو استيراد حمّل ⁦9998⁩ صفاً من ⁦10000⁩، أو تقرير يحتسب الطلبات الملغاة. كل صف يبدو معقولاً، ولا يظهر العيب إلا حين تُعدّ مجموعة من الصفوف أو تُجمع ثم تُقارن بقيمة متوقعة.

واستعلام تجميع واحد يفحص تقريراً أو دفعة فواتير أو عملية ترحيل لا يستطيع أحد قراءتها صفاً بصف. والخطر هو الاستعلام الخاطئ: قيمة NULL متجاهَلة، أو ربط يكرّر الصفوف، أو شرط في الموضع الخطأ، فيعطي نجاحاً كاذباً أو إنذاراً كاذباً.

دوال التجميع: COUNT وSUM وAVG وMIN وMAX

تعيد دالة التجميع قيمة واحدة لمجموعة من الصفوف. ودون GROUP BY تشكّل كل الصفوف التي تجتاز WHERE مجموعة واحدة، فيعيد الاستعلام صفاً واحداً بالضبط حتى لو لم يطابق شيء. ويعرض مقطع الشيفرة جدول orders ذا الصفوف الستة المستخدم في هذا الدرس كله.

وأهم تفصيل للاختبار هو NULL: كل دوال التجميع ما عدا COUNT(*) تتجاهله. ثلاثة طلبات بلا تقييم، لذا COUNT(rating) يساوي ⁦3⁩، وAVG(rating) يساوي ⁦12 / 3 = 4⁩ لا ⁦12 / 6 = 2⁩. وإن كانت قاعدة العمل تعدّ القيمة الغائبة صفراً فاكتب AVG(COALESCE(rating, 0)) ودوّن ذلك في حالة الاختبار.

• COUNT(*) يعدّ الصفوف، وCOUNT(column) يعدّ القيم التي ليست NULL، وCOUNT(DISTINCT column) يعدّ القيم المختلفة التي ليست NULL.
• SUM على صفر صفوف، أو على قيم NULL فقط، يعيد NULL لا صفراً: قارن COALESCE(SUM(amount), 0).
• AVG في SQL Server يعيد عدداً صحيحاً لعمود من الأعداد الصحيحة؛ حوّل القيمة إلى نوع عشري أولاً.
• MIN وMAX يعملان على الأرقام والتواريخ والنصوص، فهما فحص سريع للنطاق مثل أقدم created_at في عملية استيراد.

-- orders: the example table used in this lesson
-- order_id  customer_id  status     total_amount  rating
-- 1001      7            PAID             120.00       5
-- 1002      7            PAID              80.00    NULL
-- 1003      9            CANCELLED         50.00    NULL
-- 1004      9            PAID             200.00       4
-- 1005      12           PENDING           35.00    NULL
-- 1006      12           PAID              15.00       3

SELECT COUNT(*)                    AS all_rows,       -- 6
       COUNT(rating)               AS rated,          -- 3
       COUNT(DISTINCT customer_id) AS customers,      -- 3
       SUM(total_amount)           AS amount,         -- 500.00
       AVG(rating)                 AS avg_rating,     -- 4 = 12 / 3, not 12 / 6
       MIN(total_amount)           AS smallest,       -- 15.00
       MAX(total_amount)           AS largest         -- 200.00
FROM orders;

جرّبها: مولّد جمل SQL INSERT جرّبها: مولّد مجموعات بيانات SQL

GROUP BY: صف واحد في الناتج لكل مجموعة

يضع GROUP BY الصفوف التي تتشارك القيمة نفسها في أعمدة التجميع في مجموعة واحدة، ويحسب كل دالة تجميع مرة لكل مجموعة. فـGROUP BY status يحوّل الطلبات الستة إلى PAID (أربعة صفوف) وCANCELLED (صف واحد) وPENDING (صف واحد)، والتجميع حسب customer_id, status يكوّن مجموعة لكل تركيبة.

كل عمود في قائمة SELECT يجب أن يكون مجمَّعاً أو داخل دالة تجميع. فـSELECT status, order_id, COUNT(*) … GROUP BY status يطلب قيمة order_id واحدة بينما في PAID أربع قيم. يرفضه PostgreSQL وSQL Server، وكذلك MySQL في وضعه الافتراضي ONLY_FULL_GROUP_BY؛ أما SQLite فيعيد قيمة من صف غير محدد، فقد يبدو الفحص صحيحاً بالمصادفة.

• قيمة NULL في عمود التجميع تكوّن مجموعة مستقلة، وهي غالباً ملاحظة بحد ذاتها.
• لا ترتيب مضموناً للمجموعات: أضف ORDER BY قبل المقارنة بتقرير.
• يجب أن تساوي مجاميع المجموعات المجموع الكلي: ⁦50.00 + 415.00 + 35.00 = 500.00⁩ يُثبت أنه لم يُفقد صف ولم يُحسب صف مرتين.

SELECT status,
       COUNT(*)          AS orders,
       SUM(total_amount) AS amount
FROM orders
GROUP BY status
ORDER BY status;

-- status     orders  amount
-- CANCELLED       1   50.00
-- PAID            4  415.00
-- PENDING         1   35.00

جرّبها: مدقّق استعلامات SQL جرّبها: منسّق SQL

WHERE يرشّح الصفوف وHAVING يرشّح المجموعات

يُقيَّم الاستعلام المجمَّع بترتيب منطقي: FROM وJOIN، ثم WHERE، ثم GROUP BY، ثم دوال التجميع، ثم HAVING، ثم SELECT، ثم ORDER BY. قد ينفّذه المحرك بطريقة أخرى، لكن النتيجة دائماً كأنه اتبع هذا الترتيب. ولهذا لا يستطيع WHERE استخدام دالة تجميع، إذ لم توجد المجموعات بعد، ويستطيع HAVING ذلك.

ضع الشروط على الصفوف المفردة، مثل الحالة أو نطاق تاريخ، في WHERE، والشروط على نتائج المجموعات، مثل COUNT(*) > 1، في HAVING. يجد الاستعلام أدناه العملاء الذين لهم أكثر من طلب مدفوع: العميل ⁦7⁩ وحده، بطلبين و⁦200.00⁩. احذف سطر WHERE فيظهر العميلان ⁦9⁩ و⁦12⁩ أيضاً، لأن طلباتهما الملغاة والمعلّقة صارت تُحتسب.

وبالشكل نفسه يُكتب فحص التكرار المعتاد: SELECT email, COUNT(*) FROM customers GROUP BY email HAVING COUNT(*) > 1 يعرض كل عنوان بريد إلكتروني مسجَّل لأكثر من عميل.

-- Customers with more than one PAID order
SELECT customer_id,
       COUNT(*)          AS paid_orders,
       SUM(total_amount) AS paid_amount
FROM orders
WHERE status = 'PAID'      -- row filter, before grouping
GROUP BY customer_id
HAVING COUNT(*) > 1;       -- group filter, after aggregation

-- customer_id  paid_orders  paid_amount
-- 7                      2       200.00

فحوص المطابقة: رقمان يجب أن يتفقا

المطابقة (Reconciliation) تحسب العدد أو المجموع نفسه في مكانين وتقارنهما. والمكان الثاني هو مرجع الاختبار (Test Oracle)، أي مصدر النتيجة المتوقعة: رأس المستند مقابل أسطره، والنظام المصدر مقابل النظام الهدف بعد استيراد، وقاعدة البيانات مقابل تقرير أو استجابة API.

اكتب الفحص بحيث تعيد قاعدة البيانات السليمة صفر صفوف: جمّع حسب المفتاح المشترك واستبقِ بـ HAVING المجموعات المختلفة فقط. وكل صف يعود دليل لتقرير خطأ. وقارن لكل مجموعة لا في الإجمالي الكلي وحده، حيث قد يلغي خطآن متعاكسان أحدهما الآخر.

• تضاعف الصفوف بالربط (Join Fan-out): ربط الطلبات بأسطرها يكرّر كل طلب بعدد أسطره، فيضخّم SUM(o.total_amount) الإجمالي بعد الربط. اجمع الأسطر لكل طلب كما يفعل مقطع الشيفرة.
• الصفوف الغائبة: الطلب الذي لا أسطر له يختفي من الربط الداخلي (INNER JOIN). ويُبقيه LEFT JOIN، لكن مجموعه NULL والمقارنة total_amount <> NULL لا تكون صحيحة أبداً، فيستبعده HAVING رغم ذلك؛ أما COALESCE فيحوّل NULL إلى صفر فيظهر الاختلاف.

-- Rule: an order's total_amount equals the sum of its item lines
SELECT o.order_id,
       o.total_amount,
       COALESCE(SUM(oi.quantity * oi.unit_price), 0) AS lines_total
FROM orders o
LEFT JOIN order_items oi ON oi.order_id = o.order_id
GROUP BY o.order_id, o.total_amount
HAVING o.total_amount <> COALESCE(SUM(oi.quantity * oi.unit_price), 0);

-- Zero rows: the rule holds. Every row returned is a mismatch to report.

مثال تطبيقي: مطابقة تقرير المبيعات اليومي

المتطلب: «يعرض تقرير المبيعات اليومي لكل يوم عدد الطلبات المدفوعة (PAID) ومجموع مبالغها بتوقيت الرياض». ويُخزَّن created_at بتوقيت الرياض، فتجمّع الطلبات المدفوعة حسب التاريخ للفترة من ⁦1⁩ إلى ⁦3⁩ سبتمبر وتقارن كل يوم بالتقرير. ويحذف CAST(created_at AS DATE) الوقت من الطابع الزمني، فتقع كل طلبات التاريخ الواحد في مجموعة واحدة.

يتطابق يومان ويختلف يوم ⁦2⁩ سبتمبر. واختلاف العدد والمبلغ معاً يوحي بصفوف زائدة لا بمبالغ خاطئة، فتعمّق في ذلك اليوم بتجميع طلباته حسب كل الحالات.

• قاعدة البيانات: ⁦38⁩ طلباً و⁦5720.50⁩. التقرير: ⁦40⁩ طلباً و⁦5905.50⁩. الفرق: طلبان و⁦185.00⁩.
• حسب الحالة: في CANCELLED طلبان و⁦185.00⁩، وهو الفرق بالضبط. وتطابق الأرقام قرينة قوية لا دليل قاطع: إن كان التقرير يعرض قائمة طلباته فتأكد من أنها تضم هذين الطلبين.
• تقرير الخطأ: يبدو أن المبيعات اليومية تحتسب طلبات أُلغيت بعد دفعها. أرفق الاستعلامين ونتيجتيهما، واسأل مالك المنتج هل تدخل هذه الطلبات في مبيعات اليوم، فالمتطلب لا يذكر ذلك.

-- 1. Expected values: PAID orders per day, 1 to 3 September
--    created_at holds Riyadh local time; in SQLite write date(created_at)
SELECT CAST(created_at AS DATE) AS sales_day,
       COUNT(*)                 AS paid_orders,
       SUM(total_amount)        AS paid_amount
FROM orders
WHERE status = 'PAID'
  AND created_at >= '2026-09-01' AND created_at < '2026-09-04'
GROUP BY CAST(created_at AS DATE)
ORDER BY sales_day;

-- sales_day   database        report          result
-- 2026-09-01  41  6150.00     41  6150.00     match
-- 2026-09-02  38  5720.50     40  5905.50     mismatch
-- 2026-09-03  44  6480.00     44  6480.00     match

-- 2. Drill down into 2 September, every status
SELECT status, COUNT(*) AS orders, SUM(total_amount) AS amount
FROM orders
WHERE created_at >= '2026-09-02' AND created_at < '2026-09-03'
GROUP BY status
ORDER BY status;

-- CANCELLED   2   185.00    <- exactly the difference
-- PAID       38  5720.50
-- PENDING     3   240.00

جرّبها: مولّد قوائم SQL VALUES جرّبها: محوّل CSV إلى SQL

أسئلة شائعة

المراجع

• PostgreSQL Documentation: SQL SyntaxThe PostgreSQL Global Development Groupالبنية المعجمية لـ SQL في PostgreSQL: المعرّفات والاقتباس والثوابت النصية والتهريب.
• SQL As Understood By SQLiteSQLiteقواعد SQL التي يقبلها SQLite، عبارة تلو الأخرى، مع مخططات نحوية.
• ISO/IEC 9075-1:2023 — Information technology — Database languages SQL — Part 1: Framework (SQL/Framework)ISO/IEC · 2023الجزء الإطاري من معيار SQL الدولي، الذي تنفّذه لهجات PostgreSQL وMySQL وSQLite وغيرها بدرجات متفاوتة.
• ISTQB GlossaryISTQBالتعريفات القياسية لمصطلحات الاختبار — حالة الاختبار واختبار الانحدار والخطورة والأولوية وتحليل القيم الحدّية وغيرها.