oracle keep 分析函数

配合使用的函数为聚合函数

可用范围

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这样的函数是不可用的。应用范围其实比较窄。

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

 

1、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从句的一般特点）。

例子
1、聚合函数

查询emp表中福利最大员工的最高工资

select max(sal) keep(dense_rank last order by comm desc nulls last) maxsalcomm from emp;--结果为：5000

 

2，分析函数

  查询emp表中各个组comm福利最大的员工工资最大值

  由于是分组统计，因此使用over开窗函数子句

select emp.*,max(sal) keep(dense_rank last order by comm desc nulls last) over(partition by deptno)  maxsalcomm from emp;

结果如下：

分析函数
获取comm最大的行中sal最大值 （null排最后）
获取每组中comm最大的行中sal最大值 （null排最后）
对comm最大行的sal求和，这里为了验证，我们让null排最前

select emp.*,max(sal) keep(dense_rank last order by comm desc nulls last) over() maxsalcomm,
max(sal) keep(dense_rank last order by comm desc nulls last) over(partition by deptno)  deptmaxsal,
sum(sal) keep(dense_rank first order by comm desc nulls first) over(partition by deptno)  deptcommsum from emp

结果如下：

 

应用场景

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.

如下是摘抄别人的：https://blog.csdn.net/nayi_224/article/details/79869802

解释：从排好序的组中获取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结果集

MAX
4200
后三个sql结果集

NAME    DEPT    SALARY    BONUS    MAX
a    1    2500    100    2500
b    2    3000    500    5000
c    2    3000    50    5000
d    2    5000    600    5000
e    3    4000    800    4200
f    3    4200    800    4200
这是两组完全等效的sql。

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

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