Analytical Function at Rescue

With Analytical function in oracle, we can leverage very powerful data projections for complex data requirement.
It exhibit great tool to play around group sets with in a window and achieve desired outcome.

Will walk  through one of requirement which application team were finding difficult to implement, but with analytical function it was piece of cake.

let try to understand requirement first.
Below was the sample data set and as part of requirement for a particular City we need to have new column projecting previous Step_Number of that City within particular ID.

dataset

So expected output was similar to below.
For instance if City mentioned is Pune, for new column we would update step_number from city Pune itself.
In case City repeat itself, Step_Number need to get updated as shown below for ID = 2.

req_dataset

For such requirement, if we try to achieve same through PLSQL/SQL without using analytical functions surely it would add to more LOC and ultimately more overhead.

From Analytical function we would be using below.

MAX(CASE WHEN CITY = ‘Pune’
THEN STEP_NUMBER
END)
OVER (PARTITION BY ID ORDER BY STEP_NUMBER ROWS BETWEEN UNBOUNDED
PRECEDING AND 1 PRECEDING) REQ_COLUMN;

With in a window of ID, we will traverse reverse and find where city is Pune and get Max Step Number.

About Deepak Mahto

Having 8+ Years of relevant/exciting/fruitful/challenging/learning in Oracle Technology. Currently working as Oracle Performance Consultant in an MNC. Hold Expertise in Performance Engineering, SQL Tuning, Database Tuning, SQL, PLSQL Development/Design Considerations and Oracle Internals. I enjoy reading on Oracle internals, posting some of my finding or observations and playing football.
This entry was posted in Helper SQL and tagged , , . Bookmark the permalink.

One Response to Analytical Function at Rescue

  1. Pingback: Multiplication of Rows Data and Analytical Functions. | Oracle Insight and Performance concepts

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s