开窗函数 –over()

          查询出来的结果,统计出每个人的最高成绩,          查询出来的结果,统计出每个人的最高成绩,每个分组返回一个统计值,分析函数和聚合函数的不同之处是什么,每个分组返回一个统计值,分析函数和聚合函数的不同之处是什么

个人明白就是,开窗函数和聚合函数功用是相反的。

SQL查询语句如下:
select *
from
(
select name,class,s,rank()over(partition by class order by s desc) mm
from t2

一、Oracle分析函数入门

 

浅析函数是怎么样?
浅析函数是Oracle专门用于化解复杂报表总计要求的作用强大的函数,它能够在多少中实行分组然后计算基于组的某种总括值,并且每一组的每1行都足以回去二个总计值。

          

剖析函数和聚合函数的差别之处是怎样?
普普通通的聚合函数用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

   

 示例目的:当前行的上1行(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分析函数ROW_NUMBE君越()|RANK()|LAG()使用详解

 

 

1.三、扶助通晓over()的实例

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

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

运作结果:

 

图片 17

        

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

 

图片 18

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

图片 19

运作结果:

 

图片 20

                   
例叁:关怀点:sql无排序,over()排序子句有,窗口也有,窗口特意强调全组数据

 

图片 21

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;

图片 22

运维结果:

 

图片 23

      
例四:关怀点:sql有排序(正序),over()排序子句无,先做sql排序再拓展解析函数运算

 

图片 24

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

图片 25

运维结果:

 

图片 26

 

例五:关心点:sql有排序(倒序),over()排序子句无,先做sql排序再开始展览辨析函数运算

 

图片 27

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;

图片 28

运转结果:

图片 29

                 

例陆:关切点:sql有排序(倒序),over()排序子句有,窗口子句无,此时的演算是:sql先选数据只是不排序,而后排序子句先排序并拓展解析函数处理(窗口私下认可为首先行到眼下行),最后再开展sql排序

 

 

图片 30

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;

图片 31

运维结果:

图片 32

 

图片 33

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;

图片 34

运作结果:

图片 35

              

 

开窗函数适用于在每一行的最后1列添加聚合函数的结果。

个体领会正是,开窗函数和聚合函数功用是相反的。

二、理解over()函数

一.一、多个order by的施行时机
解析函数(以及与其合作的开窗函数over())是在漫天sql查询停止后(sql语句中的order
by的施行相比较优异)再开始展览的操作, 也等于说sql语句中的order
by也会影响分析函数的实践结果:

a) 两者1致:尽管sql语句中的order
by满意与分析函数合作的开窗函数over()分析时讲求的排序,即sql语句中的order
by子句里的内容和开窗函数over()中的order by子句里的剧情千篇一律,

那正是说sql语句中的排序将先实施,分析函数在分析时就无需再排序;
b) 两者分裂:假若sql语句中的order
by不满意与分析函数合营的开窗函数over()分析时讲求的排序,即sql语句中的order
by子句里的内容和开窗函数over()中的order by子句里的剧情不1致,

那么sql语句中的排序将最后在解析函数分析截止后实施排序。

           

壹.2、开窗函数over()分析函数中的分组/排序/窗口
      开窗函数over()分析函数涵盖多少个分析子句:分组子句(partition
by), 排序子句(order by), 窗口子句(rows)
      窗口正是分析函数分析时要拍卖的数目范围,就拿sum来说,它是sum窗口中的记录而不是全方位分组中的记录,由此我们在想获取有个别栏位的累计值时,大家必要把窗口钦定到该分组中的第贰行数据到近期行,
即使你内定该窗口从该分组中的第3行到最后壹行,那么该组中的每二个sum值都会同样,即全数组的总和。

      窗口子句在此间作者只说rows格局的窗口,range方式和滑动窗口也不提。

 

     
窗口子句中我们常常选拔指定第二行,当前行,最终壹行如此的多少个属性:
先是行是 unbounded preceding,
日前行是 current row,
末段壹行是 unbounded following,

注释:

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

unbounded
preceding即首先行是指表中2个分组里的率先行, unbounded
following即最终1行是指表中3个分组里的末段一行;

开窗函数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)

上述示例内定窗口为1体分组。而出现order
by子句的时候,不自然要有窗口子句,但成效会很不一样等,此时的窗口默许是当前组的第一行到当下行!

 

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

 

而随正是不是省略分组子句,如下结论都以确立的:

1、窗口子句不能够独立出现,必须有order by子句时才能冒出

二、当省略窗口子句时:
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分析函数入门

 

浅析函数是怎么?
浅析函数是Oracle专门用来缓解复杂报表计算必要的效能强大的函数,它能够在多少中展开分组然后总括基于组的某种总计值,并且每壹组的每一行都可以回到一个总结值。

          

剖析函数和聚合函数的区别之处是何许?
平凡的聚合函数用group
by分组,每种分组重临贰个计算值,而分析函数采取partition
by分组,并且每组每行都能够回到叁个计算值。

              

剖析函数的方式
浅析函数带有三个开窗函数over(),包蕴多个分析子句:分组(partition by),
排序(order by), 窗口(rows) ,
她们的运用格局如下:over(partition by xxx
order by yyy rows between zzz)。
注:窗口子句在此地小编只说rows形式的窗口,range情势和滑动窗口也不提

    

分析函数例子(在scott用户下模拟)

演示指标:突显各部门职工的薪俸,并顺便展现该有的的参天工资。

图片 36

--显示各部门员工的工资,并附带显示该部分的最高工资。
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;

图片 37

运营结果:

图片 38

               

演示目标:依照deptno分组,然后总括每组值的总数

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

运作结果:

图片 39

     

以身作则指标:对各单位展开分组,并顺便展现第一行至当前行的汇总

图片 40

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;

图片 41

运转结果:

图片 42

   

示范指标:当前行至最终一行的汇聚

图片 43

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;

图片 44

运作结果:

图片 45

   

 示例指标:当前行的上1行(rownum-1)到如今行的集中

图片 46

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;

图片 47

运作结果:

图片 48

    

以身作则指标:   当前行的上一行(rownum-一)到当前行的下辆行(rownum+二)的集中     

图片 49

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;

图片 50

运营结果:

图片 51

      

 

 

select id,name,max(score) from Student group by id,name order by name

三个学习性职务:每种人有两样次数的成就,计算出各类人的最高成绩。

Oracle分析函数ROW_NUMBE福睿斯()|RANK()|LAG()使用详解

 

一.三、协助理解over()的实例

例一:关切点:sql无排序,over()排序子句不难

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

运营结果:

 

图片 52

        

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

 

图片 53

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

图片 54

运维结果:

 

图片 55

                   
例三:关注点:sql无排序,over()排序子句有,窗口也有,窗口特意强调全组数据

 

图片 56

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;

图片 57

运作结果:

 

图片 58

      
例四:关心点:sql有排序(正序),over()排序子句无,先做sql排序再开始展览辨析函数运算

 

图片 59

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

图片 60

运作结果:

 

图片 61

 

例5:关心点:sql有排序(倒序),over()排序子句无,先做sql排序再拓展解析函数运算

 

图片 62

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;

图片 63

运作结果:

图片 64

                 

例六:关怀点:sql有排序(倒序),over()排序子句有,窗口子句无,此时的运算是:sql先选数据只是不排序,而后排序子句先排序并实行分析函数处理(窗口暗许为第二行到最近行),最后再开始展览sql排序

 

 

图片 65

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;

图片 66

运行结果:

图片 67

 

图片 68

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;

图片 69

运作结果:

图片 70

              

 

Oracle的LAG和LEAD分析函数

 

 

 

 

幸免那种意况,能够利用开窗函数。

Oracle的LAG和LEAD分析函数

 

 

 

二、理解over()函数

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.二、开窗函数over()分析函数中的分组/排序/窗口
      开窗函数over()分析函数包括四个分析子句:分组子句(partition
by), 排序子句(order by), 窗口子句(rows)
      窗口就是分析函数分析时要拍卖的数码范围,就拿sum来说,它是sum窗口中的记录而不是1体分组中的记录,因而大家在想取得有个别栏位的累计值时,咱们需求把窗口钦定到该分组中的第二行数据到最近行,
假若你钦点该窗口从该分组中的第1行到最后1行,那么该组中的每3个sum值都会同样,即全部组的总和。

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

 

     
窗口子句中大家日常使用钦点第一行,当前行,最后壹行如此的五个属性:
先是行是 unbounded preceding,
日前行是 current row,
最后一行是 unbounded following,

注释:

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

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

开窗函数over()简单来说了分组(partition by)子句时, 

unbounded
preceding即首先行是指表中的首先行, unbounded
following即最终壹行是指表中的最终1行。

 

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

例如:

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

上述示例钦定窗口为一切分组。而产出order
by子句的时候,不必然要有窗口子句,但职能会很不一样,此时的窗口私下认可是当下组的第3行到近年来行!

 

若果不难分组,则把任何记录当成3个组。
a) 如果存在order by则默认窗口是unbounded preceding and current
row   –当前组的第一行到当前行
b) 如果这时省略order by则窗口默认为unbounded preceding and unbounded
following  –整个组
 

 

而无论是是不是省略分组子句,如下结论都以建立的:

1、窗口子句不能独立现身,必须有order by子句时才能出现

二、当省略窗口子句时:
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)在那几个窗口范围实践。

 

参见:

 

) as t
where t.mm=1

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

三、常见分析函数详解

为了方便开始展览实践,特将演示表和数目罗列如下:

一、创建表

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

      

二、插入数据

图片 71

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;

图片 72

            

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

图片 73

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

图片 74

运营结果:

图片 75

   

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

rank,dense_rank,row_number函数为每条记下爆发3个从壹始发至n的自然数,n的值大概低于等于记录的总数。那三个函数的绝无仅有分歧在于当遭受相同数量时的排名策略。
①row_number: 
row_number函数再次来到一个唯1的值,当境遇相同数量时,排行根据记录集中记录的各类依次递增。
②dense_rank: 
dense_rank函数再次回到三个唯1的值,当碰到相同数量时,此时拥有同1数量的排行都是均等的。
③rank: 
rank函数再次来到一个唯1的值,当蒙受相同的多少时,此时持有同一数量的排名是1致的,同时会在最后一条相同记录和下一条区别记录的排行之间空出排行。

          

示范数据在Oracle自带的scott用户下:
一、rank()值相同时排名一样,其后排行跳跃不总是

图片 76

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

图片 77

运维结果:

图片 78
2、dense_rank()值相同时排行一样,其后排行连续不跳跃

图片 79

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

图片 80

运维结果:

图片 81
3、row_number()值相同时排行不对等,其后排行接二连三不跳跃

图片 82

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

图片 83

运维结果:

图片 84

 

五、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  


   

6、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),
最终对全表举办集中操作。

   

一、生成演示数据:
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;

图片 85

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);

图片 86

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);

图片 87

  

七、max(),min(),sun()与avg():求移动的最值总和与平均值
难点:计算出各类地方连日七个月的通话成本的平平均数量(移动平均值)

 

图片 88

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)

图片 89

运作结果:

图片 90

  

标题:求外地点按月度拉长的话费

图片 91

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

图片 92

运维结果:

图片 93

 


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

另见:《Oracle分析函数ROW_NUMBECR-V()|RANK()|LAG()使用详解》

三、常见分析函数详解

为了有利于进行实施,特将演示表和数目罗列如下:

一、创建表

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函数为每条记下发生二个从一发端至n的自然数,n的值大概低于等于记录的总数。那三个函数的绝无仅有差异在于当曰镪相同数量时的排名策略。
①row_number: 
row_number函数重回二个唯一的值,当蒙受相同数量时,排行依据记录集中记录的一1依次递增。
②dense_rank: 
dense_rank函数重临1个唯一的值,当碰着相同数量时,此时享有同一数量的排行都以一样的。
③rank: 
rank函数重临1个唯1的值,当遭遇相同的数量时,此时颇具同一数量的排名是千篇一律的,同时会在最后一条相同记录和下一条分裂记录的排行之间空出排行。

          

以身作则数据在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函数能够在1回询问中取出同一字段的前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  


   

6、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):
则第3会对(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

  

7、max(),min(),sun()与avg():求移动的最值总和与平均值
难点:总计出各种地点接连7个月的打电话开销的平均数(移动平均值)

 

图片 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

防止那种景况,能够应用开窗函数。

常用分析函数:(最常用的应该是一.二.叁 的排序)
1、row_number() over(partition by … order by …)
2、rank() over(partition by … order by …)
3、dense_rank() over(partition by … order by …)
4、count() over(partition by … order by …)
5、max() over(partition by … order by …)
6、min() over(partition by … order by …)
7、sum() over(partition by … order by …)
8、avg() over(partition by … order by …)
9、first_value() over(partition by … order by …)
10、last_value() over(partition by … order by …)
11、lag() over(partition by … order by …)
12、lead() over(partition by … order by …)
lag 和lead 能够获取结果集中,按自然排序所排列的此时此刻行的左右相邻若干offset
的有些行的有个别列(不用结果集的自关系);
lag ,lead 分别是向前,向后;
lag 和lead
有八个参数,第5个参数是列名,首个参数是偏移的offset,第5个参数是
超出记录窗口时的暗中认可值)

聚合函数,将多行数据统十分一一行数据;而开窗函数则是将1行数据拆分成多行。

例如 : 1 张三 100