        继续扫文档的过程中，看到几个函数，功能上有相似（相反）的地方，于是做了一个比较，文中例子摘自Analytic functions by Example。

FIRST,LAST,FIRST_VALUE,LAST_VALUE

   FIRST/LAST函数即可用作聚合函数，也可用作分析函数。在某种场景下，我们将对查询结果的每一个分组做一个排序，将这个分组分成若干等级，每个等级相当与又构成了一个新的分组，其中包含若干记录。当需要对这些等级中最底（最高）等级的记录进行运算时就可以利用FIRST（LAST）函数。

语法
      aggregate_funciton() KEEP (DENSE_RANK FIRST ORDER BY expes)
      OVER(query_patition_clause)
      其中：
      1. aggregate_funciton指定了需要对满足条件的记录进行的运算，合法的聚合   函数包括： MIN, MAX, SUM, AVG, COUNT, VARIANCE, 或STDDEV。
      2. ORDER BY指明了分级标准。

       例如，下面例子选取部门10，20的员工信息，对每一个部门的记录按雇佣日期升序排序，取其第一档次的记录集计算评价薪金。即是部门10中第一等级的记录有2条，计算评价值是就是(2450＋5000)/2=3725。这里使用了分析函数的语法，即每个分组（由部门号确定）中的每个窗口运行一次FIRST函数，FIRST/LAST函数OVER子句中只能初选分组语句，所以窗口默认为第一行到当前窗口，记过都是一样的。

SELECT empno, deptno, TO_CHAR(hiredate,'YYYY') HIRE_YR, sal,
TRUNC(
AVG(sal) KEEP (DENSE_RANK FIRST
ORDER BY TO_CHAR(hiredate,'YYYY') )
OVER (PARTITION BY deptno)
     ) AVG_SAL_YR1_HIRE
FROM emp
WHERE deptno IN (20, 10)
ORDER BY deptno, empno, HIRE_YR;

     EMPNO     DEPTNO HIRE        SAL AVG_SAL_YR1_HIRE
---------- ---------- ---- ---------- ----------------
      7782         10 1981       2450             3725
      7839         10 1981       5000             3725
      7934         10 1982       1300             3725
      7369         20 1980        800              800
      7566         20 1981       2975              800
      7788         20 1982       3000              800
      7876         20 1983       1100              800
      7902         20 1981       3000              800

8 rows selected.

FIRST_VALUE/LAST_VALUE只能用作分析函数，获取每个窗口的第一个（最后一个）记录值。
FIRST_VALUE(expr) OVER(analytic_clause)

SELECT empno, deptno, hiredate - FIRST_VALUE(hiredate)
OVER (PARTITION BY deptno ORDER BY hiredate) DAY_GAP
FROM emp
WHERE deptno IN (20, 30)
ORDER BY deptno, DAY_GAP;

     EMPNO     DEPTNO    DAY_GAP
---------- ---------- ----------
      7369         20          0
      7566         20        106
      7902         20        351
      7788         20        722
      7876         20        756
      7499         30          0
      7521         30          2
      7698         30         70
      7844         30        200
      7654         30        220
      7900         30        286

11 rows selected.

:END

ITPub博客

几个聚合/分析函数的比较<一>

最新文章