溫馨提示×
您好，登錄后才能下訂單哦！
點擊 登錄注冊 即表示同意《億速云用戶服務條款》
您好，登錄后才能下訂單哦！
下面一起來了解下幫助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 獲取起始雇員的所有子節(jié)點
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 獲取起始雇員的所有父節(jié)點
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 中每條記錄的父節(jié)點都在本節(jié)點之前，故 A8 可以從前往后對 A7 中每條記錄依次修改 PATH 值
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
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 類似
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) |
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 分組聚集
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 創(chuàng)建管道，并附加分組求和
(2) A4 將 A2 中數據推送到 A3，注意此動作只有在 A2 中數據有實際取出行為才執(zhí)行
(3) A5 創(chuàng)建管道，并附加分組求和
(4) A6 將 A3 結果推送到 A5，此處也可以直接將 A2 中數據推送到 A5，但會增加時間復雜度
(5) A7 保留 A3 的數據
(6) 循環(huán)讀取 A2，每次只取 1000 條，減少內存占用
(7) A10 對 A3 和 A5 中數據排序，因為算法是穩(wěn)定的，所以 JOB_ID 為 null 的排在后面
看完幫助MySQL實現Oracl高級分析函數的方法這篇文章后，很多讀者朋友肯定會想要了解更多的相關內容，如需獲取更多的行業(yè)信息，可以關注我們的行業(yè)資訊欄目。
免責聲明：本站發(fā)布的內容（圖片、視頻和文字）以原創(chuàng)、轉載和分享為主，文章觀點不代表本網站立場，如果涉及侵權請聯系站長郵箱：is@yisu.com進行舉報，并提供相關證據，一經查實，將立刻刪除涉嫌侵權內容。
