oracle分析函数(三)——keep函数

一、keep函数介绍

keep是Oracle下的另一个分析函数，他的用法不同于通过over关键字指定的分析函数，可以用于这样一种场合下：取同一个分组下以某个字段排序后，对指定字段取最小或最大的那个值。

从这个前提出发，我们可以看到其实这个目标通过一般的row_number分析函数也可以实现，即指定rn=1。但是，该函数无法实现同时获取最大和最小值。或者说用first_value和last_value，结合row_number实现，但是该种方式需要多次使用分析函数，而且还需要套一层SQL。于是出现了keep。

语法：

min | max(column1) keep (dense_rank first | last order by column2) over (partion by column3);

最前是聚合函数，可以是min、max、avg、sum。。。
column1为要计算的列；
dense_rank first，dense_rank last为keep 函数的保留属性，表示分组、排序结果集中第一个、最后一个；

解释：返回按照column3分组后，按照column2排序的结果集中第一个或最后一个最小值或最大值column1。

二、keep函数分析：

SQL> desc t
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 X                                                  VARCHAR2(10)
 Y                                                  DATE
 Z                                                  NUMBER

SQL> select x,to_char(y,'yyyy-mm') y,z from t
  2  order by x,y,z
  3  /

X          Y                Z
---------- ------- ----------
a          2011-02        111
a          2011-02        222
a          2011-02        888
a          2011-03        333
a          2011-04        555
a          2011-04        666
b          2011-02        111
b          2011-02        222
b          2011-03        333
b          2011-04        555

10 rows selected.
SQL>

下面，我们来使用keep函数来看一些例子。

SQL> select x, 
  2         min(z) keep(dense_rank first order by trunc(y,'mm')) first_min,
  3         min(z) keep(dense_rank last order by trunc(y,'mm')) last_min,
  4         max(z) keep(dense_rank first order by trunc(y,'mm')) first_max,
  5         max(z) keep(dense_rank last order by trunc(y,'mm')) last_max
  6  from t
  7  group by x
  8  /

X           FIRST_MIN   LAST_MIN  FIRST_MAX   LAST_MAX
---------- ---------- ---------- ---------- ----------
a                 111        555        888        666
b                 111        555        222        555

这是显示的效果。
我们来分析一下，SQL里面的group by 是作用于聚会函数的，可以这么理解一下。还有，这里我们头脑里应该有这么一个概念，排序即分组。

结合结果集与keep函数里order by 子句，我们来看一下表中的数据，然后把表里的数据进行如下划分，以X为维度，按值a进行划分：
a 2011-02 111
a 2011-02 222
a 2011-02 888
————————————— 这些数据划分为第一组。
因为order by 是按日期（截止到月）进行排序的，上面的日期都一样。
a 2011-03 333
—————————————- 这些数据划分为第二组。
a 2011-04 555
a 2011-04 666
—————————————- 这些数据划分为第三组。
以X为维度，按值b进行划分：
b 2011-02 111
b 2011-02 222
—————————————– 第一组
b 2011-03 333
—————————————- 第二组
b 2011-04 555
—————————————- 第三组

好，有了上述的划分，我们就不难理解SQL输出的结果集了。下面我们来分析一下SQL中输入的值。结合SQL

min(z) keep(dense_rank first order by trunc(y,’mm’)) first_min 该子句用来显示，以a为维度，以y为序，显示第一组（因为keep函数里指定是的first子句）中的最小值，所以，它显示为111（结合上述的划分结果来看）。
min(z) keep(dense_rank last order by trunc(y,’mm’)) last_min 该子句用来显示，以a为维度，以y为序，显示最后一组（因为keep函数里指定是的last子句）中的最小值，所以，这显示为555。

下面，我们来再分析一下以X为b的结果。
我们来看一下x=b的那一行，last_min与last_max的值一样。这是因为x为b的最后一组，它只有一行数据，对于一行数据而言，它的最大值与最小值肯定是同一个值了，所以last_min与last_max一样。

使用其他聚合函数也非常好理解了，比如我使用sum函数。

SQL> select x, 
  2         sum(z) keep(dense_rank first order by trunc(y,'mm')) first_sum,
  3         sum(z) keep(dense_rank last order by trunc(y,'mm'))  last_sum
  4  from t
  5  group by x
  6  /

X           FIRST_SUM   LAST_SUM
---------- ---------- ----------
a                1221       1221
b                 333        555

三、实例

1、按照某字段分组、然后排序、去最值
创建表、插入数据

create table tx2(id1 int ,id2 int,id3 int);

insert into tx2(id1,id2,id3) values(1,111,1);
insert into tx2(id1,id2,id3) values(1,222,1);
insert into tx2(id1,id2,id3) values(1,333,2);
insert into tx2(id1,id2,id3) values(1,444,3);
insert into tx2(id1,id2,id3) values(2,555,1);
insert into tx2(id1,id2,id3) values(2,666,2);
insert into tx2(id1,id2,id3) values(2,777,3);

查询：

SQL> select * from tx2;

       ID1	  ID2	     ID3
---------- ---------- ----------
	 1	  111	       1
	 1	  222	       1
	 1	  333	       2
	 1	  444	       3
	 2	  555	       1
	 2	  666	       2
	 2	  777	       3

7 rows selected.

按照ID1分组,ID3排序后，第一个最小的ID2：

select t.id1,t.id2,t.id3,min(t.id2)keep(dense_rank first order by t.id3)over(partition by t.id1) from tx2 t;

       ID1	  ID2	     ID3 MIN(T.ID2)KEEP(DENSE_RANKFIRSTORDERBYT.ID3)OVER(PARTITIONBYT.ID1)
---------- ---------- ---------- -----------------------------------------------------------------
	 1	  111	       1							       111
	 1	  222	       1							       111
	 1	  333	       2							       111
	 1	  444	       3							       111
	 2	  555	       1							       555
	 2	  666	       2							       555
	 2	  777	       3							       555

7 rows selected.

2、统计部门最高、低工资

SQL> SELECT t.empno,t.ename,t.mgr,t.sal,t.deptno  FROM emp t  ORDER BY t.sal,  t.deptno;  

     EMPNO ENAME	     MGR	SAL	DEPTNO
---------- ---------- ---------- ---------- ----------
      7369 SMITH	    7902	800	    20
      7900 JAMES	    7698	950	    30
      7876 ADAMS	    7788       1100	    20
      7521 WARD 	    7698       1250	    30
      7654 MARTIN	    7698       1250	    30
      7934 MILLER	    7782       1300	    10
      7844 TURNER	    7698       1500	    30
      7499 ALLEN	    7698       1600	    30
      7782 CLARK	    7839       2450	    10
      7698 BLAKE	    7839       2850	    30
      7566 JONES	    7839       2975	    20

     EMPNO ENAME	     MGR	SAL	DEPTNO
---------- ---------- ---------- ---------- ----------
      7788 SCOTT	    7566       3000	    20
      7902 FORD 	    7566       3000	    20
      7839 KING 		       5000	    10


14 rows selected.

现在要查询表中工资最高的部门号的最大最小值,工资最低的部门号的最大最小值：

SQL> SELECT MIN(t.deptno) KEEP(DENSE_RANK FIRST ORDER BY t.sal) a,
MAX(t.deptno) KEEP(DENSE_RANK FIRST ORDER BY t.sal) b,
MIN(t.deptno) KEEP(DENSE_RANK LAST ORDER BY t.sal) c,
MAX(t.deptno) KEEP(DENSE_RANK LAST ORDER BY t.sal) d
  5  FROM emp t;  

	 A	    B	       C	  D
---------- ---------- ---------- ----------
	20	   20	      10	 10

加上over,对每一行记录做计算，看看效果：

SQL> SELECT t.empno,t.ename,t.mgr,t.sal,t.deptno,
MIN(t.deptno) KEEP(DENSE_RANK FIRST ORDER BY t.sal)over() a,
MAX(t.deptno) KEEP(DENSE_RANK FIRST ORDER BY t.sal)over() b,
MIN(t.deptno) KEEP(DENSE_RANK LAST ORDER BY t.sal)over() c,
MAX(t.deptno) KEEP(DENSE_RANK LAST ORDER BY t.sal)over() d
  6  FROM emp t;  

     EMPNO ENAME	     MGR	SAL	DEPTNO		A	   B	      C 	 D
---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
      7369 SMITH	    7902	800	    20	       20	  20	     10 	10
      7499 ALLEN	    7698       1600	    30	       20	  20	     10 	10
      7521 WARD 	    7698       1250	    30	       20	  20	     10 	10
      7566 JONES	    7839       2975	    20	       20	  20	     10 	10
      7654 MARTIN	    7698       1250	    30	       20	  20	     10 	10
      7698 BLAKE	    7839       2850	    30	       20	  20	     10 	10
      7782 CLARK	    7839       2450	    10	       20	  20	     10 	10
      7788 SCOTT	    7566       3000	    20	       20	  20	     10 	10
      7839 KING 		       5000	    10	       20	  20	     10 	10
      7844 TURNER	    7698       1500	    30	       20	  20	     10 	10
      7876 ADAMS	    7788       1100	    20	       20	  20	     10 	10

     EMPNO ENAME	     MGR	SAL	DEPTNO		A	   B	      C 	 D
---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
      7900 JAMES	    7698	950	    30	       20	  20	     10 	10
      7902 FORD 	    7566       3000	    20	       20	  20	     10 	10
      7934 MILLER	    7782       1300	    10	       20	  20	     10 	10

14 rows selected.

下面对每一个mgr求最大(最小)工资的部门号的最大(最小)值：

SQL> SELECT t.empno,t.ename,t.mgr,t.sal,t.deptno,
MIN(t.deptno) KEEP(DENSE_RANK FIRST ORDER BY t.sal)over(PARTITION BY t.mgr) a,
MAX(t.deptno) KEEP(DENSE_RANK FIRST ORDER BY t.sal)over(PARTITION BY t.mgr) b,
MIN(t.deptno) KEEP(DENSE_RANK LAST ORDER BY t.sal)over(PARTITION BY t.mgr) c,
MAX(t.deptno) KEEP(DENSE_RANK LAST ORDER BY t.sal)over(PARTITION BY t.mgr) d
  6  FROM emp t;  

     EMPNO ENAME	     MGR	SAL	DEPTNO		A	   B	      C 	 D
---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
      7902 FORD 	    7566       3000	    20	       20	  20	     20 	20
      7788 SCOTT	    7566       3000	    20	       20	  20	     20 	20
      7900 JAMES	    7698	950	    30	       30	  30	     30 	30
      7499 ALLEN	    7698       1600	    30	       30	  30	     30 	30
      7521 WARD 	    7698       1250	    30	       30	  30	     30 	30
      7844 TURNER	    7698       1500	    30	       30	  30	     30 	30
      7654 MARTIN	    7698       1250	    30	       30	  30	     30 	30
      7934 MILLER	    7782       1300	    10	       10	  10	     10 	10
      7876 ADAMS	    7788       1100	    20	       20	  20	     20 	20
      7698 BLAKE	    7839       2850	    30	       10	  10	     20 	20
      7566 JONES	    7839       2975	    20	       10	  10	     20 	20

     EMPNO ENAME	     MGR	SAL	DEPTNO		A	   B	      C 	 D
---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
      7782 CLARK	    7839       2450	    10	       10	  10	     20 	20
      7369 SMITH	    7902	800	    20	       20	  20	     20 	20
      7839 KING 		       5000	    10	       10	  10	     10 	10

14 rows selected.