ORACLE的窗口函数（分析函数） ORACLE的窗口函数（分析函数) 目录ORACLE的窗口函数（分析函数)前言描述实例有关ROWS/RANGE窗口的例子（借鉴其他的博客）常用分析函数汇总1.排名函数1.row_number() over()2.rank() over()3.dense_rank() over()2.count() over()：计数3.sum() over()：求和4.avg() over()：求平均5.min() over()；max() over()：求最小最大6.ntile(n) over()：7.first_value() over()/last_value() over()：8.keep (dense_rank first/last order by …)over():9.lag(column_name,n,若首行无填充默认为null) over()10.ratio_to_report(a) over(partition by b) ：11.percent_rank() over():12.cume_dist() over() ：13.precentile_cont( x ) within group(order by …) over()：14.stddev() over()：15.variance() over()：16.covar_samp over()：decode函数decode实现行和列的转换Oracle 行转列 动态转换的列pivot进行静态行转列pivot进行动态行转列查找两张表的数据，用一个标识区分 前言描述 分析函数带有一个开窗函数over()，包含三个分析子句: 分组(partition by), 排序(order by), 窗口(rows) ， 他们的使用形式如下： a) order by 字段两者一致：即sql语句中的order by子句里的内容和开窗函数over（）中的order by子句里的内容一样，那么sql语句中的排序将先执行,分析函数在分析时就不必再排序； b) order by 字段两者不一致：即sql语句中的order by子句里的内容和开窗函数over（）中的order by子句里的内容不一样，那么sql语句中的排序将最后在分析函数分析结束后执行排序。) 窗口函数的由来： partition by:分组后的记录集合称为窗口。此处的窗口并非“窗户”的意思，而是代表范围。这也是窗口函数的由来。通过PARTITION BY分组后的记录集合称为“窗口” 实例 以Scott用户中的emp表，结合sum()over()简单示例 累计收入求和的需要重新按empno或者empno、deptno排一下序,不然有些乱看的不是很清楚，基本常用的就是上面几种形式 有关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(窗口)子句。 常用分析函数汇总 1.排名函数 项目开发中，我们有时会碰到需要分组排序来解决问题的情况： 1、要求取出按field1分组后，并在每组中按照field2排序； 2、亦或更加要求取出1中已经分组排序好的前多少行的数据 这里通过一张表的示例和SQL语句阐述下oracle数据库中用于分组排序函数的用法。 1.row_number() over() 注：此分析函数必须要加order by排序 row_number()over(partition by col1 order by col2)表示根据col1分组，在分组内部根据col2排序，而此函数计算的值就表示每组内部排序后的顺序编号（组内连续的唯一的）。 与rownum的区别在于：使用rownum进行排序的时候是先对结果集加入伪劣rownum然后再进行排序，而此函数在包含排序从句后是先排序再计算行号码。row_number()和rownum差不多，功能更强一点（可以在各个分组内从1开始排序）。 1 2 3 4 5 如： 2.rank() over() 注：此分析函数同row_number() over()必须要加order by排序 rank()是跳跃排序，有两个第二名时接下来就是第四名（同样是在各个分组内） 1 2 2 4 5 3.dense_rank() over() 注：此分析函数同row_number() over()必须要加order by排序 dense_rank()也是连续排序，有两个第二名时仍然跟着第三名。相比之下row_number是没有重复值的。 1 2 2 3 4 dense_rank row_number() over() rank() over() 2.count() over()：计数 3.sum() over()：求和 4.avg() over()：求平均 5.min() over()；max() over()：求最小最大 6.ntile(n) over()： 将数据等分成n组（不够等分的按顺序添加到每个组内）； 注：必须要加order by 7.first_value() over()/last_value() over()： first_value() over():取对应第一条记录； last_value() over()：取对应最后一条记录（可加 ignore nulls 空值填充）向下（上）找最近的不为空的值 还有：nth_value(value any, nth integer)：返回窗口框架中的指定值，如nth_value(salary,2),则表示返回字段salary的第二个窗口函数值 8.keep (dense_rank first/last order by …)over(): 配合max()/min()取集合内第一或最后一条 9.lag(column_name,n,若首行无填充默认为null) over() ：取出前n行数据；lead() over() ：取出前(后)第n行数据；注：必须要加order by排序（11g中支持ignore nulls） 10.ratio_to_report(a) over(partition by b) ： 求按照b分组后a的值在所属分组中总值的占比,a的值必须为数值或数值型字段；注：禁用order by 简单理解就是a/b 11.percent_rank() over(): （所在序号-1）/（总行数-1） 注：必须要加order by排序 12.cume_dist() over() ： 所在组排名序号除以该组所有的行数，注意对于重复行，计算时取重复行中的最后一行的位置 13.precentile_cont( x ) within group(order by …) over()： over()中partition by可选，order by 不可选 x为输入的百分比，是0-1之间的一个小数，返回该百分比位置的数据，若没有则取上下对应两个值的平均值： 14.stddev() over()： 计算样本标准差，只有一行数据时返回0，partition by 可选，order by 可选 stddev_samp() over()：计算样本标准差，只有一行数据时返回null，partition by 可选，order by 可选 stddev_pop() over()：计算总体标准差，partition by 可选，order by 可选 15.variance() over()： 计算样本方差，只有一行数据时返回0，partition by 可选，order by 可选 var_samp() over()：计算样本方差，只有一行数据时返回null，partition by 可选，order by 可选 var_pop() over()：计算总体方差，partition by 可选，order by 可选 –stddev()=sqrt(variance()) sqrt()–求开方 –stddev_samp()=sqrt(var_samp()) –stddec_pop=sqrt(var_pop()) 16.covar_samp over()： 返回一对表达式的样本协方差，partition by 可选，order by 可选 covar_pop over()： 返回一堆表达式的总体协方差，partition by 可选，order by 可选 corr() over() ：返回一对表达式的相关系数，partition by 可选，order by 可选 REGR_ (Linear Regression) Functions：这些线性回归函数适合最小二乘法回归线，有9个不同的回归函数可使用 decode函数 decode函数类似于case when decode函数可以实现行列转换 在逻辑编程中，经常用到If – Then –Else 进行逻辑判断。在DECODE的语法中，实际上就是这样的逻辑处理过程。它的语法如下： Value 代表某个表的任何类型的任意列或一个通过计算所得的任何结果。当每个value值被测试，如果value的值为if1，Decode 函数的结果是then1；如果value等于if2，Decode函数结果是then2；等等。事实上，可以给出多个if/then 配对。 如果value结果不等于给出的任何配对时，Decode 结果就返回else 。 需要注意的是，这里的if、then及else 都可以是函数或计算表达式。 列1 按照行不列出学生信息 1、decode实现 2、case实现 列2、取下表中m对应的n或者f中较大的数据 case实现: 第二种写法 测试使用表达式： 不能使用下面这种>,<,应该使用函数sign替换 decode实现： (sign)函数 如果sign(x)中的x>0 返回1 x>0 返回1 x<0 返回-1 x=0 返回0 decode实现行和列的转换 一、需要将格式1转换为格式2 的表样式 格式1： 格式2： 实例： decode实现行列转换 注意group by 分组函数的使用特点： 在分组函数的select列中只能是group by 后面的分组列，或者聚合函数（比如count(),sum(),avg()等）。 否则会出错。 Oracle 行转列 动态转换的列 pivot进行静态行转列 pivot进行转换的SQL 求不同的客户购买的不同商品的最高价格 求不同的客户购买的不同商品的总价 pivot进行动态行转列 其实原理很简单，就是通过动态sql，去把你不愿意写，或者说是不确定的转换列数，通过查询查出来，拼接进去，然后执行拼接后的sql，创建视图。 创建存储过程： 查找两张表的数据，用一个标识区分 查找a表中id在b表和不在b表中的数据,并使用flag标识数据的来源
2024最新激活全家桶教程，稳定运行到2099年，请移步至置顶文章：https://sigusoft.com/99576.html
版权声明：本文内容由互联网用户自发贡献，该文观点仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请联系我们举报，一经查实，本站将立刻删除。 文章由激活谷谷主-小谷整理,转载请注明出处：https://sigusoft.com/68405.html
