oracle 分析函数之first_value和last_value

first_value函数返回结果集中排在第一位的值

语法：first_value(expression) over(partition-clause order-by-clause windowing-clause)

 

建表语句：

create table SMALL_CUSTOMERS(CUSTOMER_ID NUMBER,SUM_ORDERS  NUMBER);   
insert into SMALL_CUSTOMERS (CUSTOMER_ID, SUM_ORDERS) values (1000, 10);   
insert into SMALL_CUSTOMERS (CUSTOMER_ID, SUM_ORDERS) values (1000, 20);   
insert into SMALL_CUSTOMERS (CUSTOMER_ID, SUM_ORDERS) values (1000, 30);   
insert into SMALL_CUSTOMERS (CUSTOMER_ID, SUM_ORDERS) values (800, 5);   
insert into SMALL_CUSTOMERS (CUSTOMER_ID, SUM_ORDERS) values (800, 10);   
insert into SMALL_CUSTOMERS (CUSTOMER_ID, SUM_ORDERS) values (800, 1);  

 

first_value具体用法如下：

select customer_id,
       sum_orders,
       first_value(sum_orders) over(partition by customer_id order by sum_orders)
  from small_customers;

 

last_value函数返回结果集中排在最后一位的值

select customer_id,
       sum_orders,
       last_value(sum_orders) over(partition by customer_id order by sum_orders 
       ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
  from small_customers;

 

注：ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING 必需要，否则结果如下:

 

UNBOUNDED PRECEDING 表示从第一行开始，UNBOUNDED FOLLOWING 表示到最后一行，current row表示当前行

具体用法如下：

select customer_id,
       sum_orders,
       sum(sum_orders) over(order by sum_orders 
          rows between unbounded preceding and current row) current_sum,
       sum(sum_orders) over(order by sum_orders 
          rows between unbounded preceding and unbounded following) total_sum
  from small_customers;

 

在11g中，oracle新增了一个NTH_VALUE 函数，这个功能包含了FIRST_VALUE 和LAST_VALUE 的功能，还可以取任意的正数或倒数