Chinaunix

标题: 利用分析函数在Oracle中分组排序取首条或末条记录行 [打印本页]

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

1、建立测试数据表

create table sail_test
(group_id number(3),
order_id number(3),
other1 varchar2(8),
other2 varchar2(8));

2、插入测试数据

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

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

GROUP_ID ORDER_ID OTHER1 OTHER2
-------- -------- -------- --------
1 1 g1_o11 g1_o21
1 2 g1_o12 g1_o22
1 3 g1_o13 g1_o23
1 4 g1_o14 g1_o24
2 1 g2_o11 g2_o21
2 2 g2_o12 g2_o22
2 3 g2_o13 g2_o23
3 1 g3_o11 g3_o21
3 2 g3_o12 g3_o22
3 3 g3_o13 g3_o23

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

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

查询结果为：

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

4、总结

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

欢迎光临 Chinaunix (http://bbs.chinaunix.net/)