平武建站

帮助MySQL实现Oracl高级分析函数的方法

下面一起来了解下帮助MySQL实现Oracl高级分析函数的方法，相信大家看完肯定会受益匪浅，文字在精不在多，希望帮助MySQL实现Oracl高级分析函数的方法这篇短内容是你想要的。

创新互联秉承实现全网价值营销的理念，以专业定制企业官网，成都网站建设、做网站，小程序制作，网页设计制作，手机网站制作，营销型网站帮助传统企业实现“互联网+”转型升级专业定制企业官网,公司注重人才、技术和管理，汇聚了一批优秀的互联网技术人才,对客户都以感恩的心态奉献自己的专业和所长。

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


A

1

=connect("orcl")

2

=A1.query@x("select   employee_id, first_name, last_name, manager_id from hr.employees")

3

=A2.keys(EMPLOYEE_ID)

4

=A2.select@1(EMPLOYEE_ID==102)

5

=A2.switch(MANAGER_ID,   A2)

6

=A2.nodes(MANAGER_ID,   A4)

7

=(A4|A6).new(EMPLOYEE_ID,   FIRST_NAME, LAST_NAME, MANAGER_ID.EMPLOYEE_ID:MANAGER_ID)

(1)   A3 设置序表 A2 的键

(2)   A4 选取起始雇员

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

(4)   A6 获取起始雇员的所有子节点

                                              帮助MySQL实现Oracl高级分析函数的方法

b)    select employee_id, first_name,last_name,manager_id  

from hr.employees  

start with employee_id=104  

connect by prior manager_id = employee_id


A

1

=connect("orcl")

2

=A1.query@x("select   employee_id, first_name, last_name, manager_id from hr.employees")

3

=A2.keys(EMPLOYEE_ID)

4

=A2.switch(MANAGER_ID,   A2)

5

=A2.select@1(EMPLOYEE_ID==104)

6

=A5.prior(MANAGER_ID)

7

=A6.new(EMPLOYEE_ID,   FIRST_NAME, LAST_NAME, MANAGER_ID.EMPLOYEE_ID:MANAGER_ID)

(1)   A6 获取起始雇员的所有父节点

帮助MySQL实现Oracl高级分析函数的方法

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


A

1

=connect("orcl")

2

=A1.query@x("select   employee_id, last_name, manager_id,null path from hr.employees")

3

=A2.keys(EMPLOYEE_ID)

4

=A2.select@1(EMPLOYEE_ID==102)

5

=A2.switch(MANAGER_ID,   A2)

6

=A2.nodes(MANAGER_ID,   A4)

7

=A4|A6

8

=A7.run(PATH=if(EMPLOYEE_ID==102,   "/"+LAST_NAME, MANAGER_ID.PATH+"/"+LAST_NAME))

9

=A7.new(EMPLOYEE_ID,   LAST_NAME, MANAGER_ID.EMPLOYEE_ID:MANAGER_ID, PATH)

(1)   由于 A7 中每条记录的父节点都在本节点之前，故 A8 可以从前往后对 A7 中每条记录依次修改 PATH 值

帮助MySQL实现Oracl高级分析函数的方法

 

2、         嵌套聚集函数

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

from hr.employees

group by department_id


A

1

=connect("orcl")

2

=A1.query@x("select * from   hr.employees")

3

=A2.groups(DEPARTMENT_ID;max(SALARY):m1,   min(SALARY):m2)

4

=A3.group(;~.avg(m1):avg_max,~.avg(m2):avg_min)

(1)   A2 中 A1.query 也可以改用 A1.cursor

帮助MySQL实现Oracl高级分析函数的方法

 

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


A

1

=connect("orcl")

2

=A1.query@x("select   * from hr.employees order by department_id,commission_pct")

3

=A2.group@o(DEPARTMENT_ID)

4

=A3.new(DEPARTMENT_ID,~.minp@a(ifn(COMMISSION_PCT,2)).min(SALARY):worst,   ~.maxp@a(ifn(COMMISSION_PCT,2)).max(SALARY):best)

5

=A4.sort(ifn(DEPARTMENT_ID,power(2,32)))

(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) 来保证这一行排在最后

如果数据量大，还可以采用游标方式。


A

1

=connect("orcl")

2

=A1.cursor@x("select   * from hr.employees")

3

=A2.groups(DEPARTMENT_ID;   min([if(COMMISSION_PCT,2),SALARY]):m1, max([if(COMMISSION_PCT,2),   SALARY]):m2)

4

=A3.new(DEPARTMENT_ID,   m1(2):worst, m2(2):best)

5

=A4.sort(ifn(DEPARTMENT_ID,power(2,32)))

(1)    A3 中，min([if(COMMISSION_PCT,2), SALARY]) 求出 COMMISSION_PCT 最小时的 SALARY 最小值，即 COMMISSION_PCT 排名第一时 SALARY 最小值，max 类似

帮助MySQL实现Oracl高级分析函数的方法

 

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


A

1

=connect("orcl")

2

=A1.query@x("select   last_name,salary from hr.employees where job_id='PU_CLERK'order by   last_name")

3

=A2.sum(SALARY)

4

=A2.new(LAST_NAME, SALARY, SALARY/A3:RR)

       帮助MySQL实现Oracl高级分析函数的方法

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


A

1

=connect("orcl")

2

=A1.query@x("select   department_id,last_name,salary from hr.employees where department_id in   (20,60) order by department_id,last_name")

3

=A2.groups@o(DEPARTMENT_ID;sum(SALARY):sum)

4

=A2.switch(DEPARTMENT_ID,   A3)

5

=A2.new(DEPARTMENT_ID.DEPARTMENT_ID:DEPARTMENT_ID,   LAST_NAME, SALARY, SALARY/DEPARTMENT_ID.sum:RR)

(1)    A2 中已按 DEPARTMENT_ID 排序，则 A3 可用 groups@o 分组聚集

帮助MySQL实现Oracl高级分析函数的方法

 

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)


A

1

=connect("orcl")

2

=A1.query@x("select department_id,   job_id, salary from hr.employees where department_Id in (30,50) order by   department_id, job_id")

3

=A2.groups@o(DEPARTMENT_ID, JOB_ID;   sum(SALARY):TOTAL)

4

=A3.group@o(DEPARTMENT_ID, null:JOB_ID; ~.sum(TOTAL):TOTAL)

5

=[A3,A4].merge(DEPARTMENT_ID,   ifn(JOB_ID,fill("z", 10)))

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

也可以采用游标方式。


A

1

=connect("orcl")

2

=A1.cursor@x("select   department_id,job_id,sum(salary) total from hr.employees where department_id   in (30,50) group by department_id, job_id order by   department_id,job_id")

3

=A2.group(DEPARTMENT_ID)

4

=A3.(~.insert(0,   ~.groups@o(DEPARTMENT_ID, null:JOB_ID;sum(TOTAL):TOTAL)))

5

=A4.fetch()

6

=A5.conj()

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

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

还可以采用管道方式。


A

1

=connect("orcl")

2

=A1.cursor@x("select department_id,   job_id, salary from hr.employees where department_Id in (30,50) order by   department_id, job_id")

3

=channel().group@o(DEPARTMENT_ID, JOB_ID;   sum(SALARY):TOTAL)

4

>A2.push(A3)

5

=channel().groups@o(DEPARTMENT_ID,   null:JOB_ID; sum(TOTAL):TOTAL)

6

>A3.push(A5)

7

=A3.fetch()

8

for A2,1000

9

=A3.result()|A5.result()

10

=A9.sort(DEPARTMENT_ID)

(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 的排在后面

帮助MySQL实现Oracl高级分析函数的方法

看完帮助MySQL实现Oracl高级分析函数的方法这篇文章后，很多读者朋友肯定会想要了解更多的相关内容，如需获取更多的行业信息，可以关注我们的行业资讯栏目。


当前文章：帮助MySQL实现Oracl高级分析函数的方法
网址分享：http://pwwzsj.com/article/jgdgpd.html

其他资讯