ما فائدة الدالة OVER و PARTITION BY في SQL

تستخدم الدالة OVER لانتقاء الأسطر من ناتج الاستعلام التي نريد تطبيق عليهم دالة معينة (دالة تجميع aggregation function)، وتفيد في عمل ترتيب أو حسابات عليهم، من خلال تقسيمهم إلى أجزاء partitions ولذلك تستخدم مع  window functions.

الشكل العام لها:

<function> OVER (        [PARTITION BY clause]
                         [ORDER BY clause]
                         [ROWS or RANGE clause])

حيث نمرر لها غلاقة clause نحدد فيها الأسطر المستهدفة.

• في حال لم يتم تمرير غلاقة يكون استعلام على كامل الجدول
• في حال تم تمرير عبارة التقسيم يتم تطبيق العملية على الأسطر / السجلات المختارة

مثلا يمكن جلب إجمالي أرباح شركة ثم حساب المجموع الجزئي حسب الفرع بعمل تقسيم حسب الدولة

SELECT year, country,  
product, -- المنتج
profit,  -- ربح النتج

SUM(profit) OVER() AS total_profit, -- ربح إجمالي يجمع الأرباح

SUM(profit) OVER(PARTITION BY country) AS country_profit -- تقسيم الربح حسب الدوالة

FROM sales

ORDER BY country, year, product, profit;

والنتيجة:

أصبح باستطاعتنا تحديد ربح منتج الحاسوب بالنسبة لأرباح فرع فنلندا الإجمالية مثلا.. 1500/1610 في سنة 2000

مثال آخر،

حساب قيم أعمدة جدول من خلال عمل حسابات جزئية على ناتج الاستعلام، في مدرسة نريد عمل تحليل حسب متوسط العمر و عدد الطلاب والنتائج يتم فرزهم حسب الجنس. 

SELECT id, name, gender,
-- عدد الطلاب حسب الجنس
COUNT(gender) OVER (PARTITION BY gender) AS Total_students,
-- متسوط العمر حسب الجنس
AVG(age) OVER (PARTITION BY gender) AS Average_Age,
-- مجموع الدخل حسب الجنس
SUM(total_score) OVER (PARTITION BY gender) AS Total_Score
FROM student

والنتائج: