Oracle 12c 之分析函数— LAST_VALUE
LAST_VALUE也是一个分析函数,它返回一个有序的值集合中的最后一个值。
可以对比FIRST_VALUE函数。
我们来看看例子:
SQL> SELECT employee_id, last_name, salary, hire_date,2 LAST_VALUE(hire_date)3 OVER (ORDER BY salary DESC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED4 FOLLOWING) AS lv5 FROM (SELECT * FROM employees6 WHERE department_id = 907 ORDER BY hire_date);EMPLOYEE_ID LAST_NAME SALARY HIRE_DATE LV
----------- -------------------------------------------------- ---------- -------------- --------------100 King 24000 17-6月 -03 13-1月 -01101 Kochhar 17000 21-9月 -05 13-1月 -01102 De Haan 17000 13-1月 -01 13-1月 -01已用时间: 00: 00: 00.05
看看下面的查询与上面的查询有什么不同?
SQL>
SQL> SELECT employee_id, last_name, salary, hire_date,2 LAST_VALUE(hire_date)3 OVER (ORDER BY salary DESC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED4 FOLLOWING) AS lv5 FROM (SELECT * FROM employees6 WHERE department_id = 907 ORDER BY hire_date DESC);EMPLOYEE_ID LAST_NAME SALARY HIRE_DATE LV
----------- -------------------------------------------------- ---------- -------------- --------------100 King 24000 17-6月 -03 21-9月 -05102 De Haan 17000 13-1月 -01 21-9月 -05101 Kochhar 17000 21-9月 -05 21-9月 -05已用时间: 00: 00: 00.01
SQL>
虽然 101号员工和102号员工具有相同的工资(Salary均为17000),但是在查询中,子查询中使用hire_date列进行了排序处理,第二个SQL查询语句中,按照 hire_date的降序(DESC)进行了排序,所以在查询结果中,102号员工排在了101号员工之前。
再看下面的两条SQL查询,找出不同点:
SQL>
SQL> SELECT employee_id, last_name, salary, hire_date,2 LAST_VALUE(hire_date)3 OVER (ORDER BY salary DESC, employee_id ROWS BETWEEN UNBOUNDED PRECEDING4 AND UNBOUNDED FOLLOWING) AS lv5 FROM (SELECT * FROM employees6 WHERE department_id = 907 ORDER BY hire_date);EMPLOYEE_ID LAST_NAME SALARY HIRE_DATE LV
----------- -------------------------------------------------- ---------- -------------- --------------100 King 24000 17-6月 -03 13-1月 -01101 Kochhar 17000 21-9月 -05 13-1月 -01102 De Haan 17000 13-1月 -01 13-1月 -01已用时间: 00: 00: 00.01
SQL>
SQL> SELECT employee_id, last_name, salary, hire_date,2 LAST_VALUE(hire_date)3 OVER (ORDER BY salary DESC, employee_id ROWS BETWEEN UNBOUNDED PRECEDING4 AND UNBOUNDED FOLLOWING) AS lv5 FROM (SELECT * FROM employees6 WHERE department_id = 907 ORDER BY hire_date DESC);EMPLOYEE_ID LAST_NAME SALARY HIRE_DATE LV
----------- -------------------------------------------------- ---------- -------------- --------------100 King 24000 17-6月 -03 13-1月 -01101 Kochhar 17000 21-9月 -05 13-1月 -01102 De Haan 17000 13-1月 -01 13-1月 -01已用时间: 00: 00: 00.01
SQL>
By ordering within the function by both salary and the unique key employee_id, you can ensure the same result regardless of the ordering in the subquery.
再看RANGE子句的使用:
SQL>
SQL> SELECT employee_id, last_name, salary, hire_date,2 LAST_VALUE(hire_date)3 OVER (ORDER BY salary DESC RANGE BETWEEN UNBOUNDED PRECEDING AND4 UNBOUNDED FOLLOWING) AS lv5 FROM (SELECT * FROM employees6 WHERE department_id = 907 ORDER BY hire_date);EMPLOYEE_ID LAST_NAME SALARY HIRE_DATE LV
----------- -------------------------------------------------- ---------- -------------- --------------100 King 24000 17-6月 -03 21-9月 -05102 De Haan 17000 13-1月 -01 21-9月 -05101 Kochhar 17000 21-9月 -05 21-9月 -05已用时间: 00: 00: 00.01
SQL>
SQL> SELECT employee_id, last_name, salary, hire_date,2 LAST_VALUE(hire_date)3 OVER (ORDER BY salary DESC RANGE BETWEEN UNBOUNDED PRECEDING AND4 UNBOUNDED FOLLOWING) AS lv5 FROM (SELECT * FROM employees6 WHERE department_id = 907 ORDER BY hire_date DESC);EMPLOYEE_ID LAST_NAME SALARY HIRE_DATE LV
----------- -------------------------------------------------- ---------- -------------- --------------100 King 24000 17-6月 -03 21-9月 -05102 De Haan 17000 13-1月 -01 21-9月 -05101 Kochhar 17000 21-9月 -05 21-9月 -05已用时间: 00: 00: 00.00
SQL>
SQL>