分析函数--FIRST_VALUE，LAST_VALUE，LAG，LEAD，ROW_NUMBER

一 ，FIRST_VALUE,LAST_VALUER的使用
1，FIRST_VALUE 是指返回组中数据窗口的第一个值
2，LAST_VALUE 是指返回组中数据窗口的最后一个值（注意点：不受排序的影响,后面发现是因为没有开窗导致的）

例子：
SELECT DEPARTMENT_ID,
FIRST_NAME || ' ' || LAST_NAME EMPLOYEE_NAME,
HIRE_DATE,
SALARY,
FIRST_VALUE(FIRST_NAME || ' ' || LAST_NAME || ':'|| SALARY) OVER(PARTITION BY DEPARTMENT_ID ORDER BY SALARY) AS LOWEST_SAL,
FIRST_VALUE(FIRST_NAME || ' ' || LAST_NAME || ':'|| SALARY) OVER(PARTITION BY DEPARTMENT_ID ORDER BY SALARY DESC) AS HIGHEST_SAL,
LAST_VALUE(FIRST_NAME || ' ' || LAST_NAME || ':'|| SALARY) OVER(PARTITION BY DEPARTMENT_ID ORDER BY SALARY) AS LAST_SAL,
LAST_VALUE(FIRST_NAME || ' ' || LAST_NAME || ':'|| SALARY) OVER(PARTITION BY DEPARTMENT_ID ORDER BY SALARY DESC) AS LAST_SAL_DESC
FROM HR.EMPLOYEES


上面的例子，并不能得到期待结果。
原因是没有指定开窗子句，
order by会加上一个默认窗口RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW.
正确的写法：
增加上指定窗口为所有行--ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING

SELECT DEPARTMENT_ID,
FIRST_NAME || ' ' || LAST_NAME EMPLOYEE_NAME,
HIRE_DATE,
SALARY,
FIRST_VALUE(FIRST_NAME || ' ' || LAST_NAME || ':'|| SALARY) OVER(PARTITION BY DEPARTMENT_ID ORDER BY SALARY) AS LOWEST_SAL,
FIRST_VALUE(FIRST_NAME || ' ' || LAST_NAME || ':'|| SALARY) OVER(PARTITION BY DEPARTMENT_ID ORDER BY SALARY DESC) AS HIGHEST_SAL,
LAST_VALUE(FIRST_NAME || ' ' || LAST_NAME || ':'|| SALARY) OVER(PARTITION BY DEPARTMENT_ID ORDER BY SALARY
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING） AS LAST_SAL,
LAST_VALUE(FIRST_NAME || ' ' || LAST_NAME || ':'|| SALARY) OVER(PARTITION BY DEPARTMENT_ID ORDER BY SALARY DESC
LAST_VALUE(FIRST_NAME || ' ' || LAST_NAME || ':'|| SALARY) OVER(PARTITION BY DEPARTMENT_ID ORDER BY SALARY DESC
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING） AS LAST_SAL_DESC
FROM HR.EMPLOYEES


二，LAG,LEAD函数的使用
1，LAG可以访问结果集中的其它行而不用进行自连接。它允许去处理游标，就好像游标
是一个数组一样。在给定组中可参考当前行之前的行，这样就可以从组中与当前行一起选择
以前的行。Offset 是一个正整数，其默认值为 1，若索引超出窗口的范围，就返回默认值（默
认返回的是组中第一行），其相反的函数是 LEAD
2,LEAD 与 LAG 相反，LEAD 可以访问组中当前行之后的行。Offset 是一个正整数，
其默认值为 1，若索引超出窗口的范围，就返回默认值（默认返回的是组中第一行）

例子：
SELECT FIRST_NAME || ' ' || LAST_NAME EMPLOYEE_NAME,
HIRE_DATE,
SALARY,
LAG(SALARY, 1, 0) OVER(ORDER BY HIRE_DATE ) AS 比当前前一个的薪酬,
LEAD(SALARY, 1, 0) OVER(ORDER BY HIRE_DATE ) AS 比当前后一个的薪酬
FROM HR.EMPLOYEES



三，ROW_NUMBE的使用
1，ROW_NUMBE是指 返回有序组中一行的偏移量，从而可用于按特定标准排序的行号。
也可以利用他排除重复的数据

例子：

SELECT DEPARTMENT_ID,
FIRST_NAME || ' ' || LAST_NAME EMPLOYEE_NAME,
EMPLOYEE_ID,
ROW_NUMBER() OVER(PARTITION BY DEPARTMENT_ID ORDER BY EMPLOYEE_ID) AS EMP_ID
FROM HR.EMPLOYEES