Oracle实战优化:递归+分析函数+OLAP函数的应用 Linux中MySQL数据库的使用③-----编码和基本数据类型 理论+实验:MySQL备份与恢复(完整备份、增量备份) “数”聚永川 “智”引未来——永川区大数据智能化产业发展强劲 从一款防疫App感受新加坡大数据智能化气息 “数”聚永川“智”引未来——永川区大数据智能化产业发展强劲 从连接量变到数据质变 物联网将二次爆发 重磅发布!猎芯半导体首创全球最小支持5G物联网的多模多频射频PA芯片 从精准授信到助企惠民,江苏银行物联网金融派上大用场 Python Selenium UI自动化_WebDriver元素_8大定位方式+总结(持续更新完善) Python中的继承、抽象基类和接口 Datawhale学习笔记【阿里云天池 金融风控-贷款违约预测】task1 赛题理解 Pytorch - torchvision计算机视觉工具库 linux 重点笔记 Ubuntu18.04安装ROS Melodic(一路到站型) 小甲鱼笔记:数据结构——线性表(一)线性表的顺序存储结构,线性表顺序存储结构的增,删,插入元素操作 实战比特币脚本编程(1) JAVA WEB DAY 01_Tomcat & Servlet Java基础算法之堆排序(Heap Sort) synchronized批量重偏向与批量撤销 终于等到了!阿里P8历时九个月整理,Java面试宝典,核心知识点笔记在此 “数字心脏”动态解析消费密码,国家级消费市场大数据联合实验室在上海先行先试 全世界运行着大约230亿台物联网设备,安全问题如何解? 物联网产业园&thinkplus解决方案中心国学讲座如期而至 都是程序员,凭什么他能站在鄙视链的顶端? 猛男必看!去小红书做程序员是种什么体验 drozer提示[Errno 2] No such file or directory 【STM32】NB-iOT BC35-G模块 AT指令应用设计指导(附代码) 【北京迅为】i.MX6ULL终结者编译LED汇编程序 Linux系统读写网卡PHY寄存器工具 洛谷:P1226 【模板】快速幂||取余运算(分治,数学) 【2020顶会KDD】AutoST:面向时空预测的高效神经网络学习模型 C/C++实现并查集disjoint_set的模板(带路径压缩优化) 实现一个百万级推送服务,除了它,还有谁 “健康守护者”——STM32标准库和HAL库的比较 程序员被公司辞退12天后,前领导要求回公司讲清代码,结果懵了 RTTR实现C++反射(1)集成rttr库 lotus node 远程运行 CCF历年4,5题收录 N卡 RTX3070/RTX3080/RTX3090挖矿算力推测 最高算力86MH 理论+实验:MySQL索引、事物与存储引擎 PostgreSQL JOIN 多表查询 TP框架实现Excel批量导入数据库数据 MySQL中的列转行 这次是真拯救了我,MySQL索引优化,explain讲得非常清楚了 Oracle快速入门(PLSQL编程) MySQL字符串拼接、截取 MySQL事务管理及存储引擎 《浪姐》万茜点赞宁静、郁可唯黑贴坐实?盗号者和程序员是背锅侠 程序员被公司辞退12天后,前领导要求回公司讲清代码,结果懵了
您的位置:首页 >前端 >

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

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