تحلیل و دسته‌بندی داده‌ها با CASE و FILTER در PostgreSQL

در این بخش وارد یکی از مفاهیم بسیار کاربردی DQL می‌شویم: عبارات منطقی و فیلتر کردن داده‌ها با CASE و FILTER.
این مفاهیم به شما کمک می‌کنند تا بدون نیاز به JOIN، داده‌ها را به‌صورت تحلیلی، دسته‌بندی‌شده و معنادار بررسی کنید.
در ادامه، آموزش را گام‌به‌گام با مثال‌های عملی از دیتابیس Northwind پیش می‌بریم.

در پایان این بخش، یاد می‌گیرید:

• از دستور CASE برای منطق شرطی در کوئری‌ها استفاده کنید.
• داده‌ها را در گروه‌های سفارشی (مثلاً مناطق یا بازه‌ها) دسته‌بندی کنید.
• از CASE در توابع تجمیعی (SUM, COUNT, AVG) استفاده کنید.
• درصد و نسبت‌های شرطی بسازید.
• با مقادیر NULL در عبارات شرطی به‌درستی کار کنید.
• و در انتها با دستور مدرن‌تر FILTER در پستگرس آشنا شوید.

🔹 دستور شرطی CASE WHEN … THEN … ELSE … END

✳️ مفهوم

عبارت CASE همان “if–then–else” در SQL است و به شما امکان می‌دهد منطق شرطی در داخل کوئری داشته باشید.

ساختار کلی:

CASE
   WHEN شرط۱ THEN نتیجه۱
   WHEN شرط۲ THEN نتیجه۲
   ELSE نتیجه_پیش‌فرض
END

می‌توانید CASE را در قسمت‌های مختلفی از کوئری استفاده کنید:
SELECT, WHERE, ORDER BY, یا حتی داخل توابع تجمیعی.

📘 مثال – دسته‌بندی سفارش‌ها بر اساس منطقه جغرافیایی

SELECT 
    order_id,
    ship_country,
    CASE
        WHEN ship_country IN ('Germany', 'France', 'UK', 'Spain', 'Sweden') THEN 'Europe'
        WHEN ship_country IN ('Argentina', 'Brazil', 'Venezuela') THEN 'South America'
        ELSE 'Other'
    END AS region
FROM orders;

💡 نتیجه: برای هر سفارش، یک ستون جدید به نام region اضافه می‌شود که منطقه کشور مقصد را مشخص می‌کند.

🧩 تمرین‌ها

1. سفارش‌ها را با عنوان 'High Freight' (در صورت freight > 100) یا 'Normal' نمایش دهید.
2. سفارش‌های قبل از ۱۹۹۷ را 'Old' و بقیه را 'Recent' نام‌گذاری کنید.
3. محصولات را بر اساس units_in_stock در سه سطح 'Low Stock', 'Medium', 'High' دسته‌بندی کنید.

🔹 CASE تو در تو و ستون‌های مشتق‌شده (Derived Columns)

گاهی لازم است چندین CASE را در یک دستور با هم استفاده کنید تا خروجی دقیق‌تری برای گزارشات خود بگیرید.

SELECT
    product_name,
    units_in_stock,
    units_on_order,
    CASE
        WHEN units_in_stock = 0 THEN 'Out of Stock'
        WHEN units_in_stock < 20 THEN 'Low'
        ELSE 'Available'
    END AS stock_status,
    CASE
        WHEN discontinued = 1 THEN 'Discontinued'
        ELSE 'Active'
    END AS product_status
FROM products;

📌 دو ستون جدید تولید می‌شود:
stock_status برای وضعیت موجودی و product_status برای فعال یا غیرفعال بودن کالا.

🔹 CASE در ORDER BY

می‌توان با استفاده از CASE، ترتیب خاصی برای سورت کردن داده‌ها ایجاد کرد.

SELECT ship_country, freight
FROM orders
ORDER BY 
   CASE
      WHEN ship_country = 'USA' THEN 1
      WHEN ship_country = 'UK'  THEN 2
      ELSE 3
   END,
   freight DESC;

💡 در این مثال ابتدا سفارش‌های آمریکا، سپس انگلستان و در آخر بقیه کشورها مرتب می‌شوند.

🔹 CASE در توابع تجمیعی (Aggregates)

وقتی می‌خواهید شمارش یا جمع را فقط برای بخشی از داده‌ها حساب کنید، CASE بسیار کاربردی است.

SELECT
    ship_country,
    COUNT(*) AS total_orders,
    SUM(CASE WHEN freight > 100 THEN 1 ELSE 0 END) AS high_freight_orders,
    SUM(CASE WHEN freight <= 100 THEN 1 ELSE 0 END) AS normal_freight_orders
FROM orders
GROUP BY ship_country;

🔸 در اینجا، SUM تنها برای رکوردهایی که شرط در CASE برقرار است جمع می‌شود.

🔹 محاسبه نسبت‌ها و درصدها (Ratios & Percentages)

با ترکیب CASE و توابع تجمیعی می‌توانید درصدها را محاسبه کنید:

SELECT
    ship_country,
    COUNT(*) AS total_orders,
    SUM(CASE WHEN freight > 100 THEN 1 ELSE 0 END) AS high_freight,
    ROUND(100.0 * SUM(CASE WHEN freight > 100 THEN 1 ELSE 0 END) / COUNT(*), 2) AS pct_high_freight
FROM orders
GROUP BY ship_country
ORDER BY pct_high_freight DESC;

💡 خروجی: درصد سفارش‌های با هزینه حمل بالاتر از ۱۰۰ برای هر کشور.

🔹 استفاده از CASE در WHERE یا HAVING

می‌توانید CASE را در فیلترها هم به کار ببرید:

SELECT *
FROM orders
WHERE 
  CASE 
      WHEN ship_country = 'Germany' THEN freight 
      ELSE 0 
  END > 500;

🔹 این کوئری فقط سفارش‌هایی را نشان می‌دهد که کشور مقصد آن آلمان است و freight آنها بیش از ۵۰۰ است.
(در عمل، بهتر است از شرط‌های ساده‌تر استفاده کنید، اما این مثال برای آموزش ساختار منطقی CASE در فیلترهاست.)

🔹 مدیریت NULL در CASE

در صورت وجود مقادیر NULL، می‌توان رفتار را با CASE کنترل کرد:

SELECT
    customer_id,
    CASE
        WHEN fax IS NULL THEN 'No Fax'
        ELSE 'Has Fax'
    END AS fax_status
FROM customers;

✅ به‌روشنی نشان می‌دهد که مقدار NULL را می‌توان به کمک دستور Case به راحتی با مقادیری که برای گزارشات مناسب باشند جایگزین کرد.

🔹 استفاده از دستور FILTER (ویژگی خاص PostgreSQL)

از نسخه‌های جدید PostgreSQL، می‌توانید به‌جای CASE از دستور FILTER برای سادگی و خوانایی استفاده کنید.

✳️ ساختار کلی

aggregate_function(expression) FILTER (WHERE condition)

📘 مثال – جایگزین CASE در Aggregates

SELECT
    ship_country,
    COUNT(*) AS total_orders,
    COUNT(*) FILTER (WHERE freight > 100) AS high_freight_orders,
    COUNT(*) FILTER (WHERE freight <= 100) AS normal_freight_orders,
    ROUND(100.0 * COUNT(*) FILTER (WHERE freight > 100) / COUNT(*), 2) AS pct_high_freight
FROM orders
GROUP BY ship_country
ORDER BY pct_high_freight DESC;

💡 همان نتیجه‌ی قبل، ولی خواناتر و استانداردتر.

✅ جمع‌بندی کاربردهای دستور Case و Filter