澳门新萄京:oracle分析函数技术详解(配上开窗函数over())

三个学习性职责:每一个人有分歧次数的成绩,计算出每一个人的参天战绩。

一个学习性任务:每种人有不一样次数的成就,总计出种种人的参天成绩。

一、Oracle分析函数入门

 

浅析函数是何等?
浅析函数是Oracle专门用于缓解复杂报表总计须要的功效强大的函数,它能够在数额中进行分组然后总括基于组的某种总计值,并且每一组的每1行都足以回去一个总计值。

          

浅析函数和聚合函数的分化之处是哪些?
平凡的聚合函数用group
by分组,种种分组再次回到1个总结值,而分析函数选取partition
by分组,并且每组每行都能够返回七个总计值。

              

分析函数的款式
浅析函数带有1个开窗函数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-一)到日前行的汇总

澳门新萄京 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-一)到近日行的下辆行(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-一)到近年来行的下辆行(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

      

 

 

本条题材应该仍旧相对简单,其实就用聚合函数就好了。

这一个标题应当依然相对简便易行,其实就用聚合函数就好了。

二、理解over()函数

一.一、八个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语句中的排序将最终在解析函数分析截至后进行排序。

           

一.贰、开窗函数over()分析函数中的分组/排序/窗口
      开窗函数over()分析函数含有四个分析子句:分组子句(partition
by), 排序子句(order by), 窗口子句(rows)
      窗口就是分析函数分析时要处理的数目范围,就拿sum来说,它是sum窗口中的记录而不是整套分组中的记录,由此大家在想获取某些栏位的累计值时,大家供给把窗口钦命到该分组中的第三行数据到如今行,
假设你内定该窗口从该分组中的第2行到最终1行,那么该组中的每三个sum值都会壹如既往,即全数组的总和。

      窗口子句在此处本人只说rows格局的窗口,range方式和滑动窗口也不提。

 

     
窗口子句中大家平日利用钦点第3行,当前行,最终壹行如此的两个属性:
第1行是 unbounded preceding,
日前行是 current row,
终极一行是 unbounded following,

注释:

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

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

开窗函数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子句时才能冒出

二、当省略窗口子句时:
a) 假若存在order by则暗中同意的窗口是unbounded preceding and current
row  –当前组的率先行到当下行,即在现阶段组中,第3行到近年来行
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)在那一个窗口范围推行。

 

参见:

 

二、理解over()函数

一.一、三个order by的履行时机
分析函数(以及与其合作的开窗函数over())是在全方位sql查询甘休后(sql语句中的order
by的推行相比独特)再拓展的操作, 也正是说sql语句中的order
by也会影响分析函数的执行结果:

a) 两者1致:假设sql语句中的order
by满足与分析函数合营的开窗函数over()分析时供给的排序,即sql语句中的order
by子句里的始末和开窗函数over()中的order by子句里的内容1律,

那就是说sql语句中的排序将先进行,分析函数在分析时就不要再排序;
b) 两者不等同:假若sql语句中的order
by不满足与分析函数同盟的开窗函数over()分析时必要的排序,即sql语句中的order
by子句里的始末和开窗函数over()中的order by子句里的始末不一致,

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

           

一.二、开窗函数over()分析函数中的分组/排序/窗口
      开窗函数over()分析函数涵盖七个分析子句:分组子句(partition
by), 排序子句(order by), 窗口子句(rows)
      窗口正是分析函数分析时要拍卖的多寡范围,就拿sum来说,它是sum窗口中的记录而不是整整分组中的记录,因而我们在想取得有些栏位的累计值时,大家要求把窗口内定到该分组中的第2行数据到眼下行,
假设你钦命该窗口从该分组中的第二行到终极一行,那么该组中的每1个sum值都会1如既往,即一切组的总数。

      窗口子句在那里自个儿只说rows方式的窗口,range方式和滑动窗口也不提。

 

     
窗口子句中大家平日选择钦定第三行,当前行,最终1行如此的四个属性:
首先行是 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子句的时候,不自然要有窗口子句,但成效会很区别,此时的窗口暗中认可是日前组的首先行到当前行!

 

比方简单分组,则把全副记下当成一个组。
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)在那么些窗口范围推行。

 

参见:

 

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

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

Oracle的LAG和LEAD分析函数

 

 

 

Oracle的LAG和LEAD分析函数

 

 

 

上边那种景况只适用id 和name是逐一对应的,否则查询出来的多寡是不得法的。

上面那种景观只适用id 和name是各种对应的,不然查询出来的数额是不得法的。

Oracle分析函数ROW_NUMBER()|RANK()|LAG()使用详解

 

一.3、协助驾驭over()的实例

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

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

运转结果:

 

澳门新萄京 33

        

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

 

澳门新萄京 34

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

澳门新萄京 35

运维结果:

 

澳门新萄京 36

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

 

澳门新萄京 37

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;

澳门新萄京 38

运营结果:

 

澳门新萄京 39

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

 

澳门新萄京 40

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

澳门新萄京 41

运维结果:

 

澳门新萄京 42

 

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

 

澳门新萄京 43

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;

澳门新萄京 44

运维结果:

澳门新萄京 45

                 

例陆:关切点:sql有排序(倒序),over()排序子句有,窗口子句无,此时的演算是:sql先选数据只是不排序,而后排序子句先排序并进行分析函数处理(窗口暗中同意为第二行到最近行),最终再展开sql排序

 

 

澳门新萄京 46

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;

澳门新萄京 47

运维结果:

澳门新萄京 48

 

澳门新萄京 49

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;

澳门新萄京 50

运营结果:

澳门新萄京 51

              

 

Oracle分析函数ROW_NUMBEHaval()|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

      
例4:关切点: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

                 

例6:关切点:sql有排序(倒序),over()排序子句有,窗口子句无,此时的演算是:sql先选数据只是不排序,而后排序子句先排序并进行辨析函数处理(窗口暗许为第3行到当前行),最终再展开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

              

 

例如 : 1 张三 100

例如 : 1 张三 100

3、常见分析函数详解

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

一、创建表

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

      

2、插入数据

澳门新萄京 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函数为每条记下爆发三个从壹发端至n的自然数,n的值恐怕低于等于记录的总和。那二个函数的唯1不相同在于当蒙受相同数量时的排行策略。
①row_number: 
row_number函数重临3个唯一的值,当碰到相同数量时,排行依据记录集中记录的逐条依次递增。
②dense_rank: 
dense_rank函数重回三个唯1的值,当境遇相同数量时,此时持有同一数量的排行都以同样的。
③rank: 
rank函数重回三个唯一的值,当蒙受相同的数码时,此时全体同1数量的排名是1模一样的,同时会在结尾一条相同记录和下一条不一样记录的排行之间空出排行。

          

以身作则数据在Oracle自带的scott用户下:
一、rank()值相同时排行1样,其后排行跳跃不一连

澳门新萄京 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_NUMBE库罗德()|RANK()|LAG()使用详解》

3、常见分析函数详解

为了便于举办实施,特将演示表和多少罗列如下:

一、创建表

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函数为每条记下爆发2个从壹早先至n的自然数,n的值大概低于等于记录的总数。这三个函数的绝无仅有差别在于当遭遇相同数量时的名次策略。
①row_number: 
row_number函数重回3个唯1的值,当遭遇相同数量时,排名根据记录集中记录的逐一依次递增。
②dense_rank: 
dense_rank函数重临二个唯一的值,当蒙受相同数量时,此时享有同壹数量的排行都是如出壹辙的。
③rank: 
rank函数返回二个唯一的值,当境遇相同的数码时,此时颇具同一数量的排名是1律的,同时会在结尾一条相同记录和下一条不一致记录的排行之间空出排名。

          

示范数据在Oracle自带的scott用户下:
一、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()值相同时排行1样,其后排名一而再不跳跃

澳门新萄京 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):
第3会对(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;

澳门新萄京 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

           2 张三 90

           2 张三 90

          查询出来的结果

          查询出来的结果

发表评论

电子邮件地址不会被公开。 必填项已用*标注

网站地图xml地图