GROUP BY، HAVING، ONLY_FULL_GROUP_BY و گزارش‌های تجمیعی

از ردیف‌ها به گزارش

توابع تجمیعی (COUNT، SUM، AVG، MIN، MAX، GROUP_CONCAT) چند ردیف را به یک مقدار تبدیل می‌کنند و GROUP BY تعیین می‌کند که گروه‌ها چطور ساخته شوند.

SELECT d.name AS design,
       COUNT(*)                 AS items,
       SUM(oi.qty)              AS carpets_sold,
       SUM(oi.qty * oi.unit_price) AS revenue_rial,
       ROUND(AVG(oi.unit_price))   AS avg_price
FROM order_items oi
JOIN products p ON p.id = oi.product_id
JOIN designs  d ON d.id = p.design_id
JOIN orders   o ON o.id = oi.order_id
WHERE o.status IN ('paid', 'shipped')               -- فیلتر ردیف‌ها، قبل از گروه‌بندی
GROUP BY d.id, d.name
HAVING SUM(oi.qty) >= 10                           -- فیلتر گروه‌ها، بعد از گروه‌بندی
ORDER BY revenue_rial DESC;

هر شرطی که می‌تواند در WHERE باشد، در WHERE بگذارید؛ HAVING بعد از ساختن همه‌ی گروه‌ها اجرا می‌شود و از ایندکس برای حذف زودهنگام ردیف‌ها کمکی نمی‌گیرد.

ONLY_FULL_GROUP_BY

SELECT customer_id, created_at, SUM(total_amount)
FROM orders GROUP BY customer_id;
-- ERROR 1055: Expression #2 of SELECT list is not in GROUP BY clause
-- and contains nonaggregated column 'orders.created_at'...

MySQL قدیمی این کوئری را اجرا می‌کرد و برای created_at یک مقدار تصادفی از گروه برمی‌گرداند؛ نتیجه‌ای که امروز درست به نظر می‌رسید و فردا عوض می‌شد. از 5.7 حالت ONLY_FULL_GROUP_BY پیش‌فرض است. راه‌حل‌ها:

• ستون را تجمیع کنید: MAX(created_at) AS last_order.
• اگر ستون به کلید گروه وابستگی تابعی دارد، MySQL خودش می‌فهمد: GROUP BY c.id و انتخاب c.full_name مجاز است، چون id کلید اصلی customers است.
• اگر واقعاً فرقی نمی‌کند کدام مقدار بیاید: ANY_VALUE(col).

جدول محوری (Pivot) با تجمیع شرطی

SELECT YEAR(o.created_at) AS y,
  SUM(CASE WHEN o.status = 'paid'      THEN 1 ELSE 0 END) AS paid,
  SUM(o.status = 'shipped')                               AS shipped,   -- کوتاه‌نویسی MySQL
  SUM(o.status = 'cancelled')                             AS cancelled
FROM orders o
GROUP BY y;

در MySQL عبارت منطقی مقدار 1 یا 0 دارد، پس SUM(شرط) تعداد ردیف‌های صادق را می‌شمارد.

جمع جزء و کل با ROLLUP

SELECT IF(GROUPING(ci.province), 'جمع کل', ci.province) AS province,
       IF(GROUPING(ci.name), 'جمع استان', ci.name)     AS city,
       COUNT(*) AS customers
FROM customers c JOIN cities ci ON ci.id = c.city_id
GROUP BY ci.province, ci.name WITH ROLLUP;

تابع GROUPING() (از 8.0) تشخیص می‌دهد NULL خروجی از ROLLUP آمده یا مقدار واقعی NULL است.

نکته‌هایی که کمتر کسی می‌داند

• از MySQL 8، GROUP BY دیگر نتیجه را به‌طور ضمنی مرتب نمی‌کند؛ گزارش‌هایی که به این رفتار 5.7 تکیه کرده بودند بعد از ارتقا نامرتب می‌شوند. همیشه ORDER BY صریح بنویسید.
• COUNT(*) و COUNT(1) در InnoDB هیچ فرقی ندارند؛ اما COUNT(col) ردیف‌های NULL را نمی‌شمارد و معنای دیگری دارد.
• COUNT(*) بدون WHERE روی جدول InnoDB بزرگ کند است، چون InnoDB (برخلاف MyISAM) تعداد ردیف را جایی نگه نمی‌دارد؛ برای عدد تقریبی از information_schema.tables.table_rows استفاده کنید.
• AVG ردیف‌های NULL را نادیده می‌گیرد؛ میانگین تخفیف با NULL به‌جای صفر، عدد بزرگ‌تری از واقعیت نشان می‌دهد. AVG(COALESCE(discount, 0)).
• COUNT(DISTINCT a, b) در MySQL مجاز است و ترکیب‌های یکتا را می‌شمارد؛ ولی ترکیب‌هایی که یکی از اجزایشان NULL است شمرده نمی‌شوند.