Oracle Lead/Last函數(shù)
成都創(chuàng)新互聯(lián)成立于2013年，是專業(yè)互聯(lián)網(wǎng)技術(shù)服務(wù)公司，擁有項(xiàng)目做網(wǎng)站、成都網(wǎng)站設(shè)計(jì)網(wǎng)站策劃，項(xiàng)目實(shí)施與項(xiàng)目整合能力。我們以讓每一個(gè)夢(mèng)想脫穎而出為使命，1280元蓮花做網(wǎng)站,已為上家服務(wù),為蓮花各地企業(yè)和個(gè)人服務(wù),聯(lián)系電話:028-86922220
Purpose
FIRST and LAST are very similar functions.Both are aggregate and analytic functions that operate on a set of values froma set of rows that rank as the FIRST or LAST withrespect to a given sorting specification. If only one row ranks as FIRSTor LAST, then the aggregate operates on the set with only one element.
If you omit the OVERclause, then the FIRST and LAST functions are treated as aggregate functions. You can use thesefunctions as analytic functions by specifying the OVER clause. Thequery_partition_clause is the only part of the OVER clause valid with thesefunctions. If you include the OVER clause but omit thequery_partition_clause, then the function is treated as an analytic function, but the window defined for analysis is theentire table.
中文說明：省略over子句，F(xiàn)isrt/Last被當(dāng)做聚合函數(shù)使用，見示例1；含over關(guān)鍵字但沒query_partition_clause，F(xiàn)isrt/Last被當(dāng)做分析函數(shù)使用，分析的窗口是整個(gè)表，見示例2。
These functions take as an argument anynumeric data type or any nonnumeric data type that can be implicitly convertedto a numeric data type. The function returns the same data type as the numericdata type of the argument.
When you need a value from the first orlast row of a sorted group, but the needed value is not the sort key, the FIRSTand LAST functions eliminate the need for self-joins or views and enable betterperformance.
The aggregate_functionargument is any one of the MIN, MAX, SUM, AVG, COUNT, VARIANCE, or STDDEVfunctions. It operates on values from the rows thatrank either FIRST or LAST. If only one row ranks as FIRST or LAST, then theaggregate operates on a singleton (nonaggregate) set.
The KEEP keyword is for semantic clarity.It qualifies aggregate_function, indicating that only the FIRST or LAST valuesof aggregate_function will be returned.
DENSE_RANK FIRST or DENSE_RANK LASTindicates that Oracle Database will aggregate over only those rows with theminimum (FIRST) or the maximum (LAST) dense rank (also called olympic rank).
min(job_id) keep(dense_rank first order bycount(job_id) desc) over(partition by department_id)
語義：按每個(gè)部門查找工種人數(shù)最多的工種。
min：例如某個(gè)部門，人數(shù)占用最多的工種有兩個(gè)，例如某個(gè)部門A工種3人，B工種3人，這時(shí)用min返回的值就是A，相應(yīng)的用max返回的值就是B。若你想用AVG這類函數(shù)，則會(huì)報(bào)錯(cuò)，invalid number。其實(shí)作用就是防止返回兩個(gè)值，也不是網(wǎng)上說的，完全沒有意義（max和min結(jié)果是不一樣的）。
keep：關(guān)鍵字。
dense_rank：排序操作，換成row_number試了下，直接拋出異常。
over：即是分析函數(shù)分析的窗口，省略over及其后面語句，則整個(gè)結(jié)果聚合（aggregate）
select max(e.job_id) keep(dense_rank lastorder by count(job_id) desc), min(e.job_id) keep(dense_rank last order by count(job_id) desc), max(e.job_id) keep(dense_rank first order by count(job_id) desc), min(e.job_id) keep(dense_rank first order by count(job_id) desc) from employees e group by e.department_id, e.job_id;
返回的結(jié)果集如下
SA_REP AC_ACCOUNT SA_REP SA_REP
發(fā)現(xiàn)：整個(gè)表的聚合，也驗(yàn)證了max和min的結(jié)果有時(shí)不一致。
select distinct department_id, --count(job_id), min(job_id) keep(dense_rank first order by count(job_id) desc)over(partition by department_id) job_id from employees group by department_id, job_id order by 1;
分析窗口：以部門分組
返回結(jié)果集如下
1 10 AD_ASST
2 20 MK_MAN
3 30 PU_CLERK
4 40 HR_REP
5 50 SH_CLERK
6 60 IT_PROG
7 70 PR_REP
8 80 SA_REP
9 90 AD_VP
10 100 FI_ACCOUNT
11 110 AC_ACCOUNT
12 SA_REP
返回每個(gè)部門的工種人數(shù)最多的工種，注意部門ID為空也返回了，這個(gè)是boss。
with t as (select department_id, job_id, count(job_id)cnt from employees group by department_id, job_id) select department_id, max(job_id) --再次聚合 from t where (department_id, cnt) in (selectdepartment_id, max(cnt) from t group by department_id) group by department_id order by 1;
1 10 AD_ASST
2 20 MK_REP
3 30 PU_CLERK
4 40 HR_REP
5 50 ST_CLERK
6 60 IT_PROG
7 70 PR_REP
8 80 SA_REP
9 90 AD_VP
10 100 FI_ACCOUNT
11 110 AC_MGR
總結(jié)：1. boss這個(gè)部門，即部門為空，沒有返回；
2.某個(gè)部門工種人數(shù)最多的，有兩個(gè)工種，不得不再次進(jìn)行聚合。
3.代碼較為繁瑣。
select department_id, job_id from (select e.department_id, e.job_id, count(e.job_id), row_number() over(partition bydepartment_id order by count(job_id) desc) rk from employees e group by e.department_id, e.job_id) where rk = 1;
1 10 AD_ASST
2 20 MK_MAN
3 30 PU_CLERK
4 40 HR_REP
5 50 ST_CLERK
6 60 IT_PROG
7 70 PR_REP
8 80 SA_REP
9 90 AD_VP
10 100 FI_ACCOUNT
11 110 AC_ACCOUNT
12 SA_REP
總結(jié)：1.用row_number排序，然后使用外查詢過濾row_number為1的；
2.boss這個(gè)人包含的結(jié)果返回。
select /*distinct*/ department_id, count(job_id), min(job_id) keep(dense_rank first order by count(job_id) desc)over(partition by department_id) job_id from employees group by department_id, job_id order by 1;
1 10 1 AD_ASST
2 20 1 MK_MAN
3 20 1 MK_MAN
4 30 5 PU_CLERK
5 30 1 PU_CLERK
6 40 1 HR_REP
7 50 20 SH_CLERK
8 50 20 SH_CLERK
9 50 5 SH_CLERK
10 60 5 IT_PROG
11 70 1 PR_REP
12 80 5 SA_REP
13 80 29 SA_REP
14 90 1 AD_VP
15 90 2 AD_VP
16 100 5 FI_ACCOUNT
17 100 1 FI_ACCOUNT
18 110 1 AC_ACCOUNT
19 110 1 AC_ACCOUNT
20 1 SA_REP
這里按department_id, job_id分組，我們只關(guān)心department_id, job_id，SQL進(jìn)行調(diào)整下
tuneSQL
select distinct department_id, --count(job_id), min(job_id) keep(dense_rank first order by count(job_id) desc)over(partition by department_id) job_id from employees group by department_id, job_id order by 1;
1 10 AD_ASST
2 20 MK_MAN
3 30 PU_CLERK
4 40 HR_REP
5 50 SH_CLERK
6 60 IT_PROG
7 70 PR_REP
8 80 SA_REP
9 90 AD_VP
10 100 FI_ACCOUNT
11 110 AC_ACCOUNT
12 SA_REP
總結(jié)：1.boss這個(gè)部門返回；
2.沒有涉及子查詢，代碼簡(jiǎn)潔；
3.仔細(xì)對(duì)比，method2和method3的結(jié)果，還是稍有差異
重慶分公司
重慶分公司
