SQL زبانی برای پرس‌وجو و کار با دادهٔ رابطه‌ای است و برای تحلیلگر، پلی میان سؤال کسب‌وکار و جدول‌های واقعی محسوب می‌شود. توانایی نوشتن SELECT کافی نیست؛ باید بدانید هر سطر چیست، جدول‌ها چگونه به هم وصل می‌شوند و آیا خروجی دوباره‌شماری دارد یا نه. یک کوئری که بدون خطا اجرا می‌شود ممکن است درآمد را چند برابر گزارش کند، اگر رابطهٔ جدول‌ها درست فهمیده نشده باشد.
این آموزش با مشتری و سفارش پیش می‌رود. هدف، ساخت گزارشی از فروش قطعی هر مشتری است؛ گزارشی که مشتریِ بدون سفارش را هم در صورت نیاز حفظ کند. مثال‌ها عمومی‌اند، ولی جزئیات بعضی قابلیت‌ها میان SQLite، PostgreSQL و دیگر موتورهای پایگاه داده تفاوت دارد. پیش از استفادهٔ واقعی، نحو و رفتار را با مستندات موتور خود تطبیق دهید و تحلیل را روی دادهٔ تمرینی اجرا کنید.
ابتدا سطح هر جدول را تعریف کنید
جدول customers معمولاً یک ردیف برای هر مشتری دارد و orders یک ردیف برای هر سفارش. اگر اقلام سفارش در جدول order_items باشند، هر ردیف آن یک قلم است، نه کل سفارش. این تفاوت روی شمارش و جمع اثر بنیادی دارد. پیش از کوئری، کلید اصلی، کلید خارجی و انتظار یکتایی هر ستون مهم را مشخص کنید تا رابطه‌ها به‌جای حدس، قرارداد روشن داشته باشند.
نام ستون به‌تنهایی معنا را تضمین نمی‌کند. amount ممکن است مبلغ قبل از تخفیف، مبلغ پرداخت‌شده یا ماندهٔ بدهی باشد. تاریخ هم می‌تواند تاریخ ایجاد، پرداخت یا ارسال باشد. گزارش «فروش ماهانه» بدون تعیین این قرارداد کامل نیست. اگر منبع داده مستند نیست، چند سفارش را از ابتدا تا پایان بررسی کنید و تعریف شاخص را بنویسید؛ هیچ تابع تجمیعی جای این مرحله را نمی‌گیرد.
SELECT و WHERE؛ انتخاب ستون و ردیف
SELECT ستون‌های خروجی را مشخص می‌کند و WHERE ردیف‌ها را پیش از تجمیع فیلتر می‌کند. برای گزارش فقط ستون‌های لازم را بخوانید؛ SELECT * وابستگی پنهان به ساختار جدول می‌سازد و می‌تواند دادهٔ غیرضروری یا حساس را وارد خروجی کند. نام مستعار با AS نیز کمک می‌کند ستون محاسبه‌شده معنای روشن داشته باشد و مخاطب مجبور به حدس‌زدن نشود.
SELECT id, customer_id, amount
FROM orders
WHERE status = 'paid'
ORDER BY amount DESC, id ASC;ORDER BY ترتیب را تضمین می‌کند؛ بدون آن نباید به ترتیب ظاهری ردیف‌ها اعتماد کنید. اگر چند مقدار برابرند و خروجی صفحه‌بندی می‌شود، مرتب‌سازی ثانویه با کلید یکتا اهمیت دارد. برای بازهٔ تاریخ نیز معمولاً شروع شامل و پایان غیرشامل، قرارداد روشنی می‌سازد. این شیوه از ابهام ساعت آخر روز و تفاوت دقت زمانی جلوگیری می‌کند، به شرط اینکه منطقهٔ زمانی مشخص باشد.
NULL با صفر و رشتهٔ خالی فرق دارد
NULL معمولاً نبود یا نامعلوم‌بودن مقدار را بیان می‌کند. مقایسه با = NULL درست نیست؛ از IS NULL یا IS NOT NULL استفاده کنید. منطق سه‌ارزشی SQL باعث می‌شود بعضی شرط‌ها در حضور NULL نتیجهٔ نامعلوم داشته باشند و ردیف از WHERE عبور نکند. اگر این رفتار را نشناسید، ممکن است بخشی از داده بی‌سروصدا از تحلیل حذف شود.
تابع COALESCE اولین مقدار غیرتهی را برمی‌گرداند و برای نمایش مقدار جایگزین مفید است. اما جایگزین‌کردن NULL با صفر باید معنای درست داشته باشد. مبلغ ثبت‌نشده لزوماً فروش صفر نیست. همچنین COUNT(*) همهٔ سطرها را می‌شمارد و COUNT(column) معمولاً مقدارهای NULL آن ستون را نادیده می‌گیرد. تفاوت این دو برای گزارش تعداد دادهٔ معتبر و کنترل کیفیت بسیار کاربردی است.
GROUP BY و شاخص‌های تجمیعی
GROUP BY ردیف‌ها را بر اساس کلیدهای مشخص گروه‌بندی می‌کند. سپس SUM، AVG، MIN، MAX یا COUNT روی هر گروه محاسبه می‌شود. هر ستون غیرتجمیعی خروجی باید با قواعد موتور سازگار و از نظر تحلیلی قابل‌تعیین باشد. اتکا به رفتار آسان‌گیرانهٔ یک موتور برای انتخاب ستون نامشخص، گزارش را مبهم و انتقال به پایگاه دادهٔ دیگر را دشوار می‌کند.
SELECT customer_id,
COUNT(*) AS paid_orders,
SUM(amount) AS paid_amount
FROM orders
WHERE status = 'paid'
GROUP BY customer_id
HAVING SUM(amount) >= 1000
ORDER BY paid_amount DESC, customer_id;HAVING پس از شکل‌گیری گروه‌ها شرط اعمال می‌کند، در حالی که WHERE ردیف‌های اولیه را محدود می‌کند. جابه‌جاکردن این دو می‌تواند سؤال را عوض کند. مثلاً انتخاب سفارش‌های بالای هزار و بعد جمع آن‌ها با انتخاب مشتریانی که مجموع سفارششان بالای هزار است یکی نیست. قبل از نوشتن شرط، آن را با زبان معمولی بیان کنید و روی داده‌ای کوچک، تفاوت دو برداشت را نشان دهید.
JOIN؛ از رابطه شروع کنید، نه از نحو
INNER JOIN فقط تطبیق‌ها را نگه می‌دارد. LEFT JOIN همهٔ ردیف‌های سمت چپ و تطبیق‌های سمت راست را حفظ می‌کند. اگر می‌خواهید مشتری بدون سفارش هم در گزارش باشد، LEFT JOIN معمولاً مناسب‌تر است. اما یک شرط روی جدول راست در WHERE می‌تواند ردیف‌های بدون تطبیق را حذف کند و رفتار حفظ مشتری را ناخواسته از بین ببرد.
شرط وضعیت سفارش را در ON قرار دهید اگر قرار است مشتری بی‌سفارش باقی بماند. سپس توجه کنید COUNT(*) در چنین گزارشی برای مشتری بدون سفارش هم یک ردیف می‌شمارد. برای تعداد سفارش، COUNT(o.id) مناسب‌تر است، مشروط به اینکه id سفارش تهی نباشد. این مثال نشان می‌دهد منطق NULL و JOIN جدا از هم نیستند و باید در کنار سؤال تحلیل فهمیده شوند.
SELECT c.id,
c.name,
COUNT(o.id) AS paid_orders,
COALESCE(SUM(o.amount), 0) AS paid_amount
FROM customers AS c
LEFT JOIN orders AS o
ON o.customer_id = c.id AND o.status = 'paid'
GROUP BY c.id, c.name
ORDER BY c.id;چرا جمع پس از اتصال چند برابر می‌شود؟
اگر یک سفارش چند قلم داشته باشد، اتصال orders به order_items مبلغ کل سفارش را در چند ردیف تکرار می‌کند. جمع amount سفارش بعد از این اتصال، درآمد را زیاد نشان می‌دهد. استفادهٔ بی‌فکر از DISTINCT راه‌حل عمومی نیست؛ دو سفارش واقعی می‌توانند مبلغ برابر داشته باشند و حذف مقدارهای برابر دادهٔ درست را از بین ببرد. باید سطح محاسبه را با سطح ردیف هماهنگ کنید.
دو راه رایج، تجمیع اقلام پیش از اتصال یا محاسبهٔ شاخص در سطح درست است. برای کنترل، تعداد سطر و تعداد کلید یکتا را قبل و بعد ببینید. همچنین چند سفارش با یک، دو و چند قلم را دستی بررسی کنید. اگر رابطهٔ چندبه‌چند ناخواسته است، آن را در منبع یا شرط اتصال اصلاح کنید. گزارش قابل‌اعتماد باید علت هر افزایش تعداد ردیف را توضیح دهد.
CTE برای خوانایی مرحله‌های تحلیل
با WITH می‌توانید یک نتیجهٔ میانی نام‌دار تعریف کنید. این کار برای جداکردن سفارش‌های معتبر، تجمیع اولیه و گزارش نهایی مفید است. CTE الزاماً جدول ذخیره‌شده یا تضمین بهبود سرعت نیست؛ رفتار به موتور و برنامهٔ اجرا وابسته است. مزیت اصلی در بسیاری از گزارش‌ها، روشن‌کردن گام‌های منطقی و امکان بررسی جداگانهٔ هر مرحله است.
نام‌های معنادار انتخاب کنید و از ساختن زنجیره‌ای طولانی از مرحله‌های بی‌فایده بپرهیزید. برای یک سؤال ساده، کوئری ساده بهتر است. اما وقتی چند تعریف تجاری در گزارش وجود دارد، جداکردن هر تعریف می‌تواند بازبینی را آسان کند. خروجی میانی را با تعدادها و جمع‌های موردانتظار بسنجید تا خطا به آخرین جدول منتقل و در آن پنهان نشود.
توابع پنجره‌ای و تفاوت با گروه‌بندی
تابع پنجره‌ای می‌تواند محاسبه‌ای گروهی انجام دهد و در عین حال ردیف‌های اصلی را حفظ کند. برای مثال، مجموع تجمعی فروش یا رتبهٔ سفارش هر مشتری را می‌توان بدون تبدیل همهٔ سفارش‌ها به یک ردیف محاسبه کرد. PARTITION BY محدودهٔ گروه و ORDER BY ترتیب داخل آن را تعیین می‌کند. معنای پنجره و نحوهٔ برخورد با مقدارهای برابر را صریح بررسی کنید.
در مجموع تجمعی، تعیین قاب ROWS می‌تواند از رفتار ناخواسته هنگام تاریخ‌های برابر جلوگیری کند. رتبه‌های ROW_NUMBER، RANK و DENSE_RANK هم در تساوی رفتار متفاوت دارند. انتخاب را بر اساس سؤال انجام دهید: دقیقاً یک سفارش آخر می‌خواهید یا همهٔ سفارش‌های هم‌رتبه؟ اگر آخرین رویداد مهم است، زمان به‌تنهایی شاید یکتا نباشد و کلید ثانویه لازم شود.
امنیت ورودی و دسترسی فقط‌خواندنی
اگر مقدار جست‌وجو از کاربر می‌آید، آن را با اتصال رشته‌ای داخل SQL نگذارید. از پارامترهای آماده‌شدهٔ درایور استفاده کنید. این روش هم از تزریق SQL جلوگیری می‌کند و هم قرارداد نوع و مقدار را روشن‌تر می‌سازد. نام جدول یا ستون معمولاً با پارامتر مقدار جایگزین نمی‌شود؛ برای بخش‌های ساختاری پویا باید از فهرست مجاز و محدود استفاده کرد.
برای تحلیل، حساب فقط‌خواندنی و محیط مناسب انتخاب کنید. اجرای آزمایشی DELETE یا UPDATE روی دادهٔ اصلی بخشی از یادگیری SQL نیست. حتی SELECT سنگین می‌تواند فشار ایجاد کند؛ ابتدا روی نمونهٔ کوچک یا نسخهٔ تحلیلی کار کنید و از خواندن ستون‌های غیرضروری خودداری کنید. اطلاعات شخصی و محرمانه را در خروجی تمرینی، فایل عمومی یا مخزن قرار ندهید؛ قابلیت دسترسی به داده، مجوز انتشار آن نیست.
شاخص، برنامهٔ اجرا و اندازه‌گیری
شاخص می‌تواند بعضی جست‌وجوها و اتصال‌ها را سریع‌تر کند، اما برای همهٔ کوئری‌ها معجزه نمی‌کند و هزینهٔ ذخیره‌سازی و نوشتن دارد. پیش از تغییر ساختار، برنامهٔ اجرا را با ابزار مناسب موتور بررسی کنید. نوع ستون، انتخاب‌پذیری شرط و حجم داده بر تصمیم بهینه‌ساز اثر دارند. نتیجهٔ یک جدول ده‌ردیفی نمایندهٔ عملکرد روی میلیون‌ها ردیف نیست.
از پیچیده‌کردن زودهنگام کوئری به نام سرعت پرهیز کنید. ابتدا صحت گزارش را روی نمونهٔ دستی ثابت کنید، سپس گلوگاه را اندازه بگیرید. اگر فقط بخشی از داده لازم است، فیلتر و بازهٔ زمانی مناسب بگذارید. در محیط مشترک، تغییر شاخص یا تنظیمات پایگاه داده باید با مجوز و برنامهٔ اثرسنجی انجام شود؛ یک تمرین تحلیلی دلیل کافی برای تغییر زیرساخت تولید نیست.
کنترل صحت با جدول‌های کوچک
برای هر کوئری مهم، داده‌ای کوچک بسازید که خطای محتمل را آشکار کند. یک مشتری بدون سفارش، دو سفارش با مبلغ برابر و سفارشی با چند قلم، سه نمونهٔ مفیدند. اگر فقط دادهٔ معمولی داشته باشید، استفادهٔ اشتباه از DISTINCT یا COUNT ممکن است دیده نشود. انتظار خروجی را پیش از اجرای کوئری بنویسید تا نتیجهٔ فعلی برنامه به‌اشتباه پاسخ مرجع نشود.
کنترل‌های جمعی نیز ارزشمندند. مجموع فروش مشتریان باید با مجموع فروش معتبر در سطح سفارش سازگار باشد، مگر اینکه فیلتر یا دامنهٔ متفاوتی عمداً تعریف کرده باشید. تعداد شناسه‌های یکتا و تعداد سطر را کنار هم ببینید. اختلاف آن‌ها همیشه خطا نیست، اما باید توضیح داشته باشد. در گزارش چندمرحله‌ای، این کنترل را در هر مرحله انجام دهید تا محل شروع دوباره‌شماری پیدا شود.
وقتی داده از چند جدول و زمان دریافت متفاوت می‌آید، سازگاری زمانی را هم بررسی کنید. ممکن است سفارش ثبت شده باشد ولی اطلاعات مشتری هنوز در نسخهٔ دریافتی نباشد. انتخاب INNER JOIN در چنین وضعی داده را حذف می‌کند، نه اینکه ناسازگاری را حل کند. برای گزارش حساس، زمان snapshot یا قرارداد به‌روزرسانی منابع مهم است. کیفیت تحلیل فقط به درستی عبارت SQL محدود نمی‌شود و به وضعیت دادهٔ خوانده‌شده نیز بستگی دارد.
اگر نتیجه با گزارش قبلی فرق کرد، ابتدا تعریف شاخص، بازه، وضعیت و سطح ردیف را مقایسه کنید. اختلاف همیشه نشانهٔ خرابی کوئری جدید نیست؛ شاید گزارش قدیمی تعریف دیگری داشته است. مقایسهٔ مرحله‌به‌مرحله و چند نمونهٔ مشخص، از بحث بر سر یک عدد نهایی مفیدتر است. هدف رسیدن به تعریف مشترک و محاسبهٔ قابل‌تکرار است، نه صرفاً برابرکردن خروجی با عددی که قبلاً پذیرفته شده است.
تمرین پایانی و پرسش‌های متداول
سه مشتری بسازید: یکی دو سفارش پرداخت‌شده، یکی سفارش لغوشده و دیگری بدون سفارش. سپس جدولی از اقلام اضافه کنید که یک سفارش دو قلم داشته باشد. گزارش باید فروش قطعی را درست حساب کند، مشتری بدون خرید را در نسخهٔ LEFT JOIN حفظ کند و درآمد را پس از اتصال دو برابر نکند. انتظار خروجی را پیش از اجرا روی کاغذ بنویسید.
آیا برای تحلیل داده باید همهٔ SQL را یاد بگیرم؟
نه. SELECT، فیلتر، تجمیع، JOIN و درک NULL پایهٔ قوی می‌سازند. سپس CTE و توابع پنجره‌ای را با سؤال‌های واقعی تمرین کنید. مدیریت سرور، طراحی تراکنش پیچیده و همهٔ امکانات یک موتور شرط شروع تحلیل نیستند. مهم‌تر از تعداد دستورها، توانایی توضیح سطح هر ردیف و درستی اتصال‌هاست.
SQL جای Pandas را می‌گیرد؟
این ابزارها مکمل‌اند. SQL برای فیلتر و تجمیع نزدیک به پایگاه داده مناسب است و Pandas برای تبدیل‌های تحلیلی و اتصال به ابزارهای پایتون انعطاف دارد. بهتر است دادهٔ لازم را انتخاب کنید و سپس تحلیل بعدی را انجام دهید، نه اینکه همیشه کل پایگاه را به حافظه بیاورید. نتیجهٔ دو روش را روی نمونهٔ کوچک با هم مقایسه کنید.
چرا COUNT و SUM گزارش‌های متفاوت می‌دهند؟
زیرا اولی تعداد و دومی مقدار را خلاصه می‌کند و هر دو به سطح ردیف و NULL حساس‌اند. تعداد قلم با تعداد سفارش فرق دارد و جمع مبلغ در اتصال نادرست تکرار می‌شود. برای ادامه، راهنمای پاک‌سازی Pandas و مصورسازی داده را بخوانید تا خروجی کوئری را به گزارشی قابل‌آزمون و قابل‌فهم تبدیل کنید.
منابع و مطالعهٔ بیشتر
برای بررسی جزئیات و ادامهٔ مطالعه؛ متن این راهنما مستقلاً برای نوونتیکس نوشته شده است.