پنج عملکرد برتر پنجره SQL که برای مصاحبه های علم داده باید بدانید

SQL زبان جهانی در دنیای داده است و مهم‌ترین مهارتی است که می‌توان به عنوان یک حرفه‌ای در زمینه داده به کار برد.

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

چیزی که دانشمندان بزرگ داده را از دانشمندان داده خوب جدا می کند این است که دانشمندان داده بزرگ می توانند داده ها را تا آنجایی که قابلیت های SQL اجازه می دهد با هم درگیر کنند. بخش بزرگی از توانایی استفاده کامل از همه چیزهایی که SQL ارائه می دهد، دانستن نحوه استفاده از توابع پنجره است.

با این گفته، بیایید به آن شیرجه بزنیم!

1. دلتاها با LEAD() و LAG()

 
LEAD() و LAG() بیشتر برای مقایسه یک دوره زمانی با دوره زمانی قبلی برای یک متریک معین استفاده می شوند. برای آوردن چند مثال…

• شما می توانید دلتای بین فروش هر سال و فروش سال قبل را بدست آورید
• شما می توانید دلتا را در تعداد ثبت نام / تبدیل / بازدید از وب سایت به صورت ماه به ماه دریافت کنید
• شما می توانید ریزش کاربر را به صورت ماهانه مقایسه کنید

مثال:

پرس و جو زیر نشان می دهد که چگونه می توانید درصد تغییر ماهانه هزینه ها را استعلام کنید

with monthly_costs as ( SELECT date , monthlycosts , LEAD(monthlycosts) OVER (ORDER BY date) as previousCosts FROM costs )SELECT date , (monthlycosts - previousCosts) / previousCosts * 100 AS costPercentChange FROM monthly_costs

2. مجموع تجمعی با SUM() یا COUNT()

 
محاسبه مجموع در حال اجرا را می توان به سادگی از طریق یک تابع ویندوز انجام داد که با SUM() یا COUNT() شروع می شود. این یک ابزار قدرتمند برای زمانی است که می خواهید رشد یک معیار خاص را در طول زمان نشان دهید. به طور خاص، در شرایط زیر مفید است:

• در طول زمان مجموع درآمد و هزینه ها را دریافت کنید
• مجموع زمان صرف شده روی برنامه را برای هر کاربر دریافت کنید
• در طول زمان مجموع تبدیل ها را دریافت کنید

مثال:

مثال زیر نشان می دهد که چگونه می توانید یک ستون جمعی از هزینه های ماهانه را در نظر بگیرید:

SELECT date , monthlycosts , SUM(monthlycosts) OVER (ORDER BY date) as cumCosts FROM cost_table

3. میانگین متحرک با AVG()

 
AVG() در توابع ویندوز واقعا قدرتمند است زیرا به شما امکان محاسبه را می دهد میانگین متحرک اضافه کار.

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

به طور خاص…

• می توان از آنها برای دریافت روند کلی فروش هفتگی استفاده کرد (آیا میانگین در طول زمان افزایش می یابد؟). این نشان دهنده رشد به عنوان یک شرکت است.
• همچنین می توان از آنها برای دریافت روند کلی تبدیل هفتگی یا بازدید از وب سایت استفاده کرد.

مثال:

پرس و جو زیر نمونه ای از دریافت میانگین متحرک 10 روزه برای تبدیل است.

SELECT Date , dailyConversions , AVG(dailyConversions) OVER (ORDER BY Date ROWS 10 PRECEDING) AS 10_dayMovingAverage FROM conversions

4. row_number ()

 
ROW_NUMBER() مخصوصاً زمانی مفید است که می‌خواهید اولین یا آخرین رکورد را دریافت کنید. به عنوان مثال، اگر جدولی از زمان آمدن اعضای باشگاه به باشگاه دارید و می‌خواهید تاریخ اولین روز ورود آنها به باشگاه را دریافت کنید، می‌توانید PARTITION بر اساس مشتری (نام/شناسه) و سفارش بر اساس تاریخ خرید. سپس، برای به دست آوردن ردیف اول، می توانید به سادگی ردیف هایی با rowNumber برابر با یک را فیلتر کنید.

مثال:

این مثال نشان می‌دهد که چگونه می‌توانید از ROW_NUMBER() برای دریافت اولین تاریخ بازدید از هر عضو (کاربر) استفاده کنید.

with numbered_visits as ( SELECT memberId , visitDate , ROW_NUMBER() OVER (PARTITION BY customerId ORDER BY purchaseDate) as rowNumber FROM gym_visits )SELECT * FROM numbered_visits WHERE rowNumber = 1

برای جمع بندی، اگر زمانی نیاز به دریافت اولین یا آخرین رکورد داشتید، ROW_NUMBER() یک راه عالی برای رسیدن به آن است.

5. ثبت رتبه با DENSE_RANK()

 
DENSE_RANK() مشابه ROW_NUMBER() است با این تفاوت که همان رتبه را برای مقادیر مساوی برمی گرداند. رتبه بندی متراکم برای بازیابی رکوردهای برتر بسیار مفید است، به عنوان مثال:

• اگر می‌خواهید 10 برنامه پربیننده نتفلیکس را در این هفته انتخاب کنید
• اگر می خواهید 100 کاربر برتر را بر اساس دلار خرج شده به دست آورید
• اگر می خواهید رفتار 1000 کاربر کمتر فعال را ببینید

مثال:

اگر می خواهید مشتریان برتر خود را بر اساس فروش کل رتبه بندی کنید، DENSE_RANK() یک تابع مناسب برای استفاده خواهد بود.

SELECT customerId , totalSales , DENSE_RANK() OVER (ORDER BY totalSales DESC) as rank FROM customers

با تشکر برای خواندن!

 
و بس! من امیدوارم که این به شما در آمادگی برای مصاحبه کمک کند - من مطمئن هستم که اگر این 5 مفهوم را از درون به بیرون بشناسید، در مورد اکثر مشکلات عملکرد پنجره SQL که وجود دارد، عالی عمل خواهید کرد.

مثل همیشه، برای شما در تلاش های یادگیری بهترین ها را آرزو می کنم!

 
 
ترنس شین یک علاقه‌مند به داده‌ها با بیش از 3 سال تجربه در SQL و 2 سال تجربه در پایتون، و یک وبلاگ نویس در Towards Data Science و KDnuggets است.

اصلی. مجدداً با اجازه دوباره ارسال شد.

منبع: https://www.kdnuggets.com/2022/01/top-five-sql-window-functions-know-data-science-interviews.html