目录
1. 认识分析函数
1.1 什么是分析函数
1.2 分析函数和聚合函数的不同
1.3 分析函数的形式
2. 理解over()函数
2.1 两个order by 的执行机制
2.2 分析函数中的分组、排序、窗口
2.3 帮助理解over()的实例
3. 常见分析函数
3.1 演示表和数据的生成
3.2 first_value()与last_value():求最值对应的其他属性
3.3 rank()、dense_rank()与row_number() 排序问题
3.4 lag()与lead():求之前或之后的第N行
3.5 rollup()与cube()：排列组合分组
3.6 max()、min()、sum()与avg()：求移动的最值、总和与平均值
3.7 ratio_to_report()：求百分比
--------------------------------------------------------------------------------------------------------------------------------
分析函数是Oracle专门用于解决复杂报表统计需求的功能强大的函数，它可以在数据中进行分组然后计算基于组的某种统计值，并且每一组的每一行都可以返回一个统计值。
普通的聚合函数用group by分组，每个分组返回一个统计值；而分析函数采用partition by 分组，并且每组每行都可以返回一个统计值。
分析函数带有一个开窗函数over(),包含三个分析子句：分组(partition by)，排序(order by), 窗口(rows)，他们的使用形式如下：
分析函数的例子：
显示各部门员工的工资，并附带显示该部分的最高工资。
SQL如下：
结果为：
注： current row 表示当前行
unbounded preceding 表示第一行
unbounded following 表示最后一行
last_value(sal) 的结果与 order by sal 排序有关。如果排序为order by sal desc, 则最终的结果为分组排序后sal的最小值(分组排序后的最后一个值), 当deptno为10时，max_sal为1300。
分析函数是在整个SQL查询结束后(SQL语句中的order by 的执行比较特殊)再进行的操作，也就是说SQL语句中的order by也会影响分析函数的执行结果：
- 两者一致：如果SQL语句中的order by 满足分析函数分析时要求的排序，那么SQL语句中的排序将先执行，分析函数在分析时就不必再排序。
- 两者不一致：如果SQL语句中的order by 不满足分析函数分析时要求的排序，那么SQL语句中的排序将最后在分析函数分析结束后执行排序。
分析函数包含三个分析子句：分组(partition by)、排序(order by)、窗口(rows)。
窗口就是分析函数分析时要处理的数据范围，就拿sum来说，它是sum窗口中的记录而不是整个分组中的记录。因此我们在想得到某个栏位的累计值时，我们需要把窗口指定到该分组中的第一行数据到当前行，如果你指定该窗口从该分组中的第一行到最后一行，那么该组中的每一个sum值都会一样，即整个组的总和。
窗口子句中我们经常用到指定第一行，当前行，最后一行这样的三个属性：
第一行是 unbounded preceding
当前行是 current row
最后一行是 unbounded following
窗口子句不能单独出现，必须有order by 子句时才能出现，如：
LAST_VALUE(SAL) OVER (PARTITION BY DEPTNO ORDER BY SAL ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING )
以上示例指定窗口为整个分组.
而出现order by 子句的时候，不一定要有窗口子句，但效果会不一样，此时窗口默认是当前组的第一行到当前行！
SQL语句为：
等价于
结果如下图所示：
当省略窗口子句时：
- 如果存在order by, 则默认的窗口是 unbounded preceding and current row.
- 如果同时省略order by, 则默认的窗口是 unbounded preceding and unbounded following.
如果省略分组，则把全部记录当成一个组：
- 如果存在order by 则默认窗口是unbounded preceding and current row
- 如果这时省略order by 则窗口默认为 unbounded preceding and unbounded following
例1：关注点：SQL无排序，over()排序子句省略
例2：关注点：SQL无排序，over()排序子句有，窗口省略
例3：关注点：SQL无排序，over()排序子句有，窗口也有，窗口特意强调全组数据
例4：关注点：SQL有排序（正序），over() 排序子句无，先做SQL排序再进行分析函数运算
例5：关注点：SQL有排序（倒序），over() 排序子句无，先做SQL排序再进行分析函数运算
例6：关注点：SQL有排序（倒序），over()排序子句有，窗口子句无，此时的运算是：SQL先选数据但是不排序，而后排序子句先排序并进行分析函数处理（窗口默认为第一行到当前行），最后再进行SQL排序
建表语句：
插入数据：
问题：取出每个月通话费最高和最低的两个地区
思路：先进行group by bill_month, area_code使用聚合函数sum()求解出by bill_month, area_code的local_fare总和, 即sum(local_fare), 然后再运用分析函数进行求解每个月通话费用最高和最低的两个地区。
演示数据再Oracle自带的scott用户下
1.rank()值相同时排名相同，其后排名跳跃不连续
2. dense_rank()值相同时排名相同，其后排名连续不跳跃
3. row_number()值相同时排名不相等，其后排名连续不跳跃
lag(arg1, arg2, arg3):
arg1：是从其他行返回的表达式
arg2：是希望检索的当前行分区的偏移量。是一个正的偏移量，是一个往回检索以前的行数目
arg3：是在arg2表示的数目超出了分组的范围时返回的值
而lead()与lag()相反
group by rollup(A, B, C)：
首先会对 (A, B, C) 进行group by,
然后再对 (A, B) 进行group by,
其后再对 (A) 进行group by,
最后对全表进行汇总操作。
group by cube(A, B, C):
则首先会对 (A, B, C) 进行group by,
然后依次是 (A, B), (A, C), (A), (B, C), (B), (C),
最后对全表进行汇总操作。
1.生成演示数据：
2.普通group by 体验
3. group by rollup(A, B, C)：
首先会对 (A, B, C) 进行group by,
然后再对 (A, B) 进行group by,
其后再对 (A) 进行group by,
最后对全表进行汇总操作。
4. group by cube(A, B, C):
则首先会对 (A, B, C) 进行group by,
然后依次是 (A, B), (A, C), (A), (B, C), (B), (C),
最后对全表进行汇总操作。
（只截取了部分图）
问题：计算出各个地区连续3个月的通话费用的平均数(移动平均值)
问题：求各地区按月份累加的通话费
问题：求各地区花费占各月花费的比例
以上就是本篇文章【3.6 max()、min()、sum()与avg()：求移动的最值、总和与平均值】的全部内容了，欢迎阅览 ！ 文章地址：http://w.yusign.com/quote/6760.html 行业 资讯 企业新闻 行情 企业黄页 同类资讯 网站地图 返回首页 述古往 http://w.yusign.com/mobile/ , 查看更多