SQL Interview Prep · درس

تجميعات كل مجموعة دون GROUP BY

استخدام استعلام فرعي مترابط لحساب أكبر قيمة للمجموعة إلى جانب صفوف التفاصيل

تجميعات كل مجموعة دون GROUP BY درس مجاني في SQL Interview Prep على CoddyKit. هذا هو الدرس 2 من أصل 4. يمكنك قراءة الدرس كاملاً أدناه مجاناً — ثم تمرن عليه مباشرة في المتصفح باستخدام محرر أكواد مدمج ومدرس ذكاء اصطناعي متاح 24/7. هذا الدرس جزء من مسار التعلم في SQL Interview Prep، وتقدمك يتزامن عبر الويب وتطبيق CoddyKit. تتضمن دورة SQL Interview Prep 4 دروس في المجموع.

مشكلة التفاصيل مع التجميع

طلب شائع في المقابلات: "اعرض كل صف إلى جانب قيمة تجميعية لمجموعته." فمثلًا، اعرض كل موظف مع أعلى راتب في قسمه في السطر نفسه.

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

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

لماذا يفشل GROUP BY العادي هنا

إذا كتبت SELECT dept_id, MAX(salary) FROM employees GROUP BY dept_id، فستحصل على صف واحد لكل قسم، وتفقد أسماء الموظفين الفردية.

إضافة name إلى SELECT من دون إضافته إلى GROUP BY تؤدي إلى الخطأ التقليدي "يجب أن يظهر العمود في GROUP BY".

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

الاستعلام الفرعي المترابط هو الحل

ضع تجميع المجموعة في قائمة SELECT على هيئة استعلام فرعي مترابط. إذ يؤدي كل صف موظف إلى تشغيل MAX داخلي مقيّد بقسم ذلك الموظف.

يربط الترابط e2.dept_id = e1.dept_id التجميع بالمجموعة الصحيحة، بينما يواصل الاستعلام الخارجي إعادة صف واحد لكل موظف.

SELECT e1.name,
       e1.dept_id,
       e1.salary,
       (SELECT MAX(e2.salary)
        FROM employees e2
        WHERE e2.dept_id = e1.dept_id) AS dept_max_salary
FROM employees e1;

مقارنة كل صف بمجموعته

بعد وضع تجميع المجموعة في الاستعلام، يمكنك مقارنة كل صف به. ومن الأسئلة الشائعة: "اعثر على الموظفين الذين يتقاضون أكثر من متوسط رواتب أقسامهم."

يوجد AVG المترابط هنا في WHERE، لذلك يُختبر كل موظف مقابل متوسط قسمه هو.

SELECT e1.name, e1.dept_id, e1.salary
FROM employees e1
WHERE e1.salary > (
    SELECT AVG(e2.salary)
    FROM employees e2
    WHERE e2.dept_id = e1.dept_id
);

حساب الفرق عن المجموعة

يمكنك أيضًا عرض بُعد كل صف عن تجميع مجموعته. ويؤدي طرح المتوسط المترابط إلى الحصول على فرق خاص بكل صف.

لاحظ أنه يمكن إعادة استخدام الاستعلام الفرعي المترابط نفسه في عدة تعبيرات ضمن SELECT؛ إذ يقيّمه المحرّك لكل صف في كل موضع يظهر فيه.

SELECT e1.name,
       e1.salary,
       e1.salary - (SELECT AVG(e2.salary)
                    FROM employees e2
                    WHERE e2.dept_id = e1.dept_id) AS gap_from_dept_avg
FROM employees e1;

العثور على صاحب أعلى راتب في كل مجموعة

لإعادة الشخص الأعلى راتبًا في كل قسم فقط، قارن كل راتب بقيمة MAX المترابطة واحتفظ بالصفوف المطابقة.

يعيد هذا النمط جميع حالات التعادل: فإذا اشترك موظفان في أعلى راتب للقسم، فسيظهر كلاهما. وغالبًا ما يكون التعامل مع التعادلات سؤال المتابعة لدى القائم بالمقابلة.

SELECT e1.name, e1.dept_id, e1.salary
FROM employees e1
WHERE e1.salary = (
    SELECT MAX(e2.salary)
    FROM employees e2
    WHERE e2.dept_id = e1.dept_id
);

البديل باستخدام دوال النوافذ

يوفّر SQL الحديث أداة أكثر وضوحًا: دوال النوافذ. تحسب MAX(salary) OVER (PARTITION BY dept_id) تجميع المجموعة من دون اختزال الصفوف ومن دون إعادة فحص مترابطة.

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

SELECT name,
       dept_id,
       salary,
       MAX(salary) OVER (PARTITION BY dept_id) AS dept_max_salary
FROM employees;

المفاضلة بين المترابط ودالة النافذة

يعيد الأسلوبان الشكل نفسه للنتائج، لكنهما يختلفان في ما يلي:

الاستعلام الفرعي المترابط: قابل للنقل بين الأنظمة، ويعمل على المحرّكات القديمة جدًا، لكنه يُعاد تقييمه لكل صف.
دالة النافذة: تمريرة واحدة، وأسرع بكثير على الجداول الكبيرة، لكنها تتطلب دعم نوافذ SQL.

اذكر أيهما ستختار ولماذا. بالنسبة إلى عملية لمرة واحدة على جدول صغير، فكلاهما مناسب؛ أما في التحليلات واسعة النطاق، ففضّل دالة النافذة.

مثال تطبيقي: الطلبات الأعلى من متوسط العميل

طبّق النمط على الطلبات. اعرض الطلبات التي تتجاوز قيمتها متوسط قيمة طلبات العميل الذي أجراها.

يُقيَّد AVG المترابط بالشرط o2.customer_id = o1.customer_id، مما يمنح كل طلب خط أساس خاصًا بعميله.

SELECT o1.order_id, o1.customer_id, o1.amount
FROM orders o1
WHERE o1.amount > (
    SELECT AVG(o2.amount)
    FROM orders o2
    WHERE o2.customer_id = o1.customer_id
);

انتبه إلى حالات NULL والمجموعات الفارغة

إذا احتوت المجموعة على صف واحد فقط، فسيكون متوسطها مساويًا لقيمة ذلك الصف، ولذلك يكون salary > avg غير صحيح ويُستبعد الصف. اذكر هذه الحالة الحدية استباقيًا.

كذلك، تتجاهل AVG وMAX الرواتب التي تساوي NULL، بما يتوافق مع دلالات التجميع في SQL. وإذا كانت كل القيم في مجموعة ما تساوي NULL، فسيكون التجميع NULL وتصبح المقارنات UNKNOWN، مما يستبعد الصف. إن توقع هذه الحالات هو ما يميز الإجابة الشاملة.

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

يمكنك التعبير عن ترتيب صف داخل مجموعته باستخدام COUNT مترابط. وللعثور على ترتيب راتب كل موظف داخل قسمه، احسب عدد الزملاء الذين يتقاضون راتبًا أعلى.

يعني الترتيب 1 أعلى راتب. تؤدي إضافة 1 إلى تحويل عدد أصحاب الرواتب الأعلى إلى ترتيب يبدأ من 1، ويحافظ الترابط على تقييد الحساب بالقسم.

SELECT e1.name,
       e1.dept_id,
       e1.salary,
       (SELECT COUNT(*) + 1
        FROM employees e2
        WHERE e2.dept_id = e1.dept_id
          AND e2.salary > e1.salary) AS salary_rank_in_dept
FROM employees e1;

تحقق سريع

اختر السبب الذي يجعل الاستعلام الفرعي المترابط أفضل من GROUP BY العادي في هذه المهمة.

مراجعة: تجميعات كل مجموعة من دون GROUP BY

أهم النقاط:

يضع الاستعلام الفرعي المترابط تجميعًا على مستوى المجموعة في كل صف تفصيلي من دون اختزال الصفوف.
استخدمه في SELECT لعرض التجميع، أو في WHERE لمقارنة كل صف بمجموعته.
يعيد النمط = MAX(...) جميع الصفوف الأعلى المتعادلة.
تنفّذ دالة النافذة مع PARTITION BY المهمة نفسها في تمريرة واحدة، وعادةً ما تتوسع بكفاءة أكبر.

قدّم الحلين وعلّل اختيارك في المقابلة.

الأسئلة الشائعة

هل درس «تجميعات كل مجموعة دون GROUP BY» مجاني؟

نعم — نص درس «تجميعات كل مجموعة دون GROUP BY» كامل متاح مجاناً هنا على الويب. لتمرينه بشكل تفاعلي (محرر أكواد مدمج ومدرس ذكاء اصطناعي متاح 24/7) وفتح باقي دورة SQL Interview Prep، انتقل إلى CoddyKit PRO. تتضمن دورة SQL Interview Prep 4 دروس في المجموع.

ماذا ستتعلم في «تجميعات كل مجموعة دون GROUP BY»؟

استخدام استعلام فرعي مترابط لحساب أكبر قيمة للمجموعة إلى جانب صفوف التفاصيل تتمرن على SQL Interview Prep مع أكواد عملية تشغلها مباشرة في المتصفح، ومدرس ذكاء اصطناعي متاح 24/7 يجيب على أسئلتك أثناء عملك.

هل أحتاج إلى خبرة سابقة لأبدأ SQL Interview Prep؟

لا تُشترط خبرة سابقة. SQL Interview Prep على CoddyKit منظم للمبتدئين حتى المتقدمين، لذا يمكنك البدء من هنا أو من البداية والتقدم بسرعتك الخاصة. هذا هو الدرس 2 من أصل 4.

كم من الوقت يستغرق درس «تجميعات كل مجموعة دون GROUP BY»؟

معظم دروس CoddyKit تستغرق حوالي 5–10 دقائق. كل منها موجز وتفاعلي، لذا تحرز تقدماً مستمراً وتستأنف من حيث توقفت عبر الويب والتطبيق.

هل يمكنني كتابة وتشغيل أكواد في درس SQL Interview Prep هذا؟

نعم. كل درس في SQL Interview Prep يتضمن محرر أكواد مدمج، لذا تكتب وتشغل أكواداً حقيقية مباشرة في متصفحك وتحصل على تعليقات فورية من الذكاء الاصطناعي — بدون إعداد محلي.

جميع الدروس في هذه الدورة

تشريح استعلام فرعي مترابط
تجميعات كل مجموعة دون GROUP BY
EXISTS وNOT EXISTS المترابطان
إعادة كتابة الاستعلامات الفرعية المترابطة كعمليات ربط

← العودة إلى SQL Interview Prep