ESC را فشار دهید تا بسته شود

زمیوس آموزش، یادگیری و سرگرمی

بررسی جامع توابع تحلیلی در Oracle: از ROW_NUMBER تا MATCH_RECOGNIZE

بررسی جامع توابع تحلیلی در Oracle

توابع تحلیلی (Analytical Functions) در Oracle یکی از آن قابلیت‌هایی هستند که وقتی یادشان می‌گیریم، انگار یک دنیای تازه در SQL به روی ما باز می‌شود.

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

در این مقاله آموزش اوراکل در بخش آموزش Oracle SQL، قصد داریم با زبانی ساده، کاربردی و همراه با مثال‌های واقعی، سراغ بررسی کامل این توابع برویم؛ از توابع ابتدایی مثل ROW_NUMBER() تا پیشرفته‌ترینشان یعنی MATCH_RECOGNIZE.

آیا می‌خوای بدونی که در اوراکل کوئری من بهتره از Full Table Scan استفاده می کنه یا از Index Scan  ، پیشنهاد می شود نوشته زیر را مطالعه کنید:

در این نوشته شما می خوانید

توابع تحلیلی در اوراکل چیستند؟

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

این یعنی مثلاً بتوانید ببینید:

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

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

توابع تحلیلی همان چیزهایی‌اند که این تحلیل‌ها را ممکن می‌کنند.

ساختار OVER() و مفاهیم پایه

تقریباً تمام توابع تحلیلی از کلمه کلیدی OVER() استفاده می‌کنند. بیایید ساختار آن را با هم ببینیم:

				
					FUNCTION(...) OVER (
  PARTITION BY ...
  ORDER BY ...
  ROWS BETWEEN ...
)

تقریباً تمام توابع تحلیلی از کلمه کلیدی OVER() استفاده می‌کنند. بیایید ساختار آن را با هم ببینیم:

🔸 PARTITION BY چیست؟

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

مثلاً اگر بخواهیم در یک شرکت، حقوق افراد را در چارچوب هر دپارتمان مقایسه کنیم، از PARTITION BY deptno استفاده می‌کنیم.

🔸 ORDER BY چیست؟

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

مثلاً وقتی می‌خواهیم رتبه‌بندی کنیم، باید مشخص کنیم بر چه اساسی رتبه‌ها داده شوند.

🔸 ROWS BETWEEN چیست؟

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

مثال:

				
					SUM(sal) OVER (ORDER BY sal ROWS BETWEEN 2 PRECEDING AND CURRENT ROW)

توابع رتبه‌بندی (Ranking Functions)

🔸 ()ROW_NUMBER

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

				
					SELECT empno, ename, deptno,
       ROW_NUMBER() OVER (PARTITION BY deptno ORDER BY sal DESC) AS rn
FROM emp;

مثال کاربردی: اگر بخواهید فقط نفر اول با بیشترین حقوق را از هر دپارتمان بگیرید، می‌توانید از این تابع در یک CTE استفاده کنید و WHERE rn = 1 بزنید.

🔸 ()RANK

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

				
					SELECT ename, sal,
       RANK() OVER (ORDER BY sal DESC) AS rnk
FROM emp;

نکته: اگر دو نفر رتبه ۱ باشند، نفر بعدی رتبه ۳ خواهد داشت.

🔸 ()DENSE_RANK

مثل RANK است با این تفاوت که پرش نمی‌کند. در مثال بالا، نفر بعدی رتبه ۲ خواهد بود.

				
					SELECT ename, sal,
       DENSE_RANK() OVER (ORDER BY sal DESC) AS drnk
FROM emp;

🔸 (n)NTILE

برای تقسیم‌بندی داده‌ها به بخش‌های مساوی (مثلاً ۴ چارک)

				
					SELECT ename, sal,
       NTILE(4) OVER (ORDER BY sal DESC) AS quartile
FROM emp;

کاربرد: تحلیل توزیع نمره یا فروش در چارک‌ها.

توابع مرجع: LAG و LEAD

🔸 ()LAG

به شما مقدار ردیف قبلی را می‌دهد.

🔸 ()LEAD

مقدار ردیف بعدی را می‌دهد.

				
					SELECT ename, sal,
       LAG(sal) OVER (ORDER BY sal) AS prev_sal,
       LEAD(sal) OVER (ORDER BY sal) AS next_sal
FROM emp;

کاربرد: بررسی اینکه آیا حقوق کارمند فعلی نسبت به قبلی افزایش داشته یا کاهش.

توابع تجمیعی به شکل تحلیلی در اوراکل

می‌توان از توابع SUM، AVG، COUNT، MIN، MAX به صورت تحلیلی استفاده کرد:

				
					SELECT empno, deptno, sal,
       SUM(sal) OVER (PARTITION BY deptno) AS dept_total,
       AVG(sal) OVER (PARTITION BY deptno) AS dept_avg
FROM emp;

کاربرد: مقایسه حقوق کارمند نسبت به هم‌دپارتمانی‌ها

توابع FIRST_VALUE و LAST_VALUE

گاهی می‌خواهید اولین یا آخرین مقدار در یک پارتیشن را برگردانید:

				
					SELECT ename, deptno, sal,
       FIRST_VALUE(sal) OVER (PARTITION BY deptno 
       ORDER BY sal DESC) AS top_salary,
       LAST_VALUE(sal) OVER (PARTITION BY deptno ORDER BY sal ASC ROWS 
       BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
       AS bottom_salary
FROM emp;

MATCH_RECOGNIZE — الگوها را پیدا کن!

تابع MATCH_RECOGNIZE یکی از قابلیت‌های فوق‌العاده Oracle 12c و بالاتر است که امکان تشخیص الگوهای پیچیده در داده‌ها را می‌دهد.

فرض کنید می‌خواهید بررسی کنید که آیا مشتری در چند خرید متوالی افت داشته و سپس دوباره رشد کرده یا نه؟

مثال:

				
					SELECT * FROM sales
MATCH_RECOGNIZE (
  PARTITION BY customer_id
  ORDER BY sale_date
  MEASURES
    STRT.sale_date AS start_down,
    LAST(UP.sale_date) AS recover_date
  PATTERN (STRT DOWN+ UP+)
  DEFINE
    DOWN AS amount < PREV(amount),
    UP AS amount > PREV(amount)
);

کاربردهای واقعی:

  • کشف رفتار مشتریان
  • تحلیل بورس و قیمت سهام
  • شناسایی تقلب
  • پیش‌بینی روندها

سوالات متداول درباره Analytical Functions در Oracle

هر سه تابع برای رتبه‌بندی سطرها استفاده می‌شوند، اما تفاوت در نحوه برخورد با تکرارهاست:

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

برای این کار از FIRST_VALUE() و LAST_VALUE() استفاده می‌شود.

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

مثال:

FIRST_VALUE(sal) OVER (PARTITION BY deptno ORDER BY sal DESC)

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

MATCH_RECOGNIZE برای تشخیص الگوهای پیچیده در داده‌ها (مثل کاهش و افزایش قیمت، تشخیص رفتار مشتری، یا حتی تحلیل روند بازار بورس) استفاده می‌شود.

این قابلیت از Oracle 12c معرفی شده و برای تحلیل سری‌های زمانی و sequence-based analysis بسیار مفید است.

قطعاً! توابع تحلیلی مثل LAG, LEAD, RANK, NTILE, MATCH_RECOGNIZE ابزاری فوق‌العاده برای تحلیل روندها، مقایسه داده‌ها، ردیابی تغییرات و ساخت گزارش‌های مدیریتی هوشمند هستند—بدون نیاز به استفاده از JOINهای سنگین یا subqueryهای پیچیده.

📦 جمع‌بندی

توابع تحلیلی در Oracle واقعاً دست برنامه‌نویس و تحلیل‌گر داده را باز می‌گذارند.

آن‌ها کمک می‌کنند داده‌ها را همان‌طور که هستند، دقیق‌تر و کاربردی‌تر ببینیم.

فرقی ندارد مشغول تحلیل فروش هستید یا حقوق، یا روند مشتریان؛ توابعی مثل ROW_NUMBER و MATCH_RECOGNIZE می‌توانند ابزار طلایی شما باشند.

📥 اگر سوالی داری در مورد Analytical Functions در اوراکل داری، در بخش کامنت‌ها بپرس.

سؤالی درباره این مقاله داری؟

اگر نکته‌ای در این مقاله برات مبهم بود یا خواستی بیشتر بدونی، همین حالا برام بنویس تا دقیق و صمیمی پاسخت رو بدم — مثل یه گفت‌وگوی واقعی 💬

برو به صفحه پرسش و پاسخ

میثم راد

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

دیدگاهتان را بنویسید

نشانی ایمیل شما منتشر نخواهد شد. بخش‌های موردنیاز علامت‌گذاری شده‌اند *