当前位置: 代码迷 >> 综合 >> Oracle 12c 之分析函数— LAST_VALUE
  详细解决方案

Oracle 12c 之分析函数— LAST_VALUE

热度:13   发布时间:2024-01-15 19:57:09.0

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>