Oracle Lead/Last函数

xuanxi

　　Oracle Lead/Last函数
1.   Syntax

　　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子句，Fisrt/Last被当做聚合函数使用，见示例1；含over关键字但没query_partition_clause，Fisrt/Last被当做分析函数使用，分析的窗口是整个表，见示例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).
2.   说明
min(job_id) keep(dense_rank first order bycount(job_id) desc) over(partition by department_id)　　语义：按每个部门查找工种人数最多的工种。
　　min：例如某个部门，人数占用最多的工种有两个，例如某个部门A工种3人，B工种3人，这时用min返回的值就是A，相应的用max返回的值就是B。若你想用AVG这类函数，则会报错，invalid number。其实作用就是防止返回两个值，也不是网上说的，完全没有意义（max和min结果是不一样的）。
　　keep：关键字。
　　dense_rank：排序操作，换成row_number试了下，直接抛出异常。
　　over：即是分析函数分析的窗口，省略over及其后面语句，则整个结果聚合（aggregate）
3.   示例
1.    示例1
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;
　　返回的结果集如下
　　SA_REP  AC_ACCOUNT     SA_REP  SA_REP
　　发现：整个表的聚合，也验证了max和min的结果有时不一致。
2.    示例2
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
　　返回每个部门的工种人数最多的工种，注意部门ID为空也返回了，这个是boss。
3.    几种写法比较
1.       method 1
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
　　总结：1. boss这个部门，即部门为空，没有返回；
　　2.某个部门工种人数最多的，有两个工种，不得不再次进行聚合。
　　3.代码较为繁琐。
2.       method 2
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
　　总结：1.用row_number排序，然后使用外查询过滤row_number为1的；
　　2.boss这个人包含的结果返回。
3.       method 3
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分组，我们只关心department_id, job_id，SQL进行调整下
　　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
　　总结：1.boss这个部门返回；
　　2.没有涉及子查询，代码简洁；
　　3.仔细对比，method2和method3的结果，还是稍有差异