[SQL学习] 分析函数 first_value

- FIRST_VALUE/LAST_VALUE分析函数的10g语法描述
http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/functions057.htm#i83212
FIRST_VALUE (expr [ IGNORE NULLS ])
   OVER (analytic_clause)

- 分析函数FIRST_VALUE可以非常便利并迅速的的得到排序后的第一条数据的各种信息
有关分析函数的扩展可以参考Oracle的官方文档中的“Analytic Functions”描述：http://download.oracle.com/docs/cd/B19306_01/server.102 /b14200/functions001.htm#SQLRF06174

- 通过调整order by的顺序可以取得最大最小值
- last_value与first_value同理

- FIRST_VALUE的使用方法
-- 创建测试表T，并初始化9条数据
create table t (group_id number(10), name varchar2(10), salary int);
insert into t values (1,'Tom',1200);
insert into t values (2,'Kary',2400);
insert into t values (2,'Joe',800);
insert into t values (3,'Andy',100);
insert into t values (3,'Ellen',200);
insert into t values (3,'Erick',300);
insert into t values (3,'Hou',400);
insert into t values (3,'Mary',500);
insert into t values (3,'Secooler',600);
commit;

-- T表全貌
-- 共三组数据，group_id分别是1、2和3。第1组有一个人，第2组有两个人，第3组有六个人。最后一列是每个人的薪水值
sec@ora10g> select * from t order by group_id,name;

  GROUP_ID NAME                               SALARY
---------- ------------------------------ ----------
         1 Tom                                  1200
         2 Joe                                   800
         2 Kary                                 2400
         3 Andy                                  100
         3 Ellen                                 200
         3 Erick                                 300
         3 Hou                                   400
         3 Mary                                  500
         3 Secooler                              600

9 rows selected.

-- 在T表中添加一列，标识每一个数据分区中薪水最低的人名
select group_id, name, salary, FIRST_VALUE(name) OVER (partition by group_id order by salary asc ) as lowest_sal_name from t order by group_id, name;
or
select group_id, name, salary, FIRST_VALUE(name) OVER (partition by group_id order by salary asc ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) as lowest_sal_name from t order by group_id, name;

  GROUP_ID NAME                               SALARY LOWEST_SAL
---------- ------------------------------ ---------- ----------
         1 Tom                                  1200 Tom
         2 Joe                                   800 Joe
         2 Kary                                 2400 Joe
         3 Andy                                  100 Andy
         3 Ellen                                 200 Andy
         3 Erick                                 300 Andy
         3 Hou                                   400 Andy
         3 Mary                                  500 Andy
         3 Secooler                              600 Andy

9 rows selected.

-- 在T表中添加一列，标识每一个数据分区中薪水最低的薪水值
select group_id, name, salary, FIRST_VALUE(SALARY) OVER (partition by group_id order by salary asc ) as lowest_sal_name from t order by group_id, name;
or
select group_id, name, salary, FIRST_VALUE(SALARY) OVER (partition by group_id order by salary asc ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) as lowest_sal_name from t order by group_id, name;


  GROUP_ID NAME                               SALARY LOWEST_SAL_NAME
---------- ------------------------------ ---------- ---------------
         1 Tom                                  1200            1200
         2 Joe                                   800             800
         2 Kary                                 2400             800
         3 Andy                                  100             100
         3 Ellen                                 200             100
         3 Erick                                 300             100
         3 Hou                                   400             100
         3 Mary                                  500             100
         3 Secooler                              600             100

9 rows selected.