چگونه از توابع پنجره SQL استفاده کنیم

منتشر‌شده در : towardsdatascience به تاریخ ۲ ژوئن ۲۰۲۱
لینک منبع How to Use SQL Window Functions

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

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

عملکرد پنجره چیست؟

تابع پنجره تابعی است که در سراسر ردیف‌های جدول مختلف اجرا می‌شود. به جای نگاه کردن به هر ردیف به صورت جداگانه مانند یک تابع «نرمال»، این معمولا به ردیف‌های قبل و یا بعد به منظور محاسبه ردیف هدف نگاه می‌کنند.

محبوب‌ترین توابع پنجره

1. Sum
2. Max
3. Row Number
4. Rank
5. Lead/Lag

بیایید نگاهی به هر یک از این موارد به صورت جداگانه بیندازیم تا ببینیم آن‌ها چه کاری انجام می‌دهند و بهترین راه برای استفاده از آن‌ها در کد SQL شما چیست.

جمع(Sum)

تابع Sum دقیقا همان چیزی است که به نظر می‌رسد. آن هر آنچه را که شما برای آن مشخص می‌کنید جمع می‌کند یا اضافه می‌کند. تفاوت اصلی بین تابع مجموع پنجره و یک تابع مجموع معمولی این است که این کار مجموع اجرای هر ردیف را به جای مجموع یک ردیف برای همه سطرها می‌گیرد.

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

SELECT
employee_name,
day_of_week,
amount_spent,
SUM(amount_spent) OVER (ORDER BY date) AS total
FROM personal_finances

این به یک خروجی شبیه به این منجر خواهد شد:

اگر شرکتی بودیم که نگاه می‌کرد کارمندان ما چقدر خرج می‌کنند، حتی ممکن است مفید باشد که یک بخش فرعی را به آن اضافه کنیم.

SELECT
employee_name,
day_of_week,
amount_spent,
SUM(amount_spent) OVER (ORDER BY date PARTITION BY employee_name) AS total
FROM personal_finances

خروجی این نامه پرس و جو به این شکل خواهد بود:

حداکثر(MAX)

درست مانند مجموع، max به طور مشابه با تابع «نرمال» SQL max عمل می‌کند. حداکثر مقدار ستونی که مشخص می‌کنید را می‌گیرد و به شرایطی که با ORDER BY و PARTITION BYاستفاده می‌کنید بستگی دارد. اما، به یاد داشته باشید، این کارکردها اختیاری هستند.

در اینجا به نحوه پیدا کردن کارمند از هر بخشی که بیش‌ترین هزینه را بر روی کارت شرکت خود صرف کرده‌است، اشاره می‌کنیم:

SELECT
employee_name,
department,
MAX(total_spent) OVER(ORDER BY total_spent PARTITION BY department) as max_spent
FROM personal_finances

نتیجه به این شکل خواهد بود:

شماره ردیف(Row Number)

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

در اینجا ما کارمندان را با شناسه کارمندانشان سفارش می‌دهیم و هر ردیف را براساس سفارش کارمند در جدول قرار می‌دهیم.

SELECT
ROW_NUMBER() OVER(ORDER BY employee_id),
employee_id,
employee_name
FROM org_chart

این به یک خروجی شبیه به این منجر خواهد شد:

رتبه(Rank)

از رنک برای مرتب کردن سطرها براساس یک مقدار ستون خاص استفاده می‌شود. این به دلیل بندهایORDER BY و PARTITION BY برای هر نوع سفارش بسیار عالی است و بسیار مفید است. رتبه احتمالا محبوب‌ترین و پر استفاده ترین عملکرد پنجره من است!

در اینجا ما می‌توانیم از آن برای یافتن منابع برتر در شرکت استفاده کنیم:

SELECT
employee_name,
employee_id,
amount_spent,
RANK(amount_spent) OVER(ORDER BY amount_spent DESC) AS spend_rank
FROM employees

توجه داشته باشید که منDESC را بعد از جمله ORDER BY قرار داده‌ام. این امر هنگام استفاده از رتبه بسیار مهم است. شما باید مشخص کنید که آیا می خواهید توسط ستون ASC (مقادیر بزرگتر شوند) یا DESC (مقادیر کمتر می شوند) سفارش دهید. اگر این را مشخص نکنید، نامه پرس و جو ممکن است برخلاف آنچه که شما در نظر گرفته‌اید برگردد.

بیایید به همان مثال نگاه کنیم اما با استفاده از PARTITION BY.

SELECT
employee_name,
employee_id,
department_id,
RANK(amount_spent) OVER(ORDER BY amount_spent DESC PARTITION BY department_id) AS spend_rank
FROM employees

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

توجه داشته باشید که چگونه لزوماً ردیف‌ها را گروه یا مرتب نمی‌کند اما رتبه‌بندی توسطdepart_id و ترتیب هر کارمند در بخش خود تعیین می‌شود.

اگر علاقمند هستید که عمیق‌تر وارد توابع رتبه‌بندی پنجره‌ها شوید و حتی تفاوت بین رتبه و رتبه متراکم را یاد بگیرید، مقاله من را بررسی کنید که چگونه از توابع رتبه‌بندی SQL استفاده کنید: How to Use SQL RANK and DENSE_RANK Functions

Lead/Lag

درک این مسائل زمانی که برای اولین بار در مورد آن‌ها یاد می‌گیرید، می‌تواند بسیار گیج‌کننده باشد. من در واقع یک مقاله کامل نوشتم که به توضیح تفاوت‌های آن‌ها و یک ترفند برای به یاد آوردن این که کدام معنی می‌دهد، اختصاص داشت. چگونگی استفاده از SQL لید و توابع Lag را برای یادگیری بیشتر بررسی کنید: How to Use SQL Lead and Lag Functions

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

این متن با استفاده از ربات ترجمه مقالات علم داده ترجمه شده و به صورت محدود مورد بازبینی انسانی قرار گرفته است. در نتیجه می‌تواند دارای برخی اشکالات ترجمه باشد.

مقالات لینک‌شده در این متن می‌توانند به صورت رایگان با استفاده از مقاله‌خوان ترجمیار به فارسی مطالعه شوند.