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

إتقان دوال التجميع و GROUP BY

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

إتقان دوال التجميع و GROUP BY

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

مراجعة دوال التجميع الأساسية

COUNT(): عد الصفوف أو القيم غير NULL
SUM(): إجمالي القيم الرقمية
AVG(): متوسط القيم الرقمية
MIN(): القيمة الدنيا
MAX(): القيمة القصوى
GROUP_CONCAT(): دمج القيم في سلسلة نصية (خاص بـ MySQL)

GROUP BY مع أعمدة متعددة

جمّع حسب أعمدة متعددة لإنشاء تجميعات متعددة الأبعاد:

-- المبيعات حسب المنطقة والفئة
SELECT
    region,
    category,
    COUNT(*) AS order_count,
    SUM(total_amount) AS total_revenue,
    AVG(total_amount) AS avg_order_value,
    MIN(order_date) AS first_order,
    MAX(order_date) AS last_order
FROM orders
JOIN products ON orders.product_id = products.product_id
GROUP BY region, category
ORDER BY region, total_revenue DESC;

-- المبيعات الشهرية حسب فئة المنتج
SELECT
    YEAR(order_date) AS year,
    MONTH(order_date) AS month,
    category,
    COUNT(DISTINCT order_id) AS orders,
    SUM(quantity * unit_price) AS revenue,
    COUNT(DISTINCT customer_id) AS unique_customers
FROM orders
JOIN order_items ON orders.order_id = order_items.order_id
JOIN products ON order_items.product_id = products.product_id
GROUP BY YEAR(order_date), MONTH(order_date), category
ORDER BY year DESC, month DESC, revenue DESC;

ROLLUP - إنشاء المجاميع الفرعية والمجاميع الكلية

يولد ROLLUP مجاميع فرعية هرمية ومجاميع كلية:

-- المبيعات مع المجاميع الفرعية حسب المنطقة والفئة
SELECT
    region,
    category,
    SUM(total_amount) AS total_sales,
    COUNT(*) AS order_count
FROM orders
JOIN products ON orders.product_id = products.product_id
GROUP BY region, category WITH ROLLUP;

-- تتضمن النتيجة:
-- 1. مجموعات المنطقة + الفئة الفردية
-- 2. المجاميع الفرعية لكل منطقة (الفئة = NULL)
-- 3. المجموع الكلي (المنطقة = NULL، الفئة = NULL)

-- تراكم متعدد المستويات مع COALESCE للقراءة
SELECT
    COALESCE(region, 'TOTAL') AS region,
    COALESCE(category, 'All Categories') AS category,
    COUNT(*) AS orders,
    SUM(total_amount) AS revenue,
    ROUND(AVG(total_amount), 2) AS avg_order_value
FROM orders
JOIN products ON orders.product_id = products.product_id
GROUP BY region, category WITH ROLLUP;

فهم ROLLUP: مع GROUP BY col1, col2 WITH ROLLUP، ينشئ MySQL تجميعات لـ (col1, col2)، (col1, NULL)، و (NULL, NULL). هذا يمنحك مجاميع فرعية في كل مستوى تسلسل هرمي.

دالة GROUPING()

تحدد GROUPING() الصفوف التي هي مجاميع فرعية تم إنشاؤها بواسطة ROLLUP:

-- استخدام GROUPING لتحديد صفوف الملخص
SELECT
    region,
    category,
    SUM(total_amount) AS revenue,
    GROUPING(region) AS is_region_total,
    GROUPING(category) AS is_category_total,
    CASE
        WHEN GROUPING(region) = 1 AND GROUPING(category) = 1 THEN 'Grand Total'
        WHEN GROUPING(category) = 1 THEN 'Region Subtotal'
        ELSE 'Detail'
    END AS row_type
FROM orders
JOIN products ON orders.product_id = products.product_id
GROUP BY region, category WITH ROLLUP;

GROUP_CONCAT() - تجميع السلاسل النصية

يدمج GROUP_CONCAT() قيماً متعددة في سلسلة نصية مفصولة بفواصل:

-- سرد جميع المنتجات المطلوبة من قبل كل عميل
SELECT
    c.customer_name,
    COUNT(DISTINCT o.order_id) AS order_count,
    GROUP_CONCAT(DISTINCT p.product_name ORDER BY p.product_name SEPARATOR ', ') AS products_ordered
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
JOIN order_items oi ON o.order_id = oi.order_id
JOIN products p ON oi.product_id = p.product_id
GROUP BY c.customer_id, c.customer_name
ORDER BY order_count DESC;

-- GROUP_CONCAT متقدم مع تنسيق مخصص
SELECT
    category,
    COUNT(*) AS product_count,
    GROUP_CONCAT(
        CONCAT(product_name, ' ($', ROUND(price, 2), ')')
        ORDER BY price DESC
        SEPARATOR ' | '
    ) AS products_with_prices
FROM products
GROUP BY category;
-- الإخراج: "Premium Widget ($99.99) | Standard Widget ($49.99) | Basic Widget ($19.99)"

حد GROUP_CONCAT: الطول الأقصى الافتراضي هو 1024 حرفاً. قم بزيادته باستخدام SET SESSION group_concat_max_len = 10000; إذا لزم الأمر لمجموعات النتائج الكبيرة.

HAVING مقابل WHERE

يصفي WHERE قبل التجميع، ويصفي HAVING بعد التجميع:

-- الاستخدام الصحيح لـ WHERE و HAVING
SELECT
    category,
    COUNT(*) AS product_count,
    AVG(price) AS avg_price,
    SUM(stock_quantity) AS total_stock
FROM products
WHERE is_active = 1  -- تصفية الصفوف قبل التجميع
GROUP BY category
HAVING AVG(price) > 50  -- تصفية المجموعات بعد التجميع
   AND COUNT(*) >= 5
ORDER BY avg_price DESC;

قاعدة عامة: استخدم WHERE لتصفية الصفوف الفردية بناءً على قيم الأعمدة. استخدم HAVING لتصفية المجموعات بناءً على نتائج التجميع.

أنماط التجميع المتقدمة

التجميع المشروط

-- إعداد تقارير بأسلوب المحور مع التجميع المشروط
SELECT
    category,
    COUNT(*) AS total_products,
    SUM(CASE WHEN price < 50 THEN 1 ELSE 0 END) AS budget_products,
    SUM(CASE WHEN price BETWEEN 50 AND 100 THEN 1 ELSE 0 END) AS mid_range_products,
    SUM(CASE WHEN price > 100 THEN 1 ELSE 0 END) AS premium_products,
    ROUND(AVG(CASE WHEN price < 50 THEN price END), 2) AS avg_budget_price,
    ROUND(AVG(CASE WHEN price > 100 THEN price END), 2) AS avg_premium_price
FROM products
GROUP BY category;

-- مقاييس المبيعات حسب الفترة الزمنية
SELECT
    category,
    SUM(total_amount) AS total_sales,
    SUM(CASE WHEN YEAR(order_date) = 2024 THEN total_amount ELSE 0 END) AS sales_2024,
    SUM(CASE WHEN YEAR(order_date) = 2023 THEN total_amount ELSE 0 END) AS sales_2023,
    ROUND(
        (SUM(CASE WHEN YEAR(order_date) = 2024 THEN total_amount ELSE 0 END) -
         SUM(CASE WHEN YEAR(order_date) = 2023 THEN total_amount ELSE 0 END)) * 100.0 /
        NULLIF(SUM(CASE WHEN YEAR(order_date) = 2023 THEN total_amount ELSE 0 END), 0),
        2
    ) AS yoy_growth_pct
FROM orders
JOIN products ON orders.product_id = products.product_id
GROUP BY category
ORDER BY yoy_growth_pct DESC;

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

-- تحليلات شاملة للعملاء
SELECT
    c.customer_id,
    c.customer_name,
    COUNT(DISTINCT o.order_id) AS total_orders,
    COUNT(DISTINCT oi.product_id) AS unique_products,
    SUM(oi.quantity) AS total_items_purchased,
    SUM(o.total_amount) AS lifetime_value,
    ROUND(AVG(o.total_amount), 2) AS avg_order_value,
    MIN(o.order_date) AS first_order_date,
    MAX(o.order_date) AS last_order_date,
    DATEDIFF(MAX(o.order_date), MIN(o.order_date)) AS customer_lifespan_days,
    ROUND(
        SUM(o.total_amount) / NULLIF(DATEDIFF(MAX(o.order_date), MIN(o.order_date)), 0) * 365,
        2
    ) AS annualized_value
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
JOIN order_items oi ON o.order_id = oi.order_id
GROUP BY c.customer_id, c.customer_name
HAVING total_orders >= 3
ORDER BY lifetime_value DESC
LIMIT 100;

التجميعات الإحصائية

-- التحليل الإحصائي المتقدم
SELECT
    category,
    COUNT(*) AS product_count,
    ROUND(AVG(price), 2) AS mean_price,
    ROUND(STDDEV(price), 2) AS std_deviation,
    ROUND(VARIANCE(price), 2) AS variance,
    MIN(price) AS min_price,
    MAX(price) AS max_price,
    MAX(price) - MIN(price) AS price_range
FROM products
GROUP BY category
ORDER BY mean_price DESC;

التجميعات المتداخلة مع الاستعلامات الفرعية

-- البحث عن الفئات التي تحتوي على عدد منتجات أعلى من المتوسط
SELECT
    category,
    COUNT(*) AS product_count
FROM products
GROUP BY category
HAVING COUNT(*) > (
    SELECT AVG(cat_count)
    FROM (
        SELECT COUNT(*) AS cat_count
        FROM products
        GROUP BY category
    ) AS category_counts
)
ORDER BY product_count DESC;

-- العملاء في أفضل 20% حسب الإنفاق
WITH customer_spending AS (
    SELECT
        customer_id,
        SUM(total_amount) AS total_spent,
        NTILE(5) OVER (ORDER BY SUM(total_amount) DESC) AS spending_quintile
    FROM orders
    GROUP BY customer_id
)
SELECT
    c.customer_name,
    cs.total_spent,
    cs.spending_quintile
FROM customer_spending cs
JOIN customers c ON cs.customer_id = c.customer_id
WHERE cs.spending_quintile = 1
ORDER BY cs.total_spent DESC;

أمثلة إعداد التقارير الواقعية

-- لوحة معلومات المبيعات الشاملة
SELECT
    DATE_FORMAT(o.order_date, '%Y-%m') AS month,
    COUNT(DISTINCT o.order_id) AS total_orders,
    COUNT(DISTINCT o.customer_id) AS unique_customers,
    SUM(oi.quantity) AS items_sold,
    SUM(o.total_amount) AS revenue,
    ROUND(AVG(o.total_amount), 2) AS avg_order_value,
    ROUND(SUM(o.total_amount) / COUNT(DISTINCT o.customer_id), 2) AS revenue_per_customer,
    COUNT(DISTINCT o.order_id) / COUNT(DISTINCT o.customer_id) AS orders_per_customer
FROM orders o
JOIN order_items oi ON o.order_id = oi.order_id
WHERE o.order_date >= DATE_SUB(CURDATE(), INTERVAL 12 MONTH)
GROUP BY DATE_FORMAT(o.order_date, '%Y-%m')
ORDER BY month DESC;

-- مصفوفة أداء المنتج
SELECT
    p.category,
    p.product_name,
    COUNT(DISTINCT oi.order_id) AS times_ordered,
    SUM(oi.quantity) AS units_sold,
    ROUND(SUM(oi.quantity * oi.unit_price), 2) AS total_revenue,
    ROUND(AVG(oi.unit_price), 2) AS avg_selling_price,
    MIN(o.order_date) AS first_sale,
    MAX(o.order_date) AS last_sale,
    DATEDIFF(CURDATE(), MAX(o.order_date)) AS days_since_last_sale
FROM products p
LEFT JOIN order_items oi ON p.product_id = oi.product_id
LEFT JOIN orders o ON oi.order_id = o.order_id
GROUP BY p.product_id, p.category, p.product_name
ORDER BY total_revenue DESC;

-- تحليل الاحتفاظ بالمجموعة
SELECT
    DATE_FORMAT(first_order, '%Y-%m') AS cohort_month,
    COUNT(*) AS cohort_size,
    SUM(CASE WHEN months_active >= 1 THEN 1 ELSE 0 END) AS active_month_1,
    SUM(CASE WHEN months_active >= 3 THEN 1 ELSE 0 END) AS active_month_3,
    SUM(CASE WHEN months_active >= 6 THEN 1 ELSE 0 END) AS active_month_6,
    ROUND(SUM(CASE WHEN months_active >= 3 THEN 1 ELSE 0 END) * 100.0 / COUNT(*), 2) AS retention_3m_pct,
    ROUND(SUM(CASE WHEN months_active >= 6 THEN 1 ELSE 0 END) * 100.0 / COUNT(*), 2) AS retention_6m_pct
FROM (
    SELECT
        customer_id,
        MIN(order_date) AS first_order,
        TIMESTAMPDIFF(MONTH, MIN(order_date), MAX(order_date)) AS months_active
    FROM orders
    GROUP BY customer_id
) AS customer_lifetime
GROUP BY DATE_FORMAT(first_order, '%Y-%m')
ORDER BY cohort_month DESC;

نصائح تحسين الأداء

أفضل الممارسات:

أضف فهارس على أعمدة GROUP BY لأداء أفضل
استخدم WHERE لتصفية البيانات قبل التجميع (يقلل الصفوف للمعالجة)
كن حذراً مع GROUP_CONCAT على مجموعات النتائج الكبيرة
فكر في جداول الملخص للاستعلامات التجميعية المتكررة
استخدم EXPLAIN لتحليل أداء الاستعلام
قم بتحديد مجموعات النتائج باستخدام LIMIT عند الاقتضاء

-- التجميع المحسّن مع الفهرسة الصحيحة
-- توصيات الفهرس:
CREATE INDEX idx_orders_date_customer ON orders(order_date, customer_id);
CREATE INDEX idx_order_items_order_product ON order_items(order_id, product_id);
CREATE INDEX idx_products_category ON products(category);

-- ثم قم بتشغيل التجميع
SELECT
    category,
    COUNT(*) AS order_count,
    SUM(total_amount) AS revenue
FROM orders
JOIN order_items ON orders.order_id = order_items.order_id
JOIN products ON order_items.product_id = products.product_id
WHERE order_date >= '2024-01-01'
GROUP BY category;

تمرين عملي:

التحدي: أنشئ تقارير شاملة باستخدام دوال التجميع:

تقرير الإيرادات الشهرية مع مقارنة على أساس سنوي
أداء فئة المنتج مع المجاميع الفرعية باستخدام ROLLUP
تقسيم العملاء حسب تكرار الشراء والقيمة

الحلول:

-- 1. مقارنة شهرية على أساس سنوي
SELECT
    DATE_FORMAT(order_date, '%Y-%m') AS month,
    SUM(total_amount) AS revenue,
    LAG(SUM(total_amount), 12) OVER (ORDER BY DATE_FORMAT(order_date, '%Y-%m')) AS same_month_last_year,
    ROUND((SUM(total_amount) - LAG(SUM(total_amount), 12) OVER (ORDER BY DATE_FORMAT(order_date, '%Y-%m'))) * 100.0 /
          LAG(SUM(total_amount), 12) OVER (ORDER BY DATE_FORMAT(order_date, '%Y-%m')), 2) AS yoy_growth_pct
FROM orders
GROUP BY DATE_FORMAT(order_date, '%Y-%m')
ORDER BY month DESC;

-- 2. أداء الفئة مع ROLLUP
SELECT
    COALESCE(region, 'ALL REGIONS') AS region,
    COALESCE(category, 'SUBTOTAL') AS category,
    COUNT(*) AS orders,
    SUM(total_amount) AS revenue,
    ROUND(AVG(total_amount), 2) AS avg_order_value
FROM orders
JOIN products ON orders.product_id = products.product_id
GROUP BY region, category WITH ROLLUP;

-- 3. تقسيم العملاء
SELECT
    CASE
        WHEN order_count >= 10 THEN 'High Frequency'
        WHEN order_count BETWEEN 5 AND 9 THEN 'Medium Frequency'
        ELSE 'Low Frequency'
    END AS frequency_segment,
    CASE
        WHEN total_spent >= 5000 THEN 'High Value'
        WHEN total_spent BETWEEN 1000 AND 4999 THEN 'Medium Value'
        ELSE 'Low Value'
    END AS value_segment,
    COUNT(*) AS customer_count,
    ROUND(AVG(total_spent), 2) AS avg_lifetime_value
FROM (
    SELECT customer_id, COUNT(*) AS order_count, SUM(total_amount) AS total_spent
    FROM orders
    GROUP BY customer_id
) AS customer_stats
GROUP BY frequency_segment, value_segment
ORDER BY customer_count DESC;

الملخص

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

GROUP BY متعدد الأعمدة للتجميعات متعددة الأبعاد
ROLLUP للمجاميع الفرعية الهرمية والمجاميع الكلية
دالة GROUPING() لتحديد صفوف الملخص
GROUP_CONCAT() لتجميع السلاسل النصية
HAVING مقابل WHERE للتصفية الصحيحة
التجميع المشروط مع عبارات CASE
استعلامات إعداد التقارير الواقعية المعقدة

تهانينا! لقد أكملت الوحدة 2: الاستعلامات المتقدمة. لديك الآن المهارات لكتابة استعلامات تحليلية متطورة، وإنشاء تقارير شاملة، واستخراج رؤى عميقة من بياناتك. التالي، ستغوص في الوحدة 3: الفهارس والأداء!