如何协助 MySQL 实现 Oracle 高级分析函数-阿里云开发者社区

开发者社区> 润乾软件> 正文

如何协助 MySQL 实现 Oracle 高级分析函数

简介:
+关注继续查看

Oracle 支持一些独特的语法和函数,在移植到 MySQL 上时或多或少给程序员造成了困扰,下面我们针对 Oracle 的一些特殊用法举例并讲解如何用集算器来完成同样功能。这些方法当然也不限于针对 MySQL,对于所有其它数据库也能支持。

 

1、         递归语句

a)     select employee_id,first_name,last_name,manager_id

from hr.employees

start with employee_id=102

connect by prior employee_id = manager_id
1
(1) A3 设置序表 A2 的键

(2) A4 选取起始雇员

(3) A5 将 A2 中 MANAGER_ID 值转换成记录,以便递归

(4) A6 获取起始雇员的所有子节点
00
2
(1) A6 获取起始雇员的所有父节点
22
c)     select employee_id,last_name,manager_id,sys_connect_by_path(last_name,'/') path from hr.employees

start with employee_id=102

connect by prior employee_id = manager_id
3
(1) 由于 A7 中每条记录的父节点都在本节点之前,故 A8 可以从前往后对 A7 中每条记录依次修改 PATH 值
33
2、 嵌套聚集函数

select avg(max(salary)) avg_max, avg(min(salary)) avg_min

from hr.employees

group by department_id
4
(1) A2 中 A1.query 也可以改用 A1.cursor
44
3、         聚集分析函数 FIRST 和 LAST

SELECT department_id,

MIN(salary) KEEP (DENSE_RANK FIRST ORDER BY commission_pct) worst,

MAX(salary) KEEP (DENSE_RANK LAST ORDER BY commission_pct) best

FROM hr.employees

GROUP BY department_id

ORDER BY department_id
5
(1)   A2 已按 DEPARTMENT_ID 排序,则 A3 分组时可采用 group@o

(2)   FIRST/LAST 取排序的后第一组 / 最后一组,而 Oracle 排序时 null 排在最后,所以 LAST 会取到的最后一组就是 null 值所在组。maxp/minp 求具有最大值 / 最小值的所有行时排除了 null,所以在 A4 是用 ifn(COMMISSION_PCT,2) 保证 null 值时最大

(3)   A5 中,DEPARTMENT_ID=null 时采用采用比所有 DEPARTMENT_ID 都大的 power(2,32) 来保证这一行排在最后

如果数据量大,还可以采用游标方式。
6
(1) A3 中,min([if(COMMISSION_PCT,2), SALARY]) 求出 COMMISSION_PCT 最小时的 SALARY 最小值,即 COMMISSION_PCT 排名第一时 SALARY 最小值,max 类似
55
4、         占比函数 ratio_to_report

a)      SELECT last_name, salary, RATIO_TO_REPORT(salary) OVER () AS rr

FROM hr.employees

WHERE job_id = 'PU_CLERK'

ORDER BY last_name
7
66
b)      SELECT department_id,last_name, salary, RATIO_TO_REPORT(salary) OVER (partition by department_id) AS rr

FROM hr.employees

WHERE department_id in (20,60)

ORDER BY department_id,last_name
8
(1) A2 中已按 DEPARTMENT_ID 排序,则 A3 可用 groups@o 分组聚集
77
5、         多重分组

SELECT department_id, job_id, sum(salary) total

FROM hr.employees

WHERE department_id in (30, 50)

GROUP BY grouping sets((department_id, job_id), department_id)
9
(1) 因为 A3 和 A4 均对 DEPARTMENT_ID 有序,故 A5 可 merge,ifn(JOB_ID,fill("z",10))) 用来保证 JOB_ID 为 null 排在后面

也可以采用游标方式。
11

10
(1) A3 中 A2.group 要求 A2 对 DEPARTMENT_ID 有序

(2) A4 对 A3 每一组求和并将结果插入此组末尾

还可以采用管道方式。
(1)   A3 创建管道,并附加分组求和

(2)   A4 将 A2 中数据推送到 A3,注意此动作只有在 A2 中数据有实际取出行为才执行

(3)   A5 创建管道,并附加分组求和

(4)   A6 将 A3 结果推送到 A5,此处也可以直接将 A2 中数据推送到 A5,但会增加时间复杂度

(5)   A7 保留 A3 的数据

(6)   循环读取 A2,每次只取 1000 条,减少内存占用

(7)   A10 对 A3 和 A5 中数据排序,因为算法是稳定的,所以 JOB_ID 为 null 的排在后面
88

作者:KittyYan
链接:http://c.raqsoft.com.cn/article/1535964014661
来源:乾学院
著作权归作者所有。商业转载请联系作者获得授权,非商业转载请注明出处。

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
alert日志中的两种ORA错误分析
今天在巡检系统的时候,发现alert日志中有两种类型的ora错误。 Errors in file /U01/app/oracle/diag/rdbms/XX/XX/trace/xxdb_j002_20401.
921 0
理解和使用Oracle分析工具LogMiner
用LogMiner             理解和使用Oracle   8i分析工具LogMiner                 Oracle   LogMiner   是Oracle公司从产品8i以后提供的一个实际非常有用的分析工具,使用该工具可以轻松获得Oracle   重作日志文件(归档日志文件)中的具体内容,特别是,该工具可以分析出所有对于数据库操作的DML(insert、update、delete等)语句,另外还可分析得到一些必要的回滚SQL语句。
1177 0
CDN高级技术专家周哲:深度剖析短视频分发过程中的用户体验优化技术点
深圳云栖大会已经圆满落幕,在3月29日飞天技术汇-弹性计算、网络和CDN专场中,阿里云CDN高级技术专家周哲为我们带来了《海量短视频极速分发》的主题分享,带领我们从视频内容采集、上传、存储和分发的角度介绍整体方案,并且重点讲解短视频加速的注意事项和用户体验优化要点。
5110 0
【原】oracle高级工具之tkporf
作者:david_zhang@sh 【转载时请以超链接形式标明文章】 链接:http://www.cnblogs.com/david-zhang-index/archive/2012/08/16/2642261.
846 0
函数计算助力高德地图平稳支撑亿级流量高峰
2020 年的“十一出行节”期间,高德地图创造了记录 ——截止 2020 年 10 月 1 日 13 时 27 分 27 秒,高德地图当日活跃用户突破 1 亿,比 2019 年 10 月 1 日提前 3 时 41 分达成此记录。 期间,Serverless 作为其中一个核心技术场景,平稳扛住了流量高峰期的考验。值得一提的是,由 Serverless 支撑的业务在流量高峰期的表现十分优秀,每分钟函数调用量接近两百万次。这再次验证了 Serverless 基础技术的价值,进一步拓展了技术场景。
3302 0
记一次远程协助分析rac问题的案例
今天通过微信群和qq帮助一个网友分析了一个rac节点性能的问题,征得这位朋友的同学,和大家分享一下。 最开始这位朋友是在微信群中留言,说有一个rac的问题,现在已经严重影响在线业务了,希望我能够帮忙看看,有什么好的建议没,这对我来说着实是一个提高自己,分析问题的好机会,因为在地铁上,自己就简单确认了下环境,然后让他提供一些基本的错误日志或者报告。
781 0
+关注
85
文章
5
问答
文章排行榜
最热
最新
相关电子书
更多
《2021云上架构与运维峰会演讲合集》
立即下载
《零基础CSS入门教程》
立即下载
《零基础HTML入门教程》
立即下载