Several examples of Oracle over functions

The data used for the test is the EMP table in Scott/tiger mode:

We use the job and sal two column tests:

The above statement refers to the job placement group (partition by job) and then ranks within each group by salary (SAL). Notice the juxtaposition, for example, of the occupation of Slaesman, there are two 1250 tied first, 1500 is the 3rd place.

Change rank () to Dense_rand to see the difference:

It can be seen that in dealing with side-by-side issues, the rank is not occupied by the rank number.

Another common analysis function is sum () over:

SUM () over calculates the cumulative sum, starting with the first line, accumulating the value of the SAL column, noting that the order by Sal, if not sorted by Sal, results in the following:

If you want to accumulate sums separately for each grouping, simply add the partition partition by:

Several examples of Oracle over functions