الصورة عن طريق Freepik

SQL (لغة الاستعلام الهيكلية) هي لغة برمجة تستخدم لإدارة البيانات ومعالجتها. ولهذا السبب تعد استعلامات SQL ضرورية جدًا للتفاعل مع قواعد البيانات بطريقة منظمة وفعالة.

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

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

يتم استخدام جملة GROUP BY في SQL

1. تجميع البيانات على بعض الأعمدة
2. تقليل المجموعة إلى صف واحد
3. إجراء عمليات التجميع على أعمدة أخرى من المجموعات.

عمود التجميع = يجب أن تكون القيمة الموجودة في عمود التجميع هي نفسها لجميع الصفوف في المجموعة

عمود التجميع = تختلف القيم الموجودة في عمود التجميع عمومًا والتي يتم تطبيق دالة عليها مثل المجموع والحد الأقصى وما إلى ذلك.

يجب ألا يكون عمود التجميع هو عمود التجميع.

السيناريو 1: التجميع للعثور على مجموع الإجمالي

لنفترض أننا نريد حساب إجمالي مبيعات كل فئة في جدول المبيعات.

لذلك، سنقوم بتجميع المبيعات حسب الفئة وتجميع المبيعات الفردية في كل فئة.

select category, 
sum(amount) as sales
from sales
group by category;

عمود التجميع = الفئة

عمود التجميع = المبلغ

دالة التجميع = المبلغ ()

السيناريو 2: التجميع للعثور على العدد

لنفترض أننا نريد حساب عدد الموظفين في كل قسم.

في هذه الحالة، سنقوم بالتجميع حسب القسم وحساب عدد الموظفين في كل قسم.

select department, 
count(empid) as emp_count
from employees
group by department;

عمود التجميع = القسم

عمود التجميع = empid

دالة التجميع = العد

السيناريو 3: التجميع للعثور على المتوسط

لنفترض أننا نريد حساب متوسط ​​راتب الموظفين في كل قسم

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

select department, 
avg(salary) as avg_salary
from employees
group by department;

عمود التجميع = القسم

عمود التجميع = الراتب

دالة التجميع = المتوسط

السيناريو 4: التجميع للعثور على الحد الأقصى/الحد الأدنى

لنفترض أننا نريد حساب أعلى راتب للموظفين في كل قسم.

سنقوم بتجميع الأقسام وحساب الحد الأقصى للراتب في كل قسم.

select department, 
max(salary) as max_salary
from employees
group by department;

عمود التجميع = القسم

عمود التجميع = الراتب

وظيفة التجميع = الحد الأقصى

السيناريو 5: التجميع للبحث عن التكرارات

لنفترض أننا نريد العثور على أسماء العملاء المكررة أو نفسها في قاعدة بياناتنا.

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

select name, 
count(*) AS duplicate_count
from customers
group by name
having count(*) > 1;

عمود التجميع = الاسم

عمود التجميع = *

دالة التجميع = العد

وجود = شرط التصفية ليتم تطبيقه على وظيفة التجميع

يتم استخدام جملة PARTITION BY في SQL

1. تجميع/تقسيم البيانات في بعض الأعمدة
2. يتم الاحتفاظ بالصفوف الفردية و ليس مجتمعة في واحدة
3. إجراء عمليات الترتيب والتجميع على أعمدة أخرى من المجموعة/القسم.

عمود التقسيم = نختار عمودًا نقوم بتجميع البيانات عليه. يجب أن تكون البيانات الموجودة في عمود القسم هي نفسها لكل مجموعة. إذا لم يتم تحديده، فسيتم اعتبار الجدول الكامل بمثابة قسم واحد.

عمود الترتيب = مع كل مجموعة تم إنشاؤها بناءً على عمود التقسيم، سنقوم بترتيب/فرز الصفوف في المجموعة

وظيفة التصنيف = سيتم تطبيق وظيفة التصنيف أو وظيفة التجميع على الصفوف الموجودة في القسم

السيناريو 6: التقسيم للعثور على أعلى سجل في المجموعة

لنفترض أننا نريد حساب الكتاب الذي حقق أعلى مبيعات في كل فئة - بالإضافة إلى المبلغ الذي حققه الكتاب الأكثر مبيعًا.

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

ومع ذلك، نحتاج إلى تفاصيل السجل مثل اسم الكتاب والمبلغ وما إلى ذلك، بالإضافة إلى الفئة لمعرفة الكتاب الذي حقق أعلى مبيعات في كل فئة.

select book_name, amount
row_number() over (partition by category order by amount) as sales_rank
from book_sales;

عمود التقسيم = الفئة

عمود الطلب = المبلغ

دالة الترتيب =row_number()

يعطينا هذا الاستعلام كافة الصفوف الموجودة في جدول book_sales، ويتم ترتيب الصفوف في كل فئة كتاب، بحيث يكون الكتاب الأكثر مبيعًا هو الصف رقم 1.

نحتاج الآن إلى تصفية صفوف الصف رقم 1 فقط للحصول على الكتب الأكثر مبيعًا في كل فئة

select category, book_name, amount from (
select category, book_name, amount
row_number() over (partition by category order by amount) as sales_rank
from book_sales
) as book_ranked_sales
where sales_rank = 1;

سيوفر لنا الفلتر أعلاه الكتب الأكثر مبيعًا فقط في كل فئة بالإضافة إلى مبلغ البيع الذي حققه كل كتاب من الكتب الأكثر مبيعًا.

السيناريو 7: التقسيم للبحث عن الإجماليات التراكمية في مجموعة

لنفترض أننا نريد حساب الإجمالي الجاري (الإجمالي التراكمي) للبيع عند بيعها. نحن بحاجة إلى إجمالي تراكمي منفصل لكل منتج.

سنقوم بالتقسيم حسب Product_id وفرز القسم حسب التاريخ

select product_id, date, amount,
sum(amount) over (partition by product_id order by date desc) as running_total
from sales_data;

عمود التقسيم = Product_id

عمود الطلب = التاريخ

دالة الترتيب = المبلغ ()

السيناريو 8: التقسيم لمقارنة القيم داخل المجموعة

لنفترض أننا نريد مقارنة راتب كل موظف بمتوسط ​​راتب قسمه.

لذلك سنقوم بتقسيم الموظفين على أساس القسم وإيجاد متوسط ​​الراتب لكل قسم.

ويمكن طرح المتوسط ​​بسهولة من الراتب الفردي للموظف لحساب ما إذا كان راتب الموظف أعلى أو أقل من المتوسط.

select employee_id, salary, department,
avg(salary) over (partition by department) as avg_dept_sal
from employees;

عمود التقسيم = القسم

عمود الطلب = لا يوجد طلب

دالة الترتيب = المتوسط ​​()

السيناريو 9: التقسيم لتقسيم النتائج إلى مجموعات متساوية

لنفترض أننا نريد تقسيم الموظفين إلى 4 مجموعات متساوية (أو شبه متساوية) بناءً على رواتبهم.

لذلك سوف نقوم باشتقاق عمود منطقي آخر Tile_id، والذي سيحتوي على المعرف الرقمي لكل مجموعة من الموظفين.

سيتم إنشاء المجموعات على أساس الراتب - المجموعة الأولى ستحصل على أعلى راتب، وهكذا.

select employee_id, salary,
ntile(4) over (order by salary desc) as tile_id
from employees;

عمود التقسيم = لا يوجد قسم - الجدول الكامل موجود في نفس القسم

عمود الطلب = الراتب

دالة الترتيب = ntile()

السيناريو 10: التقسيم لتحديد الجزر أو الفجوات في البيانات

لنفترض أن لدينا عمود Product_id متسلسلاً، ونريد تحديد الثغرات فيه.

لذا، سنشتق عمودًا منطقيًا Island_id آخر، والذي سيكون له نفس الرقم إذا كان Product_id متسلسلًا. عندما يتم تحديد فاصل في Product_id، تتم زيادة Island_id.

select product_id,
row_number() over (order by product_id) as row_num,
product_id - row_number() over (order by product_id) as island_id,
from products;

عمود التقسيم = لا يوجد قسم - الجدول الكامل موجود في نفس القسم

عمود الطلب = Product_id

دالة الترتيب =row_number()

يتم استخدام التجميع حسب والتقسيم حسب لحل العديد من المشكلات مثل:

تلخيص المعلومات: يتيح لك التجميع تجميع البيانات وتلخيص المعلومات في كل مجموعة.

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

تحليل احصائي: يتيح حساب المقاييس الإحصائية مثل المتوسطات والأعداد والحد الأقصى والحد الأدنى والوظائف التجميعية الأخرى داخل المجموعات.

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

تحليل الجماعة: مفيد في التحليل القائم على الفوج، وتتبع ومقارنة مجموعات الكيانات مع مرور الوقت وما إلى ذلك.
 
 

HANU يدير مدونة HelperCodes والذي يتعامل بشكل رئيسي مع أوراق الغش في SQL. أنا مطور مكدس كامل ومهتم بإنشاء أصول قابلة لإعادة الاستخدام.

• محتوى مدعوم من تحسين محركات البحث وتوزيع العلاقات العامة. تضخيم اليوم.
• PlatoData.Network Vertical Generative Ai. تمكين نفسك. الوصول هنا.
• أفلاطونايستريم. ذكاء Web3. تضخيم المعرفة. الوصول هنا.
• أفلاطون كربون، كلينتك ، الطاقة، بيئة، شمسي، إدارة المخلفات. الوصول هنا.
• أفلاطون هيلث. التكنولوجيا الحيوية وذكاء التجارب السريرية. الوصول هنا.
• المصدر https://www.kdnuggets.com/sql-group-by-and-partition-by-scenarios-when-and-how-to-combine-data-in-data-science?utm_source=rss&utm_medium=rss&utm_campaign=sql-group-by-and-partition-by-scenarios-when-and-how-to-combine-data-in-data-science