- ubuntu12.04环境下使用kvm ioctl接口实现最简单的虚拟机
- Ubuntu 通过无线网络安装Ubuntu Server启动系统后连接无线网络的方法
- 在Ubuntu上搭建网桥的方法
- ubuntu 虚拟机上网方式及相关配置详解
CFSDN坚持开源创造价值,我们致力于搭建一个资源共享平台,让每一个IT人在这里找到属于你的精彩世界.
这篇CFSDN的博客文章Oracle开发之窗口函数由作者收集整理,如果你对这篇文章有兴趣,记得点赞哟.
1、窗口函数简介:
到目前为止,我们所学习的分析函数在计算/统计一段时间内的数据时特别有用,但是假如计算/统计需要随着遍历记录集的每一条记录而进行呢?举些例子来说:
①列出每月的订单总额以及全年的订单总额 ②列出每月的订单总额以及截至到当前月的订单总额 ③列出上个月、当月、下一月的订单总额以及全年的订单总额 ④列出每天的营业额及一周来的总营业额 ⑤列出每天的营业额及一周来每天的平均营业额 。
仔细回顾一下前面我们介绍到的分析函数,我们会发现这些需求和前面有一些不同:前面我们介绍的分析函数用于计算/统计一个明确的阶段/记录集,而这里有部分需求例如2,需要随着遍历记录集的每一条记录的同时进行统计.
也即是说:统计不止发生一次,而是发生多次。统计不至发生在记录集形成后,而是发生在记录集形成的过程中.
这就是我们这次要介绍的窗口函数的应用了。它适用于以下几个场合:
①通过指定一批记录:例如从当前记录开始直至某个部分的最后一条记录结束 ②通过指定一个时间间隔:例如在交易日之前的前30天 ③通过指定一个范围值:例如所有占到当前交易量总额5%的记录 。
2、窗口函数示例-全统计:
下面我们以需求:列出每月的订单总额以及全年的订单总额为例,来看看窗口函数的应用.
【1】测试环境:
。
。
名称 是否为空? 类型 ----------------------- -------- ---------------- MONTH NUMBER(2) TOT_SALES NUMBER 。
SQL> 。
【2】测试数据:
。
。
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之后的所有记录,实际情况如何让我们通过示例来验证:
。
。
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的意思针对当前所有记录的前一条、后一条记录,也就是表中的所有记录。那么假如我们直接指定从第一条记录开始直至末尾呢?看看下面的结果:
。
。
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不管当前记录是第几条,只要前面有多少条记录,都列入统计的范围.
3、窗口函数进阶-滚动统计(累积/均值):
考虑前面提到的第2个需求:列出每月的订单总额以及截至到当前月的订单总额。也就是说2月份的记录要显示当月的订单总额和1,2月份订单总额的和。3月份要显示当月的订单总额和1,2,3月份订单总额的和,依此类推.
很明显这个需求需要在统计第N月的订单总额时,还要再统计这N个月来的订单总额之和。想想上面的语句,假如我们能够把and unbounded following换成代表当前月份的逻辑多好啊!很幸运的是Oracle考虑到了我们这个需求,为此我们只需要将语句稍微改成: curreent row就可以了.
。
。
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行.
现在我们能得到滚动的销售总额了!下面这个统计结果看起来更加完美,它展现了所有我们需要的数据:
。
。
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))即可.
4、窗口函数进阶-根据时间范围统计:
前面我们说过,窗口函数不单适用于指定记录集进行统计,而且也能适用于指定范围进行统计的情况,例如下面这个SQL语句就统计了当天销售额和五天内的评价销售额: 。
。
为了对指定范围进行统计,Oracle使用关键字range、interval来指定一个范围。上面的例子告诉Oracle查找当前日期的前2天,后2天范围内的记录,并统计其销售平均值.
5、窗口函数进阶-first_value/last_value:
Oracle提供了2个额外的函数:first_value、last_value,用于在窗口记录集中查找第一条记录和最后一条记录。假设我们的报表需要显示当前月、上一个月、后一个月的销售情况,以及每3个月的销售平均值,这两个函数就可以派上用场了.
。
。
首先我们来看:rows between 1 preceding and 1 following告诉Oracle在当前记录的前一条、后一条范围内查找并统计,而first_value和last_value在这3条记录中至分别找出第一条、第三条记录,这样我们就轻松地得到相邻三个月的销售记录及平均值了! 。
6、窗口函数进阶-比较相邻记录:
通过第五部分的学习,我们知道了如何利用窗口函数来显示相邻的记录,现在假如我们想每次显示当月的销售额和上个月的销售额,应该怎么做呢?
从第五部分的介绍我们可以知道,利用first_value(sum(tot_sales) over (order by month rows between 1 preceding and 0 following))就可以做到了,其实Oracle还有一个更简单的方式让我们来比较2条记录,它就是lag函数.
leg函数类似于preceding和following子句,它能够通过和当前记录的相对位置而被应用,在比较同一个相邻的记录集内两条相邻记录的时候特别有用.
。
。
lag(sum(tot_sales),1)中的1表示以1月为基准.
以上就是Oracle窗口函数用法的全部内容,希望能给大家一个参考,也希望大家多多支持我.
最后此篇关于Oracle开发之窗口函数的文章就讲到这里了,如果你想了解更多关于Oracle开发之窗口函数的内容请搜索CFSDN的文章或继续浏览相关文章,希望大家以后支持我的博客! 。
C语言sscanf()函数:从字符串中读取指定格式的数据 头文件: ?
最近,我有一个关于工作预评估的问题,即使查询了每个功能的工作原理,我也不知道如何解决。这是一个伪代码。 下面是一个名为foo()的函数,该函数将被传递一个值并返回一个值。如果将以下值传递给foo函数,
CStr 函数 返回表达式,该表达式已被转换为 String 子类型的 Variant。 CStr(expression) expression 参数是任意有效的表达式。 说明 通常,可以
CSng 函数 返回表达式,该表达式已被转换为 Single 子类型的 Variant。 CSng(expression) expression 参数是任意有效的表达式。 说明 通常,可
CreateObject 函数 创建并返回对 Automation 对象的引用。 CreateObject(servername.typename [, location]) 参数 serv
Cos 函数 返回某个角的余弦值。 Cos(number) number 参数可以是任何将某个角表示为弧度的有效数值表达式。 说明 Cos 函数取某个角并返回直角三角形两边的比值。此比值是
CLng 函数 返回表达式,此表达式已被转换为 Long 子类型的 Variant。 CLng(expression) expression 参数是任意有效的表达式。 说明 通常,您可以使
CInt 函数 返回表达式,此表达式已被转换为 Integer 子类型的 Variant。 CInt(expression) expression 参数是任意有效的表达式。 说明 通常,可
Chr 函数 返回与指定的 ANSI 字符代码相对应的字符。 Chr(charcode) charcode 参数是可以标识字符的数字。 说明 从 0 到 31 的数字表示标准的不可打印的
CDbl 函数 返回表达式,此表达式已被转换为 Double 子类型的 Variant。 CDbl(expression) expression 参数是任意有效的表达式。 说明 通常,您可
CDate 函数 返回表达式,此表达式已被转换为 Date 子类型的 Variant。 CDate(date) date 参数是任意有效的日期表达式。 说明 IsDate 函数用于判断 d
CCur 函数 返回表达式,此表达式已被转换为 Currency 子类型的 Variant。 CCur(expression) expression 参数是任意有效的表达式。 说明 通常,
CByte 函数 返回表达式,此表达式已被转换为 Byte 子类型的 Variant。 CByte(expression) expression 参数是任意有效的表达式。 说明 通常,可以
CBool 函数 返回表达式,此表达式已转换为 Boolean 子类型的 Variant。 CBool(expression) expression 是任意有效的表达式。 说明 如果 ex
Atn 函数 返回数值的反正切值。 Atn(number) number 参数可以是任意有效的数值表达式。 说明 Atn 函数计算直角三角形两个边的比值 (number) 并返回对应角的弧
Asc 函数 返回与字符串的第一个字母对应的 ANSI 字符代码。 Asc(string) string 参数是任意有效的字符串表达式。如果 string 参数未包含字符,则将发生运行时错误。
Array 函数 返回包含数组的 Variant。 Array(arglist) arglist 参数是赋给包含在 Variant 中的数组元素的值的列表(用逗号分隔)。如果没有指定此参数,则
Abs 函数 返回数字的绝对值。 Abs(number) number 参数可以是任意有效的数值表达式。如果 number 包含 Null,则返回 Null;如果是未初始化变量,则返回 0。
FormatPercent 函数 返回表达式,此表达式已被格式化为尾随有 % 符号的百分比(乘以 100 )。 FormatPercent(expression[,NumDigitsAfterD
FormatNumber 函数 返回表达式,此表达式已被格式化为数值。 FormatNumber( expression [,NumDigitsAfterDecimal [,Inc
我是一名优秀的程序员,十分优秀!