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

1. The difference between over partition by and group by

-over partition by can display summary data and source data in a row

For example: query the salary of each employee and the maximum salary of the department

-group by can only display grouped data and aggregate results

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

-- query information about the highest-paid employees in each department

Select * from (select emp.*,rank () over (partition by deptno order by sal desc) maxsalno from emp) where maxsalno=1

3. Min () / max () over (partition by...)

-- while querying the information of each employee, calculate the difference between the employee's salary and the highest / lowest wage of the department to which he belongs.

Select a.Maxsal diff from (select emp.*,max (sal) over (partition by deptno) maxsal from emp) aEMPNO ENAME DEPTNO SAL DIFF

--

7782 CLARK 10 2450-2550

7839 KING 10 5000 0

7934 MILLER 10 1300-3700

7566 JONES 20 2975-25

7902 FORD 20 3000 0

7876 ADAMS 20 1100-1900

7369 SMITH 20 800-2200

7788 SCOTT 20 3000 0

7521 WARD 30 1250-1600

7844 TURNER 30 1500-1350

7499 ALLEN 30 1600-1250

EMPNO ENAME DEPTNO SAL DIFF

--

7900 JAMES 30 950-1900

7698 BLAKE 30 2850 0

7654 MARTIN 30 1250-1600

14 rows selected.

Select a.empnorea.enamerea.deptnorea.salcalleglegent diff from (select emp.*,max (sal) over (partition by deptno order by sal) maxsal from emp) a

EMPNO ENAME DEPTNO SAL MAXSAL DIFF

--

7934 MILLER 10 1300 1300 0

7782 CLARK 10 2450 2450 0

7839 KING 10 5000 5000 0

7369 SMITH 20 800 800 0

7876 ADAMS 20 1100 1100 0

7566 JONES 20 2975 2975 0

7788 SCOTT 20 3000 3000 0

7902 FORD 20 3000 3000 0

7900 JAMES 30 950 950 0

7654 MARTIN 30 1250 1250 0

7521 WARD 30 1250 1250 0

EMPNO ENAME DEPTNO SAL MAXSAL DIFF

--

7844 TURNER 30 1500 1500 0

7499 ALLEN 30 1600 1600 0

7698 BLAKE 30 2850 2850 0

14 rows selected.

* the role of order by

That represents the same sort number in the grouping in the current row (max maximum)

Select a. Empno. Ename. deptno. A. Sal. sum (sal) over (partition by deptno order by sal) maxsal from emp a where deptno=20.

EMPNO ENAME DEPTNO SAL MAXSAL

--

7369 SMITH 20 800 800

7876 ADAMS 20 1100 1900

7566 JONES 20 2975 4875

7902 FORD 20 3000 10875 same serial number 4 3000 "3000" 4875 "10875

7788 SCOTT 20 3000 10875 sequence number same 4

* there is sorting, and the current row is summed by rows with the same sequence number in the group.

Lead () / lag () over (partition by... Order by.)

-- calculate the difference between an individual's salary and his / her higher / lower salary

Select EMP.*,sal-lead (SAL,1,sal) over (partition by deptno order by sal) after,sal-lag (sal,1,sal) over (partition by deptno order by sal) before from EMP;select a.Enameline a.deptno line a.Salle lead (SAL,1,sal) over (partition by deptno order by sal) after,sal-lag (sal,1,sal) over (partition by deptno order by sal) before from EMP

EMPNO ENAME DEPTNO SAL AFTER BEFORE

--

7934 MILLER 10 1300-1150 0

7782 CLARK 10 2450-2550 1150

7839 KING 10 5000 0 2550

7369 SMITH 20 800-300 0

7876 ADAMS 20 1100-1875 300

7566 JONES 20 2975-25 1875

7788 SCOTT 20 3000 0 25

7902 FORD 20 3000 00

7900 JAMES 30 950-300 0

7654 MARTIN 30 1250 0 300

7521 WARD 30 1250-250

EMPNO ENAME DEPTNO SAL AFTER BEFORE

--

7844 TURNER 30 1500-100250

7499 ALLEN 30 1600-1250 100

7698 BLAKE 30 2850 0 1250

14 rows selected.

* lead (a _ line b _ c) represents the value of the next b line of the current row a, and if not, the value is c

Lag represents the value of the upper b line of the current row a. If not, the value is c.

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.