oracle分析函数技术详解（配上开窗函数over()）

最新推荐文章于 2023-09-19 10:23:22 发布
最新推荐文章于 2023-09-19 10:23:22 发布
阅读量186 收藏 1
点赞数 1
分类专栏： SQL
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_38135677/article/details/89916652
版权

一、Oracle分析函数入门 

分析函数是什么？

分析函数是oracle用于解决复杂报表统计需求的功能强大的函数，它可以在数据中进行分组，然后计算基于组的某种统计值，并且每一组的每一行都可以返回一个统计值。

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

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

分析函数的形式

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

注：窗口子句在这里我只说rows方式的窗口,range方式和滑动窗口也不提。

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

  • 示例目的：显示各部门员工的工资，并附带显示该部门的最高工资 
--显示各部门员工的工资，并附带显示该部分的最高工资。
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;

运行结果：

 

 

  • 示例目的：按照deptno分组，然后计算每组值的总和
SELECT EMPNO,
       ENAME,
       DEPTNO,
       SAL,
       SUM(SAL) OVER(PARTITION BY DEPTNO ORDER BY ENAME) max_sal
  FROM SCOTT.EMP;

运行结果

 

 

  • 示例目的：对各部门进行分组，并附带显示第一行至当前行的汇总
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;

 运行结果：

  • 示例目标：当前行至最后一行的汇总
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;

运行结果：

 

  • 示例目标：当前行的上一行(rownum-1)到当前行的汇总
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;

运行结果：

结果分析：什么是当前行？ 在窗口函数计算的时候，会基于每一组每一行返回一个统计值，计算该行统计值的时候，该行就叫做当前行。 比如第二行的max_sal值是由当前行（也就是第二行）的sal值和当前行的上一行（也就是第一行）的sal值的累加。第三行的max_sal值是由当前行（第三行）的sal值和当前行的上一行（第二行）的sal值的累加。 

 

  • 示例目标：   当前行的上一行(rownum-1)到当前行的下俩行(rownum+2)的汇总
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;

运行结果：

结果分析：先看第一行的max_sal是当前行的上一行（第0行）到当前行的下俩行（第二行和第三行）的累加 。首先窗口函数的执行范围是查询完数据后的组，比如说第三行的max_sal值是第二行和当前行的累加，没有第四行和第五行，因为第三行是deptno = 10 的最后一行。第四行和第五行是别的组。

 

二、理解over()函数

  1. 俩个order by 的执行机制

分析函数以及与其配合的开窗函数over() 是在整个sql查询结束后(sql语句中的order by的执行比较特殊)再进行的操作, 也就是说sql语句中的order by也会影响分析函数的执行结果：

a) 两者一致：如果sql语句中的order by满足与分析函数配合的开窗函数over（）分析时要求的排序，即sql语句中的order by子句里的内容和开窗函数over（）中的order by子句里的内容一样，

那么sql语句中的排序将先执行,分析函数在分析时就不必再排序；sql的排序先执行，分析函数不排序

b) 两者不一致：如果sql语句中的order by不满足与分析函数配合的开窗函数over（）分析时要求的排序，即sql语句中的order by子句里的内容和开窗函数over（）中的order by子句里的内容不一样，

那么sql语句中的排序将最后在分析函数分析结束后执行排序。（即sql的排序在最后执行）

  1. 开窗函数over()  分析函数中的分组/排序/窗口

开窗函数包含三个分析子句：分组子句（partition by）、排序子句（order by）、窗口子句（rows)

窗口就是分析函数分析时要处理的数据范围， 就拿sum来说，它是sum窗口中的记录而不是整个分组中的记录。因此我们要得到某个栏位的累计值时，我们需要把窗口指定到该分组中的第一行数据到当前行，如果你指定窗口从第一行到最后一行，那么该组中的每一个sum值都一样，即分组的总和。

窗口子句中我们经常用到指定第一行，当前行，最后一行这样的三个属性：

第一行是 unbounded preceding，
当前行是 current row，
最后一行是 unbounded following，

注释：

a).当开窗函数over（）出现分组(partition by)子句时，

 unbounded preceding 即第一行是指表中的一个分组里的第一行，unbounded following 即最后一行是指表中的一个分组的最后一行。

b).当开窗函数over（）省略了分组(partition by)子句时， 

unbounded preceding即第一行是指表中的第一行， unbounded following即最后一行是指表中的最后一行。

last_value(sal) over(partition by deptno 
                     order by sal 
                     rows between unbounded preceding and unbounded following)

以上示例指定窗口为整个分组。

窗口子句不能单独出现，必须结合 order by 一起使用。但出现order by子句的时候，不一定要有窗口子句。

如果省略分组,则把全部记录当成一个组。

而无论是否省略分组子句，如下结论都是成立的：

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：关注点：sql无排序，over()排序子句省略

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

运行结果：

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

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

运行结果：

例3：关注点：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;

运行结果：

例4：关注点：sql有排序（正序），over()排序子句无，先做sql排序再进行分析函数运算

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

运行结果：

例5：关注点：sql有排序（倒序），over()排序子句无，先做sql排序再进行分析函数运算

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;

运行结果：

例6：关注点：sql有排序（倒序），over()排序子句有，窗口子句无。此时的运算顺序是：1.sql先选数据，但不排序。2.排序子句先排序并进行分析函数处理（窗口默认从第一行到当前行）。3.最后进行sql排序

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;

运行结果：

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;

运行结果：

 

三、常见分析函数详解

一、创建表

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;

三、first_value()与last_value()：求最值对应的其他属性（其实就是依据排序来取第一行数据和最后一行数据）

问题、取出每月通话费最高和最低的两个地区。

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

运行结果：

四、rank()，dense_rank()，row_number()：求排序

rank，dense_rank，row_number函数为每条记录产生一个从1至n的自然数，n的值可能小于等于记录的总数。这3个函数的唯一区别就是遇到相同数据是的排名策略

  • row_number：返回一个唯一的值，当碰到相同数据时会按照记录中的记录逐渐递增
  • dense_rank：返回一个唯一的值，当碰到相同数据时，此时所有相同数据的排名都是一样的。
  • rank：返回一个唯一的值，当碰到相同的数据时，此时所有相同数据的排名是一样的，同时会在最后一条相同记录和下一条不同记录的排名之间空出排名。

示例1：row_number()值相同时排名不相等，其后排名连续不跳跃

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

运行结果：

示例2：dense_rank()值相同时排名相同，其后排名连续不跳跃

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

运行结果：

示例3：rank()值相同时排名相同，其后排名跳跃不连续

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

运行结果： 

五、lag()与lead()：求之前或之后的第N行 

lag和lesad函数可以在一次查询中取出同一字段的前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;

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

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

七、max(),min(),sun()与avg()：求移动的最值总和与平均值
问题：计算出各个地区连续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 分析函数over
wuyisky
02-24 149
分析函数(OVER) &lt;script type="text/javascript"&gt;&lt;!-- google_ad_client = "pub-4334685396432654"; /* 728x15, 创建于 09-12-23 */ google_ad_slot = "4194329749"; google_ad_width = 728; google_ad_height = ...
Oracle分析函数OVER的用法
FIGHT_ANGEL的专栏
04-02 592
使用方法举例 ---创建测试表 SQL> desc t_test;  名称                                      是否为空? 类型  ----------------------------------------- -------- --------------------------  T_ID                       
sum() over
qq_20916553的博客
03-19 1061
select sum(case job when 'CLERK' then sal end) as cl, sum(case job when 'MANAGER' then sal end) as ma from emp; select sum(decode(job,'CLERK',sal)) as cl, sum(decode(job,'MANAGER',sal)) ...
oracle　over()分析函数的使用实例
nimeijian的专栏
01-16 479
表数据： 1、按部门求薪酬总和 SELECT t.*,SUM(sal) over(PARTITION BY deptno) 部门薪酬总和 　　 from scott.emp t; 2、按部门累加薪酬 SELECT t.*, SUM(sal) over(PARTITION BY deptno) 部门薪酬总和, SUM(sal) over(PARTITION BY deptno ORD
oracle分析函数over_及开窗函数.txt
05-05
### Oracle分析函数OVER及开窗函数详解 #### 一、概述 在Oracle数据库中，分析函数（Analytic Functions）是一种非常强大的工具，用于处理复杂的查询需求。这些函数可以在一组相关的行上执行计算，并且每行返回一个...
SQL开窗函数的具体实现详解
09-08
SQL开窗函数是一种在SQL查询中执行复杂分析的高级技术，它允许你在同一查询中对一组相关行（称为“窗口”或“分组”）进行聚合操作，而不仅仅是整个结果集。这使得处理复杂的排序、分组和计算变得更加便捷。在2003年...
Oracle分析函数rank() over开窗函数使用介绍
汉南最後の読書人
02-26 3616
开窗函数常用作分组统计排名的现实的数据分析应用场景，下面是它的几种使用形式： 1.dense_rank() over开窗函数:在做排序时如果遇到列有重复值，则重复值所在行的序列值（排名）相同，而其后的序列值依旧递增。本人牛客网SQL编程使用的具体例子（要求获取当前（to_date='9999-01-01'）薪水第二多的员工的emp_no以及其对应的薪水salary） --按照emp_no分组...
sum() over() 函数用于累加计算
xy_best_的博客
10-20 1万+
本文主要讲的是计算一个指标时，需要按天累加，也就是累计指标计算，可以使用到该函数 1.可以按天进行累加 2.如果有相同的日期，可以先按天分组，然后进行累加，筛选项也是一样 数据源： 1、按照日期对应的V1（数字）进行升序（desc降序），然后进行累加 select V0, V1, sum(V1) over(order by V1 desc) from table 2、先根据V0进行分组，再按照v1进行升序，然后进行累加（可用做筛选项的累加） select V0, V1, sum(V1) ove
数据库sum()over()函数
最新发布
feiyuxc的博客
09-19 4633
累计求和 SQL 数据库 sum()over() hive mysql 窗口函数
sum() over()函数使用
qq_43494013的博客
04-12 3330
1 语法 sum(col1) over(partition by col2 order by col3 ) 按col2 进行分组(partition )，每组以col3 进行排序(order)，并进行连续加总（sum） 2 案例 2.1 描述 某班的每个人的综合成绩用A,B,C,D,E表示，90分以上都是A，80-90都是B，70-80分为C，60~70为D，E为60分以下 假设每个名次最多1个人，比如有2个A，那么必定有1个A是第1名，有1个A是第2名 2.2 表数据 第1行表示成绩为A的学
【Oracle】sum(..) over(..)用法分析
zclinux的博客
04-24 1万+
今天再看sql优化详解的时候，提到了一个sum(..) over(..)于是自己实验并在网上找了相关的一些文章来看下面创建一张表：create sequence xulie increment by 1 start with 1 maxvalue 9999999999; create table test(id number(20),name varchar2(20),sal number(38......
oracle窗口函数over()的理解
朝着梦想 渐行前进
10-19 6741
over()开窗函数和聚合函数的不同之处是对于每个组返回多行，而聚合函数对于每个组只返回一行。 开窗函数指定了分析函数工作的数据窗口大小，这个数据窗口大小可能会随着行的变化而变化，举例如下： over（order by salary） 按照salary排序进行累计，order by是个默认的开窗函数 over（partition by deptno）按照部门分区 over（order by sal
sum() over(partition by)函数1
热门推荐
bingbangx的博客
08-04 1万+
sum(...) over( )，对所有行求和 sum(...) over( order by ...)，和 = 第一行 到 与当前行同序号行的最后一行的所有值求和， 测试用的数据 1.sum(LABST) over() 2.sum(LABST) over(order by MATNR) 3.sum(LABST) over(order by MATNR,ZWEEK) 4.sum(LABST) over(partition by MAT...
hive踩坑笔记 —— 开窗函数为聚合函数时，加order by 与不加 order by的区别
一纸春秋的博客
11-28 6393
over()开窗函数前分排序函数和聚合函数两种 当为排序函数，如row_number(),rank()等时，over中的order by只起到窗口内排序作用。 当为聚合函数，如max，min，count等时，over中的order by不仅起到窗口内排序，还起到窗口内从当前行到之前所有行的聚合（多了一个范围）。 以 sum 举例，下面的结果中，左图为不加 order by的结果，右图为加上order by的结果 发现不加order by score的话，就是针对一整个分区进行sum求和。加上or.
Oracle NTH_VALUE分析函數及ROWS BETWEEN UNBOUNDED PRECEDING AND子句
cuilingqiao0657的博客
12-06 522
在Oracle 10G分析函數中使用FIRST_VALUE，LAST_VALUE 可返回第一行和最後一行值 在Oracle 11G推出更為強大NTH_VALUE 開窗分析函數，其作用取任意一行值，官方說明： NTH_V...
Oracle开发专题之：OLAP 函数 （rows 2 preceding / unbounded preceding）
极客神殿
09-24 6661
ORACLE OLAP 函数 　　 最近这个东东用得特别多，总结了一下 。 语法: FUNCTION_NAME(,…) 　　 OVER() OLAP函数语法四个部分: 　　1、function本身 用于对窗口中的数据进行操作； 　　2、partitioning clause 用于将结果集分区； 　　3、order by clause 用于对分区中的数据进行排序； 　　4、windowing cl...
oracle的sum(...) over ... 的使用
iteye_8954的博客
09-11 453
over不能单独使用，要和分析函数：rank(),dense_rank(),row_number()等一起使用。 over函数的参数：over（partition by columnname1 order by columnname2） 含义，按columname1指定的字段进行分组排序，或者说按字段columnname1的值进行分组排序。 例如：employees表中，有两个部门的...
Oracle分析函数详解：开窗函数与应用示例
除了`AVG`，Oracle分析函数还包括`RANK()`, `DENSE_RANK()`, `ROW_NUMBER()`, `MIN()`, `MAX()`, `SUM()`, `COUNT()`, `LEAD()`, `LAG()`, `FIRST_VALUE()`, `LAST_VALUE()`等。这些函数提供了丰富的数据分析能力。 ...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值