گروه‌بندی حرفه‌ای: FILTER، ROLLUP، CUBE و GROUPING SETS

گزارش چندسطحی در یک کوئری

FILTER به‌جای SUM(CASE ...)

گزارش رایج: برای هر شهر، تعداد کل سفارش‌ها، تحویل‌شده‌ها و لغوشده‌ها. به‌جای چند زیرکوئری یا CASEهای طولانی:

SELECT c.city,
       count(*)                                           AS all_orders,
       count(*) FILTER (WHERE o.status = 'delivered')     AS delivered,
       count(*) FILTER (WHERE o.status = 'cancelled')     AS cancelled,
       round(100.0 * count(*) FILTER (WHERE o.status = 'cancelled') / count(*), 1) AS cancel_pct
FROM orders o JOIN customers c ON c.id = o.customer_id
WHERE o.created_at >= timestamptz '2026-03-21 00:00+03:30'
GROUP BY c.city
HAVING count(*) >= 10
ORDER BY all_orders DESC;

ROLLUP: جمع‌های میانی و کل

SELECT coalesce(c.city, 'جمع کل') AS city,
       coalesce(d.title, 'جمع شهر') AS design,
       sum(oi.qty) AS pieces,
       sum(oi.line_total_rial) AS revenue,
       GROUPING(c.city, d.title) AS lvl
FROM order_items oi
JOIN orders o    ON o.id = oi.order_id
JOIN customers c ON c.id = o.customer_id
JOIN designs d   ON d.code = oi.design_code
GROUP BY ROLLUP (c.city, d.title)
ORDER BY c.city NULLS LAST, lvl, revenue DESC;

ROLLUP (a, b) معادل سه گروه‌بندی است: (a, b)، (a) و () یعنی جمع کل. تابع GROUPING() یک عدد بیتی برمی‌گرداند که نشان می‌دهد کدام ستون‌ها در این ردیف «جمع زده شده‌اند»؛ به این ترتیب ردیف جمع از NULL واقعی داده قابل تشخیص است.

CUBE و GROUPING SETS

-- فروش به تفکیک شهر، به تفکیک شانه، و جمع کل؛ در یک پیمایش
SELECT c.city, d.reed, sum(oi.line_total_rial)
FROM order_items oi
JOIN orders o ON o.id = oi.order_id
JOIN customers c ON c.id = o.customer_id
JOIN designs d ON d.code = oi.design_code
GROUP BY GROUPING SETS ((c.city), (d.reed), ());

aggregateهای کاربردی

SELECT o.id,
       string_agg(oi.design_code, '، ' ORDER BY oi.id) AS designs,
       percentile_cont(0.5) WITHIN GROUP (ORDER BY oi.unit_price_rial) AS median_price,
       bool_and(oi.qty >= 10) AS all_wholesale
FROM orders o JOIN order_items oi ON oi.order_id = o.id
GROUP BY o.id;

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

• اگر بر اساس کلید اصلی گروه‌بندی کنید، می‌توانید بقیه‌ی ستون‌های همان جدول را بدون آوردن در GROUP BY انتخاب کنید (وابستگی تابعی): GROUP BY c.id و سپس c.full_name, c.city در SELECT.
• از نسخه‌ی 16 تابع any_value(col) یک مقدار دلخواه از گروه برمی‌گرداند؛ جایگزین استاندارد ترفند min(col) وقتی همه‌ی مقادیر یکسان‌اند.
• FILTER روی هر aggregateی کار می‌کند، نه فقط count: array_agg(code) FILTER (WHERE colors > 8).
• count(DISTINCT x) همیشه مرتب‌سازی دارد و روی میلیون‌ها ردیف کند است؛ گاهی SELECT count(*) FROM (SELECT DISTINCT x ...) t پلن بهتری (HashAggregate) می‌گیرد.
• تقسیم دو عدد صحیح در درصدگیری صفر می‌دهد؛ همیشه یکی را numeric کنید (100.0 *) و برای جلوگیری از تقسیم بر صفر از NULLIF(count(*), 0) در مخرج استفاده کنید.