Oracle over函数

sql over的作用及用法

RANK ( ) OVER ( [query_partition_clause] order_by_clause )
DENSE_RANK ( ) OVER ( [query_partition_clause] order_by_clause )
可实现按指定的字段分组排序，对于相同分组字段的结果集进行排序,
其中PARTITION BY 为分组字段，ORDER BY 指定排序字段


over不能单独使用，要和分析函数：rank(),dense_rank(),row_number()等一起使用。
其参数：over（partition by columnname1 order by columnname2）
含义：按columname1指定的字段进行分组排序，或者说按字段columnname1的值进行分组排序。
例如：employees表中，有两个部门的记录：department_id ＝10和20
select department_id，rank（） over（partition by department_id order by salary) from employees就是指在部门10中进行薪水的排名，在部门20中进行薪水排名。如果是partition by org_id，则是在整个公司内进行排名。

SQL code:
WITH
OBJ AS(
SELECT
name, type

目的：以oracle自带的scott模式为测试环境，主要通过试验体会分析函数的用法。

类似 sum(...) over ... 的使用


1.原表信息：

SQL> break on deptno skip 1 -- 为效果更明显，把不同部门的数据隔段显示。
SQL> select deptno,ename,sal
2 from emp
3 order by deptno;

    DEPTNO ENAME             SAL
---------- ---------- ----------
        10 CLARK            2450
           KING             5000
           MILLER           1300

        20 SMITH             800
           ADAMS            1100
           FORD             3000
           SCOTT            3000
           JONES            2975

        30 ALLEN            1600
           BLAKE            2850
           MARTIN           1250
           JAMES             950
           TURNER           1500
           WARD             1250


已选择14行。



2.先来一个简单的，注意over(...)条件的不同，
使用 sum(sal) over (order by ename)... 查询员工的薪水“连续”求和,
注意over (order by ename)如果没有order by 子句，求和就不是“连续”的，
放在一起，体会一下不同之处：

SQL> break on '' -- 取消数据分段显示
SQL> select deptno,ename,sal,
2 sum(sal) over (order by ename) 连续求和,
3 sum(sal) over () 总和,                  -- 此处sum(sal) over () 等同于sum(sal)
4 100*round(sal/sum(sal) over (),4) "份额(%)"
5 from emp
6 /

    DEPTNO ENAME             SAL   连续求和       总和    份额(%)
---------- ---------- ---------- ---------- ---------- ----------
        20 ADAMS            1100       1100      29025       3.79
        30 ALLEN            1600       2700      29025       5.51
        30 BLAKE            2850       5550      29025       9.82
        10 CLARK            2450       8000      29025       8.44
        20 FORD             3000      11000      29025      10.34
        30 JAMES             950      11950      29025       3.27
        20 JONES            2975      14925      29025      10.25
        10 KING             5000      19925      29025      17.23
        30 MARTIN           1250      21175      29025       4.31
        10 MILLER           1300      22475      29025       4.48
        20 SCOTT            3000      25475      29025      10.34
        20 SMITH             800      26275      29025       2.76
        30 TURNER           1500      27775      29025       5.17
        30 WARD             1250      29025      29025       4.31

已选择14行。


3.使用子分区查出各部门薪水连续的总和。注意按部门分区。注意over(...)条件的不同，
sum(sal) over (partition by deptno order by ename) 按部门“连续”求总和
sum(sal) over (partition by deptno) 按部门求总和
sum(sal) over (order by deptno，ename) 不按部门“连续”求总和
sum(sal) over () 不按部门，求所有员工总和，效果等同于sum(sal)。

SQL> break on deptno skip 1 -- 为效果更明显，把不同部门的数据隔段显示。
SQL> select deptno,ename,sal,
2 sum(sal) over (partition by deptno order by ename) 部门连续求和,--各部门的薪水"连续"求和
3 sum(sal) over (partition by deptno) 部门总和, -- 部门统计的总和，同一部门总和不变
4 100*round(sal/sum(sal) over (partition by deptno),4) "部门份额(%)",
5 sum(sal) over (order by deptno,ename) 连续求和, --所有部门的薪水"连续"求和
6 sum(sal) over () 总和, -- 此处sum(sal) over () 等同于sum(sal)，所有员工的薪水总和
7 100*round(sal/sum(sal) over (),4) "总份额(%)"
8 from emp
9 /

DEPTNO ENAME    SAL 部门连续求和   部门总和 部门份额(%)   连续求和   总和 总份额(%)
------ ------ ----- ------------ ---------- ----------- ---------- ------ ----------
    10 CLARK   2450         2450       8750          28       2450 29025       8.44
       KING    5000         7450       8750       57.14       7450 29025      17.23
       MILLER 1300         8750       8750       14.86       8750 29025       4.48

    20 ADAMS   1100         1100     10875       10.11       9850 29025       3.79
       FORD    3000         4100      10875       27.59      12850 29025      10.34
       JONES   2975         7075      10875       27.36      15825 29025      10.25
       SCOTT   3000        10075      10875       27.59      18825 29025      10.34
       SMITH    800        10875      10875        7.36      19625 29025       2.76

    30 ALLEN   1600         1600       9400       17.02      21225 29025       5.51
       BLAKE   2850         4450       9400       30.32      24075 29025       9.82
       JAMES    950         5400       9400       10.11      25025 29025       3.27
       MARTIN 1250         6650       9400        13.3      26275 29025       4.31
       TURNER 1500         8150       9400       15.96      27775 29025       5.17
       WARD    1250         9400       9400        13.3      29025 29025       4.31


已选择14行。



4.来一个综合的例子，求和规则有按部门分区的，有不分区的例子
SQL> select deptno,ename,sal,sum(sal) over (partition by deptno order by sal) dept_sum,
2 sum(sal) over (order by deptno,sal) sum
3 from emp;

    DEPTNO ENAME             SAL   DEPT_SUM        SUM
---------- ---------- ---------- ---------- ----------
        10 MILLER           1300       1300       1300
           CLARK            2450       3750       3750
           KING             5000       8750       8750

        20 SMITH             800        800       9550
           ADAMS            1100       1900      10650
           JONES            2975       4875      13625
           SCOTT            3000      10875      19625
           FORD             3000      10875      19625

        30 JAMES             950        950      20575
           WARD             1250       3450      23075
           MARTIN           1250       3450      23075
           TURNER           1500       4950      24575
           ALLEN            1600       6550      26175
           BLAKE            2850       9400      29025


已选择14行。



5.来一个逆序的，即部门从大到小排列，部门里各员工的薪水从高到低排列，累计和的规则不变。

SQL> select deptno,ename,sal,
2 sum(sal) over (partition by deptno order by deptno desc,sal desc) dept_sum,
3 sum(sal) over (order by deptno desc,sal desc) sum
4 from emp;

    DEPTNO ENAME             SAL   DEPT_SUM        SUM
---------- ---------- ---------- ---------- ----------
        30 BLAKE            2850       2850       2850
           ALLEN            1600       4450       4450
           TURNER           1500       5950       5950
           WARD             1250       8450       8450
           MARTIN           1250       8450       8450
           JAMES             950       9400       9400

        20 SCOTT            3000       6000      15400
           FORD             3000       6000      15400
           JONES            2975       8975      18375
           ADAMS            1100      10075      19475
       MITH             800      10875      20275

        10 KING             5000       5000      25275
           CLARK            2450       7450      27725
           MILLER           1300       8750      29025


已选择14行。



6.体会：在"... from emp;"后面不要加order by 子句，使用的分析函数的(partition by deptno order by sal)
里已经有排序的语句了，如果再在句尾添加排序子句，一致倒罢了，不一致，结果就令人费劲了。如：

SQL> select deptno,ename,sal,sum(sal) over (partition by deptno order by sal) dept_sum,
2 sum(sal) over (order by deptno,sal) sum
3 from emp
4 order by deptno desc;

    DEPTNO ENAME             SAL   DEPT_SUM        SUM
---------- ---------- ---------- ---------- ----------
        30 JAMES             950        950      20575
           WARD             1250       3450      23075
           MARTIN           1250       3450      23075
           TURNER           1500       4950      24575
           ALLEN            1600       6550      26175
           BLAKE            2850       9400      29025

        20 SMITH             800        800       9550
           ADAMS            1100       1900      10650
           JONES            2975       4875      13625
           SCOTT            3000      10875      19625
           FORD             3000      10875      19625

        10 MILLER           1300       1300       1300
           CLARK            2450       3750       3750
           KING             5000       8750       8750


已选择14行


==================================================================
利用over实现的分页功能：
--假设code1,code2为用来分页的KEY，每页显示5第数据
select code1,code2,code3,
ceil(count(*) over(partition by code1,code2 order by rownum)/5),
count(*) over(partition by code1,code2)
from ma_kbn order by code1,code2

==================================================================