精华内容
下载资源
问答
  • Oracle分析函数

    2019-02-15 18:07:40
    oracle分析函数精品文档,适合初学者,逐步深入学习掌握oracle分析函数
  • ORACLE分析函数大全

    2013-06-14 14:27:02
    文档详细介绍了oracle分析函数,包括功能说明、sql示例等。分析函数功能强大,在报表或数据迁移的时候可能会使用到。分析函数用法看上去有点复杂,最好使用的时候,参考文档
  • Oracle分析函数.pdf

    2020-05-30 13:01:09
    Oracle分析函数.pdf,这份资料详细介绍了Oracle分析函数的使用,Oracle分析函数.pdf是一份不错的文档
  • ORACLE分析函数

    千次阅读 2018-11-14 18:14:38
    1,rollup函数 select class 班级, subject 科目, sex 性别,sum(score) 总分数 from sailing_class group by rollup(class,subject,sex) order by class,subject,sex; 解释: sum(汇总字段) group by rollup...

    1,rollup函数

    select class 班级, subject 科目, sex 性别,sum(score) 总分数  
    from sailing_class  
    group by rollup(class,subject,sex) 
    order by class,subject,sex;
    

    解释:
    sum(汇总字段) group by rollup(字段A,字段B,字段C)

    该函数会先对(A,B,C)三个字段分组,再对(A,B)两个字段分组,再对(A)字段进行分组,之后对整个表分组。
    注:会严格按照三个字段的顺序分组,以哪个字段为主,则必须把这个字段放在首位。
    ROLLUP

    2,cube函数

    select class 班级, subject 科目, sex 性别,sum(score) 总分数  
    from sailing_class  
    group by cube(class,subject,sex) 
    order by class,subject,sex;
    

    解释:
    sum(汇总字段) group by cube(字段A,字段B,字段C)

    该函数的分组顺序如下

    1. (A,B,C)
    2. (A,B)
    3. (A,C)
    4. (A)
    5. (B,C)
    6. (B)
    7. (C)
    8. 全表
      CUBE

    3,grouping函数

    select 	
            class 实际班级,
            grouping(class),
            decode(grouping(class),1,'所有班级',class) 班级,  
            subject 实际科目,
            grouping(subject),
            decode(grouping(subject),1,'全部科目',subject) 科目, 
            sex 实际性别,
            grouping(sex),
            decode(grouping(sex),1,'全部性别',sex) 性别, 
            sum(score) 总分数  
    from sailing_class  
    group by cube(class,subject,sex)  
    order by class,subject nulls last;
    

    解释:
    grouping函数用来区分NULL值,这里NULL值有2种情况,一是原本表中的数据就为NULL,二是由rollup、cube、grouping sets生成的NULL值。
    当为第一种情况中的空值时,grouping(NULL)返回0;当为第二种情况中的空值时,grouping(NULL)返回1。
    可据图对比。
    GROUPING

    4,排序(1)—rank()

    select class 班级,subject 科目,stu_name 学生姓名,sex 性别,score 分数,   
           rank() over (partition by class,subject order by score desc) 排名  
    from sailing_class;
    

    解释:
    数据有重复,序号重复并别相同,后续序号中断。
    rank()

    5,排序(2)—dense_rank()

    select class 班级,subject 科目,stu_name 学生姓名,sex 性别,score 分数,   
           dense_rank() over (partition by class,subject order by score desc) 排名  
    from sailing_class;
    

    解释:
    数据有重复,序号重复并列相同,后续序号不中断。
    dense_rank()

    6,排序(3)—row_number()

    select class 班级,subject 科目,stu_name 学生姓名,sex 性别,score 分数,   
           row_number() over (partition by class,subject order by score desc) 排名  
    from sailing_class;
    

    解释:
    数据有重复,序号不重复,后续序号始终递增
    row_number()


    使用的数据:

    -- create table sailing_class -- 考试成绩表 
    -- (  
    --   class varchar2(8), -- 班级  
    --   subject varchar2(20), -- 科目  
    --   stu_no varchar2(10), -- 学生编号  
    --   stu_name varchar2(20), -- 学生姓名  
    -- 	sex number(1),--性别 
    --   score number(10,2) -- 考试分数  
    -- )
    
    -- insert into sailing_class values('2017_01','高等数学','100122','李贺',1,78.6);
    -- insert into sailing_class values('2017_01','高等数学','100123','刘睿嫣',2,95.1);
    -- insert into sailing_class values('2017_01','高等数学','100124','赵宗望',1,92);
    -- insert into sailing_class values('2017_01','高等数学','100125','王乐',1,52);
    -- insert into sailing_class values('2017_01','高等数学','100126','张启慧',2,68);
    -- insert into sailing_class values('2017_01','高等数学','100127','袁牧',1,92);
    -- 
    -- insert into sailing_class values('2017_01','英语','100122','李贺',1,88.6);
    -- insert into sailing_class values('2017_01','英语','100123','刘睿嫣',2,75.1);
    -- insert into sailing_class values('2017_01','英语','100124','赵宗望',1,58);
    -- insert into sailing_class values('2017_01','英语','100125','王乐',1,82);
    -- insert into sailing_class values('2017_01','英语','100126','张启慧',2,78);
    -- insert into sailing_class values('2017_01','英语','100127','袁牧',1,80.4);
    -- 
    -- insert into sailing_class values('2017_02','高等数学','100242','卿泽明',1,98.6);
    -- insert into sailing_class values('2017_02','高等数学','100243','史明利',1,75.1);
    -- insert into sailing_class values('2017_02','高等数学','100244','赵卿焰',2,92);
    -- insert into sailing_class values('2017_02','高等数学','100245','演绎木',1,62);
    -- insert into sailing_class values('2017_02','高等数学','100246','卿木燕',2,78);
    -- insert into sailing_class values('2017_02','高等数学','100247','成亚瑟',1,54);
    -- 
    -- insert into sailing_class values('2017_02','英语','100242','卿泽明',1,78.6);
    -- insert into sailing_class values('2017_02','英语','100243','史明利',1,85.1);
    -- insert into sailing_class values('2017_02','英语','100244','赵卿焰',2,88);
    -- insert into sailing_class values('2017_02','英语','100245','演绎木',1,69);
    -- insert into sailing_class values('2017_02','英语','100246','卿木燕',2,88);
    -- insert into sailing_class values('2017_02','英语','100247','成亚瑟',1,56);
    
    展开全文
  • oracle 分析函数全面解析,用于报表统计等方面的高级查询。
  • Oracle 分析函数

    2019-03-17 01:05:47
    NULL 博文链接:https://mydicta.iteye.com/blog/460158
  • oracle分析函数(用法+实例),这属于oracle的高级应用。
  • oracle 分析函数

    2012-04-06 00:31:04
    oracle 分析函数 开发必备 数据库开发工程师
  • oracle分析函数

    2018-04-11 22:17:54
    ①查找上一年度各个销售区域排名前10的员工 ②按区域查找上一年度订单总额占区域订单总额20%以上的客户 ③查找上一年度销售最差的部门所在的区域 ④查找上一年度销售最好和最差的产品
  • oracle分析函数-计算移动平均、累计、分布
  • 【Oracle】Oracle分析函数详解

    千次阅读 2019-05-25 22:45:09
    本篇将详细介绍Oracle数据库中的分析函数。 一 分析函数概述 所谓分析函数,是基于一组数据行计算聚合值,其与聚合函数的不同之处在于,它为每一组返回多个数据行。一组数据行称为一个窗口,由analytic_clause子句...

    Oracle数据库中的函数有多种,比如单行函数、聚合函数、对象引用函数、模型函数、OLAP函数等。本篇将详细介绍Oracle数据库中的分析函数。

    一 分析函数概述

    所谓分析函数,是基于一组数据行计算聚合值,其与聚合函数的不同之处在于,它为每一组返回多个数据行。一组数据行称为一个窗口,由analytic_clause子句进行定义,对于每一行,定义一个行移动窗口,窗口确定用于为当前行执行计算的行的范围,它的大小可以基于物理行或逻辑间隔(如时间)。

    二 分析函数语法

    说明:

    analytic_function

    指出分析函数的名称;

    arguments

    分析函数的参数,参数数量在0个到3个之间,该参数类型可以是任何数值数据类型,或者任何可以隐式转换为数值数据类型的非数值类型。

    analytic_clause

    使用OVER analytic_clause子句展示在查询结果集上所进行的函数操作,该子句是在FROM、WHERE、GROUP BY和HAVING子句后进行计算的。

     使用PARTITION BY子句可以基于一个或多个expr将查询结果集划分为多个组,如果忽略该子句,则函数会将整个查询结果集当做一个组。

    order_by_clause子句用于指定数据是在一个分组里是如何进行排序的。

    三 分析函数类型

    Oracle有以下类型的分析函数,其中,带有*号的函数可以使用上面的完全语句,包括窗口子句。

    • AVG *
    • COPR *
    • COUNT *
    • COVAR_POP *
    • COVAR_SAMP *
    • CUME_DIST
    • DENSE_RANK
    • FIRST
    • FIRST_VALUE *
    • LAG
    • LAST
    • LAST_VALUE *
    • LEAD
    • LISTAGG
    • MAX *
    • MEDIAN
    • MIN *
    • NTH_VALUE *
    • NTILE
    • PERCENT_RANK
    • PERCENTILE_COUNT
    • PERCENTILE_DISC
    • RANK
    • RATIO_TO_REPORT
    • REGR_(Linear Regression) Functions *
    • ROW_NUMBER
    • STDDEV *
    • STDDEV_POP *
    • STDDEV_SAMP *
    • SUM *
    • VAR_POP *
    • VAR_SAMP *
    • VARIANCE *

    四 分析函数详解

    1 演示数据库版本

    SQL> select * from v$version;
    BANNER
    --------------------------------------------------------------------------------
    Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
    PL/SQL Release 11.2.0.4.0 - Production
    CORE 11.2.0.4.0 Production
    TNS for Linux: Version 11.2.0.4.0 - Production
    NLSRTL Version 11.2.0.4.0 - Production

    2 AVG函数

    1)语法结构

    AVG函数用于返回expr的平均值,如果指定DISTINCT,那么只能指定analytic_clause中的query_partition_clause子句,不能指定order_by_clause andwindowing_clause子句。

    2)示例

    SQL> SELECT empno,
    ename,
    job,
    mgr,
    hiredate,
    deptno,
    sal,
    round(AVG(sal) over(), 2) avg_all_sal,
    round(AVG(sal) over(PARTITION BY deptno), 2) avg_dept_sal,
    round(AVG(sal) over(PARTITION BY deptno ORDER BY empno
    rows BETWEEN 1 preceding AND 1 following),2) avg_sal1,
    round(AVG(sal) over(PARTITION BY deptno ORDER BY empno
    rows BETWEEN CURRENT ROW AND 1 following),2) avg_sal2
    FROM emp;

     3 COUNT

    1)语法结构

    COUNT返回查询的行数,如果指定DISTINCT,那么只能指定analytic_clause中的query_partition_clause子句,不能指定order_by_clause andwindowing_clause子句。如果指定expr,COUNT返回expr非空的行数,如果指定*,则返回所有行数,包括重复行和空行。

    2)示例

    SQL> SELECT empno,
    ename,
    job,
    mgr,
    hiredate,
    deptno,
    sal,
    comm,
    COUNT(1) over() qty1,
    COUNT(*) over() qty2,
    COUNT(comm) over() qty3,
    COUNT(DISTINCT deptno) over() qty4,
    COUNT(1) over(PARTITION BY deptno) qty5
    FROM emp;

    4 RANK、DENSE_RANK、ROW_NUMBER

    1)语法结构

    2)示例

    SQL> SELECT empno,
    ename,
    job,
    mgr,
    hiredate,
    sal,
    deptno,
    --row_number() over(ORDER BY sal) rn1,
    row_number() over(PARTITION BY deptno ORDER BY sal) rn2,
    --rank()over(order by sal) rn3
    rank() over(PARTITION BY deptno ORDER BY sal) r4,
    dense_rank() over(PARTITION BY deptno ORDER BY sal) r5
    FROM emp;

    注:这三个函数主要用于进行排序,ROW_NUMBER函数排序的结果是连续的,而DENSE函数排序会出现跳号,DENSE_RANK函数排序,不会出现跳号。

    5 FIRST_VALUE

    1)语法结构

    FIRST_VALUE用于返回在一个排序结果集中的第一个值。

    2)示例

    SQL> SELECT empno,
    ename,
    job,
    mgr,
    hiredate,
    sal,
    deptno,
    first_value(sal) over(PARTITION BY deptno ORDER BY sal) sal,
    first_value(sal) over(PARTITION BY deptno ORDER BY sal DESC) sal2
    FROM emp;

    6 LAST_VALUE

    1)语法结构

    对于该函数,如果忽略了analytic_clause子句中的windowing_clause子句,默认是使用RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ,使用默认值有时可能返回不是预期的结果,因为LAST VALUE是窗口中的最后一个值,它不是固定的,而是随着当前行的改变而改变。为了得到与其的结果,可以使用RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING窗口,也可以使用RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING窗口。

    2)示例

    SQL> SELECT empno,
    ename,
    job,
    mgr,
    hiredate,
    sal,
    deptno,
    last_value(sal) over(PARTITION BY deptno ORDER BY sal) sal1,
    last_value(sal) over(PARTITION BY deptno ORDER BY sal RANGE BETWEEN unbounded preceding AND CURRENT ROW) sal2,
    last_value(sal) over(PARTITION BY deptno ORDER BY sal RANGE BETWEEN unbounded preceding AND unbounded following) sal3,
    last_value(sal) over(PARTITION BY deptno ORDER BY sal RANGE BETWEEN CURRENT ROW AND unbounded following) sal4,
    last_value(sal) over(PARTITION BY deptno ORDER BY sal DESC RANGE BETWEEN unbounded preceding AND unbounded following) sal5,
    last_value(sal) over(PARTITION BY deptno ORDER BY sal rows BETWEEN unbounded preceding AND unbounded following) sal6
    FROM emp;

    7 LAG

    1)语法结构

    该函数提供了在不使用自连接的情况下同时访问表的多行的能力,即将数据行根据偏移量靠后,offset参数指定偏移量,默认值为1,default参数指定超过窗口边界的默认值,没有指定,则为NULL。

    2)示例

    SQL> SELECT empno,
    ename,
    job,
    mgr,
    hiredate,
    sal,
    deptno,
    lag(sal) over(PARTITION BY deptno ORDER BY sal) sal1,
    lag(sal, 1) over(PARTITION BY deptno ORDER BY sal) sal2,
    lag(sal, 2, 0) over(PARTITION BY deptno ORDER BY sal) sal3
    FROM emp;

    8 LEAD

    1)语法结构

    该函数和LAG函数功能相反,主要用于将行数据提前。

    2)示例

    SQL> SELECT empno,
    ename,
    job,
    mgr,
    hiredate,
    sal,
    deptno,
    lead(sal) over(PARTITION BY deptno ORDER BY sal) sal1,
    lead(sal, 1) over(PARTITION BY deptno ORDER BY sal) sal2,
    lead(sal, 2, 0) over(PARTITION BY deptno ORDER BY sal) sal3
    FROM emp;

    9 LISTAGG

    1)语法结构

    该函数用于在分组内对数据进行排序,然后将对应的值进行拼接,参数delimilter用于指定拼接符。

    2)示例

    SQL> SELECT empno,
    ename,
    job,
    mgr,
    hiredate,
    sal,
    deptno,
    listagg(ename, ',') within GROUP(ORDER BY empno) over(PARTITION BY deptno) name1
    FROM emp;

    思考:如果拼接的列数据类型为varchar2,则有无长度限制,如果超过了该怎么处理?

    SQL> SELECT deptno,
    dbms_lob.substr(substr(rtrim(xmlcast(xmlagg(xmlelement(e,
    ename || ',')
    ORDER BY empno) AS CLOB),
    ','),
    1,
    100)) NAME
    FROM emp
    GROUP BY deptno;

    10 MAX、MIN、SUM

    1)语法结构

    这三个函数主要用于在分组内求最大值、最小值以及求和。

    2)示例

    SQL> SELECT empno,
    ename,
    job,
    mgr,
    hiredate,
    sal,
    deptno,
    MAX(sal) over() sal1,
    MAX(sal) over(PARTITION BY deptno) sal2,
    MAX(sal) over(PARTITION BY deptno ORDER BY empno rows BETWEEN unbounded preceding AND 1 following) sal3,
    MIN(sal) over() sal4,
    MIN(sal) over(PARTITION BY deptno) sal5,
    MIN(sal) over(PARTITION BY deptno ORDER BY empno rows BETWEEN unbounded preceding AND 1 following) sal6,
    SUM(sal) over() sal7,
    SUM(sal) over(PARTITION BY deptno) sal8,
    SUM(sal) over(PARTITION BY deptno ORDER BY empno rows BETWEEN unbounded preceding AND 1 following) sal9
    FROM emp;

    11 CUME_DIST

    1)语法结构

    该函数用于计算一个值在一组值中的累积分布,返回的值的范围是0到1。计算逻辑为:在一组值中某一个值的相对位置,即对于某一行R,总行数为N,值为R/N。

    2)示例

    SQL> SELECT empno,
    ename,
    job,
    mgr,
    hiredate,
    sal,
    deptno,
    cume_dist() over(ORDER BY sal) p1,
    cume_dist() over(PARTITION BY deptno ORDER BY sal) p2
    FROM emp
    ORDER BY deptno, sal;

    12 PERCENT_RANK

    1)语法结构

    该函数和CUME_DIST函数类似,返回值的范围是0到1,使用该函数的任何集合的第一行是0。计算逻辑为:对于某一行R,总行数为N,值为(R-1)/(N-1)。

    2)示例

    SQL> SELECT empno,
    ename,
    job,
    mgr,
    hiredate,
    sal,
    deptno,
    percent_rank() over(ORDER BY sal) p1,
    percent_rank() over(PARTITION BY deptno ORDER BY sal) p2
    FROM emp
    ORDER BY deptno, sal;

     

    展开全文
  • Oracle分析函数汇总(超全)

    万次阅读 2019-09-13 11:13:28
    前半部分数据源为Oracle自带HR用户下的员工信息表:EMPLOYEES,若创建库的时候未勾选创建示例,可以在这下载sql文件,创建相关表及数据源。(百度网盘链接:https://pan.baidu.com/s/1axlyRjfEGi0pOi8xmRewrA 密码:...

    前半部分数据源为Oracle自带HR用户下的员工信息表:EMPLOYEES,若创建库的时候未勾选创建示例,可以在这下载sql文件,创建相关表及数据源。(百度网盘链接:https://pan.baidu.com/s/1axlyRjfEGi0pOi8xmRewrA   密码:t3yy)

    中间部分的数据源为Oracle自带SCOTT用户下的员工表&部门表:EMP、DEPT

    后半部分属于转载,暂无数据源,但是方便归纳总结:https://blog.csdn.net/cc_0101/article/details/80884076

     

    --1、查询各个部门的平均工资,以及该部门的员工信息

    SELECT 
    	A.MANAGER_ID,A.EMPLOYEE_NAME,A.HIRE_DATE,A.SALARY,B.AVG_SALARY
    FROM
    	(SELECT MANAGER_ID,FIRST_NAME||' '||LAST_NAME EMPLOYEE_NAME, HIRE_DATE, SALARY FROM EMPLOYEES) A,
    	(SELECT MANAGER_ID,AVG(SALARY) AVG_SALARY FROM EMPLOYEES GROUP BY MANAGER_ID) B
    WHERE A.MANAGER_ID=B.MANAGER_ID
    ORDER BY A.MANAGER_ID;
    
    SELECT 
    	MANAGER_ID,FIRST_NAME||' '||LAST_NAME EMPLOYEE_NAME,HIRE_DATE,SALARY,
    	AVG(SALARY) OVER (PARTITION BY MANAGER_ID) AVG_SALARY
    FROM EMPLOYEES;
    

    --2、按照入职时间排序,并计算第一个员工到当前员工的平均工资(如果时间一样则相同时间一起计算)

    SELECT 
    	MANAGER_ID, FIRST_NAME||' '||LAST_NAME EMPLOYEE_NAME, HIRE_DATE, SALARY, 
    	AVG(SALARY) OVER (ORDER BY HIRE_DATE)
    FROM EMPLOYEES;

    --3、按照入职时间排序,且按照部门经理进行分组,并计算该部门第一个员工到当前员工的平均工资(如果时间一样则相同时间一起计算)

    SELECT 
    	MANAGER_ID, FIRST_NAME||' '||LAST_NAME EMPLOYEE_NAME, HIRE_DATE, SALARY, 
    	AVG(SALARY) OVER (PARTITION BY MANAGER_ID ORDER BY HIRE_DATE)
    FROM EMPLOYEES;

    --ROWS表示行
    --4、按照入职时间排序,且按照部门经理进行分组,计算当前员工的前一个到后两个共四个员工的平均工资(如果时间一样,则默认按照先后顺序计算)

    SELECT 
    	MANAGER_ID, FIRST_NAME||' '||LAST_NAME EMPLOYEE_NAME, HIRE_DATE, SALARY, 
    	AVG(SALARY) OVER (PARTITION BY MANAGER_ID ORDER BY HIRE_DATE ROWS BETWEEN 1 PRECEDING AND 2 FOLLOWING)
    FROM EMPLOYEES;

    --RANGE表示取值范围(数字和日期可以取值)
    --5、按照入职时间排序,且按照部门经理分组,计算当前员工雇佣时间之前的50天以内,之后的150天以内之间的平均工资(未验证前50,后150的边界问题)

    SELECT 
    	MANAGER_ID, FIRST_NAME||' '||LAST_NAME EMPLOYEE_NAME, HIRE_DATE, SALARY,
    	AVG(SALARY) OVER (PARTITION BY MANAGER_ID ORDER BY HIRE_DATE RANGE BETWEEN 50 PRECEDING AND 150 FOLLOWING)
    FROM EMPLOYEES;

    /*窗口子句中我们经常用到指定第一行,当前行,最后一行这样的三个属性:
    第一行是 unbounded preceding,
    当前行是 current row,
    最后一行是 unbounded following*/

    --求平均做一个总结,并展示第一个到最后一个的取值方法

    SELECT 
    	MANAGER_ID, FIRST_NAME||' '||LAST_NAME EMPLOYEE_NAME, HIRE_DATE, SALARY,
    	AVG(SALARY) OVER (PARTITION BY MANAGER_ID ORDER BY HIRE_DATE) AVG_SALARY_PART_ORDER,--累计求平均,和第3个一样
    	AVG(SALARY) OVER (PARTITION BY MANAGER_ID ) AVG_SALARY_ORDER,--求整个部门的平均,和第1个一样
    	AVG(SALARY) OVER (PARTITION BY MANAGER_ID ORDER BY HIRE_DATE RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AVG_SALARY_UNBOUND1, --求整个部门的平均,表示取值范围第一个到最后一个,结果和上面一致
    	AVG(SALARY) OVER (PARTITION BY MANAGER_ID ORDER BY HIRE_DATE ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AVG_SALARY_UNBOUND2--求整个部门的平均,行号取值范围第一个到最后一个,结果和上面一致
    FROM EMPLOYEES;
    --按照deptno分组,然后计算当前行至最后一行的汇总
    SELECT 
    	EMPNO,ENAME,DEPTNO,SAL,
    	SUM(SAL) OVER(PARTITION BY DEPTNO ORDER BY ENAME ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) MAX_SAL
    FROM EMP;
    
    
    --按照deptno分组,然后计算当前行的上一行(rownum-1)到当前行的汇总
    SELECT 
    	EMPNO,ENAME,DEPTNO,SAL,
        SUM(SAL) OVER(PARTITION BY DEPTNO ORDER BY ENAME ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) MAX_SAL
    FROM EMP;
    
    
    --按照deptno分组,然后计算当前行的上一行(rownum-1)到当前行的下两行(rownum+2)的汇总
    SELECT 
    	EMPNO,ENAME,DEPTNO,SAL,
        SUM(SAL) OVER(PARTITION BY DEPTNO ORDER BY ENAME ROWS BETWEEN 1 PRECEDING AND 2 FOLLOWING) MAX_SAL
    FROM EMP;
    --注意ROWS BETWEEN 1 preceding AND 1 following 是指当前行的上一行(rownum-1)到当前行的下两行(rownum+2)的汇总

     

    --MIN、MAX、AVG、SUM、COUNT

    SELECT 
    	DEPARTMENT_ID, FIRST_NAME||' '||LAST_NAME EMPLOYEE_NAME, HIRE_DATE, SALARY,
    	MIN(SALARY) OVER (PARTITION BY DEPARTMENT_ID ORDER BY HIRE_DATE) AS DEPT_MIN,--按照部门ID分组,然后按照入职时间排序,计算包括当前员工入职时间及之前的所有员工的最低薪资(如果时间一样,则一起计算)
    	MAX(SALARY) OVER (PARTITION BY DEPARTMENT_ID ORDER BY HIRE_DATE) AS DEPT_MAX,--按照部门ID分组,然后按照入职时间排序,计算包括当前员工入职时间及之前的所有员工的最高薪资(如果时间一样,则一起计算)
    	AVG(SALARY) OVER (PARTITION BY DEPARTMENT_ID ORDER BY HIRE_DATE) AS DEPT_AVG,--按照部门ID分组,然后按照入职时间排序,计算该部门第一个员工到当前员工的平均工资(如果时间一样则相同时间一起计算)
    	SUM(SALARY) OVER (PARTITION BY DEPARTMENT_ID ORDER BY HIRE_DATE) AS DEPT_SUM,--按照部门ID分组,然后按照入职时间排序,计算该部门第一个员工到当前员工的工资和(如果时间一样则相同时间一起计算)
    	COUNT(1) OVER (ORDER BY SALARY) AS COUNT_BY_SALARY,--按照工资进行排序,统计从第一个到当前工资的个数(如果工资一样,则一起统计)
    	COUNT(1) OVER (ORDER BY SALARY RANGE BETWEEN 50 PRECEDING AND 150 FOLLOWING) AS COUNT_BY_SALARY_RANGE--按照工资进行排序,统计比当前工资小50到比当前工资大150的个数
    FROM EMPLOYEES;

    --RANK、DENSE_RANK
    /*row_number的用途非常广泛,排序最好用它,它会为查询出来的每一行记录生成一个序号,依次排序且不会重复,注意使用row_number函数时必须要用over子句选择对某一列进行排序才能生成序号。

    rank函数用于返回结果集的分区内每行的排名,行的排名是相关行之前的排名数加一。简单来说rank函数就是对查询出来的记录进行排名,与row_number函数不同的是,rank函数考虑到了over子句中排序字段值相同的情况,如果使用rank函数来生成序号,over子句中排序字段值相同的序号是一样的,后面字段值不相同的序号将跳过相同的排名号排下一个,也就是相关行之前的排名数加一,可以理解为根据当前的记录数生成序号,后面的记录依此类推。

    dense_rank函数的功能与rank函数类似,dense_rank函数在生成序号时是连续的,而rank函数生成的序号有可能不连续。dense_rank函数出现相同排名时,将不跳过相同排名号,rank值紧接上一次的rank值。在各个分组内,rank()是跳跃排序,有两个第一名时接下来就是第三名,dense_rank()是连续排序,有两个第一名时仍然跟着第二名。*/

    SELECT 
    	DEPARTMENT_ID, FIRST_NAME||' '||LAST_NAME EMPLOYEE_NAME, SALARY,
    	RANK() OVER (ORDER BY SALARY) AS RANK_ORDER,--按照工资进行排序,标记排名,重复值则标记为一样,后面的跳跃标记
    	DENSE_RANK() OVER (ORDER BY SALARY) AS DENSE_RANK_ORDER,--按照工资进行排序,标记排名,重复值则标记为一样,后面的挨着标记
    	ROW_NUMBER() OVER (ORDER BY SALARY) AS ROW_NUMBER_ORDER--按照工资进行排序,标记排名,连续排名且不重复
    FROM EMPLOYEES;
    
    
    SELECT 
    	DEPARTMENT_ID, FIRST_NAME||' '||LAST_NAME EMPLOYEE_NAME, SALARY,
    	RANK() OVER (PARTITION BY DEPARTMENT_ID ORDER BY SALARY) AS RANK_PART_ORDER,--按照部门ID分组,然后按照工资进行排序,标记排名,重复值则标记为一样,后面的跳跃标记
    	DENSE_RANK() OVER (PARTITION BY DEPARTMENT_ID ORDER BY SALARY) AS DENSE_RANK_PART_ORDER,--按照部门ID分组,然后按照工资进行排序,标记排名,重复值则标记为一样,后面的挨着标记
    	ROW_NUMBER() OVER (PARTITION BY DEPARTMENT_ID ORDER BY SALARY) AS ROW_NUMBER_PART_ORDER--按照部门ID分组,然后按照工资进行排序,标记排名,连续排名且不重复
    FROM EMPLOYEES;

    /*lag(exp_str,offset,defval) over()
    exp_str 指的是要做对比的字段。
    offset 是 exp_str 字段的偏移量,即 offset 为N ,指的是在表中从当前行位置向前数N行就是我们所要找的那一行了。
    比如说,
    在表中,假设当前我们说的当前行在表中排在第四行,则 offset 为3时表示的是我们所要找的数据行就是表中的第一行(即4-3=1)。
    offset 的默认值为1
    lag()函数的返回值为在表中从当前行位置向前数N行的那一行上 exp_str 字段的值。
    当在表中从当前行位置向前数N行已经超出了表的范围时,lag()函数将 defval 这个参数值作为函数的返回值。
    比如说,
    在表中,假设当前我们说的当前行在表中排在第四行,则 offset 为6时表示的是我们所要找的数据行就是表中的第-2行(即4-6=-2),这就表示我们所要找的数据行不在表中已经超出表的范围了,所以lag()函数将 defval 这个参数值作为函数的返回值。
    default 参数的默认值为空值 null,即如果在lag()函数中没有显式设置 default 参数值时 lag() 函数的返回值为空值 null。
    Lead函数的用法类似。*/

    SELECT ENAME,JOB,SAL ,LAG(SAL) OVER(ORDER BY SAL) LAST_SAL FROM EMP;
    
    SELECT ENAME,JOB,SAL ,LAG(SAL,1,500) OVER(ORDER BY SAL) LAST_SAL FROM EMP;
    
    SELECT ENAME,JOB,SAL ,LAG(SAL,2) OVER(ORDER BY SAL) LAST_SAL FROM EMP;
    
    SELECT ENAME,JOB,SAL ,LEAD(SAL) OVER(ORDER BY SAL) LAST_SAL FROM EMP;
    
    SELECT ENAME,JOB,SAL ,LEAD(SAL,1) OVER(ORDER BY SAL) LAST_SAL FROM EMP;
    
    SELECT ENAME,JOB,SAL ,LEAD(SAL,2) OVER(ORDER BY SAL) LAST_SAL FROM EMP;
    
    SELECT ENAME,JOB,SAL ,LEAD(SAL,1) OVER(PARTITION BY JOB ORDER BY SAL) LAST_SAL FROM EMP;
    
    SELECT ENAME,JOB,SAL ,LAG(SAL,1) OVER(PARTITION BY JOB ORDER BY SAL) LAST_SAL FROM EMP;

     

    --查询各部门工资最高的2名员工信息
    SELECT * FROM (SELECT DEPTNO,ENAME,SAL,ROW_NUMBER() OVER (PARTITION BY DEPTNO ORDER BY SAL DESC) RN FROM EMP) WHERE RN<3; 

    --查询全公司工资最高的第6-10名员工
    select * from (SELECT ENAME,SAL,ROW_NUMBER() OVER (ORDER BY SAL DESC) RN FROM EMP) where  RN>=6 and RN<=10;

    --查询大于本部门平均工资的员工信息
    select a.DEPTNO,a.ENAME,a.SAL from EMP a,(select DEPTNO,avg(SAL) as SAL from EMP group by DEPTNO) b where a.DEPTNO=b.DEPTNO and a.SAL>b.SAL;

     

    --对排序的理解
    --sql无排序,over()排序子句省略
    SELECT 
        DEPTNO,EMPNO,ENAME,SAL, 
        LAST_VALUE(SAL) OVER(PARTITION BY DEPTNO)
    FROM EMP;

    --sql无排序,over()排序子句有,窗口省略
    SELECT 
        DEPTNO,EMPNO,ENAME,SAL,
        LAST_VALUE(SAL) OVER(PARTITION BY DEPTNO ORDER BY SAL DESC)
    FROM EMP;

    --sql无排序,over()排序子句有,窗口也有,窗口特意强调全组数据
    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;

    --sql有排序(正序),over()排序子句无,先做sql排序再进行分析函数运算
    SELECT 
        DEPTNO,MGR,ENAME,SAL,HIREDATE,
        LAST_VALUE(SAL) OVER(PARTITION BY DEPTNO) LAST_VALUE
    FROM EMP ORDER BY DEPTNO, SAL;

    --sql有排序(倒序),over()排序子句无,先做sql排序再进行分析函数运算
    SELECT 
        DEPTNO,MGR,ENAME,SAL,HIREDATE,
        LAST_VALUE(SAL) OVER(PARTITION BY DEPTNO) LAST_VALUE
    FROM EMP ORDER BY DEPTNO, SAL DESC;

    --sql有排序(倒序),over()排序子句有,窗口子句无,此时的运算是:sql先选数据但是不排序,而后排序子句先排序并进行分析函数处理(窗口默认为第一行到当前行),最后再进行sql排序
    SELECT 
        DEPTNO,MGR,ENAME,SAL,HIREDATE,
        LAST_VALUE(SAL) OVER(PARTITION BY DEPTNO ORDER BY SAL ASC) LAST_VALUE
    FROM EMP ORDER BY DEPTNO,SAL DESC;

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

     

     

    --以下转载暂无数据源

     

    --count() over()  :统计分区中各组的行数,partition by 可选,order by 可选

    SELECT ENAME,ESEX,EAGE,COUNT(*) OVER() FROM EMP; --总计数
    SELECT ENAME,ESEX,EAGE,COUNT(*) OVER(ORDER BY EAGE) FROM EMP; --递加计数
    SELECT ENAME,ESEX,EAGE,COUNT(*) OVER(PARTITION BY ESEX) FROM EMP; --分组计数
    SELECT ENAME,ESEX,EAGE,COUNT(*) OVER(PARTITION BY ESEX ORDER BY EAGE) FROM EMP;--分组递加计数

    --sum() over()  :统计分区中记录的总和,partition by 可选,order by 可选

    select ename,esex,eage,sum(salary) over() from emp; --总累计求和
    select ename,esex,eage,sum(salary) over(order by eage) from emp; --递加累计求和
    select ename,esex,eage,sum(salary) over(partition by esex) from emp; --分组累计求和
    select ename,esex,eage,sum(salary) over(partition by esex order by eage) from emp; --分组递加累计求和

    --avg() over()  :统计分区中记录的平均值,partition by 可选,order by 可选

    select ename,esex,eage,avg(salary) over() from emp; --总平均值
    select ename,esex,eage,avg(salary) over(order by eage) from emp; --递加求平均值
    select ename,esex,eage,avg(salary) over(partition by esex) from emp; --分组求平均值
    select ename,esex,eage,avg(salary) over(partition by esex order by eage) from emp; --分组递加求平均值

    --min() over() :统计分区中记录的最小值,partition by 可选,order by 可选
    --max() over() :统计分区中记录的最大值,partition by 可选,order by 可选

    select ename,esex,eage,salary,min(salary) over() from emp; --求总最小值
    select ename,esex,eage,salary,min(salary) over(order by eage) from emp; --递加求最小值
    select ename,esex,eage,salary,min(salary) over(partition by esex) from emp; --分组求最小值
    select ename,esex,eage,salary,min(salary) over(partition by esex order by eage) from emp; --分组递加求最小值
    select ename,esex,eage,salary,max(salary) over() from emp; --求总最大值
    select ename,esex,eage,salary,max(salary) over(order by eage) from emp; --递加求最大值
    select ename,esex,eage,salary,max(salary) over(partition by esex) from emp; --分组求最大值
    select ename,esex,eage,salary,max(salary) over(partition by esex order by eage) from emp; --分组递加求最大值

    --rank() over()  :跳跃排序,partition by 可选,order by 必选

    select ename,eage,rank() over(partition by job order by eage) from emp;
    select ename,eage,rank() over(order by eage) from emp;

    --dense_rank() :连续排序,partition by 可选,order by 必选

    select ename,eage,dense_rank() over(partition by job order by eage) from emp;
    select ename,eage,dense_rank() over(order by eage) from emp;

    --row_number() over() :排序,无重复值,partition by 可选,order by 必选

    select ename,eage,row_number() over(partition by job order by eage) from emp;
    select ename,eage,row_number() over(order by eage) from emp;

    --ntile(n) over() :partition by 可选,order by 必选
    --n表示将分区内记录平均分成n份,多出的按照顺序依次分给前面的组

    select ename,salary,ntile(3) over(order by salary desc) from emp;
    select ename,salary,ntile(3) over(partition by job order by salary desc) from emp;

    --first_value() over() :取出分区中第一条记录的字段值,partition by 可选,order by 可选
    --last_value() over() :取出分区中最后一条记录的字段值,partition by 可选,order by 可选

    select ename,first_value(salary) over() from emp;
    select ename,first_value(salary) over(order by salary desc) from emp;
    select ename,first_value(salary) over(partition by job) from emp;                                                           
    select ename,first_value(salary) over(partition by job order by salary desc) from emp;
    
    select ename,last_value(ename) over() from emp;
    select ename,last_value(ename) over(order by salary desc) from emp;
    select ename,last_value(ename) over(partition by job) from emp;
    select ename,last_value(ename) over(partition by job order by salary desc) from emp;

    --first :从DENSE_RANK返回的集合中取出排在最前面的一个值的行
    --last :从DENSE_RANK返回的集合中取出排在最后面的一个值的行

    select 
    	job,max(salary) keep(dense_rank first order by salary desc),
    	max(salary) keep(dense_rank last order by salary desc) 
    from emp
    group by job;

    --lag() over() :取出前n行数据,partition by 可选,order by 必选
    --lead() over() :取出后n行数据,partition by 可选,order by 必选

    select 
    	ename,eage,lag(eage,1,0) over(order by salary), 
    	lead(eage,1,0) over(order by salary) 
    from emp;
     
    select 
    	ename,eage,lag(eage,1) over(partition by esex order by salary),
    	lead(eage,1) over(partition by esex order by salary) 
    from emp;

    --ratio_to_report(a) over(partition by b) :求按照b分组后a的值在所属分组中总值的占比,a的值必须为数值或数值型字段。partition by 可选,order by 不可选

    select ename,job,salary,ratio_to_report(1) over() from emp; --给每一行赋值1,求当前行在总值的占比,总是0.1
    select ename,job,salary,ratio_to_report(salary) over() from emp; --当前行的值在所有数据中的占比
    select ename,job,salary,ratio_to_report(1) over(partition by job) from emp; --给每一行赋值1,求当前行在分组后的组内总值的占比
    select ename,job,salary,ratio_to_report(salary) over(partition by job) from emp; --当前行的值在分组后组内总值占比

    --percent_rank() over()  :partition by 可选,order by 必选
    --所在组排名序号-1除以该组所有的行数-1,排名跳跃排序

    select ename,job,salary,percent_rank() over(order by salary) from emp;
    select ename,job,salary,percent_rank() over(partition by job order by salary) from emp;

    --cume_dist() over() :partition by 可选,order by必选
    --所在组排名序号除以该组所有的行数,注意对于重复行,计算时取重复行中的最后一行的位置

    select ename,job,salary,cume_dist() over(order by salary) from emp;
    select ename,job,salary,cume_dist() over(partition by job order by salary) from emp;

    /*precentile_cont( x ) within group(order by ...) over()    :over()中partition by可选,order by 不可选
    x为输入的百分比,是0-1之间的一个小数,返回该百分比位置的数据,若没有则返回以下计算值(r):
    a=1+( x *(N-1) )  x为输入的百分比,N为分区内的记录的行数
    b=ceil ( a )  向上取整
    c = floor( a ) 向下取整
    r=a * 百分比位置上一条数据 + b * 百分比位置下一条数据*/

    select ename,job,salary,percentile_cont(0.5) within group(order by salary) over() from emp;
    select ename,job,salary,percentile_cont(0.5) within group(order by salary) over(partition by job) from emp;

    --precentile_disc( x ) within group(order by ...) over()   :over()中partition by可选,order by 不可选
    --x为输入的百分比,是0-1之间的一个小数,返回百分比位置对应位置上的数据值,若没有对应数据值,就取大于该分布值的下一个值

    select ename,job,salary,percentile_disc(0.5) within group(order by salary) over()from emp;
    select ename,job,salary,percentile_disc(0.5) within group(order by salary) over(partition by job) from emp;

    /*stddev() over():计算样本标准差,只有一行数据时返回0,partition by 可选,order by 可选
    stddev_samp() over():计算样本标准差,只有一行数据时返回null,partition by 可选,order by 可选
    stddev_pop() over():计算总体标准差,partition by 可选,order by 可选*/

    select stddev(stu_age) over() from student; --计算所有记录的样本标准差
    select stddev(stu_age) over(order by stu_age) from student; --计算递加的样本标准差
    select stddev(stu_age) over(partition by stu_major) from student; --计算分组的样本标准差
    select stddev(stu_age) over(partition by stu_major order by stu_age) from student; --计算分组递加的样本标准差
    select stddev_samp(stu_age) over() from student; --计算所有记录的样本标准差
    select stddev_samp(stu_age) over(order by stu_age) from student; --计算递加的样本标准差
    select stddev_samp(stu_age) over(partition by stu_major) from student; --计算分组的样本标准差
    select stddev_samp(stu_age) over(partition by stu_major order by stu_age) from student; --计算分组递加的样本标准差
    select stddev_pop(stu_age) over() from student; --计算所有记录的总体标准差
    select stddev_pop(stu_age) over(order by stu_age) from student; --计算递加的总体标准差
    select stddev_pop(stu_age) over(partition by stu_major) from student; --计算分组的总体标准差
    select stddev_pop(stu_age) over(partition by stu_major order by stu_age) from student;--计算分组递加的总体标准差

    /*variance() over():计算样本方差,只有一行数据时返回0,partition by 可选,order by 可选
    var_samp() over():计算样本方差,只有一行数据时返回null,partition by 可选,order by 可选
    var_pop() over():计算总体方差,partition by 可选,order by 可选*/

    select variance(stu_age) over() from student; --计算所有记录的样本方差
    select variance(stu_age) over(order by stu_age) from student; --计算递加的样本方差
    select variance(stu_age) over(partition by stu_major) from student; --计算分组的样本方差
    select variance(stu_age) over(partition by stu_major order by stu_age) from student; --计算分组递加的样本方差
    select var_samp(stu_age) over() from student; --计算所有记录的样本方差
    select var_samp(stu_age) over(order by stu_age) from student; --计算递加的样本方差
    select var_samp(stu_age) over(partition by stu_major) from student; --计算分组的样本方差
    select var_samp(stu_age) over(partition by stu_major order by stu_age) from student; --计算分组递加的样本方差
    select var_pop(stu_age) over() from student; --记录所有就的总体方差
    select var_pop(stu_age) over(order by stu_age) from student; --计算递加的总体方差
    select var_pop(stu_age) over(partition by stu_major) from student; --计算分组的总体方差
    select var_pop(stu_age) over(partition by stu_major order by stu_age) from student;--计算分组递加的样本方差

    /*
    stddev()=sqrt( variance() )     sqrt()--求开方
    stddev_samp()=sqrt( var_samp() )
    stddec_pop=sqrt( var_pop() )
    */

    --covar_samp over():返回一对表达式的样本协方差,partition by 可选,order by 可选
    --covar_pop over(): 返回一堆表达式的总体协方差,partition by 可选,order by 可选

    select covar_samp(stu_age,line) over() from student; --计算所有记录的样本协方差
    select covar_samp(stu_age,line) over(order by stu_age) from student; --计算递加的样本协方差
    select covar_samp(stu_age,line) over(partition by stu_major) from student; --计算分组的样本协方差
    select covar_samp(stu_age,line) over(partition by stu_major order by stu_age) from student; --计算分组递加的样本协方差
    select covar_pop(stu_age,line) over() from student; --计算所有记录的总体协方差
    select covar_pop(stu_age,line) over(order by stu_age) from student; --计算递加的总体协方差
    select covar_pop(stu_age,line) over(partition by stu_major) from student; --计算分组的总体协方差
    select covar_pop(stu_age,line) over(partition by stu_major order by stu_age) from student; --计算分组递加的总体协方差

    --corr() over() :返回一对表达式的相关系数,partition by 可选,order by 可选

    select corr(stu_age,line) over() from student; --计算所有记录的相关系数
    select corr(stu_age,line) over(order by stu_age) from student; --计算递加的相关系数
    select corr(stu_age,line) over(partition by stu_major) from student; --计算分组的相关系数
    select corr(stu_age,line) over(partition by stu_major order by stu_age) from student; --计算分组递加的相关系数

     

    --实际案例

    --创建模拟表

    create table T( 
       BILL_MONTH varchar2(12) , 
       AREA_CODE number, 
       NET_TYPE varchar(2), 
       LOCAL_FARE number 
    );
    
    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;

    --取出每月通话费最高和最低的两个地区

    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;

    --计算出各个地区连续3个月的通话费用的平均数(移动平均值)

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

    --求各地区按月份累加的通话费

    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;

     

    展开全文
  • 本文是小编日常手机整理的有关oracle分析函数oracle分析函数功能非常强大,工作也常用到,所以大家务必要掌握,对oracle分析函数知识感兴趣的朋友一起看看吧
  • Oracle分析函数基本概念和语法总结及Regexp_***(regexp_substr,regexp_instr, regexp_like, regexp_replace, regexp_count)用法
  • Oracle分析函数使用总结Oracle分析函数使用总结Oracle分析函数使用总结Oracle分析函数使用总结
  • ORACLE分析函数教程

    2013-01-09 11:15:08
    关于ORACLE分析函数的教程,教程描述描述清晰
  • NULL 博文链接:https://yangkai.iteye.com/blog/754447
  • oracle 分析函数和性能

    2019-09-30 20:39:49
    分析函数语法 sum()over (partition by aa order by bb rows betweenunbounded preceding and unboundedfollowing ) partition by 为分组 order by 和order by 之后 为开窗函数 控制数据范围 控制数据范围的...

    分析函数语法

    sum()over (partition by aa order by bb rows between unbounded preceding and unboundedfollowing )

    partition by 为分组

    order by 和order by 之后 为开窗函数 控制数据范围

    控制数据范围的语句

    unbounded preceding 第一行,可以理解为当前行的最上面一行,就是第一行

    1 preceding 当前行的上一行

    unbouned following 最后一行,可以理解为当前行的最后一行,就是最后一行

    1 following     当前行的下一行

    current row  当前行

    测试

    select name,
           shangjin,
           zhenying,
           sum(shangjin) over(partition by zhenying order by name  ROWS between unbounded  PRECEDING AND unbounded   FOLLOWING)
      from xzhdx;

    求每个分组中第一行到最后一行的和,就是该分组的和。

     

    select name,
           shangjin,
           zhenying,
           sum(shangjin) over(partition by zhenying order by name  ROWS between 1  PRECEDING AND unbounded   FOLLOWING)
      from xzhdx;

    求每个分组当前行上一行到分组最后一行的和

     

    select name,
           shangjin,
           zhenying,
           sum(shangjin) over(partition by zhenying order by name  ROWS between 1  PRECEDING AND 1   FOLLOWING)
      from xzhdx;

    求每个分组当中,当前行的上一行( 1  PRECEDING)到当前行的下一行(1   FOLLOWING)的和

     

    select name,
           shangjin,
           zhenying,
           sum(shangjin) over(partition by zhenying order by name --省略后面的ROWS between )
      from xzhdx;

    表示分组求每个分组的第一行到当前行的和,当order by 之后不跟ROWS between 就是默认第一行到current row 相当于 order by name ROWS between  unbouned following  and current row。

    select name,
           shangjin,
           zhenying,
           sum(shangjin) over(partition by zhenying  )--省略后面的order by name )
      from xzhdx;

    相当于求每组的第一行和最后一行的和

     

    select name,
           shangjin,
           zhenying,
           sum(shangjin) over(--省略partition by order by name  ROWS between 1  PRECEDING AND 1   FOLLOWING)
      from xzhdx;

    表示不分组 求所有数据的当前行上一行 到 当前行下一行的 和

     

    select name,
           shangjin,
           zhenying,
           sum(shangjin) over( --省略partition by order by  )
      from xzhdx;

    就是求所有数据的和

     总结

    sum()over(order by rows between)

    partition by 忽略的时候,表示不分组,求的是order by 子句范围的行的汇总

     sum()over(partition by  order by  )

    rows between 忽略的时候表示第一行到当前行的汇总

    sum()over(partition by 

    order by 忽略的时候,就表示组的第一行到最后一行的汇总

    sum()over ()

    partition byorder by 忽略的时候一起忽略的时候表示求数据的汇总。

    其他分析行数

    lag(1,2,3)over(partition by  order by rows between

    参数1代表 列

    参数2代表该列的当前行向上找几行

    参数3默认值,如果没有默认为空

    lead(1,2,3)over(partition by  order by rows between)

     参数1代表 列

     参数2代表该列的当前行向下找几行

    参数3默认值,如果没有默认为空

    rank () over()

    排序1,2,2,4

    dense_rank()over()

    排序1,2,2,3

    row_number()over()

    排序1,2,3,4

    sql语句order by 和over(order by )先后顺序

    如果sql语句有order by,开窗函数中也有order by ,先是计算开窗函数,最后才计算sql中的排序.,sql中的order by 不会影响开窗函数结果,

    如果sql语句有order by,开窗函数中没有order by ,那么先是计算sql中的order by,然后在算开窗函数,也就是会影响开窗函数

     

    转载于:https://www.cnblogs.com/throughRiversandLake/p/11249238.html

    展开全文
  • oracle分析函数汇总

    万次阅读 多人点赞 2018-07-03 15:00:47
    一、分析函数语法 function_name(,<argument>...) over(<partition_Clause><order by_Clause>); function_name():函数名称 argument:参数 over( ):开窗函数 partition_Clause:分区子句,数据记录集分组,group ...
  • Oracle分析函数操作及注意事项分享,文档中有具体的操作案例,有大量的截图方便大家理解每个分析函数的功能,及自已对每个函数的一些理解。
  • oracle分析函数.doc

    2019-06-18 22:44:07
    oracle常用分析函数解析,可以帮助各位同学更好了了解分析函数的优势
  • 作为一个数据库管理员可以不知道分析函数,但是开发dba还是需要了解的。有时候即使经常写sql的也很少用到的分析函数。在优化sql 的过程中,经常遇到一些通过编写复杂的sql 来实现分析函数功能的sql。正如作者说的,...
  • Oracle分析函数详解.doc

    2021-07-23 16:14:36
    Oracle分析函数详解

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 108,912
精华内容 43,564
关键字:

oracle分析函数