FIRST_VALUE *** LAST_VALUE Analytic Functions

اخیرا Tim Hall در یوتیوب یک ویدئو در این خصوص منتشر کرد ، و محتویات آن را به یکسری  تصویر تبدیل کردم و در این بلاگ در مورد بدست آوردن اولین و آخرین مقدار یک فیلد، کوچکترین/بزرگترین مقدار مشاهده می کنید. این دو فانکشن از فانکشن های تحلیلی اوراکل می باشد.

همانطور که در تصویر زیر نشان می دهد، می خواهد بر روی مقادیر فیلد SAL  چندین عملیات را انجام دهد. 

در ابتدا می خواهد که اولین مقدار SAL را در جدول زیر بدست آورد،  ازفانکشن زیر استفاده می کند.

FIRST_VALUE(sal) OVER ()

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

خوب حالا می خواهد که کمترین مقدار فیلد SAL یا همان حقوق را بدست آورد. یک سوال مطرح می شود که آیا مرتب بودن اهمیت دارد؟(بله حتما، درادامه متوجه خواهید شد)

دستوری که در این خصوص استفاده می کنیم بدین صورت است:

FIRST_VALUE(sal) OVER (ORDER BY sal)

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

در اینجا می خواهیم بدانیم که آیا می توانیم بطور همزمان از عملگرهای ریاضی هم استفاده کرد؟

یک ستون جدید به Query قبلی اضافه می کد بدین معنی که می خواهد مقدار حقوق هر پرسنلی را از کمترین مقدار حقوق کسر کرده و تفاوت آن را بدست بیاورد:

sal – FIRST_VALUE(sal) OVER (ORDER BY sal)

سوالی مطرح می شود که می توان کمترین حقوق را در هر دپارتمان داشته باشیم و نمایش داد؟

برای حل این موضوع باید از PARTITION BY استفاده کرد:

FIRST_VALUE(sal) OVER (PARTITION BY DEPTNO ORDER BY sal)

در این مرحله می خواهد بیشترین مقدار حقوق را هم بدست آورد. سوال مطرح می شود که آیا می توان از همان فانکشن FIRST_VALUE استفاده کرد و در هنگام مرتب سازی بصورت نزولی مرتب کنیم؟

یک ستون جدید ایجاد می شود:

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

حال می خواد از فانکشنی استفاده کند که بیشترین مقدار حقوق در هر دپارتمان را برای ما بر میگرداند به جای استفاده از FIRST_VALUE

LAST_VALUE(sal) OVER (PARTITION BY DEPTNO ORDER BY sal)

همانطور که در تصویر مشاهده می کنید در خروجی Query تغییرمقداری بین ستون SAL  و ستون HIGHEST_IN_DEPT وجود ندارد و این مشکل مربوط به این فانکشن می باشد.

برای حل این مشکل باید از دستور زیر در فانکشن LAST_VALUE استفاده کرد:

RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

در مرحله آخر می خواهد یکم پیچیده تر موضوع رو نشان دهد و در یک Query بتواند کمترین و بیشترین مقدار حقوق بر اساس دپارتمان را بدست بیاورد.

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

FIRST_VALUE(sal) OVER (PARTITION BY DEPTNO ORDER BY sal)

LAST_VALUE(sal) OVER (PARTITION BY DEPTNO ORDER BY sal

ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)

امیدوار هستم که مورد استفاده دوستان قرار گیرد.