本文共 6056 字，大约阅读时间需要 20 分钟。
Oracle 12c 之分析函数— FIRST_VALUE
FIRST_VALUE是一个分析函数，它返回一个有序的值集合中的第一个值。如果集合中的第一个值为NULL，则函数将返回NULL，除非指定忽略NULL。
下面我们来看看如何使用这个分析函数：
以Oracle 12c Sample中的HR用户为例，查找部门编号为90部门的员工的最低工资：
SELECT employee_id, last_name, salary, hire_date, FIRST_VALUE(last_name) OVER (ORDER BY salary ASC ROWS UNBOUNDED PRECEDING) AS fv FROM (SELECT * FROM employees WHERE department_id = 90 ORDER BY hire_date);
SQL> set linesize 200;SQL> set pagesize 999;SQL> set timing on;SQL> SELECT employee_id, last_name, salary, hire_date, 2 FIRST_VALUE(last_name) 3 OVER (ORDER BY salary ASC ROWS UNBOUNDED PRECEDING) AS fv 4 FROM (SELECT * FROM employees 5 WHERE department_id = 90 6 ORDER BY hire_date);EMPLOYEE_ID LAST_NAME SALARY HIRE_DATE FV----------- -------------------------------------------------- ---------- -------------- -------------------------------------------------- 102 De Haan 17000 13-1月 -01 De Haan 101 Kochhar 17000 21-9月 -05 De Haan 100 King 24000 17-6月 -03 De Haan
我们发现在查询结果集中，存在两个工资为 17000 的雇员，员工编号分别为 102和101，但是为什么显示的顺序为 102在前，而101在后呢？因为在查询中，我们按照 hire_date的ASC（升序）进行了排序。
改写一下查询的SQL，如下：
SELECT employee_id, last_name, salary, hire_date, FIRST_VALUE(last_name) OVER (ORDER BY salary ASC ROWS UNBOUNDED PRECEDING) AS fv FROM (SELECT * FROM employees WHERE department_id = 90 ORDER by hire_date DESC);
SQL> SELECT employee_id, last_name, salary, hire_date, 2 FIRST_VALUE(last_name) 3 OVER (ORDER BY salary ASC ROWS UNBOUNDED PRECEDING) AS fv 4 FROM (SELECT * FROM employees 5 WHERE department_id = 90 6 ORDER by hire_date DESC);EMPLOYEE_ID LAST_NAME SALARY HIRE_DATE FV----------- -------------------------------------------------- ---------- -------------- -------------------------------------------------- 101 Kochhar 17000 21-9月 -05 Kochhar 102 De Haan 17000 13-1月 -01 Kochhar 100 King 24000 17-6月 -03 Kochhar已用时间: 00: 00: 00.01
这样，查询结果集对于工资相同的 101号员工和102号员工按照hire_date的降序进行了排序显示。
仔细查看下面的两条查询SQL语句，无论子查询中是否按照hire_date进行排序ASC，或者DESC，显示的结果集中101号和102号员工的顺序都是一致的：
SQL>SQL> SELECT employee_id, last_name, salary, hire_date, 2 FIRST_VALUE(last_name) 3 OVER (ORDER BY salary ASC, employee_id ROWS UNBOUNDED PRECEDING) AS fv 4 FROM (SELECT * FROM employees 5 WHERE department_id = 90 6 ORDER BY hire_date);EMPLOYEE_ID LAST_NAME SALARY HIRE_DATE FV----------- -------------------------------------------------- ---------- -------------- -------------------------------------------------- 101 Kochhar 17000 21-9月 -05 Kochhar 102 De Haan 17000 13-1月 -01 Kochhar 100 King 24000 17-6月 -03 Kochhar已用时间: 00: 00: 00.74SQL>SQL> SELECT employee_id, last_name, salary, hire_date, 2 FIRST_VALUE(last_name) 3 OVER (ORDER BY salary ASC, employee_id ROWS UNBOUNDED PRECEDING) AS fv 4 FROM (SELECT * FROM employees 5 WHERE department_id = 90 6 ORDER BY hire_date DESC);EMPLOYEE_ID LAST_NAME SALARY HIRE_DATE FV----------- -------------------------------------------------- ---------- -------------- -------------------------------------------------- 101 Kochhar 17000 21-9月 -05 Kochhar 102 De Haan 17000 13-1月 -01 Kochhar 100 King 24000 17-6月 -03 Kochhar已用时间: 00: 00: 00.01SQL>
区别在哪里？
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.
再看下面的两条查询SQL语句：
SQL>SQL> SELECT employee_id, last_name, salary, hire_date, 2 FIRST_VALUE(last_name) 3 OVER (ORDER BY salary ASC RANGE UNBOUNDED PRECEDING) AS fv 4 FROM (SELECT * FROM employees 5 WHERE department_id = 90 6 ORDER BY hire_date);EMPLOYEE_ID LAST_NAME SALARY HIRE_DATE FV----------- -------------------------------------------------- ---------- -------------- -------------------------------------------------- 102 De Haan 17000 13-1月 -01 De Haan 101 Kochhar 17000 21-9月 -05 De Haan 100 King 24000 17-6月 -03 De Haan已用时间: 00: 00: 00.06SQL>SQL> SELECT employee_id, last_name, salary, hire_date, 2 FIRST_VALUE(last_name) 3 OVER (ORDER BY salary ASC RANGE UNBOUNDED PRECEDING) AS fv 4 FROM (SELECT * FROM employees 5 WHERE department_id = 90 6 ORDER BY hire_date DESC);EMPLOYEE_ID LAST_NAME SALARY HIRE_DATE FV----------- -------------------------------------------------- ---------- -------------- -------------------------------------------------- 102 De Haan 17000 13-1月 -01 De Haan 101 Kochhar 17000 21-9月 -05 De Haan 100 King 24000 17-6月 -03 De Haan已用时间: 00: 00: 00.01SQL>
找到区别在哪里？
想想为什么？
这个例子中，返回了工资相同的两名员工的记录（工资都为17000），即两名员工的工资并列相同。FIRST_VALUE函数返回的值不是绝对值1条，而是2条。
转载地址：http://wftdi.baihongyu.com/