精华内容
下载资源
问答
  • ORACLE窗函数语法和使用介绍,oracle开函数语法语法:row_number() OVER(PARTITION BY ...)分组取最大的objid那一条,根据objid倒叙排序,取rn=1select * from (select a.* ,row_number()over(partiotion by col1,...

    ORACLE开窗函数语法和使用介绍,oracle开函数语法

    语法:row_number() OVER(PARTITION BY ...)

    分组取最大的objid那一条,根据objid倒叙排序,取rn=1

    select * from (select a.* ,row_number()over(partiotion by col1,col2 order by col1,col2) r from tablename ) where r=1;

    分析函数和聚合函数的不同之处是什么?

    普通的聚合函数用group by分组,每个分组返回一个统计值,而分析函数采用partition by分组,并且每组每行都可以返回一个统计值。

    分析函数的形式

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

    --利用开窗函数取每组数据的最后一条数据

    SQL>select * from (select ROW_NUMBER() over (partition by accountid order by updatetimestamp desc) rn, plm_openaccountrecon.* from plm_openaccountrecon) where rn = 1 and t.accountid = :accountid

    --unbounded preceding and unbouned following针对当前所有记录的前一条、后一条记录,也就是表中的所有记录

    --unbounded:不受控制的,无限的

    --preceding:在...之前

    --following:在...之后

    --第一行到当前行

    SQL> select t.objid, t.applyempid, t.applydate, t.status, t.leavetype, t.leavedays, SUM(leavedays) OVER(PARTITION BY applyempid ORDER BY objid ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) lds from RT_GTCXLEAVE t;

    --当前行至最后一行

    SQL> select t.objid, t.applyempid, t.applydate, t.status, t.leavetype, t.leavedays, SUM(leavedays) OVER(PARTITION BY applyempid ORDER BY objid ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) lds from RT_GTCXLEAVE t;

    --当前行的上一行 (rownum - 1) 到当前行

    --BETWEEN 1 PRECEDING AND CURRENT ROW

    SQL> select t.objid, t.applyempid, t.applydate, t.status, t.leavetype, t.leavedays, SUM(leavedays) OVER(PARTITION BY applyempid ORDER BY objid ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) lds from RT_GTCXLEAVE t;

    --当前行的上一行 (rownum - 1) 到当前行的下两行 (rownum + 2)

    --ROWS BETWEEN 1 PRECEDING AND 2 FOLLOWING

    http://www.dengb.com/oracle/1319181.htmlwww.dengb.comtruehttp://www.dengb.com/oracle/1319181.htmlTechArticleORACLE开窗函数语法和使用介绍,oracle开函数语法 语法:row_number() OVER(PARTITION BY ...) 分组取最大的objid那一条,根据objid倒叙排序,取rn=1 sele...

    展开全文
  • oracle窗函数

    2021-08-03 14:29:51
    oracle窗函数和group by还是有不一样的地方, group by 是分组后就直接去重了, 开窗函数分组后不去重,并且还可以进行排序 使用方法 over(PARTITION BY age ) 表示对结果集按照age进行分区 实战 新建一个...

    开窗函数为 : over()

    说明

    oracle开窗函数和group by还是有不一样的地方,

    • group by 是分组后就直接去重了,
    • 开窗函数分组后不去重,并且还可以进行排序
      在这里插入图片描述

    使用方法

     over(PARTITION BY age ) 表示对结果集按照age进行分区
    

    实战

    新建一个user_test表,建表语句为:

    create table user_test(
      id number(20) not null,
      name varchar2(50),
      age number(3)
    )
    

    表数据如下,对没错,一共就四条数据,数据量少的情况下理解更容易,不然只有写文章的人能看懂,看文章的人却一脸懵;
    在这里插入图片描述

    当我们使用group by 语句时,得出来的结果是这样的,这是group by 的特性,分组后会自动去重
    在这里插入图片描述

    但是当我们使用开窗函数时,分组的同时,不会去重,并且还给你排好序
    在这里插入图片描述

    通过以上2个例子可以更好的理解开窗函数和group by的区别;但是开窗函数远不止这些用法
    接下来我们看看rank()的用法
    首先增加2条记录,最后的5和6是新加的
    在这里插入图片描述

    rank() 编号的时候排序字段会参与,数据相同的字段的编号也相同,总编号不变 1 2 2 4 5
    在这里插入图片描述

    dense_rank() 编号的时候排序字段会参与,数据相同的字段的编号也相同,总编号改变 1 2 2 3 4

    在这里插入图片描述

    其他用法

    当我将列和统计count(*)一起查询时会报错,
    在这里插入图片描述

    但是当我在count(*)后面加上开窗函数之后就可以运行了
    在这里插入图片描述

    展开全文
  • Oracle窗函数

    2020-07-28 17:30:50
    目前在 MSSQLServer、Oracle、DB2 等主流数据库中都提供了对开窗函数的支持,不过非常遗憾的是 MYSQL 暂时还未对开窗函数给予支持。 开窗函数简介:与聚合函数一样,开窗函数也是对行集组进行聚合计算,但是它不像...

    SQL开窗函数

    开窗函数:在开窗函数出现之前存在着很多用 SQL 语句很难解决的问题,很多都要通过复杂的相关子查询或者存储过程来完成。为了解决这些问题,在 2003 年 ISO SQL 标准加入了开窗函数,开窗函数的使用使得这些经典的难题可以被轻松的解决。目前在 MSSQLServer、Oracle、DB2 等主流数据库中都提供了对开窗函数的支持,不过非常遗憾的是 MYSQL 暂时还未对开窗函数给予支持。

    开窗函数简介:与聚合函数一样,开窗函数也是对行集组进行聚合计算,但是它不像普通聚合函数那样每组只返回一个值,开窗函数可以为每组返回多个值,因为开窗函数所执行聚合计

    算的行集组是窗口。在 ISO SQL 规定了这样的函数为开窗函数,在 Oracle 中则被称为分析函数。
     

     

    数据表(Oracle):T_Person 表保存了人员信息,FName 字段为人员姓名,FCity 字段为人员所在的城市名,FAge 字段为人员年龄,FSalary 字段为人员工资

    CREATE TABLE T_Person (FName VARCHAR2(20),FCity VARCHAR2(20),FAge INT,FSalary INT)

    向 T_Person 表中插入一些演示数据:

    复制代码

    INSERT INTO T_Person(FName,FCity,FAge,FSalary)
    VALUES('Tom','BeiJing',20,3000);
    INSERT INTO T_Person(FName,FCity,FAge,FSalary)
    VALUES('Tim','ChengDu',21,4000);
    INSERT INTO T_Person(FName,FCity,FAge,FSalary)
    VALUES('Jim','BeiJing',22,3500);
    INSERT INTO T_Person(FName,FCity,FAge,FSalary)
    VALUES('Lily','London',21,2000);
    INSERT INTO T_Person(FName,FCity,FAge,FSalary)
    VALUES('John','NewYork',22,1000);
    INSERT INTO T_Person(FName,FCity,FAge,FSalary)
    VALUES('YaoMing','BeiJing',20,3000);
    INSERT INTO T_Person(FName,FCity,FAge,FSalary)
    VALUES('Swing','London',22,2000);
    INSERT INTO T_Person(FName,FCity,FAge,FSalary)
    VALUES('Guo','NewYork',20,2800);
    INSERT INTO T_Person(FName,FCity,FAge,FSalary)
    VALUES('YuQian','BeiJing',24,8000);
    INSERT INTO T_Person(FName,FCity,FAge,FSalary)
    VALUES('Ketty','London',25,8500);
    INSERT INTO T_Person(FName,FCity,FAge,FSalary)
    VALUES('Kitty','ChengDu',25,3000);
    INSERT INTO T_Person(FName,FCity,FAge,FSalary)
    VALUES('Merry','BeiJing',23,3500);
    INSERT INTO T_Person(FName,FCity,FAge,FSalary)
    VALUES('Smith','ChengDu',30,3000);
    INSERT INTO T_Person(FName,FCity,FAge,FSalary)
    VALUES('Bill','BeiJing',25,2000);
    INSERT INTO T_Person(FName,FCity,FAge,FSalary)
    VALUES('Jerry','NewYork',24,3300);

    复制代码

    select * from t_person:

    要计算所有人员的总数,我们可以执行下面的 SQL 语句:SELECT COUNT(*) FROM T_Person

    除了这种较简单的使用方式,有时需要从不在聚合函数中的行中访问这些聚合计算的值。比如我们想查询每个工资小于 5000 元的员工信息(城市以及年龄),并且在每行中都显示所有工资小于 5000 元的员工个数:

    复制代码

    select fname,
           fcity,
           fsalary,
           (select count(*) from t_person where fsalary < 5000) 工资少于5000员工总数
      from t_person
     where fsalary < 5000

    复制代码

     

    虽然使用子查询能够解决这个问题,但是子查询的使用非常麻烦,使用开窗函数则可以大大简化实现,下面的 SQL 语句展示了如果使用开窗函数来实现同样的效果:

     

    select fname, fcity, fsalary, count(*) over() 工资小于5000员工数
      from t_person
     where fsalary < 5000

    可以看到与聚合函数不同的是,开窗函数在聚合函数后增加了一个 OVER 关键字。

    开窗函数格式: 函数名(列) OVER(选项)

    OVER 关键字表示把函数当成开窗函数而不是聚合函数。SQL 标准允许将所有聚合函数用做开窗函数,使用 OVER 关键字来区分这两种用法。
    在上边的例子中,开窗函数 COUNT(*) OVER()对于查询结果的每一行都返回所有符合条件的行的条数。OVER 关键字后的括号中还经常添加选项用以改变进行聚合运算的窗口范围。如果 OVER 关键字后的括号中的选项为空,则开窗函数会对结果集中的所有行进行聚合运算。

    PARTITION BY 子句:

    开窗函数的 OVER 关键字后括号中的可以使用 PARTITION BY 子句来定义行的分区来供进行聚合计算。与 GROUP BY 子句不同,PARTITION BY 子句创建的分区是独
    立于结果集的,创建的分区只是供进行聚合计算的,而且不同的开窗函数所创建的分区也不互相影响。下面的 SQL 语句用于显示每一个人员的信息以及所属城市的人员数:

    select fname,fcity,fage,fsalary,count(*) over(partition by fcity) 所在城市人数 from t_person

    COUNT(*) OVER(PARTITION BY FCITY)表示对结果集按照FCITY进行分区,并且计算当前行所属的组的聚合计算结果。比如对于FName等于 Tom的行,它所属的城市是BeiJing,同
    属于BeiJing的人员一共有6个,所以对于这一列的显示结果为6。
     

    这就不需要先对fcity分组求和,然后再和t_person表连接查询了,省事儿。

    在同一个SELECT语句中可以同时使用多个开窗函数,而且这些开窗函数并不会相互干
    扰。比如下面的SQL语句用于显示每一个人员的信息、所属城市的人员数以及同龄人的人数:

    复制代码

    --显示每一个人员的信息、所属城市的人员数以及同龄人的人数:
    select fname,
           fcity,
           fage,
           fsalary,
           count(*) over(partition by fcity) 所属城市的人个数,
           count(*) over(partition by fage) 同龄人个数
      from t_person

     

     ORDER BY子句:

    开窗函数中可以在OVER关键字后的选项中使用ORDER BY子句来指定排序规则,而且有的开窗函数还要求必须指定排序规则。使用ORDER BY子句可以对结果集按
    照指定的排序规则进行排序,并且在一个指定的范围内进行聚合运算。ORDER BY子句的语法为:

    ORDER BY 字段名 RANGE|ROWS BETWEEN 边界规则1 AND 边界规则2

    RANGE表示按照值的范围进行范围的定义,而ROWS表示按照行的范围进行范围的定义;边界规则的可取值见下表:

    “RANGE|ROWS BETWEEN 边界规则1 AND 边界规则2”部分用来定位聚合计算范围,这个子句又被称为定位框架。

    例子程序一:查询从第一行到当前行的工资总和:

    复制代码

    select fname,
           fcity,
           fage,
           fsalary,
           sum(fsalary) over(order by fsalary rows between unbounded preceding and current row) 到当前行工资求和
      from t_person

    复制代码

    这里的开窗函数“SUM(FSalary) OVER(ORDER BY FSalary ROWS BETWEEN
    UNBOUNDED PRECEDING AND CURRENT ROW)”表示按照FSalary进行排序,然后计算从第
    一行(UNBOUNDED PRECEDING)到当前行(CURRENT ROW)的和,这样的计算结果就是按照
    工资进行排序的工资值的累积和。

    “RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW”是开窗函数中最常使用的定位框架,为了简化使用,如果使用的是这种定位框架,则可以省略定位框架声明部分,
    也就是说上边的sql可以简化成:

    复制代码

    select fname,
           fcity,
           fage,
           fsalary,
           sum(fsalary) over(order by fsalary) 到当前行工资求和
      from t_person

    复制代码

    例子程序二:把例子程序一的row换成了range,是按照范围进行定位的

    复制代码

    select fname,
           fcity,
           fage,
           fsalary,
           sum(fsalary) over(order by fsalary range between unbounded preceding and current row) 到当前行工资求和
      from t_person

    复制代码

     

    区别:

    复制代码

    这个SQL语句与例1中的SQL语句唯一不同的就是“ROWS”被替换成了“RANGE”。“ROWS”
    是按照行数进行范围定位的,而“RANGE”则是按照值范围进行定位的,这两个不同的定位方式
    主要用来处理并列排序的情况。比如 Lily、Swing、Bill这三个人的工资都是2000元,如果按照
    “ROWS”进行范围定位,则计算从第一条到当前行的累积和,而如果 如果按照 “RANGE”进行
    范围定位,则仍然计算从第一条到当前行的累积和,不过由于等于2000元的工资有三个人,所
    以计算的累积和为从第一条到2000元工资的人员结,所以对 Lily、Swing、Bill这三个人进行开
    窗函数聚合计算的时候得到的都是7000( “ 1000+2000+2000+2000 ”)。

    复制代码

     下边这的估计不常用:

    例子程序三:

    SELECT FName,
           FSalary,
           SUM(FSalary) OVER(ORDER BY FSalary ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING)  前二后二和
      FROM T_Person;

    复制代码

    这里的开窗函数“SUM(FSalary) OVER(ORDER BY FSalary ROWS BETWEEN 2
    PRECEDING AND 2 FOLLOWING)”表示按照FSalary进行排序,然后计算从当前行前两行(2
    PRECEDING)到当前行后两行(2 FOLLOWING)的工资和,注意对于第一条和第二条而言它们
    的“前两行”是不存在或者不完整的,因此计算的时候也是要按照前两行是不存在或者不完整进
    行计算,同样对于最后两行数据而言它们的“后两行”也不存在或者不完整的,同样要进行类似
    的处理。

    复制代码

    例子程序四:

    SELECT FName, FSalary,
    SUM(FSalary) OVER(ORDER BY FSalary ROWS BETWEEN 1 FOLLOWING AND 3 FOLLOWING) 后面一到三之和
    FROM T_Person;

    这里的开窗函数“SUM(FSalary) OVER(ORDER BY FSalary ROWS BETWEEN 1
    FOLLOWING AND 3 FOLLOWING)”表示按照FSalary进行排序,然后计算从当前行后一行(1
    FOLLOWING)到后三行(3 FOLLOWING)的工资和。注意最后一行没有后续行,其计算结果为
    空值NULL而非0。

    例子程序五:算工资排名

    SELECT FName, FSalary,
    COUNT(*) OVER(ORDER BY FSalary ROWS BETWEEN UNBOUNDED PRECEDING AND
    CURRENT ROW)
    FROM T_Person;

    这里的开窗函数“COUNT(*) OVER(ORDER BY FSalary RANGE BETWEEN UNBOUNDED
    PRECEDING AND CURRENT ROW)”表示按照FSalary进行排序,然后计算从第一行
    (UNBOUNDED PRECEDING)到当前行(CURRENT ROW)的人员的个数,这个可以看作是计算
    人员的工资水平排名。

    不再用ROWNUM 了  省事了。这个over简写就会出错。

    例子程序6:结合max求到目前行的最大值

    SELECT FName, FSalary,FAge,
    MAX(FSalary) OVER(ORDER BY FAge) 此行之前最大值
    FROM T_Person;

    这里的开窗函数“MAX(FSalary) OVER(ORDER BY FAge)”是“MAX(FSalary)
    OVER(ORDER BY FAge RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)”
    的简化写法,它表示按照FSalary进行排序,然后计算从第一行(UNBOUNDED PRECEDING)
    到当前行(CURRENT ROW)的人员的最大工资值。

     例子程序6:over(partition by XX  order by XX)  partition by和order by 结合

    员工信息+同龄人最高工资,按工资排序

    SELECT FName, FSalary,FAge,
    MAX(FSalary) OVER(PARTITION BY FAge order by fsalary) 同龄人最高工资
    FROM T_Person;

     

    PARTITION BY子句和ORDER BY 可以 共 同 使用,从 而 可以 实现 更 加复 杂 的 功能
    ==================================================================================

    高级开窗函数/ 排名的实现ROW_NUMBER();rank() ,dense_rank()

    除了可以在开窗函数中使用COUNT()、SUM()、MIN()、MAX()、AVG()等这些聚合函数,
    还可以在开窗函数中使用一些高级的函数,有些函数同时被DB2和Oracle同时支持,比如
    RANK()、DENSE_RANK()、ROW_NUMBER(),而有些函数只被Oracle支持,比如
    RATIO_TO_REPORT()、NTILE()、LEAD()、LAG()、FIRST_VALUE()、LAST_VALUE()。
    下面对这几个函数进行详细介绍。

    RANK()和DENSE_RANK()函数都可以用于计算一行的排名,不过对于并列排名的处理方式
    不同;ROW_NUMBER()函数计算一行在结果集中的行号,同样可以将其当成排名函数。这三个
    函数的功能存在一定的差异,举例如下:工资从高到低排名:

    SELECT FName, FSalary,FAge,
    RANK() OVER(ORDER BY fsalary desc) f_RANK,
    DENSE_RANK() OVER(ORDER BY fsalary desc) f_DENSE_RANK,
    ROW_NUMBER() OVER(ORDER BY fsalary desc) f_ROW_NUMBER
    FROM T_Person;

    rank(),dense_rank()语法:

    复制代码

    RANK()
    dense_rank()
    【语法】RANK ( ) OVER ( [query_partition_clause] order_by_clause )
        dense_RANK ( ) OVER ( [query_partition_clause] order_by_clause )
    
    【功能】聚合函数RANK 和 dense_rank 主要的功能是计算一组数值中的排序值。
    【参数】dense_rank与rank()用法相当,
    【区别】dence_rank在并列关系是,相关等级不会跳过。rank则跳过
    rank()是跳跃排序,有两个第二名时接下来就是第四名(同样是在各个分组内) 
    dense_rank()l是连续排序,有两个第二名时仍然跟着第三名。

    复制代码

    row_number() 函数语法:

    复制代码

    ROW_NUMBER()
    【语法】ROW_NUMBER() OVER (PARTITION BY COL1 ORDER BY COL2) 
    【功能】表示根据COL1分组,在分组内部根据 COL2排序,而这个值就表示每组内部排序后的顺序编号(组内连续的唯一的) 
    row_number() 返回的主要是“行”的信息,并没有排名
    【参数】
    【说明】Oracle分析函数
    
    主要功能:用于取前几名,或者最后几名等

    复制代码

    ===================================================================

    排序函数实际场景使用:计算排行榜,排名

    微信活动,每天参与,有得分,活动结束后选出排名靠前的发奖。

    每参与一次,就是一个订单,表结构:

     

    比如要查询期号issue为20170410期的排行榜,按得分倒叙排序,得分一样按订单创建先后,算排行,sql需要这么写:

    select ROWNUM rank, t.*
      from (select *
              from t_zhcw_order
             where issue = '20170410'
             order by integral desc, create_date asc) t

     

    使用了开窗函数后就可以简化:

    select t.*,
                   row_number() over(order by t.integral desc, t.create_date asc) 排名
              from t_zhcw_order t
             where issue = '20170410'

     

     如果想只要排名范围,可以在外边再包一层,这也是高效分页的一种方式:

    复制代码

    select tt.*  from (
    select t.id,
           t.integral,
           t.cell,
           t.create_date,
           row_number() over(order by t.integral desc, t.create_date asc) rankNum
      from t_zhcw_order t
     where t.issue = 20170331
    )tt where tt.rankNum<=50

    复制代码

     

     


     

     

    展开全文
  • ORACLE数据库(六)-----开窗函数窗函数又名分析函数、窗口函数、OLAP(数据分析)函数聚合函数:将数据按照一定的规则分组,统一分析各组的某项情况,每个分组返回一行结果开窗函数:将数据按照一定的规则分组,统一...

    ORACLE数据库(六)-----开窗函数

    开窗函数又名分析函数、窗口函数、OLAP(数据分析)函数

    聚合函数:将数据按照一定的规则分组,统一分析各组的某项情况,每个分组返回一行结果

    开窗函数:将数据按照一定的规则分组,统一分析各组的某项情况,每行数据返回一行结果

    一、开窗函数语法

    分析函数名() over(分析子句)

    over作为开窗函数的标志

    二、分析子句

    分组(PARTITION BY) 排序(ORDER BY) 窗口(ROWS)

    三、窗口条件

    PRECEDING:之前的

    FOLLOWING:之后的

    CURRENT:当前的

    UNBOUNDING:不受限的

    ROW: 行

    ROWS BETWEEN N PRECEDING AND N FOLLOWING -- 前面N行---- 后面N行

    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW -- 第一行 - 当前行

    ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING -- 当前行 - 最后一行

    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING -- 第一行 - 最后一行

    三、分析函数

    1 聚合类

    SUM()、AVG() 、MAX()、 MIN()、 COUNT() 功能和聚合函数一样

    1 PARTITION BY 默认窗口的作用范围是从每个组的第一行到最后一行

    2 ORDER BY 默认窗口作用范围是从第一条到当前条

    3 ORDER BY 分组内部排序,尽可能的按照顺序排,但是结果不一定完全按照内部排序结果

    用SUM()举例

    SUM(列名) OVER([PARTITION] [ORDER] [ROWS])

    --查询每天的营业额及周每日累计营业额

    SELECT DATA_DT,AMT,SUM(AMT)OVER(PARTITION BY WEEK ORDER BY DATA_DT ASC) FROM BUSINESS; -- 各组内的第一条到当前条

    cfa693409d9250e01fb81a4b4af2ae99.png

    注意点

    1)分析函数名内必须包含需要分析的内容

    2)分析子句没有硬性要求 --- 出现ROWS时必须伴随ORDER BY

    3)采用默认窗口范围时,下一个N相同值(用来排序的值相同)会被一并算入

    2 排序类

    ROW_NUMBER:对于排序值相同的行 排名不并列也不跳跃

    RANK:对排序值相同的行 排名并列并且跳跃

    DENSE_RANK:对排序值相同的行 并列不跳跃

    用法

    ROW_NUMBER()OVER([PARTITION BY ] ORDER BY)

    查询每天的营业额并在整月范围内升序排列

    SELECT DATA_DT,AMT,ROW_NUMBER()OVER(ORDER BY AMT) RN FROM BUSINESS; -- 不并列 不跳跃

    SELECT DATA_DT,AMT,RANK()OVER(ORDER BY AMT) RN FROM BUSINESS; -- 并列跳跃

    SELECT DATA_DT,AMT,DENSE_RANK()OVER(ORDER BY AMT) RN FROM BUSINESS; -- 并列不跳跃

    11c517ea1480aebbeec3646f8427d4ce.png

    735a42dae59813642386ef177aadf3e5.png

    c11b38a63426fee92dd64a36301465c5.png

    ROW_NUMBER 可以用来去除重复的行 完全重复和不完全重复(一般多条数据中除了日期不同其他均相同的行)

    排序类开窗函数注意点:

    1)分子函数名内不能包含任何内容

    2)分析子句内必须添加ORDER BY,且不能指定窗口

    3 偏移类

    LAG() : 向前偏移

    LEAD(): 向后偏移

    用法

    用法(以LAG为例):LAG(COL_NAME,[OFFSET],[DEFVAL])OVER():向前偏移N行取数

    COL_NAME:要分析的字段

    OFFSET:偏移量 --默认偏移一行

    DEFVAL:默认返回值 --默认返回空null 返回的类型要与分析字段的类型一样

    --查询每天的营业额以及前一天的营业额

    SELECT DATA_DT,AMT,

    LAG(AMT,1,0)OVER(ORDER BY DATA_DT)

    FROM BUSINESS;

    abd8d19ab0bad411375e764b06197999.png

    偏移类开窗函数注意点:

    1)分析函数名内必须包含要分析的内容,其他两项参数可以默认

    2)分析子句内必须添加ORDER BY,且不能指定窗口

    3)若不再有可供偏移的行,则返回默认值

    4)偏移量不允许写负数

    5)默认返回值数据类型与分析的字段要保持一致

    四、总结

    1 窗口子句不能单独出现,必须要有排序子句出现的情况下才能指定窗口范围

    2 若出现排序子句(ORDER BY),同时未指定窗口范围,默认的窗口范围是第一行到当前行;若未出现排序子句,同时未指定窗口范围,默认的窗口范围是第一行到最后一行

    展开全文
  • oracle窗函数学习技巧总结,主要讲解over函数的使用的哦
  • 1.开窗函数over partition开窗函数使用开窗函数使用于取出多列分组,取一列分组下另一组前几名,先利用开窗函数对其分组排名,开窗函数排名函数较多使用row_number(),还有rank()等,生成排名列之后将结果集筛选其...
  • 介绍Oracle的开窗函数之前先介绍一下分析函数,因为开窗函数也属于分析函数 分析函数用于计算基于组的某种聚合值,它和聚合函数的不同之处是:对于每个组返回多行,而聚合函数对于每个组只返回一行。 上面是开...
  • ORACLE窗函数

    千次阅读 2018-07-26 14:02:00
    语法:row_number() OVER(PARTITION BY ...) 分组取最大的objid那一条,根据objid倒叙排序,取rn=1 select * from (select a.*, row_number() over(partiotion by col1,col2 order by col1,col2) r ...分析函数和...
  • 分组函数用于求取分组后的某个值,有时候这种查询并不能满足我们的需求,这就有了开窗函数,开窗函数也能对数据进行分组。开窗函数的作用是计算基于组的某种聚合值。开窗函数指定了分析函数工作的数据窗口大学,这个...
  • 首先想到的是使用开窗函数, 但这是mysql, mysql 8.x版本才支持开窗函数, 服务器上装的是5.x 用不了啊, 于是一找了度娘, 如下sql语句和自己的理解(注释) exp: 统计一年级各班前三名 SELECT * FROM T_SCORE A ...
  • oracle的分析函数over 及开窗函数 一:分析函数over Oracle从8.1.6开始提供分析函数,分析函数用于计算基于组的某种聚合值,它和聚合函数的不同之处是 对于每个组返回多行,而聚合函数对于每个组只返回一行。  ...
  • 一、开窗函数与聚合函数的不同之处在于: 聚合函数每个组返回一行,而开窗函数返回多行。所有有些时候使用开窗函数时需要去重。 例如: select t.deptno,sum(t.sal) sal from linshi_ljq t group by t.deptno; 结果...
  • oracle窗函数 Over

    2009-06-04 17:20:38
    oracle窗函数 OVER row_number over(partition by c1 order by c2) 对一组数据排序显示列号 聚合函数与over()开窗函数的使用: sum(c1) over(par...
  • oracle的分析函数over 及开窗函数
  • 聚合类开窗函数 聚合类开窗函数类似分组函数group by中的sum()、avg()、count()、max()、min()等等。但是开窗函数不会像分组聚合函数一样按照分组返回结果,而是有多少行记录就返回多少个结果,结果输出的形式是...
  • Oracle—开窗函数

    千次阅读 2019-04-18 11:16:00
    开发工具与关键技术:工具:...分组函数用于求取分组后的某个值,有时候这种查询并不能满足我们的需求,这就有了开窗函数,开窗函数也能对数据进行分组。   开窗函数的作用是计算基于组的某种聚合值。开窗函数...
  • oracle窗函数案例详解

    千次阅读 2019-04-18 10:21:58
    order by子句会让输入的数据强制排序(文章前面提到过,窗口函数是SQL语句最后执行的函数,因此可以把SQL结果集想象成输入数据)。 Order By子句对于诸如Row_Number(),Lead(),LAG()等函数是必须的,因为如果数据...
  • 文章目录ORACLE分析函数结合开窗函数分析函数结合开窗函数使用示例准备测试数据分析函数的朋友,开窗函数over()分析函数之评级函数示例:分析函数之报表函数LAG()、LEAD() 分析函数用于计算基于组的某种聚合值,它...
  • oracle窗函数over()

    2017-07-11 22:41:56
    oracle从8.1.6开始提供分析函数,分析函数用户计算基于组的聚合值,但是与聚合函数不同的是,分析函数可以返回基于组的多行值,而分析函数只能基于group by条件返回一行值。 2.再说应用场景 JRN_NO 流水号 ...
  • oracle分析函数及开窗函数的使用,包括over等分析函数
  • 一、rank() over(partition by 字段1 order by 字段2) 从最经典的一个例子来说: ...普通分组函数(group by按某一个条件分组,必须要使用聚合函数,每组返回一个结果) 这就需要使用rank() over(partition by ...
  • 介绍Oracle的开窗函数之前先介绍一下分析函数,因为开窗函数也属于分析函数 分析函数用于计算基于组的某种聚合值,它和聚合函数的不同之处是:对于每个组返回多行,而聚合函数对于每个组只返回一行。 上面是开...
  • oracle分析函数 分析函数是什么? 分析函数是Oracle专门用于解决复杂报表统计需求的功能强大的函数,它可以在数据中进行分组然后计算基于组的某种统计值,并且每一组的每一行都可以返回一个...分析函数-开窗函数的...
  • 目前在 MSSQLServer、Oracle、DB2 等主流数据库中都提供了对开窗函数的支持,不过非常遗憾的是 MYSQL 暂时还未对开窗函数给予支持。 开窗函数简介:与聚合函数一样,开窗函数也是对行集组进行聚合计算,但是它不像...
  • oracle分析函数,oracle分析函数over_及开窗函数用法。
  • 首先针对开窗函数做一下简单介绍: 开窗函数   Oracle从8.1.6开始提供分析函数,分析函数用于计算基于组的某种聚合值,它和聚合函数的不同之处是:对于每个组返回多行,而聚合函数对于每个组只返回一行。  开...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 26,896
精华内容 10,758
关键字:

oracle分窗函数