1010cc时时彩标准版 > 1010cc三分网站 > 1010cc时时彩标准版配上开窗函数over,SQL使用技巧

原标题:1010cc时时彩标准版配上开窗函数over,SQL使用技巧

浏览次数:155 时间:2019-08-14

一个学习性任务:每个人有不同次数的成绩,统计出每个人的最高成绩。

一、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用户下模拟)

示例目的:显示各部门员工的工资,并附带显示该部分的最高工资。

1010cc时时彩标准版 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;

1010cc时时彩标准版 2

运行结果:

1010cc时时彩标准版 3

               

示例目的:按照deptno分组,然后计算每组值的总和

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

运行结果:

1010cc时时彩标准版 4

     

示例目的:对各部门进行分组,并附带显示第一行至当前行的汇总

1010cc时时彩标准版 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;

1010cc时时彩标准版 6

运行结果:

1010cc时时彩标准版 7

   

示例目标:当前行至最后一行的汇总

1010cc时时彩标准版 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;

1010cc时时彩标准版 9

运行结果:

1010cc时时彩标准版 10

   

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

1010cc时时彩标准版 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;

1010cc时时彩标准版 12

运行结果:

1010cc时时彩标准版 13

    

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

1010cc时时彩标准版 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;

1010cc时时彩标准版 15

运行结果:

1010cc时时彩标准版 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用户下模拟)

示例目的:显示各部门员工的工资,并附带显示该部分的最高工资。

1010cc时时彩标准版 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;

1010cc时时彩标准版 18

运行结果:

1010cc时时彩标准版 19

               

示例目的:按照deptno分组,然后计算每组值的总和

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

运行结果:

1010cc时时彩标准版 20

     

示例目的:对各部门进行分组,并附带显示第一行至当前行的汇总

1010cc时时彩标准版 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;

1010cc时时彩标准版 22

运行结果:

1010cc时时彩标准版 23

   

示例目标:当前行至最后一行的汇总

1010cc时时彩标准版 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;

1010cc时时彩标准版 25

运行结果:

1010cc时时彩标准版 26

   

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

1010cc时时彩标准版 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;

1010cc时时彩标准版 28

运行结果:

1010cc时时彩标准版 29

    

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

1010cc时时彩标准版 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;

1010cc时时彩标准版 31

运行结果:

1010cc时时彩标准版 32

      

 

 

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)在这个窗口范围执行。

 

参见:

 

二、理解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)在这个窗口范围执行。

 

参见:

 

  decode(条件,值1,返回值1,值2,返回值2,...值n,返回值n,缺省值);

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

Oracle的LAG和LEAD分析函数

 

 

 

Oracle的LAG和LEAD分析函数

 

 

 

 

上边这种情况只适用id 和name是一一对应的,否则查询出来的数据是不正确的。

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

 

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

例1:关注点:sql无排序,over()排序子句省略

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

运行结果:

 

1010cc时时彩标准版 33

        

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

 

1010cc时时彩标准版 34

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

1010cc时时彩标准版 35

运行结果:

 

1010cc时时彩标准版 36

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

 

1010cc时时彩标准版 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;

1010cc时时彩标准版 38

运行结果:

 

1010cc时时彩标准版 39

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

 

1010cc时时彩标准版 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;

1010cc时时彩标准版 41

运行结果:

 

1010cc时时彩标准版 42

 

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

 

1010cc时时彩标准版 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;

1010cc时时彩标准版 44

运行结果:

1010cc时时彩标准版 45

                 

例6:关注点:sql有排序(倒序),over()排序子句有,窗口子句无,此时的运算是:sql先选数据但是不排序,而后排序子句先排序并进行分析函数处理(窗口默认为第一行到当前行),最后再进行sql排序

 

 

1010cc时时彩标准版 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;

1010cc时时彩标准版 47

运行结果:

1010cc时时彩标准版 48

 

1010cc时时彩标准版 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;

1010cc时时彩标准版 50

运行结果:

1010cc时时彩标准版 51

              

 

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

 

 

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

例1:关注点:sql无排序,over()排序子句省略

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

运行结果:

 

1010cc时时彩标准版 52

        

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

 

1010cc时时彩标准版 53

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

1010cc时时彩标准版 54

运行结果:

 

1010cc时时彩标准版 55

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

 

1010cc时时彩标准版 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;

1010cc时时彩标准版 57

运行结果:

 

1010cc时时彩标准版 58

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

 

1010cc时时彩标准版 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;

1010cc时时彩标准版 60

运行结果:

 

1010cc时时彩标准版 61

 

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

 

1010cc时时彩标准版 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;

1010cc时时彩标准版 63

运行结果:

1010cc时时彩标准版 64

                 

例6:关注点:sql有排序(倒序),over()排序子句有,窗口子句无,此时的运算是:sql先选数据但是不排序,而后排序子句先排序并进行分析函数处理(窗口默认为第一行到当前行),最后再进行sql排序

 

 

1010cc时时彩标准版 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;

1010cc时时彩标准版 66

运行结果:

1010cc时时彩标准版 67

 

1010cc时时彩标准版 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;

1010cc时时彩标准版 69

运行结果:

1010cc时时彩标准版 70

              

 

  select decode(sign(变量1-变量2),-1,变量1,变量2) from dual; --取较小值

例如 : 1 张三 100

三、常见分析函数详解

为了方便进行实践,特将演示表和数据罗列如下:

一、创建表

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

      

二、插入数据

1010cc时时彩标准版 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;

1010cc时时彩标准版 72

            

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

1010cc时时彩标准版 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

1010cc时时彩标准版 74

运行结果:

1010cc时时彩标准版 75

   

四、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()值相同时排名相同,其后排名跳跃不连续

1010cc时时彩标准版 76

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

1010cc时时彩标准版 77

运行结果:

1010cc时时彩标准版 78
2、dense_rank()值相同时排名相同,其后排名连续不跳跃

1010cc时时彩标准版 79

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

1010cc时时彩标准版 80

运行结果:

1010cc时时彩标准版 81
3、row_number()值相同时排名不相等,其后排名连续不跳跃

1010cc时时彩标准版 82

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

1010cc时时彩标准版 83

运行结果:

1010cc时时彩标准版 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  


   

六、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;

1010cc时时彩标准版 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);

1010cc时时彩标准版 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);

1010cc时时彩标准版 87

  

七、max(),min(),sun()与avg():求移动的最值总和与平均值
问题:计算出各个地区连续3个月的通话费用的平均数(移动平均值)

 

1010cc时时彩标准版 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)

1010cc时时彩标准版 89

运行结果:

1010cc时时彩标准版 90

  

问题:求各地区按月份累加的通话费

1010cc时时彩标准版 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

1010cc时时彩标准版 92

运行结果:

1010cc时时彩标准版 93

 


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

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

三、常见分析函数详解

为了方便进行实践,特将演示表和数据罗列如下:

一、创建表

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

      

二、插入数据

1010cc时时彩标准版 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;

1010cc时时彩标准版 95

            

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

1010cc时时彩标准版 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

1010cc时时彩标准版 97

运行结果:

1010cc时时彩标准版 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()值相同时排名相同,其后排名跳跃不连续

1010cc时时彩标准版 99

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

1010cc时时彩标准版 100

运行结果:

1010cc时时彩标准版 101
2、dense_rank()值相同时排名相同,其后排名连续不跳跃

1010cc时时彩标准版 102

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

1010cc时时彩标准版 103

运行结果:

1010cc时时彩标准版 104
3、row_number()值相同时排名不相等,其后排名连续不跳跃

1010cc时时彩标准版 105

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

1010cc时时彩标准版 106

运行结果:

1010cc时时彩标准版 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;

1010cc时时彩标准版 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);

1010cc时时彩标准版 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);

1010cc时时彩标准版 110

  

七、max(),min(),sun()与avg():求移动的最值总和与平均值
问题:计算出各个地区连续3个月的通话费用的平均数(移动平均值)

 

1010cc时时彩标准版 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)

1010cc时时彩标准版 112

运行结果:

1010cc时时彩标准版 113

  

问题:求各地区按月份累加的通话费

1010cc时时彩标准版 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

1010cc时时彩标准版 115

运行结果:

1010cc时时彩标准版 116

 

           2 张三 90

  sign()函数根据某个值是0、正数还是负数,分别返回0、1、-1

          查询出来的结果

  例如:

          两条信息都会输出。

  变量1=10,变量2=20

避免这种情况,可以使用开窗函数。

  则sign(变量1-变量2)返回-1,decode解码结果为“变量1”,达到了取较小值的目的。

个人理解就是,开窗函数和聚合函数功能是相反的。

 

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

举例:查询emp表中的每个部门的人数?

开窗函数可以满足上述问题,同事也可以满足其他问题。例如:求每个班最高成绩学生的信息。

SELECT sum(decode(deptno,10,1,0)) as 部门10,
            sum(decode(deptno,20,1,0)) as 部门20,
            sum(decode(deptno,30,1,0)) as 部门30 from emp

分析:每个人学号一定是不同的,名字可能有重名,最大复杂的情况是,每个班最高成绩可能不止一个。

1010cc时时彩标准版 117

        如果继续使用开始的方式,那么是不能满足要求的。

      

        使用开窗函数就能很好的解决这个问题。

      做个对比:select deptno,count(deptno) from emp group by deptno

--每个班级的成绩第一的学生
--学生表中信息如下
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

      1010cc时时彩标准版 118

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

 

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

2、递归查询的优化

) as t
where t.mm=1

   lpad/rpad( string, padded_length, [ pad_string ] )

 

   解释:字符不够的时候向左或者向右填充。

心得:
rank()跳跃排序,有两个第二名时后边跟着的是第四名
dense_rank() 连续排序,有两个第二名时仍然跟着第三名

   例如:SQL> select lpad('abcde',10,'x') from dual;

over()开窗函数: 在使用聚合函数后,会将多行变成一行,
而开窗函数是将一行变成多行;
并且在使用聚合函数后,如果要显示其他的列必须将列加入到group by中,
而使用开窗函数后,可以不使用group by,直接将所有信息显示出来。

      LPAD('ABCDE',10,'X')

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

      xxxxxabcde

常用开窗函数:
1.为每条数据显示聚合信息.(聚合函数() over())
2.为每条数据提供分组的聚合函数结果(聚合函数() over(partition by 字段) as 别名) --按照字段分组,分组后进行计算
3.与排名函数一起使用(row number() over(order by 字段) as 别名)

 

常用分析函数:(最常用的应该是1.2.3 的排序)
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 有三个参数,第一个参数是列名,第二个参数是偏移的offset,第三个参数是 超出记录窗口时的默认值)

  1. SELECT ID, FATHER_ID, NAME, CONNECT_BY_ISLEAF LEAF   
  2. FROM T_TREE   
  3. START WITH FATHER_ID = 0   
  4. CONNECT BY PRIOR ID = FATHER_ID;  

 

解释:CONNECT_BY_ISLEAF 判断该行记录是否为叶子节点,如果是返回1,否则返回0

          START WITH FATHER_ID = 0 规定哪一条记录为根节点

          CONNECT BY PRIOR ID = FATHER_ID 判断此节点的父节点是哪一条记录

 

例子:

 SELECT LPAD(MENU_NAME,

            LENGTHB(MENU_NAME) LEVEL,

            DECODE(CONNECT_BY_ISLEAF, 1, ' | ', ' '))

  FROM MENU

 START WITH PID = '0'

 CONNECT BY PRIOR ID = PID;

 

3、利用分析函数排序和去重

 

分析函数是什么?
分析函数是Oracle专门用于解决复杂报表统计需求的功能强大的函数,它可以在数据中进行分组然后计算基于组的某种统计值,并且每一组的每一行都可以返回一个统计值。

          

分析函数和聚合函数的不同之处是什么?
普通的聚合函数用group by分组,每个分组返回一个统计值,返回的字段名只能是分组名。而分析函数采用partition by分组,并且每组每行都可以返回一个统计值,返回的字段名可以是每个字段,因为是对应到记录的,所以没有关系。

              

分析函数的形式
分析函数带有一个开窗函数over(),包含三个分析子句:分组(partition by), 排序(order by), 窗口(rows) ,他们的使用形式如下:over(partition by xxx order by yyy rows between zzz)

 

 开窗函数over()包含三个分析子句:分组子句(partition by), 排序子句(order by), 窗口子句(rows)
      窗口就是分析函数分析时要处理的数据范围,就拿sum来说,它是sum窗口中的记录而不是整个分组中的记录,因此我们在想得到某个栏位的累计值时,我们需要把窗口指定到该分组中的第一行数据到当前行, 如果你指定该窗口从该分组中的第一行到最后一行,那么该组中的每一个sum值都会一样,即整个组的总和。

     

OVER(PARTITION BY DEPTNO ORDER BY ENAME ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW):当前组第一行到当前行的汇总

OVER(PARTITION BY DEPTNO ORDER BY ENAME ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING):当前行到最后一行的汇总

OVER(PARTITION BY DEPTNO ORDER BY ENAME ROWS BETWEEN 1 PRECEDING AND CURRENT ROW):当前行的上一行(rownum-1)到当前行的汇总

OVER(PARTITION BY DEPTNO ORDER BY ENAME ROWS BETWEEN 1 PRECEDING AND 2 FOLLOWING):当前行的上一行(rownum-1)到下两行(rownum 2)的汇总

 

而无论是否省略分组子句,如下结论都是成立的:

 

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

 2、当省略窗口子句时:
    a) 如果存在order by则默认的窗口是unbounded preceding and current row  --当前组的第一行到当前行,即在当前组中,第一行到当前行,这里强调一下,如果partition by字段和order by 字段一样的话,这个order by不生效,相当于省略了order by

    b) 如果同时省略order by则默认的窗口是unbounded preceding and unbounded following  --整个组

 

两个order by的执行时机

分析函数(以及与其配合的开窗函数over())是在整个sql查询结束后(sql语句中的order by的执行比较特殊)再进行的操作, 也就是说sql语句中的order by也会影响分析函数的执行结果:

本文由1010cc时时彩标准版发布于1010cc三分网站,转载请注明出处:1010cc时时彩标准版配上开窗函数over,SQL使用技巧

关键词:

上一篇:1010cc时时彩标准版WinFrom开发小案例,mysql面试常

下一篇:没有了