>

oracle分析函数技艺详解(配上开窗函数over())

- 编辑:乐百家599手机首页 -

oracle分析函数技艺详解(配上开窗函数over())

常用开窗函数:
1.为每条数据体现聚合音信.(聚合函数() over())
2.为每条数据提供分组的聚合函数结实(聚合函数() over(partition by 字段) as 别名) --根据字段分组,分组后进行估测计算
3.与排行函数一同使用(row number() over(order by 字段) as 外号)

一、Oracle深入分析函数入门

 

分析函数是什么?
浅析函数是Oracle特意用来缓慢解决复杂报表计算需要的效能强大的函数,它可以在数码中张开分组然后总括基于组的某种总括值,并且每一组的每一行都能够回去一个总括值。

          

剖判函数和聚合函数的分化之处是哪些?
常见的聚合函数用group by分组,每一个分组再次来到一个总结值,而分析函数采纳partition by分组,何况每组每行都能够回到二个总结值。

              

剖析函数的款型
剖判函数带有一个开窗函数over(),富含多个剖判子句:分组(partition by), 排序(order by), 窗口(rows) ,他俩的行使情势如下:over(partition by xxx order by yyy rows between zzz)。
注:窗口子句在那边小编只说rows形式的窗口,range方式和滑动窗口也不提

    

解析函数例子(在scott顾客下模拟)

示范目标:显示各机关职工的报酬,并顺便展现该有的的参天薪水。

乐百家数据库 1

--显示各部门员工的工资,并附带显示该部分的最高工资。
SELECT E.DEPTNO,
       E.EMPNO,
       E.ENAME,
       E.SAL,
       LAST_VALUE(E.SAL) 
       OVER(PARTITION BY E.DEPTNO 
            ORDER BY E.SAL ROWS 
            --unbounded preceding and unbouned following针对当前所有记录的前一条、后一条记录,也就是表中的所有记录
            --unbounded:不受控制的,无限的
            --preceding:在...之前
            --following:在...之后
            BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) MAX_SAL
  FROM EMP E;

乐百家数据库 2

运维结果:

乐百家数据库 3

               

演示指标:根据deptno分组,然后总结每组值的总的数量

SELECT EMPNO,
       ENAME,
       DEPTNO,
       SAL,
       SUM(SAL) OVER(PARTITION BY DEPTNO ORDER BY ENAME) max_sal
  FROM SCOTT.EMP;

运作结果:

乐百家数据库 4

     

亲自去做目标:对各单位扩充裕组,并顺便展现第一行至当前行的聚焦

乐百家数据库 5

SELECT EMPNO,
       ENAME,
       DEPTNO,
       SAL,
       --注意ROWS BETWEEN unbounded preceding AND current row  是指第一行至当前行的汇总
       SUM(SAL) OVER(PARTITION BY DEPTNO 
                     ORDER BY ENAME 
                     ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) max_sal
  FROM SCOTT.EMP;

乐百家数据库 6

运维结果:

乐百家数据库 7

   

示范目的:当前行至最终一行的汇集

乐百家数据库 8

SELECT EMPNO,
       ENAME,
       DEPTNO,
       SAL,
       --注意ROWS BETWEEN current row AND unbounded following 指当前行到最后一行的汇总
       SUM(SAL) OVER(PARTITION BY DEPTNO 
                     ORDER BY ENAME 
                     ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) max_sal
  FROM SCOTT.EMP;

乐百家数据库 9

运维结果:

乐百家数据库 10

   

 示例指标:当前行的上一行(rownum-1)到眼下行的集中

乐百家数据库 11

SELECT EMPNO,
       ENAME,
       DEPTNO,
       SAL,
       --注意ROWS BETWEEN 1 preceding AND current row 是指当前行的上一行(rownum-1)到当前行的汇总 
       SUM(SAL) OVER(PARTITION BY DEPTNO 
                     ORDER BY ENAME ROWS 
                     BETWEEN 1 PRECEDING AND CURRENT ROW) max_sal
  FROM SCOTT.EMP;

乐百家数据库 12

运作结果:

乐百家数据库 13

    

躬行实践指标:   当前行的上一行(rownum-1)到当前行的下辆行(rownum 2)的聚焦     

乐百家数据库 14

SELECT EMPNO,
       ENAME,
       DEPTNO,
       SAL,
       --注意ROWS BETWEEN 1 preceding AND 1 following 是指当前行的上一行(rownum-1)到当前行的下辆行(rownum 2)的汇总
       SUM(SAL) OVER(PARTITION BY DEPTNO 
                     ORDER BY ENAME 
                     ROWS BETWEEN 1 PRECEDING AND 2 FOLLOWING) max_sal
  FROM SCOTT.EMP;

乐百家数据库 15

运作结果:

乐百家数据库 16

      

 

 

一、Oracle深入分析函数入门

 

剖判函数是何等?
深入分析函数是Oracle特地用来减轻复杂报表总括须求的功能庞大的函数,它可以在数码中张开分组然后总括基于组的某种总结值,而且每一组的每一行都得以重返二个计算值。

          

剖析函数和聚合函数的分化之处是什么?
经常的聚合函数用group by分组,各样分组重返贰个总计值,而剖判函数选拔partition by分组,而且每组每行都得以回来二个计算值。

              

剖析函数的款型
解析函数带有三个开窗函数over(),包涵四个深入分析子句:分组(partition by), 排序(order by), 窗口(rows) ,她俩的施用格局如下:over(partition by xxx order by yyy rows between zzz)。
注:窗口子句在那边笔者只说rows格局的窗口,range情势和滑动窗口也不提

    

浅析函数例子(在scott客商下模拟)

示范目标:突显各单位职工的工薪,并顺便展现该片段的最高级技术员资。

乐百家数据库 17

--显示各部门员工的工资,并附带显示该部分的最高工资。
SELECT E.DEPTNO,
       E.EMPNO,
       E.ENAME,
       E.SAL,
       LAST_VALUE(E.SAL) 
       OVER(PARTITION BY E.DEPTNO 
            ORDER BY E.SAL ROWS 
            --unbounded preceding and unbouned following针对当前所有记录的前一条、后一条记录,也就是表中的所有记录
            --unbounded:不受控制的,无限的
            --preceding:在...之前
            --following:在...之后
            BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) MAX_SAL
  FROM EMP E;

乐百家数据库 18

运营结果:

乐百家数据库 19

               

示范指标:依据deptno分组,然后计算每组值的总和

SELECT EMPNO,
       ENAME,
       DEPTNO,
       SAL,
       SUM(SAL) OVER(PARTITION BY DEPTNO ORDER BY ENAME) max_sal
  FROM SCOTT.EMP;

运维结果:

乐百家数据库 20

     

示范指标:对各机构开展分组,并顺便呈现第一行至当前行的汇聚

乐百家数据库 21

SELECT EMPNO,
       ENAME,
       DEPTNO,
       SAL,
       --注意ROWS BETWEEN unbounded preceding AND current row  是指第一行至当前行的汇总
       SUM(SAL) OVER(PARTITION BY DEPTNO 
                     ORDER BY ENAME 
                     ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) max_sal
  FROM SCOTT.EMP;

乐百家数据库 22

运维结果:

乐百家数据库 23

   

演示目的:当前行至最后一行的聚焦

乐百家数据库 24

SELECT EMPNO,
       ENAME,
       DEPTNO,
       SAL,
       --注意ROWS BETWEEN current row AND unbounded following 指当前行到最后一行的汇总
       SUM(SAL) OVER(PARTITION BY DEPTNO 
                     ORDER BY ENAME 
                     ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) max_sal
  FROM SCOTT.EMP;

乐百家数据库 25

运作结果:

乐百家数据库 26

   

 示例目标:当前行的上一行(rownum-1)到近来行的汇总

乐百家数据库 27

SELECT EMPNO,
       ENAME,
       DEPTNO,
       SAL,
       --注意ROWS BETWEEN 1 preceding AND current row 是指当前行的上一行(rownum-1)到当前行的汇总 
       SUM(SAL) OVER(PARTITION BY DEPTNO 
                     ORDER BY ENAME ROWS 
                     BETWEEN 1 PRECEDING AND CURRENT ROW) max_sal
  FROM SCOTT.EMP;

乐百家数据库 28

运转结果:

乐百家数据库 29

    

示范目的:   当前行的上一行(rownum-1)到眼下行的下辆行(rownum 2)的汇总     

乐百家数据库 30

SELECT EMPNO,
       ENAME,
       DEPTNO,
       SAL,
       --注意ROWS BETWEEN 1 preceding AND 1 following 是指当前行的上一行(rownum-1)到当前行的下辆行(rownum 2)的汇总
       SUM(SAL) OVER(PARTITION BY DEPTNO 
                     ORDER BY ENAME 
                     ROWS BETWEEN 1 PRECEDING AND 2 FOLLOWING) max_sal
  FROM SCOTT.EMP;

乐百家数据库 31

运营结果:

乐百家数据库 32

      

 

 

--每一种班级的战范例先的上学的儿童
--学生表中国国投息如下
a 1 80
b 1 78
c 1 95
d 2 74
e 2 92
f 3 99
g 3 99
h 3 45
i 3 55
j 3 78

三、常见分析函数详解

为了方便开展实行,特将演示表和多少罗列如下:

一、创建表

create table t( 
   bill_month varchar2(12) , 
   area_code number, 
   net_type varchar(2), 
   local_fare number 
);

      

二、插入数据

乐百家数据库 33

insert into t values('200405',5761,'G', 7393344.04); 
insert into t values('200405',5761,'J', 5667089.85); 
insert into t values('200405',5762,'G', 6315075.96); 
insert into t values('200405',5762,'J', 6328716.15); 
insert into t values('200405',5763,'G', 8861742.59); 
insert into t values('200405',5763,'J', 7788036.32); 
insert into t values('200405',5764,'G', 6028670.45); 
insert into t values('200405',5764,'J', 6459121.49); 
insert into t values('200405',5765,'G', 13156065.77); 
insert into t values('200405',5765,'J', 11901671.70); 
insert into t values('200406',5761,'G', 7614587.96); 
insert into t values('200406',5761,'J', 5704343.05); 
insert into t values('200406',5762,'G', 6556992.60); 
insert into t values('200406',5762,'J', 6238068.05); 
insert into t values('200406',5763,'G', 9130055.46); 
insert into t values('200406',5763,'J', 7990460.25); 
insert into t values('200406',5764,'G', 6387706.01); 
insert into t values('200406',5764,'J', 6907481.66); 
insert into t values('200406',5765,'G', 13562968.81); 
insert into t values('200406',5765,'J', 12495492.50); 
insert into t values('200407',5761,'G', 7987050.65); 
insert into t values('200407',5761,'J', 5723215.28); 
insert into t values('200407',5762,'G', 6833096.68); 
insert into t values('200407',5762,'J', 6391201.44); 
insert into t values('200407',5763,'G', 9410815.91); 
insert into t values('200407',5763,'J', 8076677.41); 
insert into t values('200407',5764,'G', 6456433.23); 
insert into t values('200407',5764,'J', 6987660.53); 
insert into t values('200407',5765,'G', 14000101.20); 
insert into t values('200407',5765,'J', 12301780.20); 
insert into t values('200408',5761,'G', 8085170.84); 
insert into t values('200408',5761,'J', 6050611.37); 
insert into t values('200408',5762,'G', 6854584.22); 
insert into t values('200408',5762,'J', 6521884.50); 
insert into t values('200408',5763,'G', 9468707.65); 
insert into t values('200408',5763,'J', 8460049.43); 
insert into t values('200408',5764,'G', 6587559.23); 
insert into t values('200408',5764,'J', 7342135.86); 
insert into t values('200408',5765,'G', 14450586.63); 
insert into t values('200408',5765,'J', 12680052.38); 
commit;

乐百家数据库 34

            

三、first_value()与last_value():求最值对应的其余质量
主题素材、收取每月话费最高和最低的八个地面。

乐百家数据库 35

SELECT BILL_MONTH, 
       AREA_CODE,
       SUM(LOCAL_FARE) LOCAL_FARE, 
       FIRST_VALUE(AREA_CODE) 
       OVER(PARTITION BY BILL_MONTH 
            ORDER BY SUM(LOCAL_FARE) DESC 
            ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) FIRSTVAL, 
       LAST_VALUE(AREA_CODE) 
       OVER(PARTITION BY BILL_MONTH 
            ORDER BY SUM(LOCAL_FARE) DESC 
            ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) LASTVAL 
  FROM T 
 GROUP BY BILL_MONTH, AREA_CODE 
 ORDER BY BILL_MONTH

乐百家数据库 36

运作结果:

乐百家数据库 37

   

四、rank(),dense_rank()与row_number():求排序

rank,dense_rank,row_number函数为每条记下发生多个从1始发至n的自然数,n的值恐怕低于等于记录的总和。那3个函数的天下第一分化在于当碰着一样数量时的排名攻略。
①row_number: 
row_number函数再次来到三个独一的值,当碰着同样数量时,排名依据记录聚焦记录的相继依次递增。
②dense_rank: 
dense_rank函数重临四个独一的值,当际遇同样数量时,此时全体同一数量的排名都以一致的。
③rank: 
rank函数重回贰个独一的值,当境遇同样的数据时,此时有所同一数量的排行是均等的,同不常候会在结尾一条相同记录和下一条不一样记录的排行之间空出排行。

          

身体力行数据在Oracle自带的scott客户下:
1、rank()值一样一时间排名同样,其后排行跳跃不延续

乐百家数据库 38

SELECT * 
  FROM (SELECT DEPTNO, 
               RANK() OVER(PARTITION BY DEPTNO ORDER BY SAL DESC) RW, 
               ENAME,
               SAL
          FROM SCOTT.EMP) 
 WHERE RW <= 4;

乐百家数据库 39

运维结果:

乐百家数据库 40
2、dense_rank()值同样期名次一样,其后排行一而再不跳跃

乐百家数据库 41

SELECT * 
  FROM (SELECT DEPTNO, 
               DENSE_RANK() OVER(PARTITION BY DEPTNO ORDER BY SAL DESC) RW, 
               ENAME,
               SAL
          FROM SCOTT.EMP) 
 WHERE RW <= 4;

乐百家数据库 42

运作结果:

乐百家数据库 43
3、row_number()值一样期排行不等于,其后排名接二连三不跳跃

乐百家数据库 44

SELECT * 
  FROM (SELECT DEPTNO, 
               ROW_NUMBER() OVER(PARTITION BY DEPTNO ORDER BY SAL DESC) RW, 
               ENAME,
               SAL
          FROM SCOTT.EMP) 
 WHERE RW <= 4;

乐百家数据库 45

运转结果:

乐百家数据库 46

 

五、lag()与lead():求在此以前或今后的第N行 
lag和lead函数能够在二回查询中抽出同一字段的前n行的数量和后n行的值。这种操作能够运用对同样表的表连接来完结,然则使用lag和lead有更加高的功用。
lag(arg1,arg2,arg3)
先是个参数是列名,
其次个参数是偏移的offset,
其多少个参数是过量记录窗口时的默许值。
   
举个例子如下:
SQL> select *  from kkk;                                          
                                                                  
        ID NAME                                                   
---------- --------------------                                   
         1 1name                                                  
         2 2name                                                  
         3 3name                                                  
         4 4name                                                  
         5 5name                                                  
SQL> select id,name,lag(name,1,0) over(order by id) from kkk; 
                                                                  
        ID NAME                 LAG(NAME,1,0)OVER(ORDERBYID)      
---------- -------------------- ----------------------------      
         1 1name                0                                 
         2 2name                1name                             
         3 3name                2name                             
         4 4name                3name                             
         5 5name                4name

SQL> select id,name,lead(name,1,0) over(order by id) from kkk;
                                                                  
        ID NAME                 LEAD(NAME,1,0)OVER(ORDERBYID)     
---------- -------------------- -----------------------------     
         1 1name                2name                             
         2 2name                3name                             
         3 3name                4name                             
         4 4name                5name                             
         5 5name                0

SQL> select id,name,lead(name,2,0) over(order by id) from kkk;                                                                                                              
        ID NAME                 LEAD(NAME,2,0)OVER(ORDERBYID)     
---------- -------------------- -----------------------------     
         1 1name                3name                             
         2 2name                4name                             
         3 3name                5name                             
         4 4name                0                                 
         5 5name                0  
SQL> select id,name,lead(name,1,'linjiqin') over(order by id) from kkk;                                 
                                                                                 
        ID NAME                 LEAD(NAME,1,'ALSDFJLASDJFSAF')                   


------------------------------                   
         1 1name                2name                                            
         2 2name                3name                                            
         3 3name                4name                                            
         4 4name                5name                                            
         5 5name                linjiqin  


   

六、rollup()与cube():排列组合分组 
1)、group by rollup(a, b, c):
先是会对(a、b、c)进行group by,
下一场再对(a、b)进行group by,
未来再对(a)实行group by,
最终对全表实行集中操作。

     

2)、group by cube(a, b, c):
则第一会对(a、b、c)进行group by,
然后每家每户是(a、b),(a、c),(a),(b、c),(b),(c),
提及底对全表进行集中操作。

   

1、生成演示数据:
Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 
Connected as ds_trade
 
SQL> conn system/oracle as sysdba
Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 
Connected as SYS
 
SQL> create table scott.t as select * from dba_indexes;
 
Table created
 
 
SQL> connect scott/oracle
Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 
Connected as scott
 
SQL>

    

2、普通group by体验
sql> select owner, index_type, status, count(*) from t where owner like 'SY%' group by owner, index_type, status;

乐百家数据库 47

3、group by rollup(A,B,C)
GROUP BY ROLLUP(A, B, C):
率先会对(A、B、C)进行GROUP BY,
接下来再对(A、B)进行GROUP BY,
从此以往再对(A)举行GROUP BY,
最后对全表举办聚焦操作。
sql> select owner, index_type, status, count(*) from t where owner like 'SY%' group by ROLLUP(owner, index_type, status);

乐百家数据库 48

4、group by cube(A,B,C)
GROUP BY CUBE(A, B, C):
则第一会对(A、B、C)举行GROUP BY,
下一场依次是(A、B),(A、C),(A),(B、C),(B),(C),
末段对全表举行聚集操作。

sql> select owner, index_乐百家数据库,type, status, count(*) from t where owner like 'SY%' group by cube(owner, index_type, status);

乐百家数据库 49

  

七、max(),min(),sun()与avg():求移动的最值总和与平均值
难题:总计出各个地方连日来6个月的打电话费用的平均数(移动平均值)

 

乐百家数据库 50

SELECT AREA_CODE, 
       BILL_MONTH,
       LOCAL_FARE,
       SUM(LOCAL_FARE) OVER(PARTITION BY AREA_CODE 
                            ORDER BY TO_NUMBER(BILL_MONTH) 
                            RANGE BETWEEN 1 PRECEDING AND 1 FOLLOWING) "3month_sum", 
       AVG(LOCAL_FARE) OVER(PARTITION BY AREA_CODE 
                            ORDER BY TO_NUMBER(BILL_MONTH) 
                            RANGE BETWEEN 1 PRECEDING AND 1 FOLLOWING) "3month_avg", 
       MAX(LOCAL_FARE) OVER(PARTITION BY AREA_CODE 
                            ORDER BY TO_NUMBER(BILL_MONTH) 
                            RANGE BETWEEN 1 PRECEDING AND 1 FOLLOWING) "3month_max", 
       MIN(LOCAL_FARE) OVER(PARTITION BY AREA_CODE 
                            ORDER BY TO_NUMBER(BILL_MONTH) 
                            RANGE BETWEEN 1 PRECEDING AND 1 FOLLOWING) "3month_min" 
  FROM (SELECT T.AREA_CODE, T.BILL_MONTH, SUM(T.LOCAL_FARE) LOCAL_FARE 
          FROM T 
         GROUP BY T.AREA_CODE, T.BILL_MONTH)

乐百家数据库 51

运作结果:

乐百家数据库 52

  

标题:求各市方按月度增进的话费

乐百家数据库 53

SELECT AREA_CODE, 
       BILL_MONTH,
       LOCAL_FARE,
       SUM(LOCAL_FARE) OVER(PARTITION BY AREA_CODE 
                            ORDER BY BILL_MONTH ASC) "last_sum_value" 
  FROM (SELECT T.AREA_CODE, T.BILL_MONTH, SUM(T.LOCAL_FARE) LOCAL_FARE 
          FROM T 
         GROUP BY T.AREA_CODE, T.BILL_MONTH) 
 ORDER BY AREA_CODE, BILL_MONTH

乐百家数据库 54

运转结果:

乐百家数据库 55

 


Blog:
J2EE、Android、Linux、Oracle QQ交流群:142463980、158560018(满)

另见:《Oracle深入分析函数ROW_NUMBE普拉多()|RANK()|LAG()使用详解》

Oracle深入分析函数ROW_NUMBEOdyssey()|RANK()|LAG()使用详解

 

 

1.3、支持领悟over()的实例

例1:关心点:sql无排序,over()排序子句轻巧

SELECT DEPTNO, EMPNO, ENAME, SAL, 
       LAST_VALUE(SAL) OVER(PARTITION BY DEPTNO)
FROM EMP;

运维结果:

 

乐百家数据库 56

        

例2:关怀点:sql无排序,over()排序子句有,窗口省略

 

乐百家数据库 57

SELECT DEPTNO,
       EMPNO,
       ENAME,
       SAL,
       LAST_VALUE(SAL) OVER(PARTITION BY DEPTNO 
                            ORDER BY SAL DESC)
  FROM EMP;

乐百家数据库 58

运作结果:

 

乐百家数据库 59

                   
例3:关切点:sql无排序,over()排序子句有,窗口也可以有,窗口特意重申全组数据

 

乐百家数据库 60

SELECT DEPTNO,
       EMPNO,
       ENAME,
       SAL,
       LAST_VALUE(SAL) 
       OVER(PARTITION BY DEPTNO 
            ORDER BY SAL 
            ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) MAX_SAL
  FROM EMP;

乐百家数据库 61

运作结果:

 

乐百家数据库 62

      
例4:关切点:sql有排序(正序),over()排序子句无,先做sql排序再打开分析函数运算

 

乐百家数据库 63

SELECT DEPTNO,
       MGR,
       ENAME,
       SAL,
       HIREDATE,
       LAST_VALUE(SAL) OVER(PARTITION BY DEPTNO) LAST_VALUE
  FROM EMP
 WHERE DEPTNO = 30
 ORDER BY DEPTNO, MGR;

乐百家数据库 64

运作结果:

 

乐百家数据库 65

 

例5:关心点:sql有排序(倒序),over()排序子句无,先做sql排序再实行深入分析函数运算

 

乐百家数据库 66

SELECT DEPTNO,
       MGR,
       ENAME,
       SAL,
       HIREDATE,
       LAST_VALUE(SAL) OVER(PARTITION BY DEPTNO) LAST_VALUE
  FROM EMP
 WHERE DEPTNO = 30
 ORDER BY DEPTNO, MGR DESC;

乐百家数据库 67

运作结果:

乐百家数据库 68

                 

例6:关切点:sql有排序(倒序),over()排序子句有,窗口子句无,此时的运算是:sql先选数据只是不排序,而后排序子句先排序并开展剖判函数管理(窗口默以为首先行到当前行),最终再扩充sql排序

 

乐百家lo599, 

乐百家数据库 69

SELECT DEPTNO,
       MGR,
       ENAME,
       SAL,
       HIREDATE,
       MIN(SAL) OVER(PARTITION BY DEPTNO ORDER BY SAL ASC) LAST_VALUE
  FROM EMP
 WHERE DEPTNO = 30
 ORDER BY DEPTNO, MGR DESC;

乐百家数据库 70

运维结果:

乐百家数据库 71

 

乐百家数据库 72

SELECT DEPTNO,
       MGR,
       ENAME,
       SAL,
       HIREDATE,
       MIN(SAL) OVER(PARTITION BY DEPTNO ORDER BY SAL DESC) LAST_VALUE
  FROM EMP
 WHERE DEPTNO = 30
 ORDER BY DEPTNO, MGR DESC;

乐百家数据库 73

运营结果:

乐百家数据库 74

              

 

防止这种场所,可以行使开窗函数。

Oracle的LAG和LEAD解析函数

 

 

 

二、理解over()函数

1.1、两个order by的试行机会
浅析函数(以及与其协作的开窗函数over())是在全部sql查询甘休后(sql语句中的order by的实行相比非凡)再进行的操作, 也正是说sql语句中的order by也会潜濡默化解析函数的实行结果:

a) 两个一致:借使sql语句中的order by满意与深入分析函数合营的开窗函数over()解析时讲求的排序,即sql语句中的order by子句里的内容和开窗函数over()中的order by子句里的剧情一样,

那么sql语句中的排序将先举行,剖判函数在分析时就无须再排序;
b) 两个不雷同:若是sql语句中的order by不知足与剖析函数同盟的开窗函数over()解析时讲求的排序,即sql语句中的order by子句里的源委和开窗函数over()中的order by子句里的内容不等同,

那便是说sql语句中的排序将最终在条分缕析函数分析结束后实施排序。

           

1.2、开窗函数over()分析函数中的分组/排序/窗口
      开窗函数over()分析函数带有四个深入分析子句:分组子句(partition by), 排序子句(order by), 窗口子句(rows)
      窗口正是解析函数剖判时要管理的数码范围,就拿sum来讲,它是sum窗口中的记录并非全部分组中的记录,因而大家在想得到有个别栏位的累计值时,大家必要把窗口钦定到该分组中的第一行数据到当前行, 假若你钦赐该窗口从该分组中的第一行到终极一行,那么该组中的每二个sum值都会一样,即一切组的总额。

      窗口子句在这里作者只说rows格局的窗口,range形式和滑动窗口也不提。

 

      窗口子句中大家平常应用钦赐第一行,当前行,最终一行如此的四个属性:
率先行是 unbounded preceding,
当下行是 current row,
最后一行是 unbounded following,

注释:

开窗函数over()并发分组(partition by)子句时,

unbounded preceding即首先行是指表中三个分组里的第一行, unbounded following即最终一行是指表中三个分组里的结尾一行;

开窗函数over()轻松了分组(partition by)子句时, 

unbounded preceding即首先行是指表中的第一行, unbounded following即最终一行是指表中的尾声一行。

 

窗口子句不能单独出现,必须有order by子句时才能出现

例如:

last_value(sal) over(partition by deptno 
                     order by sal 
                     rows between unbounded preceding and unbounded following)

如上示例钦定窗口为全方位分组。而出现order by子句的时候,不确定要有窗口子句,但成效会很不雷同,此时的窗口默许是现阶段组的率先行到当前行!

 

比如轻松分组,则把方方面面记下当成二个组。
a) 如果存在order by则默认窗口是unbounded preceding and current row   --当前组的第一行到当前行 b) 如果这时省略order by则窗口默认为unbounded preceding and unbounded following  --整个组 

 

而不论是是还是不是省略分组子句,如下结论都以白手起家的:

1、窗口子句无法独立出现,必得有order by子句时技能冒出

2、当省略窗口子句时:
a) 倘使存在order by则暗中同意的窗口是unbounded preceding and current row  --当前组的首先行到当下行,即在脚下组中,第一行到近些日子行
b) 假诺还要省略order by则暗中同意的窗口是unbounded preceding and unbounded following  --整个组

              
所以,

 

lag(sal) over(order by sal) 解释

over(order by salary)表暗示义如下:

首先,我们要清楚是因为省略分组子句,所以当前组的限制为任何表的数据行,

然后,在眼下组(此时为全部表的数据行)这些限制里举行排序(即order by salary),

最终,大家领略解析函数lag(sal)在如今组(此时为全体表的数据行)这么些范围里的窗口范围为眼下组的第一行到当前行,即深入分析函数lag(sal)在那个窗口范围实施。

 

参见:

 

剖判:每一个人学号一定是见仁见智的,名字或然有重名,最大复杂的图景是,各种班最高战表可能不断三个。

Oracle解析函数ROW_NUMBE奥迪Q7()|RANK()|LAG()使用详解

 

1.3、帮忙理解over()的实例

例1:关切点:sql无排序,over()排序子句简单

SELECT DEPTNO, EMPNO, ENAME, SAL, 
       LAST_VALUE(SAL) OVER(PARTITION BY DEPTNO)
FROM EMP;

运作结果:

 

乐百家数据库 75

        

例2:关怀点:sql无排序,over()排序子句有,窗口省略

 

乐百家数据库 76

SELECT DEPTNO,
       EMPNO,
       ENAME,
       SAL,
       LAST_VALUE(SAL) OVER(PARTITION BY DEPTNO 
                            ORDER BY SAL DESC)
  FROM EMP;

乐百家数据库 77

运行结果:

 

乐百家数据库 78

                   
例3:关注点:sql无排序,over()排序子句有,窗口也可能有,窗口特意重申全组数据

 

乐百家数据库 79

SELECT DEPTNO,
       EMPNO,
       ENAME,
       SAL,
       LAST_VALUE(SAL) 
       OVER(PARTITION BY DEPTNO 
            ORDER BY SAL 
            ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) MAX_SAL
  FROM EMP;

乐百家数据库 80

运转结果:

 

乐百家数据库 81

      
例4:关切点:sql有排序(正序),over()排序子句无,先做sql排序再展开剖判函数运算

 

乐百家数据库 82

SELECT DEPTNO,
       MGR,
       ENAME,
       SAL,
       HIREDATE,
       LAST_VALUE(SAL) OVER(PARTITION BY DEPTNO) LAST_VALUE
  FROM EMP
 WHERE DEPTNO = 30
 ORDER BY DEPTNO, MGR;

乐百家数据库 83

运营结果:

 

乐百家数据库 84

 

例5:关切点:sql有排序(倒序),over()排序子句无,先做sql排序再实行剖释函数运算

 

乐百家数据库 85

SELECT DEPTNO,
       MGR,
       ENAME,
       SAL,
       HIREDATE,
       LAST_VALUE(SAL) OVER(PARTITION BY DEPTNO) LAST_VALUE
  FROM EMP
 WHERE DEPTNO = 30
 ORDER BY DEPTNO, MGR DESC;

乐百家数据库 86

运转结果:

乐百家数据库 87

                 

例6:关切点:sql有排序(倒序),over()排序子句有,窗口子句无,此时的运算是:sql先选数据只是不排序,而后排序子句先排序并开展分析函数处理(窗口默以为首先行到当下行),最后再扩充sql排序

 

 

乐百家数据库 88

SELECT DEPTNO,
       MGR,
       ENAME,
       SAL,
       HIREDATE,
       MIN(SAL) OVER(PARTITION BY DEPTNO ORDER BY SAL ASC) LAST_VALUE
  FROM EMP
 WHERE DEPTNO = 30
 ORDER BY DEPTNO, MGR DESC;

乐百家数据库 89

运维结果:

乐百家数据库 90

 

乐百家数据库 91

SELECT DEPTNO,
       MGR,
       ENAME,
       SAL,
       HIREDATE,
       MIN(SAL) OVER(PARTITION BY DEPTNO ORDER BY SAL DESC) LAST_VALUE
  FROM EMP
 WHERE DEPTNO = 30
 ORDER BY DEPTNO, MGR DESC;

乐百家数据库 92

运作结果:

乐百家数据库 93

              

 

Oracle的LAG和LEAD剖判函数

 

 

 

询问结果如下:
c 1 95 1
e 2 92 1
f 3 99 1
g 3 99 1

二、理解over()函数

1.1、七个order by的试行机缘
分析函数(以及与其同盟的开窗函数over())是在方方面面sql查询结束后(sql语句中的order by的实行相比较特殊)再拓宽的操作, 也正是说sql语句中的order by也会影响分析函数的施行结果:

a) 两个一致:假诺sql语句中的order by满足与深入分析函数合营的开窗函数over()分析时讲求的排序,即sql语句中的order by子句里的剧情和开窗函数over()中的order by子句里的从头到尾的经过一样,

那正是说sql语句中的排序将先进行,剖析函数在深入分析时就无须再排序;
b) 两个不雷同:倘诺sql语句中的order by不满意与分析函数协作的开窗函数over()深入分析时须要的排序,即sql语句中的order by子句里的情节和开窗函数over()中的order by子句里的剧情不等同,

那就是说sql语句中的排序将最后在剖判函数深入分析截止后举办排序。

           

1.2、开窗函数over()分析函数中的分组/排序/窗口
      开窗函数over()分析函数包蕴三个深入分析子句:分组子句(partition by), 排序子句(order by), 窗口子句(rows)
      窗口正是深入分析函数分析时要拍卖的数据范围,就拿sum来讲,它是sum窗口中的记录实际不是整整分组中的记录,由此大家在想获得某些栏位的累计值时,大家须要把窗口内定到该分组中的第一行数据到最近行, 如若你钦定该窗口从该分组中的第一行到终极一行,那么该组中的每二个sum值都会一直以来,即一切组的总的数量。

      窗口子句在此处自个儿只说rows情势的窗口,range格局和滑动窗口也不提。

 

      窗口子句中大家经常应用钦赐第一行,当前行,最终一行如此的五脾个性:
首先行是 unbounded preceding,
脚下行是 current row,
提及底一行是 unbounded following,

注释:

开窗函数over()出现分组(partition by)子句时,

unbounded preceding即首先行是指表中二个分组里的首先行, unbounded following即末了一行是指表中二个分组里的末梢一行;

开窗函数over()简言之了分组(partition by)子句时, 

unbounded preceding即首先行是指表中的首先行, unbounded following即最后一行是指表中的结尾一行。

 

窗口子句不能单独出现,必须有order by子句时才能出现

例如:

last_value(sal) over(partition by deptno 
                     order by sal 
                     rows between unbounded preceding and unbounded following)

上述示例钦定窗口为任何分组。而产出order by子句的时候,不必然要有窗口子句,但功效会很差异,此时的窗口暗中同意是当前组的第一行到日前行!

 

借使轻松分组,则把全体记录当成三个组。
a) 如果存在order by则默认窗口是unbounded preceding and current row   --当前组的第一行到当前行 b) 如果这时省略order by则窗口默认为unbounded preceding and unbounded following  --整个组 

 

而不论是不是省略分组子句,如下结论都以创造的:

1、窗口子句不能够独立出现,必得有order by子句时技巧出现

2、当省略窗口子句时:
a) 要是存在order by则暗中同意的窗口是unbounded preceding and current row  --当前组的第一行到当下行,即在当下组中,第一行到如今行
b) 假诺还要省略order by则私下认可的窗口是unbounded preceding and unbounded following  --整个组

              
所以,

 

lag(sal) over(order by sal) 解释

over(order by salary)表示意义如下:

首先,大家要清楚是因为省略分组子句,所以当前组的限制为任何表的数额行,

接下来,在眼下组(此时为一切表的数据行)这几个界定里施行排序(即order by salary),

终极,大家理解解析函数lag(sal)在现阶段组(此时为整体表的数据行)这些界定里的窗口范围为眼下组的首先行到近期行,即深入分析函数lag(sal)在那些窗口范围执行。

 

参见:

 

三、常见解析函数详解

为了有助于开展实行,特将演示表和数码罗列如下:

一、创建表

create table t( 
   bill_month varchar2(12) , 
   area_code number, 
   net_type varchar(2), 
   local_fare number 
);

      

二、插入数据

乐百家数据库 94

insert into t values('200405',5761,'G', 7393344.04); 
insert into t values('200405',5761,'J', 5667089.85); 
insert into t values('200405',5762,'G', 6315075.96); 
insert into t values('200405',5762,'J', 6328716.15); 
insert into t values('200405',5763,'G', 8861742.59); 
insert into t values('200405',5763,'J', 7788036.32); 
insert into t values('200405',5764,'G', 6028670.45); 
insert into t values('200405',5764,'J', 6459121.49); 
insert into t values('200405',5765,'G', 13156065.77); 
insert into t values('200405',5765,'J', 11901671.70); 
insert into t values('200406',5761,'G', 7614587.96); 
insert into t values('200406',5761,'J', 5704343.05); 
insert into t values('200406',5762,'G', 6556992.60); 
insert into t values('200406',5762,'J', 6238068.05); 
insert into t values('200406',5763,'G', 9130055.46); 
insert into t values('200406',5763,'J', 7990460.25); 
insert into t values('200406',5764,'G', 6387706.01); 
insert into t values('200406',5764,'J', 6907481.66); 
insert into t values('200406',5765,'G', 13562968.81); 
insert into t values('200406',5765,'J', 12495492.50); 
insert into t values('200407',5761,'G', 7987050.65); 
insert into t values('200407',5761,'J', 5723215.28); 
insert into t values('200407',5762,'G', 6833096.68); 
insert into t values('200407',5762,'J', 6391201.44); 
insert into t values('200407',5763,'G', 9410815.91); 
insert into t values('200407',5763,'J', 8076677.41); 
insert into t values('200407',5764,'G', 6456433.23); 
insert into t values('200407',5764,'J', 6987660.53); 
insert into t values('200407',5765,'G', 14000101.20); 
insert into t values('200407',5765,'J', 12301780.20); 
insert into t values('200408',5761,'G', 8085170.84); 
insert into t values('200408',5761,'J', 6050611.37); 
insert into t values('200408',5762,'G', 6854584.22); 
insert into t values('200408',5762,'J', 6521884.50); 
insert into t values('200408',5763,'G', 9468707.65); 
insert into t values('200408',5763,'J', 8460049.43); 
insert into t values('200408',5764,'G', 6587559.23); 
insert into t values('200408',5764,'J', 7342135.86); 
insert into t values('200408',5765,'G', 14450586.63); 
insert into t values('200408',5765,'J', 12680052.38); 
commit;

乐百家数据库 95

            

三、first_value()与last_value():求最值对应的其他属性
标题、收取每月话费最高和最低的三个地段。

乐百家数据库 96

SELECT BILL_MONTH, 
       AREA_CODE,
       SUM(LOCAL_FARE) LOCAL_FARE, 
       FIRST_VALUE(AREA_CODE) 
       OVER(PARTITION BY BILL_MONTH 
            ORDER BY SUM(LOCAL_FARE) DESC 
            ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) FIRSTVAL, 
       LAST_VALUE(AREA_CODE) 
       OVER(PARTITION BY BILL_MONTH 
            ORDER BY SUM(LOCAL_FARE) DESC 
            ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) LASTVAL 
  FROM T 
 GROUP BY BILL_MONTH, AREA_CODE 
 ORDER BY BILL_MONTH

乐百家数据库 97

运维结果:

乐百家数据库 98

   

四、rank(),dense_rank()与row_number():求排序

rank,dense_rank,row_number函数为每条记下发生三个从1开头至n的自然数,n的值只怕低于等于记录的总量。那3个函数的不二法门不一样在于当碰到一样数量时的排行计谋。
①row_number: 
row_number函数再次来到二个独一的值,当碰到同样数量时,排行遵照记录集中记录的一一依次递增。
②dense_rank: 
dense_rank函数重回一个独一的值,当遭遇一样数量时,此时享有同一数量的排名都以平等的。
③rank: 
rank函数再次回到一个独一的值,当境遇一样的多少时,此时颇具同一数量的排行是一样的,同期会在最终一条同样记录和下一条不一致记录的排行之间空出排行。

          

演示数据在Oracle自带的scott客户下:
1、rank()值相同期排行一样,其后排行跳跃不总是

乐百家数据库 99

SELECT * 
  FROM (SELECT DEPTNO, 
               RANK() OVER(PARTITION BY DEPTNO ORDER BY SAL DESC) RW, 
               ENAME,
               SAL
          FROM SCOTT.EMP) 
 WHERE RW <= 4;

乐百家数据库 100

运作结果:

乐百家数据库 101
2、dense_rank()值一样不常间排行同样,其后排行一而再不跳跃

乐百家数据库 102

SELECT * 
  FROM (SELECT DEPTNO, 
               DENSE_RANK() OVER(PARTITION BY DEPTNO ORDER BY SAL DESC) RW, 
               ENAME,
               SAL
          FROM SCOTT.EMP) 
 WHERE RW <= 4;

乐百家数据库 103

运营结果:

乐百家数据库 104
3、row_number()值同样一时间排行不对等,其后排行一连不跳跃

乐百家数据库 105

SELECT * 
  FROM (SELECT DEPTNO, 
               ROW_NUMBER() OVER(PARTITION BY DEPTNO ORDER BY SAL DESC) RW, 
               ENAME,
               SAL
          FROM SCOTT.EMP) 
 WHERE RW <= 4;

乐百家数据库 106

运行结果:

乐百家数据库 107

 

五、lag()与lead():求在此之前或现在的第N行 
lag和lead函数能够在二回查询中收取同一字段的前n行的多少和后n行的值。这种操作可以行使对同样表的表连接来完毕,不过使用lag和lead有更加高的功用。
lag(arg1,arg2,arg3)
率先个参数是列名,
其次个参数是偏移的offset,
其三个参数是高于记录窗口时的私下认可值。
   
比如如下:
SQL> select *  from kkk;                                          
                                                                  
        ID NAME                                                   
---------- --------------------                                   
         1 1name                                                  
         2 2name                                                  
         3 3name                                                  
         4 4name                                                  
         5 5name                                                  
SQL> select id,name,lag(name,1,0) over(order by id) from kkk; 
                                                                  
        ID NAME                 LAG(NAME,1,0)OVER(ORDERBYID)      
---------- -------------------- ----------------------------      
         1 1name                0                                 
         2 2name                1name                             
         3 3name                2name                             
         4 4name                3name                             
         5 5name                4name

SQL> select id,name,lead(name,1,0) over(order by id) from kkk;
                                                                  
        ID NAME                 LEAD(NAME,1,0)OVER(ORDERBYID)     
---------- -------------------- -----------------------------     
         1 1name                2name                             
         2 2name                3name                             
         3 3name                4name                             
         4 4name                5name                             
         5 5name                0

SQL> select id,name,lead(name,2,0) over(order by id) from kkk;                                                                                                              
        ID NAME                 LEAD(NAME,2,0)OVER(ORDERBYID)     
---------- -------------------- -----------------------------     
         1 1name                3name                             
         2 2name                4name                             
         3 3name                5name                             
         4 4name                0                                 
         5 5name                0  
SQL> select id,name,lead(name,1,'linjiqin') over(order by id) from kkk;                                 
                                                                                 
        ID NAME                 LEAD(NAME,1,'ALSDFJLASDJFSAF')                   


------------------------------                   
         1 1name                2name                                            
         2 2name                3name                                            
         3 3name                4name                                            
         4 4name                5name                                            
         5 5name                linjiqin  


   

六、rollup()与cube():排列组合分组 
1)、group by rollup(a, b, c):
率先会对(a、b、c)举行group by,
下一场再对(a、b)进行group by,
今后再对(a)实行group by,
末尾对全表进行聚集操作。

     

2)、group by cube(a, b, c):
则第一会对(a、b、c)实行group by,
接下来逐条是(a、b),(a、c),(a),(b、c),(b),(c),
最后对全表举行聚焦操作。

   

1、生成演示数据:
Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 
Connected as ds_trade
 
SQL> conn system/oracle as sysdba
Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 
Connected as SYS
 
SQL> create table scott.t as select * from dba_indexes;
 
Table created
 
 
SQL> connect scott/oracle
Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 
Connected as scott
 
SQL>

    

2、普通group by体验
sql> select owner, index_type, status, count(*) from t where owner like 'SY%' group by owner, index_type, status;

乐百家数据库 108

3、group by rollup(A,B,C)
GROUP BY ROLLUP(A, B, C):
第一会对(A、B、C)实行GROUP BY,
下一场再对(A、B)进行GROUP BY,
然后再对(A)举行GROUP BY,
最后对全表进行集中操作。
sql> select owner, index_type, status, count(*) from t where owner like 'SY%' group by ROLLUP(owner, index_type, status);

乐百家数据库 109

4、group by cube(A,B,C)
GROUP BY CUBE(A, B, C):
则率先会对(A、B、C)实行GROUP BY,
然后所有人家是(A、B),(A、C),(A),(B、C),(B),(C),
终极对全表举办聚集操作。

sql> select owner, index_type, status, count(*) from t where owner like 'SY%' group by cube(owner, index_type, status);

乐百家数据库 110

  

七、max(),min(),sun()与avg():求移动的最值总和与平均值
标题:总计出各个地方接二连三半年的打电话花费的平平均数量(移动平均值)

 

乐百家数据库 111

SELECT AREA_CODE, 
       BILL_MONTH,
       LOCAL_FARE,
       SUM(LOCAL_FARE) OVER(PARTITION BY AREA_CODE 
                            ORDER BY TO_NUMBER(BILL_MONTH) 
                            RANGE BETWEEN 1 PRECEDING AND 1 FOLLOWING) "3month_sum", 
       AVG(LOCAL_FARE) OVER(PARTITION BY AREA_CODE 
                            ORDER BY TO_NUMBER(BILL_MONTH) 
                            RANGE BETWEEN 1 PRECEDING AND 1 FOLLOWING) "3month_avg", 
       MAX(LOCAL_FARE) OVER(PARTITION BY AREA_CODE 
                            ORDER BY TO_NUMBER(BILL_MONTH) 
                            RANGE BETWEEN 1 PRECEDING AND 1 FOLLOWING) "3month_max", 
       MIN(LOCAL_FARE) OVER(PARTITION BY AREA_CODE 
                            ORDER BY TO_NUMBER(BILL_MONTH) 
                            RANGE BETWEEN 1 PRECEDING AND 1 FOLLOWING) "3month_min" 
  FROM (SELECT T.AREA_CODE, T.BILL_MONTH, SUM(T.LOCAL_FARE) LOCAL_FARE 
          FROM T 
         GROUP BY T.AREA_CODE, T.BILL_MONTH)

乐百家数据库 112

运营结果:

乐百家数据库 113

  

主题材料:求外市点按月度增进的电话费

乐百家数据库 114

SELECT AREA_CODE, 
       BILL_MONTH,
       LOCAL_FARE,
       SUM(LOCAL_FARE) OVER(PARTITION BY AREA_CODE 
                            ORDER BY BILL_MONTH ASC) "last_sum_value" 
  FROM (SELECT T.AREA_CODE, T.BILL_MONTH, SUM(T.LOCAL_FARE) LOCAL_FARE 
          FROM T 
         GROUP BY T.AREA_CODE, T.BILL_MONTH) 
 ORDER BY AREA_CODE, BILL_MONTH

乐百家数据库 115

运行结果:

乐百家数据库 116

 

          两条音讯都会输出。

over()开窗函数: 在使用聚合函数后,会将多行形成一行,
而开窗函数是将一行成为多行;
再者在动用聚合函数后,假如要显得其余的列必得将列插足到group by中,
而选用开窗函数后,能够不使用group by,间接将享有音讯体现出来。

        使用开窗函数就能够很好的缓和这么些难点。

本文由乐百家数据库发布,转载请注明来源:oracle分析函数技艺详解(配上开窗函数over())