تدوير صفوف SQL إلى أعمدة: دليل شامل

ماذا يعني تحويل الصفوف إلى أعمدة في SQL؟

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

ضع في اعتبارك المثال التالي: لدي جدول يحتوي على معاملات مبيعات يومية، وتسجل كل صف التاريخ واسم المنتج ومبلغ المبيعات.

من خلال استدارة هذا الجدول، يمكنني إعادة تنظيمه لعرض كل منتج كعمود، مع بيانات المبيعات لكل تاريخ تحت العمود المقابل له. لاحظ أيضًا أن هناك عملية تجميع تحدث.

في العادة، كانت عمليات الدوران تتطلب استعلامات SQL معقدة تحتوي على تجميع شرطي. مع مرور الوقت، تطورت تنفيذات SQL، حيث تضم العديد من قواعد البيانات الحديثة الآن عوامل PIVOT و UNPIVOT للسماح بتحويلات أكثر كفاءة وسهولة.

فهم صفوف الدوران في SQL إلى أعمدة

تحول عملية الدوران في SQL البيانات عن طريق تحويل قيم الصفوف إلى أعمدة. وفيما يلي بنية وصيغة SQL الأساسية للدوران مع الأجزاء التالية:

• SELECT: يشير البيان الـ SELECT إلى الأعمدة المراد استرجاعها في جدول الدوران في SQL.

• الاستعلام الفرعي: يحتوي الاستعلام الفرعي على مصدر البيانات أو الجدول الذي يجب تضمينه في جدول الـ SQL pivot.

• المحور: يحتوي عامل PIVOT على التجميعات والتصفية التي يتم تطبيقها في جدول المحور.

-- حدد الأعمدة الثابتة والأعمدة المحورية
SELECT <static columns>, [pivoted columns]
FROM
    (
        -- استعلام فرعي يحدد بيانات المصدر للمحور
        <subquery that defines data>
    ) AS source
PIVOT
(
    -- وظيفة تجميع تُطبق على عمود القيمة، مما ينشئ أعمدة جديدة
    <aggregation function>(<value column>)
    FOR <column to pivot> IN ([list of pivoted columns])
) AS pivot_table;

دعونا نلقي نظرة على المثال التوضيحي خطوة بخطوة التالي لتوضيح كيفية تحويل الصفوف إلى أعمدة في SQL. اعتبر جدول SalesData أدناه.

مثال على جدول لتحويله باستخدام عامل SQL PIVOT. صورة بواسطة المؤلف.

أريد تحويل هذه البيانات لمقارنة مبيعات كل منتج يوميًا. سأبدأ بتحديد الاستعلام الفرعي الذي سينظم عامل PIVOT.

-- الاستعلام الفرعي الذي يحدد بيانات المصدر للتحويل
SELECT Date, Product, Sales
FROM SalesData;

الآن، سأستخدم عامل PIVOT لتحويل قيم المنتج إلى أعمدة وتجميع المبيعات باستخدام عامل SUM.

-- تحديد التاريخ والأعمدة المدورة لكل منتج
SELECT Date, [Laptop], [Mouse] 
FROM
    (
        -- الاستعلام الفرعي لاسترداد الأعمدة التالية: التاريخ، المنتج، والمبيعات
        SELECT Date, Product, Sales FROM SalesData
    ) AS source
PIVOT
(
    -- تجميع المبيعات حسب المنتج، وتدوير قيم المنتجات إلى أعمدة
    SUM(Sales)
    FOR Product IN ([Laptop], [Mouse])
) AS pivot_table;

مثال على تحويل الإخراج باستخدام تدوير SQL للصفوف إلى أعمدة. صورة بواسطة المؤلف.

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

• أسماء الأعمدة الديناميكية: عندما تكون القيم المراد تحويلها (على سبيل المثال، أنواع المنتجات) غير معروفة، فإن استخدام أسماء الأعمدة الثابتة لن يعمل. بعض قواعد البيانات، مثل SQL Server، تدعم SQL الديناميكي باستخدام الإجراءات المخزنة لتجنب هذه المشكلة، بينما تتطلب قواعد البيانات الأخرى التعامل مع هذا الموضوع على مستوى التطبيق.

• التعامل مع قيم NULL: عندما لا تكون هناك بيانات لعمود معين تم تقويسه، فإن النتيجة قد تتضمن NULL. يمكنك استخدام COALESCE لاستبدال قيم NULL بالصفر أو عنصر بديل آخر.

• التوافق عبر قواعد البيانات: ليس جميع قواعد البيانات تدعم مباشرة عامل PIVOT. يمكنك تحقيق نتائج مماثلة باستخدام عبارات CASE والتجميع الشرطي إذا لم يدعم لهجة SQL الخاصة بك ذلك.

تحويل صفوف SQL إلى أعمدة: أمثلة وحالات استخدام

يتم استخدام طرق مختلفة لتحويل البيانات في SQL، وذلك يعتمد على قاعدة البيانات المستخدمة أو المتطلبات الأخرى. بينما يُستخدم عادة مشغل PIVOT في SQL Server، تتيح تقنيات أخرى، مثل بيانات CASE، عمليات تحويل قاعدة بيانات مماثلة بدون دعم مباشر لـ PIVOT. سأغطي طريقتين شائعتين لتحويل البيانات في SQL، وسأتحدث عن الإيجابيات والسلبيات.

استخدام مشغل PIVOT

يوفر مشغل PIVOT، المتاح في SQL Server، طريقة مباشرة لتحويل الصفوف إلى أعمدة من خلال تحديد وظيفة التجميع وتعريف الأعمدة المراد تحويلها.

النظر في الجدول التالي المسمى sales_data.

جدول الطلبات المثالي للتحويل باستخدام مشغل PIVOT. الصورة من قبل المؤلف.

سأستخدم مشغل PIVOT لتجميع البيانات بحيث يتم عرض إجمالي sales_revenue لكل سنة في أعمدة.

-- استخدم PIVOT لتجميع إيرادات المبيعات حسب السنة
SELECT *
FROM (
    -- حدد الأعمدة المتعلقة من جدول المصدر
    SELECT sale_year, sales_revenue
    FROM sales_data
) AS src
PIVOT (
    -- تجميع إيرادات المبيعات لكل سنة
    SUM(sales_revenue)
    -- إنشاء أعمدة لكل سنة
    FOR sale_year IN ([2020], [2021], [2022], [2023])
) AS piv;

مثال على تحويل الناتج باستخدام PIVOT SQL. صورة بواسطة المؤلف.

للمشغل PIVOT مزايا وقيود التالية:

• المزايا: الطريقة فعالة عندما تكون الأعمدة مفهرسة بشكل صحيح. كما أنها تتمتع ببناء بسيط وصيغة أكثر قراءةً.

• القيود:ليست جميع قواعد البيانات تدعم عامل PIVOT. يتطلب تحديد الأعمدة مسبقًا، وتحويل البيانات الديناميكية يتطلب تعقيدًا إضافيًا.

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

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

على سبيل المثال، يمكننا تحويل البيانات يدويًا في نفس جدول sales_data باستخدام تعبيرات CASE.

-- تجميع إيرادات المبيعات حسب السنة باستخدام تعبيرات CASE
SELECT 
    -- حساب إجمالي إيرادات المبيعات لكل سنة
    SUM(CASE WHEN sale_year = 2020 THEN sales_revenue ELSE 0 END) AS sales_2020,
    SUM(CASE WHEN sale_year = 2021 THEN sales_revenue ELSE 0 END) AS sales_2021,
    SUM(CASE WHEN sale_year = 2022 THEN sales_revenue ELSE 0 END) AS sales_2022,
    SUM(CASE WHEN sale_year = 2023 THEN sales_revenue ELSE 0 END) AS sales_2023
FROM 
    sales_data;

تحويل الناتج المثالي باستخدام عبارة SQL CASE. صورة بواسطة الكاتب.

استخدام عبارة CASE للتحويل لديه مزايا وقيود:

• المزايا: الطريقة تعمل عبر جميع قواعد البيانات SQL ومرنة لإنشاء أعمدة جديدة ديناميكيًا، حتى عندما تكون أسماء المنتجات غير معروفة أو تتغير بشكل متكرر.

• القيود: يمكن أن تصبح الاستعلامات معقدة وطويلة إذا كان هناك العديد من الأعمدة للتحويل. نظرًا للعديد من الفحوصات الشرطية، يعمل الأسلوب ببطء بشكل طفيف مقارنة بالمشغل PIVOT.

اعتبارات الأداء عند تحويل الصفوف إلى أعمدة

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

أفضل الممارسات

فيما يلي أفضل الممارسات لتحسين استعلاماتك وتحسين الأداء.

• استراتيجيات الفهرسة: الفهرسة السليمة أمر أساسي لتحسين استعلامات البيانات الدورانية، مما يتيح لـ SQL استرداد البيانات ومعالجتها بشكل أسرع. عليك دائمًا إنشاء فهارس للأعمدة المستخدمة بشكل متكرر في شرط WHERE أو الأعمدة التي تجمعها لتقليل أوقات المسح.

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

• تحديد الأعمدة والصفوف في العشوائية: الأعمدة المحورية هي اللازمة للتحليل حيث يمكن أن تكون العشوائية للعديد من الأعمدة مستهلكة للموارد وتنشئ جداول كبيرة.

تجنب الوقوع في الأخطاء الشائعة

إليك بعض الأخطاء الشائعة التي قد تواجهها في استعلامات الجدول المحوري وكيفية تجنبها.

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

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

• التجميع على مجموعات بيانات كبيرة بدون تصفية مسبقة: يمكن أن تؤدي وظائف التجميع مثل SUM أو COUNT على مجموعات بيانات كبيرة إلى بطء في أداء قاعدة البيانات. بدلاً من تحويل المجموعة البيانات بأكملها، قم بتصفية البيانات أولاً باستخدام عبارة WHERE.

• قيم NULL في أعمدة التدوير: غالبًا ما تنتج عمليات التدوير قيم NULL عندما لا تكون هناك بيانات لعمود معين. يمكن أن تبطئ هذه القيم استعلامات قاعدة البيانات وتجعل النتائج أصعب في التفسير. لتجنب هذه المشكلة، استخدم وظائف مثل COALESCE لاستبدال قيم NULL بقيمة افتراضية.

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

جرب مسارنا المهني مطور SQL Server، الذي يغطي كل شيء من المعاملات ومعالجة الأخطاء إلى تحسين أداء الاستعلام.

تنفيذات محددة لقاعدة البيانات

عمليات الدوران تختلف بشكل كبير بين قواعد البيانات مثل SQL Server و MySQL و Oracle. لكل من هذه القواعد قواعد بناء جملة محددة وقيود. سأغطي أمثلة على تحويل البيانات في قواعد البيانات المختلفة وميزاتها الرئيسية.

SQL Server

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

• الدعم المباشر لـ PIVOT و UNPIVOT: يسمح مشغل PIVOT في قاعدة بيانات SQL Server بتحويل الصفوف إلى أعمدة بسرعة. يمكن لمشغل UNPIVOT عكس هذه العملية أيضًا.

• خيارات التجميع: يسمح لك مشغل PIVOT باستخدام وظائف تجميع مختلفة مثل SUM، COUNT و AVG.

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

في المثال أدناه، يحول مشغل PIVOT قيم Product إلى أعمدة ويجمع Sales باستخدام مشغل SUM.

-- حدد التاريخ والأعمدة المدورة لكل منتج
SELECT Date, [Laptop], [Mouse] 
FROM
    (
        -- الاستعلام الفرعي لاسترداد الأعمدة التاريخ والمنتج والمبيعات
        SELECT Date, Product, Sales FROM SalesData
    ) AS source
PIVOT
(
    -- تجميع المبيعات حسب المنتج، وتدوير قيم المنتجات إلى أعمدة
    SUM(Sales)
    FOR Product IN ([Laptop], [Mouse])
) AS pivot_table;

أوصي بحضور دورة مقدمة إلى خادم SQL على DataCamp لاحتراف أساسيات خادم SQL لتحليل البيانات.

MySQL

تفتقر MySQL إلى الدعم الأصلي لمشغل PIVOT. ومع ذلك، يمكنك استخدام بيان CASE لتدوير الصفوف يدويًا إلى أعمدة ودمج وظائف تجميع أخرى مثل SUM، AVG، و COUNT. على الرغم من أن هذه الطريقة مرنة، إلا أنها يمكن أن تصبح معقدة إذا كان لديك العديد من الأعمدة للتدوير.

الاستعلام أدناه يحقق نفس النتيجة كما في مثال PIVOT في خادم SQL عن طريق التجميع الشرطي لمبيعات كل منتج باستخدام بيان CASE.

-- اختر التاريخ والأعمدة المدورة لكل منتج
SELECT 
    Date,
    -- استخدم بيان CASE لإنشاء عمود لمبيعات الكمبيوتر المحمول والفأرة
    SUM(CASE WHEN Product = 'Laptop' THEN Sales ELSE 0 END) AS Laptop,
    SUM(CASE WHEN Product = 'Mouse' THEN Sales ELSE 0 END) AS Mouse
FROM SalesData
GROUP BY Date;

أوراكل

أوراكل تدعم مشغل PIVOT، الذي يسمح بتحويل الصفوف بسهولة إلى أعمدة. تمامًا مثل خادم SQL، ستحتاج إلى تحديد الأعمدة بشكل صريح للتحويل.

في الاستعلام أدناه، يحول مشغل PIVOT قيم ProductName إلى أعمدة ويجمع SalesAmount باستخدام مشغل SUM.

SELECT *
FROM (
    -- اختيار بيانات المصدر
    SELECT SaleDate, ProductName, SaleAmount FROM SalesData
)
PIVOT (
    -- تجميع المبيعات حسب المنتج، وإنشاء أعمدة مدورة
    SUM(SaleAmount)
    FOR ProductName IN ('Laptop' AS Laptop, 'Mouse' AS Mouse)
);

عرض مثالي لتحويل الناتج باستخدام مشغل PIVOT في SQL في Oracle. صورة من قبل المؤلف.

تقنيات متقدمة لتحويل الصفوف إلى أعمدة في SQL

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

التحويلات الديناميكية

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

نفترض أن لدينا جدول SalesData ويمكننا إنشاء جدول محوري ديناميكي يتكيف إذا تمت إضافة منتجات جديدة. في الاستعلام أدناه ، @columns يقوم ببناء قائمة الأعمدة المحورية ديناميكيًا ، و sp_executesql يُشغل الاستعلام الذي تم إنشاؤه.

DECLARE @columns NVARCHAR(MAX), @sql NVARCHAR(MAX);
-- الخطوة 1: إنشاء قائمة منتجات مميزة للتحور
SELECT @columns = STRING_AGG(QUOTENAME(Product), ', ') 
FROM (SELECT DISTINCT Product FROM SalesData) AS products;
-- الخطوة 2: بناء استعلام SQL الديناميكي
SET @sql = N'
SELECT Date, ' + @columns + '
FROM 
    (SELECT Date, Product, Sales FROM SalesData) AS source
PIVOT
(
    SUM(Sales)
    FOR Product IN (' + @columns + ')
) AS pivot_table;';
-- الخطوة 3: تنفيذ SQL الديناميكي
EXEC sp_executesql @sql;

معالجة عدة أعمدة

في السيناريوهات التي تحتاج فيها إلى تحويل عدة أعمدة بشكل متزامن ، ستستخدم مشغل PIVOT وتقنيات التجميع الإضافية لإنشاء عدة أعمدة في نفس الاستعلام.

في المثال أدناه ، قمت بتحويل أعمدة Sales و Quantity حسب Product.

-- تحويل مبيعات وكمية الكمبيوتر المحمول والفأرة حسب التاريخ
SELECT 
    p1.Date, 
    p1.[Laptop] AS Laptop_Sales, 
    p2.[Laptop] AS Laptop_Quantity, 
    p1.[Mouse] AS Mouse_Sales, 
    p2.[Mouse] AS Mouse_Quantity
FROM 
    (
        -- جدول محوري للمبيعات
        SELECT Date, [Laptop], [Mouse]
        FROM 
            (SELECT Date, Product, Sales FROM SalesData) AS source
        PIVOT
            (SUM(Sales) FOR Product IN ([Laptop], [Mouse])) AS pivot_sales
    ) p1
JOIN
    (
        -- جدول محوري للكمية
        SELECT Date, [Laptop], [Mouse]
        FROM 
            (SELECT Date, Product, Quantity FROM SalesData) AS source
        PIVOT
            (SUM(Quantity) FOR Product IN ([Laptop], [Mouse])) AS pivot_quantity
    ) p2
ON p1.Date = p2.Date;

مثال على تحويل الإخراج المتعدد للأعمدة باستخدام عامل PIVOT في SQL. الصورة من تأليف المؤلف.

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

الاستنتاج

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

دورتنا تقارير في SQL هي خيار رائع إذا كنت ترغب في تعلم المزيد حول تلخيص البيانات وإعدادها للعرض و/أو بناء لوحات التحكم. مساراتنا المهنية محلل بيانات معتمد في SQL و مهندس بيانات معتمد في SQL هي فكرة رائعة أيضًا، وتضيف الكثير إلى أي سيرة ذاتية، لذا انضم اليوم.