共計 2474 個字符，預計需要花費 7 分鐘才能閱讀完成。
丸趣 TV 小編給大家分享一下怎么協(xié)助 MySQL 實現(xiàn) Oracle 高級分析函數(shù)，相信大部分人都還不怎么了解，因此分享這篇文章給大家參考一下，希望大家閱讀完這篇文章后大有收獲，下面讓我們一起去了解一下吧！
Oracle 支持一些獨特的語法和函數(shù)，在移植到 MySQL 上時或多或少給程序員造成了困擾，下面我們針對 Oracle 的一些特殊用法舉例并講解如何用集算器來完成同樣功能。這些方法當然也不限于針對 MySQL，對于所有其它數(shù)據(jù)庫也能支持。
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) 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
(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
(1) 由于 A7 中每條記錄的父節(jié)點都在本節(jié)點之前，故 A8 可以從前往后對 A7 中每條記錄依次修改 PATH 值
2、 嵌套聚集函數(shù)
select avg(max(salary)) avg_max, avg(min(salary)) avg_min
from hr.employees
group by department_id
(1) A2 中 A1.query 也可以改用 A1.cursor
3、 聚集分析函數(shù) 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
(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) 來保證這一行排在最后
如果數(shù)據(jù)量大，還可以采用游標方式。
(1) A3 中，min([if(COMMISSION_PCT,2), SALARY]) 求出 COMMISSION_PCT 最小時的 SALARY 最小值，即 COMMISSION_PCT 排名第一時 SALARY 最小值，max 類似
4、 占比函數(shù) 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
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
(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)
(1) 因為 A3 和 A4 均對 DEPARTMENT_ID 有序，故 A5 可 merge，ifn(JOB_ID,fill( z ,10))) 用來保證 JOB_ID 為 null 排在后面
也可以采用游標方式。
(1) A3 中 A2.group 要求 A2 對 DEPARTMENT_ID 有序
(2) A4 對 A3 每一組求和并將結果插入此組末尾
還可以采用管道方式。
(1) A3 創(chuàng)建管道，并附加分組求和
(2) A4 將 A2 中數(shù)據(jù)推送到 A3，注意此動作只有在 A2 中數(shù)據(jù)有實際取出行為才執(zhí)行
(3) A5 創(chuàng)建管道，并附加分組求和
(4) A6 將 A3 結果推送到 A5，此處也可以直接將 A2 中數(shù)據(jù)推送到 A5，但會增加時間復雜度
(5) A7 保留 A3 的數(shù)據(jù)
(6) 循環(huán)讀取 A2，每次只取 1000 條，減少內(nèi)存占用
(7) A10 對 A3 和 A5 中數(shù)據(jù)排序，因為算法是穩(wěn)定的，所以 JOB_ID 為 null 的排在后面
以上是“怎么協(xié)助 MySQL 實現(xiàn) Oracle 高級分析函數(shù)”這篇文章的所有內(nèi)容，感謝各位的閱讀！相信大家都有了一定的了解，希望分享的內(nèi)容對大家有所幫助，如果還想學習更多知識，歡迎關注丸趣 TV 行業(yè)資訊頻道！
