C# Geeks (.NET) on Telegram: “🗄 10 قابلیت کمترشناخته‌شده SQL که هر Developer…”

🗄 10 قابلیت کمترشناخته‌شده SQL که هر Developer باید بداند(پارت 3️⃣)

📊 4. ءGROUPING SETS، ROLLUP و CUBE

یک Report اغلب به چندین سطح از Summary به‌صورت هم‌زمان نیاز دارد: مجموع‌ها بر اساس Carrier و Status، Subtotalهای هر Carrier و یک Grand Total.
روش ساده این است که چند Query را با استفاده از UNION ALL به یکدیگر متصل کنیم.
ءGROUPING SETS، ROLLUP و CUBE تمام این سطوح را در یک Query تولید می‌کنند.

SELECT
    carrier,
    status,
    COUNT(*) AS shipment_count,
    SUM(si.quantity) AS total_quantity
FROM shipments.shipments s
LEFT JOIN shipments.shipment_items si
    ON s.id = si.shipment_id
GROUP BY GROUPING SETS (
    (carrier, status), -- by carrier & status
    (carrier),         -- subtotal by carrier
    (status),          -- subtotal by status
    ()                 -- grand total
);

SELECT
    carrier,
    status,
    DATE_TRUNC('month', created_at) AS month,
    COUNT(*) AS shipment_count
FROM shipments.shipments
GROUP BY ROLLUP (
    carrier,
    status,
    DATE_TRUNC('month', created_at)
);

ءQuery اول دقیقاً Groupingهایی را فهرست می‌کند که به آن‌ها نیاز دارید: بر اساس Carrier و Status، فقط بر اساس Carrier، فقط بر اساس Status و () خالی برای Grand Total.
ءROLLUP در Query دوم، شکل کوتاه‌شده‌ای برای Subtotalهای سلسله‌مراتبی است: Carrier، سپس Carrier و Status، بعد Carrier و Status و Month، و در نهایت Total.
ءCUBE تمام ترکیب‌های ممکن از Columnها را تولید می‌کند.
یک Query جایگزین چهار Query می‌شود و Database سطوح مختلف را در یک Pass محاسبه می‌کند، به‌جای اینکه Table را چندین بار Scan کند.
این قابلیت‌ها بخشی از Standard SQL هستند و در PostgreSQL، SQL Server و Oracle کار می‌کنند.

🧮 5. ءFILTER Clause در Aggregateها

اغلب لازم است فقط Rowهایی را Count یا Sum کنید که یک شرط مشخص را دارند و نتیجه‌ی آن‌ها را به‌صورت جداگانه و کنار هم نمایش دهید.
عبارت FILTER یک شرط را روی یک Aggregate مشخص اعمال می‌کند؛ بنابراین هر Aggregate یک Subset متفاوت را Count می‌کند — همه در یک Row و در یک Pass روی داده‌ها.

SELECT
    carrier,
    COUNT(*) AS total_shipments,
    COUNT(*) FILTER (
        WHERE status = 'delivered'
    ) AS delivered_count,
    COUNT(*) FILTER (
        WHERE status = 'in_transit'
    ) AS in_transit_count,
    COUNT(*) FILTER (
        WHERE status = 'pending'
    ) AS pending_count,
    SUM(si.quantity) FILTER (
        WHERE status = 'delivered'
    ) AS delivered_quantity,
    SUM(si.quantity) FILTER (
        WHERE status = 'pending'
    ) AS pending_quantity
FROM shipments.shipments s
LEFT JOIN shipments.shipment_items si
    ON s.id = si.shipment_id
GROUP BY carrier;

هر COUNT(*) FILTER (WHERE ...) فقط Rowهای مطابق با شرط را Count می‌کند؛ بنابراین برای هر Carrier، تعداد Shipmentهای Delivered، In-Transit و Pending را در Columnهای جداگانه دریافت می‌کنید.
عبارت زیر:

COUNT(*) FILTER (WHERE status = 'delivered')

خواناتر از روش قدیمی استفاده از CASE است:

SUM(
    CASE
        WHEN status = 'delivered' THEN 1
        ELSE 0
    END
)

هدف و منظور عبارت FILTER بسیار واضح‌تر است.

📌 نکته: FILTER توسط PostgreSQL پشتیبانی می‌شود. SQL Server و MySQL این قابلیت را ندارند؛ در آن Databaseها باید از CASE داخل Aggregate استفاده کنید، مانند:

COUNT(
    CASE
        WHEN status = 'delivered' THEN 1
    END
)