توابع پنجره ای در پایگاه  داده SQL

توابع پنجره ای در پایگاه  داده SQL

توابع پنجره‌ای (Window Functions) در SQL یکی از ابزارهای قدرتمند برای انجام محاسبات تحلیلی بر روی مجموعه‌ای از داده‌ها هستند.

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

این توابع با استفاده از عبارت OVER اعمال می‌شوند و امکان تعریف یک پنجره داده (مانند گروه‌بندی یا ترتیب‌بندی) برای انجام محاسبات را فراهم می‌کنند.

توابعی مانند ROW_NUMBER، RANK، DENSE_RANK و NTILE از پرکاربردترین توابع پنجره‌ای هستند.

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

به عنوان مثال، تابع ROW_NUMBER شماره منحصربه‌فردی به هر ردیف اختصاص می‌دهد، در حالی که RANK و DENSE_RANK برای رتبه‌بندی با رفتارهای متفاوت در مواجهه با مقادیر تکراری طراحی شده‌اند.

ساختار کلی توابع پنجره‌ای در SQL

توابع پنجره‌ای برخلاف توابع گروهی (مانند SUM یا COUNT)، بدون کاهش تعداد ردیف‌های خروجی عمل می‌کنند.

آن‌ها بر اساس یک پنجره از داده‌ها که با استفاده از عبارت OVER تعریف می‌شود، محاسبات را انجام می‌دهند.

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

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

<Window_Function>() OVER ([PARTITION BY <Column>] [ORDER BY <Column>])

Window_Function: تابع پنجره‌ای مانند ROW_NUMBER، RANK، DENSE_RANK، یا NTILE.

PARTITION BY: (اختیاری) داده‌ها را به گروه‌هایی تقسیم می‌کند.

ORDER BY: ترتیب ردیف‌ها در پنجره را مشخص می‌کند.

 

🌟 آیا می‌خواهید به یک متخصص پایگاه داده تبدیل شوید و در دنیای فناوری اطلاعات بدرخشید؟

با دوره آموزشی SQL Server ما، شما می‌توانید به راحتی و با روشی عملی، تمام مهارت‌های لازم را یاد بگیرید!

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

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

پس فرصت را از دست ندهید! همین امروز به جمع یادگیرندگان ما بپیوندید و اولین قدم را به سوی آینده شغلی روشن‌تر بردارید!

 همین حالا شروع کنید و به دنیای داده‌ها بپیوندید!

 

انواع توابع پنجره ای در پایگاه  داده SQL

• تابع ROW_NUMBER

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

به عنوان مثال، فرض کنید جدولی به نام Employees داریم که شامل اطلاعات کارکنان است.
برای شماره‌دهی به ردیف‌ها در هر بخش بر اساس حقوق (Salary)، از تابع ROW_NUMBER به صورت زیر استفاده می‌کنیم:

SELECT
    Name,
    Department,
    Salary,
    ROW_NUMBER() OVER (PARTITION BY Department ORDER BY Salary DESC) AS RowNum
FROM Employees;

در این مثال، ROW_NUMBER به هر ردیف یک شماره اختصاص می‌دهد و ردیف‌ها را بر اساس حقوق به ترتیب نزولی مرتب می‌کند.

• تابع RANK

  تابع RANK ردیف‌ها را بر اساس ترتیب مشخص شده در ORDER BY رتبه‌بندی می‌کند و در صورت وجود مقادیر تکراری، جایگاه بعدی را با توجه به تعداد مقادیر تکراری تنظیم می‌کند.
  به عبارت دیگر، فاصله بین رتبه‌ها ممکن است وجود داشته باشد.

به عنوان مثال، برای رتبه‌بندی کارکنان بر اساس حقوق می‌توانیم از دستور زیر استفاده کنیم:

SELECT
    Name,
    Department,
    Salary,
    RANK() OVER (PARTITION BY Department ORDER BY Salary DESC) AS Rank
FROM Employees;

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

• تابع DENSE_RANK

  تابع DENSE_RANK مشابه RANK عمل می‌کند، اما در صورت وجود مقادیر تکراری، جایگاه بعدی به طور متوالی تعیین می‌شود و فاصله‌ای بین رتبه‌ها وجود ندارد.

برای مثال، برای رتبه‌بندی کارکنان بر اساس حقوق می‌توانیم از دستور زیر استفاده کنیم:

SELECT
    Name,
    Department,
    Salary,
    DENSE_RANK() OVER (PARTITION BY Department ORDER BY Salary DESC) AS DenseRank
FROM Employees;

در اینجا، DENSE_RANK به هر ردیف رتبه‌ای بدون فاصله بین مقادیر تکراری اختصاص می‌دهد.

• تابع NTILE(n)

  تابع NTILE داده‌ها را به n بخش مساوی (یا نزدیک به مساوی) تقسیم می‌کند و یک شماره گروه به هر ردیف اختصاص می‌دهد.
  این تابع برای تقسیم داده‌ها به گروه‌های متوازن بسیار کاربردی است.

برای مثال، اگر بخواهیم کارکنان بخش IT را به ۲ گروه تقسیم کنیم، می‌توانیم از دستور زیر استفاده کنیم:

SELECT
    Name,
    Department,
    Salary,
    NTILE(2) OVER (PARTITION BY Department ORDER BY Salary DESC) AS GroupNum
FROM Employees;

در اینجا، NTILE(2) داده‌ها را به دو گروه تقسیم می‌کند و شماره گروه را به هر ردیف اختصاص می‌دهد.

مقایسه توابع پنجره‌ای  در  SQL

در زیر مقایسه‌ای بین توابع ROW_NUMBER، RANK، DENSE_RANK و NTILE آورده شده است:

• ROW_NUMBER

  شماره‌دهی یکتا به ردیف‌ها. کاربردی برای شماره‌گذاری دانشجویان.

• RANK

  رتبه‌بندی با در نظر گرفتن فاصله در صورت تکرار مقادیر. کاربردی برای رتبه‌بندی بازیکنان در مسابقات.

• DENSE_RANK

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

• NTILE(n)

  تقسیم داده‌ها به گروه‌های متعادل. کاربردی برای تقسیم وظایف بین کارمندان.

مزایای توابع پنجره‌ای در SQL

• حفظ تمامی ردیف‌ها

  برخلاف توابع گروهی، تعداد ردیف‌ها کاهش نمی‌یابد.

• عملیات تحلیلی پیچیده

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

• انعطاف‌پذیری بالا

  می‌توان داده‌ها را با استفاده از PARTITION BY به گروه‌های مختلف تقسیم و محاسبات مستقل انجام داد.

• ترتیب‌دهی آسان

  امکان اعمال محاسبات بر اساس ترتیب دلخواه با ORDER BY وجود دارد.

• کاربرد گسترده

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

• جایگزین ساده برای کوئری‌های پیچیده

  بسیاری از مسائل چندجدولی یا تو در تو با استفاده از این توابع به‌راحتی قابل حل هستند.
  
  
  

تبدیل دستور RANK 

اگر بخواهید از RANK برای حل مسائل خاص استفاده کنید، معمولاً می‌توانید از آن برای پیدا کردن رکوردهایی با رتبه خاص بهره ببرید.

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

WITH RankedSalaries AS (
    SELECT
        Name,
        Department,
        Salary,
        RANK() OVER (PARTITION BY Department ORDER BY Salary DESC) AS Rank
    FROM Employees
)
SELECT * FROM RankedSalaries WHERE Rank = 2;

این دستور ردیف‌هایی با رتبه ۲ را در هر بخش باز می‌گرداند.

مثال پروژه محور از توابع پنجره‌ای در SQL

فرض کنید جدولی به نام Sales داریم که شامل اطلاعات فروش است:

SalesID | Employee | Amount
1       | John     | 200
2       | Alice    | 300
3       | John     | 400
4       | Alice    | 100

استفاده از تابع ROW_NUMBER:

SELECT 
    Employee,
    Amount,
    ROW_NUMBER() OVER (PARTITION BY Employee ORDER BY Amount DESC) AS RowNum
FROM Sales;

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

استفاده از تابع RANK:

SELECT 
    Employee,
    Amount,
    RANK() OVER (PARTITION BY Employee ORDER BY Amount DESC) AS Rank
FROM Sales;

خروجی: رتبه‌بندی فروش‌ها برای هر کارمند با در نظر گرفتن تکرار مقادیر.

استفاده از تابع SUM با پنجره:

SELECT 
    Employee,
    Amount,
    SUM(Amount) OVER (PARTITION BY Employee) AS TotalSales
FROM Sales;

خروجی: جمع کل فروش برای هر کارمند بدون کاهش تعداد ردیف‌ها.