MySQL وتصميم قواعد البيانات

دوال النافذة والتحليلات

13 دقيقة الدرس 10 من 40

دوال النافذة والتحليلات

دوال النافذة (تسمى أيضاً الدوال التحليلية) تقوم بإجراء حسابات عبر مجموعة من صفوف الجدول المتعلقة بالصف الحالي. على عكس GROUP BY، لا تقوم دوال النافذة بطي الصفوف - بل تضيف أعمدة محسوبة مع الحفاظ على جميع الصفوف الأصلية.

ما هي دوال النافذة؟

تعمل دوال النافذة على "نافذة" من الصفوف وتُرجع قيمة لكل صف بناءً على الحسابات عبر تلك النافذة:

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

-- بناء جملة دالة النافذة الأساسية
SELECT
    column1,
    column2,
    WINDOW_FUNCTION() OVER (
        [PARTITION BY partition_column]
        [ORDER BY sort_column]
        [ROWS or RANGE frame_specification]
    ) AS result_column
FROM table_name;

ROW_NUMBER() - الترقيم المتسلسل

تعين ROW_NUMBER() رقماً متسلسلاً فريداً لكل صف داخل قسم:

-- ترقيم جميع الطلبات لكل عميل
SELECT
    customer_id,
    order_id,
    order_date,
    total_amount,
    ROW_NUMBER() OVER (
        PARTITION BY customer_id
        ORDER BY order_date
    ) AS order_sequence
FROM orders;

-- البحث عن أحدث طلب لكل عميل
SELECT *
FROM (
    SELECT
        customer_id,
        order_id,
        order_date,
        total_amount,
        ROW_NUMBER() OVER (
            PARTITION BY customer_id
            ORDER BY order_date DESC
        ) AS rn
    FROM orders
) AS ranked_orders
WHERE rn = 1;

RANK() و DENSE_RANK()

تعين RANK() و DENSE_RANK() الترتيب مع معالجة مختلفة للروابط:

-- مقارنة RANK مقابل DENSE_RANK
SELECT
    product_name,
    sales_count,
    RANK() OVER (ORDER BY sales_count DESC) AS rank_position,
    DENSE_RANK() OVER (ORDER BY sales_count DESC) AS dense_rank_position
FROM products;

-- RANK():       1, 2, 2, 4, 5    (يتخطى الأرقام بعد الروابط)
-- DENSE_RANK(): 1, 2, 2, 3, 4    (بدون فجوات)

-- أفضل 3 منتجات لكل فئة
SELECT *
FROM (
    SELECT
        category,
        product_name,
        price,
        DENSE_RANK() OVER (
            PARTITION BY category
            ORDER BY price DESC
        ) AS price_rank
    FROM products
) AS ranked_products
WHERE price_rank <= 3;

متى تستخدم: استخدم RANK() للترتيب التقليدي مع فجوات بعد الروابط. استخدم DENSE_RANK() عندما تريد ترتيباً متتالياً. استخدم ROW_NUMBER() عندما تحتاج أرقاماً فريدة (حتى للروابط).

PARTITION BY - إنشاء النوافذ

يقسم PARTITION BY مجموعات النتائج إلى مجموعات (أقسام) لدالة النافذة:

-- ترتيب المبيعات داخل كل منطقة
SELECT
    region,
    salesperson_name,
    total_sales,
    RANK() OVER (
        PARTITION BY region
        ORDER BY total_sales DESC
    ) AS regional_rank,
    RANK() OVER (
        ORDER BY total_sales DESC
    ) AS overall_rank
FROM sales_data;

-- النسبة المئوية من إجمالي الفئة
SELECT
    category,
    product_name,
    revenue,
    SUM(revenue) OVER (PARTITION BY category) AS category_total,
    ROUND(revenue * 100.0 / SUM(revenue) OVER (PARTITION BY category), 2) AS pct_of_category
FROM product_revenue;

LAG() و LEAD() - الوصول إلى الصفوف المجاورة

يصل LAG() إلى الصفوف السابقة، ويصل LEAD() إلى الصفوف التالية:

-- مقارنة مبيعات كل شهر بالشهر السابق
SELECT
    sale_month,
    monthly_revenue,
    LAG(monthly_revenue) OVER (ORDER BY sale_month) AS previous_month,
    monthly_revenue - LAG(monthly_revenue) OVER (ORDER BY sale_month) AS month_over_month_change,
    ROUND((monthly_revenue - LAG(monthly_revenue) OVER (ORDER BY sale_month)) * 100.0 /
          LAG(monthly_revenue) OVER (ORDER BY sale_month), 2) AS pct_change
FROM monthly_sales
ORDER BY sale_month;

-- مقارنة السعر الحالي بتغييرات السعر السابقة والتالية
SELECT
    product_name,
    price_date,
    price,
    LAG(price, 1) OVER (PARTITION BY product_id ORDER BY price_date) AS previous_price,
    LEAD(price, 1) OVER (PARTITION BY product_id ORDER BY price_date) AS next_price,
    price - LAG(price, 1) OVER (PARTITION BY product_id ORDER BY price_date) AS price_change
FROM price_history;

المعاملات: LAG(column, offset, default) و LEAD(column, offset, default). الإزاحة الافتراضية هي 1، والقيمة الافتراضية هي NULL إذا لم يكن هناك صف موجود عند تلك الإزاحة.

المجاميع التراكمية والإجماليات المتحركة

يمكن لدوال النافذة حساب المجاميع التراكمية، والمتوسطات المتحركة، والإحصائيات التراكمية:

-- المجموع التراكمي للمبيعات
SELECT
    order_date,
    daily_revenue,
    SUM(daily_revenue) OVER (
        ORDER BY order_date
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS cumulative_revenue
FROM daily_sales
ORDER BY order_date;

-- المتوسط المتحرك لمدة 7 أيام
SELECT
    sale_date,
    daily_sales,
    AVG(daily_sales) OVER (
        ORDER BY sale_date
        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    ) AS moving_avg_7day
FROM daily_sales
ORDER BY sale_date;

-- العد التراكمي والنسبة المئوية
SELECT
    order_date,
    order_count,
    SUM(order_count) OVER (ORDER BY order_date) AS cumulative_orders,
    ROUND(SUM(order_count) OVER (ORDER BY order_date) * 100.0 /
          SUM(order_count) OVER (), 2) AS cumulative_pct
FROM daily_order_counts
ORDER BY order_date;

مواصفات الإطار

تحدد مواصفات الإطار الصفوف التي يجب تضمينها في حسابات النافذة:

-- بناء جملة مواصفات الإطار
ROWS BETWEEN start AND end

-- مواصفات الإطار الشائعة:
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW  -- من البداية إلى الحالي
ROWS BETWEEN 3 PRECEDING AND CURRENT ROW          -- آخر 4 صفوف بما في ذلك الحالي
ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING          -- الصف الحالي +/- 1
ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING  -- الحالي إلى النهاية
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING  -- جميع الصفوف (افتراضي)

-- مقاييس متحركة لمدة 3 أشهر
SELECT
    month,
    revenue,
    AVG(revenue) OVER (
        ORDER BY month
        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    ) AS avg_3month,
    MAX(revenue) OVER (
        ORDER BY month
        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    ) AS max_3month
FROM monthly_revenue;

NTILE() - النسب المئوية والربعيات

يقسم NTILE() الصفوف إلى عدد محدد من المجموعات المتساوية:

-- تقسيم العملاء إلى ربعيات حسب الإنفاق
SELECT
    customer_name,
    total_spent,
    NTILE(4) OVER (ORDER BY total_spent DESC) AS spending_quartile
FROM customer_spending;

-- تحليل النسبة المئوية
SELECT
    spending_percentile,
    COUNT(*) AS customer_count,
    MIN(total_spent) AS min_spending,
    MAX(total_spent) AS max_spending
FROM (
    SELECT
        customer_name,
        total_spent,
        NTILE(100) OVER (ORDER BY total_spent) AS spending_percentile
    FROM customer_spending
) AS percentile_data
GROUP BY spending_percentile
ORDER BY spending_percentile;

دوال نافذة متعددة

يمكنك استخدام دوال نافذة متعددة في نفس الاستعلام:

-- تحليل شامل للمنتج
SELECT
    product_name,
    category,
    price,
    sales_count,
    ROW_NUMBER() OVER (PARTITION BY category ORDER BY sales_count DESC) AS sales_rank_in_category,
    DENSE_RANK() OVER (ORDER BY sales_count DESC) AS overall_sales_rank,
    ROUND(price * 100.0 / AVG(price) OVER (PARTITION BY category), 2) AS pct_of_avg_category_price,
    sales_count - AVG(sales_count) OVER (PARTITION BY category) AS diff_from_category_avg,
    NTILE(10) OVER (ORDER BY price) AS price_decile
FROM products;

أمثلة تحليلات من العالم الحقيقي

-- تحليل مجموعة العملاء
SELECT
    customer_id,
    first_purchase_month,
    purchase_month,
    DATEDIFF(purchase_month, first_purchase_month) / 30 AS months_since_first,
    monthly_revenue,
    SUM(monthly_revenue) OVER (
        PARTITION BY customer_id
        ORDER BY purchase_month
    ) AS lifetime_value,
    ROW_NUMBER() OVER (
        PARTITION BY customer_id
        ORDER BY purchase_month
    ) AS purchase_sequence
FROM (
    SELECT
        customer_id,
        DATE_FORMAT(order_date, '%Y-%m-01') AS purchase_month,
        MIN(DATE_FORMAT(order_date, '%Y-%m-01')) OVER (PARTITION BY customer_id) AS first_purchase_month,
        SUM(total_amount) AS monthly_revenue
    FROM orders
    GROUP BY customer_id, DATE_FORMAT(order_date, '%Y-%m-01')
) AS customer_purchases;

-- لوحة معلومات أداء المبيعات
SELECT
    salesperson_name,
    region,
    monthly_sales,
    RANK() OVER (PARTITION BY region ORDER BY monthly_sales DESC) AS regional_rank,
    ROUND(monthly_sales * 100.0 / SUM(monthly_sales) OVER (PARTITION BY region), 2) AS pct_of_regional_sales,
    monthly_sales - LAG(monthly_sales) OVER (PARTITION BY salesperson_name ORDER BY sale_month) AS month_over_month,
    AVG(monthly_sales) OVER (
        PARTITION BY salesperson_name
        ORDER BY sale_month
        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    ) AS avg_3month
FROM salesperson_monthly_sales
ORDER BY region, regional_rank;

ملاحظة الأداء: يمكن أن تكون دوال النافذة كثيفة الموارد على مجموعات البيانات الكبيرة. أضف دائماً فهارس مناسبة على أعمدة PARTITION BY و ORDER BY.

تمرين عملي:

التحدي: اكتب استعلامات باستخدام دوال النافذة:

البحث عن المنتجات التي يكون سعرها أعلى من متوسط فئتها
حساب معدل النمو على أساس سنوي للمبيعات الشهرية
تحديد أفضل 20% من العملاء حسب الإيرادات (استخدم NTILE)

الحلول:

-- 1. المنتجات أعلى من متوسط الفئة
SELECT product_name, category, price, category_avg
FROM (
    SELECT
        product_name,
        category,
        price,
        AVG(price) OVER (PARTITION BY category) AS category_avg
    FROM products
) AS price_comparison
WHERE price > category_avg;

-- 2. النمو على أساس سنوي
SELECT
    sale_month,
    monthly_revenue,
    LAG(monthly_revenue, 12) OVER (ORDER BY sale_month) AS same_month_last_year,
    ROUND((monthly_revenue - LAG(monthly_revenue, 12) OVER (ORDER BY sale_month)) * 100.0 /
          LAG(monthly_revenue, 12) OVER (ORDER BY sale_month), 2) AS yoy_growth_pct
FROM monthly_sales;

-- 3. أفضل 20% من العملاء
SELECT customer_name, total_revenue
FROM (
    SELECT
        customer_name,
        total_revenue,
        NTILE(5) OVER (ORDER BY total_revenue DESC) AS revenue_quintile
    FROM customer_revenue
) AS customer_segments
WHERE revenue_quintile = 1;

الملخص

في هذا الدرس، أتقنت:

بناء جملة دالة النافذة مع OVER و PARTITION BY و ORDER BY
ROW_NUMBER() و RANK() و DENSE_RANK() للترتيب
LAG() و LEAD() للوصول إلى الصفوف المجاورة
المجاميع التراكمية والمتوسطات المتحركة مع مواصفات الإطار
NTILE() لتحليل النسبة المئوية والربعية
أنماط التحليلات الواقعية باستخدام دوال النافذة

التالي: في الدرس التالي، سنستكشف التعبيرات الجدولية الشائعة (CTEs) لتنظيم الاستعلامات المعقدة والتعامل مع البيانات المتكررة!