分析函数用法及窗口子句 range/rows差别
来源:互联网 发布:it培训中心 编辑:程序博客网 时间:2024/05/18 03:40
分析函数用法及窗口子句 range/rows差别
分析函数的语法结构一般是:分析函数名(参数) OVER (PARTITION BY子句 ORDER BY子句 ROWS/RANGE子句)。
即由以下三部分组成:
分析函数名:如sum、max、min、count、avg等聚集函数以及lead、lag行比较函数等;
over: 关键字,表示前面的函数是分析函数,不是普通的集合函数;
分析子句:over关键字后面挂号内的内容;
分析子句又由下面三部分组成:
partition by :分组子句,表示分析函数的计算范围,不同的组互不相干;
ORDER BY: 排序子句,表示分组后,组内的排序方式;
ROWS/RANGE:窗口子句,是在分组(PARTITION BY)后,组内的子分组(也称窗口),此时分析函数的计算范围窗口,而不是PARTITON。窗口有两种,ROWS和RANGE;
下面分析rows与range窗口子句的用法,先看下面例子:
从上面的例子可知:
1、窗口子句必须和order by 子句同时使用,且如果指定了order by 子句未指定窗口子句,则默认为RANGE BETWEEN unbounded preceding AND CURRENT ROW,如上例结果集中的defult_sum等于range_unbound_sum;
2、如果分析函数没有指定ORDER BY子句,也就不存在ROWS/RANGE窗口的计算;
3、range是逻辑窗口,是指定当前行对应值的范围取值,列数不固定,只要行值在范围内,对应列都包含在内,如上例中range_sum(即range 1 preceing and 2 following)例的分析结果:
当id=1时,是sum为1-1<=id<=1+2 的和,即sum=1+1+3=5(取id为1,1,3);
当id=3时,是sum为3-1<=id<=3+2 的和,即sum=3(取id为3);
当id=6时,是sum为6-1<=id<=6+2 的和,即sum=6+6+6+7+8=33(取id为6,6,6,7,8);
以此类推下去,结果如上例中所示。
4、rows是物理窗口,即根据order by 子句排序后,取的前N行及后N行的数据计算(与当前行的值无关,只与排序后的行号相关),如上例中rows_sum例结果,是取前1行和后2行数据的求和,分析上例rows_sum的结果:
当id=1(第一个1时)时,前一行没数,后二行分别是1和3,sum=1+1+3=5;
当id=3时,前一行id=1,后二行id都为6,则sum=1+3+6+6=16;
以此类推下去,结果如上例所示。
注:行比较分析函数lead和lag无window(窗口)子句。
即由以下三部分组成:
分析函数名:如sum、max、min、count、avg等聚集函数以及lead、lag行比较函数等;
over: 关键字,表示前面的函数是分析函数,不是普通的集合函数;
分析子句:over关键字后面挂号内的内容;
分析子句又由下面三部分组成:
partition by :分组子句,表示分析函数的计算范围,不同的组互不相干;
ORDER BY: 排序子句,表示分组后,组内的排序方式;
ROWS/RANGE:窗口子句,是在分组(PARTITION BY)后,组内的子分组(也称窗口),此时分析函数的计算范围窗口,而不是PARTITON。窗口有两种,ROWS和RANGE;
下面分析rows与range窗口子句的用法,先看下面例子:
点击(此处)折叠或打开
- SQL> edit
- 已写入 file afiedt.buf
- 1 WITH t AS
- 2 (SELECT(CASE
- 3 WHEN LEVEL IN (1, 2)THEN
- 4 1
- 5 WHEN LEVEL IN (4, 5)THEN
- 6 6
- 7 ELSE
- 8 LEVEL
- 9 END) ID
- 10 FROM dual
- 11 CONNECT BYLEVEL < 10)
- 12 SELECT id,
- 13 SUM(ID) over(ORDERBY ID) default_sum,
- 14 SUM(ID) over(ORDERBY ID RANGE BETWEEN unbounded precedingAND CURRENTROW) range_unbound_sum,
- 15 SUM(ID) over(ORDERBY ID ROWS BETWEEN unbounded preceding AND CURRENT ROW) rows_unbound_sum,
- 16 SUM(ID) over(ORDERBY ID RANGE BETWEEN 1 precedingAND 2 following) range_sum,
- 17 SUM(ID) over(ORDERBY ID ROWS BETWEEN 1 preceding AND 2 following) rows_sum
- 18* FROM t
- SQL> /
- ID DEFAULT_SUM RANGE_UNBOUND_SUM ROWS_UNBOUND_SUM RANGE_SUM ROWS_SUM
- ---------- ----------- ----------------- ---------------- ---------- ----------
- 1 2 2 1 5 5
- 1 2 2 2 5 11
- 3 5 5 5 3 16
- 6 23 23 11 33 21
- 6 23 23 17 33 25
- 6 23 23 23 33 27
- 7 30 30 30 42 30
- 8 38 38 38 24 24
- 9 47 47 47 17 17
- 已选择9行。
从上面的例子可知:
1、窗口子句必须和order by 子句同时使用,且如果指定了order by 子句未指定窗口子句,则默认为RANGE BETWEEN unbounded preceding AND CURRENT ROW,如上例结果集中的defult_sum等于range_unbound_sum;
2、如果分析函数没有指定ORDER BY子句,也就不存在ROWS/RANGE窗口的计算;
3、range是逻辑窗口,是指定当前行对应值的范围取值,列数不固定,只要行值在范围内,对应列都包含在内,如上例中range_sum(即range 1 preceing and 2 following)例的分析结果:
当id=1时,是sum为1-1<=id<=1+2 的和,即sum=1+1+3=5(取id为1,1,3);
当id=3时,是sum为3-1<=id<=3+2 的和,即sum=3(取id为3);
当id=6时,是sum为6-1<=id<=6+2 的和,即sum=6+6+6+7+8=33(取id为6,6,6,7,8);
以此类推下去,结果如上例中所示。
4、rows是物理窗口,即根据order by 子句排序后,取的前N行及后N行的数据计算(与当前行的值无关,只与排序后的行号相关),如上例中rows_sum例结果,是取前1行和后2行数据的求和,分析上例rows_sum的结果:
当id=1(第一个1时)时,前一行没数,后二行分别是1和3,sum=1+1+3=5;
当id=3时,前一行id=1,后二行id都为6,则sum=1+3+6+6=16;
以此类推下去,结果如上例所示。
注:行比较分析函数lead和lag无window(窗口)子句。
阅读全文
0 0
- 分析函数用法及窗口子句 range/rows差别
- 分析函数用法及窗口子句 range/rows差别
- 分析函数窗口子句 RANGE/ROWS 差别
- 分析函数窗口子句 RANGE/ROWS 差别
- SQL Server窗口函数:ROWS与RANGE
- 窗口函数之rows的用法(读书笔记二)
- oracle分析函数系列之sum() rows/range between ... preceding and ... following,上下范围内求值
- oracle分析函数:一、窗口子句的使用
- 弹出窗口函数MessageBox用法、分析及相应源代码(转帖)
- Python 循环及函数range的用法
- 排名函数(ROW_NUMBER、RANK、DENSE_RANK)及OVER子句 Oracle 中分析函数用法之--rank(),dense_rank(),partition,over()
- 窗口函数--配合over(...)的选项 rows/range between ... preceding and ... following--工作备忘2016/9/30
- 窗口函数之range的用法(读书笔记三)
- oracle 每日一题-分析函数的RANGE/ROW窗口
- HIVE 窗口及分析函数
- HIVE 窗口及分析函数
- HIVE 窗口及分析函数
- range()函数用法
- 数学笔记20——三角替换1(sin和cos)
- Mongodb将原有副本集数据迁移到分片存储
- sprintf使用
- 【线段树区间更新】hdu1698 Just a Hook
- Centos安装pip
- 分析函数用法及窗口子句 range/rows差别
- Qt之实现窗体缩放
- ActiveMQ学习(三)Java中使用AMQ的简单实例
- SharePoint Framework 基于团队的开发(五)
- 我的mysql默认密码在哪里
- 解决输入法遮挡布局和华为手机底部虚拟按键遮挡底部界面的方法
- Linux学习的笔记
- 虚机磁盘扩展-lvm其他盘
- 如何让浏览器tab页数据不共享