فهرست مطالب

توابع پنجره ای SQL: راهنمای جامع با مثال های عملی

مقدمه

توابع پنجره ای (Window Functions) در SQL ابزار قدرتمندی برای انجام محاسبات تحلیلی پیچیده بر روی مجموعه های داده ی مرتبط با ردیف فعلی ارائه می دهند. این توابع برخلاف توابع تجمعی (Aggregate Functions) که ردیف ها را در گروه ها خلاصه می کنند و یک ردیف جدید برای هر گروه ایجاد می کنند، هویت هر ردیف را حفظ می کنند.

نحو کلی

ساختار کلی استفاده از توابع پنجره ای در SQL به صورت زیر است:

SQL
 SELECT
  column1,
  column2,
  window_function(column3) OVER (window_definition)
FROM table_name;
 

column1 و column2 ستون هایی از جدول هستند که می خواهید نمایش داده شوند.
window_function تابع پنجره ای است که می خواهید اعمال کنید.
column3 ستونی است که تابع پنجره ای بر روی آن اعمال می شود.
window_definition مشخص می کند که پنجره چگونه تعریف می شود.

اجزاء کلیدی

مفهوم پنجره (Window Definition):

پنجره مجموعه ای از ردیف ها است که تابع پنجره ای بر روی آنها اعمال می شود. این مجموعه می تواند با استفاده از کلیدواژه های PARTITION BY و ORDER BY تعریف شود.
- PARTITION BY: این کلیدواژه برای تقسیم پنجره به پارتیشن های کوچکتر بر اساس مقادیر یک یا چند ستون استفاده می شود.
- ORDER BY: این کلیدواژه برای مرتب کردن ردیف ها در داخل هر پارتیشن استفاده می شود.
چارچوب پنجره (Window Frame):

چارچوب پنجره مشخص می کند که کدام ردیف ها در داخل پنجره برای محاسبه تابع پنجره ای در نظر گرفته می شوند. سه نوع چارچوب پنجره وجود دارد:
- ROWS: شامل تعداد مشخصی از ردیف ها از ردیف فعلی است.
- RANGE: شامل ردیف هایی در یک محدوده مشخص از مقادیر یک ستون مرتب شده است.
- DENSE: شامل تمام ردیف ها در پارتیشن از ابتدای پارتیشن تا ردیف فعلی می شود.

مثال های کاربردی

محاسبه مقادیر تجمعی:

می توانید از توابع پنجره ای برای محاسبه مقادیر تجمعی مانند مجموع، میانگین، ماکزیمم و حداقل برای گروه های ردیف ها استفاده کنید. به عنوان مثال، می توانید از SUM() برای محاسبه مجموع فروش برای هر مشتری در طول سال جاری استفاده کنید:
SQL
```
SELECT
  customer_id,
  order_date,
  SUM(amount) OVER (PARTITION BY customer_id, YEAR(order_date)) AS total_sales_per_year
FROM orders;
```
محاسبه مقادیر متحرک:

می توانید از توابع پنجره ای برای محاسبه مقادیر متحرک مانند میانگین متحرک و انحراف استاندارد متحرک استفاده کنید. به عنوان مثال، می توانید از AVG() برای محاسبه میانگین متحرک ۳ روزه قیمت سهام استفاده کنید:
SQL
```
SELECT
  date,
  price,
  AVG(price) OVER (ORDER BY date ROWS BETWEEN ۲ PRECEDING AND CURRENT ROW) AS moving_average_price
FROM stock_prices;
```

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

انعطاف پذیری: توابع پنجره ای به شما امکان می دهند محاسبات پیچیده را بدون نیاز به پرس و جوهای فرعی یا جداول موقت انجام دهید.
کارایی: توابع پنجره ای می توانند در بسیاری از موارد کارآمدتر از پرس و جوهای سنتی با استفاده از GROUP BY باشند.
وضوح: توابع پنجره ای می توانند کد SQL را خواناتر و قابل درک تر کنند.

نکات مهم

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