Oracle Lead/Last函数

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的结果，还是稍有差异