توابع تحلیلی در پایگاه داده SQL

توابع تحلیلی در پایگاه داده SQL

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

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

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

این قابلیت باعث می‌شود که بتوانید تحلیل‌های پیشرفته‌ای مانند رتبه‌بندی، مقایسه بین مقادیر قبلی و بعدی، و محاسبات متحرک (Running Totals) را به‌راحتی انجام دهید.

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

توابعی مانند LAG و LEAD برای مقایسه مقادیر در ردیف‌های قبلی و بعدی، ROW_NUMBER برای تخصیص شماره ردیف، و RANK و DENSE_RANK برای رتبه‌بندی، از جمله این ابزارها هستند.

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

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

•  تابع LAG

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

نحوه استفاده:

LAG(column_name, offset, default_value) OVER (
    PARTITION BY column_name
    ORDER BY column_name
)

column_name: ستونی که مقدار آن بازگردانده می‌شود.

offset: تعداد ردیف‌هایی که می‌خواهید به عقب برگردید (پیش‌فرض ۱ است).

default_value: مقداری که در صورت نبود مقدار بازگردانده می‌شود.

مثال:

SELECT
    employee_id,
    salary,
    LAG(salary) OVER (PARTITION BY department_id ORDER BY hire_date) AS previous_salary
FROM employees;

این کوئری حقوق کارمندان را با حقوق کارمند قبلی در همان بخش مقایسه می‌کند.

• تابع LEAD

  تابع LEAD برعکس تابع LAG عمل می‌کند و مقدار سطر بعدی را نسبت به سطر فعلی بازمی‌گرداند.
  این تابع برای پیش‌بینی مقادیر آینده یا مقایسه با مقادیر بعدی مفید است.

نحوه استفاده:

LEAD(column_name, offset, default_value) OVER (
    PARTITION BY column_name
    ORDER BY column_name
)

column_name: ستونی که مقدار آن بازگردانده می‌شود.

offset: تعداد ردیف‌هایی که می‌خواهید به جلو بروید (پیش‌فرض ۱ است).

default_value: مقداری که در صورت نبود مقدار بازگردانده می‌شود.

مثال:

SELECT
    employee_id,
    salary,
    LEAD(salary) OVER (PARTITION BY department_id ORDER BY hire_date) AS next_salary
FROM employees;

این کوئری حقوق کارمندان را با حقوق کارمند بعدی در همان بخش مقایسه می‌کند.

 

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

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

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

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

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

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

 

 

•  تابع NULLIF

  تابع NULLIF برای مقایسه دو مقدار استفاده می‌شود و اگر دو مقدار برابر باشند، مقدار NULL را بازمی‌گرداند.
  در غیر این صورت، مقدار اول را بازمی‌گرداند.

نحوه استفاده:

NULLIF(expression1, expression2)

expression1 و expression2: عبارات یا مقادیری که باید مقایسه شوند.

مثال:

SELECT NULLIF(10, 10) AS result1, NULLIF(10, 20) AS result2;

result1 مقدار NULL را بازمی‌گرداند زیرا ۱۰ و ۱۰ برابرند.

result2 مقدار ۱۰ را بازمی‌گرداند زیرا ۱۰ و ۲۰ برابر نیستند.

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

SELECT
    amount / NULLIF(quantity, 0) AS average_price
FROM sales;

• تابع SESSION_USER

  تابع SESSION_USER نام کاربری فعلی را که برای اتصال به پایگاه داده استفاده شده است، بازمی‌گرداند.
  این تابع برای ثبت تغییرات یا رهگیری فعالیت‌های کاربران مفید است.

نحوه استفاده:

SESSION_USER

مثال:

SELECT SESSION_USER AS current_user;

این کوئری نام کاربری فعلی را نشان می‌دهد.

• تابع SESSIONPROPERTY

  تابع SESSIONPROPERTY اطلاعات مربوط به جلسه فعلی را بازمی‌گرداند.
  این تابع می‌تواند جزئیاتی مانند تنظیمات زبان یا وضعیت‌های خاص جلسه را نشان دهد.

نحوه استفاده:

SESSIONPROPERTY('property_name')

property_name: نام خاصیتی که می‌خواهید مقدار آن را دریافت کنید.

مثال:

SELECT SESSIONPROPERTY('language') AS language_setting;

این کوئری زبان تنظیم شده برای جلسه فعلی را بازمی‌گرداند.

• تابع SYSTEM_USER

  تابع SYSTEM_USER نام کاربری ویندوز یا سیستم عامل را که اتصال به SQL Server را ایجاد کرده است، بازمی‌گرداند.
  این تابع مشابه SESSION_USER است اما اطلاعات سیستم را بازمی‌گرداند.

نحوه استفاده:

SYSTEM_USER

مثال:

SELECT SYSTEM_USER AS system_user;

این کوئری نام کاربری سیستم عامل را نشان می‌دهد.

•  تابع USER_NAME

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

نحوه استفاده:

USER_NAME([user_id])

user_id: (اختیاری) شناسه کاربری که می‌خواهید نام آن را دریافت کنید.

مثال:

SELECT USER_NAME() AS current_user_name;

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