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客商下模拟)

演示指标:展现各单位职工的工薪,并顺便显示该有的的参天薪水。

图片 1

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

图片 2

运维结果:

图片 3

               

示范目标:依照deptno分组,然后计算每组值的总额

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

运作结果:

图片 4

     

演示指标:对各机构开展分组,并顺便显示第风度翩翩行至当前进的集聚

图片 5

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

图片 6

运转结果:

图片 7

   

演示指标:当前进至最后朝气蓬勃行的聚焦

图片 8

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

图片 9

运作结果:

图片 10

   

 示例指标:当前进的上风度翩翩行(rownum-1)到日前进的汇总

图片 11

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

图片 12

运营结果:

图片 13

    

身体力行指标:   当前进的上生机勃勃行(rownum-1)到当下行的下辆行(rownum+2)的集中     

图片 14

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

图片 15

运作结果:

图片 16

      

 

 

那一个难点应有照旧相对简便易行,其实就用聚合函数就好了。

二、理解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

Oracle的LAG和LEAD解析函数

 

 

 

下面这种情景只适用id 和name是各种对应的,不然查询出来的数量是不得法的。

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

 

1.3、帮忙通晓over()的实例

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

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

运转结果:

 

图片 17

        

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

 

图片 18

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

图片 19

运转结果:

 

图片 20

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

 

图片 21

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

图片 22

运转结果:

 

图片 23

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

 

图片 24

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

图片 25

运作结果:

 

图片 26

 

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

 

图片 27

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

图片 28

运行结果:

图片 29

                 

例6:关心点:sql有排序(倒序),over()排序子句有,窗口子句无,那时候的演算是:sql先选数据只是不排序,而后排序子句先排序并打开解析函数管理(窗口默以为第豆蔻年华行到当下行),最后再实行sql排序

 

 

图片 30

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

图片 31

运行结果:

图片 32

 

图片 33

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

图片 34

运作结果:

图片 35

              

 

例如 : 1 张三 100

三、常见深入分析函数详解

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

一、创建表

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

      

二、插入数据

图片 36

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;

图片 37

            

三、first_value()与last_value():求最值对应的别样质量
主题材料、抽出每月话费最高和压低的两个地段。

图片 38

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

图片 39

运维结果:

图片 40

   

四、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()值相同期排行相似,其后排名跳跃不三番两遍

图片 41

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

图片 42

运营结果:

图片 43
2、dense_rank()值相同期排名同样,其后排名三番两次不跳跃

图片 44

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

图片 45

运营结果:

图片 46
3、row_number()值相同一时候排行不等于,其后排行三番五次不跳跃

图片 47

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

图片 48

运行结果:

图片 49

 

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

图片 50

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

图片 51

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

图片 52

  

七、max(),min(),sun()与avg():求移动的最值总和与平均值
标题:总计出种种地点连续几日七个月的通话开支的平平均数量(移动平均值)

 

图片 53

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)

图片 54

运作结果:

图片 55

  

难点:求各市点按月度增加的通话费

图片 56

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

图片 57

运行结果:

图片 58

 


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

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

           2 张三 90

          查询出来的结果

          两条音讯都会输出。

制止这种气象,能够选取开窗函数。

个体驾驭正是,开窗函数和聚合函数功效是相反的。

聚合函数,将多行数据统10%生机勃勃行数据;而开窗函数则是将大器晚成行数据拆分成多行。

开窗函数能够知足上述难点,同事也足以满意其余主题材料。举个例子:求每种班最高成绩学子的音讯。

深入分析:每种人学号一定是例外的,名字恐怕有重名,最大复杂的景色是,种种班最高战表恐怕持续一个。

        假如后续选取起来的措施,那么是不能够知足必要的。

        使用开窗函数就能够很好的解决那个主题材料。

–每一种班级的战绩率先的上学的小孩子
–学子表中国国投息如下
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

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

 

心得:
rank()跳跃排序,有三个第二名时前边跟着的是第四名
dense_rank() 延续排序,有多个第二名时照旧跟着第三名

over()开窗函数: 在接受聚合函数后,会将多行产生生龙活虎行,
而开窗函数是将生机勃勃行成为多行;
再者在动用聚合函数后,纵然要显得其它的列必需将列加入到group by中,
而采纳开窗函数后,能够不选拔group by,直接将兼具新闻呈现出来。

开窗函数适用于在每意气风发行的末段一列增添聚合函数的结果。

常用开窗函数:
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,第多个参数是
超过记录窗口时的暗中同意值)

发表评论