菲洛嘉青春动能素135HA FILLMED® NCTF 135HA LED指示灯的常见故障分析 智微智能 Elkhartlake K075终端,零售产业新选择 天空蓝拓客管理系统详细介绍版 muso公链项目 天使计划 是什么?[秘] 独家揭秘最前沿的家装“黑科技”——掌赋 天博体育欧洲杯特辑,东道主法兰西的失意2016 亚马逊的送货侦察员 学习听起来像挡泥板 Google Comics Factory使ML变得容易 笑着说-男性或女性 Amazon Rekognition中更好的人脸检测 关于Spaun的真相-大脑模拟 两个聊天机器人彼此聊天-有趣又怪异 GANPaint:将AI用于艺术 WCF和WF给予社区 从耳朵到脸 所有神经网络的深层缺陷 蠕虫在尾巴上平衡杆子 Kickstarter上的OpenCV AI套件 TensorFlow-Google的开源AI和计算引擎 众包取代新闻工作者 Google的DeepMind学会玩街机游戏 哑机器人V智能机器人 .NET与.NET 5融为一体 Google的深度学习-语音识别 LInQer将.NET LINQ移植到Javascript 机器人TED演讲-新的图灵测试? GAN的发明者加入苹果 您的智能手机会监视您键入的内容 人工智能帮助改善国际象棋 Zalando Flair NLP库已更新 TensorFlow 1.5包含移动版本 AlphaGo输了一场比赛-比分3-1 虚拟机器学习峰会 Microsoft开源AI调试工具 SharePoint走向移动 F#4.0发出文化变革的信号 克里斯蒂拍卖AI艺术品 人工智能如何区分 Facebook在蒙特利尔的新AI实验室 Mozilla想要您的声音 微软使用极深的神经网络赢得ImageNet 建立AI合作伙伴关系 .NET Core 3-Microsoft几乎回到了起点 神经网络-更好的销售商? Google使用AI查找您的住所 虹膜-适用于Android的Siri证明苹果没有优势 TensorFlow 2提供更快的模型训练 深度学习研究人员将为Google工作
您的位置:首页 >前端 >

Oracle实战优化:递归+分析函数+OLAP函数的应用

原创文章,转载请注明出处,谢谢合作。 https://blog.csdn.net/DarianMograine/article/details/108561662

日前,笔者在工作中被人问到这样一个问题:

Oracle数据库中,如何优化取每一行数据在当前表中同分组数据的汇总。

当前表取出数据量级1W+,原逻辑是先取每行后对表进行N次访问每次读取数据,虽然是主键回表汇总金额,由于数据条数过多,页面返回时间无法预期,希望能够通过SQL层面优化。 如下图所示,希望得到每日当月累计营业额:

示例在这里插入图片描述 以上是问题的背景。

实际上,类似场景有很多,如:

汇总部分门店每日当月累计营业额按规则自动分组匹配(银行对账、核销、单据对比等)

这里给大家推荐一个思路,利用递归+分组开窗函数+OLAP分析函数一个SQL实现数据的抓取。

我们先来看下实现的代码:

创建演示表
CREATE TABLE test_cb(keyNUMBER,iodate DATE,amount NUMBER);
初始化模拟数据
INSERT INTO test_cb(key, iodate, amount)SELECT 1key,DATE '2020-05-01' iodate,100amountFROM dualUNION ALLSELECT 1key,DATE '2020-05-01' iodate,150amountFROM dualUNION ALLSELECT 1key,DATE '2020-05-03' iodate,120amountFROM dualUNION ALLSELECT 2key,DATE '2020-05-01' iodate,80 amountFROM dualUNION ALLSELECT 2key,DATE '2020-05-02' iodate,230amountFROM dualUNION ALLSELECT 2key,DATE '2020-05-04' iodate,710amountFROM dual;
抓取数据的SQL代码
SELECT sub2.key,sub2.iodate,sub2.amount "当前行金额",dbms_aw.eval_number(substr(sys_connect_by_path(sub2.amount, '+'), 2)) "汇总到当前行金额",lag(dbms_aw.eval_number(substr(sys_connect_by_path(sub2.amount, '+') ,2))) over(PARTITION BY sub2.key ORDER BY sub2.iodate) "汇总到上一行金额"FROM (SELECT sub1.key,sub1.iodate,SUM(sub1.amount) amount,row_number() over(PARTITION BY sub1.key ORDER BY sub1.iodate) rnFROM test_cb sub1GROUPBY sub1.iodate ,sub1.key) sub2CONNECT BY nocycle(PRIOR sub2.rn = sub2.rn - 1ANDPRIOR sub2.key = sub2.key)STARTWITH rn = 1

接下来我们来看一下这段代码是怎么解决的这个问题:

我们利用分析函数row_number对数据以字段key为分组依据做分组牌序,得到每行数据展示的顺序

利用递归天然有序的特性,将之前行的金额形成形如“+200+100+150”这样的字符串并去掉第一个+,这样我们就得到一个字符串200+100+150,亦即代码中**substr(sys_connect_by_path(sub2.amount, ‘+’), 2)**部分。

利用OLAP函数dbms_aw.eval_number计算200+100+150的值,类似excel里=200+100+150的功能得到450。

利用分析函数lag在以key为基准,以iodate为排序字段的分组中获取上一行的步骤3金额,即得到上一行的汇总金额。

优化后的SQL中由于使用的是Oracle的内置函数,速度基本可以控制在2s以内。

是不是很神奇?感兴趣的筒子可以一试。

原创文章,转载请注明出处,谢谢合作。 https://blog.csdn.net/DarianMograine/article/details/108561662

郑重声明:本文版权归原作者所有,转载文章仅为传播更多信息之目的,如作者信息标记有误,请第一时间联系我们修改或删除,多谢。