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;
![](https://img.laitimes.com/img/__Qf2AjLwojIjJCLyojI0JCLicGcq5SM4UmMhFTYjVjY5YTL1cjZh1yYzU2MtkjNygTLyY2MjVDMygzLcZDNxMzLcVzNwAzLcRnbl1GajFGd0F2LcRWYvxGc19CXt92YuUWelRXauwGZvw1LcpDc0RHaiojIsJye.jpg)
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 的功能,还可以取任意的正数或倒数