开窗函数 lag_开窗函数排序

开窗函数 lag_开窗函数排序窗口函数目录1.窗口函数简介2.窗口函数示例-全统计3.窗口函数进阶-滚动统计(累积/均值)4.窗口函数进阶-根据时间范围统计5.窗口函数进阶-first_value/last_value6.窗口函数进阶-比较相邻记录一、窗口函数简介&#x

窗口函数   目录   1.窗口函数简介   2.窗口函数示例-全统计   3.窗口函数进阶-滚动统计(累积/均值)   4.窗口函数进阶-根据时间范围统计   5.窗口函数进阶-first_value/last_value   6.窗口函数进阶-比较相邻记录   一、窗口函数简介:   到目前为止,我们所学习的分析函数在计算/统计一段时间内的数据时特别有用,但是假如计算/统计需要随着遍历记录集的每一条记录而进行呢?举些例子来说:   ①列出每月的订单总额以及全年的订单总额   ②列出每月的订单总额以及截至到当前月的订单总额   ③列出上个月、当月、下一月的订单总额以及全年的订单总额   ④列出每天的营业额及一周来的总营业额   ⑤列出每天的营业额及一周来每天的平均营业额   仔细回顾一下前面我们介绍到的分析函数,我们会发现这些需求和前面有一些不同:前面我们介绍的分析函数用于计算/统计一个明确的阶段/记录集,而这里有部分需求例如2,需要随着遍历记录集的每一条记录的同时进行统计。   也即是说:统计不止发生一次,而是发生多次。统计不至发生在记录集形成后,而是发生在记录集形成的过程中。   这就是我们这次要介绍的窗口函数的应用了。它适用于以下几个场合:   ①通过指定一批记录:例如从当前记录开始直至某个部分的最后一条记录结束   ②通过指定一个时间间隔:例如在交易日之前的前30天   ③通过指定一个范围值:例如所有占到当前交易量总额5%的记录   二、窗口函数示例-全统计:   下面我们以需求:列出每月的订单总额以及全年的订单总额为例,来看看窗口函数的应用。   【1】测试环境:   SQL >   desc  orders;   名称                    是否为空? 类型   – ——————— ——– —————-   MONTH                              NUMBER ( 2 )   TOT_SALES                     NUMBER   SQL >   【2】测试数据:   SQL >   select   *   from  orders;   MONTH   TOT_SALES   – ——– ———-   1         2         3         4         5         6         7         8         9         10         11         12         已选择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                2                3                4                5                6                7                8                9                10                11                12                已选择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               2               3               4               5               6               7               8               9               10               11               12               已选择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                         2                        3                        4                        5                        6                        7                        8                        9                        10                        11                        12                        已选择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                               2                              3                              4                              5                              6                              7                              8                              9                              10                              11                              12                              已选择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月为基准。   ————————————————   版权声明:本文为CSDN博主「Horrison」的原创文章,遵循CC 4.0 BY-SA版权协议,转载请附上原文出处链接及本声明。   原文链接:https://blog.csdn.net/huozhicheng/article/details//

2024最新激活全家桶教程,稳定运行到2099年,请移步至置顶文章:https://sigusoft.com/99576.html

版权声明:本文内容由互联网用户自发贡献,该文观点仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容, 请联系我们举报,一经查实,本站将立刻删除。 文章由激活谷谷主-小谷整理,转载请注明出处:https://sigusoft.com/28091.html

(0)
上一篇 2024年 9月 12日
下一篇 2024年 9月 12日

相关推荐

关注微信