精华内容
下载资源
问答
  • over函数

    千次阅读 2018-07-09 09:11:56
    1、over函数的写法:  over(partition by class order by sroce) 按照sroce排序进行累计,order by是个默认的开窗函数,按照class分区。  2、开窗的窗口范围:  over(order by sroce range between 5 ...

    1、over函数的写法:

      over(partition by class order by sroce) 按照sroce排序进行累计,order by是个默认的开窗函数,按照class分区。

      2、开窗的窗口范围:

      over(order by sroce range between 5 preceding and 5 following):窗口范围为当前行数据幅度减5加5后的范围内的。

      over(order by sroce rows between 5 preceding and 5 following):窗口范围为当前行前后各移动5行。

      3、与over()函数结合的函数的介绍

      (1)、查询每个班的第一名的成绩:如下 

    1 SELECT * FROM (select t.name,t.class,t.sroce,rank() over(partition by t.class order by t.sroce desc) mm from T2_TEMP t) where mm = 1;
      结果为:

    1 得到的结果是:
    2 dss        1        95        1
    3 ffd        1        95        1
    4 gds        2        92        1
    5 gf         3        99        1
    6 ddd        3        99        1

      注意:在求第一名成绩的时候,不能用row_number(),因为如果同班有两个并列第一,row_number()只返回一个结果。

    1 SELECT * FROM (select t.name,t.class,t.sroce,row_number() over(partition by t.class order by t.sroce desc) mm from T2_TEMP t) where mm = 1;

      结果为:
    dss      1        95        1  
    gfs      2        92        1
    ddd      3        99        1 

      可以看出,本来第一名是两个人的并列,结果只显示了一个。

      (2)、rank()和dense_rank()可以将所有的都查找出来,rank可以将并列第一名的都查找出来;rank()和dense_rank()区别:rank()是跳跃排序,有两个第二名时接下来就是第四名。

      求班级成绩排名:

    1 select t.name,t.class,t.sroce,rank() over(partition by t.class order by t.sroce desc) mm from T2_TEMP t;

      查询结果:

    dss        1        95        1
    ffd        1        95        1
    fda        1        80        3
    gds        2        92        1
    cfe        2        74        2
    gf         3        99        1
    ddd        3        99        1
    3dd        3        78        3
    asdf       3        55        4
    adf        3        45        5

      dense_rank()l是连续排序,有两个第二名时仍然跟着第三名

    1 select t.name,t.class,t.sroce,dense_rank() over(partition by t.class order by t.sroce desc) mm from T2_TEMP t;
      查询结果:

    dss        1        95        1
    ffd        1        95        1
    fda        1        80        2 
    gds        2        92        1
    cfe        2        74        2
    gf         3        99        1
    ddd        3        99        1
    3dd        3        78        2
    asdf       3        55        3
    adf        3        45        4

      3、sum()over()的使用

      根据班级进行分数求和

    1 select t.name,t.class,t.sroce,sum(t.sroce) over(partition by t.class order by t.sroce desc) mm from T2_TEMP t;

    dss        1        95        190  --由于两个95都是第一名,所以累加时是两个第一名的相加
    ffd        1        95        190 
    fda        1        80        270  --第一名加上第二名的
    gds        2        92        92
    cfe        2        74        166
    gf         3        99        198
    ddd        3        99        198
    3dd        3        78        276
    asdf       3        55        331
    adf        3        45        376

      4、first_value() over()和last_value() over()的使用

    1 select t.name,t.class,t.sroce,first_value(t.sroce) over(partition by t.class order by t.sroce desc) mm from T2_TEMP t;
    2 select t.name,t.class,t.sroce,last_value(t.sroce) over(partition by t.class order by t.sroce desc) mm from T2_TEMP t;

      分别求出第一个和最后一个成绩。

      5、sum() over()的使用

    1 select t.name,t.class,t.sroce,sum(t.sroce) over(partition by t.class order by t.sroce desc) mm from T2_TEMP t;

      求出班级的总分。

      下面还有很多用法,就不一一列举了,简单介绍一下,和上面用法类似:

    count() over(partition by ... order by ...):求分组后的总数。
      max() over(partition by ... order by ...):求分组后的最大值。
      min() over(partition by ... order by ...):求分组后的最小值。
      avg() over(partition by ... order by ...):求分组后的平均值。
      lag() over(partition by ... order by ...):取出前n行数据。  
    
      lead() over(partition by ... order by ...):取出后n行数据。

      ratio_to_report() over(partition by … order by …):Ratio_to_report() 括号中就是分子,over() 括号中就是分母。

      percent_rank() over(partition by … order by …):

      6、over partition by与group by的区别:

      group by是对检索结果的保留行进行单纯分组,一般和聚合函数一起使用例如max、min、sum、avg、count等一块用。partition by虽然也具有分组功能,但同时也具有其他的高级功能。

    转载:https://www.cnblogs.com/lcngu/p/5335170.html

    select id_card as "idCard", user_id_card, update_date
      from (select row_number() over(partition by id_card order by update_date desc) lev,
                   collection_log.*
              from collection_log
             where app_id = '1'
    
               and bio_type = '1'
               and user_id = '2')
     where lev = 1
     order by update_date desc
    展开全文
  • over 函数

    2011-10-27 10:07:29
    1.over不能单独使用,要和分析函数:rank(),dense_rank(),row_number()等一起使用。 dense_rank与rank()用法相当,但是有一个区别:dence_rank在处理相同的等级时,等级的数值不会跳过。rank则跳过。 其参数:...
    1.over不能单独使用,要和分析函数:rank(),dense_rank(),row_number()等一起使用。
    dense_rank与rank()用法相当,但是有一个区别:dence_rank在处理相同的等级时,等级的数值不会跳过。rank则跳过。

    其参数:over(partition by columnname1 order by columnname2)
    含义:按columname1指定的字段进行分组排序,或者说按字段columnname1的值进行分组排序。
    2.类似 sum(...) over ... 的使用
    sum(sal) over (order by ename) 连续求和,
    sum(sal) over () 总和, -- 此处sum(sal) over () 等同于sum(sal)

    sum(sal) over (partition by deptno order by ename) 按部门“连续”求总和
    sum(sal) over (partition by deptno) 按部门求总和
    sum(sal) over (order by deptno,ename) 不按部门“连续”求总和
    sum(sal) over () 不按部门,求所有员工总和,效果等同于sum(sal)。
    展开全文
  • Oracle开发的over函数

    2019-03-05 16:08:08
    Oracle开发的over函数
  • ORACLE的OVER函数使用技巧ORACLE的OVER函数使用技巧ORACLE的OVER函数使用技巧
  • over函数的使用

    2019-03-28 17:16:37
    over函数结合的几个函数介绍 Row_number() over()、rank() over()和dense_rank()over()函数的使用 Select * from (select name,class,s,rank() over(partition by class order by s desc) mm from t2) where mm=1;...
  • 分析函数 over函数

    2014-10-29 16:55:06
    over函数 2009-08-27 18:34:54| 分类: 函数 |举报 |字号 订阅 over()函数是Oracle的分析函数,其语法如下: 函数名( [ 参数 ] ) over( [ 分区子句 ] [ 排序子句 [ 滑动窗口子句 ] ]) 分区子句类似于聚组...
    over()函数是Oracle的分析函数,其语法如下:
    

    函数名( [ 参数 ] ) over( [ 分区子句 ]  [ 排序子句 [ 滑动窗口子句 ] ])

    分区子句类似于聚组函数所需要的group by,排序子句可看成是SQL语句中的order by,只不过在此语句中还可指定null值排前(nulls first)还是排后(nulls last)。滑动窗口语句就比较复杂,参见manual中的sql reference,总体说明可见:http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96540/functions2a.htm#81409

    示例1:剔除重复记录

    表temp_b的记录:

    over函数 - 妞妞 - 渴望,付出,收获。

    执行如下SQL语句:

    select * from (

           select city_name, city_code, prov_id, row_number() over(partition by city_name order by 1) r from temp_a

    ) where r = 1;

    剔除表temp_a中city_name相同的记录

    over函数 - 妞妞 - 渴望,付出,收获。

    示例2:分组排序

    成绩表的所有记录如下:

    over函数 - 妞妞 - 渴望,付出,收获。

    执行如下SQL语句:

    select student_name, class, score, dense_rank() over(partition by class order by score desc) 名次 from temp_b;

    分班级按成绩排名次排序如下:

    over函数 - 妞妞 - 渴望,付出,收获。

    over函数 - 妞妞 - 渴望,付出,收获。提示:

    函数dense_rank()是连续排序,有两个第二名时仍然跟着第三名。

    函数rank()是跳跃排序,有两个第二名时接下来就是第四名(同样是在各个分组内)

    示例3:分组统计

    成绩表的所有记录如下:

    over函数 - 妞妞 - 渴望,付出,收获。

    执行如下SQL语句:

    select student_name, class, score, sum(score) over(partition by class order by score desc) 累计 from temp_b;

    班级成绩累计("连续"求和)结果如下:

    over函数 - 妞妞 - 渴望,付出,收获。

    over函数 - 妞妞 - 渴望,付出,收获。提示:此功能用于统计某一段时间每天累计的收入非常有用。

    执行如下SQL语句:

    select student_name, class, score, sum(score) over(partition by class) 班级总分 from temp_b;

    班级成绩总分统计如下:

    over函数 - 妞妞 - 渴望,付出,收获。

    <正文结束>


    展开全文
  • hive over函数应用

    2017-05-08 10:00:18
    over: hive over函数

    over:窗口函数


    建表及插入数据:

    create table temp.over_test (
    name           string,
    part           string,
    salary         decimal(30,8),
    age            string
    )              
    row format delimited fields terminated by '\036'
    stored as rcfile;
    
    insert into table temp.over_test  select '11','a','100','12'              ;
    insert into table temp.over_test  select '21','a','211','12'              ;
    insert into table temp.over_test  select '11','a','323','34'              ;
    insert into table temp.over_test  select '241','b','432','12'             ;
    insert into table temp.over_test  select '231','b','123','12'             ;
    insert into table temp.over_test  select '2121','a','422','12'            ;
    insert into table temp.over_test  select '2012','a','422','12'            ;
    insert into table temp.over_test  select '13','c','131','12'              ;
    insert into table temp.over_test  select '123','b','34','12'              ;
    insert into table temp.over_test  select '342','e','789','12'             ;
    insert into table temp.over_test  select '1222231','s','555','12'         ;
    insert into table temp.over_test  select '675','e','666','12'             ;
    insert into table temp.over_test  select '51','b','33','12'               ;
    insert into table temp.over_test  select '41','b','222','12'              ;
    insert into table temp.over_test  select '11','a','36','12'               ;

    应用:

    select * from temp.over_test
    select name,salary,max(salary)over(partition by part) from temp.over_test
    
    select name,part,salary, sum(salary)over(order by salary range between 20 preceding and 20 following) mm
     from temp.over_test--salary +-20范围内salary相加
    
    select name,part,salary, sum(salary)over(order by salary rows between 1 preceding and 2 following) mm 
    from temp.over_test--前1后2行一起相加
    
    select name,part,salary, rank()over(partition by part order by salary desc)  from temp.over_test--跳号排序1、1、3
    
    select name,part,salary, row_number()over(partition by part order by salary desc)  from temp.over_test--排序加唯一行号
    
    select name,part,salary, dense_rank()over(partition by part order by salary desc)  from temp.over_test--不跳号排序1、1、2
    
    select name,part,salary, sum(salary)over(partition by part order by salary) from temp.over_test--本次之前salary相加
    
    select name,part,salary, lag(cast(salary as string),1,'')over(order by salary) as qian from temp.over_test--排序后前一行值,无用''代替(string 类型下有效)
    
    select name,part,salary, lead(cast(salary as string),1,'')over(partition by part order by salary desc) as qian--后一行数据
     from temp.over_test
    
    select name,part,salary, ntile(4)over(order by salary desc) as qian from temp.over_test--数据四分片返回所在分偏值
    
    select salary,ratio_to_report(salary)over() from temp.over_test --didn't support 求salary的百分比
    
    select name,part,salary,percent_rank()over(partition by part order by name desc) from temp.over_test--(所在行-1)/(分类后总行-1)
    
    select name,part,salary,cume_dist()over(partition by part order by salary desc) from temp.over_test--[所在行-1(并列则+并列数-1)]/总行数
    select salary,
    percentile_cont(0.7) within GROUP(ORDER BY salary) over(PARTITION BY part) "Percentile_Cont",
    percent_rank()over(partition by part order by name desc) from temp.over_test

    以上内容参考自:

    http://blog.csdn.net/sherri_du/article/details/53312085



    展开全文
  • Oracle over函数

    2017-03-30 10:33:57
    Oracle over函数 SQL code: sql over的作用及用法 RANK ( ) OVER ( [query_partition_clause] order_by_clause ) DENSE_RANK ( ) OVER ( [query_partition_clause] order_by_clause ) 可实现按指定的字段分组...
  • Oracle over函数和rank函数学习

    千次阅读 2017-05-19 17:51:28
    什么是over函数over函数简单说就是给Oracle的分析函数加条件,而比较常见的就是结合sum(),rank()函数使用sum() overselect empno, ename, deptno, sal, sum(sal) over (partition by deptno) as depsal from ...
  • Oracle 中的over函数

    2018-10-25 09:42:31
    over 函数 SQL中有经常跟over()开窗函数搭配的是row_number(),一般用于去重或者根据某些字段分组(partition by)然后再进行排序(order by )得到最终的数据。 rank () over() 和 dence_rank() over() 根据业务...
  • Oracle over函数

    2011-08-08 16:03:55
    oracle over函数 oracle over函数 [size=x-small;]sql code: sql over的作用及用法rank ( ) over ( [query_partition_clause] order_by_clause )dense_rank ( ) over ( [query_partition_clause] order_by_clause )...
  • sql中与over函数结合的几个函数介绍
  • Hive 中的OVER函数解析

    千次阅读 2019-10-31 10:43:01
    over() 函数在Hive中运用广泛,通常搭配row_number() ,min(),max(),sum()来使用,总结下over函数划定窗口的范围 id name num 1 a 1 2 b 3 3 a 5 4 c 9 5 b 3 over() 此时每一行的窗口都是所有的行...
  • oracle中的over函数怎么用的 · OVER 分析函数   Oracle从8.1.6开始提供分析函数,分析函数用于计算基于组的某种聚合值,它和聚合函数的不同之处是对于每个组返回多行,而聚合函数对于每个组只返回一行。 一...
  • Orcal中over函数.doc

    2012-08-22 11:09:25
    对于SQL文中用的OVER函数,可以参照一下,作用比较大~~~
  • oracle over函数详解

    千次阅读 2016-12-12 10:00:53
    今天在javaeye上看到一道面试题,很多人都用over函数解决的  特意查了一下它的用法 SQL> select deptno,ename,sal  2 from emp  3 order by deptno; DEPTNO ENAME SAL ---------- ---------- ---------...
  • over()函数写法over(partition by expr2 order by ...over函数不能单独使用,需要与row_number(),rank()和dense_rank,lag()和lead(),sum()等配合使用。 分区partiton by 与 group by的区别 group by会将结果集按...
  • oracle sum()over函数的使用  0 个评论  收藏 我要投稿 oracle sum()over函数的使用   over不能单独使用,要和分析函数:rank(),dense_rank(),row_number()等一起使用。    over...
  • OVER函数介绍

    千次阅读 2017-02-17 16:59:02
    开窗函数指定了分析函数工作的数据窗口... over(order by salary) 按照salary排序进行累计,order by是个默认的开窗函数  over(partition by deptno)按照部门分区    over(partition by deptno order by
  • oracle - over函数

    2010-10-08 23:18:11
    Oracle - over函数 作用 over()函数是Oracle的分析函数,其语法如下: 函数名( [ 参数 ] ) over( [ 分区子句 ] [ 排序子句 [ 滑动窗口子句 ] ]) 分区子句类似于聚组函...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 8,226
精华内容 3,290
关键字:

over函数