ORACLE分析函数(5)---其他

最新推荐文章于 2022-04-05 18:36:03 发布
最新推荐文章于 2022-04-05 18:36:03 发布
阅读量1k 收藏 1
点赞数 1
分类专栏： Oracle Develop Oracle Warehouse 文章标签： oracle 分析函数 统计 分析 over
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/yidian815/article/details/12774807
版权

1.除了使用数字来指定窗口范围，我们还可以使用日期类型，如：


2.lead和lag，返回当前窗口中与当前记录距离为n的记录。lag为向前取记录，lead为向后取记录

SELECT prod_id,
  lag(prod_list_price,1) over(order by prod_id) pre_1,
  lag(prod_list_price,2) over(order by prod_id) pre_2,
  prod_list_price,
  lead(prod_list_price,1) over(order by prod_id) lead_1,
  lead(prod_list_price,2) over(order by prod_id) lead_2
FROM products;
   PROD_ID      PRE_1      PRE_2 PROD_LIST_PRICE     LEAD_1     LEAD_2
---------- ---------- ---------- --------------- ---------- ----------
        13                                899.99     999.99     999.99
        14     899.99                     999.99     999.99     299.99
        15     999.99     899.99          999.99     299.99    1099.99
        16     999.99     999.99          299.99    1099.99    1299.99
        17     299.99     999.99         1099.99    1299.99      55.99
        18    1099.99     299.99         1299.99      55.99     599.99
        19    1299.99    1099.99           55.99     599.99     899.99
        20      55.99    1299.99          599.99     899.99      24.99
        21     599.99      55.99          899.99      24.99      21.99
        22     899.99     599.99           24.99      21.99      45.99
        23      24.99     899.99           21.99      45.99     112.99
        24      21.99      24.99           45.99     112.99     149.99
        25      45.99      21.99          112.99     149.99      44.99
        26     112.99      45.99          149.99      44.99     199.99
        27     149.99     112.99           44.99     199.99     499.99
        28      44.99     149.99          199.99     499.99       9.99
        29     199.99      44.99          499.99       9.99       8.99
        30     499.99     199.99            9.99       8.99      67.99
        31       9.99     499.99            8.99      67.99      44.99
        32       8.99       9.99           67.99      44.99      39.99
        33      67.99       8.99           44.99      39.99      49.99
        34      44.99      67.99           39.99      49.99      44.99
        35      39.99      44.99           49.99      44.99      54.99
        36      49.99      39.99           44.99      54.99      29.99

3.frist_value  last_value返回当前窗口的第一条记录和最后一条记录 
SELECT prod_id,
  first_value(prod_list_price) over( order by prod_id rows BETWEEN 1 preceding AND 1 following) pre_prod_price,
  prod_list_price,
  last_value(prod_list_price) over( order by prod_id rows BETWEEN 1 preceding AND 1 following) next_prod_price
FROM products;
   PROD_ID PRE_PROD_PRICE PROD_LIST_PRICE NEXT_PROD_PRICE
---------- -------------- --------------- ---------------
        13         899.99          899.99          999.99
        14         899.99          999.99          999.99
        15         999.99          999.99          299.99
        16         999.99          299.99         1099.99
        17         299.99         1099.99         1299.99
        18        1099.99         1299.99           55.99
        19        1299.99           55.99          599.99
        20          55.99          599.99          899.99
        21         599.99          899.99           24.99
        22         899.99           24.99           21.99
        23          24.99           21.99           45.99
        24          21.99           45.99          112.99
        25          45.99          112.99          149.99
        26         112.99          149.99           44.99
        27         149.99           44.99          199.99
        28          44.99          199.99          499.99
        29         199.99          499.99            9.99
        30         499.99            9.99            8.99
        31           9.99            8.99           67.99
        32           8.99           67.99           44.99
        33          67.99           44.99           39.99
        34          44.99           39.99           49.99
        35          39.99           49.99           44.99
        36          49.99           44.99           54.99
        37          44.99           54.99           29.99

4.性能小贴士
        如果我们使用sum（col2）over（order  by  col1 range between unbounded preceding and unbounded following ) ，此时每条记录都会进行统计运算，执行效率不高，而如果使用sum(col2) over()则仅仅统计一次，执行效率就会有所提升。下面的示例显示，效率为1/3

   

SQL> /

  COUNT(*)
----------
    918843

已用时间:  00: 00: 00.81
SQL> select count(*) from (
select prod_id,sum(amount_sold) over () from sales)
;  2    3  

  COUNT(*)
----------
    918843

已用时间:  00: 00: 00.27

5.ration_to_report统计当前记录在当前窗口中所占的百分比 

SQL> select prod_id,prod_list_price,sum(prod_list_price) over() tsum,ratio_to_report(prod_list_price) over() fx from products;

   PROD_ID PROD_LIST_PRICE       TSUM         FX
---------- --------------- ---------- ----------
        13          899.99   10047.28 .089575487
        14          999.99   10047.28  .09952843
        15          999.99   10047.28  .09952843
        16          299.99   10047.28 .029857832
        17         1099.99   10047.28 .109481372
        18         1299.99   10047.28 .129387257
        19           55.99   10047.28 .005572652
        20          599.99   10047.28  .05971666
        21          899.99   10047.28 .089575487
        22           24.99   10047.28  .00248724
        23           21.99   10047.28 .002188652
        24           45.99   10047.28 .004577358
        25          112.99   10047.28  .01124583
        26          149.99   10047.28 .014928418
        27           44.99   10047.28 .004477829
        28          199.99   10047.28  .01990489
        29          499.99   10047.28 .049763717
        30            9.99   10047.28 .000994299
        31            8.99   10047.28  .00089477
        32           67.99   10047.28 .006767006
        33           44.99   10047.28 .004477829
        34           39.99   10047.28 .003980182
        35           49.99   10047.28 .004975476
        36           44.99   10047.28 .004477829
        37           54.99   10047.28 .005473123
        38           29.99   10047.28 .002984887
        39           34.99   10047.28 .003482535
        40           44.99   10047.28 .004477829
        41           44.99   10047.28 .004477829
        42           44.99   10047.28 .004477829
        43           44.99   10047.28 .004477829
        44           44.99   10047.28 .004477829
        45           44.99   10047.28 .004477829
        46           22.99   10047.28 .002288181
        47           28.99   10047.28 .002885358
        48           11.99   10047.28 .001193358
       113           22.99   10047.28 .002288181
       114           18.99   10047.28 .001890064
       115            8.99   10047.28  .00089477
       116           11.99   10047.28 .001193358
       117            8.99   10047.28  .00089477
       118            7.99   10047.28  .00079524
       119            6.99   10047.28 .000695711
       120            6.99   10047.28 .000695711
       121           10.99   10047.28 .001093828
       122           18.99   10047.28 .001890064
       123           49.99   10047.28 .004975476
       124           18.99   10047.28 .001890064
       125           15.99   10047.28 .001591476
       126           28.99   10047.28 .002885358
       127           36.99   10047.28 .003681593
       128           27.99   10047.28 .002785829
       129          192.99   10047.28 .019208184
       130           89.99   10047.28 .008956653
       131           18.99   10047.28 .001890064
       132           24.99   10047.28  .00248724
       133           30.99   10047.28 .003084417
       134           20.99   10047.28 .002089123
       135           49.99   10047.28 .004975476
       136           32.99   10047.28 .003283476
       137           52.99   10047.28 .005274064
       138           69.99   10047.28 .006966064
       139           19.99   10047.28 .001989593
       140           29.99   10047.28 .002984887
       141           29.99   10047.28 .002984887
       142           19.99   10047.28 .001989593
       143           19.99   10047.28 .001989593
       144            7.99   10047.28  .00079524
       145           12.99   10047.28 .001292887
       146           11.99   10047.28 .001193358
       147            7.99   10047.28  .00079524
       148           20.99   10047.28 .002089123


注意：

        部分分析函数在选择列时支持distinct，如果你指定了该参数，则over条件中就只能指定partition子句，而不能再指定order by 子句了







ezbit
关注
  • 1
    点赞
  • 踩
  • 1
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
oracle计算收益率,oracle 分析函数总结--仅供参考
weixin_42301086的博客
04-10 665
oracle 分析函数总结--仅供参考/*目录：1.语法2.常见函数及用法2.1排序函数2.2wm_concat()能实现不同行同字段的合并！！！！生产环境慎用2.3开窗用法2.4占比函数ratio_to_report()over(partitionby)2.5前一个lag后一个lead2.6第一个first_value最后一个last_value2.7加1连乘再减...
Oracle分析函数实践 - blog.docx
05-16
Oracle分析函数是数据库查询中非常强大的工具，它们用于处理复杂的报表和统计需求，特别是在在线分析处理（OLAP）环境中。分析函数与聚合函数的主要区别在于，聚合函数对每个组只返回一行结果，而分析函数则可以针对...
ORACLE 实用函数总结,oracle 分析函数总结--仅供参考
weixin_33767088的博客
04-03 536
oracle 分析函数总结--仅供参考/*目录：1.语法2.常见函数及用法2.1排序函数2.2wm_concat()能实现不同行同字段的合并！！！！生产环境慎用2.3开窗用法2.4占比函数ratio_to_report()over(partitionby)2.5前一个lag后一个lead2.6第一个first_value最后一个last_value2.7加1连乘再减...
Oracle 分析汇总函数--报表
l690781365的博客
08-08 650
很多需求中都涉及到统计:均值、累计、范围均值、相邻记录比较等。这些操作会统计多次，或有明确的统计范围，或返回的记录统计的数据集不同... 根据场景不同可分为如下几类: 1. 全统计 2. 滚动统计 3. 范围统计 4. (相邻)行比较 构建测试数据： SQL> desc criss_sales; Name Type Nullable Default C...
oracle 排序的分析函数,oracle菜鸟学习之 分析函数-排序
weixin_33544518的博客
04-02 460
oracle菜鸟学习之 分析函数-排序排序函数1.row_number:返回连续的排序，无论值是否相等2.rank：具有相等值得行排序相同，序数值随后跳跃3.dense_rank:具有相等值得行排序相同，序号是连续得实验表create table chengji(sno number,km varchar2(10),score number);insert into chengji values(...
Oracle 最值的优化 -- 利用分析函数改写
Hehuyi_In的博客
04-05 2348
一、 最值在表关联中 1. 低效且有重复值的写法 找出各部门工资最高的员工信息 select a.EMPNO, a.ENAME, a.JOB, a.MGR, a.HIREDATE, a.SAL, a.COMM, a.DEPTNO from emp a, (select deptno, max(sal) as max_sal from emp group by deptno) b where a.deptno = b.deptno and a.sal = b.max_sal ...
oracle分析函数-开窗函数
LETIAN_Z的博客
01-10 556
oracle分析函数 分析函数是什么？ 分析函数是Oracle专门用于解决复杂报表统计需求的功能强大的函数，它可以在数据中进行分组然后计算基于组的某种统计值，并且每一组的每一行都可以返回一个统计值。 分析函数和聚合函数的不同之处是什么？ 普通的聚合函数用group by分组，每个分组返回一个统计值，而分析函数采用partition by分组，并且每组每行都可以返回一个统计值。 分析函数-开窗函数的...
oracle内存bucket,ORACLE分析函数-WIDTH-BUCKET
weixin_30070647的博客
04-11 286
ORACLE分析函数-WIDTH-BUCKETWIDTH-BUCKET会根据参数设置，返回当前记录所属的bucket number。语法格式如下：WIDTH_BUCKET(expression, minval expression, maxval expression, num buckets)第一个参数，为某数字或者日期表达式；第二个参数为某范围的下限；第三个参数为某范围的上限；第四个参数为对某...
Oracle之分析函数
热门推荐
博弈星宇
03-24 1万+
1.认识分析函数 什么是分析函数？ 分析函数是Oracle专门用于解决复杂报表统计需求的功能强大的函数，它可以在数据中进行分组然后计算基于组的某种统计值，并且每一组的每一行都可以返回一个统计值。 分析函数和聚合函数的不同？ 普通的聚合函数用group by分组，每个分组返回一个统计值；而分析函数采用partition by 分组，并且每组每行都可以返回一个统计值。 分析函数的形式： 分析函数带有一个开窗函数over(),包含三个分析子句：分组(partit...
Oracle自定义聚合函数-分析函数
11-08
Oracle 自定义聚合函数-分析函数 Oracle 自定义聚合函数是一种强大且灵活的功能，允许用户根据自己的需求创建自定义的聚合函数。聚合函数是一种特殊的函数，它可以对一组数据进行处理和计算，并返回一个结果。 ...
ORACLE分析函数.ppt
11-12
ORACLE分析函数 ORACLE分析函数是数据库管理系统中的一种功能强大且灵活的分析工具，能够对数据进行复杂的分析和处理。通过使用分析函数，开发者可以更加方便地实现业务逻辑，提高查询效率和数据处理速度。 在本...
Oracle-使用-技巧.doc
11-12
Oracle Index Tuning Wizard（索引调优向导）就是这样一个工具，它能帮助用户分析当前数据库的索引使用情况，并提供优化建议。 在使用Index Tuning Wizard时，首先需要运行分析，这通常涉及执行SQL工作负载。这个...
东软oracle-在线-期中考试一.docx
最新发布
12-14
5. 名词解释页脚内容 5：可以使用TRUNC函数来将数字值截断到指定的格式。使用TO_CHAR函数可以将日期值或数字值转换为字符串形式。 6. 名词解释页脚内容 6：可以使用TO_CHAR函数来将日期值或数字值转换为字符串形式...
oracle分析函数-排名函数
本作者：想花
09-24 252
一共有3种 rank()并列 不连续 dense_rank()并列 连续 row_number()不并列 连续
oracle 分析函数加条件,[Oracle] 分析函数（1）- 语法
weixin_34622572的博客
04-03 370
语法概述Analytic-FunctionAnalytic_Clause之PARTITION子句Analytic_Clause之ORDER BY子句Analytic_Clause之WINDOWING子句举例：下面看一个例子简单过下语法：例:sum(sal) over (partition by deptno order by ename) new_aliassum就是函数名(sal)是分析函数的参...
ORACLE分析函数(6)--使用listagg实现行列转换
ezbit
10-18 5112
listagg函数是在oracle11g中新引入的聚合函数，当然也可以作为分析函数来使用。该函数可以基于分组内特定列的排序，将测量列进行拼接合并。
Oracle中二进制、八进制、十进制和十六进制相互转换函数
ezbit
09-18 3330
最近，工作中需要查看数据库中某些字段的二进制表示格式，索性写了一些进制质检相互转换的过程函数，以方便调用。 记录下这些代码，以备使用。 create or replace package pkg_digit_conv as --将字符串转换为其在数据库中的二进制存储格式 function fun_str2bin(par_str in varchar2) return varc
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值