一、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函數分析：1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26SQL> 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函數來看一些例子。
1
2
3
4
5
6
7
8
9
10
11
12
13SQL> 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函數。
1
2
3
4
5
6
7
8
9
10
11SQL> 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、按照某字段分組、然後排序、去最值
建立表、插入資料
1
2
3
4
5
6
7
8
9create 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);
查詢：
1
2
3
4
5
6
7
8
9
10
11
12
13SQL> 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：
1
2
3
4
5
6
7
8
9
10
11
12
13select 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、統計部門最高、低工資
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24SQL> SELECT t.empno,t.ename,t.mgr,t.sal,t.deptno FROM emp t ORDER BY t.sal, t.deptno;
EMPNO ENAME MGRSALDEPTNO
---------- ---------- ---------- ---------- ----------
7369 SMITH 7902800 20
7900 JAMES 7698950 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 MGRSALDEPTNO
---------- ---------- ---------- ---------- ----------
7788 SCOTT 7566 3000 20
7902 FORD 7566 3000 20
7839 KING 5000 10
14 rows selected.
現在要查詢表中工資最高的部門号的最大最小值,工資最低的部門号的最大最小值 ：
1
2
3
4
5
6
7
8
9SQL> 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,對每一行記錄做計算，看看效果：
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28SQL> 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 MGRSALDEPTNOA B C D
---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
7369 SMITH 7902800 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 MGRSALDEPTNOA B C D
---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
7900 JAMES 7698950 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求最大(最小)工資的部門号的最大(最小)值 ：
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28SQL> 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 MGRSALDEPTNOA B C D
---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
7902 FORD 7566 3000 20 20 20 20 20
7788 SCOTT 7566 3000 20 20 20 20 20
7900 JAMES 7698950 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 MGRSALDEPTNOA B C D
---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
7782 CLARK 7839 2450 10 10 10 20 20
7369 SMITH 7902800 20 20 20 20 20
7839 KING 5000 10 10 10 10 10
14 rows selected.