تحويل البيانات باستخدام استعلامات T-SQL

مكتمل

يتوفر T-SQL في مستودعات Fabric وبيوت البحيرات. يوفر المستودع دعما كاملا ل T-SQL للقراءة والكتابة — يمكنك استخدام SELECTعبارات ، INSERT، UPDATE، DELETEو CREATE TABLE AS SELECT (CTAS) لتشكيل البيانات مباشرة. كما يعرض Lakehouse جداول دلتا الخاصة بها من خلال نقطة نهاية تحليلات SQL، والتي تدعم T-SQL للاستعلام والتحليل لكنها قابلة للقراءة فقط. تركز هذه الوحدة على تحويل البيانات في مستودع، حيث تتيح لك قدرة القراءة والكتابة بناء منطق تحويل يحافظ على النتائج.

استعلام بيانات في المستودع

يمكنك تشغيل استعلامات T-SQL في مستودع Fabric باستخدام محرر استعلامات SQL داخل مستكشف المستودعات. لفتحه، اختر استعلام SQL جديد من الشريط. يدعم المحرر IntelliSense، وتظليل النحو، وعدة علامات تبويب للاستعلام، بحيث يمكنك الكتابة والتكرار على الاستعلامات المعقدة دون مغادرة المتصفح.

يمكنك أيضا الاتصال بالمستودع من أدوات خارجية مثل SQL Server Management Studio (SSMS) أو تعليمة Visual Studio برمجية باستخدام امتداد MSSQL عبر سلسلة T-SQL سلسلة الاتصال. هذه المرونة تتيح لفريقك استخدام أي أداة يشعرون بالراحة معها.

للمستخدمين الذين يفضلون تجربة بدون برمجة، يوفر Visual محرر Power Query واجهة سحب وإفلات. تسحب الجداول من لوحة Explorer إلى لوحة، ثم تطبق Power Query العمليات مثل الدمج، الفلاتر، والتجميعات بصريا. يقوم المحرر بإنشاء T-SQL خلف الكواليس، ويمكنك التبديل إلى View SQL في أي وقت لرؤية الاستعلام الناتج. يمكنك أيضا حفظ المخرجات كعرض جديد أو جدول جديد مباشرة من المحرر المرئي.

Tip

محرر Visual محرر Power Query خيار جيد لاستكشاف البيانات السريع أو عند بناء انضمامات بسيطة. للتحويلات المعقدة التي تتضمن دوال النوافذ أو CTEs أو المنطق الشرطي، استخدم محرر استعلامات SQL.

بيانات التصفية والشكل

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

SELECT
    customer_id,
    order_date,
    amount
FROM staging.orders
WHERE order_date >= '2024-01-01'
    AND status = 'Completed';

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

SELECT
    order_id,
    quantity,
    unit_price,
    quantity * unit_price AS line_total,
    CASE
        WHEN quantity * unit_price > 1000 THEN 'High'
        ELSE 'Standard'
    END AS order_tier
FROM staging.orders;

عادة، تحتاج إلى تحديد كيفية التعامل مع القيم الصفرية قبل انتقال البيانات إلى جداول تحليلية. استخدم ISNULL لاستبدال القيمة الصفرية بقيمة افتراضية، أو COALESCE لاختيار أول قيمة غير صفرية من قائمة الأعمدة:

SELECT
    ISNULL(discount, 0) AS discount,
    COALESCE(shipping_address, billing_address) AS address
FROM staging.orders;

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

SELECT
    CAST(order_date_text AS DATE) AS order_date,
    CONVERT(DECIMAL(10,2), amount_text) AS amount
FROM staging.raw_orders;

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

دمج البيانات مع الروابط والتجميعات

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

الربط الأساسي يجمع بين أعمدة من جدولين بناء على مفتاح مشترك:

SELECT
    o.order_id,
    o.order_date,
    c.customer_name,
    c.segment
FROM staging.orders AS o
INNER JOIN staging.customers AS c
    ON o.customer_id = c.customer_id;

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

SELECT
    c.region,
    YEAR(o.order_date) AS order_year,
    COUNT(*) AS order_count,
    SUM(o.amount) AS total_sales
FROM staging.orders AS o
INNER JOIN staging.customers AS c
    ON o.customer_id = c.customer_id
GROUP BY c.region, YEAR(o.order_date);

استخدم الجملة HAVING لتصفية النتائج المجمعة. على عكس WHERE (الذي يقوم بتصفية الصفوف الفردية قبل التجميع)، HAVING يقوم بتصفية المجموعات بعد التجميع. هذا التمييز مهم عندما تريد إيجاد مجموعات تحقق عتبة:

SELECT
    region,
    YEAR(order_date) AS order_year,
    SUM(amount) AS total_sales
FROM staging.orders
GROUP BY region, YEAR(order_date)
HAVING SUM(amount) > 50000;

تطبيق دوال النافذة

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

SELECT
    customer_id,
    order_date,
    amount,
    ROW_NUMBER() OVER (
        PARTITION BY customer_id
        ORDER BY order_date
    ) AS order_sequence,
    SUM(amount) OVER (
        PARTITION BY customer_id
        ORDER BY order_date
    ) AS running_total,
    LAG(amount) OVER (
        PARTITION BY customer_id
        ORDER BY order_date
    ) AS previous_order_amount
FROM staging.orders;

في هذا الاستعلام، ROW_NUMBER يخصص رقما متسلسلا داخل طلبات كل عميل، وهو أمر مفيد لتحديد أول أو أحدث طلب. SUM ... OVER يحسب إجمالي إجمالي المبيعات التراكمي لكل عميل. LAG يسترجع قيمة الصف السابق داخل التقسيم، مما يتيح حسابات التغيير لفترة بعد فترة دون انضمامات ذاتية.

تشمل RANK وظائف النافذة المفيدة الأخرى (التي تتعامل مع الروابط بشكل مختلف عن ROW_NUMBER)، DENSE_RANKو LEAD (التي تنظر للأمام بدلا من الخلف).

هيكل الاستعلامات المعقدة باستخدام CTEs

تقسم التعبيرات الشائعة للجداول (CTEs) الاستعلام المعقد إلى خطوات قابلة للقراءة وتسمى مسماة. كل CTE يحدد نتيجة وسيطة يمكن للخطوة التالية الرجوع إليها.

WITH monthly_totals AS (
    SELECT
        YEAR(order_date) AS yr,
        MONTH(order_date) AS mo,
        SUM(amount) AS monthly_total
    FROM staging.orders
    GROUP BY YEAR(order_date), MONTH(order_date)
)
SELECT
    yr,
    mo,
    monthly_total,
    SUM(monthly_total) OVER (ORDER BY yr, mo) AS ytd_total
FROM monthly_totals;

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

استمرار النتائج باستخدام إنشاء جدول ك select

عندما يكون لديك استعلام تحويل ينتج نتائج ترغب في تخزينها، استخدم CREATE TABLE AS SELECT (CTAS) لإنشاء جدول جديد وملئه في بيان واحد:

CREATE TABLE gold.regional_sales_summary
AS
SELECT
    c.region,
    YEAR(o.order_date) AS order_year,
    MONTH(o.order_date) AS order_month,
    SUM(o.amount) AS total_sales,
    COUNT(DISTINCT o.customer_id) AS unique_customers
FROM staging.orders AS o
INNER JOIN staging.customers AS c
    ON o.customer_id = c.customer_id
GROUP BY c.region, YEAR(o.order_date), MONTH(o.order_date);

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

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