first_valueoverfirst_valueover.doc

Oracle分析函数：First_Value收藏 ? 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?????? ,POSFROM EMPORDER BY POS ??? MAXAGE_NAME MINAGE_NAME AVG_AGE POS E C 33PL A B 22.5 PM Published 17 一月 07 09:42 by WantSong Filed under: 好好学习 Comments # fatzly said on 一月 10, 2008 21:07: 楼上例子很经典，但补充两点： 1. 使用DISTINCT与不使用DISTINCT，对FIRST_VALUE函数是有区别的，使用DISTINCT的FIRST_VALUE函数效果等类似于SQL Server中的first()，取得所有分组中的第一条数据。 如果不使用DISTINCT，FIRST_VALUE函数的执行结果，按照楼上的例子，按照POS分组，对于每个NAME，都返回本组的第一个值(first_value) 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 1 E C33.6666666666667 PL 2 E C33.6666666666667 PL 3 E C33.6666666666667 PL 4 A B 22.5 PM 5 A B 22.5 PM 2.性能， 通常使用DISTINCT都会造成SQL在运行上性能的损失，但具体损失多少，一般大家没有直观的感受， 根据使用工具Toad(Tools of Oracle Application Developers)的分析结果，相同的SQL使用DISTINCT与使用ROWNUM = 1两种写法， DISTINCT在性能上的损失高达30%以上，证据会在下面给出。 DISTINCT的写法 SELECT DISTINCT ? ? ? FIRST_VALUE(NAME) OVER ? ? ? (PARTITION BY ID ORDER BY AGE DESC) ? ? ? AS MAXAGE_NAME ? ? ? FROM EMP ORDER BY POS ROWNUM ?= 1的写法 SELECT MAXAGE_NAME (SELECT ? ? ? FIRST_VALUE(NAME) OVER ? ? ? (PARTITION BY ID ORDER BY AGE DESC) ? ? ?