Oracle分析函数-first_value()和last_value()

短命女 2021-06-26 16:06 398阅读 0赞

`first_value()`和`last_value()`字面意思已经很直观了，取首尾记录值。  
例:查询部门最早发生销售记录日期和最近发生的销售记录日期

![在这里插入图片描述][20180924105338930]  
看结果`first_value()`很直观，不用多解释  
但是，`last_value()`值，部门D01不是应该为2014/6/12，部门D02不是应该为2014/5/2吗?为什么会每条记录都不一样?  
可以这样去理解:`last_value()`默认统计范围是 `rows between unbounded preceding and current row`  
验证一下:

select dept_id ,sale_date ,goods_type ,sale_cnt ,first_value(sale_date) over (partition by dept_id order by sale_date) first_value ,last_value(sale_date) over (partition by dept_id order by sale_date desc) last_value ,last_value(sale_date) over (partition by dept_id order by sale_date rows between unbounded preceding and unbounded following) last_value_all from criss_sales;

全统计的情况下得到的`last_value()`值，部门D01为2014/6/12，部门D02为2014/5/2。

--------------------

## Oracle开发之：窗口函数 （转） `rows between unbounded preceding and current row` ##

### 一、窗口函数简介： ###

到目前为止，我们所学习的分析函数在计算/统计一段时间内的数据时特别有用，但是假如计算/统计需要随着遍历记录集的每一条记录而进行呢？举些例子来说：

①列出每月的订单总额以及全年的订单总额  
②列出每月的订单总额以及截至到当前月的订单总额  
③列出上个月、当月、下一月的订单总额以及全年的订单总额  
④列出每天的营业额及一周来的总营业额  
⑤列出每天的营业额及一周来每天的平均营业额

仔细回顾一下前面我们介绍到的分析函数，我们会发现这些需求和前面有一些不同：前面我们介绍的分析函数用于计算/统计一个明确的阶段/记录集，而这里有部分需求例如2，需要随着遍历记录集的每一条记录的同时进行统计。

也即是说：统计不止发生一次，而是发生多次。统计不至发生在记录集形成后，而是发生在记录集形成的过程中。

这就是我们这次要介绍的窗口函数的应用了。它适用于以下几个场合：

①通过指定一批记录：例如从当前记录开始直至某个部分的最后一条记录结束  
②通过指定一个时间间隔：例如在交易日之前的前30天  
③通过指定一个范围值：例如所有占到当前交易量总额5%的记录

### 二、窗口函数示例－全统计： ###

下面我们以需求：列出每月的订单总额以及全年的订单总额为例，来看看窗口函数的应用。

【1】测试环境：

SQL> desc orders; 名称 是否为空? 类型 ----------------------- -------- ---------------- MONTH NUMBER(2) TOT_SALES NUMBER SQL>

【2】测试数据：

SQL> select * from orders; MONTH TOT_SALES ---------- ---------- 1 610697 2 428676 3 637031 4 541146 5 592935 6 501485 7 606914 8 460520 9 392898 10 510117 11 532889 12 492458

已选择12行。

【3】测试语句：

回忆一下前面《Oracle开发专题之：分析函数(OVER)》一文中，我们使用了`sum(sum(tot_sales)) over (partition by region_id)` 来统计每个分区的订单总额。现在我们要统计的不单是每个分区，而是所有分区，`partition by region_id`在这里不起作用了。

Oracle为这种情况提供了一个子句：`rows between ... preceding and ... following`。从字面上猜测它的意思是：在XXX之前和XXX之后的所有记录，实际情况如何让我们通过示例来验证：

SQL> select month, 2 sum(tot_sales) month_sales, 3 sum(sum(tot_sales)) over (order by month 4 rows between unbounded preceding and unbounded following) total_sales 5 from orders 6 group by month; MONTH MONTH_SALES TOTAL_SALES ---------- ----------- ----------- 1 610697 6307766 2 428676 6307766 3 637031 6307766 4 541146 6307766 5 592935 6307766 6 501485 6307766 7 606914 6307766 8 460520 6307766 9 392898 6307766 10 510117 6307766 11 532889 6307766 12 492458 6307766

已选择12行。

绿色高亮处的代码在这里发挥了关键作用，它告诉`oracle`统计从第一条记录开始至最后一条记录的每月销售额。这个统计在记录集形成的过程中执行了12次，这时相当费时的！但至少我们解决了问题。

`unbounded preceding and unbouned following`的意思针对当前所有记录的前一条、后一条记录，也就是表中的所有记录。那么假如我们直接指定从第一条记录开始直至末尾呢？看看下面的结果：

SQL> select month, 2 sum(tot_sales) month_sales, 3 sum(sum(tot_sales)) over (order by month 4 rows between 1 preceding and unbounded following) all_sales 5 from orders 6 group by month; MONTH MONTH_SALES ALL_SALES ---------- ----------- ---------- 1 610697 6307766 2 428676 6307766 3 637031 5697069 4 541146 5268393 5 592935 4631362 6 501485 4090216 7 606914 3497281 8 460520 2995796 9 392898 2388882 10 510117 1928362 11 532889 1535464 12 492458 1025347

已选择12行。

很明显这个语句错了。实际1在这里不是从第1条记录开始的意思，而是指当前记录的前一条记录。`preceding`前面的修饰符是告诉窗口函数执行时参考的记录数，如同`unbounded`就是告诉`oracle`不管当前记录是第几条，只要前面有多少条记录，都列入统计的范围。

### 三、窗口函数进阶－滚动统计(累积/均值)： ###

考虑前面提到的第2个需求：列出每月的订单总额以及截至到当前月的订单总额。也就是说2月份的记录要显示当月的订单总额和1,2月份订单总额的和。3月份要显示当月的订单总额和1,2,3月份订单总额的和，依此类推。

很明显这个需求需要在统计第N月的订单总额时，还要再统计这N个月来的订单总额之和。想想上面的语句，假如我们能够把`and unbounded following`换成代表当前月份的逻辑多好啊！很幸运的是Oracle考虑到了我们这个需求，为此我们只需要将语句稍微改成: `curreent row`就可以了。

SQL> select month, 2 sum(tot_sales) month_sales, 3 sum(sum(tot_sales)) over(order by month 4 rows between unbounded preceding and current row) current_total_sales 5 from orders 6 group by month; MONTH MONTH_SALES CURRENT_TOTAL_SALES ---------- ----------- ------------------- 1 610697 610697 2 428676 1039373 3 637031 1676404 4 541146 2217550 5 592935 2810485 6 501485 3311970 7 606914 3918884 8 460520 4379404 9 392898 4772302 10 510117 5282419 11 532889 5815308 12 492458 6307766

已选择12行。

现在我们能得到滚动的销售总额了！下面这个统计结果看起来更加完美，它展现了所有我们需要的数据：

SQL> select month, 2 sum(tot_sales) month_sales, 3 sum(sum(tot_sales)) over(order by month 4 rows between unbounded preceding and current row) current_total_sales, 5 sum(sum(tot_sales)) over(order by month 6 rows between unbounded preceding and unbounded following) total_sales 7 from orders 8 group by month; MONTH MONTH_SALES CURRENT_TOTAL_SALES TOTAL_SALES ---------- ----------- ------------------- ----------- 1 610697 610697 6307766 2 428676 1039373 6307766 3 637031 1676404 6307766 4 541146 2217550 6307766 5 592935 2810485 6307766 6 501485 3311970 6307766 7 606914 3918884 6307766 8 460520 4379404 6307766 9 392898 4772302 6307766 10 510117 5282419 6307766 11 532889 5815308 6307766 12 492458 6307766 6307766

已选择12行。

在一些销售报表中我们会时常看到求平均值的需求，有时可能是针对全年的数据求平均值，有时会是针对截至到当前的所有数据求平均值。很简单，只需要将：  
`sum(sum(tot_sales))`换成`avg(sum(tot_sales))`即可。

### 四、窗口函数进阶－根据时间范围统计： ###

前面我们说过，窗口函数不单适用于指定记录集进行统计，而且也能适用于指定范围进行统计的情况，例如下面这个SQL语句就统计了当天销售额和五天内的评价销售额：

select trunc(order_dt) day, sum(sale_price) daily_sales, avg(sum(sale_price)) over (order by trunc(order_dt) range between interval '2' day preceding and interval '2' day following) five_day_avg from cust_order where sale_price is not null and order_dt between to_date('01-jul-2001','dd-mon-yyyy') and to_date('31-jul-2001','dd-mon-yyyy')

为了对指定范围进行统计，Oracle使用关键字`range、interval`来指定一个范围。上面的例子告诉Oracle查找当前日期的前2天，后2天范围内的记录，并统计其销售平均值。

### 五、窗口函数进阶－`first_value/last_value`： ###

Oracle提供了2个额外的函数：`first_value、last_value`，用于在窗口记录集中查找第一条记录和最后一条记录。假设我们的报表需要显示当前月、上一个月、后一个月的销售情况，以及每3个月的销售平均值，这两个函数就可以派上用场了。

select month, first_value(sum(tot_sales)) over (order by month rows between 1 preceding and 1 following) prev_month, sum(tot_sales) monthly_sales, last_value(sum(tot_sales)) over (order by month rows between 1 preceding and 1 following) next_month, avg(sum(tot_sales)) over (order by month rows between 1 preceding and 1 following) rolling_avg from orders where year = 2001 and region_id = 6 group by month order by month;

首先我们来看:`rows between 1 preceding and 1 following`告诉Oracle在当前记录的前一条、后一条范围内查找并统计，而`first_value`和`last_value`在这3条记录中至分别找出第一条、第三条记录，这样我们就轻松地得到相邻三个月的销售记录及平均值了！

### 六、窗口函数进阶－比较相邻记录： ###

通过第五部分的学习，我们知道了如何利用窗口函数来显示相邻的记录，现在假如我们想每次显示当月的销售额和上个月的销售额，应该怎么做呢？

从第五部分的介绍我们可以知道，利用`first_value(sum(tot_sales) over (order by month rows between 1 preceding and 0 following))`就可以做到了，其实Oracle还有一个更简单的方式让我们来比较2条记录，它就是`lag`函数。

`leg`函数类似于`preceding`和`following`

子句，它能够通过和当前记录的相对位置而被应用，在比较同一个相邻的记录集内两条相邻记录的时候特别有用。

select month, sum(tot_sales) monthly_sales, lag(sum(tot_sales), 1) over (order by month) prev_month_sales from orders where year = 2001 and region_id = 6 group by month order by month;

`lag(sum(tot_sales),1)`中的1表示以1月为基准。

[20180924105338930]: /images/20210626/ffae4a73a2704566b668726b82cc4922.png