ORACLE分析函数实战(Analytical Functions)_PART6

接PART5：

1.3.2.4 FIRST/LAST
  FIRST/LAST可以作为组函数和分析函数，组函数不带OVER,分析函数带OVER,它必须结合KEEP关键字，KEEP关键字就是起到一个语义的作用：说明按照指定的排序规则找到TOP 1或BOTTOM 1，因为是DENSE_RANK,所以TOP1和BOTTOM1可能会有多行，然后外层用组函数对TOP 1或BOTTOM 1的行再处理，DENSE_RANK用的是OLMPLIC RANK规则。
  没有OVER就是一般组函数，那么窗口是所有行，函数对只做一次操作，返回一行值。有OVER指定PARTITION,则按照分析函数规则，分析函数应用于所有行（这个所有行可能是分组，HAVING等之后的结果，根据分析函数的计算规则得出所有行），每行所属分区内的值是一样的，有OVER没有PARTITION,那么分析函数应用于所有行，每行的值都是一样的，这个根据分析函数的规则很容易知道。
  它的OVER只允许PARTITION子句，排序规则只能在KEEP里。它主要用于对一个结果或表达式排序，而外层组函数计算另一个值。

  FIRST/LAST的计算顺序是：
  如果有OVER，则按照指定分区规则，对分个分区内行按照KEEP中的ORDER BY排序，计算TOP 1或BOTTOM 1，然后外层用组函数求值，没有OVER就是对所有行(可能是分组后的行)，计算一次聚合值，返回一行。

  FIRST和LAST允许我们对A列排名，但是组函数操作可以对B列进行，避免了子查询和自连接，从而提高效率。

  First和LAST可以用于组函数和分析函数。如果没有over()那么就是使用组函数。其中语法图中的aggregate_function可以使用MIN, MAX, SUM, AVG, COUNT,VARIANCE, or STDDEV函数。


  如果返回的结果集只有一行，那么first和last都取那一行。使用keep关键字来限制组函数只返回首行或末行的值。
  当已排序组中第一行或最后一行的值不是排序键值时，为了获得更好的性能，first和last函数不会进行自连接或产生视图。

SQL> select * from t1;

 

        ID      DTIME         GB

---------- ---------- ----------

         1     201001          3

         1     201002          2

         1     201003

         1     201004

         1     201005          1

         2     201001          2

         2     201002          3

         2     201003

 

8 rows selected

 

SQL>

SQL> select id,dtime,min(gb) keep (dense_rank first order by dtime) over(partition by id),gb

  2  from t1;

 

        ID      DTIME MIN(GB)KEEP(DENSE_RANKFIRSTORD         GB

---------- ---------- ------------------------------ ----------

         1     201001                              3          3

         1     201002                              3          2

         1     201003                              3

         1     201004                              3

         1     201005                              3          1

         2     201001                              2          2

         2     201002                              2          3

         2     201003                              2

 

8 rows selected

 
上面的结果就是先分区，每个分区内按照dtime排序，然后找第1个dtime值，因为都是唯一的，所以keep对每行只找找到当前行，所以最小值，最大值还是和当前行一样。

 
理解KEEP的使用：

  keep就是保存了first,last操作的结果集，然后外界使用组函数对结果集操作（因为这个结果集对每个分区或没有分区可能会有多行，因为DENSE_RANK）。如下例子：

select * from emp_test order by dept_id,emp_no;

  假设我们现在要做这样的操作：按部门(dept_id)分区，然后按salary升序排列，使用dense_rank取最大salary的排名，然后分别找出这个排名结果集中最小的emp_no和最大的emp_no?为什么有最小和最大的emp_no呢？因为dense_rank对相同salary的排序结果一样，所以会存在多行的情况，看下面的语句：

select emp_no,dept_id,salary,

         min(emp_no) keep (dense_rank last order by salary) over(partition by dept_id) min_last,

         max(emp_no) keep (dense_rank last order by salary) over(partition by dept_id) max_last,

         min(emp_no) keep (dense_rank first order by salary) over(partition by dept_id) min_first,

         max(emp_no) keep (dense_rank first order by salary) over(partition by dept_id) max_first

         from emp_test

         order by dept_id,emp_no;

  我们分析一下，比如emp_no=1的行，min_last=3,max_last=11,min_first=1,max_first=1,因为dense_rank last order by salary的结果有两个，emp_no=3和emp_no=11的salary都为5000最大，所以min_last=3,max_last=11。而dense_rank first order by salary只有一行，所以min_first和max_first结果一样。其他类似分析。由此我们知道，keep其实是在over分析函数之后执行的，通过dense_rank排名，然后结合first和last函数保存了一个对应窗口的中间结果集，外界的组函数就是对这个对应窗口的中间结果集进行聚合操作。

  keep是返回组内排名第1的行的分组值，因为dense_rank排名第1的可能有多行，而不是返回到当前行为止的分组值，下面的第1行的值并不是10，要注意，因为这里的排名第1只有333那行。

SQL> SELECT t.*,MIN(deptno) KEEP(DENSE_RANK FIRST ORDER BY sal) OVER(PARTITION BY mgr) MIN FROM t WHERE  mgr IN (222);

 

EMPNO ENAME JOB      MGR HIREDATE       SAL      COMM DEPTNO        MIN

----- ---------- --------- ----- ----------- --------- --------- ------ ----------

  111 aa        xx         222 2010/10/29  10000.00               10         50

  333 cc        xx         222 2010/10/29    800.00               50         50

  222 bb       xx         222 2010/10/29  10000.00               20         50

下面的例子，分别说明first和last在组函数和分析函数中的使用。

1.返回平均薪水最小的最小部门编号和平均薪水最大的最大部门编号。（使用组函数，因为平均薪水最小值相等的部门可能有多个，最大平均薪水同样，所以外面才有聚合函数）

select

      min(dept_id) keep (dense_rank first order by avg(salary))  "min_dept",

      max(dept_id) keep (dense_rank last order by avg(salary)) "max_dept"

    from s_emp

group by dept_id;

     结果如下：

min_dept  max_dept
  ----------------------------
     43             50
分析:先按照dept_id进行分组，排序规则为按照薪水平均值升序排列，然后取首行或末行结果集，最后应用组函数，取得最小和最大的dept_id。

2.返回每个部门的最低薪水和最高薪水（使用组函数）

select dept_id,

      min(salary) keep (dense_rank first order by salary)  "min_salary",

      max(salary) keep (dense_rank last order by salary) "max_salary"

    from s_emp

group by dept_id; 

结果如下：

分析：按照dept_id分组，排序规则为按照员工薪水排序，取首行或末行，然后求出最低薪水和最高薪水。（这里只是举例，没有什么实际意义，可以不用first和last）。

 

3.下面的例子是对每行应用用分析函数，返回员工所属部门，薪水，部门最高薪水和最低薪水。（使用分析函数）。

select dept_id,

       salary,

      min(salary) keep (dense_rank first order by salary) over(partition by dept_id)  "min_salary",

      max(salary) keep (dense_rank last order by salary) over(partition by dept_id)  "max_salary"

    from s_emp;

结果如下：

分析：使用了分析函数之后，对组内进行操作。每行都返回结果集，这是与只使用组函数的区别之一。

4.看下keep和over的区别。
OVER是分析函数的表示，如果有OVER,keep是在OVER之后做的，根据OVER指定的分区规则来做。每个分区的值是一样的，当然，如果OVER里没有PARTITION,那么所有行的值是一样的，如果没有OVER,那么就是普通的聚合函数，只返回一行。

未完待续，见PART7：http://blog.chinaunix.net/uid-7655508-id-5834951.html