【開發篇sql】 分析函式（二） 行篩選相關的函式
2,行篩選相關的函式keep,first,last,first_value,last_value,lag,lead
先來看兩個查詢，一個實現的是部門內最早僱傭的員工中的工資最多與最小的值，一個是實現工資最高和最低的員工中入職時間最早或最晚的值：
查詢1：
SQL> select a.deptno,a.hiredate,a.sal,
2 min(a.sal) keep(dense_rank first order by hiredate desc) over(partition by a.deptno) first,
3 max(a.sal) keep(dense_rank last order by hiredate desc) over(partition by a.deptno) last
4 from scott.emp a;
DEPTNO HIREDATE SAL FIRST LAST
---------- --------- ---------- ---------- ----------
10 09-JUN-81 2450 1300 2450
10 17-NOV-81 5000 1300 2450
10 23-JAN-82 1300 1300 2450
20 02-APR-81 2000 1100 800
20 03-DEC-81 3000 1100 800
20 23-MAY-87 1100 1100 800
20 17-DEC-80 800 1100 800
20 19-APR-87 3000 1100 800
30 22-FEB-81 1250 950 1600
30 08-SEP-81 1500 950 1600
30 20-FEB-81 1600 950 1600
DEPTNO HIREDATE SAL FIRST LAST
---------- --------- ---------- ---------- ----------
30 03-DEC-81 950 950 1600
30 01-MAY-81 2850 950 1600
30 28-SEP-81 1250 950 1600
14 rows selected.
查詢2：
SQL> select a.deptno,a.sal,a.hiredate,
2 min(a.hiredate) keep(dense_rank first order by a.sal desc) over(partition by a.deptno) first,
3 max(a.hiredate) keep(dense_rank last order by a.sal desc) over(partition by a.deptno) last
4 from scott.emp a;
DEPTNO SAL HIREDATE FIRST LAST
---------- ---------- --------- --------- ---------
10 2450 09-JUN-81 17-NOV-81 23-JAN-82
10 5000 17-NOV-81 17-NOV-81 23-JAN-82
10 1300 23-JAN-82 17-NOV-81 23-JAN-82
20 2000 02-APR-81 03-DEC-81 17-DEC-80
20 3000 03-DEC-81 03-DEC-81 17-DEC-80
20 1100 23-MAY-87 03-DEC-81 17-DEC-80
20 800 17-DEC-80 03-DEC-81 17-DEC-80
20 3000 19-APR-87 03-DEC-81 17-DEC-80
30 1250 22-FEB-81 01-MAY-81 03-DEC-81
30 1500 08-SEP-81 01-MAY-81 03-DEC-81
30 1600 20-FEB-81 01-MAY-81 03-DEC-81
DEPTNO SAL HIREDATE FIRST LAST
---------- ---------- --------- --------- ---------
30 950 03-DEC-81 01-MAY-81 03-DEC-81
30 2850 01-MAY-81 01-MAY-81 03-DEC-81
30 1250 28-SEP-81 01-MAY-81 03-DEC-81
14 rows selected.
上面的例子中時間沒有完全相同的，那麼簡單修改一下，則可以更好的理解keep中的first和last與min和max的意義：
正對上面的查詢1來修改得到
查詢3：
SQL> select a.deptno,to_char(hiredate,'yyyy') hiredate,a.sal,
2 min(a.sal) keep(dense_rank first order by to_char(hiredate,'yyyy') desc) over(partition by a.deptno) first,
3 max(a.sal) keep(dense_rank last order by to_char(hiredate,'yyyy') desc) over(partition by a.deptno) last
4 from scott.emp a;
DEPTNO HIRE SAL FIRST LAST
---------- ---- ---------- ---------- ----------
10 1981 2450 1300 5000
10 1981 5000 1300 5000
10 1982 1300 1300 5000
20 1981 2000 1100 800
20 1981 3000 1100 800
20 1987 1100 1100 800
20 1980 800 1100 800
20 1987 3000 1100 800
30 1981 1250 950 2850
30 1981 1500 950 2850
30 1981 1600 950 2850
DEPTNO HIRE SAL FIRST LAST
---------- ---- ---------- ---------- ----------
30 1981 950 950 2850
30 1981 2850 950 2850
30 1981 1250 950 2850
14 rows selected.
first和last很容易理解。Dense_rank的特點上面的keep中的dense_rank first或者dense_rank last都可能會取到多行。
在hiredate修改為to_char(hiredate,'yyyy')以後，有了在同一年份入職的員工，那麼first其實會把第一組年份的員工行都取了，外層使用min(a.sal)或者max(a.sal)就是再對組中的員工求最大最小工錢值。
接下來再看看first_value和last_value,有四個查詢例子：
這個查詢按deptno開窗找第一個和最後一個sal：
SQL> select a.empno,a.deptno,to_char(hiredate,'yyyy') hiredate,a.sal,
2 first_value(a.sal) over(partition by a.deptno ) first,
3 last_value(a.sal) over(partition by a.deptno ) last
4 from scott.emp a;
EMPNO DEPTNO HIRE SAL FIRST LAST
---------- ---------- ---- ---------- ---------- ----------
7782 10 1981 2450 2450 1300
7839 10 1981 5000 2450 1300
7934 10 1982 1300 2450 1300
7566 20 1981 2000 2000 3000
7902 20 1981 3000 2000 3000
7876 20 1987 1100 2000 3000
7369 20 1980 800 2000 3000
7788 20 1987 3000 2000 3000
7521 30 1981 1250 1250 1250
7844 30 1981 1500 1250 1250
7499 30 1981 1600 1250 1250
EMPNO DEPTNO HIRE SAL FIRST LAST
---------- ---------- ---- ---------- ---------- ----------
7900 30 1981 950 1250 1250
7698 30 1981 2850 1250 1250
7654 30 1981 1250 1250 1250
14 rows selected.
下面的查詢在上面的基礎上增加了order by deptno語句：
SQL> select a.empno,a.deptno,to_char(hiredate,'yyyy') hiredate,a.sal,
2 first_value(a.sal) over(partition by a.deptno order by deptno) first,
3 last_value(a.sal) over(partition by a.deptno order by deptno) last
4 from scott.emp a;
EMPNO DEPTNO HIRE SAL FIRST LAST
---------- ---------- ---- ---------- ---------- ----------
7934 10 1982 1300 1300 5000
7782 10 1981 2450 1300 5000
7839 10 1981 5000 1300 5000
7369 20 1980 800 800 3000
7876 20 1987 1100 800 3000
7566 20 1981 2000 800 3000
7788 20 1987 3000 800 3000
7902 20 1981 3000 800 3000
7900 30 1981 950 950 2850
7654 30 1981 1250 950 2850
7521 30 1981 1250 950 2850
EMPNO DEPTNO HIRE SAL FIRST LAST
---------- ---------- ---- ---------- ---------- ----------
7844 30 1981 1500 950 2850
7499 30 1981 1600 950 2850
7698 30 1981 2850 950 2850
14 rows selected.
下面的查詢是按照sal升序排序後的，這樣每個部門下面按照sal排序了，每增加一條資料，都會取當前的first和last，因此可以看到每行的last都在變化。
SQL> select a.empno,a.deptno,to_char(hiredate,'yyyy') hiredate,a.sal,
2 first_value(a.sal) over(partition by a.deptno order by sal) first,
3 last_value(a.sal) over(partition by a.deptno order by sal) last
4 from scott.emp a;
EMPNO DEPTNO HIRE SAL FIRST LAST
---------- ---------- ---- ---------- ---------- ----------
7934 10 1982 1300 1300 1300
7782 10 1981 2450 1300 2450
7839 10 1981 5000 1300 5000
7369 20 1980 800 800 800
7876 20 1987 1100 800 1100
7566 20 1981 2000 800 2000
7788 20 1987 3000 800 3000
7902 20 1981 3000 800 3000
7900 30 1981 950 950 950
7654 30 1981 1250 950 1250
7521 30 1981 1250 950 1250
EMPNO DEPTNO HIRE SAL FIRST LAST
---------- ---------- ---- ---------- ---------- ----------
7844 30 1981 1500 950 1500
7499 30 1981 1600 950 1600
7698 30 1981 2850 950 2850
14 rows selected.
再修改一下，order by rowid，效果差不多：
SQL> select a.empno,a.deptno,to_char(hiredate,'yyyy') hiredate,a.sal,
2 first_value(a.sal) over(partition by a.deptno order by rowid) first,
3 last_value(a.sal) over(partition by a.deptno order by rowid) last
4 from scott.emp a;
EMPNO DEPTNO HIRE SAL FIRST LAST
---------- ---------- ---- ---------- ---------- ----------
7782 10 1981 2450 2450 2450
7839 10 1981 5000 2450 5000
7934 10 1982 1300 2450 1300
7369 20 1980 800 800 800
7566 20 1981 2000 800 2000
7788 20 1987 3000 800 3000
7876 20 1987 1100 800 1100
7902 20 1981 3000 800 3000
7499 30 1981 1600 1600 1600
7521 30 1981 1250 1600 1250
7654 30 1981 1250 1600 1250
EMPNO DEPTNO HIRE SAL FIRST LAST
---------- ---------- ---- ---------- ---------- ----------
7698 30 1981 2850 1600 2850
7844 30 1981 1500 1600 1500
7900 30 1981 950 1600 950
14 rows selected.
再來看lag和lead，分別用兩個sql來求部門內按照hiredate排序後的每個苦工上一個或者下一個的工錢值：
SQL> select a.empno,
2 a.deptno,
3 a.hiredate,
4 a.sal,
5 lag(sal, 1, 0) over(partition by a.deptno order by hiredate asc) pre_sal
6 from scott.emp a;
EMPNO DEPTNO HIREDATE SAL PRE_SAL
---------- ---------- --------- ---------- ----------
7782 10 09-JUN-81 2450 0
7839 10 17-NOV-81 5000 2450
7934 10 23-JAN-82 1300 5000
7369 20 17-DEC-80 800 0
7566 20 02-APR-81 2000 800
7902 20 03-DEC-81 3000 2000
7788 20 19-APR-87 3000 3000
7876 20 23-MAY-87 1100 3000
7499 30 20-FEB-81 1600 0
7521 30 22-FEB-81 1250 1600
7698 30 01-MAY-81 2850 1250
EMPNO DEPTNO HIREDATE SAL PRE_SAL
---------- ---------- --------- ---------- ----------
7844 30 08-SEP-81 1500 2850
7654 30 28-SEP-81 1250 1500
7900 30 03-DEC-81 950 1250
14 rows selected.
SQL> select a.empno,
2 a.deptno,
3 a.hiredate,
4 a.sal,
5 lead(sal, 1, 0) over(partition by a.deptno order by hiredate asc) next_sal
6 from scott.emp a;
EMPNO DEPTNO HIREDATE SAL NEXT_SAL
---------- ---------- --------- ---------- ----------
7782 10 09-JUN-81 2450 5000
7839 10 17-NOV-81 5000 1300
7934 10 23-JAN-82 1300 0
7369 20 17-DEC-80 800 2000
7566 20 02-APR-81 2000 3000
7902 20 03-DEC-81 3000 3000
7788 20 19-APR-87 3000 1100
7876 20 23-MAY-87 1100 0
7499 30 20-FEB-81 1600 1250
7521 30 22-FEB-81 1250 2850
7698 30 01-MAY-81 2850 1500
EMPNO DEPTNO HIREDATE SAL NEXT_SAL
---------- ---------- --------- ---------- ----------
7844 30 08-SEP-81 1500 1250
7654 30 28-SEP-81 1250 950
7900 30 03-DEC-81 950 0
14 rows selected.
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/16179598/viewspace-663169/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 【開發篇sql】 分析函式（一） 評級相關的函式SQL函式
- SQL 日期格式相關函式SQL函式
- 字串的相關函式字串函式
- 平凡的函式 線性篩積性函式函式
- sql優化用group by 函式代替分析函式SQL優化函式
- 正規表示式☞相關函式函式
- 物件及函式相關物件函式
- SAP PP相關函式函式
- Python 函式相關Python函式
- ORACLE函式介紹第六篇 著名函式之分析函式Oracle函式
- oracle的分析函式over 及開窗函式Oracle函式
- ORACLE單行函式與多行函式之二：字元函式示例Oracle函式字元
- 字串相關函式的實現字串函式
- ORACLE函式介紹第二篇 非著名函式之單值函式Oracle函式
- ES6 函式相關函式
- 3.3 數學相關函式函式
- 函式組：CSAP（BOM 相關）函式
- SQL函式之日期函式SQL函式
- Python資料分析--Numpy常用函式介紹(5)--Numpy中的相關性函式Python函式
- ORACLE函式介紹第七篇 非著名函式之分析函式Oracle函式
- [Oracle]高效的SQL語句之分析函式（一）（二）OracleSQL函式
- MYSQL 一個巧用字元函式巧用字元函式做資料篩選的題MySql字元函式
- 執行緒中的幾個退出相關函式執行緒函式
- Oracle聚合函式/分析函式Oracle函式
- oracle的分析函式over(Partition by...) 及開窗函式Oracle函式
- 第十三篇：成員函式與非成員函式的選擇函式
- Oracle分析函式七——分析函式案例Oracle函式
- 函式篇函式
- 【C語言】常用的字串函式及相關函式的自我實現C語言字串函式
- Sql 中的 left 函式、right 函式SQL函式
- PL/SQL單行函式和組函式詳解(轉)SQL函式
- Spark SQL 開窗函式SparkSQL函式
- ORACLE函式介紹第五篇 分析函式簡述Oracle函式
- SQL函式SQL函式
- ORACLE分析函式手冊二Oracle函式
- Oracle分析函式之開窗函式over()詳解Oracle函式
- 測開之函式進階· 第2篇《純函式》函式
- 測開之函式進階· 第4篇《匿名函式》函式