【997755.com澳门葡京】oracle分析函数技术详解,开窗函数

一个学习性任务:每个人有不同次数的实绩,总结出每个人的参天成绩。

一个学习性任务:每个人有两样次数的成绩,总结出每个人的最高成绩。

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

演示目标:显示各机关员工的工钱,并顺便展现该部分的参天工资。

997755.com澳门葡京 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;

997755.com澳门葡京 2

运作结果:

997755.com澳门葡京 3

               

以身作则目标:遵照deptno分组,然后总括每组值的总和

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

运转结果:

997755.com澳门葡京 4

     

以身作则目标:对各机构开展分组,并顺便突显第一行至当前行的集中

997755.com澳门葡京 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;

997755.com澳门葡京 6

运行结果:

997755.com澳门葡京 7

   

演示目的:当前行至最终一行的集中

997755.com澳门葡京 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;

997755.com澳门葡京 9

运行结果:

997755.com澳门葡京 10

   

 示例目标:当前行的上一行(rownum-1)到眼前行的会聚

997755.com澳门葡京 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;

997755.com澳门葡京 12

运作结果:

997755.com澳门葡京 13

    

以身作则目的:   当前行的上一行(rownum-1)到眼前行的下辆行(rownum+2)的汇总     

997755.com澳门葡京 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;

997755.com澳门葡京 15

运行结果:

997755.com澳门葡京 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模式和滑动窗口也不提

997755.com澳门葡京,    

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

示范目标:突显各机关职工的工资,并顺便突显该片段的最高工资。

997755.com澳门葡京 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;

997755.com澳门葡京 18

运转结果:

997755.com澳门葡京 19

               

演示目标:遵照deptno分组,然后统计每组值的总数

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

运行结果:

997755.com澳门葡京 20

     

演示目标:对各机关举行分组,并顺便展现第一行至当前行的会聚

997755.com澳门葡京 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;

997755.com澳门葡京 22

运作结果:

997755.com澳门葡京 23

【997755.com澳门葡京】oracle分析函数技术详解,开窗函数。   

以身作则目标:当前行至最终一行的会聚

997755.com澳门葡京 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;

997755.com澳门葡京 25

运作结果:

997755.com澳门葡京 26

   

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

997755.com澳门葡京 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;

997755.com澳门葡京 28

运转结果:

997755.com澳门葡京 29

    

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

997755.com澳门葡京 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;

997755.com澳门葡京 31

运作结果:

997755.com澳门葡京 32

      

 

 

本条题材应当如故相对简单,其实就用聚合函数就好了。

以此题材应当仍旧相对简便易行,其实就用聚合函数就好了。

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

 

参见:

 

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()使用详解

 

1.3、协助了解over()的实例

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

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

运行结果:

 

997755.com澳门葡京 33

        

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

 

997755.com澳门葡京 34

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

997755.com澳门葡京 35

运行结果:

 

997755.com澳门葡京 36

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

 

997755.com澳门葡京 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;

997755.com澳门葡京 38

运转结果:

 

997755.com澳门葡京 39

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

 

997755.com澳门葡京 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;

997755.com澳门葡京 41

运作结果:

 

997755.com澳门葡京 42

 

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

 

997755.com澳门葡京 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;

997755.com澳门葡京 44

运转结果:

997755.com澳门葡京 45

                 

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

 

 

997755.com澳门葡京 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;

997755.com澳门葡京 47

运行结果:

997755.com澳门葡京 48

 

997755.com澳门葡京 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;

997755.com澳门葡京 50

运作结果:

997755.com澳门葡京 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;

运行结果:

 

997755.com澳门葡京 52

        

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

 

997755.com澳门葡京 53

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

997755.com澳门葡京 54

运行结果:

 

997755.com澳门葡京 55

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

 

997755.com澳门葡京 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;

997755.com澳门葡京 57

运转结果:

 

997755.com澳门葡京 58

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

 

997755.com澳门葡京 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;

997755.com澳门葡京 60

运行结果:

 

997755.com澳门葡京 61

 

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

 

997755.com澳门葡京 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;

997755.com澳门葡京 63

运转结果:

997755.com澳门葡京 64

                 

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

 

 

997755.com澳门葡京 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;

997755.com澳门葡京 66

运行结果:

997755.com澳门葡京 67

 

997755.com澳门葡京 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;

997755.com澳门葡京 69

运作结果:

997755.com澳门葡京 70

              

 

例如 : 1 张三 100

例如 : 1 张三 100

三、常见分析函数详解

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

一、创建表

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

      

二、插入数据

997755.com澳门葡京 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;

997755.com澳门葡京 72

            

三、first_value()与last_value():求最值对应的别样性能
题目、取出每月话费最高和最低的几个地面。

997755.com澳门葡京 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

997755.com澳门葡京 74

运行结果:

997755.com澳门葡京 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()值相同时名次一样,其后排行跳跃不连续

997755.com澳门葡京 76

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

997755.com澳门葡京 77

运作结果:

997755.com澳门葡京 78
2、dense_rank()值相同时名次一样,其后排行连续不跳跃

997755.com澳门葡京 79

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

997755.com澳门葡京 80

运作结果:

997755.com澳门葡京 81
3、row_number()值相同时名次不对等,其后排行连续不跳跃

997755.com澳门葡京 82

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

997755.com澳门葡京 83

运作结果:

997755.com澳门葡京 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;

997755.com澳门葡京 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);

997755.com澳门葡京 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);

997755.com澳门葡京 87

  

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

 

997755.com澳门葡京 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)

997755.com澳门葡京 89

运行结果:

997755.com澳门葡京 90

  

题材:求各地段按月度增长的电话费

997755.com澳门葡京 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

997755.com澳门葡京 92

运行结果:

997755.com澳门葡京 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 
);

      

二、插入数据

997755.com澳门葡京 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;

997755.com澳门葡京 95

            

三、first_value()与last_value():求最值对应的此外属性
题目、取出每月话费最高和压低的两个地段。

997755.com澳门葡京 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

997755.com澳门葡京 97

运转结果:

997755.com澳门葡京 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()值相同时名次一样,其后排行跳跃不总是

997755.com澳门葡京 99

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

997755.com澳门葡京 100

运行结果:

997755.com澳门葡京 101
2、dense_rank()值相同时排行一样,其后排行连续不跳跃

997755.com澳门葡京 102

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

997755.com澳门葡京 103

运行结果:

997755.com澳门葡京 104
3、row_number()值相同时排行不等于,其后排行连续不跳跃

997755.com澳门葡京 105

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

997755.com澳门葡京 106

运作结果:

997755.com澳门葡京 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;

997755.com澳门葡京 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);

997755.com澳门葡京 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);

997755.com澳门葡京 110

  

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

 

997755.com澳门葡京 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)

997755.com澳门葡京 112

运作结果:

997755.com澳门葡京 113

  

题目:求各地方按月度增长的电话费

997755.com澳门葡京 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

997755.com澳门葡京 115

运作结果:

997755.com澳门葡京 116

           2 张三 90

           2 张三 90

          查询出来的结果

          查询出来的结果

          两条音讯都会输出。

          两条音信都会输出。

制止这种情景,可以运用开窗函数。

避免这种气象,可以动用开窗函数。

个人精晓就是,开窗函数和聚合函数效能是倒转的。

个体理解就是,开窗函数和聚合函数功效是倒转的。

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

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

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

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

浅析:每个人学号一定是例外的,名字可能有重名,最大复杂的动静是,每个班最高战表可能不止一个。

解析:每个人学号一定是见仁见智的,名字或者有重名,最大复杂的境况是,每个班最高成绩或者无休止一个。

        假如继续运用起来的办法,那么是不可能满意要求的。

        假使后续应用起来的法门,那么是不可以满足要求的。

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

        使用开窗函数就能很好的缓解这些题材。

–每个班级的实绩率先的学生
–学生表中消息如下
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

–每个班级的实绩率先的学习者
–学生表中新闻如下
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

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

询问结果如下:
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

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

) as t
where t.mm=1

) as t
where t.mm=1

 

 

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

心得:
rank()跳跃排序,有五个第二名时前面跟着的是第四名
dense_rank() 连续排序,有五个第二名时依旧跟着第三名

over()开窗函数: 在采取聚合函数后,会将多行变成一行,
而开窗函数是将一行成为多行;
还要在选择聚合函数后,如若要来得其他的列必须将列出席到group by中,
而选取开窗函数后,可以不选取group by,直接将拥有音信展现出来。

over()开窗函数: 在动用聚合函数后,会将多行变成一行,
而开窗函数是将一行成为多行;
同时在采纳聚合函数后,假若要体现任何的列必须将列参加到group by中,
而采取开窗函数后,可以不利用group by,直接将所有音讯展现出来。

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

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

常用开窗函数:
1.为每条数据呈现聚合信息.(聚合函数() over())
2.为每条数据提供分组的聚合函数结实(聚合函数() over(partition by 字段) as
别名) –遵照字段分组,分组后展开总括
3.与排行函数一起行使(row number() over(order by 字段) as 别名)

常用开窗函数:
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.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,第三个参数是
超出记录窗口时的默认值)

相关文章

发表评论

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

*
*
Website