社群
學習
工具庫
AI工具
休閒
搜尋
繁体中文
目錄
一、Oracle分析函数入门
二、理解over()函数
三、常见分析函数详解
首頁 資料庫 mysql教程 oracle分析函数技术详解（配上开窗函数over()）

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

WBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWB
WBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWB
Jun 07, 2016 pm 03:50 PM
oracle over 函數 分析 科技 詳解

一、Oracle分析函数入门 分析函数是什么？ 分析函数是Oracle专门用于 解决复杂报表统计需求 的功能强大的函数， 它可以在数据中进行分组然后计算基于组的某种统计 ，并且每一组的每一行都可以返回一个统计。 分析函数和聚合函数的不同之处是什么？ 普通的聚

一、Oracle分析函数入门

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

          

分析函数和聚合函数的不同之处是什么？
普通的聚合函数用group by分组，每个分组返回一个统计值，而分析函数采用partition by分组，并且每组每行都可以返回一个统计值。

              

分析函数的形式
分析函数带有一个开窗函数over()，包含三个分析子句:分组(partition by), 排序(order by), 窗口(rows) ，他们的使用形式如下：over(partition by xxx order by yyy rows between zzz)。
注：窗口子句在这里我只说rows方式的窗口,range方式和滑动窗口也不提

    

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

示例目的：显示各部门员工的工资，并附带显示该部分的最高工资。

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

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

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

运行结果：

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

               

示例目的：按照deptno分组，然后计算每组值的总和

<span>SELECT</span> EMPNO,
       ENAME,
       DEPTNO,
       SAL,
       <span>SUM</span>(SAL) <span>OVER</span>(PARTITION <span>BY</span> DEPTNO <span>ORDER</span> <span>BY</span> ENAME) max_sal
  <span>FROM</span> SCOTT.EMP;
登入後複製

运行结果：

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

     

示例目的：对各部门进行分组，并附带显示第一行至当前行的汇总

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

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

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

运行结果：

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

   

示例目标：当前行至最后一行的汇总

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

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

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

运行结果：

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

   

 示例目标：当前行的上一行(rownum-1)到当前行的汇总

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

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

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

运行结果：

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

    

示例目标：   当前行的上一行(rownum-1)到当前行的下辆行(rownum+2)的汇总     

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

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

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

运行结果：

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

     



二、理解over()函数

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

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

那么sql语句中的排序将先执行,分析函数在分析时就不必再排序；
b) 两者不一致：如果sql语句中的order by不满足与分析函数配合的开窗函数over（）分析时要求的排序，即sql语句中的order by子句里的内容和开窗函数over（）中的order by子句里的内容不一样，

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

           

1.2、开窗函数over（）分析函数中的分组/排序/窗口
      开窗函数over（）分析函数包含三个分析子句：分组子句(partition by)， 排序子句(order by)， 窗口子句(rows)
      窗口就是分析函数分析时要处理的数据范围，就拿sum来说,它是sum窗口中的记录而不是整个分组中的记录，因此我们在想得到某个栏位的累计值时，我们需要把窗口指定到该分组中的第一行数据到当前行, 如果你指定该窗口从该分组中的第一行到最后一行,那么该组中的每一个sum值都会一样，即整个组的总和。

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


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

注释：

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

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

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

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


窗口子句不能单独出现，必须有order by子句时才能出现，

例如:

<span>last_value(sal) <span>over</span>(partition <span>by</span> deptno 
                     <span>order</span> <span>by</span> sal 
                     rows <span>between</span> unbounded preceding <span>and</span> unbounded following)</span>
登入後複製

以上示例指定窗口为整个分组。而出现order by子句的时候，不一定要有窗口子句，但效果会很不一样，此时的窗口默认是当前组的第一行到当前行！


如果省略分组,则把全部记录当成一个组。
a) 如果存在order by则默认窗口是unbounded preceding and current row   --当前组的第一行到当前行
b) 如果这时省略order by则窗口默认为unbounded preceding and unbounded following  --整个组 


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

1、窗口子句不能单独出现，必须有order by子句时才能出现。

2、当省略窗口子句时:
a) 如果存在order by则默认的窗口是unbounded preceding and current row  --当前组的第一行到当前行，即在当前组中，第一行到当前行
b) 如果同时省略order by则默认的窗口是unbounded preceding and unbounded following  --整个组

             
所以，

lag(sal) over(order by sal) 解释

over（order by salary）表示意义如下：

首先，我们要知道由于省略分组子句，所以当前组的范围为整个表的数据行，

然后，在当前组（此时为整个表的数据行）这个范围里执行排序（即order by salary），

最后，我们知道分析函数lag(sal)在当前组（此时为整个表的数据行）这个范围里的窗口范围为当前组的第一行到当前行，即分析函数lag(sal)在这个窗口范围执行。


参见：

Oracle的LAG和LEAD分析函数


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


1.3、帮助理解over()的实例

例1：关注点：sql无排序，over()排序子句省略

<span>SELECT</span> DEPTNO, EMPNO, ENAME, SAL, 
       LAST_VALUE(SAL) <span>OVER</span>(PARTITION <span>BY</span> DEPTNO)
<span>FROM</span> EMP;
登入後複製

运行结果：

 

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

       

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

 

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

<span>SELECT</span> DEPTNO,
       EMPNO,
       ENAME,
       SAL,
       LAST_VALUE(SAL) <span>OVER</span>(PARTITION <span>BY</span> DEPTNO 
                            <span>ORDER</span> <span>BY</span> SAL <span>DESC</span>)
  <span>FROM</span> EMP;
登入後複製

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

运行结果：

 

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

                  
例3：关注点：sql无排序，over()排序子句有，窗口也有，窗口特意强调全组数据

 

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

<span>SELECT</span> DEPTNO,
       EMPNO,
       ENAME,
       SAL,
       LAST_VALUE(SAL) 
       <span>OVER</span>(PARTITION <span>BY</span> DEPTNO 
            <span>ORDER</span> <span>BY</span> SAL 
            ROWS <span>BETWEEN</span> UNBOUNDED PRECEDING <span>AND</span> UNBOUNDED FOLLOWING) MAX_SAL
  <span>FROM</span> EMP;
登入後複製

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

运行结果：

 

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

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

 

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

<span>SELECT</span> DEPTNO,
       MGR,
       ENAME,
       SAL,
       HIREDATE,
       LAST_VALUE(SAL) <span>OVER</span>(PARTITION <span>BY</span> DEPTNO) LAST_VALUE
  <span>FROM</span> EMP
 <span>WHERE</span> DEPTNO <span>=</span> <span>30</span>
 <span>ORDER</span> <span>BY</span> DEPTNO, MGR;
登入後複製

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

运行结果：

 

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

 



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

 

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

<span>SELECT</span> DEPTNO,
       MGR,
       ENAME,
       SAL,
       HIREDATE,
       LAST_VALUE(SAL) <span>OVER</span>(PARTITION <span>BY</span> DEPTNO) LAST_VALUE
  <span>FROM</span> EMP
 <span>WHERE</span> DEPTNO <span>=</span> <span>30</span>
 <span>ORDER</span> <span>BY</span> DEPTNO, MGR <span>DESC</span>;
登入後複製

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

运行结果：

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

                

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

 

 

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

<span>SELECT</span> DEPTNO,
       MGR,
       ENAME,
       SAL,
       HIREDATE,
       <span>MIN</span>(SAL) <span>OVER</span>(PARTITION <span>BY</span> DEPTNO <span>ORDER</span> <span>BY</span> SAL <span>ASC</span>) LAST_VALUE
  <span>FROM</span> EMP
 <span>WHERE</span> DEPTNO <span>=</span> <span>30</span>
 <span>ORDER</span> <span>BY</span> DEPTNO, MGR <span>DESC</span>;
登入後複製

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

运行结果：

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

 

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

<span>SELECT</span> DEPTNO,
       MGR,
       ENAME,
       SAL,
       HIREDATE,
       <span>MIN</span>(SAL) <span>OVER</span>(PARTITION <span>BY</span> DEPTNO <span>ORDER</span> <span>BY</span> SAL <span>DESC</span>) LAST_VALUE
  <span>FROM</span> EMP
 <span>WHERE</span> DEPTNO <span>=</span> <span>30</span>
 <span>ORDER</span> <span>BY</span> DEPTNO, MGR <span>DESC</span>;
登入後複製

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

运行结果：

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

              

三、常见分析函数详解

为了方便进行实践，特将演示表和数据罗列如下：

一、创建表

<span>create</span> <span>table</span> t( 
   bill_month <span>varchar2</span>(<span>12</span>) , 
   area_code <span>number</span>, 
   net_type <span>varchar</span>(<span>2</span>), 
   local_fare <span>number</span> 
);
登入後複製

      

二、插入数据

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

<span>insert</span> <span>into</span> t <span>values</span>(<span>'</span><span>200405</span><span>'</span>,<span>5761</span>,<span>'</span><span>G</span><span>'</span>, <span>7393344.04</span>); 
<span>insert</span> <span>into</span> t <span>values</span>(<span>'</span><span>200405</span><span>'</span>,<span>5761</span>,<span>'</span><span>J</span><span>'</span>, <span>5667089.85</span>); 
<span>insert</span> <span>into</span> t <span>values</span>(<span>'</span><span>200405</span><span>'</span>,<span>5762</span>,<span>'</span><span>G</span><span>'</span>, <span>6315075.96</span>); 
<span>insert</span> <span>into</span> t <span>values</span>(<span>'</span><span>200405</span><span>'</span>,<span>5762</span>,<span>'</span><span>J</span><span>'</span>, <span>6328716.15</span>); 
<span>insert</span> <span>into</span> t <span>values</span>(<span>'</span><span>200405</span><span>'</span>,<span>5763</span>,<span>'</span><span>G</span><span>'</span>, <span>8861742.59</span>); 
<span>insert</span> <span>into</span> t <span>values</span>(<span>'</span><span>200405</span><span>'</span>,<span>5763</span>,<span>'</span><span>J</span><span>'</span>, <span>7788036.32</span>); 
<span>insert</span> <span>into</span> t <span>values</span>(<span>'</span><span>200405</span><span>'</span>,<span>5764</span>,<span>'</span><span>G</span><span>'</span>, <span>6028670.45</span>); 
<span>insert</span> <span>into</span> t <span>values</span>(<span>'</span><span>200405</span><span>'</span>,<span>5764</span>,<span>'</span><span>J</span><span>'</span>, <span>6459121.49</span>); 
<span>insert</span> <span>into</span> t <span>values</span>(<span>'</span><span>200405</span><span>'</span>,<span>5765</span>,<span>'</span><span>G</span><span>'</span>, <span>13156065.77</span>); 
<span>insert</span> <span>into</span> t <span>values</span>(<span>'</span><span>200405</span><span>'</span>,<span>5765</span>,<span>'</span><span>J</span><span>'</span>, <span>11901671.70</span>); 
<span>insert</span> <span>into</span> t <span>values</span>(<span>'</span><span>200406</span><span>'</span>,<span>5761</span>,<span>'</span><span>G</span><span>'</span>, <span>7614587.96</span>); 
<span>insert</span> <span>into</span> t <span>values</span>(<span>'</span><span>200406</span><span>'</span>,<span>5761</span>,<span>'</span><span>J</span><span>'</span>, <span>5704343.05</span>); 
<span>insert</span> <span>into</span> t <span>values</span>(<span>'</span><span>200406</span><span>'</span>,<span>5762</span>,<span>'</span><span>G</span><span>'</span>, <span>6556992.60</span>); 
<span>insert</span> <span>into</span> t <span>values</span>(<span>'</span><span>200406</span><span>'</span>,<span>5762</span>,<span>'</span><span>J</span><span>'</span>, <span>6238068.05</span>); 
<span>insert</span> <span>into</span> t <span>values</span>(<span>'</span><span>200406</span><span>'</span>,<span>5763</span>,<span>'</span><span>G</span><span>'</span>, <span>9130055.46</span>); 
<span>insert</span> <span>into</span> t <span>values</span>(<span>'</span><span>200406</span><span>'</span>,<span>5763</span>,<span>'</span><span>J</span><span>'</span>, <span>7990460.25</span>); 
<span>insert</span> <span>into</span> t <span>values</span>(<span>'</span><span>200406</span><span>'</span>,<span>5764</span>,<span>'</span><span>G</span><span>'</span>, <span>6387706.01</span>); 
<span>insert</span> <span>into</span> t <span>values</span>(<span>'</span><span>200406</span><span>'</span>,<span>5764</span>,<span>'</span><span>J</span><span>'</span>, <span>6907481.66</span>); 
<span>insert</span> <span>into</span> t <span>values</span>(<span>'</span><span>200406</span><span>'</span>,<span>5765</span>,<span>'</span><span>G</span><span>'</span>, <span>13562968.81</span>); 
<span>insert</span> <span>into</span> t <span>values</span>(<span>'</span><span>200406</span><span>'</span>,<span>5765</span>,<span>'</span><span>J</span><span>'</span>, <span>12495492.50</span>); 
<span>insert</span> <span>into</span> t <span>values</span>(<span>'</span><span>200407</span><span>'</span>,<span>5761</span>,<span>'</span><span>G</span><span>'</span>, <span>7987050.65</span>); 
<span>insert</span> <span>into</span> t <span>values</span>(<span>'</span><span>200407</span><span>'</span>,<span>5761</span>,<span>'</span><span>J</span><span>'</span>, <span>5723215.28</span>); 
<span>insert</span> <span>into</span> t <span>values</span>(<span>'</span><span>200407</span><span>'</span>,<span>5762</span>,<span>'</span><span>G</span><span>'</span>, <span>6833096.68</span>); 
<span>insert</span> <span>into</span> t <span>values</span>(<span>'</span><span>200407</span><span>'</span>,<span>5762</span>,<span>'</span><span>J</span><span>'</span>, <span>6391201.44</span>); 
<span>insert</span> <span>into</span> t <span>values</span>(<span>'</span><span>200407</span><span>'</span>,<span>5763</span>,<span>'</span><span>G</span><span>'</span>, <span>9410815.91</span>); 
<span>insert</span> <span>into</span> t <span>values</span>(<span>'</span><span>200407</span><span>'</span>,<span>5763</span>,<span>'</span><span>J</span><span>'</span>, <span>8076677.41</span>); 
<span>insert</span> <span>into</span> t <span>values</span>(<span>'</span><span>200407</span><span>'</span>,<span>5764</span>,<span>'</span><span>G</span><span>'</span>, <span>6456433.23</span>); 
<span>insert</span> <span>into</span> t <span>values</span>(<span>'</span><span>200407</span><span>'</span>,<span>5764</span>,<span>'</span><span>J</span><span>'</span>, <span>6987660.53</span>); 
<span>insert</span> <span>into</span> t <span>values</span>(<span>'</span><span>200407</span><span>'</span>,<span>5765</span>,<span>'</span><span>G</span><span>'</span>, <span>14000101.20</span>); 
<span>insert</span> <span>into</span> t <span>values</span>(<span>'</span><span>200407</span><span>'</span>,<span>5765</span>,<span>'</span><span>J</span><span>'</span>, <span>12301780.20</span>); 
<span>insert</span> <span>into</span> t <span>values</span>(<span>'</span><span>200408</span><span>'</span>,<span>5761</span>,<span>'</span><span>G</span><span>'</span>, <span>8085170.84</span>); 
<span>insert</span> <span>into</span> t <span>values</span>(<span>'</span><span>200408</span><span>'</span>,<span>5761</span>,<span>'</span><span>J</span><span>'</span>, <span>6050611.37</span>); 
<span>insert</span> <span>into</span> t <span>values</span>(<span>'</span><span>200408</span><span>'</span>,<span>5762</span>,<span>'</span><span>G</span><span>'</span>, <span>6854584.22</span>); 
<span>insert</span> <span>into</span> t <span>values</span>(<span>'</span><span>200408</span><span>'</span>,<span>5762</span>,<span>'</span><span>J</span><span>'</span>, <span>6521884.50</span>); 
<span>insert</span> <span>into</span> t <span>values</span>(<span>'</span><span>200408</span><span>'</span>,<span>5763</span>,<span>'</span><span>G</span><span>'</span>, <span>9468707.65</span>); 
<span>insert</span> <span>into</span> t <span>values</span>(<span>'</span><span>200408</span><span>'</span>,<span>5763</span>,<span>'</span><span>J</span><span>'</span>, <span>8460049.43</span>); 
<span>insert</span> <span>into</span> t <span>values</span>(<span>'</span><span>200408</span><span>'</span>,<span>5764</span>,<span>'</span><span>G</span><span>'</span>, <span>6587559.23</span>); 
<span>insert</span> <span>into</span> t <span>values</span>(<span>'</span><span>200408</span><span>'</span>,<span>5764</span>,<span>'</span><span>J</span><span>'</span>, <span>7342135.86</span>); 
<span>insert</span> <span>into</span> t <span>values</span>(<span>'</span><span>200408</span><span>'</span>,<span>5765</span>,<span>'</span><span>G</span><span>'</span>, <span>14450586.63</span>); 
<span>insert</span> <span>into</span> t <span>values</span>(<span>'</span><span>200408</span><span>'</span>,<span>5765</span>,<span>'</span><span>J</span><span>'</span>, <span>12680052.38</span>); 
<span>commit</span>;
登入後複製

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

            

三、first_value()与last_value()：求最值对应的其他属性
问题、取出每月通话费最高和最低的两个地区。

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

<span>SELECT</span> BILL_MONTH, 
       AREA_CODE,
       <span>SUM</span>(LOCAL_FARE) LOCAL_FARE, 
       FIRST_VALUE(AREA_CODE) 
       <span>OVER</span>(PARTITION <span>BY</span> BILL_MONTH 
            <span>ORDER</span> <span>BY</span> <span>SUM</span>(LOCAL_FARE) <span>DESC</span> 
            ROWS <span>BETWEEN</span> UNBOUNDED PRECEDING <span>AND</span> UNBOUNDED FOLLOWING) FIRSTVAL, 
       LAST_VALUE(AREA_CODE) 
       <span>OVER</span>(PARTITION <span>BY</span> BILL_MONTH 
            <span>ORDER</span> <span>BY</span> <span>SUM</span>(LOCAL_FARE) <span>DESC</span> 
            ROWS <span>BETWEEN</span> UNBOUNDED PRECEDING <span>AND</span> UNBOUNDED FOLLOWING) LASTVAL 
  <span>FROM</span> T 
 <span>GROUP</span> <span>BY</span> BILL_MONTH, AREA_CODE 
 <span>ORDER</span> <span>BY</span> BILL_MONTH
登入後複製

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

运行结果：

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

   

四、rank(),dense_rank()与row_number()：求排序

rank，dense_rank，row_number函数为每条记录产生一个从1开始至n的自然数，n的值可能小于等于记录的总数。这3个函数的唯一区别在于当碰到相同数据时的排名策略。
①row_number：
row_number函数返回一个唯一的值，当碰到相同数据时，排名按照记录集中记录的顺序依次递增。
②dense_rank：
dense_rank函数返回一个唯一的值，当碰到相同数据时，此时所有相同数据的排名都是一样的。
③rank：
rank函数返回一个唯一的值，当碰到相同的数据时，此时所有相同数据的排名是一样的，同时会在最后一条相同记录和下一条不同记录的排名之间空出排名。

          

演示数据在Oracle自带的scott用户下：
1、rank()值相同时排名相同，其后排名跳跃不连续

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

<span>SELECT</span> <span>*</span> 
  <span>FROM</span> (<span>SELECT</span> DEPTNO, 
               RANK() <span>OVER</span>(PARTITION <span>BY</span> DEPTNO <span>ORDER</span> <span>BY</span> SAL <span>DESC</span>) RW, 
               ENAME,
               SAL
          <span>FROM</span> SCOTT.EMP) 
 <span>WHERE</span> RW <span> <span>4</span>;</span>
登入後複製

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

运行结果：

oracle分析函数技术详解（配上开窗函数over()）
2、dense_rank()值相同时排名相同，其后排名连续不跳跃

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

<span>SELECT</span> <span>*</span> 
  <span>FROM</span> (<span>SELECT</span> DEPTNO, 
               DENSE_RANK() <span>OVER</span>(PARTITION <span>BY</span> DEPTNO <span>ORDER</span> <span>BY</span> SAL <span>DESC</span>) RW, 
               ENAME,
               SAL
          <span>FROM</span> SCOTT.EMP) 
 <span>WHERE</span> RW <span> <span>4</span>;</span>
登入後複製

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

运行结果：

oracle分析函数技术详解（配上开窗函数over()）
3、row_number()值相同时排名不相等，其后排名连续不跳跃

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

<span>SELECT</span> <span>*</span> 
  <span>FROM</span> (<span>SELECT</span> DEPTNO, 
               ROW_NUMBER() <span>OVER</span>(PARTITION <span>BY</span> DEPTNO <span>ORDER</span> <span>BY</span> SAL <span>DESC</span>) RW, 
               ENAME,
               SAL
          <span>FROM</span> SCOTT.EMP) 
 <span>WHERE</span> RW <span> <span>4</span>;</span>
登入後複製

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

运行结果：

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

 

五、lag()与lead()：求之前或之后的第N行
lag和lead函数可以在一次查询中取出同一字段的前n行的数据和后n行的值。这种操作可以使用对相同表的表连接来实现，不过使用lag和lead有更高的效率。
lag(arg1,arg2,arg3)
第一个参数是列名，
第二个参数是偏移的offset，
第三个参数是超出记录窗口时的默认值。
  
举例如下：
SQL> select *  from kkk;                                         
                                                                 
        ID NAME                                                  
---------- --------------------                                  
         1 1name                                                 
         2 2name                                                 
         3 3name                                                 
         4 4name                                                 
         5 5name                                                 
SQL> select id,name,lag(name,1,0) over(order by id) from kkk;
                                                                 
        ID NAME                 LAG(NAME,1,0)OVER(ORDERBYID)     
---------- -------------------- ----------------------------     
         1 1name                0                                
         2 2name                1name                            
         3 3name                2name                            
         4 4name                3name                            
         5 5name                4name

SQL> select id,name,lead(name,1,0) over(order by id) from kkk;
                                                                 
        ID NAME                 LEAD(NAME,1,0)OVER(ORDERBYID)    
---------- -------------------- -----------------------------    
         1 1name                2name                            
         2 2name                3name                            
         3 3name                4name                            
         4 4name                5name                            
         5 5name                0

SQL> select id,name,lead(name,2,0) over(order by id) from kkk;                                                                                                              
        ID NAME                 LEAD(NAME,2,0)OVER(ORDERBYID)    
---------- -------------------- -----------------------------    
         1 1name                3name                            
         2 2name                4name                            
         3 3name                5name                            
         4 4name                0                                
         5 5name                0 
SQL> select id,name,lead(name,1,'linjiqin') over(order by id) from kkk;                                 
                                                                                 
        ID NAME                 LEAD(NAME,1,'ALSDFJLASDJFSAF')                   
---------- -------------------- ------------------------------                   
         1 1name                2name                                            
         2 2name                3name                                            
         3 3name                4name                                            
         4 4name                5name                                            
         5 5name                linjiqin  

---------------------------------------------------------------------------------------

   

六、rollup()与cube()：排列组合分组
1)、group by rollup(a, b, c)：
首先会对(a、b、c)进行group by，
然后再对(a、b)进行group by，
其后再对(a)进行group by，
最后对全表进行汇总操作。

     

2)、group by cube(a, b, c)：
则首先会对(a、b、c)进行group by，
然后依次是(a、b)，(a、c)，(a)，(b、c)，(b)，(c)，
最后对全表进行汇总操作。

   

1、生成演示数据：
Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.1.0
Connected as ds_trade
 
SQL> conn system/oracle as sysdba
Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.3.0
Connected as SYS
 
SQL> create table scott.t as select * from dba_indexes;
 
Table created
 
 
SQL> connect scott/oracle
Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.3.0
Connected as scott
 
SQL>

    

2、普通group by体验
sql> select owner, index_type, status, count(*) from t where owner like 'SY%' group by owner, index_type, status;

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

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

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

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

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

  

七、max(),min(),sun()与avg()：求移动的最值总和与平均值
问题：计算出各个地区连续3个月的通话费用的平均数(移动平均值)

 

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

<span>SELECT</span> AREA_CODE, 
       BILL_MONTH,
       LOCAL_FARE,
       <span>SUM</span>(LOCAL_FARE) <span>OVER</span>(PARTITION <span>BY</span> AREA_CODE 
                            <span>ORDER</span> <span>BY</span> TO_NUMBER(BILL_MONTH) 
                            RANGE <span>BETWEEN</span> <span>1</span> PRECEDING <span>AND</span> <span>1</span> FOLLOWING) "3month_sum", 
       <span>AVG</span>(LOCAL_FARE) <span>OVER</span>(PARTITION <span>BY</span> AREA_CODE 
                            <span>ORDER</span> <span>BY</span> TO_NUMBER(BILL_MONTH) 
                            RANGE <span>BETWEEN</span> <span>1</span> PRECEDING <span>AND</span> <span>1</span> FOLLOWING) "3month_avg", 
       <span>MAX</span>(LOCAL_FARE) <span>OVER</span>(PARTITION <span>BY</span> AREA_CODE 
                            <span>ORDER</span> <span>BY</span> TO_NUMBER(BILL_MONTH) 
                            RANGE <span>BETWEEN</span> <span>1</span> PRECEDING <span>AND</span> <span>1</span> FOLLOWING) "3month_max", 
       <span>MIN</span>(LOCAL_FARE) <span>OVER</span>(PARTITION <span>BY</span> AREA_CODE 
                            <span>ORDER</span> <span>BY</span> TO_NUMBER(BILL_MONTH) 
                            RANGE <span>BETWEEN</span> <span>1</span> PRECEDING <span>AND</span> <span>1</span> FOLLOWING) "3month_min" 
  <span>FROM</span> (<span>SELECT</span> T.AREA_CODE, T.BILL_MONTH, <span>SUM</span>(T.LOCAL_FARE) LOCAL_FARE 
          <span>FROM</span> T 
         <span>GROUP</span> <span>BY</span> T.AREA_CODE, T.BILL_MONTH)
登入後複製

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

运行结果：

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

  

问题：求各地区按月份累加的通话费

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

<span>SELECT</span> AREA_CODE, 
       BILL_MONTH,
       LOCAL_FARE,
       <span>SUM</span>(LOCAL_FARE) <span>OVER</span>(PARTITION <span>BY</span> AREA_CODE 
                            <span>ORDER</span> <span>BY</span> BILL_MONTH <span>ASC</span>) "last_sum_value" 
  <span>FROM</span> (<span>SELECT</span> T.AREA_CODE, T.BILL_MONTH, <span>SUM</span>(T.LOCAL_FARE) LOCAL_FARE 
          <span>FROM</span> T 
         <span>GROUP</span> <span>BY</span> T.AREA_CODE, T.BILL_MONTH) 
 <span>ORDER</span> <span>BY</span> AREA_CODE, BILL_MONTH
登入後複製

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

运行结果：

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

 

--------------------------------------------------------------------------
Blog：http://www.cnblogs.com/linjiqin/
J2EE、Android、Linux、Oracle QQ交流群：142463980、158560018(满)

另见：《Oracle分析函数ROW_NUMBER()|RANK()|LAG()使用详解》

本網站聲明
本文內容由網友自願投稿，版權歸原作者所有。本站不承擔相應的法律責任。如發現涉嫌抄襲或侵權的內容，請聯絡admin@php.cn

熱AI工具

Undresser.AI Undress

Undresser.AI Undress

人工智慧驅動的應用程序，用於創建逼真的裸體照片

AI Clothes Remover

AI Clothes Remover

用於從照片中去除衣服的線上人工智慧工具。

Undress AI Tool

Undress AI Tool

免費脫衣圖片

Clothoff.io

Clothoff.io

AI脫衣器

Video Face Swap

Video Face Swap

使用我們完全免費的人工智慧換臉工具，輕鬆在任何影片中換臉！

顯示更多

熱門文章

<🎜>：種植花園 - 完整的突變指南
3 週前 By DDD
<🎜>：泡泡膠模擬器無窮大 - 如何獲取和使用皇家鑰匙
3 週前 By 尊渡假赌尊渡假赌尊渡假赌
如何修復KB5055612無法在Windows 10中安裝？
3 週前 By DDD
北端：融合系統，解釋
3 週前 By 尊渡假赌尊渡假赌尊渡假赌
Mandragora：巫婆樹的耳語 - 如何解鎖抓鉤
3 週前 By 尊渡假赌尊渡假赌尊渡假赌
顯示更多

熱工具

記事本++7.3.1

記事本++7.3.1

好用且免費的程式碼編輯器

SublimeText3漢化版

SublimeText3漢化版

中文版，非常好用

禪工作室 13.0.1

禪工作室 13.0.1

強大的PHP整合開發環境

Dreamweaver CS6

Dreamweaver CS6

視覺化網頁開發工具

SublimeText3 Mac版

SublimeText3 Mac版

神級程式碼編輯軟體(SublimeText3)

顯示更多

熱門話題

Java教學
1665
14
CakePHP 教程
1423
52
Laravel 教程
1321
25
PHP教程
1269
29
C# 教程
1249
24
顯示更多
Related knowledge
oracle打不開怎麼辦 oracle打不開怎麼辦 Apr 11, 2025 pm 10:06 PM

Oracle 打不開的解決辦法包括：1. 啟動數據庫服務；2. 啟動監聽器；3. 檢查端口衝突；4. 正確設置環境變量；5. 確保防火牆或防病毒軟件未阻止連接；6. 檢查服務器是否已關閉；7. 使用 RMAN 恢復損壞的文件；8. 檢查 TNS 服務名稱是否正確；9. 檢查網絡連接；10. 重新安裝 Oracle 軟件。

oracle游標關閉怎麼解決 oracle游標關閉怎麼解決 Apr 11, 2025 pm 10:18 PM

解決 Oracle 游標關閉問題的方法包括：使用 CLOSE 語句顯式關閉游標。在 FOR UPDATE 子句中聲明游標，使其在作用域結束後自動關閉。在 USING 子句中聲明游標，使其在關聯的 PL/SQL 變量關閉時自動關閉。使用異常處理確保在任何異常情況下關閉游標。使用連接池自動關閉游標。禁用自動提交，延遲游標關閉。

oracle怎麼循環創建游標 oracle怎麼循環創建游標 Apr 12, 2025 am 06:18 AM

Oracle 中，FOR LOOP 循環可動態創建游標， 步驟為：1. 定義游標類型；2. 創建循環；3. 動態創建游標；4. 執行游標；5. 關閉游標。示例：可循環創建游標，顯示前 10 名員工姓名和工資。

oracle日誌寫滿怎麼辦 oracle日誌寫滿怎麼辦 Apr 12, 2025 am 06:09 AM

Oracle 日誌文件寫滿時，可採用以下解決方案：1）清理舊日誌文件；2）增加日誌文件大小；3）增加日誌文件組；4）設置自動日誌管理；5）重新初始化數據庫。在實施任何解決方案前，建議備份數據庫以防數據丟失。

oracle視圖怎麼導出 oracle視圖怎麼導出 Apr 12, 2025 am 06:15 AM

可以通過 EXP 實用程序導出 Oracle 視圖：登錄 Oracle 數據庫。啟動 EXP 實用程序，指定視圖名稱和導出目錄。輸入導出參數，包括目標模式、文件格式和表空間。開始導出。使用 impdp 實用程序驗證導出。

HDFS配置CentOS需要哪些步驟 HDFS配置CentOS需要哪些步驟 Apr 14, 2025 pm 06:42 PM

在CentOS系統上搭建Hadoop分佈式文件系統（HDFS）需要多個步驟，本文提供一個簡要的配置指南。一、前期準備安裝JDK:在所有節點上安裝JavaDevelopmentKit(JDK)，版本需與Hadoop兼容。可從Oracle官網下載安裝包。環境變量配置:編輯/etc/profile文件，設置Java和Hadoop的環境變量，使系統能夠找到JDK和Hadoop的安裝路徑。二、安全配置：SSH免密登錄生成SSH密鑰:在每個節點上使用ssh-keygen命令

甲骨文在商業世界中的作用 甲骨文在商業世界中的作用 Apr 23, 2025 am 12:01 AM

Oracle不僅是數據庫公司，還是雲計算和ERP系統的領導者。 1.Oracle提供從數據庫到雲服務和ERP系統的全面解決方案。 2.OracleCloud挑戰AWS和Azure，提供IaaS、PaaS和SaaS服務。 3.Oracle的ERP系統如E-BusinessSuite和FusionApplications幫助企業優化運營。

oracle數據庫怎麼停止 oracle數據庫怎麼停止 Apr 12, 2025 am 06:12 AM

要停止 Oracle 數據庫，請執行以下步驟：1. 連接到數據庫；2. 優雅關機數據庫（shutdown immediate）；3. 完全關機數據庫（shutdown abort）。

See all articles