0Pricing
SQL Interview Prep · درس

صياغة PIVOT وCrosstab الخاصة بالمورّد

‏PIVOT في SQL Server وcrosstab في Postgres، وحدودهما

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

ما بعد التجميع الشرطي

أنت تعرف بالفعل أسلوب التدوير المحمول باستخدام CASE. لكن المحاوِرين يريدون أيضًا معرفة ما إذا كنت تستطيع استخدام عوامل التدوير الخاصة بالمورّد عند توفرها.

يوفّر SQL Server عاملًا مخصصًا هو PIVOT. ويقدّم PostgreSQL الدالة crosstab ضمن الامتداد tablefunc. إن معرفة كليهما، ومعرفة جوانبهما الدقيقة، تدل على خبرة عملية حقيقية.

بنية PIVOT في SQL Server

يتطلب PIVOT في SQL Server ثلاثة أشياء:

  • دالة تجميع تُطبَّق على عمود القيمة.
  • عبارة FOR تسمّي العمود الذي ستصبح قيمه أعمدة جديدة.
  • قائمة IN بالقيم الحرفية التي ستتحول إلى أعمدة.

يجب تطبيقه على جدول مشتق يعرض المفتاح وعمود التوزيع والقيمة فقط، دون أي أعمدة أخرى.

SELECT region, [Q1], [Q2]
FROM (SELECT region, quarter, amount FROM sales) AS src
PIVOT (
  SUM(amount)
  FOR quarter IN ([Q1], [Q2])
) AS p;

GROUP BY الضمني

من التفاصيل الدقيقة في PIVOT التي يختبرها المحاوِرون أن التجميع يكون ضمنيًا. إذ يجمع SQL Server حسب كل عمود في المصدر لا يكون عمود التجميع أو عمود FOR.

لذلك، إذا احتوى جدولك المشتق بالخطأ على عمود إضافي مثل order_id، فسيُجري التدوير التجميع حسبه أيضًا، وستحصل على صفوف أكثر بكثير من المتوقع. اختصر الاستعلام الداخلي دائمًا ليقتصر على المفتاح والتوزيع والقيمة.

-- WRONG: order_id leaks in and breaks grouping
FROM (SELECT region, quarter, amount, order_id FROM sales) AS src
PIVOT (SUM(amount) FOR quarter IN ([Q1],[Q2])) AS p;
-- The pivot now groups by region AND order_id

أسماء الأعمدة بين قوسين مربعين

في SQL Server، تكون أسماء الأعمدة التي جرى تدويرها هي القيم الحرفية من البيانات، وتُحاط بأقواس مربعة. وإذا بدأت قيمة ما برقم أو احتوت على مسافات، تصبح الأقواس إلزامية.

تحدّد هذه الأعمدة بالاسم نفسه المحاط بالأقواس المربعة في عبارة SELECT الخارجية. ولهذا السبب أيضًا لا يستطيع PIVOT التعامل مع القيم غير المعروفة دون SQL ديناميكي؛ فقائمة IN مكتوبة بشكل ثابت.

SELECT region, [2023], [2024]
FROM (SELECT region, yr, amount FROM sales) AS s
PIVOT (SUM(amount) FOR yr IN ([2023], [2024])) AS p;

crosstab في PostgreSQL

لا يحتوي PostgreSQL على كلمة PIVOT المفتاحية. وبدلًا من ذلك، يوفّر الامتداد tablefunc الدالة crosstab التي تستقبل سلسلة SQL وتعيد تشكيل ناتجها.

يجب تفعيل الامتداد أولًا. وتتوقع crosstab أن يعيد الاستعلام المصدر ثلاثة أعمدة بالضبط: معرّف الصف، والفئة، والقيمة، بهذا الترتيب.

CREATE EXTENSION IF NOT EXISTS tablefunc;

SELECT *
FROM crosstab(
  'SELECT region, quarter, amount FROM sales ORDER BY 1, 2'
) AS ct(region text, q1 numeric, q2 numeric);

قائمة تعريف الأعمدة

أكثر أجزاء crosstab عرضةً للأخطاء هي قائمة تعريف الأعمدة اللاحقة AS ct(...). يجب أن تعلن بنفسك عن أسماء أعمدة الإخراج وأنواعها، ويجب أن تطابق عدد الفئات وترتيبها.

إذا غابت فئة ما عن أحد الصفوف، فستملأها crosstab حسب الموضع، مما قد يؤدي إلى عدم تطابق البيانات ما لم تستخدم الصيغة ذات الوسيطتين أدناه.

SELECT *
FROM crosstab(
  'SELECT region, quarter, amount FROM sales ORDER BY 1, 2'
) AS ct(region text, q1 numeric, q2 numeric);
-- ct(...) MUST list every output column and its type

crosstab ذو الوسيطتين

لتجنب عدم التطابق عندما تفتقد بعض الصفوف بعض الفئات، استخدم الصيغة ذات الوسيطتين. يعيد الاستعلام الثاني القائمة الكاملة والمرتبة لقيم الفئات، وبذلك تعرف crosstab بالضبط العمود الذي تنتمي إليه كل قيمة.

هذه هي الصيغة المتينة التي يتوقعها المحاوِرون عند التعامل مع فئات متفرقة.

SELECT *
FROM crosstab(
  'SELECT region, quarter, amount FROM sales ORDER BY 1, 2',
  'SELECT DISTINCT quarter FROM sales ORDER BY 1'
) AS ct(region text, q1 numeric, q2 numeric);

لا يتوفر أيٌّ منهما في MySQL

إذا سألك المحاوِر عن MySQL، فالإجابة مباشرة: لا يحتوي MySQL على PIVOT ولا على crosstab. والخيار الوحيد فيه هو التجميع الشرطي باستخدام CASE (أو الاختصار SUM(... ) + IF()).

ولهذا السبب تحديدًا يحظى نمط CASE القابل للنقل بهذه القيمة: فهو القاسم المشترك الأدنى الذي يعمل في كل مكان.

-- MySQL: only conditional aggregation works
SELECT
  region,
  SUM(IF(quarter = 'Q1', amount, 0)) AS q1,
  SUM(IF(quarter = 'Q2', amount, 0)) AS q2
FROM sales
GROUP BY region;

مثال تطبيقي: عدّ الحالات في SQL Server

طلب شائع في إعداد التقارير: «صف واحد لكل منطقة، مع عمود يعدّ الطلبات في كل حالة». في SQL Server، مرّر جدولًا مشتقًا ومختصرًا إلى PIVOT باستخدام COUNT.

نظرًا إلى أنك تعدّ عمود الحالة نفسه، فسيُحتسب كل صف ذي حالة غير NULL داخل المجموعة. وتعرض عبارة SELECT الخارجية كل حالة بوصفها عمودًا بين أقواس مربعة. وهذه صيغة موجزة بدلًا من كتابة ثلاثة تعبيرات من نوع COUNT(CASE ...).

SELECT region, [pending], [shipped], [delivered]
FROM (SELECT region, status FROM orders) AS src
PIVOT (
  COUNT(status)
  FOR status IN ([pending], [shipped], [delivered])
) AS p;

القيود المشتركة

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

  • SQL Server: قائمة IN حرفية.
  • crosstab في Postgres: قائمة تعريف الأعمدة حرفية.

لا يستطيع أيٌّ منهما اكتشاف الفئات أثناء التشغيل. ويتطلب ذلك إنشاء سلسلة SQL ديناميكيًا.

أيّها ينبغي أن تستخدم؟

تقارن الإجابة الجيدة في المقابلة بينها بصدق:

  • تجميع CASE: قابل للنقل، واضح، ويعمل في كل محرك. وهو الخيار الافتراضي.
  • PIVOT في SQL Server: موجز عند التعامل مع أعمدة كثيرة، لكن التجميع الضمني قد يفاجئ البعض.
  • crosstab في Postgres: قوي لكنه مطوّل، ويتطلب امتدادًا وقائمة تعريف أعمدة.

عند الشك، استخدم التجميع الشرطي واذكر عوامل المورّد بوصفها بدائل.

اختبار سريع

حدّد سلوك SQL Server PIVOT الذي يختبره المحاوِرون.

مراجعة

صياغة التدوير الخاصة بالمورّد في شاشة واحدة:

  • SQL Server: PIVOT (SUM(x) FOR col IN ([a],[b]))، مع GROUP BY ضمني على الأعمدة المتبقية.
  • Postgres: crosstab() من tablefunc، ويتطلب قائمة تعريف أعمدة؛ استخدم الصيغة ذات الوسيطتين للبيانات المتفرقة.
  • MySQL: لا يتوفر أيٌّ منهما، فاستخدم CASE.
  • تتطلب الأنظمة الثلاثة معرفة الأعمدة وقت كتابة الاستعلام.

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

هل درس «صياغة PIVOT وCrosstab الخاصة بالمورّد» مجاني؟

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

ماذا ستتعلم في «صياغة PIVOT وCrosstab الخاصة بالمورّد»؟

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

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

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

كم من الوقت يستغرق درس «صياغة PIVOT وCrosstab الخاصة بالمورّد»؟

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

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

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

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

  1. إجراء Pivot باستخدام التجميع الشرطي
  2. صياغة PIVOT وCrosstab الخاصة بالمورّد
  3. تحويل الأعمدة إلى صفوف
  4. عمليات Pivot ديناميكية بأعمدة غير معروفة
← العودة إلى SQL Interview Prep