over partition by order by rows|range（5）之Nth_value

上文中，我们提到了First_value和Last_value两个分析函数，

可以提取第一行或者最后一行数据

但是，如果我们想提取开始的第二行，第三行，或者倒数第二行，倒数第三行的时候，就比较困难了

因此，oracle提供了Nth_value函数，可以提取需要的任一行。

语法如下：

NTH_VALUE(mesure,n)  [FROM FIRST,FROM LAST] [RESPECT NULLS|IGNORE NULLS]

over (partition by ... order by ... rows between ... and ...)

示例如下：

select year,week,product,
sale,
nth_value(sale,2) over ( --读取排序以后的第2行
partition by product,region,country,year
order by sale desc
rows between unbounded preceding and unbounded following
) top_sale,
nth_value(week,2) over (--读取排序以后的第2行（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

如果nth_value中的数字为1，那么就和First_value一致了