[size=-1]FIRST_VALUE 功能描述:返回组中数据窗口的第一个值。有些类似于SQL Server中的first(),但用法完全不一样,而功能也强大一些。
SQL> create table EMP(
id number,
name varchar2(20),
age number,
POS VARCHAR2(20)
);
SQL> alter table EMP
add constraint emp_PK_id primary key (ID);
...
SQL> SELECT * FROM EMP;
| ID | NAME | AGE | POS | | 1 | A | 25 | PM | | 2 | B | 20 | PM | | 3 | C | 30 | PL |
| 4 | D | 35 | PL | | 5 | E | 36 | PL | 需求:求出每种职位的平均年龄,同时列出每种职位的年龄最大和最小者。
SQL> SELECT DISTINCT
FIRST_VALUE(NAME) OVER
(PARTITION BY POS ORDER BY AGE DESC)
AS MAXAGE_NAME
,FIRST_VALUE(NAME) OVER
(PARTITION BY POS ORDER BY AGE ASC)
AS MINAGE_NAME
,AVG(AGE) OVER
(PARTITION BY POS)
AS AVG_AGE
,POS
FROM EMP
ORDER BY POS
| MAXAGE_NAME | MINAGE_NAME | AVG_AGE | POS |
| E | C | 33.66666667 | PL |
| A | B | 22.5 | PM |
|
|