Oracle advanced query Over (partition by..)

In order to facilitate learning and testing, all examples are created under the Oracle's own user Scott.

CREATE TABLE EMP (  empno    number (4) Not null,  ename    VARCHAR2 (Ten),  job   &NB Sp  VARCHAR2 (9),  Mgr      number (4),  hiredate date,  sal      number ( 7,2),  Comm     Number (7,2),  deptno   Number (2)) ALTER TABLE emp  add constraint pk_emp Prim ary Key (EMPNO); insert into EMP (EMPNO, ename, Job, Mgr, HireDate, Sal, Comm, Deptno)         valu ES (7369, ' SMITH ', ' clerk ', 7902, to_date (' 17-12-1980 ', ' dd-mm-yyyy '), n, null, +); insert into EMP (empno, ename, Job, Mgr, HireDate, Sal, Comm, Deptno)         values (7499, ' ALLEN ', ' salesman ', 7698, To_date (' 20-02- 1981 ', ' Dd-mm-yyyy '), (,), "INSERT INTO EMP" (Empno, ename, Job, Mgr, HireDate, Sal, Comm, Deptno)    &NB Sp    values (7521, ' WARD ', ' salesman ', 7698, to_date (' 22-02-1981 ', ' dd-mm-yyyy '), 1250, +); insert INTO EMP ( Empno, ename, job, MGR, HireDate, Sal, Comm, Deptno)         values (7566, ' JONES ', ' MANAGER ', 7839, to_date (' 02-04-19 Bayi ', ' dd-mm-yyyy '), 2975, NULL, +); insert into EMP (empno, ename, Job, Mgr, HireDate, Sal, Comm, Deptno)    &nbs P    values (7654, ' MARTIN ', ' salesman ', 7698, to_date (' 28-09-1981 ', ' dd-mm-yyyy '), 1250, 1400, +); INSERT INTO EM P (empno, ename, Job, Mgr, HireDate, Sal, Comm, Deptno)         values (7698, ' BLAKE ', ' MANAGER ', 7839, To_date (' 01-05-1981 ', ' dd-mm-yyyy '), 2850, NULL, +); insert into EMP (empno, ename, Job, Mgr, HireDate, Sal, Comm, D Eptno)         values (7782, ' CLARK ', ' MANAGER ', 7839, to_date (' 09-06-1981 ', ' dd-mm-yyyy '), 2450 , NULL, ten); insert into EMP (empno, ename, Job, Mgr, HireDate, Sal, Comm, Deptno)         values ( 7788, ' SCOTT ', ' ANALYST ', 7566, to_date (' 19-04-1987 ', ' dd-mm-yyyy '), max, NULL, +); insert into EMP (empno, ename, job, M GR, HireDate, Sal, comm, Deptno)         values (7839, ' KING ', ' president ', NULL, to_date (' 17-11-1981 ', ' dd-mm-yyyy '), 5 Insert into EMP (empno, ename, Job, Mgr, HireDate, Sal, Comm, Deptno)         value  S (7844, ' TURNER ', ' salesman ', 7698, to_date (' 08-09-1981 ', ' dd-mm-yyyy '), 0, +); insert into EMP (empno, ename, Job, Mgr, HireDate, Sal, Comm, Deptno)         values (7876, ' ADAMS ', ' clerk ', 7788, to_date (' 23-05-19 "Dd-mm-yyyy"), 1100, NULL, +); insert into EMP (empno, ename, Job, Mgr, HireDate, Sal, Comm, Deptno)    &nbs P    values (7900, ' JAMES ', ' clerk ', 7698, to_date (' 03-12-1981 ', ' dd-mm-yyyy '), 950, NULL, +); insert into EMP (em Pno, ename, Job, Mgr, HireDate, Sal, Comm, Deptno)         values (7902, ' FORD ', ' ANALYST ', 7566, To_date (' 03-12-1981 ', ' dd-mm-yyyy '), +, NULL, +); insert into EMP (empno, ename, Job, Mgr, HireDate, Sal, Comm, Deptno)      &NBsp  values (7934, ' MILLER ', ' clerk ', 7782, to_date (' 23-01-1982 ', ' dd-mm-yyyy '), 1300, NULL, 10);

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

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

Now that the customer has a requirement to query the highest-paid employees in each department, it is believed that some of the students with Oracle Application knowledge can write the following SQL statement:

SELECT * FROM (select ename, Job, HireDate, E.sal, E.deptno from          emp E,               (select Deptno, max (sal) sal from EMP Grou P by deptno) T         where E.deptno = T.deptno and           e.sal = t.sal) Order by Deptno;        SELECT * FROM (select ename name, Job occupation, HireDate entry date, e.sal salary, E.deptno Department from          Emp E,               (select Deptno, max (SAL) Sal from EMP Group by Deptno) T         where E.deptno = T.deptno and           e.sal = T.sal) the order by department;

In meeting the needs of customers, we should be accustomed to think about whether there are other ways. This is for sure, using rank () over (partition by ...) in the title of this section. or Dense_rank () over (partition by ...) syntax, SQL is as follows:

Select Empno, ename, Job, HireDate, Sal, Deptno from  (select Empno, ename, Job, HireDate, Sal, Deptno, Rank () over (par Tition by Deptno ORDER BY Sal Desc) (r from emp) where r = 1; Select Empno, ename, Job, HireDate, Sal, Deptno from  (select Empno, ename, Job, HireDate, Sal, Deptno, Dense_rank () ov ER (partition by deptno ORDER BY Sal Desc) r from emp) where r = 1

Why do you get the same result as the above statement? This is supplemented by the syntax of rank ()/dense_rank () over (partition by the E.deptno order by e.sal Desc).
Over :on what terms.
partition by E.deptno:Divide by department number (partition).
ORDER BY e.sal Desc:Sort by salary from highest to lowest (use rank ()/dense_rank (), must take order by otherwise illegal)
rank ()/dense_rank ():Grade
The whole statement means that the employee is graded from high to low on a per-department basis, and the "level" is represented by a small-to-large number (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 the third level.
Dense_rank (): sequential ordering, if there are two first levels, the next is still the second level.

Small job: Query employee information for the department's minimum wage.

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

We have now queried the department's maximum/minimum wage, and the customer's demand has come again, while the employee information is being queried and the difference between the employee's salary and the department's maximum/minimum wage is calculated. This is still relatively simple, in the first section of the GROUPBY statement based on the following changes:

--the difference between the employee's salary and the highest/lowest employee's salary in the department is calculated while each employee's information is queried

Select Ename name, Job occupation, HireDate entry date, E.deptno department, e.sal salary, e.sal-me.min_sal minimum difference, me.max_sal-e.sal maximum difference from  emp E , (select Deptno, min (sal) min_sal, Max (SAL) max_sal from EMP Group by deptno) Me   where E.deptno = Me.deptno ORDER BY E.deptno, E.sal;  

Above we used the min () and Max (), the former to find the minimum value, the latter to find the maximum value. If these two methods match over (partition by ...) What would be the effect of using it? Let's look at the following SQL statement:

Select Ename name, Job occupation, HireDate entry date, Deptno Department, Min (SAL) over (partition by Deptno) departmental minimum wage, max (SAL) over (part  Ition by Deptno) department maximum wage from EMP order by Deptno, Sal; Select Ename name, Job occupation, HireDate entry date, Deptno Department, NVL (Sal-min by Partition), 0) Department minimum wage Difference, NV L (Max (SAL) over (partition by Deptno)-Sal, 0) department maximum wage difference from emp order by Deptno, Sal;

The query results for these two statements are the same, and you can see that min () and Max () actually ask for the minimum and maximum values, only on the partition by partition.

Small jobs: What happens if you add order by in this example?

Iii. lead ()/lag () over (partition by ...)

Chinese people love to compare, good face, famous world. The customer is better this one, in and the maximum/minimum wage after the comparison still feel not enjoyable, this time put forward a more perverted demand, the calculation of personal wages and higher than their own/lower one salary difference. This requirement really makes me very embarrassed, in the GroupBy statement do not know how to achieve. But.... Now that we have over (partition by ...), everything seems so simple. As follows:

--Calculating the difference between a personal salary and a one-off/lower salary

Select Ename name, Job occupation, Sal Payroll, Deptno Department, lead     (Sal, 1, 0) over (partition by Deptno order by Sal) is  higher than his salary in the Department of the previous one, 
   lag (Sal, 1, 0) over (partition by the Deptno order by Sal) is  a lower-paid department after one,     Nvl (lead (SAL) over (partition by Deptno Orde R by Sal)-Sal, 0) is  higher than its own salary of the department before a difference,      NVL (Sal-lag (SAL) over (partition by Deptno order by Sal), 0)   than their own high-wage department after a Difference from EMP;   

Look at the above statement, we will also feel a false alarm (surprised out a cold sweat after the chicken frozen up, so easy to catch colds)? Let's take a look at the two new methods used above.
Lead (column name, n,m):The value of the < column name > for the nth row following the current record, no default value is M, and if no parameter n,m is found, the first row after the current record < The value of the column name >, no default value is null.
Lag (column name, n,m):The value of the < column name > of the row N record in front of the current record, the default value is M, and if no parameter n,m is found, the first row before the current record < The value of column name >, no default value is null.

The following is a list of some common methods used in this syntax ( Note: The method with the ORDER BY clause must have an order by when using the method):

Select Ename name, Job occupation, Sal salary, Deptno Department,     First_value (SAL) over (partition by Deptno) First_sal,     last_value (SAL) Over (partition by Deptno) last_sal,     sum (SAL) over (partition by DEPTNO) Department Total payroll,     avg (SAL) over (partition by Deptno) Department average salary,     count (1) over (partition by DEPTNO) department total,     Row_number () over (partition by Deptno order by Sal) ordinal from  E mp  

Important NOTE: After reading this article, we may have a little misunderstanding, that is over (PARTITION by.) Better than group BY, it is not the case, the former can not replace the latter, and in the implementation of the efficiency of the former is not high, but the former provides more functions only, so I hope that everyone in use in accordance with the needs of the situation to choose.

Original Digest from: http://blog.csdn.net/fu0208/article/details/7179001

Oracle advanced query Over (partition by..)