Shulou(Shulou.com)06/01 Report--

In order to make it easy for you to learn and test, all the examples are set up under Oracle's own user Scott.

Note: the red order by in the title indicates that you must bring order by when using this method.

Rank () / dense_rank () over (partition by... order by...)

Now customers have such a demand to query the information of the highest-paid employees in each department. I believe that students with some knowledge of oracle application can write the following SQL sentence:

[sql] view plaincopy

Select e.ename, e.job, e.sal, e.deptno

From scott.emp e

(select e.deptno, max (e.sal) sal from scott.emp e group by e.deptno) me

Where e.deptno = me.deptno

And e.sal = me.sal

While meeting the needs of customers, we should habitually think about whether there are other ways. This is for sure, that is, using rank () over (partition by...) in the heading of this section Or dense_rank () over (partition by...) The syntax and SQL are as follows:

[sql] view plaincopy

Select e.ename, e.job, e.sal, e.deptno

From (select e.ename

E.job

E.sal

E.deptno

Rank () over (partition by e.deptno order by e.sal desc) rank

From scott.emp e) e

Where e.rank = 1

[sql] view plaincopy

Select e.ename, e.job, e.sal, e.deptno

From (select e.ename

E.job

E.sal

E.deptno

Dense_rank () over (partition by e.deptno order by e.sal desc) rank

From scott.emp e) e

Where e.rank = 1

Why did you get the same result as the above statement? Here is an additional explanation of the rank () / dense_rank () over (partition by e.deptno order by e.sal desc) syntax.

Over: on what terms.

Partition by e.deptno: divided by department number (partition).

Order by e.sal desc: sort by salary from highest to lowest (when using rank () / dense_rank (), you must carry order by otherwise it is illegal)

Rank () / dense_rank (): rating

The whole sentence means: employees are graded by salary from high to low on the basis of division, and the "rank" is represented by a number from small to large (the minimum value must be 1).

So what's the difference between rank () and dense_rank ()?

Rank (): jump sort, if there are two first levels, then there is the third level.

Dense_rank (): sort sequentially, if there are two first levels, then there is still the second level.

Small homework: inquire about the employee information of the department's minimum wage.

Second, min () / max () over (partition by...)

Now that we have obtained the department's maximum / minimum wage, the customer demand is coming again. We can calculate the difference between the employee's wage and the department's maximum / minimum wage while inquiring for employee information. This is relatively simple, which is modified on the basis of the groupby statement in the first section:

[sql] view plaincopy

Select e.ename

E.job

E.sal

E.deptno

E.sal-me.min_sal diff_min_sal

Me.max_sal-e.sal diff_max_sal

From scott.emp e

(select e.deptno, min (e.sal) min_sal, max (e.sal) max_sal

From scott.emp e

Group by e.deptno) me

Where e.deptno = me.deptno

Order by e.deptno, e.sal

Above we use min () and max (), the former for the minimum and the latter for the maximum. If these two methods cooperate with over (partition by...) What will be the effect of using it? Take a look at the following SQL statement:

[sql] view plaincopy

Select e.ename

E.job

E.sal

E.deptno

Nvl (e.sal-min (e.sal) over (partition by e.deptno), 0) diff_min_sal

Nvl (max (e.sal) over (partition by e.deptno)-e.sal, 0) diff_max_sal

From scott.emp e

The query results of these two statements are the same. You can see that min () and max () actually seek the minimum and maximum values, but on the basis of the partition by partition.

Small assignment: what will be the result if you add order by to this example?

Third, lead () / lag () over (partition by. Order by.)

Chinese people love to keep up with others, save face, and are famous all over the world. Customers are even better, and after comparing with the maximum / minimum wage, they still feel unsatisfied. This time, they put forward a more abnormal demand to calculate the difference between an individual's salary and a higher / lower salary. This requirement is really embarrassing for me, and I don't know how to implement it in the groupby statement. But... no, no, no. Now that we have over (partition by...), everything seems so simple. As follows:

[sql] view plaincopy

Select e.ename

E.job

E.sal

E.deptno

Lead (e.sal, 1,0) over (partition by e.deptno order by e.sal) lead_sal

Lag (e.sal, 1,0) over (partition by e.deptno order by e.sal) lag_sal

Nvl (lead (e.sal) over (partition by e.deptno order by e.sal)-e.sal

0) diff_lead_sal

Nvl (e.sal-lag (e.sal) over (partition by e.deptno order by e.sal), 0) diff_lag_sal

From scott.emp e

After reading the above sentence, will everyone also feel a false alarm (suddenly excited after a cold sweat, so it is easy to catch a cold)? Let's explain the two new methods used above.

Lead (column name, NMagol m): the value of the nth row after the current record, if not, the default value is m; if there is no parameter njournal m, the value of the record in the first row after the current record is found. If not, the default value is null.

Lag (column name, NMagol m): the value recorded in the nth row before the current record, if not, the default value is m; if there is no parameter njournal m, the value of the record in the first row before the current record is found, and if not, the default value is null.

The following is a list of the applications of some commonly used methods in this syntax (Note: the method with order by clause states that you must use order by when using this method):

[sql] view plaincopy

Select e.ename

E.job

E.sal

E.deptno

First_value (e.sal) over (partition by e.deptno) first_sal

Last_value (e.sal) over (partition by e.deptno) last_sal

Sum (e.sal) over (partition by e.deptno) sum_sal

Avg (e.sal) over (partition by e.deptno) avg_sal

Count (e.sal) over (partition by e.deptno) count_num

Row_number () over (partition by e.deptno order by e.sal) row_num

From scott.emp e

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.