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

本文涉及的产品
云数据库 RDS MySQL,集群系列 2核4GB
推荐场景:
搭建个人博客
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云数据库 RDS MySQL,高可用系列 2核4GB
简介:

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
来源:乾学院
著作权归作者所有。商业转载请联系作者获得授权,非商业转载请注明出处。

相关实践学习
如何快速连接云数据库RDS MySQL
本场景介绍如何通过阿里云数据管理服务DMS快速连接云数据库RDS MySQL,然后进行数据表的CRUD操作。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
相关文章
|
20天前
|
SQL 关系型数据库 MySQL
MySQL事务日志-Undo Log工作原理分析
事务的持久性是交由Redo Log来保证,原子性则是交由Undo Log来保证。如果事务中的SQL执行到一半出现错误,需要把前面已经执行过的SQL撤销以达到原子性的目的,这个过程也叫做"回滚",所以Undo Log也叫回滚日志。
MySQL事务日志-Undo Log工作原理分析
|
3天前
|
关系型数据库 MySQL 数据库
mysql慢查询每日汇报与分析
通过启用慢查询日志、提取和分析慢查询日志,可以有效识别和优化数据库中的性能瓶颈。结合适当的自动化工具和优化措施,可以显著提高MySQL数据库的性能和稳定性。希望本文的详解和示例能够为数据库管理人员提供有价值的参考,帮助实现高效的数据库管理。
31 11
|
1月前
|
SQL 关系型数据库 MySQL
MySQL 窗口函数详解:分析性查询的强大工具
MySQL 窗口函数从 8.0 版本开始支持,提供了一种灵活的方式处理 SQL 查询中的数据。无需分组即可对行集进行分析,常用于计算排名、累计和、移动平均值等。基本语法包括 `function_name([arguments]) OVER ([PARTITION BY columns] [ORDER BY columns] [frame_clause])`,常见函数有 `ROW_NUMBER()`, `RANK()`, `DENSE_RANK()`, `SUM()`, `AVG()` 等。窗口框架定义了计算聚合值时应包含的行。适用于复杂数据操作和分析报告。
82 11
|
2月前
|
SQL Oracle 关系型数据库
[Oracle]面试官:你举例几个内置函数,并且说说如何使用内置函数作正则匹配
本文介绍了多种SQL内置函数,包括单行函数、非空判断函数、日期函数和正则表达式相关函数。每种函数都有详细的参数说明和使用示例,帮助读者更好地理解和应用这些函数。文章强调了字符串操作、数值处理、日期计算和正则表达式的使用方法,并提供了丰富的示例代码。作者建议读者通过自测来巩固学习成果。
36 1
[Oracle]面试官:你举例几个内置函数,并且说说如何使用内置函数作正则匹配
|
3月前
|
存储 缓存 关系型数据库
MySQL事务日志-Redo Log工作原理分析
事务的隔离性和原子性分别通过锁和事务日志实现,而持久性则依赖于事务日志中的`Redo Log`。在MySQL中,`Redo Log`确保已提交事务的数据能持久保存,即使系统崩溃也能通过重做日志恢复数据。其工作原理是记录数据在内存中的更改,待事务提交时写入磁盘。此外,`Redo Log`采用简单的物理日志格式和高效的顺序IO,确保快速提交。通过不同的落盘策略,可在性能和安全性之间做出权衡。
1790 14
MySQL事务日志-Redo Log工作原理分析
|
3月前
|
存储 关系型数据库 MySQL
基于案例分析 MySQL 权限认证中的具体优先原则
【10月更文挑战第26天】本文通过具体案例分析了MySQL权限认证中的优先原则,包括全局权限、数据库级别权限和表级别权限的设置与优先级。全局权限优先于数据库级别权限,后者又优先于表级别权限。在权限冲突时,更严格的权限将被优先执行,确保数据库的安全性与资源合理分配。
|
4月前
|
存储 Oracle 关系型数据库
Oracle和MySQL有哪些区别?从基本特性、技术选型、字段类型、事务、语句等角度详细对比Oracle和MySQL
从基本特性、技术选型、字段类型、事务提交方式、SQL语句、分页方法等方面对比Oracle和MySQL的区别。
900 18
|
3月前
|
SQL 关系型数据库 MySQL
MySQL 更新1000万条数据和DDL执行时间分析
MySQL 更新1000万条数据和DDL执行时间分析
262 4
|
3月前
|
SQL 自然语言处理 关系型数据库
Vanna使用ollama分析本地MySQL数据库
这篇文章详细介绍了如何使用Vanna结合Ollama框架来分析本地MySQL数据库,实现自然语言查询功能,包括环境搭建和配置流程。
417 0
|
4月前
|
Oracle NoSQL 关系型数据库
主流数据库对比:MySQL、PostgreSQL、Oracle和Redis的优缺点分析
主流数据库对比:MySQL、PostgreSQL、Oracle和Redis的优缺点分析
863 2

热门文章

最新文章

推荐镜像

更多