SQL Interview Prep · درس

عمليات Pivot ديناميكية بأعمدة غير معروفة

إنشاء أعمدة Pivot عندما لا تكون الفئات معروفة مسبقًا

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

السؤال الصعب حول Pivot

يشترك كل Pivot ثابت، سواء أكان تجميعًا باستخدام CASE أم PIVOT في SQL Server أم crosstab في Postgres، في قيد واحد: يجب أن تسرد أعمدة الناتج عند كتابة الاستعلام.

لكن ماذا تفعل إذا كانت الفئات غير معروفة، مثل أسماء المنتجات التي تتغير أسبوعيًا أو وجود عمود واحد لكل شهر نشط؟ هذا هو Pivot الديناميكي، وهو سؤال مقابلات للمستوى المتقدم لأن SQL العادي لا يستطيع إرجاع نتيجة تُحدَّد قائمة أعمدتها وقت التشغيل.

لماذا لا يستطيع SQL وحده تنفيذ ذلك

يستخدم SQL أنواعًا ثابتة على مستوى مجموعة النتائج؛ إذ يجب أن يعرف المخطط الأعمدة وأنواعها قبل التنفيذ. ولا يستطيع استعلام واحد أن يقول أنشئ عمودًا لكل قيمة تعثر عليها.

لذلك تتمثل التقنية العامة في إنشاء نص SQL على خطوتين: استعلم أولًا عن الفئات المميزة، ثم أنشئ منها سلسلة استعلام Pivot ونفّذ تلك السلسلة.

الخطوة 1: جمع الفئات

الخطوة الأولى هي استعلام عادي يسرد القيم المميزة التي ستصبح أعمدة. وعادةً ما ترتب هذه القيم للحفاظ على تخطيط ثابت للأعمدة.

تُمرَّر هذه النتيجة إلى خطوة إنشاء السلسلة النصية. في نظام حقيقي، تنفذ هذا الاستعلام، وتلتقط الصفوف، ثم تركّب الاستعلام التالي منها.

SELECT DISTINCT quarter
FROM sales
ORDER BY quarter;
-- e.g. Q1, Q2, Q3, Q4

الخطوة 2: إنشاء قائمة الأعمدة

بعد ذلك، حوّل تلك القيم إلى قائمة مفصولة بفواصل من تعبيرات CASE (أو أسماء محاطة بأقواس مربعة في PIVOT). توفر قواعد البيانات دوال لتجميع السلاسل النصية وتنفيذ ذلك داخل SQL نفسه.

في Postgres تكون الدالة string_agg، وفي MySQL GROUP_CONCAT، وفي SQL Server STRING_AGG أو الحيلة الأقدم باستخدام FOR XML PATH.

-- Postgres: build the SELECT-list fragment
SELECT string_agg(
  format('SUM(CASE WHEN quarter = %L THEN amount END) AS %I',
         quarter, quarter),
  ', '
)
FROM (SELECT DISTINCT quarter FROM sales ORDER BY 1) q;

الخطوة 3: التركيب والتنفيذ

ادمج الجزء المُنشأ في سلسلة استعلام كاملة، ثم شغّلها باستخدام التنفيذ الديناميكي: EXECUTE في PL/pgSQL، أو sp_executesql في SQL Server، أو PREPARE/EXECUTE في MySQL.

هذا هو جوهر Pivot الديناميكي: يكتب SQL لغة SQL ثم ينفذها.

-- SQL Server pattern
DECLARE @cols NVARCHAR(MAX), @sql NVARCHAR(MAX);
SELECT @cols = STRING_AGG(QUOTENAME(quarter), ',')
FROM (SELECT DISTINCT quarter FROM sales) q;
SET @sql = N'SELECT region, ' + @cols + '
  FROM (SELECT region, quarter, amount FROM sales) s
  PIVOT (SUM(amount) FOR quarter IN (' + @cols + ')) p;';
EXEC sp_executesql @sql;

مثال كامل في PostgreSQL

في Postgres، تغلّف الخطوات الثلاث داخل كتلة DO أو دالة. أنشئ قائمة الأعمدة باستخدام string_agg، وأدرجها في الاستعلام، ثم شغّله باستخدام EXECUTE.

نظرًا إلى أن أعمدة النتيجة غير معروفة حتى وقت التشغيل، تستخدم الدالة التي تُرجع هذه النتيجة غالبًا RETURNS SETOF record، أو تُرجع الصفوف بصيغة json ثم يوسّعها المستدعي.

DO $do$
DECLARE
  cols text;
  qry  text;
BEGIN
  SELECT string_agg(
    format('SUM(CASE WHEN quarter=%L THEN amount END) AS %I', quarter, quarter), ', ')
  INTO cols
  FROM (SELECT DISTINCT quarter FROM sales ORDER BY 1) q;
  qry := format('SELECT region, %s FROM sales GROUP BY region', cols);
  EXECUTE qry;
END $do$;

MySQL باستخدام العبارات المُحضَّرة

لا يحتوي MySQL على معامل Pivot، لذلك تنشئ عمليات Pivot الديناميكية سلسلة للتجميع الشرطي باستخدام GROUP_CONCAT، ثم تشغّلها عبر عبارة مُحضَّرة.

لدى GROUP_CONCAT حدّ لطول الناتج هو group_concat_max_len، وقد يذكر المحاوِر هذا الحد؛ فارفعه إذا كان لديك عدد كبير من الفئات.

SET @sql = NULL;
SELECT GROUP_CONCAT(DISTINCT
  CONCAT('SUM(CASE WHEN quarter=''', quarter,
         ''' THEN amount END) AS ', QUOTE(quarter))
) INTO @sql FROM sales;
SET @sql = CONCAT('SELECT region, ', @sql,
                  ' FROM sales GROUP BY region');
PREPARE st FROM @sql; EXECUTE st; DEALLOCATE PREPARE st;

خطر حقن SQL

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

احرص دائمًا على إلغاء تهريب المعرّفات والقيم الحرفية باستخدام الأدوات الآمنة التي يوفرها المحرك: format('%I', ...) و%L في Postgres، وQUOTENAME في SQL Server. لا تلصق القيم الخام مباشرةً في السلسلة النصية.

-- Safe quoting prevents injection / breakage
-- Postgres: %I identifier, %L literal
format('SUM(CASE WHEN k=%L THEN v END) AS %I', cat, cat)
-- SQL Server: QUOTENAME(cat)

إرجاع أعمدة غير معروفة

توجد صعوبة ثانية: لا يستطيع المستدعي معرفة شكل النتيجة مسبقًا. ومن الاستراتيجيات الشائعة التي يقبلها المحاوِرون:

إرجاع الصفوف بصيغة JSON وترك طبقة التطبيق توسّع المفاتيح.
جعل الإجراء يطبع الاستعلام أو ينشئه، ثم تشغيله كخطوة ثانية.
تنفيذ Pivot النهائي في كود التطبيق (pandas أو أداة BI) بعد معرفة الفئات.

لا توجد طريقة نظيفة لإرجاع أعمدة اعتباطية من استدعاء ثابت واحد.

مثال تطبيقي: Pivot حسب المنتج

افترض أن المنتجات تظهر وتختفي، وأن التقرير يحتاج إلى عمود إيرادات واحد لكل منتج موجود حاليًا في sales. لا يمكنك تثبيت القائمة مسبقًا، لذا تنشئها. يجعل Postgres الأمر واضحًا: أنشئ جزء CASE باستخدام string_agg والاقتباس الآمن، وأدرجه في استعلام، ثم نفّذه باستخدام EXECUTE.

اشرح للمحاوِر خطوات التنفيذ: اكتشاف المنتجات، وتحويل كل منتج إلى عمود مقتبس، ثم التركيب والتنفيذ. ينطبق النمط نفسه على أي محرك؛ ولا تختلف إلا الأدوات المساعدة.

DO $do$
DECLARE cols text; qry text;
BEGIN
  SELECT string_agg(
    format('SUM(CASE WHEN product=%L THEN amount END) AS %I',
           product, product), ', ')
  INTO cols
  FROM (SELECT DISTINCT product FROM sales ORDER BY 1) p;
  qry := format('SELECT region, %s FROM sales GROUP BY region', cols);
  EXECUTE qry;
END $do$;

متى تتجنب Pivot الديناميكي

يعرف المرشحون المتمكنون متى لا ينفذون ذلك في SQL. فـSQL الديناميكي أصعب في القراءة والاختبار والتأمين والتخزين المؤقت. وغالبًا ما تكون الإجابة الأفضل:

إرجاع البيانات بصيغة طويلة من SQL وتنفيذ Pivot في التطبيق أو طبقة التقارير.
إذا كانت مجموعة الفئات صغيرة وبطيئة التغيّر، فاستخدم Pivot ثابتًا وحدّثه من حين إلى آخر.

اقتصر على عمليات Pivot الديناميكية عندما تكون مجموعة الفئات مفتوحة فعلًا ومتغيرة باستمرار.

تحقق سريع

اختبر فهمك للسبب الأساسي لوجود عمليات Pivot الديناميكية.

مراجعة

تتعامل عمليات Pivot الديناميكية مع مجموعات الأعمدة غير المعروفة:

تفشل عمليات Pivot الثابتة لأن أعمدة النتيجة يجب أن تكون محددة قبل التنفيذ.
النمط هو: الاستعلام عن الفئات المميزة، وإنشاء سلسلة SQL لـPivot، ثم تنفيذها ديناميكيًا.
استخدم string_agg/GROUP_CONCAT/STRING_AGG لإنشاء قائمة الأعمدة.
ألغِ تهريب القيم باستخدام (%I/%L وQUOTENAME) لتجنب حقن SQL.
غالبًا ما يكون إرجاع البيانات بصيغة طويلة وتنفيذ Pivot في طبقة التطبيق أنظف.

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

هل درس «عمليات Pivot ديناميكية بأعمدة غير معروفة» مجاني؟

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

ماذا ستتعلم في «عمليات Pivot ديناميكية بأعمدة غير معروفة»؟

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

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

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

كم من الوقت يستغرق درس «عمليات Pivot ديناميكية بأعمدة غير معروفة»؟

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

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

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

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

إجراء Pivot باستخدام التجميع الشرطي
صياغة PIVOT وCrosstab الخاصة بالمورّد
تحويل الأعمدة إلى صفوف
عمليات Pivot ديناميكية بأعمدة غير معروفة

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