利用分析函数在Oracle中分组排序取首条或末条记录行

saillee

    在工作中，我们经常会使用到按时间或指定字段进行排序，并提取首条/末条记录的操作，而Oracle中的分析函数让我们能够方便快捷地实现这个功能，下面实例的详细情况：

1、建立测试数据表

1. create table sail_test
   
2. (group_id number(3),
   
3.  order_id number(3),
   
4.  other1 varchar2(8),
   
5.  other2 varchar2(8));

2、插入测试数据

1. insert into sail_test values (1, 1, 'g1_o11', 'g1_o21');
   
2. insert into sail_test values (1, 2, 'g1_o12', 'g1_o22');
   
3. insert into sail_test values (1, 3, 'g1_o13', 'g1_o23');
   
4. insert into sail_test values (1, 4, 'g1_o14', 'g1_o24');
   
5. insert into sail_test values (2, 1, 'g2_o11', 'g2_o21');
   
6. insert into sail_test values (2, 2, 'g2_o12', 'g2_o22');
   
7. insert into sail_test values (2, 3, 'g2_o13', 'g2_o23');
   
8. insert into sail_test values (3, 1, 'g3_o11', 'g3_o21');
   
9. insert into sail_test values (3, 2, 'g3_o12', 'g3_o22');
   
10. insert into sail_test values (3, 3, 'g3_o13', 'g3_o23');

其中，group_id是分组的字段，order_id是排序字段，下面是所有数据的快照：

1. GROUP_ID ORDER_ID OTHER1 OTHER2
   
2. -------- -------- -------- --------
   
3.        1 1 g1_o11 g1_o21
   
4.        1 2 g1_o12 g1_o22
   
5.        1 3 g1_o13 g1_o23
   
6.        1 4 g1_o14 g1_o24
   
7.        2 1 g2_o11 g2_o21
   
8.        2 2 g2_o12 g2_o22
   
9.        2 3 g2_o13 g2_o23
   
10.        3 1 g3_o11 g3_o21
    
11.        3 2 g3_o12 g3_o22
    
12.        3 3 g3_o13 g3_o23

3、我们现在想查询出各个group_id中，order_id最小的那个记录行，下面是SQL：

1. -- 以group_id分组，按照order_id排序（可以是倒序，order by语句后面加desc，取首条记录
   
2. select distinct a.group_id,
   
3.        first_value(a.other1) over (partition by a.group_id order by order_id),
   
4.        first_value(a.other2) over (partition by a.group_id order by order_id)
   
5. from sail_test a
   
6. order by a.group_id;

查询结果为：

1. GROUP_ID FIRST_VALUE(A.OTHER1)OVER(PART FIRST_VALUE(A.OTHER2)OVER(PART
   
2. -------- ------------------------------ ------------------------------
   
3.        1 g1_o11 g1_o21
   
4.        2 g2_o11 g2_o21
   
5.        3 g3_o11 g3_o21

4、总结

Oracle中的分析函数使用很方便，在本例子中通过over来指定分组字段和排序字段的范围，通过first_value或last_value函数来获取首条或末条记录字段值，排序可以是顺序或倒序，而distinct则指定获取一条记录行。