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


در این مطلب، نکات و ترفندهای SQL برای دانشمندان داده به منظور کار کردن سریعتر و بهتر با دادههای موجود در پایگاه داده، آموزش داده شده است.
ترفندهای SQL برای دانشمندان داده
«دانشمندان داده» (Data Scientists) و «تحلیلگران داده» (Data Analysts) باید SQL (سرنام عبارت Structured Query Language که به فارسی به آن زبان پرسمان ساختیافته گفته میشود) را بدانند و در واقع، با این مورد آشنایی کامل و حتی در بسیاری از مواقع، بر آن تسلط داشته باشند.
در واقع، کلیه افرادی که با دادهها سر و کار دارند و کارهای تحلیلی انجام میدهند، باید 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 روی جدول دادهای که در زیر نمایش داده شده و شامل چندین نوع از عناصر داده است، کار شده است.
دستور COALESCE() برای بازکدگذاری دادههای نال/ناموجود
هنگامی که بحث «بازکدگذاری» (Re-Coding) «مقادیر ناموجود» (Missing Values) مطرح میشود، تابع COALESCE() گزینه پیشنهادی جادویی است که میتواند بر اساس شرایط، مقادیر «نال» (NULL) را به مقادیر تعیین شده در آرگومان بعدی، بازکدگذاری کند.
در این مثال، هدف بازکدگذاری NULL_VAR به کاراکتر «MISSING» است. قطعه کد لازم برای انجام این کار، در ادامه آورده شده است. توصیه میشود کلیه قطعه کدها و اعمالی که از این پس آموزش داده میشود را کاربر خود نیز بررسی کند تا به خوبی آنها را بیاموزد.
1---- 1) COALESCE() to recode the NULL value to the character string MISSING
2SELECT
3 ID_VAR,
4 NULL_VAR,
5 COALESCE(NULL_VAR, 'MISSING') AS RECODE_NULL_VAR
6FROM
7 CURRENT_TABLE
8ORDER BY ID_VAR
خروجی قطعه کد بالا، به صورت زیر است.
نکته بسیار مهمی که در این وهله باید به آن توجه داشت این است که در پایگاه داده، «مقادیر ناموجود» (Missing Values | به آنها مقادیر گم شده نیز گفته میشود) را میتوان علاوه بر NULL، به شکلهای گوناگون دیگری نیز کدگذاری کرد. به عنوان نمونه، ممکن است یک رشته یا فضای خالی باشند، برای مثال، در جدول معرفی شده در این مطلب به صورت «EMPTY_STR_VAR» است؛ همچنین، میتواند یک رشته کاراکتر «NA» باشد که در مثال ارائه شده در این مطلب، «NA_STR_VAR» است. در این مثالها، COALESCE( ) کار نمیکند؛ اما میتوان این موارد را با عبارت CASE WHEN مدیریت کرد.
1--- However, COALESCE() NOT WORK for Empty or NA string, instead, use CASE WHEN
2SELECT
3 ID_VAR,
4 EMPTY_STR_VAR,
5 COALESCE(EMPTY_STR_VAR, 'MISSING') AS COALESCE_EMPTY_STR_VAR,
6 CASE WHEN EMPTY_STR_VAR = ' ' THEN 'EMPTY_MISSING' END AS CASEWHEN_EMPTY_STR_VAR,
7
8 NA_STR_VAR,
9 CASE WHEN NA_STR_VAR = 'NA' THEN 'NA_MISSING' END AS CASEWHEN_NA_STR_VAR
10FROM
11 CURRENT_TABLE
12ORDER BY ID_VAR
خروجی قطعه کد بالا، به صورت زیر است.
محاسبه توزیع تجمعی و کل اجرا
«جمع تجمعی» (Running Total | جمع رونده نیز به آن گفته میشود) هنگامی که کاربر به دنبال «جمع کل» (Total Sum) (و نه مقادیر مستقل) در یک نقطه خاص برای بخشبندی جمعیت برای تحلیلهای بالقوه و «شناسایی دورافتادگی» (Outlier Identification) است میتواند گزینه مفیدی باشد.
قطعه کد زیر، روش محاسبه جمع تجمعی و توزیع تجمعی برای متغیر NUM_VAR را نشان میدهد.
1--- 2) Running total/frequency
2SELECT
3 DAT.NUM_VAR,
4 SUM(NUM_VAR) OVER (PARTITION BY JOIN_ID) AS TOTAL_SUM,
5 ROUND(CUM_SUM / SUM(NUM_VAR) OVER (PARTITION BY JOIN_ID), 4) AS CUM_FREQ
6FROM
7(
8 SELECT
9 T.*,
10 SUM(NUM_VAR) OVER (ORDER BY NUM_VAR ROWS UNBOUNDED PRECEDING) AS CUM_SUM,
11 CASE WHEN ID_VAR IS NOT NULL THEN '1' END AS JOIN_ID
12 FROM CURRENT_TABLE T
13) DAT
خروجی قطعه کد بالا، به صورت زیر است.
خروجیها، در سمت چپ جدول قرار دارند. در اینجا، دو ترفند وجود دارد؛ اول آنکه انجام عمل SUM روی «ردیف پیش از آماده سازی»، جمع همه مقادیر در اولویت نسبت به این نقطه را محاسبه میکند. دوم آنکه یک JOIN_ID برای محاسبه جمع کل میسازد. در اینجا از «تابع پنجره» (Window Function) برای این محاسبات استفاده میشود و از توزیع تجمعی، میتوان به خوبی فهمید که آخرین رکورد احتمالا یک دورافتادگی است.
پیدا کردن رکوردهایی با مقادیر فوقالعاده، بدون استفاده از Self Joining
اکنون، هدف بازگرداندن سطرهایی با بزرگترین مقدار NUM_VAR برای هر شناسه (ID) یکتا است. در این وهله باید ابتدا مقدار بیشینه برای هر شناسه (ID) را با استفاده از دستور group by پیدا کرد و سپس، ID و مقدار بیشینه را self joining کرد. راهکار دقیق برای انجام این کار، در قطعه کد زیر ارائه شده است.
1--- 3) Find the record having a number calculated by analytic functions (e.g., MAX) without self-joining
2SELECT *
3FROM
4(
5 SELECT
6 DAT.*,
7 CASE WHEN (NUM_VAR = MAX(NUM_VAR) OVER (PARTITION BY ID_VAR)) THEN 'Y' ELSE 'N' END AS MAX_NUM_IND
8 FROM
9 CURRENT_TABLE DAT
10) DAT2
11WHERE MAX_NUM_IND = 'Y'
خروجی قطعه کد و در واقع «کوئری» (Query) بالا، به صورت زیر است. در این خروجی، سطرهای دارای بیشینه NUM_VAR را که بر اساس ID گروهبندی شدهاند، نشان داده شدهاند.
دستور 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
قطعه کد زیر، آنچه در بالا بیان شد را پیادهسازی میکند.
1-- 4) Conditional where clause
2SELECT
3 DAT.ID_VAR,
4 DAT.SEQ_VAR,
5 DAT.NUM_VAR,
6 DATE_VAR1,
7 DATE_VAR2,
8 TRUNC(DATE_VAR2) - TRUNC(DATE_VAR1) AS LAG_IN_DATES
9FROM
10 CURRENT_TABLE DAT
11WHERE
12 (TRUNC(DATE_VAR2) - TRUNC(DATE_VAR1)) >= CASE WHEN SEQ_VAR IN (1,2,3) THEN 0 WHEN SEQ_VAR IN (4,5,6) THEN 1 ELSE 2 END
13ORDER BY ID_VAR, SEQ_VAR
خروجی قطعه کد بالا به صورت زیر است.
منطقی که پیش از این بیان شد، باید توالی ۴ و ۵ از ID = 19064 را حذف کند. دلیل این امر، تفاوت بین date2 و date1 = 0 است و این دقیقا همان خروجی است که قطعه کد بالا ارائه میکند.
دستورات Lag() و Lead() برای کار با سطرهای متوالی
دستور Lag (بررسی سطر قبلی) و Lead (بررسی سطر بعدی) احتمالا دو تا از پرکاربردترین توابع تحلیلی در فعالیتهای روزانه تحلیلگران دادهای است که از SQL طی کارها و فعالیتهای خود استفاده میکنند. این دو تابع به کاربران این امکان را میدهد که بیش از یک سطر را در هر بار با دستور self-joining کوئری بزنند.
فرض میشود که هدف، محاسبه تفاوت در NUM_VAR بین دو سطر متوالی است (که بر اساس توالی مرتب شدهاند)؛ قطعه کد لازم برای انجام این کار، در ادامه آورده شده است.
1--- 5) LAG() or LEAD() function
2SELECT
3 DAT.ID_VAR,
4 DAT.SEQ_VAR,
5 DAT.NUM_VAR,
6 NUM_VAR - PREV_NUM AS NUM_DIFF
7FROM
8(
9 SELECT
10 T.*,
11 LAG(NUM_VAR, 1, 0) OVER (PARTITION BY ID_VAR ORDER BY SEQ_VAR) AS PREV_NUM
12 FROM
13 CURRENT_TABLE T
14) DAT
15ORDER BY ID_VAR, SEQ_VAR
تابع LAG() سطر پیشین را باز میگرداند و اگر هیچ سطری وجود نداشت (برای مثال، اگر سطر مورد نظر، اولین سطر از هر ID بود)، PREV_NUM برابر با ۰ کدگذاری میشود تا تفاوت را که با NUM_DIFF در زیر نمایش داده شده است، محاسبه کند.
یکپارچهسازی کوئری SQL با پایتون و R
پیشنیاز یکپارچهسازی کوئری های SQL در پایتون و R، بنا نهادن اتصالات پایگاه داده با ODBC یا JDBC است. البته این موضوع فراتر از موضوع بحث این مطلب است و در اینجا به آن پرداخته نمیشود. اکنون، فرض میشود که در حال حاضر پایتون و R به پایگاه داده متصل شدهاند.
سادهترین راهکار برای استفاده از کوئری برای مثال در پایتون، کپی و چسباندن (Paste) کردن آن به عنوان رشته و سپس، فراخوانی «pandas.read_sql()» است. قطعه کد لازم برای این کار، در ادامه آمده است.
1my_query = "SELECT * FROM CURRENT_TABLE"
2sql_data = pandas.read_sql(my_query, connection)
بنابراین، تا هنگامی که کوئریها کوتاه هستند و بدون نیاز به تغییرات بیشتر نهایی میشوند، این متد به خوبی کار میکند. پرسشی که در این وهله برای برخی از افراد احتمالا مطرح میشود این است که اگر کوئری دارای ۱۰۰۰ خط باشد و یا نیاز باشد که به طور مداوم آن را به روز رسانی کرد، چه کار میتوان انجام داد. در این سناریو، باید فایلهای sql. را به طور مستقیم در پایتون یا R خواند. دستورات زیر نشان میدهد که چگونه باید دستور getSQL را در پایتون پیادهسازی کرد. ایده مورد استفاده برای این کار، در زبان برنامهنویسی R نیز مشابه است.
1import pandas as pd
2def getSQL(sql_query,
3 place_holder_str,
4 replace_place_holder_with,
5 database_con):
6 '''
7 Args:
8 sql_query: sql query file
9 place_holder_str: string in the original sql query that is to be replaced
10 replace_place_holder_with: real values that should be put in
11 database_con: connection to the database
12 '''
13
14 sqlFile = open(sql_query, 'r')
15 sqlQuery = sqlFile.read()
16
17 sqlQuery = sqlQuery.replace(place_holder_str, replace_place_holder_with)
18
19 df = pd.read_sql_query(sqlQuery, database_con)
20
21 database_con.close()
22
23 return df
در اینجا، اولین arg sql_query فایل sql. را به طور جداگانه و تنها دریافت میکند که به سادگی قابل نگهداری است. پیادهسازی آنچه بیان شد، در ادامه انجام شده است.
1SELECT
2 *
3FROM
4 CURRENT_TABLE DAT
5WHERE
6 ID_VAR IN ('ID_LIST')
7ORDER BY ID_VAR, SEQ_VAR
«ID_LIST» یک رشته نگهدارنده برای مقادیری است که قرار داده میشوند و getSQL( ) با استفاده از کد زیر قابل فراخوانی است.
1seq12_df = getSQL('SQL_FILE.sql', 'ID_LIST', "','".join(['19228', '19272']), database_con=conn)
عبارات با قاعده در SQL
بسیاری از افراد تاکنون از «عبارات با قاعده» (Regular Expression) در SQL استفاده نکردهاند. اما استفاده از آنها میتواند برای استخراج متن حقیقتا کاربردی و مفید باشد. برای مثال، کد ارائه شده در زیر، مثال سادهای را از چگونگی استفاده از REGEXP_INSTR( ) برای پیدا کردن و استخراج اعداد نشان میدهد.
1-- Find and extract numbers between 0 - 9 that consecutively happens 5 times
2SELECT
3 SUBSTRING(LONG_TEXT, REG_IDX, REG_IDX+5) AS NUMBER_LIST_FOUND
4FROM
5(
6 SELECT
7 REGEXP_INSTR(LONG_TEXT, '[0-9]{5}') AS REG_IDX,
8 LONG_TEXT
9 FROM
10 BONUS
11) DAT
اگر نوشته بالا برای شما مفید بوده است، آموزشهای زیر نیز به شما پیشنهاد میشوند:
- مجموعه آموزشهای آمار، احتمالات و دادهکاوی
- آموزش هوش مصنوعی
- مجموعه آموزشهای برنامه نویسی پایتون (Python)
- داده کاوی (Data Mining) — از صفر تا صد
- یادگیری علم داده (Data Science) با پایتون — از صفر تا صد
- معرفی منابع جهت آموزش یادگیری عمیق (Deep Learning) — راهنمای کامل
- آموزش SQL Server Management Studio | کامل، رایگان و گام به گام
^^