oracle keep 使用详解

我会带着你远行 2021-09-26 14:58 347阅读 0赞

*  语法
    
     *  摘自官方文档的语法：
        
         *  例子
            
             *  聚合函数
             *  分析函数
     *  应用场景
        
         *  使用示例
         *  效率解释
         *  可用范围
     *  总结

# 语法 #

## 摘自官方文档的语法： ##

![这里写图片描述][70]

> FIRST and LAST are very similar functions.

由于first与last函数只有一个顺序的差别，本文将只讲解first情况下的。

文档中对语法的解释：

> FIRST and LAST are very similar functions. Both are aggregate and analytic functions that operate on a set of values from a set of rows that rank as the FIRST or LAST with respect to a given sorting specification. If only one row ranks as FIRST or LAST, then the aggregate operates on the set with only one element.

解释：它会返回排序之后为first的部分。first的部分可以是一行，也可以是多行（这也是与first\_value函数最大的区别）。

> If you omit the OVER clause, then the FIRST and LAST functions are treated as aggregate functions. You can use these functions as analytic functions by specifying the OVER clause. The query\_partition\_clause is the only part of the OVER clause valid with these functions. If you include the OVER clause but omit the query\_partition\_clause, then the function is treated as an analytic function, but the window defined for analysis is the entire table.

解释：如果省略over，first将作为聚合函数。可以添加over从句使其成为分析函数。over从句中将只能使用分组从句（如果用排序将直接报错）。如果用了over并省略分组从句，函数将作用于全表（over从句的一般特点）。

先建一张测试表

create table nayi_first_180410 as select cast('a' as varchar2(10)) name, 1 dept, 2500 salary, 100 bonus from dual union all select 'b' , 2, 3000, 500 from dual union all select 'c' , 2, 3000, 50 from dual union all select 'd' , 2, 5000, 600 from dual union all select 'e' , 3, 4000, 800 from dual union all select 'f' , 3, 4200, 800 from dual ;
    
    select*from nayi_first_180410;

<table> 
 <thead> 
  <tr> 
   <th>NAME</th> 
   <th>DEPT</th> 
   <th>SALARY</th> 
   <th>BONUS</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>a</td> 
   <td>1</td> 
   <td>2500</td> 
   <td>100</td> 
  </tr> 
  <tr> 
   <td>b</td> 
   <td>2</td> 
   <td>3000</td> 
   <td>500</td> 
  </tr> 
  <tr> 
   <td>c</td> 
   <td>2</td> 
   <td>3000</td> 
   <td>50</td> 
  </tr> 
  <tr> 
   <td>d</td> 
   <td>2</td> 
   <td>5000</td> 
   <td>600</td> 
  </tr> 
  <tr> 
   <td>e</td> 
   <td>3</td> 
   <td>4000</td> 
   <td>800</td> 
  </tr> 
  <tr> 
   <td>f</td> 
   <td>3</td> 
   <td>4200</td> 
   <td>800</td> 
  </tr> 
 </tbody> 
</table>

### 例子 ###

#### 聚合函数 ####

获取bouns最大的行中最大的salary

select max(t.salary) keep(dense_rank first order by t.bonus desc) max from nayi_first_180410 t;

#### 分析函数 ####

获取bouns最大的行中salary最大值  
获取每组中bouns最大的行中salary最大值  
对bouns最大行的salary求和

select t.dept, t.salary, t.bonus, max(t.salary) keep(dense_rank first order by t.bonus desc) over() for_all, max(t.salary) keep(dense_rank first order by t.bonus desc) over(partition by t.dept) for_dept, sum(t.salary) keep(dense_rank first order by t.bonus desc) over() sum from nayi_first_180410 t;

<table> 
 <thead> 
  <tr> 
   <th>DEPT</th> 
   <th>SALARY</th> 
   <th>BONUS</th> 
   <th>FOR_ALL</th> 
   <th>FOR_DEPT</th> 
   <th>SUM</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>1</td> 
   <td>2500</td> 
   <td>100</td> 
   <td>4200</td> 
   <td>2500</td> 
   <td>8200</td> 
  </tr> 
  <tr> 
   <td>2</td> 
   <td>3000</td> 
   <td>500</td> 
   <td>4200</td> 
   <td>5000</td> 
   <td>8200</td> 
  </tr> 
  <tr> 
   <td>2</td> 
   <td>3000</td> 
   <td>50</td> 
   <td>4200</td> 
   <td>5000</td> 
   <td>8200</td> 
  </tr> 
  <tr> 
   <td>2</td> 
   <td>5000</td> 
   <td>600</td> 
   <td>4200</td> 
   <td>5000</td> 
   <td>8200</td> 
  </tr> 
  <tr> 
   <td>3</td> 
   <td>4000</td> 
   <td>800</td> 
   <td>4200</td> 
   <td>4200</td> 
   <td>8200</td> 
  </tr> 
  <tr> 
   <td>3</td> 
   <td>4200</td> 
   <td>800</td> 
   <td>4200</td> 
   <td>4200</td> 
   <td>8200</td> 
  </tr> 
 </tbody> 
</table>

从sum列中可以很清晰的看出是按照dense\_rank查出的，它可以是多行。

## 应用场景 ##

> When you need a value from the first or last row of a sorted group, but the needed value is not the sort key, the FIRST and LAST functions eliminate the need for self-joins or views and enable better performance.

解释：从排好序的组中获取first rank的行，并且所取的列不是排序用的列。在这种情况下，使用first函数可以替代自连接或是视图的方法，并且可以获得更好的性能。

### 使用示例 ###

简单点说就是以B列做排序，并取B列最大的行所对应的A列。正如上一个例子所写的那样。

再扩展一下。以任意规则排序，并取first行所对应的指定列。比如这样

select t.dept, t.salary, t.bonus, max(t.salary) keep(dense_rank first order by t.bonus + t.salary, mod(t.bonus, 2), t.bonus / t.salary nulls last) over() max from nayi_first_180410 t;

这个排序并没有实际意义，我想说的是，它可以作用于任何合法的order语句，而不是限定于某个列。

### 效率解释 ###

至于文档中所说的提升效率，我们可以通过改写sql的方法来观察一下。

--作为聚合函数求最大bonus对应的salary
    select max(t.salary) keep(dense_rank first order by t.bonus desc) max from nayi_first_180410 t;
    
    --通过视图方法求最大bonus对应的salary
    select max(t.salary) from (select t.salary, t.bonus, dense_rank() over(order by t.bonus desc) dense_rank from nayi_first_180410 t) t where t.dense_rank = 1;
    
    --通过自连接方法求最大bonus对应的salary
    select max(t1.salary) from nayi_first_180410 t1 where t1.bonus = (select max(v1.bonus) from nayi_first_180410 v1) ;
    
    ------------------------------------------------------------------
    
    --作为分析函数求每个dept最大bonus行所对应的salary
    select t.*, max(t.salary) keep(dense_rank first order by t.bonus desc) over(partition by t.dept) max from nayi_first_180410 t;
    
    --通过视图方法求每个dept最大bonus行所对应的salary
    select t1.name, t1.dept, t1.salary, t1.bonus, max(decode(t1.dense_rank, 1, t1.salary, null)) over(partition by t1.dept) max from (select t1.*, dense_rank() over(partition by t1.dept order by t1.bonus desc) dense_rank from nayi_first_180410 t1) t1;
    
    --通过自连接方法求每个dept最大bonus行所对应的salary
    select t1.name, t1.dept, t1.salary, t1.bonus, t2.salary max from nayi_first_180410 t1, (select t2.dept, t2.bonus, max(t2.salary) salary, dense_rank() over(partition by t2.dept order by t2.bonus desc) dense_rank from nayi_first_180410 t2 group by t2.dept, t2.bonus) t2 where t1.dept = t2.dept and t2.dense_rank = 1 order by t1.name ;

前三个sql结果集

后三个sql结果集

<table> 
 <thead> 
  <tr> 
   <th>NAME</th> 
   <th>DEPT</th> 
   <th>SALARY</th> 
   <th>BONUS</th> 
   <th>MAX</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>a</td> 
   <td>1</td> 
   <td>2500</td> 
   <td>100</td> 
   <td>2500</td> 
  </tr> 
  <tr> 
   <td>b</td> 
   <td>2</td> 
   <td>3000</td> 
   <td>500</td> 
   <td>5000</td> 
  </tr> 
  <tr> 
   <td>c</td> 
   <td>2</td> 
   <td>3000</td> 
   <td>50</td> 
   <td>5000</td> 
  </tr> 
  <tr> 
   <td>d</td> 
   <td>2</td> 
   <td>5000</td> 
   <td>600</td> 
   <td>5000</td> 
  </tr> 
  <tr> 
   <td>e</td> 
   <td>3</td> 
   <td>4000</td> 
   <td>800</td> 
   <td>4200</td> 
  </tr> 
  <tr> 
   <td>f</td> 
   <td>3</td> 
   <td>4200</td> 
   <td>800</td> 
   <td>4200</td> 
  </tr> 
 </tbody> 
</table>

这是两组完全等效的sql。

虽然有很多种其他的改写方法，但是执行计划都是大同小异。

很明显，使用first函数将只需要一次table access full与一次window sort。使用视图将在此基础上多一层view。使用自连接的方法将至少多一次table access full。因此使用first函数将更有效率优势。在代码量与可读性上也有一些优势。

### 可用范围 ###

> The aggregate\_function argument is any one of the MIN, MAX, SUM, AVG, COUNT, VARIANCE, or STDDEV functions. It operates on values from the rows that rank either FIRST or LAST. If only one row ranks as FIRST or LAST, then the aggregate operates on a singleton (nonaggregate) set.

规定了可用范围。只有min, max, sum, avg, count, variance, stddev 这些聚合函数可以接keep使用。也就是说像 first\_value,last\_value,lead,lag,percent\_rank这样的函数是不可用的。应用范围其实比较窄。

令人意外的是，它竟然可以与wm\_concat连用。这也导致我对它的适用范围有点搞不懂了，求大神指点。

select wmsys.wm_concat(t.name) keep(dense_rank first order by t.bonus desc) wm_concat from nayi_first_180410 t;

<table> 
 <thead> 
  <tr> 
   <th>WM_CONCAT</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>e,f</td> 
  </tr> 
 </tbody> 
</table>

## 总结 ##

*  以任意规则排序，并取first rank行所对应的指定列情况下使用。
 *  在正确使用的情况下通常会有更好的效率。
 *  与min, max, sum, avg, count, variance, stddev这些函数组合使用

**注：文中全部引用来自官方文档**  
**[https://docs.oracle.com/cd/E11882\_01/server.112/e41084/functions065.htm\#SQLRF00641][https_docs.oracle.com_cd_E11882_01_server.112_e41084_functions065.htm_SQLRF00641]**

[70]: /images/20210923/82a1d3f74ac040e6a23b5f03367fac22.png
[https_docs.oracle.com_cd_E11882_01_server.112_e41084_functions065.htm_SQLRF00641]: https://docs.oracle.com/cd/E11882_01/server.112/e41084/functions065.htm#SQLRF00641