ORACLE分析函数(5) 其他

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统计当前记录在当前窗口中所占的百分比

查看本栏目更多精彩内容:http://www.bianceng.cnhttp://www.bianceng.cn/database/Oracle/

以上是小编为您精心准备的的内容,在的博客、问答、公众号、人物、课程等栏目也有的相关内容,欢迎继续使用右上角搜索按钮进行搜索44
, unbound prefix
499
oracle 分析函数、oracle分析函数大全、oracle分析函数详解、oracle的分析函数、oracle 分析函数 over,以便于您获取更多的相关知识。

时间: 2024-10-02 23:08:16

ORACLE分析函数(5) 其他的相关文章

Oracle分析函数、多维函数和Model函数的简要说明

以下代码均经过测试,可直接运行 Oracle分析函数.多维函数和Model函数简要说明,主要针对BI报表统计,不一定很全面,但对BI应用场景做了少许说明 --创建一张销售数量表,数据趋势是递增的 CREATE TABLE ComputerSales AS SELECT 120+TRUNC(rn/12)+ROUND(DBMS_RANDOM.VALUE(1,10)) SalesNumber FROM ( SELECT level,ROWNUM rn FROM DUAL CONNECT BY ROWN

Oracle分析函数概述

Oracle分析函数概述 Oracle中的分析函数和聚合函数相似,但是对于每一组记录,无论 多少行,聚合函数只返回一行值,而分析函数对其中每一行记录都返回值.这一组记录,称 为分析函数的一个(WINDOW),窗口决定了要处理数据的范围,该范围在物理上可以由指定 的行数来确定,或者在逻辑上由相对偏移量来确定.分析函数总是在除了ORDER BY之外的其 他子句运算后才执行的,所以它不能出现在where.group by等子句中,只能出现在select列 表和order by子句中. 准备测试数据 在

ORACLE分析函数(8) WIDTH-BUCKET

WIDTH-BUCKET会根据参数设置,返回当前记录所属的bucket number.语法格式如下:       WIDTH_BUCKET(expression, minval expression, maxval expression, num buckets)        第一个参数,为某数字或者日期表达式:第二个参数为某范围的下限:第三个参数为某范围的上限:第四个参数为对某范围进行等值划分bucket的数量.如 WIDTH_BUCKET(expression, 0, 2000, 4),会

ORACLE分析函数(2)

下面,我们来解析一下分析函数的语法格式(语法格式图请参照:http://blog.csdn.net/yidian815/article/details/12709223). 函数名称 对it人士来时,再简单不过的东西了,不做描述. 函数参数: 分析函数通常会具有0-3个参数. 分区子句: 通过分区子句,可以对记录集进行分区,然后针对每个分区分别进行统计运算.在分析函数中,使用分区子句不必使用()将子句包围起来.在一个查询当中,我们可以使用多个分析函数,每个分析函数可以使用独立的分区规则.如果没有

ORACLE分析函数(1)

分析函数式ORACLE提供的用来进行数据统计的强有力工具,与我们常用的聚合函数具有一些相似性,但又完全不同.聚合函数,首先会将数据进行分组,然后对每一组数据进行运算,如求和sum,求平均AVG等,对于聚合函数,每一个分组返回一条数据结果行.分析函数,同样也会对数据进行求和或者求平均等运算,但是,分析函数计算的基础并不是分组,而是窗口,而且分析函数根据每条记录会返回一个处理结果. 首先,看一下下面的例子: SQL> select prod_subcategory_id,1 from product

常用Oracle分析函数大全_oracle

Oracle的分析函数功能非常强大,工作这些年来经常用到.这次将平时经常使用到的分析函数整理出来,以备日后查看. 我们拿案例来学习,这样理解起来更容易一些. 1.建表 create table earnings -- 打工赚钱表 ( earnmonth varchar2(6), -- 打工月份 area varchar2(20), -- 打工地区 sno varchar2(10), -- 打工者编号 sname varchar2(20), -- 打工者姓名 times int, -- 本月打工次

【分析函数】Oracle分析函数之LAG和LEAD

[分析函数]Oracle分析函数之LAG和LEAD Lag和Lead函数可以在一次查询中取出同一字段的前N行的数据和后N行的值.这种操作可以使用对相同表的表连接来实现,不过使用LAG和LEAD有更高的效率. lag的语法如下: lead的语法如下: lead 和lag 的语法类似以下以lag为例进行讲解! lag(exp_str,offset,defval) over() exp_str 是要做对比的字段 offset 是exp_str字段的偏移量 比如说 offset 为2 则 拿exp_st

Oracle 分析函数及常用函数

什么叫分析函数(Analytic function)? Oracle从8.1.6开始提供分析函数,分析函数用于计算基于组的某种聚合值,它和聚合函数的不同之处是 对于每个组返回多行,而聚合函数对于每个组只返回一行. 基本语法 function_name(arg1,arg2,...) OVER (<partition-clause> <order-by-clause > <window clause>) 另外,还需要提到的一点是,在有分析函数参与的SQL语句中,执行流程依次

Oracle分析函数简介:排序

Oracle常用的用于排序的分析函数有3种,rank().dense_rank().row_number(); 测试表如下: 1.rank() 跳跃式排名 with temp as ( select t_id,t_age, rank() over(order by t_age desc) sec  from aaa ) select * from temp --where sec=1--,加上此过滤条件,即可找出排名为1的记录 如果指定分组的话,如下: with temp as ( select