كيفية استخدام PARTITION BY في sql

الدالة WINDOW (النافذة) هي نوع من دوال SQL التي تعمل على مجموعة من الصفوف المحددة بواسطة جملة OVER.

تحتوي جملة OVER على جزء فرعي PARTITION BY يحدد كيفية تجميع الصفوف إلى نوافذ.

يتبع جزء PARTITION BY واحد أو أكثر من أسماء الأعمدة التي تحدد معايير تجميع الصفوف.

جزء ORDER BY اختياري ويحدد كيفية فرز الصفوف داخل كل نافذة.

على سبيل المثال:

SELECT car_make, car_model, car_price,
AVG(car_price) OVER (PARTITION BY car_type) AS "متوسط سعر نوع السيارة"
FROM car_list_prices

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

يتم حساب المتوسط باستخدام الدالة AVG().

يتم تحديد كيفية تقسيم البيانات عبر الاستعلام PARTITION BY وهنا اخترنا أن نقسم السيارات حسب النوع حتى نقوم بحساب متوسط سعر السيارات لكل نوع.

وهذا مثال آخر :

SELECT id, first_name, last_name, department, salary,
ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rownum
FROM employees;

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

سيتم ترتيب الصفوف داخل كل قسم حسب راتب الموظف بشكل تنازلي.

هذا يعني أن الموظف ذو الراتب الأعلى في كل قسم سيحصل على رقم صف 1، والثاني سيحصل على رقم صف 2، وهكذا.

هذه هي بعض البيانات التي قد يعود بها الاستعلام:

| id | first_name | last_name | department      | salary | rownum |
|----|------------|-----------|-----------------|--------|--------|
| 1  | Bob        | Mendelsohn| Risk Management | 5412.47| 1      |
| 2  | Frances    | Jackson   | Risk Management | 5123.12| 2      |
| 3  | Alice      | Smith     | Marketing       | 4321.56| 1      |
| 4  | David      | Jones     | Marketing       | 3987.34| 2      |
| 5  | Emma       | Watson    | Sales           | 4567.89| 1      |
| 6  | John       | Doe       | Sales           | 4234.67| 2      |

الدالة WINDOW في SQL تستخدم لتنفيذ عمليات تحويل البيانات في نطاق معين مع الاحتفاظ بالصلة بين الصفوف في الجدول الأصلي، وتتكون الدالة WINDOW من ثلاثة أجزاء رئيسية: OVER ، PARTITION BY ، و ORDER BY.

دعنا نشرح كل منها بالتفصيل ونقدم أمثلة لتوضيح الفكرة.

1- OVER

تحدد عملية التجميع أو الوظيفة التحليلية التي ستُطبق على مجموعة الصفوف المحددة بواسطة النافذة، ويمكن أن تكون هذه الوظيفة مثلاً  SUM ، AVG ، ROW_NUMBER ، وما إلى ذلك.

مثال:

لنفترض أن لدينا جدولًا يحتوي على أسماء الموظفين ورواتبهم، ونريد حساب المجموعة الكلية للرواتب ومعرفة رتبة كل موظف في الجدول.

SELECT Name, Salary, SUM(Salary) OVER () AS TotalSalary, 
       ROW_NUMBER() OVER (ORDER BY Salary DESC) AS Rank
FROM Employees;

2- PARTITION BY

يُستخدم لتقسيم البيانات في النافذة إلى مجموعات فرعية (بارتشن) استنادًا إلى قيمة محددة في العمود، ويتم تطبيق الوظيفة التحليلية المحددة بواسطة OVER على كل بارتشن بشكل منفصل.

مثال:

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

SELECT Name, Salary, SUM(Salary) OVER (PARTITION BY Department) AS DepartmentTotalSalary
FROM Employees;

هنا ، استخدمت PARTITION BY مع عمود القسم (Department) لتقسيم الصفوف في النافذة إلى بارتشنات.

3- ORDER BY

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

مثال:

لنستخدم نفس الجدول من المثال السابق ونريد حساب مجموع رواتب القسم وترتيب الموظفين في القسم وفقًا للرواتب.

SELECT Name, Salary, SUM(Salary) OVER (PARTITION BY Department ORDER BY Salary DESC) AS DepartmentTotalSalary, 
       RANK() OVER (PARTITION BY Department ORDER BY Salary DESC) AS Rank
FROM Employees;

استخدمت PARTITION BY مع عمود القسم (Department) لتقسيم الصفوف في النافذة إلى بارتشنات.، و ORDER BY مع عمود الرواتب (Salary) لترتيب الصفوف داخل كل بارتشن بناءً على الرواتب، ثم  حساب مجموع رواتب القسم وترتيب الموظفين في القسم وفقًا للرواتب.

باستخدام الدالة WINDOW بشكل صحيح مع OVER و PARTITION BY و ORDER BY، تستطيع تنفيذ عمليات تحليلية معقدة وحسابية على البيانات في نطاقات محددة وبشكل مرتب ومجمع.

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

تتكون الدالة WINDOW من ثلاثة أجزاء رئيسية:

• PARTITION BY: تستخدم لتقسيم الصفوف في النافذة إلى مجموعات فرعية بناءً على قيمة معينة في عمود محدد. في الجملة PARTITION BY، يتم تحديد العمود الذي سيتم تجزئته.
• ORDER BY: تستخدم لترتيب الصفوف داخل كل مجموعة فرعية في النافذة. يتم تحديد العمود الذي سيتم استخدامه للترتيب.
• OVER: تستخدم لتعريف المجموعة الكاملة للصفوف المشمولة في النافذة، بما في ذلك المجموعات الفرعية المحددة بواسطة PARTITION BY. يمكنك استخدام دوال التجميع والتحليل مع هذا الجزء لتطبيق الوظائف المعنية على الصفوف داخل النافذة.

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

بالإضافة للشرح السابق ها هو مثال عملي على توضيح دالة WINDOW:

نفترض أن لدينا جدول employees مع الأعمدة التالية:

id, name, department, salary

بحيث يوجد عدة موظفين في كل قسم

لحساب ترتيب الموظفين حسب الراتب داخل كل قسم، يمكننا استخدام الاستعلام التالي:

SELECT id, name, department, salary
     , DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) dr
FROM employees

الناتج سيكون مثل هذا:

id - name - department - salary - dr
1 - John - Marketing - 5000 - 1
2 - Jane - Marketing - 4000 - 2 
3 - Tom - Marketing - 3000 - 3
4 - Sue - IT - 6000 - 1 
5 - Kent - IT - 5000 - 2
6 - Ben - HR - 7000 - 1 
7 - Amy - HR - 6000 - 2

حيث تم ترتيب الموظفين حسب الراتب داخل كل قسم (Marketing, IT, HR) فـJohn لديه أعلى راتب في قسمه Marketing فحصل على الترتيب 1 والخ..

كما يفضل الإطلاع على المصادر التالية

الدالة WINDOW هي جزء من لغة استعلام SQL المتقدمة وتستخدم لتنفيذ عمليات تحليل البيانات والتجميع على مجموعة من الصفوف في قاعدة البيانات. تتضمن WINDOW ثلاث عبارات رئيسية: OVER و PARTITION BY و ORDER BY. لنلقِ نظرة على كل واحدة منها بالتفصيل:

OVER:

• تُستخدم لتحديد نطاق (range) الصفوف التي سيتم تطبيق الدالة عليها.
• يمكن أن يتضمن تعبيرًا مثل ROWS BETWEEN لتحديد المجموعة المحددة من الصفوف التي ستُستخدم في العملية.
• يمكن أيضًا تعيين النطاق باستخدام RANGE BETWEEN بدلاً من ROWS BETWEEN، وذلك لتحديد النطاق بناءً على القيم الفردية بدلاً من الصفوف.

PARTITION BY:

• تُستخدم لتقسيم البيانات إلى مجموعات (بارتيشن) مستقلة بناءً على قيمة معينة.

• يتم تطبيق الدالة أو العملية على كل مجموعة على حدة، مما يسمح بإجراء عمليات مقارنة وتجميع مستقلة لكل مجموعة.

ORDER BY:

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

• يتم تحديد الترتيب بناءً على قيمة أو أكثر في الصفوف، ويمكن أن يكون تصاعديًا (ASC) أو تنازليًا (DESC).