SQL حرفه‌ای: تاریخ، رشته، COALESCE، ایندکس و اشتباه‌های رایج

کارهای روزمره‌ی تحلیل (ماه، رشته، مقدار پیش‌فرض) را بنویسی، بدانی ایندکس کجا کمک می‌کند و اشتباه‌های پرتکرار را بشناسی.

16 دقیقه مطالعه · 6 سؤال تمرینی · SQL از صفر: پرس‌وجو برای تحلیل داده

تاریخ، رشته و جایگزین کردن NULL

گزارش‌های واقعی معمولاً «ماهانه» یا «به تفکیک شهر» هستند و داده هرگز تمیز نیست:

-- سفارش‌های هر ماه
SELECT DATE_TRUNC('month', order_date)::date AS month, COUNT(*) AS orders
FROM orders
GROUP BY 1
ORDER BY 1;

-- عبارت رشته‌ای و مقدار پیش‌فرض برای NULL
SELECT UPPER(name) AS name, COALESCE(city, 'unknown') AS city
FROM customers;
  • DATE_TRUNC('month', تاریخ) تاریخ را به اول ماه می‌برد؛ EXTRACT(month FROM تاریخ) فقط شماره‌ی ماه را می‌دهد.
  • UPPER، LOWER، LENGTH و الحاق با || در PostgreSQL (در MySQL CONCAT).
  • COALESCE(a, b, ...) اولین مقدار غیر NULL را می‌دهد. برای «اگر شهر نامعلوم بود، unknown نشان بده».
  • به‌خاطر داشته باشید: نام تابع‌های تاریخ و رشته بین PostgreSQL، MySQL و SQLite فرق دارد. منطق (SELECT، JOIN، GROUP BY، پنجره) یکی است؛ جزئیات تاریخ را در مستندات پایگاه‌تان ببینید.

ایندکس و EXPLAIN: چرا یک پرس‌وجو کند است؟

اگر جدول سفارش‌ها یک میلیون ردیف داشته باشد، پرس‌وجوی WHERE customer_id = 1 بدون کمک باید همه‌ی ردیف‌ها را بخواند (Seq Scan). ایندکس مثل فهرست آخر کتاب است: ساختاری مرتب (معمولاً B-tree) که پیدا کردن مقدار را بدون خواندن همه‌چیز ممکن می‌کند:

CREATE INDEX idx_orders_customer_id ON orders (customer_id);
  • ایندکس برای ستون‌هایی می‌ارزد که زیاد در WHERE، JOIN ... ON و ORDER BY می‌آیند. کلید اصلی خودکار ایندکس دارد، کلید خارجی معمولاً نه (خودتان بسازید).
  • هزینه دارد: فضا و کندتر شدن INSERT/UPDATE. روی همه‌ی ستون‌ها ایندکس نمی‌سازیم.
  • EXPLAIN برنامه‌ی اجرای پرس‌وجو را نشان می‌دهد (و EXPLAIN ANALYZE زمان واقعی را). به دنبال Seq Scan روی جدول بزرگ باشید.
  • ایندکس را بی‌اثر نکنید: WHERE LOWER(name) = 'sara' یا WHERE price * 2 > 100 از ایندکس ستون خام استفاده نمی‌کند (روی ستون تابع بزنید یا شرط را بازنویسی کنید)، و LIKE '%ra' با علامت درصد در ابتدا هم نمی‌تواند.

فهرست اشتباه‌های رایج

اکثر خطاهای تحلیلی (نه خطاهای نحوی که پایگاه داده خودش می‌گیرد) از همین‌هاست. این فهرست را مرور کنید:

  1. = NULL به‌جای IS NULL، و شرط‌هایی که بی‌صدا ردیف‌های NULL را حذف می‌کنند (<>).
  2. COUNT(*) بعد از LEFT JOIN به‌جای شمارش ستون جدول راست.
  3. NOT IN با زیرپرس‌وجویی که NULL دارد ← NOT EXISTS.
  4. تکثیر ردیف بعد از JOIN و شمردن/جمع زدن دوباره‌ی مقدارهای سطح بالاتر.
  5. JOIN بدون شرط (ضرب دکارتی).
  6. تقسیم صحیح (7 / 2 = 3).
  7. شرط تجمیعی در WHERE به‌جای HAVING، یا نام مستعار SELECT در WHERE.
  8. SELECT * در کد برنامه.
  9. ساختن SQL با چسباندن رشته‌ی ورودی کاربر ← راه درست پرس‌وجوی پارامتری است (WHERE email = $1)، وگرنه به SQL Injection باز است.

منابع و ادامه‌ی مسیر

قدم بعد: یک پایگاه داده‌ی واقعی نصب کن (PostgreSQL یا SQLite) و همین جدول‌های فروشگاه را بساز؛ هر پرس‌وجوی این دوره را خودت تایپ کن و نتیجه را با پیش‌بینی ذهنی‌ات مقایسه کن. این دوره «فهمیدن» را می‌سازد و مرور فاصله‌دار آن را نگه می‌دارد؛ مهارت انگشت‌ها را فقط تمرین عملی می‌سازد.

تمرین‌های این درس را حل کن (رایگان)