Oracle FIRST_VALUE函数语法详解及应用实例
来源:互联网 发布:地理空间数据云ftp 编辑:程序博客网 时间:2024/06/05 09:18
查询语句中发现与主键匹配的机构名有多个,业务要求只显示排在最前面的一个。
试了group by,发现主查询返回记录数会狂增,用FIRST_VALUE后问题基本解决。
SQL语句与以下类似:
ACSNAS(SELECT DISTINCT cs.P_ID AS P_ID, css.SSN AS SSN, ci.HMS_ID AS HMS_ID, ci.HMS_ID AS HMS_ID, FIRST_VALUE(org.ORG_NAME) OVER (PARTITION BY cs.P_ID,css.SSN ORDER BY org.ORG_NAME) AS ACSN FROM HDDO.OA_V org, HDDO.CIA_V ci, HDDO.CSA_V cs, HDDO.CSSA_V css WHERE org.HO_ID=ci.HO_ID AND ci.HMS_ID=cs.HMS_ID AND ci.HMS_ID=css.HMS_ID AND ci.CONTACT_ROLE='ACSNL' AND ci.CONTACT_STATUS= 'ACTIVE' AND org.ETL_YN_DELETED='N' AND ci.ETL_YN_DELETED='N')
以下为Oracle Database SQL Language Reference中关于FIRST_VALUE函数的说明。
------------------------------------------------------------------------------------------------------------------------------------
FIRST_VALUE
Syntax
Purpose
FIRST_VALUE is an analytic function. It returns the first value in an ordered set of values. If the first value in the set is null, then the function returns NULL unless you
specify IGNORE NULLS. This setting is useful for data densification.
{RESPECT | IGNORE} NULLS determines whether null values of expr are included in or eliminated from the calculation. The default is RESPECT NULLS. If you specify IGNORE NULLS, then FIRST_VALUE returns the first non-null value in the set, or NULL if all values are null. Refer to "Using Partitioned Outer Joins: Examples" on page 19-52 for an example of data densification.
You cannot nest analytic functions by using FIRST_VALUE or any other analytic function for expr. However, you can use other built-in function expressions for expr.
Refer to "About SQL Expressions" on page 6-1 for information on valid forms of expr.
Examples
The following example selects, for each employee in Department 90, the name of the employee with the lowest salary.
SELECT department_id, last_name, salary,
FIRST_VALUE(last_name)
OVER (ORDER BY salary ASC ROWS UNBOUNDED PRECEDING) AS lowest_sal
FROM (SELECT * FROM employees
WHERE department_id = 90
ORDER BY employee_id)
ORDER BY last_name;
DEPARTMENT_ID LAST_NAME SALARY LOWEST_SAL
------------- ------------------------- ---------- -------------------------
90 De Haan 17000 Kochhar
90 King 24000 Kochhar
90 Kochhar 17000 Kochhar
See Also: "Analytic Functions" on page 5-11 for information on syntax, semantics, and restrictions, including valid forms of expr
Note: The two forms of this syntax have the same behavior. The top branch is the ANSI format, which Oracle recommends. The bottom branch is deprecated but is supported for backward compatibility.
FIRST_VALUE
( expr )
RESPECT
IGNORE
NULLS
( expr
RESPECT
IGNORE
NULLS
)
OVER ( analytic_clause )
FIRST_VALUE
The example illustrates the nondeterministic nature of the FIRST_VALUE function.
Kochhar and DeHaan have the same salary, so are in adjacent rows. Kochhar appears first because the rows returned by the subquery are ordered by employee_id.
However, if the rows returned by the subquery are ordered by employee_id in descending order, as in the next example, then the function returns a different value:
SELECT department_id, last_name, salary,
FIRST_VALUE(last_name)
OVER (ORDER BY salary ASC ROWS UNBOUNDED PRECEDING) AS fv
FROM (SELECT * FROM employees
WHERE department_id = 90
ORDER by employee_id DESC)
ORDER BY last_name;
DEPARTMENT_ID LAST_NAME SALARY FV
------------- ------------------------- ---------- -------------------------
90 De Haan 17000 De Haan
90 King 24000 De Haan
90 Kochhar 17000 De Haan
The following example shows how to make the FIRST_VALUE function deterministic by
ordering on a unique key.
SELECT department_id, last_name, salary, hire_date,
FIRST_VALUE(last_name)
OVER (ORDER BY salary ASC, hire_date ROWS UNBOUNDED PRECEDING) AS fv
FROM (SELECT * FROM employees
WHERE department_id = 90
ORDER BY employee_id DESC)
ORDER BY last_name;
DEPARTMENT_ID LAST_NAME SALARY HIRE_DATE FV
------------- --------------- ---------- --------- -------------------------
90 De Haan 17000 13-JAN-01 De Haan
90 King 24000 17-JUN-03 De Haan
90 Kochhar 17000 21-SEP-05 De Haan
When you use a logical offset (RANGE instead of ROWS), the function is deterministic.
When duplicates are found for the ORDER BY expression, the FIRST_VALUE is the lowest value of expr:
SELECT department_id, last_name, salary,
FIRST_VALUE(last_name)
OVER (ORDER BY salary ASC RANGE UNBOUNDED PRECEDING) AS lowest_sal
FROM (SELECT * FROM employees
WHERE department_id = 90
ORDER BY employee_id);
DEPARTMENT_ID LAST_NAME SALARY LOWEST_SAL
------------- ------------------------- ---------- -------------------------
90 De Haan 17000 De Haan
90 Kochhar 17000 De Haan
90 King 24000 De Haan
- Oracle FIRST_VALUE函数语法详解及应用实例
- Oracle Pivot函数语法详解及应用实例
- Oracle列转行函数 Listagg() 语法详解及应用实例
- Oracle INSTR函数(子字符串查询)语法详解及应用实例
- Oracle分析函数:First_Value
- Oracle分析函数之first_value和last_value 分析函数详解
- oracle函数整理---first_value函数
- oracle 分析函数 FIRST_VALUE、LAST_VALUE
- 【转】Oracle分析函数:First_Value
- Oracle minus用法详解及应用实例
- VLOOKUP函数的应用及实例详解
- Oracle 分析函数 Last_value和First_Value
- Excel数据分析与业务建模_第三章_引用函数INDEX(语法详解及应用实例)
- Excel数据分析与业务建模_第四章_匹配函数MATCH(语法详解及应用实例)
- Oracle 触发器语法及实例
- Oracle 触发器语法及实例
- Oracle 触发器语法及实例
- Oracle 触发器语法及实例
- java-堆、栈、方法区的区别
- android中SurfaceView的应用,一个贪吃蛇的小程序
- Win10自定义开始菜单磁贴背景颜色及图标
- 分享一个链接
- 切换飞行模式SIM联系人是否应该显示?
- Oracle FIRST_VALUE函数语法详解及应用实例
- Arch 下配置Android编译环境
- java(二)-抽象类接口
- BZOJ 4545: DQS的trie
- python---模拟登陆知乎
- [Android] InputMethodManager内存泄露现象及解决 输入法造成的内存泄露
- 2016前端开发技术巡礼
- OpenCV2.0.0移植到ARM9(三)(JZ2440----S3c2440)
- BZOJ1924: [Sdoi2010]所驼门王的宝藏