Oracle开发之报表函数

   2016-06-20 0
核心提示:本文主要介绍Oracle报表函数RATIO_TO_REPORT的具体使用方法,需要的朋友可以参考下。

一、回顾一下前面《Oracle开发之窗口函数》中关于全统计一节,我们使用了Oracle提供的:

复制代码 代码如下:
sum(sum(tot_sales)) over (order by month rows between unbounded preceding and unbounded following)

来统计全年的订单总额,这个函数会在记录集形成的过程中,每检索一条记录就执行一次,它总共执行了12次。这是非常费时的。实际上我们还有更简便的方法:

复制代码 代码如下:
SQL> select month,
         sum(tot_sales) month_sales,
         sum(sum(tot_sales)) over(order by month
         rows between unbounded preceding and unbounded following) win_sales,
         sum(sum(tot_sales)) over() rpt_sales
    from orders
   group by month;

     MONTH MONTH_SALES WINDOW_SALES REPORT_SALES
---------- ----------- ------------ ------------
         1      610697      6307766      6307766
         2      428676      6307766      6307766
         3      637031      6307766      6307766
         4      541146      6307766      6307766
         5      592935      6307766      6307766
         6      501485      6307766      6307766
         7      606914      6307766      6307766
         8      460520      6307766      6307766
         9      392898      6307766      6307766
        10      510117      6307766      6307766
        11      532889      6307766      6307766
        12      492458      6307766      6307766

已选择12行。

over函数的空括号表示该记录集的所有记录都应该被列入统计的范围,如果使用了partition by则先分区,再依次统计各个分区。

二、RATIO_TO_REPORT函数:

报表函数特(窗口函数)特别适合于报表中需要同时显示详细数据和统计数据的情况。例如在销售报告中经常会出现这样的需求:列出上一年度每个月的销售总额、年底销售额以及每个月的销售额占全年总销售额的比例:

方法①:

复制代码 代码如下:
select all_sales.*,
           100 * round(cust_sales / region_sales, 2) || '%' Percent
 from (select o.cust_nbr customer,
                        o.region_id region,
                       sum(o.tot_sales) cust_sales,
                       sum(sum(o.tot_sales)) over(partition by o.region_id) region_sales
               from orders_tmp o
            where o.year = 2001
             group by o.region_id, o.cust_nbr) all_sales
 where all_sales.cust_sales > all_sales.region_sales * 0.2;

这是一种笨方法也是最易懂的方法。

方法②:

复制代码 代码如下:
select region_id, salesperson_id,
           sum(tot_sales) sp_sales,
           round(sum(tot_sales) / sum(sum(tot_sales))
                      over (partition by region_id), 2) percent_of_region
  from orders
where year = 2001
 group by region_id, salesperson_id
 order by region_id, salesperson_id;

方法③

复制代码 代码如下:
select region_id, salesperson_id,
            sum(tot_sales) sp_sales,
            round(ratio_to_report(sum(tot_sales))
                          over (partition by region_id), 2) sp_ratio
   from orders
where year = 2001
group by region_id, salesperson_id
order by region_id, salesperson_id;

Oracle提供的Ratio_to_report函数允许我们计算每条记录在其对应记录集或其子集中所占的比例。

以上就是Oracle报表函数用法的全部内容,希望能给大家一个参考,也希望大家多多支持乐学网(lexue001.com)。

 
标签: Oracle 函数
反对 0举报 0 评论 0
 

免责声明:本文仅代表作者个人观点,与乐学笔记(本网)无关。其原创性以及文中陈述文字和内容未经本站证实,对本文以及其中全部或者部分内容、文字的真实性、完整性、及时性本站不作任何保证或承诺,请读者仅作参考,并请自行核实相关内容。
    本网站有部分内容均转载自其它媒体,转载目的在于传递更多信息,并不代表本网赞同其观点和对其真实性负责,若因作品内容、知识产权、版权和其他问题,请及时提供相关证明等材料并与我们留言联系,本网站将在规定时间内给予删除等相关处理.

  • 去重复的sql(Oracle) 去重复的英文
    1.利用group by 去重复2.可以利用下面的sql去重复,如下  1) select id,name,sex from (select a.*,row_number() over(partition by a.id,a.set order by name) su from test a ) where su=1  2)select id,name,sex from (select a.*,row_number() over(p
    02-10
  • Oracle SQL七次提速技巧
    以下SQL执行时间按序号递减。1,动态SQL,没有绑定变量,每次执行都做硬解析操作,占用较大的共享池空间,若共享池空间不足,会导致其他SQL语句的解析信息被挤出共享池。create or replace procedure proc1as beginfor i in 1..100000 loop    execute imme
    02-10
  • Oracle\SQL  Server等及其他基本语句写法
    Oracle\SQL Server等及其他基本语句写法
    Oracle\SQL  Server等及其他基本语句写法目录一.Excel相关 11.Excel中写脚本范例: 12.提取字节 23. 提取单元格内字符 24.VLOOKUP函数: 2二.SQL语句汇总 21.建表: 22.增 33.删 44.查 65.改 236.Alter的应用 24三.数据库备份与恢复脚本 261. Oracle: 2
    02-10
  • SQL ORACLE case when函数用法
    case when 用法(1)简单case函数:格式:  case 列名   when 条件值1 then 选项1  when 条件值1 then 选项2......  else 默认值 end例如:  select   case job_level  when '1' then '1111'  when '2' then '2222'   when '3' then '3333
    02-10
  • Oracle迁移到MySQL性能下降的注意点 oracle数据
    背景:最近有较多的客户系统由原来由Oracle改造到MySQL后出现了性能问题CPU 100%,或是后台的CRM系统复杂SQL在业务高峰的时候出现堆积导致业务故障。在我的记忆里面淘宝最初从Oracle迁移到MySQL期间也遇到了很多SQL的性能问题,记忆最为深刻的子查询,当初的
    02-10
  • ORACLE中通过SQL语句(alter table)来增加、删除
    1.添加字段:alter table  表名  add (字段  字段类型)  [ default  '输入默认值']  [null/not null]  ;2.添加备注:comment on column  库名.表名.字段名 is  '输入的备注';  如: 我要在ers_data库中  test表 document_type字段添加备注  comm
    02-10
  • MySQL与Oracle 差异比较之六触发器
    触发器编号类别ORACLEMYSQL注释1创建触发器语句不同create or replace trigger TG_ES_FAC_UNIT  before insert or update or delete on ES_FAC_UNIT  for each rowcreate trigger `hs_esbs`.`TG_INSERT_ES_FAC_UNIT` BEFORE INSERT on `hs_esbs`.`es_fac_u
    02-10
  • Oracle的HINT可以强制指定SQL的执行计划,比如
    在Oracle中查看所有的表: select * from tab/dba_tables/dba_objects/cat; 看用户建立的表 :  select table_name from user_tables;  //当前用户的表 select table_name from all_tables;  //所有用户的表 select table_name from dba_tables;  //包
    02-10
  • Oracle sql 子字符串长度判断
    Oracle sql 子字符串长度判断 select t.* from d_table t WHEREsubstr(t.col,1,1)='8' and instr(t.col,'/')0 and length(substr(t.col,1,instr(t.col,'/')))5; 字符串的前两位都是数字:select * from d_table t WHERE regexp_like(substr(t.col,1,2), '^[
    02-10
  • Oracle、MySql、Sql Server比对
    MySql:廉价(部分免费):当前,MySQL採用双重授权(DualLicensed),他们是GPL和MySQLAB制定的商业许可协议。假设你在一个遵循GPL的***(开源)项目中使用MySQL,那么你能够遵循GPL协议免费使用MySQL。否则,你须要购买MySQLAB制定的那个商业许可协议。Windows $
    02-10
点击排行