over partition by order by rows|range（4）之first_value和last_value

在通常的max和min聚合函数中，可以取得分区中的最大值和最小值。

但是，如果取得最大值或者最小值所在的行的其它列。，就比较困难了。

因此，oracle提供了First_value和Last_value两个分析函数，可以得到各个列的值。

语法如下：

first_value(expression) 

over (

partition by ... order by ... rows between ...

)

Last_value(expression) 

over (

partition by ... order by ... rows between ...

)

通过对需要的列进行排序，可以达到指定行的数据。

示例如下：

--first_value
select year,week,product,
sale,
first_value(sale) over ( --读取排序以后的第一行（最大值）
partition by product,region,country,year
order by sale desc
rows between unbounded preceding and unbounded following
) top_sale,
first_value(week) over (--读取排序以后的第一行（week值）
partition by product,region,country,year
order by sale desc
rows between unbounded preceding and unbounded following
) top_week
from sales_fact
where product='product1' and country='country1' and region='region1'
order by product,country,year,week