oracle计算占比和环比增长率案例

已于 2022-05-12 18:40:41 修改
阅读量2.7k 收藏 9
点赞数 1
分类专栏： 数据库 文章标签： oracle 数据库 sql
于 2022-05-12 18:39:39 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/qq_39537546/article/details/124736199
版权

前言

        在数据库做统计分析的时候，我们可能需要结合不同的需求计算数据的汇总、平均、占比、环比、同步等等，那么在通过sql硬编码前我们是否应该考虑数据的各种问题，以及在编写代码时，我们是否应该通过各种函数进行数据预处理，避免最终预览数据时出现各种错误呢？

需求：

        根据科室、时间维度分组， 对不同科室人数、科室总金额进行统计汇总，以及计算科室不同药品的使用情况 如：各药品的占比、环比增长率等等？

1)对科室、时间维度进行分组，对人数进行count,对总金额进行sum
注：使用group by +聚合函数即可（sum、count、…）等
2)对药品的使用情况 进行占比分析、环比增长率
注：药品占比分析 某类型药费用总额/费用总额 * 100
需考虑情况：某类型药费用总额 或 费用总额 为 0的情况

解读环比增长率：环比增长率=（本期数-上期数）÷上期数×100%
药品环比增长率 （本月费用总额-上月费用总额）上月费用总额*100

需考虑3种情况：

1) 本月费用总额、上月费用总额 同时为 0 如 （0-0）/0 则提示ORA-01476
2) 本月费用总额、上月费用总额 不为0但是数据相等 （1000-1000）/1000=0
3) 本月费用总额不为0、上月费用总额为0 （1000-0）/0 则提示ORA-01476
所以需要针对该3种情况进行处理，可通过case when 函数



案例基础数据准备

建表使用中文命名字段是为了方便大家阅读，实际工作情况下 不推荐此骚操作。

CREATE TABLE MY_TABLE_TEST (
  "结算时间" VARCHAR2(255),
  "科室代码" VARCHAR2(255),
  "科室名称" VARCHAR2(255),
  "人次" NUMBER,
  "次均费用" NUMBER,
  "费用明细" NUMBER,
  "中药" NUMBER,
  "中成药费" NUMBER,
  "西药" NUMBER,
  "耗材" NUMBER );
INSERT INTO MY_TABLE_TEST("结算时间", "科室代码", "科室名称", "人次", "次均费用", "费用明细", "中药", "中成药费", "西药", "耗材") VALUES ('2021-12', '101', 'AAA', 8, 269.65, 2157.19, 0, 0, 259.19, 0);
INSERT INTO MY_TABLE_TEST("结算时间", "科室代码", "科室名称", "人次", "次均费用", "费用明细", "中药", "中成药费", "西药", "耗材") VALUES ('2022-01', '101', 'AAA', 72, 3081.32, 221854.81, 0, 80.85, 34520.06, 8400.8);
INSERT INTO MY_TABLE_TEST("结算时间", "科室代码", "科室名称", "人次", "次均费用", "费用明细", "中药", "中成药费", "西药", "耗材") VALUES ('2022-02', '101', 'AAA', 58, 4621.79, 268063.98, 0, 181.44, 33361.24, 53853.5);
INSERT INTO MY_TABLE_TEST("结算时间", "科室代码", "科室名称", "人次", "次均费用", "费用明细", "中药", "中成药费", "西药", "耗材") VALUES ('2022-03', '101', 'AAA', 84, 2970.27, 249502.28, 413.64, 421.76, 40433.68, 11174.9);
INSERT INTO MY_TABLE_TEST("结算时间", "科室代码", "科室名称", "人次", "次均费用", "费用明细", "中药", "中成药费", "西药", "耗材") VALUES ('2022-04', '101', 'AAA', 65, 2762.42, 179557.49, 571.45, 432.8, 30034.14, 5624.8);
INSERT INTO MY_TABLE_TEST("结算时间", "科室代码", "科室名称", "人次", "次均费用", "费用明细", "中药", "中成药费", "西药", "耗材") VALUES ('2022-05', '101', 'AAA', 31, 2362.88, 73249.27, 0, 205.2, 11180.97, 2207.7);
INSERT INTO MY_TABLE_TEST("结算时间", "科室代码", "科室名称", "人次", "次均费用", "费用明细", "中药", "中成药费", "西药", "耗材") VALUES ('2022-01', '202', 'BBB', 91, 4636.3, 421903.67, 0, 2850.69, 51995.28, 15754.6);
INSERT INTO MY_TABLE_TEST("结算时间", "科室代码", "科室名称", "人次", "次均费用", "费用明细", "中药", "中成药费", "西药", "耗材") VALUES ('2022-02', '202', 'BBB', 89, 5299.69, 471672, 0, 3606.04, 63211.36, 18940.5);
INSERT INTO MY_TABLE_TEST("结算时间", "科室代码", "科室名称", "人次", "次均费用", "费用明细", "中药", "中成药费", "西药", "耗材") VALUES ('2022-03', '202', 'BBB', 130, 5277.78, 686111.03, 0, 5606.76, 86714.17, 25642.9);
INSERT INTO MY_TABLE_TEST("结算时间", "科室代码", "科室名称", "人次", "次均费用", "费用明细", "中药", "中成药费", "西药", "耗材") VALUES ('2022-04', '202', 'BBB', 117, 4797.85, 561347.96, 0, 4996.95, 74157.71, 22723.1);
INSERT INTO MY_TABLE_TEST("结算时间", "科室代码", "科室名称", "人次", "次均费用", "费用明细", "中药", "中成药费", "西药", "耗材") VALUES ('2022-05', '202', 'BBB', 45, 5563.04, 250336.67, 0, 1773.8, 32920.17, 10349.9);

sql统计示例

（复制可使用，注释的地方主要为了减少可阅读性）

select 结算时间,科室代码,
       科室名称,
       count(科室名称) as 人次,round(sum(费用明细)/count(科室名称),2) as 次均费用,sum(费用明细) AS 费用明细,
       sum(中药) aS 中药,
       replace(to_char(round((sum(中药)/ sum(费用明细)*100.00),2),'fm999990.9999')||'%','0.%','0%')  AS 中药占比,  --将相除的数据转成字符串,并将可能出现字符进行替换
       replace(  replace( replace(to_char( (case
            when sum(中药) - LAG(sum(中药), 1, 0) OVER (PARTITION BY 科室名称 ORDER BY 结算时间) = 0 then LAG(sum(中药), 1, 0) OVER (PARTITION BY 科室名称 ORDER BY 结算时间) --判断（0-0=0）本月-减上月=0 的情况。不判断可能会出现 ORA-01476: 除数为0
            when sum(中药) - LAG(sum(中药), 1, 0) OVER (PARTITION BY 科室名称 ORDER BY 结算时间) = sum(中药) then 0   --判断 （5000-500=0）判断本月和上月数据一样相减等于0 的情况。不判断可能会出现 ORA-01476: 除数为0
            else round((sum(中药) - LAG(sum(中药), 1, 0) OVER (PARTITION BY 科室名称 ORDER BY 结算时间)) /
                       decode(LAG(sum(中药), 1, 0) OVER (PARTITION BY 科室名称 ORDER BY 结算时间), 0, 1,
                              LAG(sum(中药), 1, 0) OVER (PARTITION BY 科室名称 ORDER BY 结算时间)),
                       4)     --判断（本月-上月 ）/上月 不等于0 的情况
                     *100 end) , 'fm999990.9999') || '%', '0.%',   '0%') ,'-1.%','-100%'),'1.%','100%')   as 中药环比增长,  --将最后数据转字符串，拼接 % ，并将可能出现字符进行替换
      LAG(sum(中药), 12, 0) OVER (PARTITION BY 科室名称 ORDER BY to_date(结算时间,'yyyy-mm')) as 环比-- 同比分析，与上个年度相同月份进行比较
     /*   sum(中成药费) as 中成药费,

       replace(to_char(round((sum(中成药费)/ sum(费用明细)*100.00),2),'fm999990.9999')||'%','0.%','0%') AS  中成药占比,
       replace( replace(replace(to_char((case
            when sum(中成药费) - LAG(sum(中成药费), 1, 0) OVER (PARTITION BY 科室名称 ORDER BY 结算时间) = 0 then LAG(sum(中成药费), 1, 0) OVER (PARTITION BY 科室名称 ORDER BY 结算时间)
            when sum(中成药费) - LAG(sum(中成药费), 1, 0) OVER (PARTITION BY 科室名称 ORDER BY 结算时间) = sum(中成药费) then 0
            else round((sum(中成药费) - LAG(sum(中成药费), 1, 0) OVER (PARTITION BY 科室名称 ORDER BY 结算时间)) /
                       decode(LAG(sum(中成药费), 1, 0) OVER (PARTITION BY 科室名称 ORDER BY 结算时间), 0, 1,
                              LAG(sum(中成药费), 1, 0) OVER (PARTITION BY 科室名称 ORDER BY 结算时间)),
                       4)*100 end) , 'fm999990.9999') || '%', '0.%',   '0%'),'-1.%','-100%'),'1.%','100%')    as 中成药费环比增长,
      sum(西药) as 西药 ,
      replace(to_char(round((sum(西药)/sum(费用明细)*100.00),2),'fm999990.9999')||'%','0.%','0%') AS 西药占比,
      replace(  replace( replace(to_char((case
            when sum(西药) - LAG(sum(西药), 1, 0) OVER (PARTITION BY 科室名称 ORDER BY 结算时间) = 0 then LAG(sum(西药), 1, 0) OVER (PARTITION BY 科室名称 ORDER BY 结算时间)
            when sum(西药) - LAG(sum(西药), 1, 0) OVER (PARTITION BY 科室名称 ORDER BY 结算时间) = sum(西药) then 0
            else round((sum(西药) - LAG(sum(西药), 1, 0) OVER (PARTITION BY 科室名称 ORDER BY 结算时间)) /
                       decode(LAG(sum(西药), 1, 0) OVER (PARTITION BY 科室名称 ORDER BY 结算时间), 0, 1,
                              LAG(sum(西药), 1, 0) OVER (PARTITION BY 科室名称 ORDER BY 结算时间)),
                       4)*100 end) , 'fm999990.9999') || '%', '0.%',   '0%'),'-1.%','-100%'),'1.%','100%')     as 西药环比增长,
       sum(耗材) as  耗材,
      replace(to_char(round((sum(耗材)/sum(费用明细)*100.00),2),'fm999990.9999')||'%','0.%','0%')  AS 耗材占比 ,
      replace(  replace( replace(to_char((case
            when sum(耗材) - LAG(sum(耗材), 1, 0) OVER (PARTITION BY 科室名称 ORDER BY 结算时间) = 0 then  LAG(sum(耗材), 1, 0) OVER (PARTITION BY 科室名称 ORDER BY 结算时间)
            when sum(耗材) - LAG(sum(耗材), 1, 0) OVER (PARTITION BY 科室名称 ORDER BY 结算时间) = sum(耗材) then 0
            else round((sum(耗材) - LAG(sum(耗材), 1, 0) OVER (PARTITION BY 科室名称 ORDER BY 结算时间)) /
                       decode(LAG(sum(耗材), 1, 0) OVER (PARTITION BY 科室名称 ORDER BY 结算时间), 0, 1,
                              LAG(sum(耗材), 1, 0) OVER (PARTITION BY 科室名称 ORDER BY 结算时间)),
                       4)*100 end) , 'fm999990.9999') || '%', '0.%',   '0%') ,'-1.%','-100%')  ,'1.%','100%')       as 耗材环比增长*/
from  MY_TABLE_TEST  where  科室名称 in ('AAA','BBB')
  group by 科室代码 ,  科室名称,结算时间
order  by 科室名称  desc;

sql示例分析环比增长率

replace(  replace( replace(to_char( (case
        when sum(中药) - LAG(sum(中药), 1, 0) OVER (PARTITION BY 科室名称 ORDER BY 结算时间) = 0 then LAG(sum(中药), 1, 0) OVER (PARTITION BY 科室名称 ORDER BY 结算时间) --判断（0-0=0）本月-减上月=0 的情况。不判断可能会出现 ORA-01476: 除数为0
        when sum(中药) - LAG(sum(中药), 1, 0) OVER (PARTITION BY 科室名称 ORDER BY 结算时间) = sum(中药) then 0   --判断 （5000-500=0）判断本月和上月数据一样相减等于0 的情况。不判断可能会出现 ORA-01476: 除数为0
        else round((sum(中药) - LAG(sum(中药), 1, 0) OVER (PARTITION BY 科室名称 ORDER BY 结算时间)) /
                   decode(LAG(sum(中药), 1, 0) OVER (PARTITION BY 科室名称 ORDER BY 结算时间), 0, 1,
                          LAG(sum(中药), 1, 0) OVER (PARTITION BY 科室名称 ORDER BY 结算时间)),
                   4)     --判断（本月-上月 ）/上月 不等于0 的情况
                 *100 end) , 'fm999990.9999') || '%', '0.%',   '0%') ,'-1.%','-100%'),'1.%','100%')   as 中药环比增长

在这里插入图片描述
1、通过case when 判断数据的三种情况 ，防止出现整除为0 。
2、通过to_char(xxx,'fm999990.9999') 格式化数据转为字符串
3、通过||拼接符 拼接 %
4、将可能出现的数据 进行替换。

结尾

通过步骤拆解，所需要的功能基本实现，虽然该实现可能不是最优的，但希望能给部分所需要的同学带来一定的参考，所以当大家碰到此类问题基本不要慌，一步步去分析，一步步去尝试，最后，希望大家的技术越学越强。


                                                                                                 – 知识的价值不在占有，而在于分享和使用

项目实战哥
关注
  • 1
    点赞
  • 踩
  • 9
    收藏
    觉得还不错? 一键收藏
  • 打赏
    打赏
  • 0
    评论
专栏目录
oracle之占比函数
07-25
oracle之占比函数 oracle之占比函数 oracle之占比函数
Oracle 计算百分比
u013625182的博客
11-26 9136
ROUND(100*ratio_to_report(order_total)        OVER (PARTITION BY customer_id),2) pct_total     Oder——tota1 是分子， OVER (PARTITION BY customer_id) 是分母 round（2） 是保留两位....
oracle 求占百分比的分析函数 ratio_to_report() over()；
热门推荐
lol_3168的博客
10-17 2万+
 drop table test;  create table test  (  name varchar(20),  kemu varchar(20),  score number  );  insert into test values('testa','yuwen',10);  insert into test values('testa','英语',100);  i
用oracle语句计算百分比
方全的专栏
07-04 2万+
http://www.2cto.com/database/201605/506776.html 来公司第一天，也没什么事干，经理说今天需要把报表做出来，需要写几个oracle语句，用来查询各类别下的人 员数量和占总人数比例。如下图 统计人数到不是问题，关键是比例不知道该怎么去做，上网查了好几个方式都尝试过了，但还是没能弄出来，一 下午的时间全花在解决这个问题上了
oracle数据库 占比,oracle 占比函数
weixin_39524574的博客
04-13 735
[root@suys1~]#su-oracle[oracle@suys1~]$sqlplus/assysdbaSQL*Plus:Release11.2.0.4.0ProductiononFriJun609:00:032014Copyright(c)1982,2013,Oracle.Allrightsreserved.Connectedtoan...
Oracle——环比增长率
weixin_30699831的博客
01-02 467
　　首先，了解什么是：环比增长率？ 　　环比增长率=(本期数－上期数)÷上期数×100% 　　如：2014年2月的工资为：5000，2014年1月的工资为4000，则2月份的环比增长率为： 　　　　　　　　(5000-4000) ÷ 4000 × 100% =25% 　　那么，oracle中的sql语句怎么写呢？ 　　举个例子 　　1.建表 create table ...
oracle 占百分比,oracle 分析函数——ration_to_report 求占有率（百分比）
weixin_39724441的博客
04-12 552
oracle 的分析函数有很多，但是这个函数总是会忘记，我想通过这种方式能让自己记起来，不至于下次还要百度。创表、表数据(平时练手的表)：prompt PL/SQL Developer import fileprompt Createdon 2018年7月5日 星期四 byAdministratorset feedback offset define offprompt Creating CKX0...
【Oracle】计算百分比
阿野啊的博客
02-28 2469
使用ratio_to_report()over() 函数计算百分比
oracle计算比例，某字段占总数的百分比
lixingecho的博客
08-09 2万+
一、方法 我的sql语句本来就计算好了不同字段的记录数，只需要再统计一下占总记录数的百分比即可，也就是在select后加上这段代码： 100 * round( COUNT( * ) / SUM( COUNT( * )) OVER (), 4 ) || '%' percent 这个语句中用到的几个sql函数讲解： 1、ROUND() 函数 ： 用于把数值字段舍入为指定的小数位数，即保留几位小数。 round(a,b) a 表示要舍入的字段；b规定要返回的小数位数。 详细说明：SQL ROUND() 函数
分析函数之求占比函数
Liveor_Die的博客
05-09 3729
分析函数之求占比函数 RATIO_TO_REPORT     定义：RATIO_TO_REPORT是一个分析函数。它计算值与一组值之和的比率。如果expr求值为null，则ratio-to-report值也将计算为null。这组值由query_partition_clause。确定。如果省略该子句，则会根据查询返回的所有行计算报告比率。您不能通过使用RAT...
oracle求同比,环比函数(LAG与LEAD)的详解
01-19
Lag和Lead函数可以在一次查询中取出同一字段的前N行的数据和后N行的值。这种操作可以使用对相同表的表连接来实现，不过使用LAG和LEAD有更高的效率。 代码如下:CREATE TABLE salaryByMonth( employeeNo varchar2(20)...
Oracle计算时间差常用函数
09-09
主要介绍了Oracle计算时间差常用函数，涉及用法代码及示例，具有一定参考价值。需要的朋友可以了解下。
Oracle参数设置教程之set和reset的实用案例
09-09
最近在学习oracle，学习中遇到了一些觉着有必要记录下的内容，所以下面这篇文章主要给大家介绍了关于Oracle参数设置教程之set和reset实用案例的相关资料，文中通过示例代码介绍的非常详细，需要的朋友可以参考借鉴，...
U9经典案例PPT挑战SAP和ORACLEERP.pptx
04-14
U9经典案例PPT挑战SAP和ORACLEERP
HarmonyOS实战开发-如何在鸿蒙开发中使用数据库
m0_70749039的博客
04-26 641
鸿蒙中的数据库基于SQLite组件，用来处理关系比较复杂的数据，本文将以WORKER表为例，为大家演示在鸿蒙开发中对数据库的增删改查操作。
笔记：oracle报错ORA-55941
Lizi_TT的博客
04-26 287
这里的时间戳需要注意,如果 AUDIT_TRAIL_TYPE为OS或者XML格式,则需要使用本地系统时间的格式,其他2种格式为UTC.第四步创建DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP定期推进审计记录的归档时间戳的存储过程.第一步针对AUDIT_TRAIL_AUD_STD设置init_cleanup每24小时执行一次.第三步 设置DBMS_AUDIT_MGMT过程中的purge_job。第二步 设置为超过14天审计记录标记时间戳.设置完成,执行一次.
【课设资源分享】基于jsp的俱乐部会员系统
WEGST的博客
04-25 627
基于jsp的健身俱乐部会员系统，jsp Serverlet, JavaBeans, MySQL
SpringBoot中多数据源灵活切换解决方案
最新发布
lucky_love816的博客
04-26 972
本篇内容介绍了“SpringBoot中如何使用Dynamic Datasource配置多数据源”的有关知识，在实际案例的操作过程中，不少人都会遇到这样的困境，接下来就让小编带领大家学习一下如何处理这些情况吧！希望大家仔细阅读，能够学有所成！支持 数据源分组 ，适用于多种场景 纯粹多库 读写分离 一主多从 混合模式。支持无数据源启动，支持配置懒启动数据源(3.3.2+)。支持数据库敏感配置信息 加密 ENC()。支持每个数据库独立初始化表结构schema和数据库database。
预防oracle的漏洞及其提权
keyboard专栏
04-23 495
防止Oracle数据库的漏洞及其潜在的权限提升，需要实施一系列综合的安全措施。这些措施不仅涉及技术配置，还包括过程管理和持续的安全评估。
Oracle如何计算环比
06-01
在Oracle中计算环比，可以使用如下的SQL语句： ``` SELECT (current_value - last_value) / last_value * 100 AS MoM FROM ( SELECT value AS current_value, LAG(value) OVER (ORDER BY date_column) AS last_...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

项目实战哥

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付：¥1
获取中
扫码支付

您的余额不足，请更换扫码支付或充值

打赏作者

实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

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

余额充值