الغوص العميق في دوال النوافذ

دوال النوافذ هي أكثر مفاهيم SQL المتقدمة اختباراً في مقابلات علوم البيانات. تتيح لك الحساب عبر الصفوف مع الاحتفاظ بتفاصيل الصف الفردي - شيء لا يستطيع GROUP BY العادي فعله.

صيغة دالة النافذة

function_name(column) OVER (
    PARTITION BY partition_column    -- اختياري: تجميع الصفوف
    ORDER BY order_column            -- اختياري: الترتيب داخل القسم
    ROWS/RANGE frame_specification   -- اختياري: تعريف إطار النافذة
)

دوال الترتيب

ثلاث دوال ترتيب تخدم أغراضاً مختلفة:

الدالة	التعادلات	الفجوات
ROW_NUMBER()	ترتيب عشوائي	لا فجوات
RANK()	نفس الترتيب	فجوات بعد التعادل
DENSE_RANK()	نفس الترتيب	لا فجوات

مثال: ترتيب البائعين حسب الإيرادات

SELECT
    name,
    revenue,
    ROW_NUMBER() OVER (ORDER BY revenue DESC) as row_num,
    RANK() OVER (ORDER BY revenue DESC) as rank,
    DENSE_RANK() OVER (ORDER BY revenue DESC) as dense_rank
FROM sales;

-- النتائج:
-- name    | revenue | row_num | rank | dense_rank
-- Alice   | 100     | 1       | 1    | 1
-- Bob     | 100     | 2       | 1    | 1  (تعادل مع Alice)
-- Charlie | 90      | 3       | 3    | 2  (rank يتخطى 2، dense_rank لا)

سؤال مقابلة كلاسيكي: "جد أفضل 3 منتجات حسب المبيعات في كل فئة"

WITH ranked AS (
    SELECT
        category,
        product_name,
        sales,
        DENSE_RANK() OVER (
            PARTITION BY category
            ORDER BY sales DESC
        ) as rank
    FROM products
)
SELECT category, product_name, sales
FROM ranked
WHERE rank <= 3;

LAG و LEAD

الوصول لقيم الصف السابق أو التالي - أساسي لتحليل السلاسل الزمنية.

-- حساب التغير اليومي في الإيرادات
SELECT
    date,
    revenue,
    LAG(revenue, 1) OVER (ORDER BY date) as prev_day_revenue,
    revenue - LAG(revenue, 1) OVER (ORDER BY date) as daily_change,
    ROUND(
        100.0 * (revenue - LAG(revenue, 1) OVER (ORDER BY date))
        / LAG(revenue, 1) OVER (ORDER BY date),
        2
    ) as pct_change
FROM daily_sales;

نمط مقابلة: "حدد المستخدمين الذين انخفض إنفاقهم لـ 3 أشهر متتالية"

WITH monthly_spending AS (
    SELECT
        user_id,
        DATE_TRUNC('month', order_date) as month,
        SUM(amount) as spending,
        LAG(SUM(amount), 1) OVER (PARTITION BY user_id ORDER BY DATE_TRUNC('month', order_date)) as prev_month,
        LAG(SUM(amount), 2) OVER (PARTITION BY user_id ORDER BY DATE_TRUNC('month', order_date)) as prev_2_month
    FROM orders
    GROUP BY user_id, DATE_TRUNC('month', order_date)
)
SELECT DISTINCT user_id
FROM monthly_spending
WHERE spending < prev_month
  AND prev_month < prev_2_month;

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

أطر النوافذ تتيح لك حساب التراكمية أو المتحركة:

-- المجموع التراكمي للإيرادات
SELECT
    date,
    revenue,
    SUM(revenue) OVER (
        ORDER BY date
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) as running_total
FROM daily_sales;

-- المتوسط المتحرك لـ 7 أيام
SELECT
    date,
    revenue,
    AVG(revenue) OVER (
        ORDER BY date
        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    ) as moving_avg_7d
FROM daily_sales;

PARTITION BY للتحليل المجزأ

PARTITION BY يعيد بدء الحسابات لكل مجموعة:

-- المجموع التراكمي لكل عميل
SELECT
    customer_id,
    order_date,
    amount,
    SUM(amount) OVER (
        PARTITION BY customer_id
        ORDER BY order_date
    ) as customer_running_total
FROM orders;

نصائح احترافية للمقابلة

1. PARTITION BY مقابل GROUP BY: PARTITION BY يحتفظ بكل الصفوف، GROUP BY يطويها
2. ORDER BY في OVER: مطلوب للترتيب وLAG/LEAD، اختياري للتجميعات البسيطة
3. الإطار الافتراضي: إذا حُدد ORDER BY، الافتراضي هو ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
4. نوافذ متعددة: يمكنك استخدام جمل OVER() مختلفة في نفس الاستعلام

دوال النوافذ تفصل المرشحين المتوسطين عن الكبار. أتقن هذه الأنماط وستتميز. :::