ترفندهای SQL برای دانشمندان داده – راهنمای کاربردی

۱۴۰۳/۱۱/۳۰

۷ دقیقه

۹۳۸

آموزش متنی جامع

در این مطلب، نکات و ترفندهای SQL برای دانشمندان داده به منظور کار کردن سریع‌تر و بهتر با داده‌های موجود در پایگاه داده، آموزش داده شده است.

فهرست مطالب این نوشته

ترفندهای SQL برای دانشمندان داده

دستور COALESCE()‎ برای بازکدگذاری داده‌های نال/ناموجود

محاسبه توزیع تجمعی و کل اجرا

پیدا کردن رکوردهایی با مقادیر فوق‌العاده، بدون استفاده از Self Joining

دستور WHERE شرطی

دستورات Lag()‎ و Lead()‎ برای کار با سطرهای متوالی

یکپارچه‌سازی کوئری SQL با پایتون و R

عبارات با قاعده در SQL

ترفندهای SQL برای دانشمندان داده

«دانشمندان داده» (Data Scientists) و «تحلیل‌گران داده» (Data Analysts) باید SQL (سرنام عبارت Structured Query Language که به فارسی به آن زبان پرسمان ساخت‌یافته گفته می‌شود) را بدانند و در واقع، با این مورد آشنایی کامل و حتی در بسیاری از مواقع، بر آن تسلط داشته باشند.

فیلم آموزش Power BI برای تحلیل‌ داده – مقدماتی در فرادرس

کلیک کنید

در واقع، کلیه افرادی که با داده‌ها سر و کار دارند و کارهای تحلیلی انجام می‌دهند، باید SQL بدانند. در بسیاری از آگهی‌های استخدامی که برای جذب مهندسان، دانشمندان و تحلیل‌گران داده منتشر می‌شود، آشنایی با مفاهیم پایگاه داده و در بسیاری موارد تسلط بر نوع خاصی از پایگاه داده‌ها مثلا پایگاه داده‌های گراف و غیره نیز به عنوان نیازمندی‌های کار بیان شده است.

این در حالی است که از دیدگاه برخی از افراد، مهارت SQL که بیشتر برای پایگاه داده‌های رابطه‌ای نیز مورد استفاده قرار می‌گیرد، برای دانشمندان داده خیلی مهارت مناسب و قابل توجهی نیست، زیرا که استخراج داده‌ها از «پایگاه داده» (Data Base) را با استفاده از SQL به منظور خوراک دادن داده‌ها به کتابخانه‌های «زبان برنامه‌نویسی پایتون» (Python Programming Language) از جمله «پانداس» (Pandas) راهی غیر مناسب می‌دانند و باور دارند که راهکارهای بهتری برای دستکاری داده‌ها وجود دارد.

اگرچه، با در نظر داشتن حجم انبوه داده‌هایی که همه روزه در صنایع گوناگون تولید و گردآوری می‌شود و تا هنگامی که داده‌ها در پایگاه داده‌های سازگار با SQL نگه‌داری شوند، به نظر می‌رسد که SQL همچنان یکی از کاراترین و مناسب‌ترین ابزارها در این راستا است که می‌توان از آن برای بررسی و جستجو در میان داده‌ها، فیلتر کردن آن‌ها و تجمیع داده‌ها به منظور کسب دانش و بینش مفید، کاربردی و کامل پیرامون آن‌ها استفاده کرد. برای مثال، با «Slice» و «Dice» کردن داده‌ها با SQL، تحلیل‌گران می‌توانند الگوهایی که ارزش بررسی‌های بیشتر دارند را شناسایی کنند. این امر گاهی منجر به بازتعریف جمعیت و متغیرهای مورد استفاده برای انجام تحلیل‌ها و در واقع، کاهش آن‌ها می‌شود. در اغلب مسائل تحلیل داده، کوچک شدن دامنه اولیه مسئله، به ساده‌تر و قابل حل‌تر شدن آن کمک شایان توجهی می‌کند.

بنابراین، به جای انتقال دادن «مجموعه داده‌های» (Data Sets) بزرگ به پایتون یا R، بهتر است گام اول تحلیل، استفاده از SQL برای کسب بینش آموزنده از داده‌ها باشد. در بحث کار با «پایگاه داده‌های رابطه‌ای» (Relational Databases)، باید گفت که SQL چیزی بیش از دستورات JOIN ،SELECT و ORDER BY است. در واقع، قابلیت‌های متعدد، متنوع و زیادی دارد که با استفاده از آن‌ها می‌توان فرایند تحلیل داده‌ها را سرعت و بهبود بخشید.

فیلم آموزش مسیر تبدیل شدن به دانشمند علم داده در فرادرس

کلیک کنید

در ادامه، نکات و ترفندهای SQL برای دانشمندان داده بیان شده است که به آن‌ها کمک می‌کند که کار تحلیل خود را به طور موثرتر و ساده‌تری با استفاده از SQL و یکپارچه‌سازی آن با زبان‌های برنامه‌نویسی مانند پایتون و R انجام دهند. در اینجا، با Oracle SQL روی جدول داده‌ای که در زیر نمایش داده شده و شامل چندین نوع از عناصر داده است، کار شده است.

ترفندهای SQL برای دانشمندان داده -- راهنمای کاربردی

دستور COALESCE()‎ برای بازکدگذاری داده‌های نال/ناموجود

هنگامی که بحث «بازکدگذاری» (Re-Coding) «مقادیر ناموجود» (Missing Values) مطرح می‌شود، تابع COALESCE()‎ گزینه پیشنهادی جادویی است که می‌تواند بر اساس شرایط، مقادیر «نال» (NULL) را به مقادیر تعیین شده در آرگومان بعدی، بازکدگذاری کند.

فیلم آموزش پایگاه‌داده MySQL در فرادرس

کلیک کنید

در این مثال، هدف بازکدگذاری NULL_VAR به کاراکتر «MISSING» است. قطعه کد لازم برای انجام این کار، در ادامه آورده شده است. توصیه می‌شود کلیه قطعه کدها و اعمالی که از این پس آموزش داده می‌شود را کاربر خود نیز بررسی کند تا به خوبی آن‌ها را بیاموزد.

خروجی قطعه کد بالا، به صورت زیر است.

ترفندهای SQL برای دانشمندان داده -- راهنمای کاربردی

نکته بسیار مهمی که در این وهله باید به آن توجه داشت این است که در پایگاه داده، «مقادیر ناموجود» (Missing Values | به آن‌ها مقادیر گم شده نیز گفته می‌شود) را می‌توان علاوه بر NULL، به شکل‌های گوناگون دیگری نیز کدگذاری کرد. به عنوان نمونه، ممکن است یک رشته یا فضای خالی باشند، برای مثال، در جدول معرفی شده در این مطلب به صورت «EMPTY_STR_VAR» است؛ همچنین، می‌تواند یک رشته کاراکتر «NA» باشد که در مثال ارائه شده در این مطلب، «NA_STR_VAR» است. در این مثال‌ها، COALESCE( )‎ کار نمی‌کند؛ اما می‌توان این موارد را با عبارت CASE WHEN مدیریت کرد.

خروجی قطعه کد بالا، به صورت زیر است.

ترفندهای SQL برای دانشمندان داده -- راهنمای کاربردیv

محاسبه توزیع تجمعی و کل اجرا

«جمع تجمعی» (Running Total | جمع رونده نیز به آن گفته می‌شود) هنگامی که کاربر به دنبال «جمع کل» (Total Sum) (و نه مقادیر مستقل) در یک نقطه خاص برای بخش‌بندی جمعیت برای تحلیل‌های بالقوه و «شناسایی دورافتادگی» (Outlier Identification) است می‌تواند گزینه مفیدی باشد.

فیلم آموزش Power BI برای تحلیل‌ داده – مقدماتی در فرادرس

کلیک کنید

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

خروجی قطعه کد بالا، به صورت زیر است.

ترفندهای SQL برای دانشمندان داده -- راهنمای کاربردی

خروجی‌ها، در سمت چپ جدول قرار دارند. در اینجا، دو ترفند وجود دارد؛ اول آنکه انجام عمل SUM روی «ردیف پیش از آماده سازی»، جمع همه مقادیر در اولویت نسبت به این نقطه را محاسبه می‌کند. دوم آنکه یک JOIN_ID برای محاسبه جمع کل می‌سازد. در اینجا از «تابع پنجره» (Window Function) برای این محاسبات استفاده می‌شود و از توزیع تجمعی، می‌توان به خوبی فهمید که آخرین رکورد احتمالا یک دورافتادگی است.

پیدا کردن رکوردهایی با مقادیر فوق‌العاده، بدون استفاده از Self Joining

اکنون، هدف بازگرداندن سطرهایی با بزرگ‌ترین مقدار NUM_VAR برای هر شناسه (ID) یکتا است. در این وهله باید ابتدا مقدار بیشینه برای هر شناسه (ID) را با استفاده از دستور group by پیدا کرد و سپس، ID و مقدار بیشینه را self joining کرد. راهکار دقیق برای انجام این کار، در قطعه کد زیر ارائه شده است.

خروجی قطعه کد و در واقع «کوئری» (Query) بالا، به صورت زیر است. در این خروجی، سطرهای دارای بیشینه NUM_VAR را که بر اساس ID گروه‌بندی شده‌اند، نشان داده شده‌اند.

ترفندهای SQL برای دانشمندان داده -- راهنمای کاربردی

دستور WHERE شرطی

دستور WHERE در SQL برای مجموعه‌ها مورد استفاده قرار می‌گیرد. در حقیقت، افرادی که با SQL کار می‌کنند بسیاری از مواقع از WHERE استفاده می‌کنند. برای مثال، در این جدول، هدف آن است که تنها سطرهایی با منطق زیر نگه داشته شوند.

if SEQ_VAR in (1, 2, 3) & diff(DATE_VAR2, DATE_VAR1)≥ 0

elif SEQ_VAR in (4, 5, 6) & diff(DATE_VAR2, DATE_VAR1) ≥1

else diff(DATE_VAR2, DATE_VAR1) ≥2

قطعه کد زیر، آنچه در بالا بیان شد را پیاده‌سازی می‌کند.

خروجی قطعه کد بالا به صورت زیر است.

ترفندهای SQL برای دانشمندان داده -- راهنمای کاربردی

منطقی که پیش از این بیان شد، باید توالی ۴ و ۵ از ID = 19064 را حذف کند. دلیل این امر، تفاوت بین date2 و date1 = 0 است و این دقیقا همان خروجی است که قطعه کد بالا ارائه می‌کند.

دستورات Lag()‎ و Lead()‎ برای کار با سطرهای متوالی

دستور Lag (بررسی سطر قبلی) و Lead (بررسی سطر بعدی) احتمالا دو تا از پرکاربردترین توابع تحلیلی در فعالیت‌های روزانه تحلیلگران داده‌ای است که از SQL طی کارها و فعالیت‌های خود استفاده می‌کنند. این دو تابع به کاربران این امکان را می‌دهد که بیش از یک سطر را در هر بار با دستور self-joining کوئری بزنند.

فیلم آموزش Power BI برای تحلیل‌ داده – مقدماتی در فرادرس

کلیک کنید

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

تابع LAG() ‎ سطر پیشین را باز می‌گرداند و اگر هیچ سطری وجود نداشت (برای مثال، اگر سطر مورد نظر، اولین سطر از هر ID بود)، PREV_NUM برابر با ۰ کدگذاری می‌شود تا تفاوت را که با NUM_DIFF در زیر نمایش داده شده است، محاسبه کند.

ترفندهای SQL برای دانشمندان داده -- راهنمای کاربردی

یکپارچه‌سازی کوئری SQL با پایتون و R

پیش‌نیاز یکپارچه‌سازی کوئری های SQL در پایتون و R، بنا نهادن اتصالات پایگاه داده با ODBC یا JDBC است. البته این موضوع فراتر از موضوع بحث این مطلب است و در اینجا به آن پرداخته نمی‌شود. اکنون، فرض می‌شود که در حال حاضر پایتون و R به پایگاه داده متصل شده‌اند.

فیلم آموزش Power BI برای تحلیل‌ داده – مقدماتی در فرادرس

کلیک کنید

ساده‌ترین راهکار برای استفاده از کوئری برای مثال در پایتون، کپی و چسباندن (Paste) کردن آن به عنوان رشته و سپس، فراخوانی «pandas.read_sql()‎» است. قطعه کد لازم برای این کار، در ادامه آمده است.

بنابراین، تا هنگامی که کوئری‌ها کوتاه هستند و بدون نیاز به تغییرات بیشتر نهایی می‌شوند، این متد به خوبی کار می‌کند. پرسشی که در این وهله برای برخی از افراد احتمالا مطرح می‌شود این است که اگر کوئری دارای ۱۰۰۰ خط باشد و یا نیاز باشد که به طور مداوم آن را به روز رسانی کرد، چه کار می‌توان انجام داد. در این سناریو، باید فایل‌های sql‎. را به طور مستقیم در پایتون یا R خواند. دستورات زیر نشان می‌دهد که چگونه باید دستور getSQL را در پایتون پیاده‌سازی کرد. ایده مورد استفاده برای این کار، در زبان برنامه‌نویسی R نیز مشابه است.

در اینجا، اولین arg sql_query فایل sql‎. را به طور جداگانه و تنها دریافت می‌کند که به سادگی قابل نگهداری است. پیاده‌سازی آنچه بیان شد، در ادامه انجام شده است.

«ID_LIST» یک رشته نگه‌دارنده برای مقادیری است که قرار داده می‌شوند و getSQL( )‎ با استفاده از کد زیر قابل فراخوانی است.

عبارات با قاعده در SQL

بسیاری از افراد تاکنون از «عبارات با قاعده» (Regular Expression) در SQL استفاده نکرده‌اند. اما استفاده از آن‌ها می‌تواند برای استخراج متن حقیقتا کاربردی و مفید باشد. برای مثال، کد ارائه شده در زیر، مثال ساده‌ای را از چگونگی استفاده از REGEXP_INSTR( )‎ برای پیدا کردن و استخراج اعداد نشان می‌دهد.

اگر نوشته بالا برای شما مفید بوده است، آموزش‌های زیر نیز به شما پیشنهاد می‌شوند:

محصول، خدمات یا برند خود را در مجله فرادرس معرفی کنید.

کلیک کنید

مطالب مجله فرادرس را در جستجوی گوگل سریع‌تر ببینید

بر اساس رای ۱ نفر

آیا این مطلب برای شما مفید بود؟

اگر پرسشی درباره این مطلب دارید، آن را با ما مطرح کنید.

منابع:

«الهام حصارکی»، فارغ‌التحصیل مقطع کارشناسی ارشد مهندسی فناوری اطلاعات، گرایش سیستم‌های اطلاعات مدیریت است. او در زمینه هوش مصنوعی و داده‌کاوی، به ویژه تحلیل شبکه‌های اجتماعی، فعالیت می‌کند.

مطالب مرتبط