Oracle Analytics function

- the other side of SQL summary functions

Aggregate functions are the norm in SQL worlds, where SUM, AVG, COUNT are all the rage. We are here to see another part of summary functions where it is less frequently used because of its obscure syntax that is a wee bit hard to use. Nevertheless, these function will do well in your bag of tricks. Analytics function can shorten the time you took to write long queries with these keywords rather than old-style multiple sub-queries.

TABLENAME: TRANSACT

The above data shows a transaction data for 2 products. Now lets say we need to get the second last scrap activity rows for every LOCATION and every PRODUCT (not the SCRAP rows with MAX(TIME)-1). We can do this by:

select *,
count(*) over (partition by productid, location) as count,
dense_rank() over (partition by productid, location order by time, rownum) as rank
from transact
where activity='SCRAP'

you would get:

from here it is simply filtering out the rows where COUNT= RANK +1 and you would be getting the result you wanted. It will be more difficult to pull this off using aggregate functions and sub-queries. As a comparison, here's the old style query:

select *
from (
select max(rownum) as row_num
from transact
where activity='SCRAP' and rownum not in (select max(rownum) as row_num from transact where activity='SCRAP' group by productid, location)
group by productid, location
) max_rows
inner join
(
select *, rownum
from transact
where activity='SCRAP'
) main on max_rows.row_num=main.rownum

and it will have a restriction: the rows must be inserted with the column TIME = insertion time to ensure the correctness of ROWNUM. This will not always happen in production environment where data editing always happens.

I have yet to find any Microsoft, MySQL equivalent of analytic functions, if there are any gurus out there which have seen it all, please comment!


Regards,
Popo

a Little Jam Can Make a Difference

- Why 10 mins traffic jam everyday makes you want to smash kittys

Please think of the kittys

You know the drill - we would need to get up, put on your smiley mask, kiss your wife goodbye...and went into a comatose state in traffic jams.

We are used to the concept of taking on all hardships as a training of some sort (Karate Kid comes to mind). Billy would say 'Puhlease, traffic jams in commuting are like push-ups in kungfu, it is pointless, they all hate it, but we must do it to become Bruce Lee!'

Long term investment are the real man's way, and we belittle those weaklings that overindulge on short term rewards.

Little do we understand that 'investments' can be beneficial or harmful to us, there is always a flip-side. With our misconception that everything bad can be a good thing in the end, we are in for a long haul where we drag ourselves around like self-mutilating donkeys who enjoyed the torture for years.

This one is an exception

10 mins of daily workout and I mean real workout where you're not looking for bouncing boobs on jogging lanes are really good for your health if you do it for years. Daily traffic jams are not.

Life is a living hell for some people. Do not try to justify the fire from the depths of despair. It is neither good for your skin, nor will it make you the Human Torch if you burn in it long enough.

Next time when you're stuck in them, just let out a scream or turn on your rock n roll radio. You would feel better and your personal pyscho-therapist will thank you too.

Regards,
Popo

Popo at Work

Ah, the joy of getting up early in the morning so that we can plug-in to our computer at work. The Matrix age is here and now!

There is nothing like a fresh dose of Facebook crack when the sun rises; lest we will go into withdrawal state and whine like a kid who cannot have his toys.

Aside from being yelled at by customers, we will also need to attend to every shit coming down from the assholes upstairs:

You are one of the 4 full-of-shit birds

This is just a humble blog with a simple beginning, welcome aboard, stranger.


Regards,
Popo